pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
Date: Wed, 22 Oct 2025 07:21:52 +0000
Message-ID: <[email protected]> (raw)

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.

view thread (17+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox