Thanks, for the detailed & quick response
Guess the reason for using both is mostly that I didn't know i was :)
For example you start off with something like
ctx.select(*)
.from(Tables.FOO)
.leftOuterJoin(Tables.BAR).onKey(Tables.FOO.bar_id.eq(Tables.BAR.id)
Then you learn the code generation knows about your foreign keys
ctx.select(*)
.from(Tables.FOO)
.leftOuterJoin(Tables.BAR).onKey()
Then as queries get more complex, you start having trouble with how
are all the tables with absolute references are connected/related to
each other and if the query is correct or not,
and you notice the Foo.bar() methods
ctx.select(*)
.from(Tables.FOO)
.leftOuterJoin(Tables.FOO.bar()).onKey()
has to be correct compiler verified!
Then you look at some sql logging from the database and can't read
anything, so you include some .as() aliases and everything is pretty
(both generated sql and explicit in code what it does)
Foo foo = Tables.FOO.as("foo");
Foo bar = foo.bar().as("bar");
ctx.select(*)
.from(foo)
.leftOuterJoin(bar).onKey()
(And As i understand now possibly completely wrong usage of jooq?)
(You need the onKey for things to compile)
So more correct usage would be like
Foo foo = Tables.FOO.as("foo");
Foo bar = foo.bar().as("bar");
ctx.select(*)
.from(foo, bar)
.helloworld()
or just not reference any joins and let jooq figure it out? I'll have
to do some more playing around and reread some documentation
Can't do for example rightJoins then ?
Regards
On Mon, 20 Sept 2021 at 09:34, Lukas Eder <[email protected]> wrote:
>
> 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.
--
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_-Z47AvJKpibBGJ9KYH%3DwcJMJ0-5ZR6nB8diZgBY419HPJQ%40mail.gmail.com.
