<commit_before>package com.typesafe.slick.testkit.tests

import org.junit.Test
import org.junit.Assert._
import scala.slick.lifted._
import scala.slick.session.Database.threadLocalSession
import scala.slick.jdbc.StaticQuery._
import scala.slick.ast._
import scala.slick.testutil.TestDB
import com.typesafe.slick.testkit.util.TestkitTest

//object NewQuerySemanticsTest extends TestkitTestObject(H2Mem)

class NewQuerySemanticsTest(val tdb: TestDB) extends TestkitTest {
  import tdb.profile.Table
  import tdb.profile.Implicit._

  val run = true

  def test = db withSession {

    object SuppliersStd extends Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street ~ city ~ state ~ zip
    }

    object CoffeesStd extends Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ total
      def supplier = foreignKey("SUP_FK", supID, SuppliersStd)(_.id)
    }

    object Suppliers extends Table[(Int, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street
      def forInsert = id ~ name ~ street ~ city ~ state ~ zip
    }

    object Coffees extends Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ (total * 10)
      def forInsert = name ~ supID ~ price ~ sales ~ total
      def totalComputed = sales.asColumnOf[Double] * price
      def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
    }

    (SuppliersStd.ddl ++ CoffeesStd.ddl).create

    SuppliersStd.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    SuppliersStd.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    SuppliersStd.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    CoffeesStd.insertAll(
      ("Colombian",         101, 7.99, 1, 0),
      ("French_Roast",       49, 7.99, 2, 0),
      ("Espresso",          150, 9.99, 3, 0),
      ("Colombian_Decaf",   101, 8.49, 4, 0),
      ("French_Roast_Decaf", 49, 9.99, 5, 0)
    )

    def q(s: String) = tdb.driver.quoteIdentifier(s)
    val l1 = queryNA[String]("select c."+q("COF_NAME")+" from "+q("COFFEES")+" c").list
    println("l1: "+l1)
    assertEquals(5, l1.length)
    val l2 = queryNA[String]("select c."+q("COF_NAME")+" from "+q("COFFEES")+" c, "+q("SUPPLIERS")+" s").list
    println("l2: "+l2)
    assertEquals(15, l2.length)

    def show(name: String, g: Query[_,_]) {
      val n = Node(g)
      println("=========================================== "+name)
      //n.dump("source: ")
      //println
      //val n2 = Optimizer(n)
      //n2.dump("optimized: ")
      //println
      //val n3 = Relational(n2)
      //n3.dump("relational: ")
      //println
      //println("SQL: "+g.selectStatement)
    }

    val qa = for {
      c <- Coffees.take(3)
    } yield (c.supID, (c.name, 42))
    show("qa", qa)
    if(run) {
      val ra = qa.to[Set]
      println("ra: "+ra)
      assertEquals(3, ra.size)
      // No sorting, so result contents can vary
      assertAllMatch(ra){ case (s: Int, (i: String, 42)) => () }
    }

    val qb = qa.take(2).map(_._2)
    show("qb", qb)
    if(run) {
      val rb = qb.to[Set]
      println("rb: "+rb)
      assertEquals(2, rb.size)
      // No sorting, so result contents can vary
      assertAllMatch(rb){ case (i: String, 42) => () }
    }

    val qb2 = qa.map(n => n).take(2).map(_._2)
    show("qb2", qb2)
    if(run) {
      val rb2 = qb2.to[Set]
      println("rb2: "+rb2)
      assertEquals(2, rb2.size)
      // No sorting, so result contents can vary
      assertAllMatch(rb2){ case (i: String, 42) => () }
    }

    val qc = qa.map(_._2).take(2)
    show("qc", qc)
    if(run) {
      val rc = qc.to[Set]
      println("rc: "+rc)
      assertEquals(2, rc.size)
      // No sorting, so result contents can vary
      assertAllMatch(rc){ case (i: String, 42) => () }
    }

    val q0 = Query(Coffees)
    show("q0: Plain table", q0)
    if(run) {
      val r0 = q0.to[Set]
      println("r0: "+r0)
      val r0e = Set(
        ("Colombian",         101, 7.99, 1, 0),
        ("French_Roast",       49, 7.99, 2, 0),
        ("Espresso",          150, 9.99, 3, 0),
        ("Colombian_Decaf",   101, 8.49, 4, 0),
        ("French_Roast_Decaf", 49, 9.99, 5, 0)
      )
      assertEquals(r0e, r0)
    }

    val q1 = for {
      c <- Coffees.sortBy(c => (c.name, c.price.desc)).take(2)
      s <- Suppliers
    } yield (c.name ~ (s.city ++ ":"), c, s, c.totalComputed)
    show("q1: Plain implicit join", q1)
    if(run) {
      val r1 = q1.to[Set]
      println("r1: "+r1)
      val r1e = Set(
        (("Colombian","Groundsville:"),("Colombian",101,7.99,1,0),(101,"Acme, Inc.","99 Market Street"),7.99),
        (("Colombian","Mendocino:"),("Colombian",101,7.99,1,0),(49,"Superior Coffee","1 Party Place"),7.99),
        (("Colombian","Meadows:"),("Colombian",101,7.99,1,0),(150,"The High Ground","100 Coffee Lane"),7.99),
        (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,8.49,4,0),(101,"Acme, Inc.","99 Market Street"),33.96),
        (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,8.49,4,0),(49,"Superior Coffee","1 Party Place"),33.96),
        (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,8.49,4,0),(150,"The High Ground","100 Coffee Lane"),33.96)
      )
      assertEquals(r1e, r1)
    }

    val q1b_0 = Coffees.sortBy(_.price).take(3) join Suppliers on (_.supID === _.id)
    val q1b = for {
      (c, s) <- q1b_0.take(2).filter(_._1.name =!= "Colombian")
      (c2, s2) <- q1b_0
    } yield c.name ~ s.city ~ c2.name
    show("q1b: Explicit join with condition", q1b)
    if(run) {
      val r1b = q1b.to[Set]
      println("r1b: "+r1b)
      val r1be = Set(
        ("French_Roast","Mendocino","Colombian"),
        ("French_Roast","Mendocino","French_Roast"),
        ("French_Roast","Mendocino","Colombian_Decaf")
      )
      assertEquals(r1be, r1b)
    }

    val q2 = for {
      c <- Coffees.filter(_.price < 9.0).map(_.*)<commit_msg>Sort before limiting in NewQuerySemanticsTest.<commit_after>package com.typesafe.slick.testkit.tests

import org.junit.Test
import org.junit.Assert._
import scala.slick.lifted._
import scala.slick.session.Database.threadLocalSession
import scala.slick.jdbc.StaticQuery._
import scala.slick.ast._
import scala.slick.testutil.TestDB
import com.typesafe.slick.testkit.util.TestkitTest

//object NewQuerySemanticsTest extends TestkitTestObject(H2Mem)

class NewQuerySemanticsTest(val tdb: TestDB) extends TestkitTest {
  import tdb.profile.Table
  import tdb.profile.Implicit._

  val run = true

  def test = db withSession {

    object SuppliersStd extends Table[(Int, String, String, String, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street ~ city ~ state ~ zip
    }

    object CoffeesStd extends Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ total
      def supplier = foreignKey("SUP_FK", supID, SuppliersStd)(_.id)
    }

    object Suppliers extends Table[(Int, String, String)]("SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      def * = id ~ name ~ street
      def forInsert = id ~ name ~ street ~ city ~ state ~ zip
    }

    object Coffees extends Table[(String, Int, Double, Int, Int)]("COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = name ~ supID ~ price ~ sales ~ (total * 10)
      def forInsert = name ~ supID ~ price ~ sales ~ total
      def totalComputed = sales.asColumnOf[Double] * price
      def supplier = foreignKey("SUP_FK", supID, Suppliers)(_.id)
    }

    (SuppliersStd.ddl ++ CoffeesStd.ddl).create

    SuppliersStd.insert(101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199")
    SuppliersStd.insert( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460")
    SuppliersStd.insert(150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966")

    CoffeesStd.insertAll(
      ("Colombian",         101, 7.99, 1, 0),
      ("French_Roast",       49, 7.99, 2, 0),
      ("Espresso",          150, 9.99, 3, 0),
      ("Colombian_Decaf",   101, 8.49, 4, 0),
      ("French_Roast_Decaf", 49, 9.99, 5, 0)
    )

    def q(s: String) = tdb.driver.quoteIdentifier(s)
    val l1 = queryNA[String]("select c."+q("COF_NAME")+" from "+q("COFFEES")+" c").list
    println("l1: "+l1)
    assertEquals(5, l1.length)
    val l2 = queryNA[String]("select c."+q("COF_NAME")+" from "+q("COFFEES")+" c, "+q("SUPPLIERS")+" s").list
    println("l2: "+l2)
    assertEquals(15, l2.length)

    def show(name: String, g: Query[_,_]) {
      val n = Node(g)
      println("=========================================== "+name)
      //n.dump("source: ")
      //println
      //val n2 = Optimizer(n)
      //n2.dump("optimized: ")
      //println
      //val n3 = Relational(n2)
      //n3.dump("relational: ")
      //println
      //println("SQL: "+g.selectStatement)
    }

    val qa = for {
      c <- Coffees.take(3)
    } yield (c.supID, (c.name, 42))
    show("qa", qa)
    if(run) {
      val ra = qa.to[Set]
      println("ra: "+ra)
      assertEquals(3, ra.size)
      // No sorting, so result contents can vary
      assertAllMatch(ra){ case (s: Int, (i: String, 42)) => () }
    }

    val qb = qa.take(2).map(_._2)
    show("qb", qb)
    if(run) {
      val rb = qb.to[Set]
      println("rb: "+rb)
      assertEquals(2, rb.size)
      // No sorting, so result contents can vary
      assertAllMatch(rb){ case (i: String, 42) => () }
    }

    val qb2 = qa.map(n => n).take(2).map(_._2)
    show("qb2", qb2)
    if(run) {
      val rb2 = qb2.to[Set]
      println("rb2: "+rb2)
      assertEquals(2, rb2.size)
      // No sorting, so result contents can vary
      assertAllMatch(rb2){ case (i: String, 42) => () }
    }

    val qc = qa.map(_._2).take(2)
    show("qc", qc)
    if(run) {
      val rc = qc.to[Set]
      println("rc: "+rc)
      assertEquals(2, rc.size)
      // No sorting, so result contents can vary
      assertAllMatch(rc){ case (i: String, 42) => () }
    }

    val q0 = Query(Coffees)
    show("q0: Plain table", q0)
    if(run) {
      val r0 = q0.to[Set]
      println("r0: "+r0)
      val r0e = Set(
        ("Colombian",         101, 7.99, 1, 0),
        ("French_Roast",       49, 7.99, 2, 0),
        ("Espresso",          150, 9.99, 3, 0),
        ("Colombian_Decaf",   101, 8.49, 4, 0),
        ("French_Roast_Decaf", 49, 9.99, 5, 0)
      )
      assertEquals(r0e, r0)
    }

    val q1 = for {
      c <- Coffees.sortBy(c => (c.name, c.price.desc)).take(2)
      s <- Suppliers
    } yield (c.name ~ (s.city ++ ":"), c, s, c.totalComputed)
    show("q1: Plain implicit join", q1)
    if(run) {
      val r1 = q1.to[Set]
      println("r1: "+r1)
      val r1e = Set(
        (("Colombian","Groundsville:"),("Colombian",101,7.99,1,0),(101,"Acme, Inc.","99 Market Street"),7.99),
        (("Colombian","Mendocino:"),("Colombian",101,7.99,1,0),(49,"Superior Coffee","1 Party Place"),7.99),
        (("Colombian","Meadows:"),("Colombian",101,7.99,1,0),(150,"The High Ground","100 Coffee Lane"),7.99),
        (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,8.49,4,0),(101,"Acme, Inc.","99 Market Street"),33.96),
        (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,8.49,4,0),(49,"Superior Coffee","1 Party Place"),33.96),
        (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,8.49,4,0),(150,"The High Ground","100 Coffee Lane"),33.96)
      )
      assertEquals(r1e, r1)
    }

    val q1b_0 = Coffees.sortBy(_.price).take(3) join Suppliers on (_.supID === _.id)
    val q1b = for {
      (c, s) <- q1b_0.sortBy(_._1.price).take(2).filter(_._1.name =!= "Colombian")
      (c2, s2) <- q1b_0
    } yield c.name ~ s.city ~ c2.name
    show("q1b: Explicit join with condition", q1b)
    if(run) {
      val r1b = q1b.to[Set]
      println("r1b: "+r1b)
      val r1be = Set(
        ("French_Roast","Mendocino","Colombian"),
        ("French_Roast","Mendocino","French_Roast"),
        ("French_Roast","Mendocino","Colombian_Decaf")
      )
      assertEquals(r1be, r1b)
    }

    val q2 = for {
      c <- Coffees.filter(_.price < 9.0).map(_.*)
{'fraction_non_alphanumeric': 0.18397291196388263, 'fraction_numerical': 0.04197234762979684, 'mean_word_length': 2.785580774365821, 'pattern_counts': {'":': 2, '<': 17, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3318556', 'n_tokens_mistral': 5594, 'n_tokens_neox': 5228, 'n_words': 1145}