Generated wrong query with recursive reference.

[zhiwilliam]

This template isn't a strict requirement to open issues, but please try to provide as much information as possible.

Version: 4.6.0.1
Module: quill-jdbc
Database: postgresql

Expected behavior

Find who creates new sales records (properly query and returns result back).

Actual behavior

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column x3.total does not exist
Suggestion：Perhaps you meant to reference the column "x3._2total".
Position：335

Steps to reproduce the behavior

CREATE TABLE IF NOT EXISTS public.sale
(
    id integer,
    amount numeric,
    "time" timestamp with time zone
)

// quill code:

import io.getquill.*
import io.getquill.generic.*

import java.sql.Timestamp
import java.time.LocalDate

object More {
  case class Sale(id: Int, amount: BigDecimal, time: Timestamp)
  case class DailySale(id: Int, total: BigDecimal, date: LocalDate)

  def main(args: Array[String]): Unit = {
    //val ctx = new SqlMirrorContext(PostgresDialect, SnakeCase)
    val ctx = new PostgresJdbcContext(SnakeCase, "myDatabaseConfig")
    import ctx._

    extension (time: Timestamp)
      inline def toDate: LocalDate = quote(sql"DATE($time)".as[LocalDate])

    extension (date: LocalDate)
      inline def <(date2: LocalDate) = quote(sql"""$date < $date2""".as[Boolean])

    extension[A] (value: A)
      inline def rankByPartition[B](p: B): Long = quote {
        sql"""RANK () OVER (PARTITION BY $p ORDER BY $value DESC)""".as[Long]
      }

    extension[T] (query: Query[T]) {
      inline def maxByPartition[A, B](max: T => A, by: T => B): Quoted[Query[T]] = {
        //quote{
          query.map(t => (max(t).rankByPartition(by(t)), t)).filter(_._1 == 1L).map(_._2)
        //}
      }
    }

    inline def getDailySale = quote {
      query[Sale].groupByMap(x => (x.id, x.time.toDate))(x => DailySale(x.id, sum(x.amount), x.time.toDate))
    }

    inline def findBestSalesForeachDay = quote {
      getDailySale.maxByPartition(_.total, _.date)
    }

    inline def findWhoCreatesNewRecords = quote {
      findBestSalesForeachDay.filter(x =>
        findBestSalesForeachDay.filter(y => y.date < x.date).map(_.total).max.map(x.total > _).getOrElse(false))
    }
    //val result = run(findBestSalesForeachDay) This one works. but the next query failed.
    val result = run(findWhoCreatesNewRecords)
    println(result)
  }
}

So until find best sales for each day, it still works fine. But when I tried to find who create new sales record, it failed. the generate SQL listed below:

Quill Query: SELECT x._2id AS id, x._2total AS total, x._2date AS date FROM (SELECT RANK () OVER (PARTITION BY x.date ORDER BY x.total DESC) AS _1, x.id AS _2id, x.total AS _2total, x.date AS _2date FROM (SELECT x.id, SUM(x.amount) AS total, DATE(x.time) AS date FROM sale x GROUP BY x.id, DATE(x.time)) AS x) AS x WHERE x._1 = 1 AND ((SELECT MAX(x3.total) FROM (SELECT RANK () OVER (PARTITION BY x3.date ORDER BY x3.total DESC) AS _1, x3.id AS _2id, x3.total AS _2total, x3.date AS _2date FROM (SELECT x3.id, SUM(x3.amount) AS total, DATE(x3.time) AS date FROM sale x3 GROUP BY x3.id, DATE(x3.time)) AS x3) AS x3 WHERE x3._1 = 1 AND x3.date < x._2date) IS NOT NULL AND x._2total > (SELECT MAX(x4.total) FROM (SELECT RANK () OVER (PARTITION BY x4.date ORDER BY x4.total DESC) AS _1, x4.id AS _2id, x4.total AS _2total, x4.date AS _2date FROM (SELECT x4.id, SUM(x4.amount) AS total, DATE(x4.time) AS date FROM sale x4 GROUP BY x4.id, DATE(x4.time)) AS x4) AS x4 WHERE x4._1 = 1 AND x4.date < x._2date) OR (SELECT MAX(x6.total) FROM (SELECT RANK () OVER (PARTITION BY x6.date ORDER BY x6.total DESC) AS _1, x6.id AS _2id, x6.total AS _2total, x6.date AS _2date FROM (SELECT x6.id, SUM(x6.amount) AS total, DATE(x6.time) AS date FROM sale x6 GROUP BY x6.id, DATE(x6.time)) AS x6) AS x6 WHERE x6._1 = 1 AND x6.date < x._2date) IS NULL AND false)

Workaround

N/A

@getquill/maintainers