"in"-operation vs. DSL.selectFrom incompatibly changed in 3.13?

[Thorsten Schöning]

Hi all,

I've upgraded to jOOQ 3.13 and the following conditions don't work
anymore:

> SelectConditionStep<Record3<String, String, Long>> where = joined
> .where(REAL_ESTATE.ID.in(DSL.selectFrom(RdQueryBuilder.CTE_NAME_RE_IDS))
> .and(METER.ID.in(DSL.selectFrom(RdQueryBuilder.CTE_NAME_METER_IDS))));

The error message:

> The method in(Collection<?>) in the type Field<Integer> is not
> applicable for the arguments (SelectWhereStep<Record>)

"Field" provides the following overload for "in":

> Condition in(Select<? extends Record1<T>> query);

That method expects exactly one column only, while "DSL.selectFrom"
can return more in theory. In practice it doesn't in my case and
things worked with 3.11.7 I used before.

I wasn't able to spot the difference yet, so is that change by purpose
or a mistake? What's the easiest workaround to get the former
behaviour back?

The problem is that what's referenced are dynamically created CTEs I
don't have columns names for like for other tables. That's why using
"selectFrom" was so easy.

Thanks for your hints!

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[Lukas Eder]

Hi Thorsten,

I'm not aware of an incompatible change in this area.

jOOQ 3.10 introduced a configuration <recordsImplementingRecordN/>

https://github.com/jOOQ/jOOQ/issues/6072  

This generates record types implementing e.g. Record1<Long>, as would be required in your API usage. By default, it is turned on. Maybe you have turned it off? Otherwise, what's the type of your RdQueryBuilder.CTE_NAME_RE_IDS?

Cheers,

Lukas

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1872157547.20200605220858%40am-soft.de.

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Samstag, 6. Juni 2020 um 10:28 schrieben Sie:

> jOOQ 3.10 introduced a configuration <recordsImplementingRecordN/>
> https://github.com/jOOQ/jOOQ/issues/6072

That option is enabled, explicitly disabling it results in different
results of the generator breaking things additionally.

> [...]Otherwise, what's the type of your RdQueryBuilder.CTE_NAME_RE_IDS?

It's really only a name:

> private static final Name CTE_NAME_RE_IDS = DSL.name("real_estate_ids");

The corresponding column, indentation is by me:

> public final TableField<RealEstateRecord, IntegerID = createField
> (
> DSL.name("id"),
> org.jooq.impl.SQLDataType.INTEGER.nullable(false).defaultValue
> (
> org.jooq.impl.DSL.field
> (
> "nextval('real_estate_id_seq'::regclass)",
> org.jooq.impl.SQLDataType.INTEGER
> )
> ),
> this,
> ""
> );

The overloads in "Field":

> Condition in(Collection<?> values);
> Condition in(Result<? extends Record1<T>> result);
> Condition in(T... values);
> Condition in(Field<?>... values);

> Condition in(Select<? extends Record1<T>> query);

The following works, but is obviously wrong:

