Jooq: seekBefore is not fetching the previous but the first page

[whiskeysierra]

According to #2805:

The SEEK method is currently only partially implemented, i.e. it allows only for fetching the "next" page. In order to fetch the "previous" page, a similar seekBefore() method could be introduced.

But what it generates is:

  SELECT * FROM table
   WHERE id < 5
ORDER BY id ASC
   LIMIT 3

compared to seekAfter:

  SELECT * FROM table
   WHERE id > 5
ORDER BY id ASC
   LIMIT 3

As you can see it only inverts the condition from > to <.
Let's assume that the table contains rows with id from 1 to 10.
Using seekAfter(5).limit(3) will return 6, 7 and 8,
but seekBefore(5).limit(3) will return 1, 2 and 3.
I think it really should return 2, 3 and 4.

I'd expect something like:

SELECT * FROM (
      SELECT * FROM table
       WHERE id < 5
    ORDER BY id DESC
       LIMIT 3
) AS t
ORDER BY id ASC

See also http://ask.use-the-index-luke.com/questions/205/how-to-query-for-previous-page-with-keyset-pagination

[lukaseder]

Thank you very much for reporting. We'll look into this ASAP

[lukaseder]

You're right, the expected behaviour is the one you're describing, and the one that was described in the original issue #2805. This slipped through because the integration tests for seekBefore() paginated back from page 2 to page 1, or they paginated without LIMIT, in case of which the current behaviour is accidentally correct.

Even if this is a behaviourally backwards-incompatible change, I'll assume that no one would want the current behaviour. Also, the change does not violate the Javadoc, but extend it, as the Javadoc currently doesn't make an example using LIMIT.

We'll fix this in jOOQ 3.10. Given the backwards incompatibility, the fix won't be merged to 3.9.4 or older releases.

[whiskeysierra]

For anyone who has the issue right now, there is a workaround if you need the behaviour of seekBefore as it is specified:

• flip all sort fields in your orderBy clause, e.g. A.asc(), B.desc() should become A.desc(), B.asc()
• use seekAfter instead of seekBefore
• reverse the result list using Collections.reverse

[lukaseder]

I'm afraid, this won't fit in jOOQ 3.10 anymore. There are some delicate details related to applying this with queries that involve unions to get right...

[whiskeysierra]

I think it's OK given that a workaround exists.

[lukaseder]

Looking into fixing this now. Things to consider:

• Derived tables are not allowed to have ambiguous column names, so we need to rename them to enumerated names and then rename the enumerated names back to the original ones in the outer query. There's already a mechanism in place for this to emulate offset pagination in older SQL dialects
• A query may choose to project different columns than what is used in order by:
  input
  sql SELECT a FROM x ORDER BY b SEEK BEFORE 10 LIMIT 5
  output
  sql SELECT a FROM ( SELECT a, b -- b needs to be projected in order to be able to order it FROM x WHERE b < 10 ORDER BY b DESC LIMIT 5 ) ORDER BY b ASC
• DISTINCT makes everything even more complicated
• Speaking of emulated pagination (Oracle 11g and before), that has to work as well with both SEEK BEFORE and SEEK AFTER. Other databases don't have this problem as they all have had a way of doing LIMIT / TOP / FETCH for ages. Perhaps, we won't fix this.
• Set operations like UNION still don't work correctly with SEEK BEFORE and SEEK AFTER: #7459

[lukaseder]

This is a really challenging change. Unfortunately, these things haven't been considered properly, prior to implementing this feature. It seems that deprecating it might be the best way forward.

The workaround you've documented is one option for simple cases. Another option is to manually wrap a SEEK query in a derived table and order it again.

[lukaseder]

Deprecation: #7461. it will be lifted again, once this is fixed.

[cowwoc]

@lukaseder

Another option is to manually wrap a SEEK query in a derived table and order it again.

How can we implement seekBack in terms of a derived table without knowing the column names? I want to implement seekBack once in a generic manner instead of having to reimplement once per query but then the implementation can't know the table, column or ordering that needs to be reversed ahead of time. Any ideas?

[lukaseder]

How can we implement seekBack in terms of a derived table without knowing the column names?

You can rename the columns in a derived table by using derived column lists. SQL syntax: (...) as t (a, b, c). jOOQ syntax: Table.as(String, String...). This may be necessary anyway, in case you have duplicate column names in your derived table (allowed in top level selects, but not in subqueries)

Alternatively, when you don't rename the columns, you can still access them by using Table.fields(). Hope this helps.