Question

Hello. I am trying to get a pipelined query automated based on an index that has 4 columns. The last column is deterministic. All the other 3 columns can potentially have nulls as the value. For this example let's say it is ASC order.

How can I build a where clause that take into account the potential nulls?

When there are just two columns in the index as in Markus' fine example under Fetching the Next Page, it is not an issue because there is only the first (principal) column and the deterministic column.

With more columns, there is always the possibility that one of the other columns (in this example columns 2 or 3) will have a true value in a record, but a null in the next sorted record. This can happen it the previous column jumps in value, starting the other columns over to the lowest value. However, if this happens it needs to know to look for the null. Because of this, my attempts so far seems to skip or jump to the wrong place when trying to get a sequential order.

Hope that is clear. If not, please let me know.

Thanks, Rocky

****************************UPDATED HERE!!********************************************

I was asked to add sample data and further explanation of my issue. Here is the index and a few records ordered by this index:

CREATE INDEX idx_purchases_ordered ON purchases ( 
order_no,
id,
id_service,
ordered 
);


|ordered   |id_service|order_no|id |
------------------------------------
|NULL      |1         |I-97569 |3  |
|2013-10-03|NULL      |sdafsad |2  |
|2013-10-03|1         |r456789 |1  |

As you can see, when paginating to the first example record, it is pretty straight forward. However, to get to the next record in this order, the ordered column is no longer NULL, but the id_service is. So, the WHERE clause has to be able to look for a lower value in id_service (NULL are first) even though the order is ascending.

In the example above, NULL values are used, but it would be the same using any lower value I guess.

Sorry for the delay in updating my original post.

Thanks, Rocky

Answer 1

We haven't implemented this yet in jOOQ, but we will soon (#2786). With jOOQ, you can write a SQL statement like this one:

DSL.using(configuration)
   .select(A, B)
   .from(T)
   .orderBy(NULL_COLUMN.asc().nullsFirst(), NOT_NULL_COLUMN.asc())
   .seek(null, 1)

Where orderBy() and seek() play together to form the following predicate and ORDER BY clause:

WHERE (null_column IS NOT NULL) OR (null_column IS NULL AND not_null_column > 1)
ORDER BY
    null_column ASC NULLS FIRST, 
    not_null_column ASC

Depending on your combination of ASC / DESC and NULLS FIRST / NULLS LAST, a different combination of predicates will need to be generated.

And if NULLS FIRST is not supported in the database (Access, CUBRID, DB2, Ingres, MariaDB, MySQL, SQLite, SQL Server, Sybase ASE, Sybase SQL Anywhere), it is emulated as such

WHERE (null_column IS NOT NULL) OR (null_column IS NULL AND not_null_column > 1)
ORDER BY 
    (CASE WHEN null_column IS NULL THEN 0 ELSE 1 END) ASC,
    null_column ASC,
    not_null_column ASC

I think you get the idea...

Answer 2

Sorry for the delay in responding, everyone. I would like to respond to Victor's request for some sample data, but from the formatting options given on the editor, I can't see how to display sample data in a readable format.

I am new to the "ask" part of the website, so can someone recommend how I can do that?

Thanks,

Rocky

answered Oct 22 '14 at 00:30

Rocky
46●1●1●4

The best way to add further details is by editing your original question. For data, you might want to use code formatting which requires lines to start with 4 spaces. In case of doubt, just add it, I'll have a look at the formatting.

(Oct 22 '14 at 08:28) Markus Winand ♦♦