pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
17+ messages / 6 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 07:21 "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx) @ 2025-10-22 07:21 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Please read https://stackoverflow.com/help/minimal-reproducible-example
**Describe the issue**
We discovered a wrong result returned by the equality operator when comparing a date and a timestamp when the date operand is explicitly casted, but not the timestamp:
`SELECT cast(? as date) = ? as eq1`
returns `true`
- with first parameter being `java.sql.Date.valueOf(LocalDate.of(2013, 03, 21))`
- and second parameter is ` java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14))`
we obtain the same result with the `::` syntax: `SELECT ?::date = ? as eq1`
**Driver Version?**
42.7.8
**Java Version?**
21
**OS Version?**
Debian 6.12.32-1 (2025-06-07) x86_64 GNU/Linux
**PostgreSQL Version?**
17.5-1.pgdg120+1
**To Reproduce**
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
public class Repro {
public static void main(String[] args) throws SQLException {
String url = "jdbc:postgresql://localhost:5432/postgres";
String user = "postgres";
String password = "mysecretpassword";
Connection conn = DriverManager.getConnection(url, user, password);
var date = java.sql.Date.valueOf(LocalDate.of(2013, 03, 21));
var timestamp = java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14));
var preparedStatement = conn.prepareStatement(
"SELECT ? = ? as eq1," +
" ? = cast(? as timestamp) as eq2," +
" cast(? as date) = ? as eq3," +
" cast(? as date) = cast(? as timestamp) as eq4," +
" ? = ? as eq5," +
" ? = cast(? as date) as eq6," +
" cast(? as timestamp) = ? as eq7," +
" cast(? as timestamp) = cast(? as date) as eq8");
preparedStatement.setDate(1, date);
preparedStatement.setTimestamp(2, timestamp);
preparedStatement.setDate(3, date);
preparedStatement.setTimestamp(4, timestamp);
preparedStatement.setDate(5, date);
preparedStatement.setTimestamp(6, timestamp);
preparedStatement.setDate(7, date);
preparedStatement.setTimestamp(8, timestamp);
preparedStatement.setTimestamp(9, timestamp);
preparedStatement.setDate(10, date);
preparedStatement.setTimestamp(11, timestamp);
preparedStatement.setDate(12, date);
preparedStatement.setTimestamp(13, timestamp);
preparedStatement.setDate(14, date);
preparedStatement.setTimestamp(15, timestamp);
preparedStatement.setDate(16, date);
ResultSet rs = preparedStatement.executeQuery();
rs.next();
for (var col : List.of("eq1", "eq2", "eq3", "eq4", "eq5", "eq6", "eq7", "eq8")) {
System.out.println(col + ": " + rs.getBoolean(col));
}
}
}
```
**Expected behaviour**
This code outputs:
```
eq1: false
eq2: false
eq3: true
eq4: false
eq5: false
eq6: true
eq7: false
eq8: false
```
we would expect all expressions evaluated to false
```
eq1: false
eq2: false
eq3: false
eq4: false
eq5: false
eq6: false
eq7: false
eq8: false
```
** Note**
I directly made a simple test on dbfiddle to test if the problem does not comes from posgresql itself and I was not able to reproduce this issue:
<img width="1054" height="494" alt="Image" src="https://github.com/user-attachments/assets/edb085b7-c41f-4a91-8a63-0d415805c96e"; />
So I suspect that his is the jdbc driver that does not communicate correctly the value/type of a timestamp parameter.
**Logs**
No special logs output.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 08:24 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-10-22 08:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Hi,
It feels like the jdbc driver is not properly propagating the type of the parameters, for example if I do:
```java
var date = java.sql.Date.valueOf(LocalDate.of(2013, 03, 21));
var timestamp = java.sql.Timestamp.valueOf(LocalDateTime.of(2013, 03, 21, 13, 14));
var preparedStatement = conn.prepareStatement("SELECT pg_typeof(?), pg_typeof(?)");
preparedStatement.setDate(1, date);
preparedStatement.setTimestamp(2, timestamp);
```
I get the error: ERROR: could not determine data type of parameter $1
This means that the problem is not only with timestamp, but also with date.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 08:49 ` "vlsi (@vlsi)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: vlsi (@vlsi) @ 2025-10-22 08:49 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
What do you mean by "properly"?
Frankly, I would suggest to use `java.time` types instead of `java.sql` types.
Is there a reason you have to use `java.sql` types without casts?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 08:57 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-10-22 08:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi
> What do you mean by "properly"?
I guess I mean that when you look at the implementation of `PgPreparedStatement#setDate(int, java.sql.Date, java.util.Calendar)` or `PgPreparedStatement#setTimestamp(int, java.sql.Timestamp, java.util.Calendar)`, you can see it's using `Oid.UNSPECIFIED` most of the time to propagate the type to the server.
I just discovered this, I didn't know it when I wrote my first comment but I was intuiting this kind of thing.
> Frankly, I would suggest to use java.time types instead of java.sql types.
So you mean one should be doing `setObject(LocalDate.of(2013, 03, 21))` instead of `setDate(java.sql.Date.valueOf(LocalDate.of(2013, 03, 21)))` right?
It's indeed a good workaround right now, it's unfortunately not followed by libs like Hibernate that will directly use `setTimestamp()` or `setDate()` but I guess it's also workaroundable.
> Is there a reason you have to use java.sql types without casts?
Is there a reason that we should be using casts? I mean: the only workaround currently is to do use casts, so that's what is going to be done, but we thought there was maybe something wrong going on a the driver level and decided to start a discussion here about it.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 09:16 ` "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx) @ 2025-10-22 09:16 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
I can confirm that using directly `java.time` types and `setObject()` works as expected.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 09:53 ` "vlsi (@vlsi)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: vlsi (@vlsi) @ 2025-10-22 09:53 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
`Oid.DATE`, `Oid.TIMESTAMP`, and `Oid.TIMESTAMPTZ` differ.
At the same time, it is hard to tell if `.setTimestamp` means `TIMESTAMP` or `TIMESTAMPTZ`.
I'm not sure if it is the only reason for going with `UNSPECIFIED` when sending the dates to the backend, however, it looks like the primary way of moving forward should be `java.time` types.
Is there anything stopping you from using `java.time` with Hibernate?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:11 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-10-22 10:11 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> Oid.DATE, Oid.TIMESTAMP, and Oid.TIMESTAMPTZ differ.
> At the same time, it is hard to tell if .setTimestamp means TIMESTAMP or TIMESTAMPTZ.
So basically, this is a known behaviour of the driver and there is no way to improve it? Thanks for the explanation, it's unfortunate because it's very error-prone, but I guess we have no real choice here.
> Is there anything stopping you from using java.time with Hibernate?
Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to `java.sql.Timestamp` before calling `setTimestamp` on the statement :)
But the easy workaround is to use a cast. I may check if the currently supported version of Hibernate still have this problem (we are stuck with v5) and if they do I will report a bug there.
Feel free to close this ticket if you think there is nothing more that can be done on the driver side :)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:24 ` "vlsi (@vlsi)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: vlsi (@vlsi) @ 2025-10-22 10:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> So basically, this is a known behaviour of the driver and there is no way to improve it?
I'm afraid it would be extremely hard to improve the behavior due to backward compatibility reasons.
> it's very error-prone
This is fair. I've filed an enhancement request for error-prone: https://github.com/google/error-prone/issues/5291
>Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to java.sql.Timestamp before calling setTimestamp on the statement :)
Is it really the case?
Frankly, I think pgjdbc supports `java.time` for quite some time already, and ORMs should migrate to that API as it is a much cleaner mapping between Java and database types.
If Hibernate still uses `java.sql`, I would suggest filing a ticket to make them migrate to `java.time` instead.
They should raise warnings if the application still uses `java.sql` fields.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:29 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-10-22 10:29 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> Is it really the case?
At least on Hibernate 5, see the `doBind` methods in https://github.com/hibernate/hibernate-orm/blob/5.6/hibernate-core/src/main/java/org/hibernate/type/...
> Frankly, I think pgjdbc supports java.time for quite some time already, and ORMs should migrate to that API as it is a much cleaner mapping between Java and database types.
Yep, I agree, I will followup with Hibernate on this (for v6+ as v5 is no longer supported)
> They should raise warnings if the application still uses java.sql fields.
Just to be clear, this is even worst: even if the application is not, their code is (at least on v5, again ;)
Great idea with the error-prone ticket!!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:30 ` "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx) @ 2025-10-22 10:30 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi Just one thing. I understand it's not possible to tell exactly what parameter type it is when we use `setTimestamp()` but I don't get the reason why we couldn't determine the type of the parameter given by `setDate()`. As stated in the documentation `java.sql.Date` type is supposed to be used with zero valued time fields hence that should be exactly a sql `date` without any ambiguity.
```
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
public class Repro {
public static void main(String[] args) throws SQLException {
String url = "jdbc:postgresql://localhost:5432/postgres";
String user = "postgres";
String password = "mysecretpassword";
Connection conn = DriverManager.getConnection(url, user, password);
var date = java.sql.Date.valueOf(LocalDate.of(2013, 03, 21));
var preparedStatement = conn.prepareStatement(
"SELECT pg_typeof(?) as theType");
preparedStatement.setDate(1, date);
ResultSet rs = preparedStatement.executeQuery();
rs.next();
System.out.println("theType" + ": " + rs.getString("theType"));
}
}
```
this code throws the following exception and that's seams like not normal to me:
```
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2736)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:525)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:435)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:196)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:139)
at Repro.main(Repro.java:22)
```
I understand this is related to an old api, maybe that's not worth fixing that. I'm just thinking there is a maybe a very quick fix to provide the correct type.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:33 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-10-22 10:33 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
About Hibernate 6, a quick check shows that it uses `setObject` directly with the `java.time` object, so it should be fine there!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 10:47 ` "tbroyer (@tbroyer)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: tbroyer (@tbroyer) @ 2025-10-22 10:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
And AFAICT, `java.sql.Timestamp` is by definition a `timestamp`, not a `timestamptz`
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 12:32 ` "davecramer (@davecramer)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: davecramer (@davecramer) @ 2025-10-22 12:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> And AFAICT, `java.sql.Timestamp` is by definition a `timestamp`, not a `timestamptz`
I gather you are suggesting that we should always treat it as a timestamp then? If so how would one set a timestamptz in older versions of the JDK ?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-22 13:01 ` "tbroyer (@tbroyer)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: tbroyer (@tbroyer) @ 2025-10-22 13:01 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> > And AFAICT, `java.sql.Timestamp` is by definition a `timestamp`, not a `timestamptz`
>
> I gather you are suggesting that we should always treat it as a timestamp then?
Yes
> If so how would one set a timestamptz in older versions of the JDK ?
You can't.
I don't make the rules, the JCP does.
And [JDBC 4.3 (pdf)](https://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf) only lists TIMESTAMP_WITH_TIMEZONE associated with java.time.OffsetDateTime when java.sql.Timestamp is only ever associated with TIMESTAMP, and [JDBC 2.1](https://web.archive.org/web/20001018172021/http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/app...) which added "support for time zones" back in the day only added the `setTimestamp()` overloads taking an additional `Calendar` argument, but note that [the javadoc](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html#setTimes...)) says it uses the `Calendar` object to determine the time zone to use to compute the value instead of the local time zone, but still constructs a `TIMESTAMP` SQL value!
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-10-23 04:22 ` "davecramer (@davecramer)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: davecramer (@davecramer) @ 2025-10-23 04:22 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> I don't make the rules, the JCP does.
>
> And [JDBC 4.3 (pdf)](https://download.oracle.com/otn-pub/jcp/jdbc-4_3-mrel3-eval-spec/jdbc4.3-fr-spec.pdf) only lists TIMESTAMP_WITH_TIMEZONE associated with java.time.OffsetDateTime when java.sql.Timestamp is only ever associated with TIMESTAMP, and [JDBC 2.1](https://web.archive.org/web/20001018172021/http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/app...) which added "support for time zones" back in the day only added the `setTimestamp()` overloads taking an additional `Calendar` argument, but note that [the javadoc](https://docs.oracle.com/en/java/javase/25/docs/api/java.sql/java/sql/PreparedStatement.html#setTimes...)) says it uses the `Calendar` object to determine the time zone to use to compute the value instead of the local time zone, but still constructs a `TIMESTAMP` SQL value!
Well, Postgres has always had 2 timestamp types and the driver is for Postgres so we support both. I'll also note that timestamp is of questionable value https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don't_use_timestamp_(without_time_zone)
Either way we will continue to support both as we attempt to provide a client that can read and write all postgres types
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-12-04 09:32 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: m-van-tilburg (@m-van-tilburg) @ 2025-12-04 09:32 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> Nothing, the problem is Hibernate itself, it's implementing the use of those types by converting them to `java.sql.Timestamp` before calling `setTimestamp` on the statement :)
Are you aware that recent Hibernate versions have a setting to control this behavior? See [JAVA_TIME_USE_DIRECT_JDBC](https://docs.hibernate.org/orm/7.2/javadocs/org/hibernate/cfg/MappingSettings.html#JAVA_TIME_USE_DIR...)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
@ 2025-12-04 10:10 ` "victornoel (@victornoel)" <[email protected]>
15 siblings, 0 replies; 17+ messages in thread
From: victornoel (@victornoel) @ 2025-12-04 10:10 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@m-van-tilburg yep indeed, I'm still stuck with Hibernate 5 but this would the proper way to deal with this problem with Hibernate 6+, thanks for mentioning it :)
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2025-12-04 10:10 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-22 07:21 [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
2025-10-22 08:24 ` "victornoel (@victornoel)" <[email protected]>
2025-10-22 08:49 ` "vlsi (@vlsi)" <[email protected]>
2025-10-22 08:57 ` "victornoel (@victornoel)" <[email protected]>
2025-10-22 09:16 ` "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
2025-10-22 09:53 ` "vlsi (@vlsi)" <[email protected]>
2025-10-22 10:11 ` "victornoel (@victornoel)" <[email protected]>
2025-10-22 10:24 ` "vlsi (@vlsi)" <[email protected]>
2025-10-22 10:29 ` "victornoel (@victornoel)" <[email protected]>
2025-10-22 10:30 ` "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <[email protected]>
2025-10-22 10:33 ` "victornoel (@victornoel)" <[email protected]>
2025-10-22 10:47 ` "tbroyer (@tbroyer)" <[email protected]>
2025-10-22 12:32 ` "davecramer (@davecramer)" <[email protected]>
2025-10-22 13:01 ` "tbroyer (@tbroyer)" <[email protected]>
2025-10-23 04:22 ` "davecramer (@davecramer)" <[email protected]>
2025-12-04 09:32 ` "m-van-tilburg (@m-van-tilburg)" <[email protected]>
2025-12-04 10:10 ` "victornoel (@victornoel)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox