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