Converter of left operand shouldn't be applied in LIKE operators, Jooq version 3.19.17

[Kunal Kumar]

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

[Lukas Eder]

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()

--
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.

[Kunal Kumar]

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.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

[Lukas Eder]

OK, and what is TABLE1.NAME? I suspect this isn't from generated code?

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/8eacbc7c-dffd-46c6-8d2b-061b4298de76n%40googlegroups.com.

[Kunal Kumar]

 TABLE1.NAME :  TABLE1 is the name of table and  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

[Lukas Eder]

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.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jooq-user/9e18f2b7-42ab-46de-ac82-3ec508354e9fn%40googlegroups.com.

[Kunal Kumar]

As stated above in this , 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. 

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/9e18f2b7-42ab-46de-ac82-3ec508354e9fn%40googlegroups.com.

[Kunal Kumar]

Apologies, the old jooq code(before v3.19.16) gave this as sql generated code on calling the function:getTable1SelectCondition() as described here.
(
  "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 old conversation), where  citext gets converted as varchar along with left operand to varchar

Following this 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.

[Lukas Eder]

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.

To view this discussion visit https://groups.google.com/d/msgid/jooq-user/2609595e-388a-4a69-901c-12d6178165e6n%40googlegroups.com.