Thread: Parameter marker swapping in {fn timestampdiff()}

version info:

    Database Product Name PostgreSQL
    Database Product Version 14.2
    JDBC Driver Name PostgreSQL JDBC Driver
    JDBC Driver Version 42.5.3

I recently made a small change to a core utility and our sql tests flagged some unexpected results.  I traced these to timestampdiff().  Below is a running code function (except for creating the JDBC Connection).  I expect the same result for all three executeQuery() calls.  The version that uses string literals return 366 and the versions that use parameter markers return -366.

Output:

    w/o parameters: 366
    w/ parameters: -366
    w/ parameters varchar: -366

    void testTimestampDiffParameters(Connection conn) throws SQLException
    {
        // WITHOUT PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST('01 Jan 2000 12:00' AS TIMESTAMP), CAST('01 Jan 2001 12:00' AS TIMESTAMP))"))
        {
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/o parameters: " + rs.getInt(1));
            }
        }

        // WITH PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(? AS TIMESTAMP), CAST(? AS TIMESTAMP))"))
        {
            stmt.setString(1,"01 Jan 2000 12:00");
            stmt.setString(2,"01 Jan 2001 12:00");
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/ parameters: " + rs.getInt(1));
            }
        }

        // WITH PARAMETERS
        try (PreparedStatement stmt = conn.prepareStatement("SELECT {fn TIMESTAMPDIFF(SQL_TSI_DAY, CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP), CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP))"))
        {
            stmt.setString(1,"01 Jan 2000 12:00");
            stmt.setString(2,"01 Jan 2001 12:00");
            try (ResultSet rs = stmt.executeQuery())
            {
                rs.next();
                System.out.println("w/ parameters varchar: " + rs.getInt(1));
            }
        }
    }

Since the parameters to DateDiff are swapped from {fn timestampdiff()}, the obvious guess would be that the driver is swapping the arguments, but not remapping the JDBC parameter indexes to the new swapped location.

Thank you,

Matt

Thanks, reposted.