Sorry for being a pain here, but I have a ton of support requests, and I can't follow up 10 times on an individual issue just for the issue to become slightly more clear. I'll update the process soon. The user group is good for open ended questions, but not for bug reports like this, which will not be accepted in the future. We really need complete reproducers almost every time, unless the issue is *very* obvious (which isn't the case here).
To illustrate: I added this table to our MCVE template (see https://github.com/jOOQ/jOOQ-mcve) CREATE TABLE mcve.TABLE1 ( id uuid PRIMARY KEY, updated_at timestamptz NOT NULL, updated_by_bid uuid NOT NULL, legal_entity_id uuid NOT NULL, address jsonb, name text ); I ran code generation and tried this: @Test public void test() { System.out.println(ctx.selectFrom(TABLE1).where(TABLE1.NAME.contains("x"))); } No such cast was reproduced, instead I got: select "mcve"."table1"."id", "mcve"."table1"."updated_at", "mcve"."table1"."updated_by_bid", "mcve"."table1"."legal_entity_id", "mcve"."table1"."address", "mcve"."table1"."name" from "mcve"."table1" where "mcve"."table1"."name" like (('%' || replace( replace( replace('x', '!', '!!'), '%', '!%' ), '_', '!_' )) || '%') escape '!' And now, I'm back to wondering what exactly it is that you're doing. While it is possible to continue going back and forth in this conversation here, trying to extract important bits to reproduce the problem, why not just provide a complete reproducer instead? In our issue template on github, we ask for MCVE's (Minimal, Complete, Verifiable Examples). These help drastically increase the efficiency of support requests, to the benefit of both. Thanks for your understanding. On Wed, Jan 29, 2025 at 8:52 AM Kunal Kumar <kunalkumar110...@gmail.com> wrote: > 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 <kunalkum...@gmail.com> 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 <kunalkum...@gmail.com> >>>>> 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 < >>>>>>> jooq...@googlegroups.com> 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 jooq-user+...@googlegroups.com. >>>>>>>> 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 jooq-user+...@googlegroups.com. >>>>>> >>>>> 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 jooq-user+...@googlegroups.com. >>>> >>> 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 jooq-user+unsubscr...@googlegroups.com. > To view this discussion visit > https://groups.google.com/d/msgid/jooq-user/2609595e-388a-4a69-901c-12d6178165e6n%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/2609595e-388a-4a69-901c-12d6178165e6n%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 jooq-user+unsubscr...@googlegroups.com. To view this discussion visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6v3yxEyx0tEVeFvDcdj4MFig%2BdhKYozOfH_O6_p_%3DUDg%40mail.gmail.com.
