Bug with PreparedStatements using EXTRACT function - Mailing list pgsql-jdbc

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)
*****