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]),
()
)
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 │
└────────┴────────┴─────────┴───────────────────────────────────────┴────┴──────────┴───────────┴───────┴────────────┴──────────────┘
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|
+-------+-------+--------+------------------------------------+------+--------+---------+-----+----------+------------+
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/b3093890-d325-4282-9f27-2512ed48c8f7n%40googlegroups.com.
