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);
}
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 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!
On Friday, September 4, 2020 at 3:48:48 AM UTC-4 [email protected] wrote:
> 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/88dd825e-d9ae-4512-941e-2cc599f82705n%40googlegroups.com.
