Field.equalIgnoreCase(String) method broken for SQL Server

[MiErnst]

We migrate our implementation from 2.6.0 to 3.1.0 and figured out that the equalIgnoreCase method is broken for longer string comparisions.

The method internally calls the cast method which calls org.jooq.impl.AbstractField.cast(DataType)

// [#473] Prevent unnecessary casts
if (getDataType().equals(type)) {
    return (Field<Z>) this;
}

Here getDataType() returns the a varchar (100) and compares it with a varchar without a length. This results in a Cast Object. Under MSSQL the default length of a varchar cast is 30 so all values will be shortened and the "=" compare fails for strings which are longer than 30 characters.

The generated code is:

      lower
      (
         cast([TABLE].[FIELD_NAME] as varchar)
      )
      = lower('MY-REEAAAAAALYYYYY-LONNNNNNNNG-STRING')

[lukaseder]

Thank you for reporting this. This might be related to #2597. I'll need some further investigation, but I feel that this issue has been seen before

[lukaseder]

I could reproduce this issue as such:

A a = create().newRecord(TAuthor());
a.setValue(TAuthor_ID(), 3);
a.setValue(TAuthor_LAST_NAME(), "ABCDEFGHIJ1234567890ABCDEFGHIJ1234567890");
assertEquals(1, a.store());

assertEquals(3, (int)
create().select(TAuthor_ID())
        .from(TAuthor())
        .where(TAuthor_LAST_NAME().equalIgnoreCase("abcdefghij1234567890abcdefghij1234567890"))
        .fetchOne(TAuthor_ID()));

[lukaseder]

The quick fix is to have jOOQ's SQL Server integration cast VARCHAR types of unknown length to VARCHAR(MAX). A more thorough fix avoiding the cast entirely will be implemented with #2597.

The fix is committed and will be pushed in a bigger push, soon.

[lukaseder]

Hmm, do you happen to know what other data types suffer from this? Surely, NVARCHAR and VARBINARY do. Which other ones?

[MiErnst]

Thank you for the fix.
No, I don't have evaluated which other datatypes have similar problems, only our test cases are broken for the string comparison.