Thread: Bug with PreparedStatements using EXTRACT function

Hi!

I found a bug in the postgresql jdbc driver (I'm using version 9.1 build
901). Constructing a prepared statement that uses the EXTRACT function in
the following way results in an PSQLException when trying to execute the
prepared statement:

***** CODE *****
PreparedStatement stmt2 = conn.prepareStatement(
        "SELECT EXTRACT (YEAR FROM DATE ?)");

java.sql.Date date = new java.sql.Date(
        new GregorianCalendar(1990, 5, 7).getTime().getTime());

stmt2.setDate(1, date);

// here I get an SQL exception:
res = stmt2.executeQuery();
***** END CODE *****

The exception thrown is the following (in English the German "Syntaxfehler
bei »$1«" is "syntax error at $1"):

Exception in thread "main" org.postgresql.util.PSQLException: FEHLER:
Syntaxfehler bei »$1«
  Position: 31
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2102)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1835)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:500)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:388)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:273)
    at pitas.shipdbs.storage.Bug.main(Bug.java:42)

Executing a simple unprepared statement with an equal, unprepared query
doesn't result in a syntax error:

***** CODE ****
Statement stmt = conn.createStatement();

ResultSet res = stmt.executeQuery(
        "SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')");

res.next();

System.out.println("Year: " + res.getInt(1));
***** END CODE ****

A complete example that shows this bug is added at the end of the mail.

Regards,
Matthias Boehm


***** CODE *****

package bug;

import java.sql.*;
import java.util.*;

public class Bug {

    public static void main(String[] args) throws SQLException {

        // create connection...
        Connection conn = ...

        // ---- This does work ----
        Statement stmt = conn.createStatement();

        ResultSet res = stmt.executeQuery(
                "SELECT EXTRACT (YEAR FROM DATE '1990-06-07
+02:00:00')");

        res.next();

        System.out.println("Year: " + res.getInt(1));

        // ---- This doesn't work ----
        PreparedStatement stmt2 = conn.prepareStatement(
                "SELECT EXTRACT (YEAR FROM DATE ?)");

        java.sql.Date date = new java.sql.Date(
                new GregorianCalendar(1990, 5,
7).getTime().getTime());

        stmt2.setDate(1, date);

        // It doesn't work although the compiled statement with
values inserted
        // seems to be textually equal to the statement above:
        System.out.println(stmt2.toString());

        // here I get an SQL exception:
        res = stmt2.executeQuery();

        res.next();

        System.out.println("Year: " + res.getInt(1));


        conn.close();
    }

}


***** END CODE *****

Output of the program:

*****
Year: 1990
SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')
Exception in thread "main" org.postgresql.util.PSQLException: FEHLER:
Syntaxfehler bei »$1«
  Position: 32
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2102)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1835)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:500)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:388)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:273)
    at Bug.main(Bug.java:41)
*****

That's because the 'date' is not a cast but some sort of type
specification inherent to the expression. I don't understand the
details of the grammar, but you can see this if you try to run it via
PREPARE (the closest language-level thing you can use to JDBC's
parameterized statements):

cqdb=# prepare foo(date) as select extract (year from date '2000-01-01');
PREPARE
cqdb=# prepare bar(date) as select extract (year from date $1);
ERROR:  syntax error at or near "$1"
LINE 1: prepare bar(date) as select extract (year from date $1);
                                                            ^
cqdb=# prepare baz(date) as select extract (year from $1);
PREPARE
cqdb=#

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

> -----Ursprüngliche Nachricht-----
> Von: Maciek Sakrejda [mailto:msakrejda@truviso.com]
> Gesendet: Mittwoch, 28. März 2012 22:54
> An: Matthias Böhm
> Cc: pgsql-jdbc@postgresql.org
> Betreff: Re: [JDBC] Bug with PreparedStatements using EXTRACT function
>
> That's because the 'date' is not a cast but some sort of type
> specification inherent to the expression. I don't understand the
> details of the grammar, but you can see this if you try to run it via
> PREPARE (the closest language-level thing you can use to JDBC's
> parameterized statements):
>
> cqdb=# prepare foo(date) as select extract (year from date '2000-01-
> 01');
> PREPARE
> cqdb=# prepare bar(date) as select extract (year from date $1);
> ERROR:  syntax error at or near "$1"
> LINE 1: prepare bar(date) as select extract (year from date $1);
>                                                             ^
> cqdb=# prepare baz(date) as select extract (year from $1);
> PREPARE
> cqdb=#

OK, I found a solution:

Whereas the last prepare statement works on the sql console it doesn't work
with jdbc: I've tried to construct the PreparedStatement as follows:

PreparedStatement stmt2 = conn.prepareStatement(
        "SELECT EXTRACT (YEAR FROM ?)");

And now the exception I get is (translated roughly from German):

Function pg_catalog.date_part(unknown, unknown) is ambiguous.
Hint: Couldn't choose best candidate function. You may have to add an
explicit cast.

Adding this explicit cast makes the thing work:

PreparedStatement stmt2 = conn.prepareStatement(
         "SELECT EXTRACT (YEAR FROM CAST (? AS DATE))");


It is not obvious though at once that this cast is needed.

Thanks for taking a look at this issue and kind regards,
Matthias Böhm

