public inbox for [email protected]  

help / color / mirror / Atom feed

From: Matthew Bellew <[email protected]>
To: [email protected]
Subject: Parameter marker swapping in {fn timestampdiff()}
Date: Sun, 26 Mar 2023 15:23:01 -0700
Message-ID: <CAJnjrPOC6=nMs-Kb07xOwszJDFzeMaV67MBA9+onMLxdqG3TgA@mail.gmail.com> (raw)

(moved/reposted from pgsql-bugs)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Parameter marker swapping in {fn timestampdiff()}
  In-Reply-To: <CAJnjrPOC6=nMs-Kb07xOwszJDFzeMaV67MBA9+onMLxdqG3TgA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox