Message-ID: <gh-pgjdbc-pgjdbc-3845@github.com>
From: "sylvain-lemouzy-pfx (@sylvain-lemouzy-pfx)" <noreply+sylvain-lemouzy-pfx@github.com>
To: "pgjdbc/pgjdbc" <noreply+pgjdbc-pgjdbc@github.com>
Date: Wed, 22 Oct 2025 07:21:52 +0000
Subject: [pgjdbc/pgjdbc] issue #3845: Wrong result on equality between Date and Timestamp given as query parameter
List-Id: <gh-pgjdbc-pgjdbc.github.com>
X-GitHub-Author-Id: 44262766
X-GitHub-Author-Login: sylvain-lemouzy-pfx
X-GitHub-Issue: 3845
X-GitHub-Repo: pgjdbc/pgjdbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/3845
Content-Type: text/plain; charset=utf-8

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.