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

Parameter marker swapping in {fn timestampdiff()}

From
Matthew Bellew
Date:
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

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

From
Tom Lane
Date:
Matthew Bellew <matthewb@labkey.com> writes:
> 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.

You probably want to report that on the pgsql-jdbc list.  This list
mainly deals in bugs in core Postgres, so I'm not very sure whether
the JDBC maintainers read it.

            regards, tom lane



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

From
Matthew Bellew
Date:
Thanks, reposted.

On Sun, Mar 26, 2023 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthew Bellew <matthewb@labkey.com> writes:
> 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.

You probably want to report that on the pgsql-jdbc list.  This list
mainly deals in bugs in core Postgres, so I'm not very sure whether
the JDBC maintainers read it.

                        regards, tom lane