Hi there, On Tue, Sep 8, 2020 at 6:15 PM Aner Perez <[email protected]> wrote:
> 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. > I can have a look if I know how to reproduce this. An MCVE would be helpful, we have a template here: https://github.com/jOOQ/jOOQ-mcve I was mostly focusing on the ORDER BY issue, which should be easiest to fix. Quite possibly, something else is tampering with the results here, which I'm overlooking > 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? > You can get different results for arbitrary reasons. See e.g. https://www.postgresql.org/docs/current/queries-order.html "If sorting is not chosen, the rows will be returned in an unspecified order." This topic is as old as SQL itself, and completely vendor agnostic. Modern SQL optimisation would not be possible if any accidental ordering could be dependable. If you don't specify ORDER BY, you're essentially telling the database, "you can do what you want, I don't care about the ordering". Sorting algorithms are O(N log N). It's reasonable for a database to avoid sorting if it is not required. You didn't require it. Think about a UNION operation. It can be implemented using hashmaps (more memory consumption, but O(N)) or sorting (possibly less memory consumption, but O(N log N)). If you don't specify ORDER BY, the hashmap is probably a better approach. Boom, your implicit, accidental ordering is gone. -- 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/CAB4ELO4Q22BjqOhHH%3D44wkVaq%2BSdjufTS6JFdDKmKd67S_%3DC3w%40mail.gmail.com.
