Apologies, the old jooq code(before v3.19.16) gave this as sql generated
code on calling the function:*getTable1SelectCondition()* as described here
<https://groups.google.com/g/jooq-user/c/GS5Snot21Y8/m/eaOULe4nAQAJ>.
(
"public"."office"."is_active" = true
and "public"."office"."organization_id" =
'cc6f2148-6030-4205-a00f-3acfce27b369'
* and "public"."office"."name" like (('%' || cast('off' as citext)) ||
'%') escape '!'*
)
and complete sql generated via code generation is:
select
"public"."table1"."id",
"public"."table1"."address",
"public"."table1"."name"
from "public"."table1"
join "public"."table2"
on "public"."table1"."legal_entity_id" = "public"."table2"."id"
where (
"public"."table1"."is_active" = true
and "public"."table1"."id" = cast('abc' as uuid)
* and "public"."office"."name" like (('%' || cast('**off**' as citext)) ||
'%') **escape '!'*
)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
the new JOOQ version gave
( "public"."office"."is_active" = true
and "public"."office"."organization_id" =
'5bd8b80f-8faa-48df-a520-499a9b35ae2a'
* and "public"."office"."name" like (('%' || cast(cast('off' as citext) as
varchar)) || '%') escape '!'*
)
and complete sql generated via code generation is:
select
"public"."table1"."id",
"public"."table1"."address",
"public"."table1"."name"
from "public"."table1"
join "public"."table2"
on "public"."table1"."legal_entity_id" = "public"."table2"."id"
where (
"public"."table1"."is_active" = true
and "public"."table1"."id" = cast('abc' as uuid)
* and cast("public"."table1"."name" as varchar) like (('%' ||
cast(cast('off' as citext) as varchar)) || '%') escape '!'*
)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Here even *citext* is being casted as *varchar* which is an unnecessary
cast which got introduced with change :
https://github.com/jOOQ/jOOQ/blame/b59a96fbee2b477c7f633b51a82be9334e6f959f/jOOQ/src/main/java/org/jooq/impl/Tools.java#L1965
similar to this, the casting happens on left operand side as well due to
which this like operation is no longer case insensitive.
This change is causing the* case-sensitive* behaviour of *contains*
operation(in this
<https://groups.google.com/g/jooq-user/c/GS5Snot21Y8/m/eaOULe4nAQAJ> old
conversation), where *citext* gets converted as *varchar *along with left
operand to *varchar*
Following this <https://github.com/jOOQ/jOOQ/issues/17460> thread , it
gives a idea that the unnecessary conversion were earlier dropped for
optimization, are now taken into account to resolve some regression issues.
However, since the right operand is a *citext*, should the *contains()* not
handle things accordingly?
Also, can you please elaborate how the left operand auto-conversion happens
in code.
On Tuesday, January 28, 2025 at 3:01:19 PM UTC+5:30 Kunal Kumar wrote:
> As stated above in this
> <https://groups.google.com/g/jooq-user/c/GS5Snot21Y8/m/DgItxHEzAAAJ> , we
> have used the code generation approach only.
>
> SQL which was used to create TABLE1 is :
>
> CREATE TABLE *TABLE1* (
> id uuid PRIMARY KEY,
> updated_at timestamptz NOT NULL,
> updated_by_bid uuid NOT NULL,
> legal_entity_id uuid REFERENCES legal_entity(id) NOT NULL,
> address jsonb,
> * name text*
> );
>
> Here name is a *text* field, and is autocasted to *varchar* in
> where (
> "public"."table1"."is_active" = true
> and "public"."table1"."id" = cast('abc' as uuid)
> and *cast("public"."table1"."name" as varchar) *like (('%' ||
> cast(cast('ff' as citext) as varchar)) || '%') escape '!'
> )
> as described above.
> On Tuesday, January 28, 2025 at 1:13:10 AM UTC+5:30 Lukas Eder wrote:
>
>> I meant, can you show the code where you create the field reference.
>>
>> Anyway, as I said, https://github.com/jOOQ/jOOQ/issues/15609 is the most
>> likely explanation. I recommend using generated code. There are many
>> reasons in favour of code generation, this is one of them:
>> https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/
>>
>> Otherwise, always provide explicit data types when creating field
>> references manually.
>>
>>
>> On Monday, January 27, 2025, Kunal Kumar <[email protected]> wrote:
>>
>>> TABLE1.NAME : TABLE1 <http://TABLE1.NAME> is the name of table and
>>> NAME <http://TABLE1.NAME> is the field inside the table. This is not
>>> the generated code. I have just changed the name of table as TABLE1(for
>>> privacy)
>>>
>>> Thanks and regards,
>>> Kunal
>>>
>>> On Monday, January 27, 2025 at 8:12:09 PM UTC+5:30 Lukas Eder wrote:
>>>
>>>> OK, and what is TABLE1.NAME? I suspect this isn't from generated code?
>>>>
>>>> On Mon, Jan 27, 2025 at 10:23 AM Kunal Kumar <[email protected]>
>>>> wrote:
>>>>
>>>>> Code for *getTable1SelectCondition *is:
>>>>> ```
>>>>> private Condition *getTable1SelectCondition*(
>>>>> final UUID id, final String queryText, final
>>>>> List<ListOfficeFilter> filters) {
>>>>> var where = *TABLE1.IS_ACTIVE.eq(true).and(TABLE1.ID.eq(id));*
>>>>>
>>>>> if (StringUtils.isNotBlank(queryText)) {
>>>>> where = *where.and(TABLE1.NAME
>>>>> <http://TABLE1.NAME>.contains(JooqUtil.escapeLikeQueryPattern(queryText)));*
>>>>> }
>>>>>
>>>>> val filterConditions = new ArrayList<Condition>();
>>>>> for (val filter : filters) {
>>>>> var condition = DSL.noCondition();
>>>>> if (!filter.getOfficeIdsList().isEmpty()) {
>>>>> condition =
>>>>> condition.and(
>>>>> TABLE1.ID.in
>>>>> (filter.getOfficeIdsList().stream().map(ProfileUtils::uuid).toList()));
>>>>> }
>>>>> if (!filter.getLegalEntityIdsList().isEmpty()) {
>>>>> condition =
>>>>> condition.and(
>>>>> TABLE1.LEGAL_ENTITY_ID.in(
>>>>>
>>>>> filter.getLegalEntityIdsList().stream().map(ProfileUtils::uuid).toList()));
>>>>> }
>>>>> if (!filter.getExternalIdsList().isEmpty()) {
>>>>> condition = condition.and(TABLE1.EXTERNAL_ID.in
>>>>> (filter.getExternalIdsList()));
>>>>> }
>>>>> if (!filter.getCountriesList().isEmpty()) {
>>>>> condition = condition.and(TABLE1.COUNTRY_CODE.in
>>>>> (filter.getCountriesList()));
>>>>> }
>>>>>
>>>>> filterConditions.add(condition);
>>>>> }
>>>>>
>>>>> return
>>>>> where.and(filterConditions.stream().reduce(DSL.noCondition(),
>>>>> Condition::or));
>>>>> }
>>>>> ```
>>>>>
>>>>> It evaluates to (you may consider that *filters *was empty list)->
>>>>> (
>>>>> "public"."table1"."is_active" = true
>>>>> and "public"."table1"."id" = 'abc'
>>>>> and* "public"."table1"."name"* like (('%' || cast(cast('ff' as
>>>>> citext) as varchar)) || '%') escape '!'
>>>>> )
>>>>>
>>>>>
>>>>> Thanks and regards,
>>>>> Kunal
>>>>> On Friday, January 24, 2025 at 12:14:58 PM UTC+5:30 Lukas Eder wrote:
>>>>>
>>>>>> Thanks for your message. This looks like an instance of
>>>>>> https://github.com/jOOQ/jOOQ/issues/15609. I can't say for sure,
>>>>>> because you didn't show the code of your getTable1SelectCondition()
>>>>>>
>>>>>> On Fri, Jan 24, 2025 at 7:42 AM 'Kunal Kumar' via jOOQ User Group <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Hi
>>>>>>> While using *Jooq version 3.19.17, *I am facing the issue below,
>>>>>>>
>>>>>>> If I do this,
>>>>>>> dslContext
>>>>>>> .named(DaoJooqImpl.class, "getTemp")
>>>>>>> .select(Table1.fields())
>>>>>>> .from(Table1)
>>>>>>> .join(Table2)
>>>>>>> .on(Table1.LEGAL_ENTITY_ID.eq(Table2.ID))
>>>>>>> .where(*getTable1SelectCondition(id, queryText, filters)*)
>>>>>>>
>>>>>>> the query is coming as
>>>>>>> select
>>>>>>> "public"."table1"."id",
>>>>>>> "public"."table1"."address",
>>>>>>> "public"."table1"."name"
>>>>>>> from "public"."table1"
>>>>>>> join "public"."table2"
>>>>>>> on "public"."table1"."legal_entity_id" = "public"."table2"."id"
>>>>>>> where (
>>>>>>> "public"."table1"."is_active" = true
>>>>>>> and "public"."table1"."id" = cast('abc' as uuid)
>>>>>>> and *cast("public"."table1"."name" as varchar) *like (('%' ||
>>>>>>> cast(cast('ff' as citext) as varchar)) || '%') escape '!'
>>>>>>> )
>>>>>>>
>>>>>>>
>>>>>>> Here *cast("public"."table1"."name" as varchar) *is generated
>>>>>>> instead of *public"."table1"."name" * which is causing case
>>>>>>> sensitive matching in like operation.
>>>>>>>
>>>>>>> however the *getTable1SelectCondition(id, queryText, filters) *returns
>>>>>>> a *Condition *object which is this-
>>>>>>> (
>>>>>>> "public"."table1"."is_active" = true
>>>>>>> and "public"."table1"."id" = 'abc'
>>>>>>> and* "public"."table1"."name"* like (('%' || cast(cast('ff' as
>>>>>>> citext) as varchar)) || '%') escape '!'
>>>>>>> )
>>>>>>>
>>>>>>> How to resolve this and prevent casting of field
>>>>>>> *"public"."table1"."name"
>>>>>>> * to varchar?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Kunal
>>>>>>>
>>>>>>> --
>>>>>>> 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 visit
>>>>>>> https://groups.google.com/d/msgid/jooq-user/e7de2309-d303-4f3c-abd7-28f141ac649dn%40googlegroups.com
>>>>>>>
>>>>>>> <https://groups.google.com/d/msgid/jooq-user/e7de2309-d303-4f3c-abd7-28f141ac649dn%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>>>> .
>>>>>>>
>>>>>> --
>>>>> 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 visit
>>>>> https://groups.google.com/d/msgid/jooq-user/8eacbc7c-dffd-46c6-8d2b-061b4298de76n%40googlegroups.com
>>>>>
>>>>> <https://groups.google.com/d/msgid/jooq-user/8eacbc7c-dffd-46c6-8d2b-061b4298de76n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>>> .
>>>>>
>>>> --
>>> 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 visit
>>> https://groups.google.com/d/msgid/jooq-user/9e18f2b7-42ab-46de-ac82-3ec508354e9fn%40googlegroups.com
>>>
>>> <https://groups.google.com/d/msgid/jooq-user/9e18f2b7-42ab-46de-ac82-3ec508354e9fn%40googlegroups.com?utm_medium=email&utm_source=footer>
>>> .
>>>
>>
--
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 visit
https://groups.google.com/d/msgid/jooq-user/2609595e-388a-4a69-901c-12d6178165e6n%40googlegroups.com.
