Jooq seekAfter and null values

[ma...@streetcontxt.com]

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.

[Lukas Eder]

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

--
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.
For more options, visit https://groups.google.com/d/optout.

[ma...@streetcontxt.com]

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    |alice@gmail.com   |Alice Eve  |.....        |
|2    |bob@gmail.com     |NULL       |.....        |
|3    |carl@gmail.com    |Carl Frank |.....        |
|4    |dan@gmail.com     |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

[Lukas Eder]

Hi Many,

Thanks for the clarifications. I get it now. In any case, you have to append the ID column for all of your keyset paginating queries, just because you need some uniqueness criteria in your sorts. This is unrelated with name being nullable.

Since you know your domain and thus your possible data points, you could use coalesce as a workaround here. For example:

ctx.selectFrom(account)

   .orderBy(coalesce(account.name, inline("ZZZZZZZZZZZZZZZZZZZZ")), account.id)

   .seekAfter(nameValue, idValue)

   .fetch();

I put ZZZZ... as a placeholder there. You could use any possible non-null string that is guaranteed to be ordered after (or before, depending on how you want to handle nulls) all your data points.

Obviously, this is a workaround. A more thorough fix should be implemented via

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

I hope this helps,

Lukas

--

[ma...@streetcontxt.com]

Hi Lukas,

Yeah, it's a little bit messy to deal with, especially when trying to generalize the coalesce statement to fields of other datatypes, but it appears to be the only workaround for the moment. Not sure what can be done for #2786, but looking forward to it!

Thanks again for your help! 

Mani

[Lukas Eder]

Well, the generated predicates would have to handle nulls first / nulls last, just like if those clauses aren't supported by a given RDBMS, e.g. when writing:

ctx.selectFrom(account)

   .orderBy(account.name.nullsLast(), account.id)

   .seekAfter(nameValue, idValue)

   .fetch();

The generated SQL would have to be:

SELECT *

FROM account

WHERE 

    (CASE WHEN account.name IS NULL THEN 1 ELSE 0 END, COALESCE (account.name, 'dummy'), account.id)

  > (CASE WHEN :nameValue IS NULL THEN 1 ELSE 0 END, COALESCE (:nameValue, 'dummy'), account.id)

ORDER BY account.name NULLS LAST, account.id

Since jOOQ knows the data type of account.name, we can generate a non-null dummy value.

Good luck to your performance on that one :-)

--

[nelson....@gmail.com]

Thanks for the SQL query example, I'm facing the same issue and tries to solve it on my hand, by generating with jOOQ the kind of SQL query you posted.

You mentioned:

Since jOOQ knows the data type of account.name, we can generate a non-null dummy value.

Did you have an idea on how to generate this value from jOOQ? I looked at the DataType class, but did not find a way to do this. Any pointer would help!

Thanks!

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Thanks for your message, Nelson.

The DataType can be obtained from any Field using Field.getDataType().

I hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/ee24b65b-e91b-441e-827f-2253bcb48217%40googlegroups.com.

[nelson....@gmail.com]

Yes, I'm able to retrieve the DataType thanks to the Field.getDataType(). But when I get the DataType, did you have an easy way in mind to generate the 'dummy' value?

Currently, I have to hard code a list of dummy values per type, e.g. a dummy String will be "aaa", a dummy Integer will be 0...

Is there a way to generate this dummy value easily from the DataType?

On Tuesday, July 16, 2019 at 3:49:51 PM UTC+2, Lukas Eder wrote:

Thanks for your message, Nelson.

The DataType can be obtained from any Field using Field.getDataType().

I hope this helps,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Yes, that's what you will have to do. What I suggested here is a workaround... There aren't too many data types, so I guess the workaround is viable, no? What am I missing?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/36b3e06c-8efb-44d6-8724-ba4e27201ef5%40googlegroups.com.

[nelson....@gmail.com]

Ok no problem, yes it is a viable workaround, I was just wondering if you had another way in mind when you suggested the workaround. I'm going to keep the hardcoded list.

Thanks for the quick reply Lukas, it really helps!

On Tuesday, July 16, 2019 at 3:57:49 PM UTC+2, Lukas Eder wrote:

Yes, that's what you will have to do. What I suggested here is a workaround... There aren't too many data types, so I guess the workaround is viable, no? What am I missing?