Changeset: 636832e361e7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/636832e361e7
Branch: default
Log Message:
merged
diffs (272 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -484,8 +484,7 @@ SQLhelp sqlhelp1[] = {
"[ HAVING condition [',' ...] ]\n"
"[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING ]
select ]\n"
"[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [','
...] ]\n"
- "[ LIMIT { count | param } ]\n"
- "[ OFFSET { count | param } ]\n"
+ "[ limit_offset_clause | offset_fetchfirst_clause ]\n"
"[ SAMPLE size [ SEED size ] ]",
"cte_list,expression,group_by_element,window_definition",
"See also
https://www.monetdb.org/documentation/user-guide/sql-manual/data-manipulation/table-expressions/"},
@@ -832,8 +831,8 @@ SQLhelp sqlhelp2[] = {
NULL},
{"query_expression",
NULL,
- "select_no_parens [ order_by_clause ] [ limit_clause ] [ offset_clause
] [ sample_clause ]",
-
"select_no_parens,order_by_clause,limit_clause,offset_clause,sample_clause",
+ "select_no_parens [ order_by_clause ] [ limit_offset_clause |
offset_fetchfirst_clause ] [ sample_clause ]",
+ "select_no_parens",
NULL},
{"select_no_parens",
NULL,
@@ -842,6 +841,21 @@ SQLhelp sqlhelp2[] = {
"| '(' select_no_parens ')' }",
"column_exp_commalist,from_clause,window_clause,where_clause,group_by_clause,having_clause,corresponding",
NULL},
+ {"order_by_clause",
+ NULL,
+ "ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [','
...]",
+ "",
+ NULL},
+ {"limit_offset_clause",
+ NULL,
+ "[ LIMIT { count | param } ] [ OFFSET { count | param } ]",
+ "",
+ NULL},
+ {"offset_fetchfirst_clause",
+ NULL,
+ "[ OFFSET { count | param } [ {ROW|ROWS} ] ] [ FETCH {FIRST|NEXT} [
count | param ] {ROW|ROWS} ONLY ]",
+ "",
+ NULL},
{"corresponding",
NULL,
"{ CORRESPONDING | CORRESPONDING BY '(' column_ref_commalist ')' }",
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,3 +1,18 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Fri Jul 7 2023 Niels Nes <niels....@monetdbsolutions.com>
+- Added SQL support for: <result offset clause> and <fetch first clause>
+ in <query expression> ::=
+ [ <with clause> ] <query expression body>
+ [ <order by clause> ]
+ [ <result offset clause> ]
+ [ <fetch first clause> ]
+ [ <sample clause> ]
+
+ <result offset clause> ::=
+ OFFSET <offset row count> [ {ROW|ROWS} ]
+
+ <fetch first clause> ::=
+ FETCH {FIRST|NEXT} <fetch first row count> {ROW|ROWS} ONLY
+
diff --git a/sql/test/Tests/All b/sql/test/Tests/All
--- a/sql/test/Tests/All
+++ b/sql/test/Tests/All
@@ -70,6 +70,7 @@ create_in_schema
null-byte-hang
orderby_limit
orderby_non_selection_column
+limit_offset_fetchfirst
psm
diff --git a/sql/test/Tests/limit_offset_fetchfirst.test
b/sql/test/Tests/limit_offset_fetchfirst.test
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/limit_offset_fetchfirst.test
@@ -0,0 +1,188 @@
+-- tests [ LIMIT y ] [ OFFSET x ]
+
+statement ok
+drop table if exists limittest
+
+statement ok
+create table limittest as select * from "sys"."generate_series"(1, 221)
+
+query I nosort
+select * from limittest LIMIT 5
+----
+1
+2
+3
+4
+5
+
+statement error
+select * from limittest LIMIT +5
+
+statement error
+select * from limittest LIMIT 0
+
+statement error
+select * from limittest LIMIT -5
+
+statement error
+select * from limittest LIMIT 20, 50
+
+statement error
+select * from limittest LIMIT OFFSET 20, 50
+
+statement error
+select * from limittest OFFSET 20 LIMIT 50
+
+query I rowsort
+select * from limittest LIMIT 5 OFFSET 20
+----
+21
+22
+23
+24
+25
+
+statement error
+select * from limittest LIMIT 5 OFFSET 20 ROWS
+
+statement error
+select * from limittest LIMIT 5 ROWS OFFSET 20
+
+
+-- tests [ OFFSET pos_lng [ {ROW|ROWS} ] ] [ FETCH {FIRST|NEXT} nonzero_lng
{ROW|ROWS} ONLY ]
+
+statement error
+select * from limittest OFFSET
+
+statement error
+select * from limittest OFFSET ROW
+
+query I rowsort
+select * from limittest OFFSET 218
+----
+219
+220
+
+query I rowsort
+select * from limittest OFFSET 219 ROW
+----
+220
+
+query I rowsort
+select * from limittest OFFSET 218 ROWS
+----
+219
+220
+
+statement error
+select * from limittest OFFSET (200 + 18) ROWS
+
+statement error
+select * from limittest FETCH ROWS
+
+statement error
+select * from limittest FETCH ROWS ONLY
+
+statement error
+select * from limittest FETCH 0 ROWS ONLY
+
+query I rowsort
+select * from limittest FETCH NEXT ROW ONLY
+----
+1
+
+query I rowsort
+select * from limittest FETCH FIRST 4 ROWS ONLY
+----
+1
+2
+3
+4
+
+query I rowsort
+select * from limittest OFFSET 5 FETCH FIRST 4 ROWS ONLY
+----
+6
+7
+8
+9
+
+query I rowsort
+select * from limittest OFFSET 5 FETCH NEXT 4 ROWS ONLY
+----
+6
+7
+8
+9
+
+query I rowsort
+select * from limittest FETCH FIRST ROW ONLY
+----
+1
+
+query I rowsort
+select * from limittest FETCH FIRST 3 ROWS ONLY
+----
+1
+2
+3
+
+query I rowsort
+select * from limittest OFFSET 10 FETCH FIRST 3 ROWS ONLY
+----
+11
+12
+13
+
+query I rowsort
+select * from limittest OFFSET 218
+----
+219
+220
+
+query I rowsort
+select * from limittest OFFSET 220
+----
+
+query I rowsort
+select * from limittest OFFSET 222
+----
+
+
+statement error
+select * from limittest LIMIT 6 OFFSET 5 FETCH NEXT 4 ROWS ONLY
+
+statement error
+select * from limittest LIMIT 6 FETCH NEXT 4 ROWS ONLY
+
+statement error
+select * from limittest FETCH NEXT 4 ROWS ONLY LIMIT 6
+
+statement error
+select * from limittest FETCH NEXT 4 ROWS ONLY OFFSET 5
+
+query I rowsort
+select * from limittest FETCH FIRST 3 ROWS ONLY
+----
+1
+2
+3
+
+query I rowsort
+select * from (select * from limittest OFFSET 50 ROWS FETCH FIRST 10 ROWS
ONLY) t OFFSET 5 ROW FETCH FIRST 4 ROWS ONLY
+----
+56
+57
+58
+59
+
+query I rowsort
+select * from (select * from limittest order by 1 desc OFFSET 50 ROWS FETCH
FIRST 10 ROWS ONLY) t order by 1 asc OFFSET 5 ROW FETCH FIRST 4 ROWS ONLY
+----
+166
+167
+168
+169
+
+statement ok
+drop table if exists limittest
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
