I was looking into the usage of JOOQ's seekAfter method, (https://www.jooq.org/javadoc/3.11.5/org/jooq/SelectSeekStep1.html#seekAfter-org.jooq.Field-), and noticed the comment in the source code about it not working with null values.
Looking at the generated SQL, I can see why. The generated predicate in the where clause doesn't appropriately handle comparing null values in the given column to a non-null value from the seekAfter method. i.e. while searching for results, it can't properly compare null > 'abc'. I noticed there was an open GitHub issue, ( https://github.com/jOOQ/jOOQ/issues/2786), related to enhancing seekAfter to somehow handle this case but there hasn't been much development on that front. An idea I had that didn't pan out: I was thinking of appending a non-null unique column to the order by clause, (such as a primary key column) and using coalesce statements with the nullable columns. So the generated predicate would become something like: where (coalesce(nullable_column, <some default value>), primary_key_column) > (<some default value>, primary_key_value) However, that gets messy quite quickly when dealing with different field types. It also gets hairy when trying to determine what the default value should be since the choice of default value will now impact the comparison predicate. I was just wondering if anyone has come across this issue and found a way to handle it appropriately? Any insights would be much appreciated! -- This e-mail message is intended for the named recipient(s) above, and may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please do not read the content. Instead, immediately notify the sender and delete this e-mail message. Any unauthorized use, disclosure or distribution is strictly prohibited. Quantify Labs Inc and the sender assume no responsibility for any errors, omissions, or readers' misinterpretations and/ or understanding of the content or transmission of this email. -- 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.
