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/CAB4ELO49WCg-F8pUc-Ea_sJYs2v5oCiKqtVEKfEN1WYaK8-brg%40mail.gmail.com.
