From: labkey-matthewb (@labkey-matthewb) <noreply+labkey-matthewb@github.com>
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
Subject: [pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers
Date: Tue, 10 Nov 2026 17:40:57 +0000
Message-ID: <gh-pgjdbc-pgjdbc-4527@github.com> (raw)
I posted this bug incorrectly to pgsql years ago. Here is the link with my original repro: https://www.postgresql.org/message-id/CAJnjrPND0MiidV+zRgzmL4zb5oYv9TgCUwtYqD4yW3+k4Cc+Cg@mail.gmail....
Here is new write up from claude with references to the actual source:
To summarize: {fn timestampdiff(SQL_TSI_DAY, ...)} with string literal arguments returns 366, but the same call written with parameter markers, {fn timestampdiff(SQL_TSI_DAY, ?, ?)} with the same two values bound, returns -366.
I looked at the driver source (master, 9c6eeaa) and here is the cause.
EscapedFunctions2.sqltimestampdiff() emits the arguments in reverse order:
buf.append("extract( ")
.append(constantToDatePart(parsedArgs.get(0).toString()))
.append(" from (")
.append(parsedArgs.get(2))
.append("-")
.append(parsedArgs.get(1))
.append("))");
CachedQueryCreateAction runs Parser.replaceProcessing() on the SQL text first, and only then does Parser.parseJdbcSql() number the placeholders. So the "?" from argument 2 is now the first placeholder in the text and becomes $1. The value the application binds to parameter 1 ends up as the minuend.
Running the two parser steps directly against 42.7.14 shows this:
String sql = "SELECT {fn timestampdiff(SQL_TSI_DAY, ?, ?)}";
String rewritten = Parser.replaceProcessing(sql, true, true);
// SELECT extract( day from ( ?- ?))
NativeQuery q = Parser.parseJdbcSql(rewritten, true, true, true, false, false).get(0);
// q.nativeSql: SELECT extract( day from ( $1- $2))
The legacy EscapedFunctions.sqltimestampdiff() has the same ordering. As far as I can tell, timestampdiff is the only escape function that reorders its arguments. The existing tests in StatementTest only use nested {fn now()} calls, never parameter markers, so they don't catch this.
A minimal fix would be to keep the arguments in their original order, e.g.:
extract(day from (-(arg1 - arg2)))
The more general fix would be to have the escape parser renumber placeholders when an escape changes their order.
Separately, there's still the issue raised here in 2015: extract(minute from interval) returns the minute field of the interval rather than the total number of minutes:
https://www.postgresql.org/message-id/CAJnjrPNSNE=YQeawBgrqay=J_8g9SmKmbB4Gt2W0Mx06bekN8A@mail.gmail...
Something like extract(epoch from (arg2 - arg1)) / <seconds per unit> would fix that, but it would still need one of the ordering fixes above.