Обсуждение: Parameter marker swapping in {fn timestampdiff()}
(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
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
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));
}
}
}
{
// 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