使用准备好的语句在PostgreSQL上使用SQL:2008标准限制结果

Question

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;

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;

这是一个bug还是只是一个实现细节？

该测试可在GitHub上获得

Answer 1

执行细节。

这不是JDBC驱动程序的问题，而是数据库级别的问题。

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...

问题是只获取前n行的参数没有被解析为可供参数放置的文本。

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

  /*                          
   * 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); }
          ;