> .where(REAL_ESTATE.ID.in((Field<?>) DSL.selectFrom(RdQueryBuilder.CTE_NAME_RE_IDS))

Every other cast seems to fail with Java trying to use the overload
for "in(Collection)". I really only want an "in(SELECT * FROM ...)"
and other more verbose statements don't work as well:

> .where(REAL_ESTATE.ID.in(DSL.select(DSL.asterisk()).from("").where(DSL.trueCondition()))
> .where(REAL_ESTATE.ID.in(this.getDbConn().getJooq().select(DSL.asterisk()).from("").where(DSL.trueCondition()))

Isn't that pretty much what the official docs have as example as well?

https://www.jooq.org/doc/3.13/manual/sql-building/table-expressions/nested-selects/

[Lukas Eder]

On Sat, Jun 6, 2020 at 3:18 PM Thorsten Schöning <tscho...@am-soft.de> wrote:

Guten Tag Lukas Eder,
am Samstag, 6. Juni 2020 um 10:28 schrieben Sie:

> jOOQ 3.10 introduced a configuration <recordsImplementingRecordN/>
> https://github.com/jOOQ/jOOQ/issues/6072

That option is enabled, explicitly disabling it results in different
results of the generator breaking things additionally.

Thanks for the feedback. I did think that this did not affect you - but I left it there so future visitors can see this hint to debug their own code.

 

> [...]Otherwise, what's the type of your RdQueryBuilder.CTE_NAME_RE_IDS?

It's really only a name:

> private static final Name CTE_NAME_RE_IDS = DSL.name("real_estate_ids");

So, the compiler is inferring Select<Record> for your selectFrom(CTE_NAME_RE_IDS) call. I'm surprised that this would have compiled in any jOOQ 3.x version (3.0 introduced Record1, Record2, etc...). I cannot reproduce this regression on my side. How can I? If you have some time to create a test case to show how to reproduce this, we have a template here: https://github.com/jOOQ/jOOQ-mcve.

 

The corresponding column, indentation is by me:

> public final TableField<RealEstateRecord, IntegerID = createField
> (
>     DSL.name("id"),
>     org.jooq.impl.SQLDataType.INTEGER.nullable(false).defaultValue
>     (
>         org.jooq.impl.DSL.field
>         (
>             "nextval('real_estate_id_seq'::regclass)",
>             org.jooq.impl.SQLDataType.INTEGER
>         )
>     ),
>     this,
>     ""
> );

The overloads in "Field":

> Condition in(Collection<?> values);
> Condition in(Result<? extends Record1<T>> result);
> Condition in(T... values);
> Condition in(Field<?>... values);
> Condition in(Select<? extends Record1<T>> query);

The following works, but is obviously wrong:

> .where(REAL_ESTATE.ID.in((Field<?>) DSL.selectFrom(RdQueryBuilder.CTE_NAME_RE_IDS))

That would choose the wrong overload. You don't want the Field<?>... overload here, which is for IN lists, not for IN subqueries. 

 

Every other cast seems to fail with Java trying to use the overload
for "in(Collection)".

But you don't really want that overload to be chosen. You want the in(Select<? extends Record1<T>>) overload to be chosen.

 

I really only want an "in(SELECT * FROM ...)" and other more verbose statements don't work as well:

> .where(REAL_ESTATE.ID.in(DSL.select(DSL.asterisk()).from("").where(DSL.trueCondition()))
> .where(REAL_ESTATE.ID.in(this.getDbConn().getJooq().select(DSL.asterisk()).from("").where(DSL.trueCondition()))

Yes, all of these produce a Select<Record> type, which is unaware of your query containing exactly one column of type Integer (as required by REAL_ESTATE.ID)

 

Isn't that pretty much what the official docs have as example as well?

https://www.jooq.org/doc/3.13/manual/sql-building/table-expressions/nested-selects/

The scalar subquery example there is explicitly projecting a single column in the SELECT clause, which is what I would always recommend to do. In general, using the asterisk is a source of trouble in SQL, even in cases where you *know* that it only produces one column. The best solution (in my opinion) is to explicitly project that column from your CTE. You could probably write an auxiliary method that produces the entire subquery, rather than just the CTE name, dynamically, so you don't have to do the repetitive work all the time. Other than that, if you want to profit from jOOQ's type safety and guarantees about single column queries, your CTE needs to implement Record1<Integer> for your IN predicate to work correctly with the syntax you're trying to use.

I'm still surprised that this seems to have compiled at some earlier stage. It should not have.

Thanks,

Lukas

[Thorsten Schöning]

Guten Tag Lukas Eder,
am Montag, 8. Juni 2020 um 10:40 schrieben Sie:

> So, the compiler is inferring Select<Record> for your
> selectFrom(CTE_NAME_RE_IDS) call. I'm surprised that this would have
> compiled in any jOOQ 3.x version (3.0 introduced Record1, Record2, etc...).
> I cannot reproduce this regression on my side. How can I? If you have some
> time to create a test case to show how to reproduce this, we have a
> template here: https://github.com/jOOQ/jOOQ-mcve.

Thanks for looking into this, but I don't have the time to create the
mcve right now. Doesn't seem to change much anyway if you tell that
things only worked by accident. :-) So I'll preferrably use the time
to change my implementation to successfully move forward.

[Lukas Eder]

Sure, I perfectly understand.

Thanks for the feedback!

Lukas

--
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 jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/168455493.20200608165946%40am-soft.de.