Jooq fetching always single records but table is having more records

[mukul panwar]

Hi All

Please suggest what is going wrong here, we are running some query with where and IN clause using Jooq:

e.g : 

select snapshot_hour.hour_time from snapshot_hour where snapshot_hour.hour_time in ({ts '2023-09-24 02:00:00.0'}, {ts '2023-09-24 03:00:00.0'}, {ts '2023-09-24 04:00:00.0'})

final Set<LocalDateTime> setTime = transaction.select(toTable.snapshotTime()).from(toTable.snapshotTable())                       .where(toTable.snapshotTime().in(snapTime.keySet())).fetchSet(toTable.snapshotTime());

I tried to debug the logs I found logger listener is printing:

Fetched row(s) = 1

but if i run this generated query on table its return multiple records, Any pointer how can i debug or fix this issue? 

I will appreciate any response here.

Thank you

Mpanwar

[Lukas Eder]

Hi Mpanwar

A few hypotheses:

- You're calling fetchSet() at the end, which means that duplicate records are removed

- If your timestamps don't have seconds precision, then there might be values between those times that are available from the table, but not fetched by your query (why not use BETWEEN, rather than IN?)

- The transaction reading the rows can't see the other rows from a different transaction *yet* (or anymore).

- There's a time zone issue between client and server

I hope this helps.

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/6abbefe7-59b3-4bba-9704-e32a8b407083n%40googlegroups.com.

[mukul panwar]

Thank you so much for your help!

My table is having entries without seconds precision, I am running these queries in multithreaded env.

And It’s having issue in particular environment I checked client and server time zone also it’s same. 

Same code is working in other env. So not sure what is going on here.

Thanks 

Mpanwar

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6rtmPNh%2B4yWzjFO_8Swm4LSOy3D6%3D4H2HtRfKMJPwH1w%40mail.gmail.com.

[Lukas Eder]

Well, I think you'll have to use a debugger to step through the query execution. The answer is probably there.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB3%2BiXhd9Y%3DfYNrzaHrb5L5%2BK0x7XnsX8pj187hQ7bhU%2B%3D1LyA%40mail.gmail.com.