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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/9961bae5-6a9f-419a-b1da-1716f336bdc5n%40googlegroups.com.
