Limiting results with the SQL:2008 standard on PostgreSQL using prepared statements

Question

While working on an example to test the PostgreSQL support for SQL:2008 result set limiting feature, I realized that this syntax doesn't work with a prepared statement:

SELECT pc.id AS pc_id, p.id AS p_id  
FROM post_comment pc 
INNER JOIN post p ON p.id = pc.post_id 
ORDER BY pc.id 
OFFSET ? ROWS 
FETCH FIRST ? ROWS ONLY;

While for static statements using literals it's just fine, using a prepared statement throws:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$2" Position: 140 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182) ~[postgresql-9.4-1202-jdbc41.jar:9.4] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911) ~[postgresql-9.4-1202-jdbc41.jar:9.4] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173) ~[postgresql-9.4-1202-jdbc41.jar:9.4] at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615) ~[postgresql-9.4-1202-jdbc41.jar:9.4] at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465) ~[postgresql-9.4-1202-jdbc41.jar:9.4] at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:458) ~[postgresql-9.4-1202-jdbc41.jar:9.4]

If the SQL query is changed to:

SELECT pc.id AS pc_id, p.id AS p_id  
FROM post_comment pc 
INNER JOIN post p ON p.id = pc.post_id 
ORDER BY pc.id 
OFFSET ? ROWS 
FETCH FIRST (?) ROWS ONLY;

the parenthesis seem to do the trick and the bind parameter is taken into consideration.

Is this a bug or just an implementation detail?

The test is available on GitHub

I think this is because ? is a JSON operator and incorrectly treated by the JDBC driver: postgresql.nabble.com/… — a_horse_with_no_name
It's interesting because for OFFSET it works just fine. Only the FETCH directive is affected but this issue. — Vlad Mihalcea
I love questions where all the code and information required is right there. Thanks. — Craig Ringer
Mea cupla...the first code example shouldn't have the parenthesis around the second bind parameter place holder. — Vlad Mihalcea

Craig Ringer Craig Ringer · Accepted Answer · 2015-09-25T13:30:52

TL;DR: Implementation detail.

It's not a JDBC driver issue, it's database level.

test=> SELECT 1 OFFSET 1 ROWS FETCH FIRST 1 ROWS ONLY;
 ?column? 
----------
(0 rows)

test=> PREPARE stmt(integer, integer) AS SELECT 1 OFFSET $1 ROWS FETCH FIRST $2 ROWS ONLY;
ERROR:  syntax error at or near "$2"
LINE 1: ..., integer) AS SELECT 1 OFFSET $1 ROWS FETCH FIRST $2 ROWS ON...

The issue is that the argument to FETCH FIRST n ROWS ONLY is not being parsed as a literal that's a candidate for parameter placement.

In src/backend/parser/gram.y:

          /* SQL:2008 syntax */
          | FETCH first_or_next opt_select_fetch_first_value row_or_rows ONLY
              { $$ = $3; }

and

  /*                          
   * Allowing full expressions without parentheses causes various parsing
   * problems with the trailing ROW/ROWS key words.  SQL only calls for
   * constants, so we allow the rest only with parentheses.  If omitted,
   * default to 1.
   */
  opt_select_fetch_first_value:
              SignedIconst                        { $$ = makeIntConst($1, @1); }
              | '(' a_expr ')'                    { $$ = $2; }
              | /*EMPTY*/                         { $$ = makeIntConst(1, -1); }
          ;

shows that this is in fact intentional, and that the parentheses are required to remove ambiguity for parameter use, but SQL:2008 doesn't require this to be supported as a query parameter anyway.

If you want to supply a parameter, use parentheses.

Limiting results with the SQL:2008 standard on PostgreSQL using prepared statements

1 Answers