gistfile1.scala

    val setup = seq(
      (suppliersStd.schema ++ coffeesStd.schema).create,
      suppliersStd += (101, "Acme, Inc.",      "99 Market Street", "Groundsville", "CA", "95199"),
      suppliersStd += ( 49, "Superior Coffee", "1 Party Place",    "Mendocino",    "CA", "95460"),
      suppliersStd += (150, "The High Ground", "100 Coffee Lane",  "Meadows",      "CA", "93966"),
      coffeesStd ++= Seq(
        ("Colombian",         101, 799, 1, 0),
        ("French_Roast",       49, 799, 2, 0),
        ("Espresso",          150, 999, 3, 0),
        ("Colombian_Decaf",   101, 849, 4, 0),
        ("French_Roast_Decaf", 49, 999, 5, 0)
      )
    ).named("setup")

    val qa = for {
      c <- coffees.take(3)
    } yield (c.supID, (c.name, 42))
    val qb = qa.take(2).map(_._2)
    val qb2 = qa.map(n => n).take(2).map(_._2)
    val qc = qa.map(_._2).take(2)

    val a1 = seq(
      qa.result.map(_.toSet).map { ra =>
        ra.size shouldBe 3
        // No sorting, so result contents can vary
        ra shouldAllMatch { case (s: Int, (i: String, 42)) => () }
      },
      qb.result.map(_.toSet).map { rb =>
        rb.size shouldBe 2
        // No sorting, so result contents can vary
        rb shouldAllMatch { case (i: String, 42) => () }
      },
      qb2.result.map(_.toSet).map { rb2 =>
        rb2.size shouldBe 2
        // No sorting, so result contents can vary
        rb2 shouldAllMatch { case (i: String, 42) => () }
      },
      qc.result.map(_.toSet).map { rc =>
        rc.size shouldBe 2
        // No sorting, so result contents can vary
        rc shouldAllMatch { case (i: String, 42) => () }
      }
    )

    // Plain table
    val q0 = coffees

    // Plain implicit join
    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)

    // Explicit join with condition
    val q1b_0 = coffees.sortBy(_.price).take(3) join suppliers on (_.supID === _.id)
    def 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)

    val a2 = seq(
      q0.result.named("Plain table").map(_.toSet).map { r0 =>
        r0 shouldBe Set(
          ("Colombian",         101, 799, 1, 0),
          ("French_Roast",       49, 799, 2, 0),
          ("Espresso",          150, 999, 3, 0),
          ("Colombian_Decaf",   101, 849, 4, 0),
          ("French_Roast_Decaf", 49, 999, 5, 0)
        )
      },
      q1.result.named("Plain implicit join").map(_.toSet).map { r1 =>
       r1 shouldBe Set(
          (("Colombian","Groundsville:"),("Colombian",101,799,1,0),(101,"Acme, Inc.","99 Market Street"),799),
          (("Colombian","Mendocino:"),("Colombian",101,799,1,0),(49,"Superior Coffee","1 Party Place"),799),
          (("Colombian","Meadows:"),("Colombian",101,799,1,0),(150,"The High Ground","100 Coffee Lane"),799),
          (("Colombian_Decaf","Groundsville:"),("Colombian_Decaf",101,849,4,0),(101,"Acme, Inc.","99 Market Street"),3396),
          (("Colombian_Decaf","Mendocino:"),("Colombian_Decaf",101,849,4,0),(49,"Superior Coffee","1 Party Place"),3396),
          (("Colombian_Decaf","Meadows:"),("Colombian_Decaf",101,849,4,0),(150,"The High Ground","100 Coffee Lane"),3396)
        )
      },
      ifCap(rcap.pagingNested) {
        q1b.result.named("Explicit join with condition").map { r1b =>
          r1b.toSet shouldBe Set(
            ("French_Roast","Mendocino","Colombian"),
            ("French_Roast","Mendocino","French_Roast"),
            ("French_Roast","Mendocino","Colombian_Decaf")
          )
        }
      }
    )

    // More elaborate query
    val q2 = for {
      c <- coffees.filter(_.price < 900).map(_.*)
      s <- suppliers if s.id === c._2
    } yield (c._1, s.name)

    // Lifting scalar values
    val q3 = coffees.flatMap { c =>
      val cf = Query(c).filter(_.price === 849)
      cf.flatMap { cf =>
        suppliers.filter(_.id === c.supID).map { s =>
          (c.name, s.name, cf.name, cf.total, cf.totalComputed)
        }
      }
    }

    // Lifting scalar values, with extra tuple
    val q3b = coffees.flatMap { c =>
      val cf = Query((c, 42)).filter(_._1.price < 900)
      cf.flatMap { case (cf, num) =>
        suppliers.filter(_.id === c.supID).map { s =>
          (c.name, s.name, cf.name, cf.total, cf.totalComputed, num)
        }
      }
    }

    // Map to tuple, then filter
    def q4 = for {
      c <- coffees.map(c => (c.name, c.price, 42)).sortBy(_._1).take(2).filter(_._2 < 800)
    } yield (c._1, c._3)

    // Map to tuple, then filter, with self-join
    val q4b_0 = coffees.map(c => (c.name, c.price, 42)).filter(_._2 < 800)
    val q4b = for {
      c <- q4b_0
      d <- q4b_0
    } yield (c,d)

    val a3 = seq(
      q2.result.named("More elaborate query").map(_.toSet).map { r2 =>
        r2 shouldBe Set(
          ("Colombian","Acme, Inc."),
          ("French_Roast","Superior Coffee"),
          ("Colombian_Decaf","Acme, Inc.")
        )
      },
      q3.result.named("Lifting scalar values").map(_.toSet).map { r3 =>
        r3 shouldBe Set(("Colombian_Decaf","Acme, Inc.","Colombian_Decaf",0,3396))
      },
      q3b.result.named("Lifting scalar values, with extra tuple").map(_.toSet).map { r3b =>
        r3b shouldBe Set(
          ("Colombian","Acme, Inc.","Colombian",0,799,42),
          ("French_Roast","Superior Coffee","French_Roast",0,1598,42),
          ("Colombian_Decaf","Acme, Inc.","Colombian_Decaf",0,3396,42)
        )
      },
      ifCap(rcap.pagingNested) {
        q4.result.named("Map to tuple, then filter").map { r4 =>
          r4.toSet shouldBe Set(("Colombian",42))
        }
      },
      q4b.result.map(_.toSet).map { r4b =>
        r4b shouldBe Set(
          (("Colombian",799,42),("Colombian",799,42)),
          (("Colombian",799,42),("French_Roast",799,42)),
          (("French_Roast",799,42),("Colombian",799,42)),
          (("French_Roast",799,42),("French_Roast",799,42))
        )
      }
    )

    // Implicit self-join
    val q5_0 = coffees.sortBy(_.price).take(2)
    val q5 = for {
      c1 <- q5_0
      c2 <- q5_0
    } yield (c1, c2)

    // Explicit self-join with condition
    val q5b = for {
      t <- q5_0 join q5_0 on (_.name === _.name)
    } yield (t._1, t._2)

    // Unused outer query result, unbound TableQuery
    val q6 = coffees.flatMap(c => suppliers)

    val a4 = seq(
      q5.result.map(_.toSet).map { r5 =>
        r5 shouldBe Set(
          (("Colombian",101,799,1,0),("Colombian",101,799,1,0)),
          (("Colombian",101,799,1,0),("French_Roast",49,799,2,0)),
          (("French_Roast",49,799,2,0),("Colombian",101,799,1,0)),
          (("French_Roast",49,799,2,0),("French_Roast",49,799,2,0))
        )
      },
      q5b.result.named("Explicit self-join with condition").map(_.toSet).map { r5b =>
        r5b shouldBe Set(
          (("Colombian",101,799,1,0),("Colombian",101,799,1,0)),
          (("French_Roast",49,799,2,0),("French_Roast",49,799,2,0))
        )
      },
      q6.result.named("Unused outer query result, unbound TableQuery").map(_.toSet).map { r6 =>
        r6 shouldBe Set(
          (101,"Acme, Inc.","99 Market Street"),
          (49,"Superior Coffee","1 Party Place"),
          (150,"The High Ground","100 Coffee Lane")
        )
      }
    )

    // Simple union
    val q7a = for {
      c <- coffees.filter(_.price < 800) union coffees.filter(_.price > 950)
    } yield (c.name, c.supID, c.total)

    // Union
    val q7 = for {
      c <- coffees.filter(_.price < 800).map((_, 1)) union coffees.filter(_.price > 950).map((_, 2))
    } yield (c._1.name, c._1.supID, c._2)

    // Transitive push-down without union
    val q71 = for {
      c <- coffees.filter(_.price < 800).map((_, 1))
    } yield (c._1.name, c._1.supID, c._2)

    val a5 = seq(
      q7a.result.named("Simple union").map(_.toSet).map { r7a =>
        r7a shouldBe Set(
          ("Colombian",101,0),
          ("French_Roast",49,0),
          ("Espresso",150,0),
          ("French_Roast_Decaf",49,0)
        )
      },
      q7.result.named("Union").map(_.toSet).map { r7 =>
        r7 shouldBe Set(
          ("Colombian",101,1),
          ("French_Roast",49,1),
          ("Espresso",150,2),
          ("French_Roast_Decaf",49,2)
        )
      },
      q71.result.named("Transitive push-down without union").map(_.toSet).map { r71 =>
        r71 shouldBe Set(
          ("Colombian",101,1),
          ("French_Roast",49,1)
        )
      }
    )

    // Union with filter on the outside
    val q7b = q7 filter (_._1 =!= "Colombian")

    // Outer join
    val q8 = for {
      (c1, c2) <- coffees.filter(_.price < 900) joinLeft coffees.filter(_.price < 800) on (_.name === _.name)
    } yield (c1.name, c2.map(_.name))

    // Nested outer join
    val q8b = for {
      t <- coffees.sortBy(_.sales).take(1) joinLeft coffees.sortBy(_.sales).take(2) on (_.name === _.name) joinLeft coffees.sortBy(_.sales).take(4) on (_._1.supID === _.supID)
    } yield (t._1, t._2)

    val a6 = seq(
      q7b.result.named("Union with filter on the outside").map(_.toSet).map { r7b =>
        r7b shouldBe Set(
          ("French_Roast",49,1),
          ("Espresso",150,2),
          ("French_Roast_Decaf",49,2)
        )
      },
      q8.result.named("Outer join").map(_.toSet).map { r8 =>
        r8 shouldBe Set(
          ("Colombian",Some("Colombian")),
          ("French_Roast",Some("French_Roast")),
          ("Colombian_Decaf",None)
        )
      },
      q8b.result.named("Nested outer join").map(_.toSet).map { r8b =>
        r8b shouldBe Set(
          ((("Colombian",101,799,1,0),Some(("Colombian",101,799,1,0))),Some(("Colombian",101,799,1,0))),
          ((("Colombian",101,799,1,0),Some(("Colombian",101,799,1,0))),Some(("Colombian_Decaf",101,849,4,0)))
        )
      }
    )

    seq(setup, a1, a2, a3, a4, a5, a6)