Changeset: 9dc910c06aab for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9dc910c06aab
Added Files:
sql/test/Tests/limit_offset_fetchfirst.test
Modified Files:
sql/ChangeLog
sql/test/Tests/All
Branch: default
Log Message:
Add tests for new SQL syntax [ OFFSET pos_lng [ {ROW|ROWS} ] ] [ FETCH
{FIRST|NEXT} nonzero_lng {ROW|ROWS} ONLY ]
Also added entry to ChangeLog
diffs (226 lines):
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
