Hi Lukas! Thanks for getting back to me! Yes, seeking on a nullable column by itself wouldn't make much sense, so I figure I would have to append a non-nullable unique column to my queries. Let me provide a more detailed example.
For example: Say I have a table of accounts like this: CREATE TABLE account ( id bigint primary key, email text non-null unique, -- assume an account email address is required but the name is not name text, ... other fields ); Some example rows in this table: |id |email |name |other fields | ------------------------------------ |1 |[email protected] |Alice Eve |..... | |2 |[email protected] |NULL |..... | |3 |[email protected] |Carl Frank |..... | |4 |[email protected] |NULL |..... | . . . My use case involves fetching data from this database table to display in a table on the client side. Since this table can have a large number of rows, I've chosen to use keyset pagination to order the result set and serve up results in small batches. With typical usage of keyset pagination, if I wanted to get the list of accounts ordered by ID, I'd do something like: DSL.(...).selectFrom(account).orderBy(account.id).seekAfter(<some id value >).fetch(); and since ID is the primary key, it's unique and non-nullable so I'd get my result set as expected. However, say I want to display accounts on the client side ordered by the name column, which is this case is a nullable column. Doing something like: DSL.(...).selectFrom(account).orderBy(account.name).seekAfter(<some name value>).fetch(); would produce unexpected results, since some of the name fields are null and null values themselves aren't unique. What I'd like to do is something like this: DSL.(...).selectFrom(account).orderBy(account.name, account.id).seekAfter(<some name value>, <some id value>).fetch(); However, the generated SQL for the predicate will have the potential to compare a null value to a non-null value for the name field. So this query will fetch all accounts with a non-null name field and return them according to the order by clause. I'd miss out on any accounts with a null name field. Looking at the generated SQL, the generated predicate looks like: where (account.first_name > <some non-null account name> ) or (account.first_name = <some non-null account name> and account.id > <some non-null account id>) When that query is comparing an account record in the table that has a null name, it won't pass the predicate, so that record won't ever be returned. So I can't currently use keyset pagination/seekAfter to fetch a list of accounts and display them in a table when the client asks for the list of accounts ordered by the name field. Hope that makes sense and provides a better look at my use case. Any thoughts? Thanks, Mani -- 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.
