MIME-Version: 1.0
From: Matthew Bellew <matthewb@labkey.com>
Date: Sun, 26 Mar 2023 15:23:01 -0700
Message-ID: 
 <CAJnjrPOC6=nMs-Kb07xOwszJDFzeMaV67MBA9+onMLxdqG3TgA@mail.gmail.com>
Subject: Parameter marker swapping in {fn timestampdiff()}
To: pgsql-jdbc@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000609e9905f7d51217"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJnjrPOC6%3DnMs-Kb07xOwszJDFzeMaV67MBA9%2BonMLxdqG3TgA%40mail.gmail.com>
Precedence: bulk

--000000000000609e9905f7d51217
Content-Type: text/plain; charset="UTF-8"

(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

--000000000000609e9905f7d51217
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>(moved/reposted from pgsql-bugs)</div><div><br></div>=
<div><div dir=3D"ltr"><div>version info:</div><div>=C2=A0=C2=A0=C2=A0 Datab=
ase Product Name	PostgreSQL<br>=C2=A0=C2=A0=C2=A0 Database Product Version	=
14.2<br>=C2=A0=C2=A0=C2=A0 JDBC Driver Name	PostgreSQL JDBC Driver<br>=C2=
=A0=C2=A0=C2=A0 JDBC Driver Version	42.5.3</div><div><br></div><div>I
 recently made a small change to a core utility and our sql tests=20
flagged some unexpected results.=C2=A0 I traced these to timestampdiff().=
=C2=A0=20
Below is a running code function (except for creating the JDBC=20
Connection).=C2=A0 I expect the same result for all three executeQuery()=20
calls.=C2=A0 The version that uses string literals return 366 and the=20
versions that use parameter markers return -366.</div><div><br></div><div>O=
utput:</div><div>=C2=A0=C2=A0=C2=A0 w/o parameters: 366<br>=C2=A0=C2=A0=C2=
=A0 w/ parameters: -366 <br>=C2=A0=C2=A0=C2=A0 w/ parameters varchar: -366<=
/div><div><br></div><div>=C2=A0 =C2=A0 void testTimestampDiffParameters(Con=
nection conn) throws SQLException<br>=C2=A0 =C2=A0 {<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 // WITHOUT PARAMETERS<br>=C2=A0
 =C2=A0 =C2=A0 =C2=A0 try (PreparedStatement stmt =3D conn.prepareStatement=
(&quot;SELECT {fn=20
TIMESTAMPDIFF(SQL_TSI_DAY, CAST(&#39;01 Jan 2000 12:00&#39; AS TIMESTAMP),=
=20
CAST(&#39;01 Jan 2001 12:00&#39; AS TIMESTAMP))&quot;))<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 try (ResultSet=
 rs =3D stmt.executeQuery())<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 {=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rs.next();<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println(=
&quot;w/o parameters: &quot; + rs.getInt(1));<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 // WITH PARAMETERS<br>=C2=A0
 =C2=A0 =C2=A0 =C2=A0 try (PreparedStatement stmt =3D conn.prepareStatement=
(&quot;SELECT {fn=20
TIMESTAMPDIFF(SQL_TSI_DAY, CAST(? AS TIMESTAMP), CAST(? AS=20
TIMESTAMP))&quot;))<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 {<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 stmt.setString(1,&quot;01 Jan 2000 12:00&quot;);<b=
r>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stmt.setString(2,&quot;01 Jan 2=
001 12:00&quot;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 try (ResultS=
et rs =3D stmt.executeQuery())<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 rs.next();<br=
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 System.out.println=
(&quot;w/ parameters: &quot; + rs.getInt(1));<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 // WITH PARAMETERS<br>=C2=A0
 =C2=A0 =C2=A0 =C2=A0 try (PreparedStatement stmt =3D conn.prepareStatement=
(&quot;SELECT {fn=20
TIMESTAMPDIFF(SQL_TSI_DAY, CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP),=20
CAST(CAST(? AS VARCHAR(50)) AS TIMESTAMP))&quot;))<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 stmt.setString(1,&quo=
t;01 Jan 2000 12:00&quot;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 st=
mt.setString(2,&quot;01 Jan 2001 12:00&quot;);<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 try (ResultSet rs =3D stmt.executeQuery())<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 rs.next();<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 System.out.println(&quot;w/ parameters varchar: &quot; + rs.g=
etInt(1));<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 }</div><div><br></div><div>Since
 the parameters to DateDiff are swapped from {fn timestampdiff()}, the=20
obvious guess would be that the driver is swapping the arguments, but=20
not remapping the JDBC parameter indexes to the new swapped location.</div>=
<div><br></div><div>Thank you,</div><div>Matt<div class=3D"gmail-yj6qo"></d=
iv><div class=3D"gmail-adL"><br></div></div></div></div></div>

--000000000000609e9905f7d51217--