citext variable having issue while doing like operation in postgreSQL, due to auto casting to varchar in JOOQ v3.19.18

[Kunal Kumar]

I am facing an issue where citext variable are getting casted to varchar in new jooq version(v3.19.18). In older version like jooq v3.19.11, this issue was not there. citext variable should give case-insensitive result when like operation is being applied. There is discrepancy in contains() operation in jooq which causes auto casting of citext to varchar in new jooq versions. I have created a producer example here.  I have taken the dump of output of queries mentioned in code in sampleOutput. Kindly let me know how to address this issue and prevent auto casting of citext to varchar in new jooq version.

This is in continuation to this thread.

Thanks and regards,

Kunal

[Lukas Eder]

Thanks a lot for your reproducer. I can see it now. I've created an issue for this:

https://github.com/jOOQ/jOOQ/issues/17958

A workaround in this particular case is to use a plain SQL template:

https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/

--
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/116f0dc9-5fed-4880-a27f-89e6f2d465a9n%40googlegroups.com.

[Lukas Eder]

Hi Kunal,

Not sure if you've subscribed to #17958. If not, just a short summary: While in your usage, a regression is shown, the regression is really an existing bug that was "activated" by recent changes, it has already been there before and could have been triggered with other API usage.

A thorough fix doesn't seem to be very simple, so I cannot make any promises right now. The plain SQL template approach (or any other way of avoiding using Field::contains with citext data types) seems to be ideal right now, for you.

Best Regards,

Lukas