Different query results between jOOQ and psql
64 views
Skip to first unread message
Aner Perez
Sep 3, 2020, 10:51:43 PM9/3/20
to jOOQ User Group
I have a PostgreSQL query that I hand wrote and tested in psql. I then converted the query to the Java equivalent and after some tweaking verified that the generated SQL (using query.toString()) was the same.
If I run the generated query (from query.toString()) using psql or execute the query in the Netbeans UI, I get the correct results. If I run query.fetch().format() I get the same number of rows but a lot of the data is null and the rows are output in a different order. I have been using jOOQ for years and have never encountered anything like this and I don't know how to track down the issue. I tried with 3.12.4 and 3.13.4 with the same result.
Hopefully someone has run into something like this before and can point me in the right direction.
Basically the query is a recursive CTE to build an array that represents a reporting hierarchy and then this is referenced in a select to calculate sums and averages using a GROUP BY GROUPING SETS. The jOOQ output matched the psql output before I added org.path[1], org.path[2] and org.path[3] to the select and the grouping sets.
Everything below has been hand simplified so there may be typos (hopefully not). The query looks something like this:
WITH RECURSIVE "org"("path", "id", "username") as (
SELECT
array("user"."username"),
1,
"user"."id",
"app_user"."username"
FROM "user"
WHERE "user"."parent_user_id" is null
UNION ALL
SELECT
array_append( "org"."path", "user"."username" ),
"user"."id",
"user"."username",
FROM "org"
JOIN "user"
ON "user"."parent_user_id" = "org"."id"
)
SELECT
"org"."path"[1] as "level1",
"org"."path"[2] as "level2",
"org"."path"[3] as "level3",
"org"."path" as "worker",
"org"."id",
"org"."username",
"work"."type" as "work_type",
count(*) as "units",
(sum("work"."target") / count(*)) as "avg_target",
sum("work"."target") as "total_target"
FROM "org"
JOIN "work"
ON "work"."user_id" = "org"."id"
GROUP BY GROUPING SETS (
( "org"."path"[1], "org"."path"[2], "org"."path"[3],
"org"."path", "org"."id", "org"."username",
"work"."type" ),
( "org"."path"[1], "org"."path"[2], "org"."path"[3],
"org"."path", "org"."id", "org"."username" ),
( "org"."path"[1], "org"."path"[2], "org"."path"[3] ),
( "org"."path"[1], "org"."path"[2] ),
("org"."path"[1]),
()
)
SELECT
array("user"."username"),
1,
"user"."id",
"app_user"."username"
FROM "user"
WHERE "user"."parent_user_id" is null
UNION ALL
SELECT
array_append( "org"."path", "user"."username" ),
"user"."id",
"user"."username",
FROM "org"
JOIN "user"
ON "user"."parent_user_id" = "org"."id"
)
SELECT
"org"."path"[1] as "level1",
"org"."path"[2] as "level2",
"org"."path"[3] as "level3",
"org"."path" as "worker",
"org"."id",
"org"."username",
"work"."type" as "work_type",
count(*) as "units",
(sum("work"."target") / count(*)) as "avg_target",
sum("work"."target") as "total_target"
FROM "org"
JOIN "work"
ON "work"."user_id" = "org"."id"
GROUP BY GROUPING SETS (
( "org"."path"[1], "org"."path"[2], "org"."path"[3],
"org"."path", "org"."id", "org"."username",
"work"."type" ),
( "org"."path"[1], "org"."path"[2], "org"."path"[3],
"org"."path", "org"."id", "org"."username" ),
( "org"."path"[1], "org"."path"[2], "org"."path"[3] ),
( "org"."path"[1], "org"."path"[2] ),
("org"."path"[1]),
()
)
Output for psql looks like this:
┌────────┬────────┬─────────┬───────────────────────────────────────┬────┬──────────┬───────────┬───────┬────────────┬──────────────┐
│ level1 │ level2 │ level3 │ worker │ id │ username │ work_type │ units │ avg_target │ total_target │
├────────┼────────┼─────────┼───────────────────────────────────────┼────┼──────────┼───────────┼───────┼────────────┼──────────────┤
│ admin │ archer │ rbanks │ {"admin","archer","rbanks","rshaw"} │ 3 │ rshaw │ BAU │ 1 │ 300 │ 300 │
│ admin │ archer │ rbanks │ {"admin","archer","rbanks","rshaw"} │ 3 │ rshaw │ │ 1 │ 300 │ 300 │
│ admin │ archer │ rbanks │ │ │ │ │ 1 │ 300 │ 300 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ AC │ 9 │ 120 │ 1080 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ DU │ 2 │ 120 │ 240 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SRS │ 60 │ 60 │ 3600 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SC │ 6 │ 300 │ 1800 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SR2 │ 8 │ 300 │ 2400 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ │ 85 │ 107 │ 9120 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ AC │ 41 │ 120 │ 4920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ DU │ 16 │ 120 │ 1920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ TWU │ 4 │ 200 │ 800 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ BA │ 32 │ 60 │ 1920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SRS │ 5 │ 60 │ 300 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ GC │ 9 │ 300 │ 2700 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SC │ 7 │ 300 │ 2100 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SR2 │ 4 │ 300 │ 1200 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ │ 118 │ 134 │ 15860 │
│ admin │ archer │ lboyle │ │ │ │ │ 203 │ 123 │ 24980 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ AC │ 17 │ 120 │ 2040 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ DU │ 20 │ 120 │ 2400 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ EAA1 │ 11 │ 120 │ 1320 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ TWU │ 4 │ 200 │ 800 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ BA │ 13 │ 60 │ 780 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ BAU │ 5 │ 300 │ 1500 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SRS │ 5 │ 60 │ 300 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SC │ 8 │ 300 │ 2400 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SR2 │ 6 │ 300 │ 1800 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ │ 89 │ 149 │ 13340 │
│ admin │ archer │ pdown │ │ │ │ │ 89 │ 149 │ 13340 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ EAA1 │ 1 │ 120 │ 120 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ SC │ 3 │ 300 │ 900 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ │ 4 │ 255 │ 1020 │
│ admin │ archer │ jnasium │ │ │ │ │ 4 │ 255 │ 1020 │
│ admin │ archer │ │ │ │ │ │ 297 │ 133 │ 39640 │
│ admin │ │ │ │ │ │ │ 297 │ 133 │ 39640 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ DU │ 1 │ 120 │ 120 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ BAU │ 4 │ 300 │ 1200 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SRS │ 6 │ 60 │ 360 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SC │ 5 │ 300 │ 1500 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SR2 │ 2 │ 300 │ 600 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ │ 18 │ 210 │ 3780 │
│ aconda │ asmith │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ aconda │ asmith │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ aconda │ │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ │ │ │ │ │ │ │ 315 │ 137 │ 43420 │
└────────┴────────┴─────────┴───────────────────────────────────────┴────┴──────────┴───────────┴───────┴────────────┴──────────────┘
│ level1 │ level2 │ level3 │ worker │ id │ username │ work_type │ units │ avg_target │ total_target │
├────────┼────────┼─────────┼───────────────────────────────────────┼────┼──────────┼───────────┼───────┼────────────┼──────────────┤
│ admin │ archer │ rbanks │ {"admin","archer","rbanks","rshaw"} │ 3 │ rshaw │ BAU │ 1 │ 300 │ 300 │
│ admin │ archer │ rbanks │ {"admin","archer","rbanks","rshaw"} │ 3 │ rshaw │ │ 1 │ 300 │ 300 │
│ admin │ archer │ rbanks │ │ │ │ │ 1 │ 300 │ 300 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ AC │ 9 │ 120 │ 1080 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ DU │ 2 │ 120 │ 240 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SRS │ 60 │ 60 │ 3600 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SC │ 6 │ 300 │ 1800 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ SR2 │ 8 │ 300 │ 2400 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","adente"} │ 47 │ adente │ │ 85 │ 107 │ 9120 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ AC │ 41 │ 120 │ 4920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ DU │ 16 │ 120 │ 1920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ TWU │ 4 │ 200 │ 800 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ BA │ 32 │ 60 │ 1920 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SRS │ 5 │ 60 │ 300 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ GC │ 9 │ 300 │ 2700 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SC │ 7 │ 300 │ 2100 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ SR2 │ 4 │ 300 │ 1200 │
│ admin │ archer │ lboyle │ {"admin","archer","lboyle","afresco"} │ 48 │ afresco │ │ 118 │ 134 │ 15860 │
│ admin │ archer │ lboyle │ │ │ │ │ 203 │ 123 │ 24980 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ AC │ 17 │ 120 │ 2040 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ DU │ 20 │ 120 │ 2400 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ EAA1 │ 11 │ 120 │ 1320 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ TWU │ 4 │ 200 │ 800 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ BA │ 13 │ 60 │ 780 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ BAU │ 5 │ 300 │ 1500 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SRS │ 5 │ 60 │ 300 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SC │ 8 │ 300 │ 2400 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ SR2 │ 6 │ 300 │ 1800 │
│ admin │ archer │ pdown │ {"admin","archer","pdown"} │ 45 │ pdown │ │ 89 │ 149 │ 13340 │
│ admin │ archer │ pdown │ │ │ │ │ 89 │ 149 │ 13340 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ EAA1 │ 1 │ 120 │ 120 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ SC │ 3 │ 300 │ 900 │
│ admin │ archer │ jnasium │ {"admin","archer","jnasium"} │ 49 │ jnasium │ │ 4 │ 255 │ 1020 │
│ admin │ archer │ jnasium │ │ │ │ │ 4 │ 255 │ 1020 │
│ admin │ archer │ │ │ │ │ │ 297 │ 133 │ 39640 │
│ admin │ │ │ │ │ │ │ 297 │ 133 │ 39640 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ DU │ 1 │ 120 │ 120 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ BAU │ 4 │ 300 │ 1200 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SRS │ 6 │ 60 │ 360 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SC │ 5 │ 300 │ 1500 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ SR2 │ 2 │ 300 │ 600 │
│ aconda │ asmith │ │ {"aconda","asmith"} │ 30 │ asmith │ │ 18 │ 210 │ 3780 │
│ aconda │ asmith │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ aconda │ asmith │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ aconda │ │ │ │ │ │ │ 18 │ 210 │ 3780 │
│ │ │ │ │ │ │ │ 315 │ 137 │ 43420 │
└────────┴────────┴─────────┴───────────────────────────────────────┴────┴──────────┴───────────┴───────┴────────────┴──────────────┘
jOOQ output for the same data and query looks like this:
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
|level1 |level2 |level3 |worker | id|username|work_type|units|avg_target|total_target|
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 315| 137| 43420|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 297| 133| 39640|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 297| 133| 39640|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 1| 300| 300|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 89| 149| 13340|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 203| 123| 24980|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 4| 255| 1020|
|{null} |{null} |{null} |[admin, archer, lboyle, afresco] | 48|afresco |{null} | 118| 134| 15860|
|{null} |{null} |{null} |[aconda, asmith] | 30|asmith |{null} | 18| 210| 3780|
|{null} |{null} |{null} |[admin, archer, rbanks, rshaw] | 3|rshaw |{null} | 1| 300| 300|
|{null} |{null} |{null} |[admin, archer, pdown] | 45|pdown |{null} | 89| 149| 13340|
|{null} |{null} |{null} |[admin, archer, lboyle, adente] | 47|adente |{null} | 85| 107| 9120|
|{null} |{null} |{null} |[admin, archer, jnasium] | 49|jnasium |{null} | 4| 255| 1020|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SRS | 60| 60| 3600|
|admin |archer |rbanks |[admin, archer, rbanks, rshaw] | 3|rshaw |BAU | 1| 300| 300|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |BAU | 5| 300| 1500|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SC | 8| 300| 2400|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |AC | 9| 120| 1080|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SR2 | 8| 300| 2400|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SC | 6| 300| 1800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SR2 | 4| 300| 1200|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |AC | 17| 120| 2040|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |EAA1 | 11| 120| 1320|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |BA | 32| 60| 1920|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SRS | 5| 60| 300|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |DU | 16| 120| 1920|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SR2 | 6| 300| 1800|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |DU | 2| 120| 240|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SRS | 6| 60| 360|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |TWU | 4| 200| 800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SRS | 5| 60| 300|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |DU | 1| 120| 120|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |BAU | 4| 300| 1200|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |DU | 20| 120| 2400|
|admin |archer |jnasium |[admin, archer, jnasium] | 49|jnasium |SC | 3| 300| 900|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SR2 | 2| 300| 600|
|admin |archer |jnasium |[admin, archer, jnasium] | 49|jnasium |EAA1 | 1| 120| 120|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |TWU | 4| 200| 800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SC | 7| 300| 2100|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |AC | 41| 120| 4920|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |GC | 9| 300| 2700|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SC | 5| 300| 1500|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |BA | 13| 60| 780|
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
|level1 |level2 |level3 |worker | id|username|work_type|units|avg_target|total_target|
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 315| 137| 43420|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 297| 133| 39640|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 297| 133| 39640|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 18| 210| 3780|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 1| 300| 300|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 89| 149| 13340|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 203| 123| 24980|
|{null} |{null} |{null} |{null} |{null}|{null} |{null} | 4| 255| 1020|
|{null} |{null} |{null} |[admin, archer, lboyle, afresco] | 48|afresco |{null} | 118| 134| 15860|
|{null} |{null} |{null} |[aconda, asmith] | 30|asmith |{null} | 18| 210| 3780|
|{null} |{null} |{null} |[admin, archer, rbanks, rshaw] | 3|rshaw |{null} | 1| 300| 300|
|{null} |{null} |{null} |[admin, archer, pdown] | 45|pdown |{null} | 89| 149| 13340|
|{null} |{null} |{null} |[admin, archer, lboyle, adente] | 47|adente |{null} | 85| 107| 9120|
|{null} |{null} |{null} |[admin, archer, jnasium] | 49|jnasium |{null} | 4| 255| 1020|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SRS | 60| 60| 3600|
|admin |archer |rbanks |[admin, archer, rbanks, rshaw] | 3|rshaw |BAU | 1| 300| 300|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |BAU | 5| 300| 1500|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SC | 8| 300| 2400|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |AC | 9| 120| 1080|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SR2 | 8| 300| 2400|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |SC | 6| 300| 1800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SR2 | 4| 300| 1200|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |AC | 17| 120| 2040|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |EAA1 | 11| 120| 1320|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |BA | 32| 60| 1920|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SRS | 5| 60| 300|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |DU | 16| 120| 1920|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |SR2 | 6| 300| 1800|
|admin |archer |lboyle |[admin, archer, lboyle, adente] | 47|adente |DU | 2| 120| 240|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SRS | 6| 60| 360|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |TWU | 4| 200| 800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SRS | 5| 60| 300|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |DU | 1| 120| 120|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |BAU | 4| 300| 1200|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |DU | 20| 120| 2400|
|admin |archer |jnasium |[admin, archer, jnasium] | 49|jnasium |SC | 3| 300| 900|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SR2 | 2| 300| 600|
|admin |archer |jnasium |[admin, archer, jnasium] | 49|jnasium |EAA1 | 1| 120| 120|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |TWU | 4| 200| 800|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |SC | 7| 300| 2100|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |AC | 41| 120| 4920|
|admin |archer |lboyle |[admin, archer, lboyle, afresco] | 48|afresco |GC | 9| 300| 2700|
|aconda |asmith |{null} |[aconda, asmith] | 30|asmith |SC | 5| 300| 1500|
|admin |archer |pdown |[admin, archer, pdown] | 45|pdown |BA | 13| 60| 780|
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
Lukas Eder
Sep 4, 2020, 3:48:48 AM9/4/20
to jOOQ User Group
Hi Aner,
Your query doesn't specify an ORDER BY clause, so the database is free to produce any order it wants. If you call query.toString(), you'll get all your bind values inlined. If you run query.fetch(), then jOOQ will use a prepared statement with bind variables. That should be the main difference.
I'm not sure if you posted the entire result set in both cases, but the NULLs are a logical result when using GROUPING SETS. You get them for all groupings where a grouping column does not apply, see:
In order to get stable results, I suggest you use the GROUPING(<column>) function. For example:
select a, b, count(*), grouping(a) as grp_a, grouping(b) as grp_b
from (
values (1, 1), (1, 2), (2, 1), (2, 2)
) t (a, b)
group by grouping sets (
(a, b),
(a),
(b),
()
)
order by grp_a, grp_b
Producing
a|b|count|grp_a|grp_b|-|-|-----|-----|-----|2|2| 1| 0| 0|2|1| 1| 0| 0|1|2| 1| 0| 0|1|1| 1| 0| 0|2| | 2| 0| 1|1| | 2| 0| 1||2| 2| 1| 0||1| 2| 1| 0|| | 4| 1| 1|
I hope this helps
--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/b3093890-d325-4282-9f27-2512ed48c8f7n%40googlegroups.com.
Aner Perez
Sep 8, 2020, 10:26:40 AM9/8/20
to jOOQ User Group
As always, your comments are spot on.
I was aware that I had no ORDER BY but this was on purpose because I wanted the rolled up sums to be displayed under the group they were summarizing. The bind variables was the actual issue and it didn't even cross my mind that they could affect the query results (or that I was actually using bind variables anywhere in this query)!
I had to change my custom field function from this version that uses bind variables under the hood (from jOOQ issue #229)
public static <T> Field<T> arrayGet(Field<T[]> field, int index) {
return (Field) field("{0}[{1}]", field.getDataType().getType().getComponentType(), field, index);
}
return (Field) field("{0}[{1}]", field.getDataType().getType().getComponentType(), field, index);
}
to this which does not use bind variables:
public static <T> Field<T> arrayGet(Field<T[]> field, int index) {
return (Field) field(field.getQualifiedName()+"["+index+"]", field.getDataType().getType().getComponentType());
}
return (Field) field(field.getQualifiedName()+"["+index+"]", field.getDataType().getType().getComponentType());
}
I also didn't remember the existence of the grouping() function that can be used in the select. It's hard to internalize some of these things until you actually have a use for them. It'll come in useful in the near future.
Thanks again!
Lukas Eder
Sep 8, 2020, 10:53:26 AM9/8/20
to jOOQ User Group
On Tue, Sep 8, 2020 at 4:26 PM Aner Perez <aner....@gmail.com> wrote:
As always, your comments are spot on.I was aware that I had no ORDER BY but this was on purpose because I wanted the rolled up sums to be displayed under the group they were summarizing.
This is based on a wrong assumption. You should *never* assume any ordering, it may be completely arbitrary. Please use the approach that I've shown to keep control over the ordering.
The bind variables was the actual issue and it didn't even cross my mind that they could affect the query results (or that I was actually using bind variables anywhere in this query)!
Again, don't think of this as "bind values affecting the results" and thus "bind values are bad" or whatever. You didn't specify any ordering, so the ordering is *random* (while it may sometimes be what you expected, *by accident*)
I had to change my custom field function from this version that uses bind variables under the hood (from jOOQ issue #229)public static <T> Field<T> arrayGet(Field<T[]> field, int index) {
return (Field) field("{0}[{1}]", field.getDataType().getType().getComponentType(), field, index);
}to this which does not use bind variables:public static <T> Field<T> arrayGet(Field<T[]> field, int index) {
return (Field) field(field.getQualifiedName()+"["+index+"]", field.getDataType().getType().getComponentType());
}
I strongly recommend you revert this and apply an ORDER BY clause. Don't rely on such a funky implementation detail of a SQL execution planner. Your observed ordering is *still* accidental, and any other funky side-effect like the one you've observed here will "destroy" your expected ordering again, which you got only by accident, perhaps at midnight during your well-deserved vacation when that super urgent report is running, and you'll have to fix it.
Besides, please don't concatenate strings to produce identifiers through plain SQL API:
If you must follow the approach of avoiding bind values, then you can wrap your field and index using DSL.inline():
I also didn't remember the existence of the grouping() function that can be used in the select. It's hard to internalize some of these things until you actually have a use for them. It'll come in useful in the near future.
By "near", I'm sure, you mean "right now" 😉
Aner Perez
Sep 8, 2020, 12:15:40 PM9/8/20
to jOOQ User Group
**hangs head in shame** I wasn't really worried about the output sort order, only that the correct data was generated. I was going to tackle sorting after I got the correct results in the data but only if it looked wrong. Oops.
So what you're saying is that my change "fixed" the query but only by coincidence. The only part I don't understand is how the 2nd result set I provided in my initial post above is possible. The data I provided is complete with some simple hand editing that I triple checked (but not quadruple checked). How is it possible that in some result rows the worker column has an array of values in it but the level1 column which should be the first element of that array is null? Notice that both result sets have the same number of columns and the same the same numeric data albeit in a different order. It's just the level1, level2 and level3 columns that seem to have null in them for all groupings except for the first.
Even if I don't have a SORT BY, shouldn't the data generated be the same except for it being presented in a different order?
"right now" I'm wrapping my head around what I need to do to get the correct sort with grouping sets and the grouping() function.
Aner Perez
Sep 8, 2020, 4:41:00 PM9/8/20
to jOOQ User Group
I'm pretty sure this is a problem caused by how I'm using bind variables in the GROUP BY clause. I guess the database has no way of knowing which of the different path[?] in the query correlate to other path[?] in the SELECT/GROUPING SETS and it's doing the best it can with limited information. Adding a sort should/does not change the data, only the order of the rows. I don't know if it's a postgresql bug or if it's just misuse/abuse of bind variables but the following script replicates the issue using straight SQL in the psql tool. I think I can move this to a PostgreSQL forum since it doesn't seem to be jOOQ related.
Thanks again for providing the insight into the difference between the query executed using fetch() and the inlined query I was looking at. Saved me from going to plan B and doing it in Excel ;-)
CREATE TABLE bind_group_by (
path text [] PRIMARY KEY,
value int
);
INSERT INTO bind_group_by (path, value) VALUES
(ARRAY ['A1', 'B1', 'C1', 'D1'], 0),
(ARRAY ['A1', 'B1', 'C1', 'D2'], 1),
(ARRAY ['A1', 'B1', 'C1', 'D3'], 2),
(ARRAY ['A1', 'B1', 'C2', 'D1'], 3),
(ARRAY ['A1', 'B1', 'C2', 'D2'], 4),
(ARRAY ['A1', 'B1', 'C2', 'D3'], 5),
(ARRAY ['A1', 'B1', 'C3', 'D1'], 6),
(ARRAY ['A1', 'B1', 'C3', 'D2'], 7),
(ARRAY ['A1', 'B1', 'C3', 'D3'], 8),
(ARRAY ['A1', 'B2', 'C1', 'D1'], 9),
(ARRAY ['A1', 'B2', 'C1', 'D2'], 10),
(ARRAY ['A1', 'B2', 'C1', 'D3'], 11),
(ARRAY ['A1', 'B2', 'C2', 'D1'], 12),
(ARRAY ['A1', 'B2', 'C2', 'D2'], 13),
(ARRAY ['A1', 'B2', 'C2', 'D3'], 14),
(ARRAY ['A1', 'B2', 'C3', 'D1'], 15),
(ARRAY ['A1', 'B2', 'C3', 'D2'], 16),
(ARRAY ['A1', 'B2', 'C3', 'D3'], 17),
(ARRAY ['A1', 'B3', 'C1', 'D1'], 18),
(ARRAY ['A1', 'B3', 'C1', 'D2'], 19),
(ARRAY ['A1', 'B3', 'C1', 'D3'], 20),
(ARRAY ['A1', 'B3', 'C2', 'D1'], 21),
(ARRAY ['A1', 'B3', 'C2', 'D2'], 22),
(ARRAY ['A1', 'B3', 'C2', 'D3'], 23),
(ARRAY ['A1', 'B3', 'C3', 'D1'], 24),
(ARRAY ['A1', 'B3', 'C3', 'D2'], 25),
(ARRAY ['A1', 'B3', 'C3', 'D3'], 26),
(ARRAY ['A2', 'B1', 'C1', 'D1'], 27),
(ARRAY ['A2', 'B1', 'C1', 'D2'], 28),
(ARRAY ['A2', 'B1', 'C1', 'D3'], 29),
(ARRAY ['A2', 'B1', 'C2', 'D1'], 30),
(ARRAY ['A2', 'B1', 'C2', 'D2'], 31),
(ARRAY ['A2', 'B1', 'C2', 'D3'], 32),
(ARRAY ['A2', 'B1', 'C3', 'D1'], 33),
(ARRAY ['A2', 'B1', 'C3', 'D2'], 34),
(ARRAY ['A2', 'B1', 'C3', 'D3'], 35),
(ARRAY ['A2', 'B2', 'C1', 'D1'], 36),
(ARRAY ['A2', 'B2', 'C1', 'D2'], 37),
(ARRAY ['A2', 'B2', 'C1', 'D3'], 38),
(ARRAY ['A2', 'B2', 'C2', 'D1'], 39),
(ARRAY ['A2', 'B2', 'C2', 'D2'], 40),
(ARRAY ['A2', 'B2', 'C2', 'D3'], 41),
(ARRAY ['A2', 'B2', 'C3', 'D1'], 42),
(ARRAY ['A2', 'B2', 'C3', 'D2'], 43),
(ARRAY ['A2', 'B2', 'C3', 'D3'], 44),
(ARRAY ['A2', 'B3', 'C1', 'D1'], 45),
(ARRAY ['A2', 'B3', 'C1', 'D2'], 46),
(ARRAY ['A2', 'B3', 'C1', 'D3'], 47),
(ARRAY ['A2', 'B3', 'C2', 'D1'], 48),
(ARRAY ['A2', 'B3', 'C2', 'D2'], 49),
(ARRAY ['A2', 'B3', 'C2', 'D3'], 50),
(ARRAY ['A2', 'B3', 'C3', 'D1'], 51),
(ARRAY ['A2', 'B3', 'C3', 'D2'], 52),
(ARRAY ['A2', 'B3', 'C3', 'D3'], 53),
(ARRAY ['A3', 'B1', 'C1', 'D1'], 54),
(ARRAY ['A3', 'B1', 'C1', 'D2'], 55),
(ARRAY ['A3', 'B1', 'C1', 'D3'], 56),
(ARRAY ['A3', 'B1', 'C2', 'D1'], 57),
(ARRAY ['A3', 'B1', 'C2', 'D2'], 58),
(ARRAY ['A3', 'B1', 'C2', 'D3'], 59),
(ARRAY ['A3', 'B1', 'C3', 'D1'], 60),
(ARRAY ['A3', 'B1', 'C3', 'D2'], 61),
(ARRAY ['A3', 'B1', 'C3', 'D3'], 62),
(ARRAY ['A3', 'B2', 'C1', 'D1'], 63),
(ARRAY ['A3', 'B2', 'C1', 'D2'], 64),
(ARRAY ['A3', 'B2', 'C1', 'D3'], 65),
(ARRAY ['A3', 'B2', 'C2', 'D1'], 66),
(ARRAY ['A3', 'B2', 'C2', 'D2'], 67),
(ARRAY ['A3', 'B2', 'C2', 'D3'], 68),
(ARRAY ['A3', 'B2', 'C3', 'D1'], 69),
(ARRAY ['A3', 'B2', 'C3', 'D2'], 70),
(ARRAY ['A3', 'B2', 'C3', 'D3'], 71),
(ARRAY ['A3', 'B3', 'C1', 'D1'], 72),
(ARRAY ['A3', 'B3', 'C1', 'D2'], 73),
(ARRAY ['A3', 'B3', 'C1', 'D3'], 74),
(ARRAY ['A3', 'B3', 'C2', 'D1'], 75),
(ARRAY ['A3', 'B3', 'C2', 'D2'], 76),
(ARRAY ['A3', 'B3', 'C2', 'D3'], 77),
(ARRAY ['A3', 'B3', 'C3', 'D1'], 78),
(ARRAY ['A3', 'B3', 'C3', 'D2'], 79),
(ARRAY ['A3', 'B3', 'C3', 'D3'], 80);
SELECT 'static' AS query;
SELECT path[1], path[2], path[3], path, sum(value)
FROM bind_group_by
path text [] PRIMARY KEY,
value int
);
INSERT INTO bind_group_by (path, value) VALUES
(ARRAY ['A1', 'B1', 'C1', 'D1'], 0),
(ARRAY ['A1', 'B1', 'C1', 'D2'], 1),
(ARRAY ['A1', 'B1', 'C1', 'D3'], 2),
(ARRAY ['A1', 'B1', 'C2', 'D1'], 3),
(ARRAY ['A1', 'B1', 'C2', 'D2'], 4),
(ARRAY ['A1', 'B1', 'C2', 'D3'], 5),
(ARRAY ['A1', 'B1', 'C3', 'D1'], 6),
(ARRAY ['A1', 'B1', 'C3', 'D2'], 7),
(ARRAY ['A1', 'B1', 'C3', 'D3'], 8),
(ARRAY ['A1', 'B2', 'C1', 'D1'], 9),
(ARRAY ['A1', 'B2', 'C1', 'D2'], 10),
(ARRAY ['A1', 'B2', 'C1', 'D3'], 11),
(ARRAY ['A1', 'B2', 'C2', 'D1'], 12),
(ARRAY ['A1', 'B2', 'C2', 'D2'], 13),
(ARRAY ['A1', 'B2', 'C2', 'D3'], 14),
(ARRAY ['A1', 'B2', 'C3', 'D1'], 15),
(ARRAY ['A1', 'B2', 'C3', 'D2'], 16),
(ARRAY ['A1', 'B2', 'C3', 'D3'], 17),
(ARRAY ['A1', 'B3', 'C1', 'D1'], 18),
(ARRAY ['A1', 'B3', 'C1', 'D2'], 19),
(ARRAY ['A1', 'B3', 'C1', 'D3'], 20),
(ARRAY ['A1', 'B3', 'C2', 'D1'], 21),
(ARRAY ['A1', 'B3', 'C2', 'D2'], 22),
(ARRAY ['A1', 'B3', 'C2', 'D3'], 23),
(ARRAY ['A1', 'B3', 'C3', 'D1'], 24),
(ARRAY ['A1', 'B3', 'C3', 'D2'], 25),
(ARRAY ['A1', 'B3', 'C3', 'D3'], 26),
(ARRAY ['A2', 'B1', 'C1', 'D1'], 27),
(ARRAY ['A2', 'B1', 'C1', 'D2'], 28),
(ARRAY ['A2', 'B1', 'C1', 'D3'], 29),
(ARRAY ['A2', 'B1', 'C2', 'D1'], 30),
(ARRAY ['A2', 'B1', 'C2', 'D2'], 31),
(ARRAY ['A2', 'B1', 'C2', 'D3'], 32),
(ARRAY ['A2', 'B1', 'C3', 'D1'], 33),
(ARRAY ['A2', 'B1', 'C3', 'D2'], 34),
(ARRAY ['A2', 'B1', 'C3', 'D3'], 35),
(ARRAY ['A2', 'B2', 'C1', 'D1'], 36),
(ARRAY ['A2', 'B2', 'C1', 'D2'], 37),
(ARRAY ['A2', 'B2', 'C1', 'D3'], 38),
(ARRAY ['A2', 'B2', 'C2', 'D1'], 39),
(ARRAY ['A2', 'B2', 'C2', 'D2'], 40),
(ARRAY ['A2', 'B2', 'C2', 'D3'], 41),
(ARRAY ['A2', 'B2', 'C3', 'D1'], 42),
(ARRAY ['A2', 'B2', 'C3', 'D2'], 43),
(ARRAY ['A2', 'B2', 'C3', 'D3'], 44),
(ARRAY ['A2', 'B3', 'C1', 'D1'], 45),
(ARRAY ['A2', 'B3', 'C1', 'D2'], 46),
(ARRAY ['A2', 'B3', 'C1', 'D3'], 47),
(ARRAY ['A2', 'B3', 'C2', 'D1'], 48),
(ARRAY ['A2', 'B3', 'C2', 'D2'], 49),
(ARRAY ['A2', 'B3', 'C2', 'D3'], 50),
(ARRAY ['A2', 'B3', 'C3', 'D1'], 51),
(ARRAY ['A2', 'B3', 'C3', 'D2'], 52),
(ARRAY ['A2', 'B3', 'C3', 'D3'], 53),
(ARRAY ['A3', 'B1', 'C1', 'D1'], 54),
(ARRAY ['A3', 'B1', 'C1', 'D2'], 55),
(ARRAY ['A3', 'B1', 'C1', 'D3'], 56),
(ARRAY ['A3', 'B1', 'C2', 'D1'], 57),
(ARRAY ['A3', 'B1', 'C2', 'D2'], 58),
(ARRAY ['A3', 'B1', 'C2', 'D3'], 59),
(ARRAY ['A3', 'B1', 'C3', 'D1'], 60),
(ARRAY ['A3', 'B1', 'C3', 'D2'], 61),
(ARRAY ['A3', 'B1', 'C3', 'D3'], 62),
(ARRAY ['A3', 'B2', 'C1', 'D1'], 63),
(ARRAY ['A3', 'B2', 'C1', 'D2'], 64),
(ARRAY ['A3', 'B2', 'C1', 'D3'], 65),
(ARRAY ['A3', 'B2', 'C2', 'D1'], 66),
(ARRAY ['A3', 'B2', 'C2', 'D2'], 67),
(ARRAY ['A3', 'B2', 'C2', 'D3'], 68),
(ARRAY ['A3', 'B2', 'C3', 'D1'], 69),
(ARRAY ['A3', 'B2', 'C3', 'D2'], 70),
(ARRAY ['A3', 'B2', 'C3', 'D3'], 71),
(ARRAY ['A3', 'B3', 'C1', 'D1'], 72),
(ARRAY ['A3', 'B3', 'C1', 'D2'], 73),
(ARRAY ['A3', 'B3', 'C1', 'D3'], 74),
(ARRAY ['A3', 'B3', 'C2', 'D1'], 75),
(ARRAY ['A3', 'B3', 'C2', 'D2'], 76),
(ARRAY ['A3', 'B3', 'C2', 'D3'], 77),
(ARRAY ['A3', 'B3', 'C3', 'D1'], 78),
(ARRAY ['A3', 'B3', 'C3', 'D2'], 79),
(ARRAY ['A3', 'B3', 'C3', 'D3'], 80);
SELECT 'static' AS query;
SELECT path[1], path[2], path[3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[1], path[2], path[3], path),
(path[1], path[2], path[3]),
(path[1], path[2]),
(path[1]),
()
)
ORDER BY 1, 2, 3, 4
;
SELECT 'prepared' AS query;
PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, int, int, int) AS
SELECT path[$1], path[$2], path[$3], path, sum(value)
FROM bind_group_by
(path[1], path[2], path[3]),
(path[1], path[2]),
(path[1]),
()
)
ORDER BY 1, 2, 3, 4
;
SELECT 'prepared' AS query;
PREPARE prepared_group_by (int, int, int, int, int, int, int, int, int, int, int, int) AS
SELECT path[$1], path[$2], path[$3], path, sum(value)
FROM bind_group_by
GROUP BY GROUPING SETS (
(path[$4], path[$5], path[$6], path),
(path[$7], path[$8], path[$9]),
(path[$10], path[$11]),
(path[$12]),
()
)
ORDER BY 1, 2, 3, 4
;
EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1);
-- cleanup
DEALLOCATE prepared_group_by;
DROP TABLE bind_group_by;
(path[$7], path[$8], path[$9]),
(path[$10], path[$11]),
(path[$12]),
()
)
ORDER BY 1, 2, 3, 4
;
EXECUTE prepared_group_by (1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 1);
-- cleanup
DEALLOCATE prepared_group_by;
DROP TABLE bind_group_by;
Lukas Eder
Sep 9, 2020, 4:06:01 AM9/9/20
to jOOQ User Group
Hi there,
On Tue, Sep 8, 2020 at 6:15 PM Aner Perez <aner....@gmail.com> wrote:
So what you're saying is that my change "fixed" the query but only by coincidence. The only part I don't understand is how the 2nd result set I provided in my initial post above is possible. The data I provided is complete with some simple hand editing that I triple checked (but not quadruple checked). How is it possible that in some result rows the worker column has an array of values in it but the level1 column which should be the first element of that array is null? Notice that both result sets have the same number of columns and the same the same numeric data albeit in a different order. It's just the level1, level2 and level3 columns that seem to have null in them for all groupings except for the first.
I can have a look if I know how to reproduce this. An MCVE would be helpful, we have a template here: https://github.com/jOOQ/jOOQ-mcve
I was mostly focusing on the ORDER BY issue, which should be easiest to fix. Quite possibly, something else is tampering with the results here, which I'm overlooking
Even if I don't have a SORT BY, shouldn't the data generated be the same except for it being presented in a different order?
You can get different results for arbitrary reasons. See e.g.
"If sorting is not chosen, the rows will be returned in an unspecified order."
This topic is as old as SQL itself, and completely vendor agnostic. Modern SQL optimisation would not be possible if any accidental ordering could be dependable. If you don't specify ORDER BY, you're essentially telling the database, "you can do what you want, I don't care about the ordering". Sorting algorithms are O(N log N). It's reasonable for a database to avoid sorting if it is not required. You didn't require it.
Think about a UNION operation. It can be implemented using hashmaps (more memory consumption, but O(N)) or sorting (possibly less memory consumption, but O(N log N)). If you don't specify ORDER BY, the hashmap is probably a better approach. Boom, your implicit, accidental ordering is gone.
Lukas Eder
Sep 9, 2020, 4:07:51 AM9/9/20
to jOOQ User Group
On Wed, Sep 9, 2020 at 10:05 AM Lukas Eder <lukas...@gmail.com> wrote:
On Tue, Sep 8, 2020 at 6:15 PM Aner Perez <aner....@gmail.com> wrote:So what you're saying is that my change "fixed" the query but only by coincidence. The only part I don't understand is how the 2nd result set I provided in my initial post above is possible. The data I provided is complete with some simple hand editing that I triple checked (but not quadruple checked). How is it possible that in some result rows the worker column has an array of values in it but the level1 column which should be the first element of that array is null? Notice that both result sets have the same number of columns and the same the same numeric data albeit in a different order. It's just the level1, level2 and level3 columns that seem to have null in them for all groupings except for the first.I can have a look if I know how to reproduce this. An MCVE would be helpful, we have a template here: https://github.com/jOOQ/jOOQ-mcveI was mostly focusing on the ORDER BY issue, which should be easiest to fix. Quite possibly, something else is tampering with the results here, which I'm overlooking
OK, you figured it out in the next email already, great to hear! It does seem to be a bug in PostgreSQL when you show it like this.
Thanks for following up!
Reply all
Reply to author
Forward
0 new messages