>Whereas the last prepare statement works on the sql console...

Well, only if you explicitly provide the parameter type information,
as I did. Compare the last statement to

cqdb=# prepare quux as select extract(year from $1);
ERROR:  function pg_catalog.date_part(unknown, unknown) is not unique
LINE 1: prepare quux as select extract(year from $1);
                             ^
HINT:  Could not choose a best candidate function. You might need to
add explicit type casts.

Note that the parameter declaration on the prepared statement is
missing this time and you get a similar error.

>...it doesn't work with jdbc:

Are you using setObject() to provide the parameter value? If so, I
think the JDBC driver does not give the server enough information
about the target parameter type. It asks the server to figure it out,
and the server can't, because it's ambiguous if all it's getting is a
parameter string typed as "unknown". If you use one of the
type-specific setters, it should work. The driver does attempt to do
some type-guessing based on the Java "source" parameter type, but
there is something of a disconnect between Java and Postgres types, so
this does *not* occur for java.util.Date, and for java.sql.Timestamp,
it occurs in such a way as to break for your use case (see
https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/jdbc2/AbstractJdbc2Statement.java#L3272
).

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com

> If you use one of the type-specific setters, it should work.

No, it doesn't: I'm using setDate(int, java.util.sql.Date) or
setTimestamp(int, java.util.sql.Timestamp) for setting the parameter, but
the information about the parameter type seems to get lost:

A legal query without using a parameter is:

SELECT EXTRACT (YEAR FROM DATE '1990-06-07 +02:00:00')

But when I prepare a statement with "SELECT EXTRACT (YEAR FROM ?)" and use
the setDate method to set the parameter and after this, use the toString
method of the PreparedStatement, I get the following:

SELECT EXTRACT (YEAR FROM '1991-09-07 +02:00:00')

In this query the obviously required DATE is missing!

On the contrary, trying to prepare a statement with "SELECT EXTRACT (YEAR
FROM DATE ?)" results, after calling toString on the prepared statement, in
the following, seemingly correct query:

SELECT EXTRACT (YEAR FROM DATE '1991-09-07 +02:00:00')

But when I try to execute the query I get a "syntax error at $1".

So there seems no way to prepare such a query without using the explicit
cast.

> The driver does attempt to do
> some type-guessing based on the Java "source" parameter type, but
> there is something of a disconnect between Java and Postgres types, so
> this does *not* occur for java.util.Date, and for java.sql.Timestamp,
> it occurs in such a way as to break for your use case

Yes, I took a look at the code, and the problem seems to be that bindString
method is called with Oid.UNSPECIFIED, so that the information about the
type is lost.

Still it is not clear to me why preparing a statement with "SELECT EXTRACT
(YEAR FROM DATE ?)" doesn't work as well, because it yields, as already
mentioned above, a seemingly valid query.

I see--that attempt to be clever by the driver is actually the crux of
your problem. It's there to avoid issues like this (I'm in US/Pacific,
setting the time zone to US/Eastern for the test):

maciek@anemone:~/aux-git/pgjdbc$ cat no-tz.sql
set TimeZone to 'US/Eastern';
prepare s1(timestamp) as select extract(hour from $1);
execute s1('2012-01-01 12:00:00 +1:00');
maciek@anemone:~/aux-git/pgjdbc$ psql -f no-tz.sql
SET
PREPARE
 date_part
-----------
        12
(1 row)

maciek@anemone:~/aux-git/pgjdbc$ cat with-tz.sql
set TimeZone to 'US/Eastern';
prepare s1(timestamptz) as select extract(hour from $1);
execute s1('2012-01-01 12:00:00 +1:00');
maciek@anemone:~/aux-git/pgjdbc$ psql -f with-tz.sql
SET
PREPARE
 date_part
-----------
        6
(1 row)

>On the contrary, trying to prepare a statement with "SELECT EXTRACT (YEAR
>FROM DATE ?)" results, after calling toString on the prepared statement, in
>the following, seemingly correct query:
>
>SELECT EXTRACT (YEAR FROM DATE '1991-09-07 +02:00:00')
>
>But when I try to execute the query I get a "syntax error at $1".

Well, the toString() method is entirely client-side and it just
interpolates the stringified parameter values into the prepared
statement for display. Many drivers send that resulting string to the
server directly (the server doesn't see any parameters, just
literals), but the Postgres driver sends the original string with
parameter markers (well, munged to substitute JDBC-specific '?' with
Postgres-specific '$1', '$2', etc.) along with the actual parameter
values out of band. In theory, this is safer and faster (allowing
re-use of prepared plans). In practice it's great until it breaks down
in a case like yours ;)

That is, "date '2012-01-01'" is not a date literal by itself: again,
I'm not clear on the details of the grammar, but it's more like a
literal with a cast (e.g., you can do something like "select integer
'1'"). Because of that, you can't send that whole thing to the server
as a parameter value, because it's *only* expecting the value.

I'm not sure if there's a good solution to what you're seeing. If we
were starting from scratch, I'd push for ignoring timestamp entirely
in favor of the generally more sane timestamptz, but if we make that
change now, all sorts of subtle bugs will pop up (or rather, seep into
application interfaces unnoticed) for other users.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com