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