"Seek" paging with jOOQ skipping rows

Question

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:

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

Any suggestions would be greatly appreciated!

There's nothing wrong with your approach. Are your two transactions seeing the same data, though? — Lukas Eder, Dec 23 '19 at 10:19
@LukasEder I'm sure the application and my db client viewer session are connected to the same source. there's only one other db that could potentially be referenced, but it has completely different IDs. — homerman, Dec 23 '19 at 15:09
Hmm, well the obvious difference here is that your second run produces the expected result if the bind variable were truncated or cast to date. Do you have any converters or data type bindings in place that could produce such truncation? — Lukas Eder, Dec 24 '19 at 09:10
@LukasEder spot on! I just experimented using a hard-coded value for the date parameter to `seek()` and got the correct results in the application. thanks for pointing me in the right direction! — homerman, Dec 26 '19 at 05:06
Did you find there to be an issue in jOOQ? Or was it your client code that caused the problem? — Lukas Eder, Dec 27 '19 at 14:47
@homerman Could you tell us what the column type is for `TBL_PRODUCT.CREATED_AT` in the database and what Java type you map this column to (e.g. `java.sql.Date` or `java.sql.Timestamp`)? I would like to verify that jOOQ isn't doing anything wrong here. — knutwannheden, Jan 08 '20 at 10:03
@LukasEder + @knutwannheden apologies for the delay in responding (I was pulled away from this particular bit of work). as best I can tell, the issue seems to be with the client code, not jOOQ. `TBL_PRODUCT.CREATED_AT` is a MySQL DateTime that maps to `java.sql.Timestamp`. the value for this parameter that was eventually passed to `seek()` is constructed from an ISO-8601 formatted date/time in UTC, but it was being converted to the same date/time in the client application's local timezone (which isn't UTC). this does explain why the application's results seemed to "jump" ahead. — homerman, Jan 14 '20 at 07:47
...but I still don't know why copy/pasting the generated SQL into my DB client showed the correct results — homerman, Jan 14 '20 at 07:48

score 2 · Accepted Answer · answered Jan 08 '20 at 11:24

2

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 :-(

answered Jan 08 '20 at 11:24

knutwannheden

680
4
7

I think this is precisely what the issue was. the client code was creating `java.sql.Timestamp` instances from ISO-8601 strings represented in UTC, but converting them to the local timezone (which is offset from UTC). I've since patched the String -> Timestamp code and the results seem to be correct now... but I'm still not quite sure why I was able to copy/paste the jOOQ-generated SQL into my DB client and observe the correct results there compared to the wrong results coming from the application. is there some default, implicit UTC conversion that happens in jOOQ? – homerman Jan 14 '20 at 07:43
I forgot to preface my previous comment affirming that your assumptions about the column type and the Java type were both correct – homerman Jan 15 '20 at 02:11

"Seek" paging with jOOQ skipping rows

1 Answers1