Hi Thomas, Thanks a lot for your report. I've created an issue for it here: https://github.com/jOOQ/jOOQ/issues/12455
The regression was probably caused by this fix here: https://github.com/jOOQ/jOOQ/issues/12210 I'm not sure if it is a bug or if your query worked by accident. The usage of implicit join paths within the FROM clause is not yet supported. At least, we don't have any integration tests for it yet. This isn't well documented, it's mentioned in a few issues "implicitly", e.g. here: https://github.com/jOOQ/jOOQ/issues/12037 I'm trying to understand the intent of a query where you use both implicit joins and the onKey() clause. I mean, it happened to work this way for you, but what was the rationale to combine the two? If we ever support implicit join paths in FROM (and hopefully, we'll do), then the ON clause seems mostly redundant and should be optional. A typical approach would be to just list multiple tables in FROM, or use CROSS JOIN, or perhaps a new IMPLICIT JOIN. There's no need to re-specify "ON KEY" because that's already being done by the implicit join, implicitly. So, what I'm wondering here is if you're adding your paths redundantly, should we perhaps avoid re-adding them implicitly, which seems to have worked by accident before and stopped working because of the aliasing fix #12210. In any case, you should settle for only one type of join, then your query would better be written as such: ctx.select(targetAts.UID, targetAts.ID) .from(push_run_match) .where( sourceAts.TYPE.eq(atsType), targetAts.TYPE.eq(atsType), push_run_match.STATUS.eq(AmPushRunMatchStatus.NEW) ) .groupBy(targetAts.UID) .orderBy(targetAts.UID) .limit(10) .fetch(record -> new AtsRef(atsType, record.get(targetAts.UID), record.get(targetAts.ID))); Note there seems to be another issue with resolving paths this way, related to aliasing. If you remove all the aliases, then this warning will go away: 09:24:12,929 INFO [org.jooq.impl.FieldsImpl ] - Ambiguous match found for uid. Both "am"."uid" and "source_ats"."uid" match. java.sql.SQLWarning: null at org.jooq.impl.FieldsImpl.field0(FieldsImpl.java:274) [jooq-3.15.3.jar:?] at org.jooq.impl.FieldsImpl.field(FieldsImpl.java:213) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractRow.field(AbstractRow.java:238) [jooq-3.15.3.jar:?] at org.jooq.impl.FieldsTrait.field(FieldsTrait.java:67) [jooq-3.15.3.jar:?] at org.jooq.impl.JoinTable.onKey(JoinTable.java:748) [jooq-3.15.3.jar:?] at org.jooq.impl.JoinTable.onKey(JoinTable.java:732) [jooq-3.15.3.jar:?] at org.jooq.impl.JoinTable.onKey(JoinTable.java:148) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractContext$JoinNode.joinTree(AbstractContext.java:1049) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractContext$JoinNode.joinTree(AbstractContext.java:1049) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractContext$JoinNode.joinTree(AbstractContext.java:1049) [jooq-3.15.3.jar:?] at org.jooq.impl.DefaultRenderContext.scopeEnd0(DefaultRenderContext.java:306) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractContext.scopeEnd(AbstractContext.java:739) [jooq-3.15.3.jar:?] at org.jooq.impl.SelectQueryImpl.accept0(SelectQueryImpl.java:1850) [jooq-3.15.3.jar:?] at org.jooq.impl.SelectQueryImpl.accept(SelectQueryImpl.java:1435) [jooq-3.15.3.jar:?] at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:720) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractContext.visit(AbstractContext.java:296) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractQuery.getSQL0(AbstractQuery.java:469) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:287) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:295) [jooq-3.15.3.jar:?] at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:316) [jooq-3.15.3.jar:?] at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:2866) [jooq-3.15.3.jar:?] at org.jooq.impl.ResultQueryTrait.collect(ResultQueryTrait.java:357) [jooq-3.15.3.jar:?] at org.jooq.impl.ResultQueryTrait.fetch(ResultQueryTrait.java:1454) [jooq-3.15.3.jar:?] at org.jooq.example.test.containers.TestContainersTest.testMultisetMappingIntoJavaRecords(TestContainersTest.java:75) [test-classes/:?] That's definitely a bug, which I'll investigate separately: https://github.com/jOOQ/jOOQ/issues/12456 On Mon, Sep 20, 2021 at 8:52 AM Thomas Matthijs <[email protected]> wrote: > Hello, > > I updated to 3.15.3 from 3.15.2 and one of my queries started failing, > reproducible example here: > https://github.com/selckin/jooq-testcontainers-example > You can see both SQL generated and error for the 3.15.3 and the link > to the jooq query in java > > Anyone who knows if i'm doing something wrong in this query or what > the cause might be? Or what the correct method would be? > > Thanks > > -- > 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/CABY_-Z48MwB2UV9_8V1%2B3AO1GcAcNwKisxya9_MqXQEy6gbQBg%40mail.gmail.com > . > -- 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/CAB4ELO4HhFBfg_%3DU4ySdZ8k3FYPLzZmSqfP8Ktzq6XX2gwm8cA%40mail.gmail.com.
