Hi Mani, The reason why #2786 didn't get much traction so far is because this is probably quite an edge case of SEEK usage. Before we move on discussing workarounds, or possible solutions, I would be very interested in better understanding the use case you're having right now. When would you want to "seek" / keyset paginate on a nullable column?
Thanks, Lukas On Wed, May 1, 2019 at 8:13 PM <[email protected]> wrote: > 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. > -- 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.
