pg.ddx.io GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers
3+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers
@ 2026-11-10 17:40 "labkey-matthewb (@labkey-matthewb)" <noreply+labkey-matthewb@github.com>
0 siblings, 0 replies; 3+ messages in thread
From: labkey-matthewb (@labkey-matthewb) @ 2026-11-10 17:40 UTC (permalink / raw)
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
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.
^ permalink raw [nested|flat] 3+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers
@ 2026-11-11 17:17 "lunarbae628 (@lunarbae628)" <noreply+lunarbae628@github.com>
1 sibling, 0 replies; 3+ messages in thread
From: lunarbae628 (@lunarbae628) @ 2026-11-11 17:17 UTC (permalink / raw)
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
Hi, I'd like to work on this issue.
^ permalink raw [nested|flat] 3+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers
@ 2026-11-11 17:47 "davecramer (@davecramer)" <noreply+davecramer@github.com>
1 sibling, 0 replies; 3+ messages in thread
From: davecramer (@davecramer) @ 2026-11-11 17:47 UTC (permalink / raw)
To: pgjdbc/pgjdbc <noreply+pgjdbc-pgjdbc@github.com>
> Hi, I'd like to work on this issue.
go ahead
^ permalink raw [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-11-11 17:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-10 17:40 [pgjdbc/pgjdbc] issue #4527: timestampdiff swaps arguments, but not parameter markers "labkey-matthewb (@labkey-matthewb)" <noreply+labkey-matthewb@github.com>
2026-11-11 17:17 ` "lunarbae628 (@lunarbae628)" <noreply+lunarbae628@github.com>
2026-11-11 17:47 ` "davecramer (@davecramer)" <noreply+davecramer@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