Flat Join produces invalid SQL

[chrismikehogan]

Version: (e.g. 3.16.5)
Module: (e.g. quill-jdbc)
Database: (e.g. postgres)

We found ourselves behind on quill versions and are working on upgrading. Before making the jump to 4.x, we pinned ourselves to (at the time) the highest 3.x. As this version as been adopted we found this bug. We are testing to see if it persists on 4.x and will update this issue when we find more.

We found that flat join code that produced valid SQL in 3.5.1, produced invalid SQL in 3.16.5. Depending on the schemas being joined this could produce syntactically correct but logically invalid SQL, leading to subtly wrong behavior.

Expected behavior

Quotes compile to the same SQL across versions.

Actual behavior

Quotes don't compile to the same SQL across versions.

Steps to reproduce the behavior

Given this example query:

def testQuery(id: UUID) = {
  run(quote {
    val joins = for {
      l <- listings
      pt <- property_types.join(_.id == l.propertyTypeId)
    } yield {
      (l, pt)
    }

    joins
      .filter { case (l, _) => l.id == lift(id) }
      .map { case (l, pt) => (l.id, pt.code)}
  })
}

Quill 3.5.1 produced this valid SQL:

SELECT l.id, x1.code 
FROM listings l 
INNER JOIN property_types x1 ON x1.id = l.property_type_id 
WHERE l.id = ?

But Quill 3.16.5 produces this SQL (note the aliases used in the WHERE and SELECT clauses):

SELECT x1.id AS _1, x1.code AS _2 
FROM listings l 
INNER JOIN property_types x1 ON x1.id = l.property_type_id 
WHERE x1.id = ?

Workaround

Modifying my flat-join code to this produces the logically correct SQL that matches the behavior, although not exactly the syntax, of the 3.5.1 SQL:

def testQuery(id: UUID) = {
  run(quote {
    for {
      l <- listings.filter(_.id == lift(id)) // move filter into for block
      pt <- property_types.join(_.id == l.propertyTypeId)
    } yield {
      (l.id, pt.code) // do mapping in the yield block
    }
  })
}

SELECT x1.id AS _1, x2.code AS _2 
FROM listings x1 
INNER JOIN property_types x2 ON x2.id = x1.property_type_id 
WHERE x1.id = ?

@getquill/maintainers