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
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
Thanks, reposted.
On Sun, Mar 26, 2023 at 11:54 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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). Iexpect
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