On 22-8-2014 16:54, Mark Rotteveel wrote:
Are there any objections if I try to come up with a patch to add OFFSET
and FETCH to the select syntax (CORE-4526)?
Attached you will find my patch for this feature.
The grammar matches the SQL definition. I have made the existing
rows_clause required so it doesn't cause conflicts with the new
select_expr alternative that uses the result_offset_clause and
fetch_first_clause. For the delete_searched and update_searched I added
a rows_clause_optional so they continue to work as is.
I have reused the existing RowsClause node. I was tempted to rename it
to OffsetFetchClause as the implementation actually better matches the
needs for offset/fetch than for rows, but I left it as is.
I also updated some of the error messages and included a readme file.
Let me know if I can commit this, or if things need to be further improved.
Mark
--
Mark Rotteveel
Index: doc/sql.extensions/README.offset_fetch.txt
===================================================================
--- doc/sql.extensions/README.offset_fetch.txt (revision 0)
+++ doc/sql.extensions/README.offset_fetch.txt (working copy)
@@ -0,0 +1,51 @@
+-----------------------
+OFFSET and FETCH clause
+-----------------------
+
+ Function:
+ SQL:2008 compliant equivalent for FIRST/SKIP. The OFFSET clause specifies
+ the number of rows to skip. The FETCH clause specifies the number of rows
to
+ fetch.
+
+ OFFSET and FETCH can be applied independently on top-level and nested query
+ expressions.
+
+ Author:
+ Mark Rotteveel <[email protected]>
+
+ Syntax rules:
+ SELECT ... [ ORDER BY <expr_list> ]
+ [ OFFSET <simple_value_expr> { ROW | ROWS } ]
+ [ FETCH { FIRST | NEXT } [ <simple_value_expr> ] { ROW | ROWS } ONLY ]
+
+ Where <simple_value_expr> is a (numeric) literal, a SQL parameter (?) or
+ PSQL named parameter (:namedparameter).
+
+ Scope:
+ DSQL, PSQL
+
+ Example(s):
+ 1. SELECT * FROM T1
+ ORDER BY COL1
+ OFFSET 10 ROWS
+ 2. SELECT * FROM T1
+ ORDER BY COL1
+ FETCH FIRST 10 ROWS ONLY
+ 3. SELECT * FROM (
+ SELECT * FROM T1
+ ORDER BY COL1 DESC
+ OFFSET 1 ROW
+ FETCH NEXT 10 ROWS ONLY
+ ) a
+ ORDER BY a.COL1
+ FETCH FIRST ROW ONLY
+
+ Note(s):
+ 1. Firebird doesn't support the percentage FETCH defined in the SQL
+ standard.
+ 2. Firebird doesn't support the FETCH ... WITH TIES defined in the SQL
+ standard.
+ 3. The FIRST/SKIP and ROWS clause should be considered deprecated.
+ 4. The OFFSET and/or FETCH clauses cannot be combined with ROWS or
+ FIRST/SKIP on the same query expression.
+ 5. Expressions, column references etc are not allowed within both clauses.
\ No newline at end of file
Index: doc/sql.extensions/README.offset_fetch.txt
===================================================================
--- doc/sql.extensions/README.offset_fetch.txt (revision 0)
+++ doc/sql.extensions/README.offset_fetch.txt (working copy)
Property changes on: doc/sql.extensions/README.offset_fetch.txt
___________________________________________________________________
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property
Index: doc/sql.extensions/README.rows
===================================================================
--- doc/sql.extensions/README.rows (revision 60178)
+++ doc/sql.extensions/README.rows (working copy)
@@ -35,3 +35,5 @@
both <expr1> and <expr2> are used, then ROWS <expr1> TO <expr2> means:
FIRST (<expr2> - <expr1> + 1) SKIP (<expr1> - 1). Note that there's no
semantical equivalent
for a SKIP clause used without a FIRST clause.
+ 3. For SELECT the ROWS clause should be considered deprecated in favor of
the SQL-standard
+ OFFSET .. FETCH .. clauses
Index: src/dsql/parse.y
===================================================================
--- src/dsql/parse.y (revision 60178)
+++ src/dsql/parse.y (working copy)
@@ -570,6 +570,8 @@
%token <metaNamePtr> SERVERWIDE
%token <metaNamePtr> INCREMENT
%token <metaNamePtr> TRUSTED
+%token <metaNamePtr> ROW
+%token <metaNamePtr> OFFSET
// precedence declarations for expression evaluation
@@ -4893,6 +4895,21 @@
node->rowsClause = $4;
node->withClause = $1;
}
+ | with_clause select_expr_body order_clause result_offset_clause
fetch_first_clause
+ {
+ SelectExprNode* node = $$ = newNode<SelectExprNode>();
+ node->querySpec = $2;
+ node->orderClause = $3;
+ if ($4 || $5) {
+ RowsClause* rowsNode = newNode<RowsClause>();
+ rowsNode->skip = $4;
+ rowsNode->length = $5;
+ node->rowsClause = rowsNode;
+ } else {
+ node->rowsClause = NULL;
+ }
+ node->withClause = $1;
+ }
;
%type <withClause> with_clause
@@ -5446,14 +5463,13 @@
| LAST { $$ = OrderNode::NULLS_LAST; }
;
-// ROWS clause
+// ROWS clause - ROWS clause should be considered deprecated in favor of
OFFSET .. FETCH ..
+// Non-optional - for use in select_expr (so it doesn't cause conflicts with
OFFSET .. FETCH ..)
%type <rowsClause> rows_clause
rows_clause
- : // nothing
- { $$ = NULL; }
// equivalent to FIRST value
- | ROWS value
+ : ROWS value
{
$$ = newNode<RowsClause>();
$$->length = $2;
@@ -5468,7 +5484,46 @@
}
;
+// Optional - for use in delete_searched and update_searched
+%type <rowsClause> rows_clause_optional
+rows_clause_optional
+ : // nothing
+ { $$ = NULL; }
+ | rows_clause
+ ;
+// OFFSET n {ROW | ROWS}
+
+row_noise
+ : ROW
+ | ROWS
+ ;
+
+%type <valueExprNode> result_offset_clause
+result_offset_clause
+ : // nothing
+ { $$ = NULL; }
+ | OFFSET simple_value_spec row_noise
+ { $$ = $2; }
+ ;
+
+// FETCH {FIRST | NEXT} n {ROW | ROWS} ONLY
+
+first_next_noise
+ : FIRST
+ | NEXT
+ ;
+
+%type <valueExprNode> fetch_first_clause
+fetch_first_clause
+ : // nothing
+ { $$ = NULL; }
+ | FETCH first_next_noise simple_value_spec row_noise ONLY
+ { $$ = $3; }
+ | FETCH first_next_noise row_noise ONLY
+ { $$ = MAKE_const_slong(1); }
+ ;
+
// INSERT statement
// IBO hack: replace column_parens_opt by ins_column_parens_opt.
%type <storeNode> insert
@@ -5583,7 +5638,7 @@
%type <stmtNode> delete_searched
delete_searched
- : KW_DELETE FROM table_name where_clause plan_clause order_clause
rows_clause returning_clause
+ : KW_DELETE FROM table_name where_clause plan_clause order_clause
rows_clause_optional returning_clause
{
EraseNode* node = newNode<EraseNode>();
node->dsqlRelation = $3;
@@ -5620,7 +5675,7 @@
%type <stmtNode> update_searched
update_searched
: UPDATE table_name SET assignments where_clause plan_clause
- order_clause rows_clause returning_clause
+ order_clause rows_clause_optional returning_clause
{
ModifyNode* node = newNode<ModifyNode>();
node->dsqlRelation = $2;
@@ -6339,6 +6394,14 @@
| '(' value_primary ')' { $$ = $2; }
;
+// Matches definition of <simple value specification> in SQL standard
+%type <valueExprNode> simple_value_spec
+simple_value_spec
+ : constant
+ | variable
+ | parameter
+ ;
+
%type <valueExprNode> nonparenthesized_value
nonparenthesized_value
: column_name
Index: src/include/gen/msgs.h
===================================================================
--- src/include/gen/msgs.h (revision 60178)
+++ src/include/gen/msgs.h (working copy)
@@ -518,8 +518,8 @@
{335544814, "Services functionality will be supported in a later
version of the product"}, /* service_not_supported */
{335544815, "GENERATOR @1"}, /* generator_name */
{335544816, "UDF @1"}, /* udf_name */
- {335544817, "Invalid parameter to FIRST. Only integers >= 0 are
allowed."}, /* bad_limit_param */
- {335544818, "Invalid parameter to SKIP. Only integers >= 0 are
allowed."}, /* bad_skip_param */
+ {335544817, "Invalid parameter to FETCH or FIRST. Only integers >= 0
are allowed."}, /* bad_limit_param */
+ {335544818, "Invalid parameter to OFFSET or SKIP. Only integers >= 0
are allowed."}, /* bad_skip_param */
{335544819, "File exceeded maximum size of 2GB. Add another database
file or use a 64 bit I/O version of Firebird."}, /*
io_32bit_exceeded_err */
{335544820, "Unable to find savepoint with name @1 in transaction
context"}, /* invalid_savepoint */
{335544821, "Invalid column position used in the @1 clause"},
/* dsql_column_pos_err */
@@ -1157,7 +1157,7 @@
{336397324, "CREATE GENERATOR @1 failed"}, /*
dsql_create_generator_failed */
{336397325, "SET GENERATOR @1 failed"}, /*
dsql_set_generator_failed */
{336397326, "WITH LOCK can be used only with a single physical table"},
/* dsql_wlock_simple */
- {336397327, "FIRST/SKIP cannot be used with ROWS"}, /*
dsql_firstskip_rows */
+ {336397327, "FIRST/SKIP cannot be used with OFFSET/FETCH or ROWS"},
/* dsql_firstskip_rows */
{336397328, "WITH LOCK cannot be used with aggregates"},
/* dsql_wlock_aggregates */
{336397329, "WITH LOCK cannot be used with @1"}, /*
dsql_wlock_conflict */
{336723983, "unable to open database"}, /* gsec_cant_open_db */
Index: src/msgs/messages2.sql
===================================================================
--- src/msgs/messages2.sql (revision 60178)
+++ src/msgs/messages2.sql (working copy)
@@ -593,8 +593,8 @@
('service_not_supported', 'SVC_attach', 'svc.c', NULL, 0, 494, NULL, 'Services
functionality will be supported in a later version of the product', NULL,
NULL);
('generator_name', 'check_dependencies', 'dfw.e', NULL, 0, 495, NULL,
'GENERATOR @1', NULL, NULL);
('udf_name', 'check_dependencies', 'dfw.e', NULL, 0, 496, NULL, 'UDF @1',
NULL, NULL);
-('bad_limit_param', 'RSE_open', 'rse.c', NULL, 0, 497, NULL, 'Invalid
parameter to FIRST. Only integers >= 0 are allowed.', NULL, NULL);
-('bad_skip_param', 'RSE_open', 'rse.c', NULL, 0, 498, NULL, 'Invalid parameter
to SKIP. Only integers >= 0 are allowed.', NULL, NULL);
+('bad_limit_param', 'RSE_open', 'rse.c', NULL, 0, 497, NULL, 'Invalid
parameter to FETCH or FIRST. Only integers >= 0 are allowed.', NULL, NULL);
+('bad_skip_param', 'RSE_open', 'rse.c', NULL, 0, 498, NULL, 'Invalid parameter
to OFFSET or SKIP. Only integers >= 0 are allowed.', NULL, NULL);
('io_32bit_exceeded_err', 'seek_file', 'unix.c', NULL, 0, 499, NULL, 'File
exceeded maximum size of 2GB. Add another database file or use a 64 bit I/O
version of Firebird.', NULL, NULL);
('invalid_savepoint', 'looper', 'exe.cpp', NULL, 0, 500, NULL, 'Unable to find
savepoint with name @1 in transaction context', NULL, NULL);
('dsql_column_pos_err', '(several)', 'pass1.cpp', NULL, 0, 501, NULL, 'Invalid
column position used in the @1 clause', NULL, NULL);
@@ -2606,7 +2606,7 @@
('dsql_create_generator_failed', 'putErrorPrefix', 'DdlNodes.h', NULL, 13,
1036, NULL, 'CREATE GENERATOR @1 failed', NULL, NULL);
('dsql_set_generator_failed', 'putErrorPrefix', 'DdlNodes.h', NULL, 13, 1037,
NULL, 'SET GENERATOR @1 failed', NULL, NULL);
('dsql_wlock_simple', 'pass1_rse_impl', 'pass1.cpp', NULL, 13, 1038, NULL,
'WITH LOCK can be used only with a single physical table', NULL, NULL);
-('dsql_firstskip_rows', 'pass1_rse_impl', 'pass1.cpp', NULL, 13, 1039, NULL,
'FIRST/SKIP cannot be used with ROWS', NULL, NULL);
+('dsql_firstskip_rows', 'pass1_rse_impl', 'pass1.cpp', NULL, 13, 1039, NULL,
'FIRST/SKIP cannot be used with OFFSET/FETCH or ROWS', NULL, NULL);
('dsql_wlock_aggregates', 'pass1_rse_impl', 'pass1.cpp', NULL, 13, 1040, NULL,
'WITH LOCK cannot be used with aggregates', NULL, NULL);
('dsql_wlock_conflict', NULL, 'pass1.cpp', NULL, 13, 1041, NULL, 'WITH LOCK
cannot be used with @1', NULL, NULL);
-- SQLWARN
Index: src/yvalve/keywords.cpp
===================================================================
--- src/yvalve/keywords.cpp (revision 60178)
+++ src/yvalve/keywords.cpp (working copy)
@@ -282,6 +282,7 @@
{NULLS, "NULLS", 2, true},
{KW_NUMERIC, "NUMERIC", 1, false},
{OCTET_LENGTH, "OCTET_LENGTH", 2, false},
+ {OFFSET, "OFFSET", 2, false},
{OF, "OF", 1, false},
{ON, "ON", 1, false},
{ONLY, "ONLY", 1, false},
@@ -348,6 +349,7 @@
{ROLE, "ROLE", 1, true},
{ROLLBACK, "ROLLBACK", 1, false},
{ROUND, "ROUND", 2, false},
+ {ROW, "ROW", 2, false},
{ROW_COUNT, "ROW_COUNT", 2, false},
{ROW_NUMBER, "ROW_NUMBER", 2, false},
{ROWS, "ROWS", 2, false},
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
