Re: Bug with PreparedStatements using EXTRACT function

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