HQL does not return values even though generated SQL does

Question

I am facing this somewhat weird issue. (Maybe I don't understand it). I have the following HQL query.

"select distinct b " +
"from Booking b " +
"inner join fetch b.telephoneNumber tp " +
"left join fetch tp.patients p " +
"where b.concluded = false and b.schedule.doctor.id = :did and b.bookingDate = :date";

With following parameters.

query.setParameter("did", doctor.getId());
query.setParameter("date", date);

Here the date is a Java LocalDate. When I call the query.getResultList(), it does not return any values. (Even though there are values matching for the given criteria). Then I enabled the hibernate debug and trace logs to get the related SQL query and the values.

select distinct booking0_.id as id1_1_0_, telephonen1_.id as id1_11_1_, patient3_.id as id1_4_2_, booking0_.bookingDate as bookingD2_1_0_, booking0_.bookingReference as bookingR3_1_0_, booking0_.bookingUniqueId as bookingU4_1_0_, booking0_.channelRecord_id as channelR7_1_0_, booking0_.checkedIn as checkedI5_1_0_, booking0_.checkedInPatient_id as checkedI8_1_0_, booking0_.concluded as conclude6_1_0_, booking0_.schedule_id as schedule9_1_0_, booking0_.telephone_number_id as telepho10_1_0_, telephonen1_.isActive as isActive2_11_1_, telephonen1_.number as number3_11_1_, telephonen1_.securityToken as security4_11_1_, telephonen1_.token_id as token_id6_11_1_, telephonen1_.uniqueId as uniqueId5_11_1_, patient3_.birthDay as birthDay2_4_2_, patient3_.firstName as firstNam3_4_2_, patient3_.gender as gender4_4_2_, patient3_.height as height5_4_2_, patient3_.lastName as lastName6_4_2_, patient3_.picture as picture7_4_2_, patient3_.pictureContentType as pictureC8_4_2_, patient3_.weight as weight9_4_2_, patients2_.telephonenumber_id as telephon2_5_0__, patients2_.patient_id as patient_1_5_0__
from Booking booking0_
    left outer join TelephoneNumber telephonen1_ on booking0_.telephone_number_id=telephonen1_.id
    left outer join Patient_TelephoneNumber patients2_ on telephonen1_.id=patients2_.telephonenumber_id
    left outer join Patient patient3_ on patients2_.patient_id=patient3_.id
    cross join Schedule schedule4_
where booking0_.schedule_id=schedule4_.id and booking0_.concluded=0 and schedule4_.doctor_id=? and booking0_.bookingDate=?

2020-01-12 18:58:34 TRACE binding parameter [1] as [BIGINT] - [1]
2020-01-12 18:58:34 TRACE binding parameter [2] as [DATE] - [2020-01-12]

So if added those values to the generated SQL and run the query against the DB directly, that will give me two results (Expected).

And whenever I remove the "date" value from the HQL also it will give results. Any thoughts on why this is happening?

Hibernate version: 5.4.9.Final

DB: Mysql 8

Java 11 (Open JDK)

WildFly 18.

Thanks!

If you don't get an acceptable answer using HQL, you may always run a native query. — Tim Biegeleisen, Jan 12 '20 at 16:28
@TimBiegeleisen Thanks for the idea. I'll keep that as my last resort. And another weird thing is I have two environments, Only differences are in those environments are Java minor version, MySQL minor, OS (Mac and Ubuntu) and Wildfly 17. Same HQL works in Wildfly 17 environment but not in this environment. (All artifacts are same as I copy pasted.) — Jayanga Kaushalya, Jan 12 '20 at 16:31
@JayangaKaushalya: Is it possible you've stored on your DB a date time value? Because if you put an equal on date without time your query don't return result. — Joe Taras, Jan 13 '20 at 07:39

score 0 · Accepted Answer · answered Jan 16 '20 at 12:09

Reason behind this was a TimeZone issue. My application server and the DB server were in two different time zones. So whenever you try to retrieve through JDBC, it converts your date to the DB time zone due to this [1].

But the direct SQL works as it runs on the DB server and no time zone issues.

So my initial approach was to add the following property to connection URL (As suggested in the [1] bug). And it worked.

serverTimezone=<client_time_zone>

However, as permanent solution I changed the DB server time zone to application server timezone. (I know UTC is the correct way but I prefer this.)

[1] https://bugs.mysql.com/bug.php?id=91112

HQL does not return values even though generated SQL does

1 Answers1