Egh...
Forget what I wrote. The exact moment I had sent the previous E-Mail, I
realised what the mistake was here:
AgmtCntrctV version = AGMT_CNTRCT_V.as("version");
Result<Record4<BigDecimal, BigDecimal, String,Timestamp>>
agmtContractLineRecords =
sql.select(AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_V.CNTRCT_REF,AGMT_CNTRCT_LINE.UPDATE_DATE)
.from(AGMT_CNTRCT_LINE)
.join(AGMT_CNTRCT_V)
.on(AGMT_CNTRCT_LINE.AGMT_CNTRCT_ID.equal(AGMT_CNTRCT_V.AGMT_CNTRCT_ID))
.where(AGMT_CNTRCT_V.EFFECTIVE_DATE.equals(sql.select(max(AGMT_CNTRCT_V.EFFECTIVE_DATE)).from(version).join(AGMT_CNTRCT_V).on(version.AGMT_CNTRCT_ID.eq(AGMT_CNTRCT_V.AGMT_CNTRCT_ID))))
.fetch();
You accidentally used Object.equals() rather than Field.equal(), which is
also why the 1 = 0 predicate is generated. You're simply passing the Java
boolean "false" to where(). This is accepted, because there is a
controversial where(Boolean) overload that swallows any warnings about
potentially bad API usage.
Others have run into this issue as well, which is why we deprecated these
overloads. In the latest jOOQ versions, you should see a deprecation
warning on the where() clause.
That was tough to spot! :) Hope it helps
Lukas
2016-05-26 7:41 GMT+02:00 Lukas Eder <[email protected]>:
> Hi Todd,
>
> 2016-05-26 4:52 GMT+02:00 Todd Costella <[email protected]>:
>
>> Hi Lukas,
>>
>> The output from these two JOOQ statements was captured using an
>> ExecuteListener inspired by the Custom Loggin ExecuteListener example found
>> at http://www.jooq.org/doc/3.8/manual/sql-execution/execute-listeners/.
>>
>> I haven't written a ton of JOOQ code (yet), but what I have written (and
>> logged) seems to be correctly rendered by my implementation of an
>> ExecuteListener. It's possible that this generated statement isn't actually
>> what JOOQ is generating, but I don't know of an other way of capturing that.
>>
>
> The easiest way to see what jOOQ is really doing is by turning on debug
> logging:
> http://www.jooq.org/doc/latest/manual/sql-execution/logging
>
> Behind the scenes, this activates the LoggerListener, so this will have
> the same effect as your approach
>
> I'm a bit confused by your comment about JOIN and no WHERE clause.
>>
>> Are you referring to the subquery not having a where clause? The outer
>> part of the query has both the join and the where.
>>
>
> Yes. You're expecting this:
>
> SELECT ACL.AGMT_CNTRCT_LINE_ID,
> ACL.AGMT_CNTRCT_ID,
> ACV.CNTRCT_REF
> FROM agmt_cntrct_line acl,
> agmt_cntrct_v acv
> WHERE ACL.AGMT_CNTRCT_ID = ACV.AGMT_CNTRCT_ID
> AND acv.effective_date =
> (SELECT MAX(acv.effective_date) FROM agmt_cntrct_v a WHERE
> a.AGMT_CNTRCT_ID = acv.agmt_cntrct_id)
>
> But you wrote this:
>
> AgmtCntrctV version = AGMT_CNTRCT_V.as("version");
> Result<Record4<BigDecimal, BigDecimal, String,Timestamp>>
> agmtContractLineRecords =
>
> sql.select(AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_LINE.AGMT_CNTRCT_LINE_ID,AGMT_CNTRCT_V.CNTRCT_REF,AGMT_CNTRCT_LINE.UPDATE_DATE)
> .from(AGMT_CNTRCT_LINE)
> .join(AGMT_CNTRCT_V)
>
> .on(AGMT_CNTRCT_LINE.AGMT_CNTRCT_ID.equal(AGMT_CNTRCT_V.AGMT_CNTRCT_ID))
>
> .where(AGMT_CNTRCT_V.EFFECTIVE_DATE.equals(sql.select(max(AGMT_CNTRCT_V.EFFECTIVE_DATE)).from(version).join(AGMT_CNTRCT_V).on(version.AGMT_CNTRCT_ID.eq(AGMT_CNTRCT_V.AGMT_CNTRCT_ID))))
> .fetch();
>
> So, you're expecting "WHERE" but you wrote "JOIN". Specifically, from what
> you posted so far, I have no explanation for the 1 = 0 predicate, which is
> why I asked whether you're sure that what you posted corresponds to what
> you executed... It looks like a debugging mistake to me.
>
> In any case, your API usage is correct, it's just not the query that you
> wanted to write...
>
--
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/d/optout.
