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) 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. 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/ I hope this helps Lukas On Thu, Sep 28, 2023 at 1:40 PM Yafl Wabei <yafl.wa...@gmail.com> wrote: > Hi, > > during experiments with Tables as SelectField (see > https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/select-clause/select-clause-table/ > for the manual reference and > https://groups.google.com/g/jooq-user/c/XMD2Rc0kEqQ for my experiments) I > ran into a problem when using it together with the unionAll and orderBy > clauses. I basically tried something like this: > > ctx.select(AUTHOR, BOOK.TITLE) > .from(AUTHOR) > .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) > .where(...) > .unionAll( > ctx.select(AUTHOR, BOOK.TITLE) > .from(AUTHOR) > .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) > .join(PUBLISHER).on(PUBLISHER.ID.eq(BOOK.PUBLISHER_ID)) > .where(...) > ) > .orderBy(AUTHOR.FIRST_NAME); > > This doesn't work. In Postgres, it produces an error like this: > > org.postgresql.util.PSQLException: ERROR: column "first_name" does not > exist > > I came up with a guess why it doesn't work, but I'd like to know if that's > correct and if someone has a more detailed explanation why it's not > working. So here's my analysis and the guess based on that: > > The SQL command created by the above jooq query looked sth like this: > > select > row (author.id, author.first_name, author.last_name, ...) as author, > book.title > from author join book on author.id = book.author_id > where ... > union all > select > row (author.id, author.first_name, author.last_name, ...) as author, > book.title > from author join book on author.id = book.author_id join publisher on > publisher.id = book.publisher_id > where ... > order by first_name > > If I remove the unionAll, but keep the orderBy clause, like so: > > ctx.select(AUTHOR, BOOK.TITLE) > .from(AUTHOR) > .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID)) > .where(...) > .orderBy(AUTHOR.FIRST_NAME); > > then it works! In this case the SQL looks like this: > > select > row (author.id, author.first_name, author.last_name, ...) as author, > book.title > from author join book on author.id = book.author_id > where ... > order by author.first_name > > In both cases (with or without unionAll), the author row gets the alias > "author" (same as the table name) by jooq, and here the "order by" also > references "author". When I use an explicit alias myself (like Table<?> a = > AUTHOR.as("a")), the results are similar, so that doesn't solve the > problem. I guess the reason is that with the unionAll Postgres produces > something like two temporary tables (in the above case they both have the > alias "author", but in general they might have different aliases), but then > to unite these tables, it needs to put them together in a new temporary > table, where the aliases of the single tables will be lost (because > Postgres couldn't use different aliases for different rows in the same > result table if the two tables had different aliases). I guess that's also > why there's no alias in the "order by", because with unionAll there's > nothing to reference, so the only possibility is probably to just put the > column name and hope that it's a column in the result table. In this case > it isn't, though. > > When using a select like > > ctx.select(AUTHOR.fields()) > .select(BOOK.TITLE) > > instead, it works, because then the "first_name" column is just there as a > plain column in the result table with the correct name. > > > So I guess to avoid this, I shouldn't use "Table as SelectField", in case > I'm also using unionAll and orderBy (or just not use it at all, if it's not > fitting the use case). Instead I could just put the fields I want to query > in the select. > > Are there other (maybe easier) variants how to make this query work? Or > ways to build it differently that make more sense? > > > Meta: Why am I posting this? > > As I came up with an explanation, what's the point in posting it here? I > have a few reasons: > > - My explanation is just a guess. If someone has a better or more > in-depth explanation of what's happening or why it doesn't work, I'll be > happy to hear about that! > - If my guesses are wrong, I'll be happy to receive corrections. > - I mentioned this in > https://groups.google.com/g/jooq-user/c/XMD2Rc0kEqQ and Lukas said I > should file a bug report on GitHub. But according to my guesses, it's not a > jooq bug – I'm just building a non correct SQL statement (with jooq). If my > guess is correct, jooq cannot fix this, so I'm not gonna file a bug report. > But I'd still like to receive feedback on my guess. > - Other people with the same problem can find it and save time. > > > > All comments appreciated! > Thanks! > Yafl > > -- > 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/9961bae5-6a9f-419a-b1da-1716f336bdc5n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/9961bae5-6a9f-419a-b1da-1716f336bdc5n%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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7bRHRHF8ow9UY5gZEBe4%2BukvspY2iL3E3NomznkQs6PA%40mail.gmail.com.
