Hi Nico, In the very early days of jOOQ, jOOQ tried to be clever and translate field.eq(null) to field.isNull(). With this kind of cleverness, it would have been possible to take into account the SQLDialect and hande Oracle's '' as NULL as well.
This only led to pain and suffering, because of the many cases where the clever hack did not apply or could not be implemented consistently, e.g. when writing things like field.eq(val(null)) or when using converters or bindings (which were added later). I'm afraid that jOOQ cannot offer such cleverness throughout the API. Your list is far from complete, e.g. it does not include concatenation. In standard SQL databases, NULL || 'something' yields NULL again, e.g. run this in PostgreSQL to get "true": SELECT (null || 'something') IS NULL This makes perfect sense, but in Oracle concatenating NULL (which is the same as the empty string) to some string does not yield NULL. The following yields "0" in Oracle: SELECT CASE WHEN (NULL || 'something') IS NULL THEN 1 ELSE 0 END FROM dual; There are many other built-in functions and syntax elements that are affected. So, I'm afraid, it'll be rather difficult to handle this in a vendor agnostic way. Thanks, Lukas Am Mi., 27. Juni 2018 um 08:58 Uhr schrieb Nicolai Ehemann < [email protected]>: > Hello, > > we have varchar2 columns which should not be null, but are allowed to > contain an empty string. So from our perspective, the oracle implementation > of empty string -> store as null does not hurt per se; however, the > handling on the jooq side is a bit cumbersome (not because of jooq, but > because of oracle): > > set(field, "") // ok-ish (can't get any better with oracle) > > get(field, "") // returns null > > where(field.eq("")) // not true > where(field.isNull()) // this is in fact field.eq("") > > So, is there a best practice or jooq option to handle such use cases > without having to be extra careful on every usage of the field to handle > the special case properly? > > (Bonus question: is there a way to handle the general varchar case > cross-database-safe?) > > Thank you > > Nico > > -- > 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]. > For more options, visit https://groups.google.com/d/optout. > -- 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]. For more options, visit https://groups.google.com/d/optout.
