pg.ddx.io  GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
[pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff
2+ messages / 2 participants
[nested] [flat]

* [pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff
@ 2026-11-13 07:04 "lunarbae628 (@lunarbae628)" <noreply+lunarbae628@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: lunarbae628 (@lunarbae628) @ 2026-11-13 07:04 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

### 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.patchdiff --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();
^ permalink raw [nested|flat] 2+ messages in thread

* Re: [pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff
@ 2026-11-13 11:27 "codecov[bot] (bot) (@codecov[bot])" <noreply+codecov[bot]@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: codecov[bot] (bot) (@codecov[bot]) @ 2026-11-13 11:27 UTC (permalink / raw)
  To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>

## [Codecov](https://app.codecov.io/gh/pgjdbc/pgjdbc/pull/4529?dropdown=coverage&src=pr&el=h1&utm_med...) Report
:white_check_mark: All modified and coverable lines are covered by tests.
:white_check_mark: Project coverage is 72.46%. Comparing base ([`dca5b05`](https://app.codecov.io/gh/pgjdbc/pgjdbc/commit/dca5b058748b0428ee048749ea759476c584152b?dropdown=cov...)) to head ([`dc80d8f`](https://app.codecov.io/gh/pgjdbc/pgjdbc/commit/dc80d8f04ce7f0818349a188e578c73f74c63320?dropdown=cov...)).
:warning: Report is 1 commits behind head on master.

<details><summary>Additional details and impacted files</summary>



```diff
@@             Coverage Diff              @@
##             master    #4529      +/-   ##
============================================
+ Coverage     72.34%   72.46%   +0.12%     
- Complexity     5485     5494       +9     
============================================
  Files           244      244              
  Lines         22195    22195              
  Branches       3731     3731              
============================================
+ Hits          16056    16083      +27     
+ Misses         4470     4436      -34     
- Partials       1669     1676       +7     
```
</details>
<details><summary> :rocket: New features to boost your workflow: </summary>

- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
</details>

^ permalink  raw  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-13 11:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-13 07:04 [pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff "lunarbae628 (@lunarbae628)" <noreply+lunarbae628@github.com>
2026-11-13 11:27 Re: [pgjdbc/pgjdbc] PR #4529: fix: preserve parameter order in timestampdiff "codecov[bot] (bot) (@codecov[bot])" <noreply+codecov[bot]@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