Problem with having count(distinct ...)

[nickgieschen]

Describe the bug

Runs successfully on PG

Error: 🔨 Not supported 🔨 : The query you ran generated an AST which parts have not been read by the query planner. This means that those parts could be ignored:

    ⇨ .having.type ("binary")
    ⇨ .having.left.type ("call")
    ⇨ .having.left.function.name ("count")
    ⇨ .having.left.function ({"name":"count"})
    ⇨ .having.left.distinct ("distinct")
    ⇨ .having.left.args[0].type ("ref")
    ⇨ .having.left.args[0].table.name ("TrackGenres")
    ⇨ .having.left.args[0].table ({"name":"TrackGenres"})
    ⇨ .having.left.args[0].name ("genreId")
    ⇨ .having.left.args ([{"type":"ref","table":{"name":"TrackGenres"},"name":"genreId"}])
    ⇨ .having.left ({"type":"call","function":{"name":"count"},"distinct":"distinct","args":[{"type":"ref","table":{"name":"TrackGenres"},"name":"genreId"}]})
    ⇨ .having.right.type ("integer")
    ⇨ .having.right.value (1)
    ⇨ .having.right ({"type":"integer","value":1})
    ⇨ .having.op ("=")
    ⇨ .having ({"type":"binary","left":{"type":"call","function":{"name":"count"},"distinct":"distinct","args":[{"type":"ref","table":{"name":"TrackGenres"},"name":"genreId"}]},"right":{"type":"integer","value":1},"op":"="})

👉 pg-mem is work-in-progress, and it would seem that you've hit one of its limits.

*️⃣ Failed SQL statement: select distinct on ("position", "trackId") "Tracks"."id" as "trackId", "Tracks"."title", "Tracks"."duration", "Tracks"."bpm", "PlaylistTracks"."trackId" as "playlistTrackId", "PlaylistTracks"."position" as "position", coalesce(json_agg(json_build_object('id', "Composers"."id", 'name', "Composers"."name"))) as "composers" from "Tracks" left join "PlaylistTracks" on "Tracks"."id" = "PlaylistTracks"."trackId" left join "TrackComposers" on "Tracks"."id" = "TrackComposers"."trackId" left join "Composers" on "TrackComposers"."composerId" = "Composers"."id" inner join "TrackGenres" on "Tracks"."id" = "TrackGenres"."trackId" inner join "Genres" on "TrackGenres"."genreId" = "Genres"."id" where "Tracks"."isMain" is true and ("Genres"."id" in ('1000') or "Genres"."parentId" in ('1000')) group by "Tracks"."id", "playlistTrackId", "PlaylistTracks"."position" having count(distinct "TrackGenres"."genreId") = 1 order by "position" asc, "trackId";

👉 You can file an issue at https://github.com/oguimbal/pg-mem along with a way to reproduce this error (if you can), and  the stacktrace:


    at StatementExec.checkAstCoverage (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/execution/statement-exec.ts:171:31)
    at /Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/execution/statement-exec.ts:217:34
    at pushExecutionCtx (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/utils.ts:404:16)
    at /Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/execution/statement-exec.ts:196:54
    at StatementExec.niceErrors (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/execution/statement-exec.ts:226:20)
    at StatementExec.executeStatement (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/execution/statement-exec.ts:196:21)
    at Bound.doExecute (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/schema/prepared.ts:248:25)
    at doExecute.next (<anonymous>)
    at Bound.executeAll (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/schema/prepared.ts:226:30)
    at DbSchema.query (/Users/nick/dev/my-projects/soundmarket/node_modules/pg-mem/src/schema/schema.ts:65:14)

To Reproduce

Sql is above

pg-mem version

3.0.2