Generated query checks equality on wrong table

[jdsalchow]

Version: I've tried 4.0.0 - 4.6.0 Module: protoquill Database: I've tried postgres and h2

Expected behavior

Given

case class Foo(id: Int, someField: String)
case class Bar(fooId: Int, otherField: String)

I expect

  run(
    (for {
      foo <- query[Foo]
      bar <- query[Bar].join(bar => bar.fooId == foo.id)
    } yield (foo, bar))
      .filter { case (foo, bar) =>
        foo.someField == "baz" && bar.otherField == "boo"
      }
  )

to generate

SELECT foo.id, foo.some_field AS someField, bar.foo_id AS fooId, bar.other_field AS otherField
FROM foo foo
         INNER JOIN bar bar ON bar.foo_id = foo.id
WHERE foo.some_field = 'baz'
  AND bar.other_field = 'boo'

Actual behavior

Instead of foo.some_field = 'baz' the first term in the where clause is bar.some_field = 'baz', i.e. the generated sql is

SELECT foo.id, foo.some_field AS someField, bar.foo_id AS fooId, bar.other_field AS otherField
FROM foo foo
         INNER JOIN bar bar ON bar.foo_id = foo.id
WHERE bar.some_field = 'baz'
  AND bar.other_field = 'boo'

Steps to reproduce the behavior

See https://scastie.scala-lang.org/zYJwK8KoSH2hxV0tmTHLLQ as an example.

Workaround

Rewrite as

  run(
    query[Foo]
      .join(query[Bar])
      .on((foo, bar) => bar.fooId == foo.id)
      .filter { case (foo, bar) =>
        foo.someField == "baz" && bar.otherField == "boo"
      }
  )

@getquill/maintainers

[joelsonoda]

@jdsalchow, this appears to also exist in zio-quill and sounds like it could be related to https://github.com/zio/zio-quill/issues/2671