On Thursday, 28 September 2023 at 14:03:32 UTC+2 Lukas Eder wrote: Hi Yafl,
The UNION ALL operator looks as though it belongs to the SELECT statement, and jOOQ certainly makes it look so as well. But it doesn't really. It creates a new kind of statement that wraps 2 subqueries. Think of it this way: UnionAll(Select1, Select2) Ah, that makes sense! If I think about it that way, I also see more clearly why the aliases of the selects cannot be referenced anymore – because they are defined in an inner scope, whereas ORDER BY sits in the outer scope. I don't know if that's what actually happens (see below too), because I learned now that SQL can be very involved when it comes to (logical) execution order and what's accessible when, but it does help to think about it this way. :) Now that query continues to project things, and by convention (there are exceptions, e.g. this bug in Oracle: https://stackoverflow.com/q/25387951/521799), the names of the projected columns corresponds to the (unqualified!) column names from the first subquery. Now, some RDBMS allow for referencing those columns by name in the UNION's ORDER BY clause, others don't. jOOQ has a few open issues in this area. Some things may be expected to work (e.g. ORDER BY <column index>). In your case, I don't think jOOQ should do any magic such as unnesting the nested column again, just because it happens to be there. You could have any other column called "first_name", which is something entirely different (e.g. any expression) and then why would jOOQ prefer to unnest the nested column. I agree, I didn't expect jOOQ to implement magic here. I merely posted it to gain a better understanding of what's happening. In SQL, the column that you've nested ceases to exist outside of the UNION operator because you've nested it. This is different without a UNION (and without DISTINCT), because the column names that are not projected with SELECT may still be accessed by ORDER BY in most "reasonable" dialects. Though again, there might be exceptions, e.g.: https://github.com/trinodb/trino/issues/18726 I mentioned DISTINCT, because that creates a similar situation like UNION. I've described in in this blog post: https://blog.jooq.org/how-sql-distinct-and-order-by-are-related/ Ah, so is this the reason why the version without UNION works? Because it is accessing the table name and not the alias? Here my thinking was wrong. Good to know! In fact, separating the names of different things, I can validate this – the following doesn't work: select row (p.id, p.first_name, p.last_name, ...) as a, book.title from author as p join book on p.id = book.author_id where ... order by a.first_name whereas this does: select row (p.id, p.first_name, p.last_name, ...) as a, book.title from author as p join book on p.id = book.author_id where ... order by p.first_name The first one gives a «ERROR: Missing FROM-clause entry for table "a"». Is that because table aliases need to be defined in the from clause always? Because in the logical execution order, ORDER BY comes after SELECT, so I could imagine that it could theoretically access an alias defined in the SELECT statement. On the other hand, the entries in the column "a" are just strings of the form "(value1,value2,...)", not actual tables, so there's no way to access a column in there. If it was a table, it might be possible. You showed the difference it very well in this blog post with the DBeaver screenshots: https://blog.jooq.org/projecting-type-safe-nested-tablerecords-with-jooq-3-17/ So would the query work with a nested table in the select? Or would it still not work, because ORDER BY cannot access aliases defined in SELECT? I wanted to test this, but I cannot reproduce the nested result anymore, even with postgres in DBeaver (as described in the mentioned blog post). It always gives me rows, also with just plain simple "SELECT Table FROM Table". Don't know why. I'm pretty sure it worked at least once before. Although I read multiple times on your blog about the logical execution order of the SQL clauses and the subtleties of SQL, the order in which SQL is written still confuses me and I somehow expect references from the select to be accessible elsewhere. I'm thinking about starting to rewrite my SQL statement in the logical execution order to spot errors more easily when an error occurs. But then still, if aliases from the SELECT clause are not accessible in the ORDER BY clause, the execution order cannot be the reason for that. Sigh. -- 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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/a3ee156a-6518-41ed-b6fd-d2c56a0a908en%40googlegroups.com.
