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/CAB4ELO5qs1-AVDEry_dFBggy5z2OZRPy6Q8PUsAt03P6idXp3w%40mail.gmail.com.
