Menu
▾
▴
[Firebird-checkins] SF.net SVN: firebird:[60305] firebird/trunk
|
From: <mro...@us...> - 2014-11-29 15:40:28
|
Revision: 60305
http://sourceforge.net/p/firebird/code/60305
Author: mrotteveel
Date: 2014-11-29 15:39:59 +0000 (Sat, 29 Nov 2014)
Log Message:
-----------
CORE-4526 Support for SQL:2008 OFFSET and FETCH clauses
Modified Paths:
--------------
firebird/trunk/doc/sql.extensions/README.rows
firebird/trunk/src/dsql/parse.y
firebird/trunk/src/include/gen/msgs.h
firebird/trunk/src/msgs/messages2.sql
firebird/trunk/src/yvalve/keywords.cpp
Added Paths:
-----------
firebird/trunk/doc/sql.extensions/README.offset_fetch.txt
Added: firebird/trunk/doc/sql.extensions/README.offset_fetch.txt
===================================================================
--- firebird/trunk/doc/sql.extensions/README.offset_fetch.txt (rev 0)
+++ firebird/trunk/doc/sql.extensions/README.offset_fetch.txt 2014-11-29 15:39:59 UTC (rev 60305)
@@ -0,0 +1,53 @@
+-----------------------
+OFFSET and FETCH clause
+-----------------------
+
+ Function:
+ SQL:2008 compliant equivalent for FIRST/SKIP, and an alternative for ROWS.
+ 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 <mro...@us...>
+
+ 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 are non-standard alternatives.
+ 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 either clause.
+ 6. Contrary to the ROWS clause, OFFSET and FETCH are only available on
+ SELECT statements.
\ No newline at end of file
Property changes on: firebird/trunk/doc/sql.extensions/README.offset_fetch.txt
___________________________________________________________________
Added: svn:mime-type
## -0,0 +1 ##
+text/plain
\ No newline at end of property
Added: svn:eol-style
## -0,0 +1 ##
+native
\ No newline at end of property
Modified: firebird/trunk/doc/sql.extensions/README.rows
===================================================================
--- firebird/trunk/doc/sql.extensions/README.rows 2014-11-29 06:00:26 UTC (rev 60304)
+++ firebird/trunk/doc/sql.extensions/README.rows 2014-11-29 15:39:59 UTC (rev 60305)
@@ -3,8 +3,9 @@
-----------
Function:
- Allow to limit a number of rows retrieved from a select expression. For a highest level
- select statement, it would mean a number of rows sent to the host program.
+ Limits the number of rows retrieved from a select expression. For the
+ top-level select statement, it would mean a number of rows sent to the host
+ program.
Author:
Dmitry Yemanov <ye...@ya...>
@@ -28,10 +29,13 @@
ROWS 1
Note(s):
- 1. ROWS is a more understandable alternative to the FIRST/SKIP clauses with some extra benefits.
- It can be used in unions and all kind of subqueries. Also it's available in the UPDATE/DELETE
- statements.
- 2. When <expr2> is omitted, then ROWS <expr1> is a semantical equivalent for FIRST <expr1>. When
- 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.
+ 1. ROWS is a more understandable alternative to the FIRST/SKIP clauses with
+ some extra benefits. It can be used in unions and all kind of subqueries.
+ It is also available in the UPDATE/DELETE statements.
+ 2. When <expr2> is omitted, then ROWS <expr1> is a semantical equivalent for
+ FIRST <expr1>. When both <expr1> and <expr2> are used, then ROWS <expr1>
+ TO <expr2> means:
+ FIRST (<expr2> - <expr1> + 1) SKIP (<expr1> - 1). Note that there's no
+ semantic equivalent for a SKIP clause used without a FIRST clause.
+ 3. The ROWS-clause is not defined in the SQL standard. For SELECT, consider
+ the alternative OFFSET and FETCH clauses defined in the SQL standard.
Modified: firebird/trunk/src/dsql/parse.y
===================================================================
--- firebird/trunk/src/dsql/parse.y 2014-11-29 06:00:26 UTC (rev 60304)
+++ firebird/trunk/src/dsql/parse.y 2014-11-29 15:39:59 UTC (rev 60305)
@@ -570,6 +570,8 @@
%token <metaNamePtr> SERVERWIDE
%token <metaNamePtr> INCREMENT
%token <metaNamePtr> TRUSTED
+%token <metaNamePtr> ROW
+%token <metaNamePtr> OFFSET
// precedence declarations for expression evaluation
@@ -3809,9 +3811,11 @@
| KW_BOOLEAN // added in FB 3.0
| DETERMINISTIC
| KW_FALSE
+ | OFFSET
| OVER
| RETURN
| RDB_RECORD_VERSION
+ | ROW
| SCROLL
| SQLSTATE
| KW_TRUE
@@ -4898,6 +4902,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
@@ -5451,14 +5470,13 @@
| LAST { $$ = OrderNode::NULLS_LAST; }
;
-// ROWS clause
+// ROWS clause - ROWS clause is a non-standard alternative to 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;
@@ -5473,7 +5491,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
@@ -5588,7 +5645,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;
@@ -5625,7 +5682,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;
@@ -6344,6 +6401,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
Modified: firebird/trunk/src/include/gen/msgs.h
===================================================================
--- firebird/trunk/src/include/gen/msgs.h 2014-11-29 06:00:26 UTC (rev 60304)
+++ firebird/trunk/src/include/gen/msgs.h 2014-11-29 15:39:59 UTC (rev 60305)
@@ -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 */
@@ -1158,7 +1158,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 */
Modified: firebird/trunk/src/msgs/messages2.sql
===================================================================
--- firebird/trunk/src/msgs/messages2.sql 2014-11-29 06:00:26 UTC (rev 60304)
+++ firebird/trunk/src/msgs/messages2.sql 2014-11-29 15:39:59 UTC (rev 60305)
@@ -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);
@@ -2607,7 +2607,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
Modified: firebird/trunk/src/yvalve/keywords.cpp
===================================================================
--- firebird/trunk/src/yvalve/keywords.cpp 2014-11-29 06:00:26 UTC (rev 60304)
+++ firebird/trunk/src/yvalve/keywords.cpp 2014-11-29 15:39:59 UTC (rev 60305)
@@ -283,6 +283,7 @@
{KW_NUMERIC, "NUMERIC", 1, false},
{OCTET_LENGTH, "OCTET_LENGTH", 2, false},
{OF, "OF", 1, false},
+ {OFFSET, "OFFSET", 2, false},
{ON, "ON", 1, false},
{ONLY, "ONLY", 1, false},
{OPEN, "OPEN", 2, 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},
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
