Closed
Description
I'm upgrading from 3.19.10 to 3.19.15 and I face some issues with the use of order by inside multisets. The regression appeared in 3.19.11 and is still there in latest 3.19.15.
Expected behavior
Order by working in multiset sub query.
Generated query in v3.19.10 (working):
SELECT
"mcve"."products"."id",
(
SELECT coalesce(jsonb_agg(jsonb_build_array("v0", "v1", "v2")), jsonb_build_array())
FROM (
SELECT
"mcve"."photos"."id" AS "v0",
"mcve"."photos"."broadcast_type" AS "v1",
"mcve"."photos"."broadcast_order" AS "v2"
FROM "mcve"."photos"
WHERE "mcve"."photos"."product_id" = "mcve"."products"."id"
ORDER BY
CASE
WHEN "mcve"."photos"."broadcast_type" = 'PUBLIC' THEN 0
WHEN "mcve"."photos"."broadcast_type" = 'INTERNAL' THEN 1
ELSE 2
END ASC,
"mcve"."photos"."broadcast_order" ASC
) AS t
) AS "photos"
FROM "mcve"."products"
WHERE "mcve"."products"."id" = 'P001'Actual behavior
The query fails
ERROR: column "v1" does not exist
Generated query (failing):
SELECT
"mcve"."products"."id",
(
SELECT coalesce(jsonb_agg(jsonb_build_array("v0", "v1", "v2")), jsonb_build_array())
FROM (
SELECT
"mcve"."photos"."id" AS "v0",
"mcve"."photos"."broadcast_type" AS "v1",
"mcve"."photos"."broadcast_order" AS "v2"
FROM "mcve"."photos"
WHERE "mcve"."photos"."product_id" = "mcve"."products"."id"
ORDER BY
CASE
WHEN "v1" = 'PUBLIC' THEN 0
WHEN "v1" = 'INTERNAL' THEN 1
ELSE 2
END ASC,
"v2" ASC
) AS t
) AS "photos"
FROM "mcve"."products"
WHERE "mcve"."products"."id" = 'P001'Diff from working version:
14,15c14,15
< WHEN "mcve"."photos"."broadcast_type" = 'PUBLIC' THEN 0
< WHEN "mcve"."photos"."broadcast_type" = 'INTERNAL' THEN 1
---
> WHEN "v1" = 'PUBLIC' THEN 0
> WHEN "v1" = 'INTERNAL' THEN 1
18c18
< "mcve"."photos"."broadcast_order" ASC
---
> "v2" ASCSteps to reproduce the problem
I created a reproducer based on jOOQ-mcve here: https://github.com/jdussouillez/jOOQ-mcve/tree/multiset-order-by/jOOQ-mcve-java-postgres (:warning: use branch multiset-order-by).
mvn verify-> error 🔴- Change the version of jOOQ to
3.19.10and runmvn verifyagain -> OK ✔️
jOOQ Version
3.19.11..3.19.15
Database product and version
PostgreSQL 16
Java Version
openjdk version "21.0.4" 2024-07-16 LTS
JDBC / R2DBC driver name and version (include name if unofficial driver)
org.postgresql:postgresql:42.7.2