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: https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/grouping-functions/ 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 On Fri, Sep 4, 2020 at 4:51 AM Aner Perez <[email protected]> wrote: > > 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 > <https://groups.google.com/d/msgid/jooq-user/b3093890-d325-4282-9f27-2512ed48c8f7n%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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/CAB4ELO5ye%2BjG-CRetd2jbhr0MGKWHeq%3DBX3rW1TwGeDm3gN0Bw%40mail.gmail.com.
