Invalid query generation for Flat Joins with outer filter

[lebldavi]

Version: 4.6.0 Module: quill-jdbc-zio Database: postgres Scala version: 3.3.0, also present in Scala 2

Incorrectly generated query for Flat Joins with outer filter

object QuillContext extends PostgresZioJdbcContext(NamingStrategy(SnakeCase, PostgresEscape))
import QuillContext._

case class File(fileKey: Long, fileCategoryKey: Int)
case class FileCategory(fileCategoryKey: Int)

run {
    (for {
      f <- query[File]
      fc <- query[FileCategory].join(fc => fc.fileCategoryKey == f.fileCategoryKey)
    } yield (f.fileKey, fc.fileCategoryKey))
      .filter(_._1 == 1)          // FIXME - this is causing the problem
  }

Expected sql result

SELECT f."file_key" AS _1, fc."file_category_key" AS _2
FROM "file" f
  INNER JOIN "file_category" fc ON fc."file_category_key" = f."file_category_key"
WHERE fc."file_category_key" = f."file_category_key" AND f."file_key" = 1;

Actual sql result

See WHERE fc."_1" = 1; should be fc."file_category_key" ...

SELECT f."file_key" AS _1, fc."file_category_key" AS _2
FROM "file" f
  INNER JOIN "file_category" fc ON fc."file_category_key" = f."file_category_key"
WHERE fc."_1" = 1;

Steps to reproduce the behavior

https://scastie.scala-lang.org/FdasL5taSv63PcXy6um9EA

Workaround - correctly working for other join variations:

  /**
   * Implicit Joins:
   * 
   * SELECT f."file_key" AS _1, fc."file_category_key" AS _2
   * FROM "file" f, "file_category" fc
   * WHERE fc."file_category_key" = f."file_category_key" AND f."file_key" = 1;
   */
  def implicitJoin = run {
    (for {
      f <- query[File]
      fc <- query[FileCategory] if (fc.fileCategoryKey == f.fileCategoryKey)
    } yield (f.fileKey, fc.fileCategoryKey))
      .filter(_._1 == 1)
  }

  /**
   * Applicative Joins:
   * 
   * SELECT f."file_key" AS _1, fc."file_category_key" AS _2
   * FROM "file" f
   *  INNER JOIN "file_category" fc ON f."file_key" = fc."file_category_key"
   * WHERE f."file_key" = 1
   */
  def applicatioveJoin = run {
    query[File]
      .join(query[FileCategory]).on((f, fc) => f.fileKey == fc.fileCategoryKey)
      .map(a => (a._1.fileKey, a._2.fileCategoryKey))
      .filter(_._1 == 1)
  }

  /**
   * Filter inside for comprehension:
   * 
   * SELECT f."file_key" AS _1, fi."file_category_key" AS _2
   * FROM "file" f
   *  INNER JOIN "file_category" fi ON fi."file_category_key" = f."file_category_key"
   * WHERE f."file_key" = 1;
   */
  def insideFilter = run {
    (for {
      f <- query[File]
      fc <- query[FileCategory].join(fi => fi.fileCategoryKey == f.fileCategoryKey)
        .filter(_ => f.fileKey == 1)
    } yield (f.fileKey, fc.fileCategoryKey))
  }

  /**
   * Nested query:
   * 
   * SELECT x4._1, x4._2
   * FROM (SELECT f."file_key" AS _1, fc."file_category_key" AS _2
   *        FROM "file" f
   *        INNER JOIN "file_category" fc ON fc."file_category_key" = f."file_category_key") AS x4
   * WHERE x4._1 = 1;
   */
  def nestedQuery = run {
    (for {
      f <- query[File]
      fc <- query[FileCategory].join(fc => fc.fileCategoryKey == f.fileCategoryKey)
    } yield (f.fileKey, fc.fileCategoryKey))
      .nested
      .filter(_._1 == 1)
  }

@getquill/maintainers