Incorrect SQL generated

[mariusmuja]

The following code:

    case class Person(id: Int, name: String, age: Int)
    case class Contact(id: Int, personId: Int, phone: String)

    val q = quote {
      (for {
        p <- query[Person] if(p.id == 999)
        c <- query[Contact] if(c.personId == p.id)
      } yield c).take(10)
    }

    db.run(q)

generates this SQL query:

SELECT c.id, c.person_id, c.phone FROM (SELECT id, personId, phone FROM person p, contact c WHERE (p.id = 999) AND (c.person_id = p.id)) c LIMIT 10

which contains two errors:

• the id column in the inner select is ambiguous
• the personId column in the inner select should be person_id (using SnakeCase)

[fwbrasil]

The fix for this issue is also available. Please let me know if it works with 0.1.4-SNAPSHOT.

[mariusmuja]

Confirming that the fix works in 0.1.4-SNAPSHOT, thanks.

[mariusmuja]

I noticed another issue: if a single field is selected, the query is not correct:

   case class Person(id: Int, name: String, age: Int)
    case class Contact(id: Int, personId: Int, phone: String)


    val q = quote {
      (for {
        p <- query[Person] if(p.id == 999)
        c <- query[Contact] if(c.personId == p.id)
      } yield c.id
      ).take(10)
    }

    db.run(q)

The generated query is:

SELECT c FROM (SELECT c.id FROM person p, contact c WHERE (p.id = 999) AND (c.person_id = p.id)) c LIMIT 10

The outer select should be c.id, not c.