**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. On Tuesday, September 8, 2020 at 10:53:26 AM UTC-4 [email protected] wrote: > On Tue, Sep 8, 2020 at 4:26 PM Aner Perez <[email protected]> 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: > https://blog.jooq.org/2020/03/04/never-concatenate-strings-with-jooq/ > > If you must follow the approach of avoiding bind values, then you can wrap > your field and index using DSL.inline(): > > https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters/ > > >> 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" 😉 > -- 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/72bcde98-b36b-4bd5-b07f-48870196b7f7n%40googlegroups.com.
