pg.ddx.io  GitHub: pgjdbc/pgjdbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
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.






view thread (3+ messages)  latest in thread

Message-ID: <gh-pgjdbc-pgjdbc-4527@github.com>
Permalink:  ../gh-pgjdbc-pgjdbc-4527@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