pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
3+ messages / 2 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
@ 2026-04-27 13:10 "labkey-martyp (@labkey-martyp)" <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: labkey-martyp (@labkey-martyp) @ 2026-04-27 13:10 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
**Describe the issue**
This [issue](https://www.postgresql.org/message-id/CAJnjrPND0MiidV%2BzRgzmL4zb5oYv9TgCUwtYqD4yW3%2Bk4Cc%2BCg%40ma...) was filed to postgres but needed to be filed here.
Timestampdiff swaps parameters when using parameter markers `?`.
**Driver Version**
42.7.9
**Java Version**
25.0.1+8-LTS
**OS Version**
Windows 11
**PostgreSQL Version**
18.3
**To Reproduce**
Steps to reproduce the behaviour:
Below is a running code function (except for creating the JDBC Connection). The version that uses string literals return 366 and the versions that use parameter markers return -366.
```java
static void testTimestampDiffParameters(Connection conn) throws SQLException
{
// WITHOUT PARAMETERS
try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST('01 Jan 2000 12:00' AS TIMESTAMP), CAST('01Jan 2001 12:00' AS TIMESTAMP))"))
{
try (ResultSet rs = stmt.executeQuery())
{
rs.next();
System.out.println("w/o parameters: " + rs.getInt(1));
}
}
// WITH PARAMETERS
try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(? AS TIMESTAMP), CAST(? AS TIMESTAMP))"))
{
stmt.setString(1,"01 Jan 2000 12:00");
stmt.setString(2,"01 Jan 2001 12:00");
try (ResultSet rs = stmt.executeQuery())
{
rs.next();
System.out.println("w/ parameters: " + rs.getInt(1));
}
}
// WITH PARAMETERS
try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP), CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP))"))
{
stmt.setString(1,"01 Jan 2000 12:00");
stmt.setString(2,"01 Jan 2001 12:00");
try (ResultSet rs = stmt.executeQuery())
{
rs.next();
System.out.println("w/ parameters varchar: " +
rs.getInt(1));
}
}
}
```
Since the parameters to DateDiff are swapped from {fn timestampdiff()}, the
obvious guess would be that the driver is swapping the arguments, but not
remapping the JDBC parameter indexes to the new swapped location.
**Expected behaviour**
Expected: The same result for all three executeQuery() calls.
Actual: Using string literals results in 366. Using parameter markers gets -366.
**Logs**
```text
Output:
w/o parameters: 366
w/ parameters: -366
w/ parameters varchar: -366
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
@ 2026-04-27 17:37 ` "svendiedrichsen (@svendiedrichsen)" <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: svendiedrichsen (@svendiedrichsen) @ 2026-04-27 17:37 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Is the TIMESTAMPDIFF function an official function from Postgres? I'm not able to find any docs for this.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping
@ 2026-04-27 21:17 ` "labkey-martyp (@labkey-martyp)" <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: labkey-martyp (@labkey-martyp) @ 2026-04-27 21:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Thanks for looking into it. I looked a bit more in the code and docs and it looks like the driver has an escape function [implementation](https://github.com/pgjdbc/pgjdbc/blob/b04fc46af6c207bc7ce9e788fea8c43d18b73d0f/pgjdbc/src/main/java/...) that will work when string literals are in the parameters, but for any escape scalar function if the output reorders the parameters then prepared statements are not supported (per the second paragraph [here](https://jdbc.postgresql.org/documentation/escapes/#escaped-scalar-functions)).
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-27 21:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-27 13:10 [pgjdbc/pgjdbc] issue #4039: timestampdiff parameter swapping "labkey-martyp (@labkey-martyp)" <[email protected]>
2026-04-27 17:37 ` "svendiedrichsen (@svendiedrichsen)" <[email protected]>
2026-04-27 21:17 ` "labkey-martyp (@labkey-martyp)" <[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