pg.ddx.io GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)
help / color / mirror / Atom feedFrom: lunarbae628 (@lunarbae628) <noreply+lunarbae628@github.com>
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
Subject: [pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff
Date: Fri, 13 Nov 2026 07:04:58 +0000
Message-ID: <gh-pgjdbc-pgjdbc-4529@github.com> (raw)
### All Submissions:
- [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
- [x] Have you checked to ensure there aren't other open [Pull Requests](https://github.com/pgjdbc/pgjdbc/pulls) for the same update/change?
### Changes to Existing Features:
- Does this break existing behaviour? No public API changes. Bound timestamp arguments now produce the same result as literals.
- [x] Have you added an explanation of what your changes do and why you'd like us to include them?
- [x] Have you written new tests for your core changes, as applicable?
- [x] Have you successfully run tests with your changes locally?
### What this changes
Fixes #4527.
For a 366-day difference, `timestampdiff` returns `-366` with bound timestamps but `366` with literals. The escape rewrite swaps the arguments before the parser numbers the parameter markers.
The rewrite now uses `-(start - end)` instead of `end - start`, so the markers stay in their original order. The deprecated `EscapedFunctions` helper is updated too.
The separate total-unit calculation issue in #4527 is unchanged.
### Tests
The new tests compare literal and bound timestamps for day, hour, minute, and second intervals, including reversed dates, equal dates, nulls, and nested expressions. The 366-day case returns `-366` without the fix and `366` with it.
Ran `ParserTest`, `StatementTest`, and `PreparedStatementTest` on Java 21 with PostgreSQL 17.11 and 17.12: 195 passed, 7 skipped, no failures on each version. `./gradlew styleCheck` passes.
The full run failed in `DriverSupportsClassUnloadingTest.driverUnloadsWhenConnectionLeaks` (`pgjdbc-junit4-test`). That test also fails with the original production sources. The driver tests had no failures.
inline patch 1: pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions.java, pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java, pgjdbc/src/test/java/org/postgresql/core/ParserTest.java and 1 more (+121/-8 lines) download inline-1.patch
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions.java b/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions.java
index 8957f0962b..2a61c2eb12 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions.java
@@ -642,13 +642,14 @@ public static String sqltimestampdiff(List<? extends Object> parsedArgs) throws
}
String datePart = EscapedFunctions.constantToDatePart(parsedArgs.get(0).toString());
StringBuilder buf = new StringBuilder();
+ // Keep the argument order so parameter indexes remain unchanged.
buf.append("extract( ")
.append(datePart)
- .append(" from (")
- .append(parsedArgs.get(2))
- .append("-")
+ .append(" from (-((")
.append(parsedArgs.get(1))
- .append("))");
+ .append(")-(")
+ .append(parsedArgs.get(2))
+ .append("))))");
return buf.toString();
}
diff --git a/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java b/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java
index 9360c8e5e0..c04a48592b 100644
--- a/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java
+++ b/pgjdbc/src/main/java/org/postgresql/jdbc/EscapedFunctions2.java
@@ -598,13 +598,14 @@ public static void sqltimestampdiff(StringBuilder buf, List<? extends CharSequen
GT.tr("{0} function takes three and only three arguments.", "timestampdiff"),
PSQLState.SYNTAX_ERROR);
}
+ // Keep the argument order so parameter indexes remain unchanged.
buf.append("extract( ")
.append(constantToDatePart(parsedArgs.get(0).toString()))
- .append(" from (")
- .append(parsedArgs.get(2))
- .append("-")
+ .append(" from (-((")
.append(parsedArgs.get(1))
- .append("))");
+ .append(")-(")
+ .append(parsedArgs.get(2))
+ .append("))))");
}
private static String constantToDatePart(String type) throws SQLException {
diff --git a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
index fdca32526a..8a4e61c32d 100644
--- a/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/core/ParserTest.java
@@ -21,6 +21,7 @@
import org.junit.jupiter.params.provider.ValueSource;
import java.sql.SQLException;
+import java.util.Arrays;
import java.util.List;
/**
@@ -142,6 +143,36 @@ void escapeProcessing() throws Exception {
assertEquals("{obj : 1}", Parser.replaceProcessing("{obj : 1}", true, false));
}
+ @Test
+ void timestampDiffPreservesParameterOrder() throws SQLException {
+ // Escape processing runs before parameter numbering. Different casts let the test
+ // distinguish the two timestamp arguments.
+ String sql = "SELECT ?, {fn timestampdiff(SQL_TSI_DAY,CAST(? AS timestamp),?::timestamp)}, ?";
+ String rewritten = Parser.replaceProcessing(sql, true, true);
+ NativeQuery query = Parser.parseJdbcSql(rewritten, true, true, true, false, false).get(0);
+
+ assertEquals("SELECT $1, extract( day from (-((CAST($2 AS timestamp))-($3::timestamp)))), $4",
+ query.nativeSql);
+ }
+
+ @Test
+ @SuppressWarnings("deprecation")
+ void legacyTimestampDiffPreservesArgumentOrder() throws SQLException {
+ // The deprecated public helper must keep the same behavior as the parser's implementation.
+ assertEquals("extract( day from (-((start_ts)-(end_ts))))",
+ org.postgresql.jdbc.EscapedFunctions.sqltimestampdiff(
+ Arrays.asList("SQL_TSI_DAY", "start_ts", "end_ts")));
+ assertEquals("extract( hour from (-((start_ts)-(end_ts))))",
+ org.postgresql.jdbc.EscapedFunctions.sqltimestampdiff(
+ Arrays.asList("SQL_TSI_HOUR", "start_ts", "end_ts")));
+ assertEquals("extract( minute from (-((start_ts)-(end_ts))))",
+ org.postgresql.jdbc.EscapedFunctions.sqltimestampdiff(
+ Arrays.asList("SQL_TSI_MINUTE", "start_ts", "end_ts")));
+ assertEquals("extract( second from (-((start_ts)-(end_ts))))",
+ org.postgresql.jdbc.EscapedFunctions.sqltimestampdiff(
+ Arrays.asList("SQL_TSI_SECOND", "start_ts", "end_ts")));
+ }
+
@Test
void timestampAddDiffFracSecondIsRejected() throws Exception {
// SQL_TSI_FRAC_SECOND has no portable size across databases (nanoseconds in ODBC/SQL Server,
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
index 2fcfa6b5d0..35571f22ec 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/StatementTest.java
@@ -37,6 +37,7 @@
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
+import java.sql.Timestamp;
import java.time.Duration;
import java.util.ArrayList;
import java.util.List;
@@ -371,6 +372,85 @@ void dateFuncWithParam() throws SQLException {
assertEquals(rs.getTimestamp(1), rs.getTimestamp(2));
}
+ @Test
+ void timestampDiffWithDayParameters() throws SQLException {
+ // 2024 is a leap year; reversing the dates must change only the sign.
+ assertTimestampDiff("SQL_TSI_DAY", "2024-01-01 00:00:00", "2025-01-01 00:00:00", 366);
+ assertTimestampDiff("SQL_TSI_DAY", "2025-01-01 00:00:00", "2024-01-01 00:00:00", -366);
+ assertTimestampDiff("SQL_TSI_DAY", "2024-01-01 00:00:00", "2024-01-01 00:00:00", 0);
+ }
+
+ @Test
+ void timestampDiffWithHourParameters() throws SQLException {
+ assertTimestampDiff("SQL_TSI_HOUR", "2024-01-01 01:00:00", "2024-01-01 04:00:00", 3);
+ assertTimestampDiff("SQL_TSI_HOUR", "2024-01-01 04:00:00", "2024-01-01 01:00:00", -3);
+ }
+
+ @Test
+ void timestampDiffWithMinuteParameters() throws SQLException {
+ assertTimestampDiff("SQL_TSI_MINUTE", "2024-01-01 00:01:00", "2024-01-01 00:04:00", 3);
+ assertTimestampDiff("SQL_TSI_MINUTE", "2024-01-01 00:04:00", "2024-01-01 00:01:00", -3);
+ }
+
+ @Test
+ void timestampDiffWithSecondParameters() throws SQLException {
+ assertTimestampDiff("SQL_TSI_SECOND", "2024-01-01 00:00:01", "2024-01-01 00:00:04", 3);
+ assertTimestampDiff("SQL_TSI_SECOND", "2024-01-01 00:00:04", "2024-01-01 00:00:01", -3);
+ }
+
+ private void assertTimestampDiff(String unit, String start, String end, int expected)
+ throws SQLException {
+ try (Statement stmt = con.createStatement();
+ ResultSet rs = stmt.executeQuery("SELECT {fn timestampdiff(" + unit
+ + ",{ts '" + start + "'},{ts '" + end + "'})}")) {
+ assertTrue(rs.next());
+ assertEquals(expected, rs.getInt(1), "Timestamp literals");
+ }
+
+ try (PreparedStatement ps = con.prepareStatement("SELECT {fn timestampdiff(" + unit
+ + ",CAST(? AS timestamp),CAST(? AS timestamp))}")) {
+ ps.setTimestamp(1, Timestamp.valueOf(start));
+ ps.setTimestamp(2, Timestamp.valueOf(end));
+ try (ResultSet rs = ps.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(expected, rs.getInt(1), "Timestamp parameters");
+ }
+ }
+ }
+
+ @Test
+ void timestampDiffWithExpressionsAndSurroundingParameters() throws SQLException {
+ try (PreparedStatement ps = con.prepareStatement("SELECT ?, {fn timestampdiff(SQL_TSI_HOUR,"
+ + "CAST(? AS timestamp) + INTERVAL '1 hour',"
+ + "{fn timestampadd(SQL_TSI_HOUR,?,CAST(? AS timestamp))})}, ?")) {
+ ps.setInt(1, 11);
+ ps.setTimestamp(2, Timestamp.valueOf("2024-01-01 00:00:00"));
+ ps.setInt(3, 3);
+ ps.setTimestamp(4, Timestamp.valueOf("2024-01-01 04:00:00"));
+ ps.setInt(5, 22);
+ try (ResultSet rs = ps.executeQuery()) {
+ assertTrue(rs.next());
+ assertEquals(11, rs.getInt(1));
+ // (04:00 + 3 hours) - (00:00 + 1 hour) = 6 hours.
+ assertEquals(6, rs.getInt(2));
+ assertEquals(22, rs.getInt(3));
+ }
+ }
+ }
+
+ @Test
+ void timestampDiffWithNullParameter() throws SQLException {
+ try (PreparedStatement ps = con.prepareStatement(
+ "SELECT {fn timestampdiff(SQL_TSI_DAY,CAST(? AS timestamp),CAST(? AS timestamp))}")) {
+ ps.setTimestamp(1, null);
+ ps.setTimestamp(2, Timestamp.valueOf("2024-01-01 00:00:00"));
+ try (ResultSet rs = ps.executeQuery()) {
+ assertTrue(rs.next());
+ assertNull(rs.getObject(1));
+ }
+ }
+ }
+
@Test
void dateFunctions() throws SQLException {
Statement stmt = con.createStatement();
view thread (2+ messages) latest in thread
Message-ID: <gh-pgjdbc-pgjdbc-4529@github.com>
Permalink: ../gh-pgjdbc-pgjdbc-4529@github.com/
· ·
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox