Search code examples
mysqljooq

"Seek" paging with jOOQ skipping rows

I'm trying to implement "seek" paging using jOOQ (3.11.12) + MySQL (5.7.24).

I have a table of products, that contains the following rows:

ID                    | Name      | Created At
----------------------  ---------   -------------------
XjpPXlZxT5i3tTjO7lZQ6Q  Product A   2019-10-25 03:23:05
SmytEB9lTW-UiVFhg_gViQ  Product B   2019-10-09 05:43:44
glpNYcsBTJqAzQERbgGh5g  Product C   2019-10-02 14:53:48
HDZ1K7g_Rj-2vdQaEj79Ow  Product D   2019-09-07 14:52:56
aTcWWxdJSReZBGzkLXuNIQ  Product E   2019-09-06 08:21:24
HPOD380mTR-g2Ut4Da0k4Q  Product F   2019-09-06 08:19:57
jXzfHBDAQ6We4CjXLem_WA  Product G   2019-09-06 08:16:06
duxiQ3InRXaFy_JVDkkewQ  Product H   2019-09-06 08:15:02
QF-3ECfLQD2vdVGE_5X-rQ  Product I   2019-09-04 12:35:00
zRnp0tLZRjSsQHN0wV7N_w  Product J   2019-09-04 12:34:28
6Y3E3KkITYWbOs5aOQCHOw  Product K   2019-09-04 10:33:38
ZOoG06ThRTiDDhteIW_6tA  Product L   2019-09-04 10:19:14
6UW4MUClSLSuQI3pkA0qJA  Product M   2019-09-04 10:18:40

Assume my application shows pages of 5 products at a time, ordered from newest to oldest.

I'm therefore ordering by creation date descending, and also ordering by ID so as to disambiguate between products that may have been created at the same moment.

I'm trying to fetch the results what would be the second page. The code (with relevant runtime values substituted in) looks like such:

selectFromWhere // <-- assume this to be a SelectConditionStep built with various filter criteria
    .orderBy(TBL_PRODUCT.CREATED_AT.desc(), TBL_PRODUCT.ID.asc())
    .seek(2019-09-06T08:21:24Z, "aTcWWxdJSReZBGzkLXuNIQ") // <-- runtime values
    .limit(limit)
    .fetchInto(Product::class.java)

This generates the following SQL (fully-qualified references and filter criteria omitted for brevity):

select distinct 
  id, created_at
from tbl_product
where (
    (
    created_at < {ts '2019-09-06 08:21:24.0'}
    or (
      created_at = {ts '2019-09-06 08:21:24.0'}
      and id > 'aTcWWxdJSReZBGzkLXuNIQ'
    )
  )
)
order by 
  created_at desc, 
  id asc
limit 5

If I copy/paste and run the generated query manually from a SQL session, I get the results I expect:

  • Product F
  • Product G
  • Product H
  • Product I
  • Product J

...however, the results of the execution are saved into a local variable, and when I debug my program to examine its contents, I see it contains:

  • Product I
  • Product J
  • Product K
  • Product L
  • Product M

Two questions:

  1. Why would jOOQ return different results for the same query I run manually against the same database?
  2. Is there something wrong with my approach?

Any suggestions would be greatly appreciated!

Solution

  • Assuming the database column type for TBL_PRODUCT.CREATED_AT is DATETIME and the corresponding Java type is java.sql.Timestamp (which would be the default in jOOQ 3.11), this situation could arise when the time zone of the MySQL server differs from that of the Java client, since the JDBC driver will convert the timestamp for you (see https://stackoverflow.com/a/14070771/1732086 for details).

    This behavior can also be controlled using various JDBC connection URL parameters (see https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html). One option is to use the serverTimezone JDBC URL property to specify the client's time zone as the session time zone to be used (e.g. serverTimezone=Europe/Zurich).

    Time zones can always cause nasty surprises, especially in the context of JDBC :-(