Invalid SQL when Nested after Query and Embedded is Used

[rodrigopalhares]

Quill generate a wrong query when referencing a EMBEDED entity in a NEST entity.

Version: 3.4.8
Module: quill-core
Database: postgres

case class MyEmb(name: String) extends Embedded
case class MyParent(myEmb: MyEmb)

query[MyParent]
  .nested
  .filter(_.myEmb.name == "bug")

Expected behavior

SELECT x1.my_embname FROM (SELECT x.name AS myEmbname FROM my_parents x) AS x1 WHERE x1.myEmbname = 'bug'

Actual behavior

SELECT x1.my_embname FROM (SELECT x.name AS myEmbname FROM my_parents x) AS x1 WHERE x1.name = 'bug'

Workaround

@getquill/maintainers

[deusaquilus]

Whoops, what I was saying before was wrong. This does seem to work for distinct though:

    val q = quote {
      query[MyParent]
        .distinct
        .filter(_.myEmb.name == "bug")
    }
// SELECT x1.myEmbname FROM (SELECT DISTINCT x1.name AS myEmbname FROM MyParent x1 WHERE x1.name = 'bug') AS x1

@rodrigopalhares
Do you have a reasonable workaround for now?
Also, I'm curious to know why the nested is needed in this case? Is there a top-level query that won't work without it?

[cemelo]

@deusaquilus currently, manually setting a SchemaMeta[MyParent] specifying the name of the column worked for us.

Regarding the use case, we have some complex sub queries (that use GROUP BY and window functions) that we need to nest in order to join the result with other tables.

[rodrigopalhares]

This is a simplified version of the real query.

Workaround add implicit schemaMeta for normal query:

implicit val parentSchema = schemaMeta[MyParent]("my_parents") 

run {
  query[MyParent]
    .nested
    .filter(_.myEmb.name == "bug")
}

Or alias to dynamic query:

run {
dynamicQuerySchema[MyParent]("my_parents",
  alias(_.myEmb.name, "name")
)
  .nested
  .filter(_.myEmb.name == "bug")
}

Live workaround

[deusaquilus]

OK. Good to hear. I have a fix for this, PR was opened this morning. This issue is actually deeper than I thought. Thanks for bringing it to my attention.

…

On Mon, Sep 23, 2019 at 8:55 AM Rodrigo Palhares ***@***.***> wrote: This simplified version of the real query. Workaround add implicit schemaMeta for normal query: implicit val parentSchema = schemaMeta[MyParent]("my_parents") run { query[MyParent] .nested .filter(_.myEmb.name == "bug") } Or alias to dynamic query: run { dynamicQuerySchema[MyParent]("my_parents", alias(_.myEmb.name, "name") ) .nested .filter(_.myEmb.name == "bug") } Live workaround <https://scastie.scala-lang.org/xhCv4rweRGS35KMNtLWY9w> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1632>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAKOLCFXRFMKKZOPZSO5F33QLC4DVANCNFSM4IYY46EA> .

[cemelo]

Thank you for looking into it so quickly. :-)