Hi Brian and Tobias,
The behaviour is expected. There is actually no such thing as an "unknown"
alias for derived tables. For convenience, jOOQ auto-generates aliases if
they're missing in derived tables. The same query wouldn't run in MySQL
either:
select A.ID
from TABLE_A as A
join (
select B.ID from TABLE_B as B
where B.NAME like concat('%', ?, '%') escape '!'
) on B.ID = A.ID;
What you could do, however, is rename your derived table to "B" as such:
context.select(A.ID <http://a.id/>).from(A)
.join(
select(B.ID <http://b.id/>).from(B).where(B.
NAME.contains(text)).asTable("B")
).on(B.ID.eq(A.ID <http://a.id/>)).fetch();
That should then generate something more meaningful like this:
select A.ID
from TABLE_A as A
join (
select B.ID from TABLE_B as B
where B.NAME like concat('%', ?, '%') escape '!'
) as B on B.ID = A.ID;
Another option is to extract the ID field from the derived table:
Table<?> b = select(B.ID <http://b.id/>).from(B).where(B.
NAME.contains(text)).asTable();
context.select(A.ID <http://a.id/>).from(A)
.join(b).on(b.field("ID").eq(A.ID <http://a.id/>)).fetch();
In the above query, b.field("ID") would correspond to `alias_1234567`.`ID`
Hope this helps
Lukas
2013/11/19 <[email protected]>
> Hello. If i write a query with the following pattern using the MySQL
> dialect i get an unexpected result with JOOQ 3.2.0 and 3.1.0 (did not test
> older versions).
>
> TABLE_A A = TABLE_A.as("A");
> TABLE_B B = TABLE_B.as("B");
>
> context.select(A.ID).from(A)
> .join(
> select(B.ID).from(B).where(B.NAME.contains(text))
> ).on(B.ID.eq(A.ID)).fetch();
>
> What i get is the following:
>
> select `A`.`ID` from `TABLE_A` as `A`
> join (
> select `B`.`ID` from `TABLE_B` as `B` where `B`.`NAME` like concat('%',
> ?, '%') escape '!'
> ) as `alias_1234567` on `B`.`ID` = `A`.`ID`;
>
> Instead of the "unknown" alias `alias_1234567` i would expect `B`. Did i
> do something wrong or is this a bug in JOOQ?
>
> Greetings from Brian and Tobias
>
> --
> 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].
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
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].
For more options, visit https://groups.google.com/groups/opt_out.
