szeiger/scala-query — Issue #30: Illegal SQL code generated: FROM clause missing if subquery is used twice
Author: umm
Created: 2012-01-01 18:17:13+00:00
State: open

If a subquery variable is used more than once within a query result definition, the FROM clause in one of the subqueries is omitted.

```scala
import org.scalaquery.ql.extended.HsqldbDriver.Implicit._
import org.scalaquery.ql.extended.{ExtendedTable => Table}
import org.scalaquery.session.Database.threadLocalSession
import org.scalaquery.session._
import org.scalaquery.ql._
import org.scalaquery.ql.TypeMapper._

object Sqtest extends App {

val Persons = new Table[(Int, String, Option[Int])]("PERSONS") {
def id = column[Int]("ID", O.PrimaryKey)
def name = column[String]("NAME", O.NotNull)
def year = column[Option[Int]]("YEAR")

def * = id ~ name ~ year
}

Database.forURL("jdbc:hsqldb:mem:testdb").withSession {
Persons.ddl.create
Persons.insertAll(
(0, "Alice", Some(2000)),
(1, "Bob", None))

val q = for {
p <- Persons
val yearSum = ( for { q <- Persons } yield q.year.sum).asColumn
} yield p.* ~ yearSum ~ yearSum

println(q.selectStatement)
q.foreach(println)
}
```

This gives:

SELECT "t1"."ID","t1"."NAME","t1"."YEAR",(SELECT sum("t2"."YEAR")),(SELECT sum("t2"."YEAR") FROM "PERSONS" "t2") FROM "PERSONS" "t1"
Exception in thread "main" java.sql.SQLSyntaxErrorException: unexpected token: SELECT
[...]

In the first subquery the FROM clause is missing.