Changeset: 05e581d80bb0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/05e581d80bb0
Modified Files:
sql/test/Tests/orderby-nulls-first-last.test
Branch: Jan2022
Log Message:
Extending orderby-nulls-first-last.test with limit clause and offset clause.
MonetDB appears to produces incorrect answers when 'nulls last limit n' is used
in a query for data types: tinyint, bigint, time and date (but not for real,
double, char, varchar, clob, timestamp).
diffs (truncated from 683 to 300 lines):
diff --git a/sql/test/Tests/orderby-nulls-first-last.test
b/sql/test/Tests/orderby-nulls-first-last.test
--- a/sql/test/Tests/orderby-nulls-first-last.test
+++ b/sql/test/Tests/orderby-nulls-first-last.test
@@ -1,4 +1,4 @@
--- tests voor ORDER BY x [ ASC | DESC ] NULLS { FIRST | LAST }
+-- tests voor ORDER BY x [ ASC | DESC ] NULLS { FIRST | LAST } [ LIMIT n ] [
OFFSET n ]
-- FYI: other scripts where NULLS [ FIRST | LAST ] are tested:
-- sql/test/analytics/Tests/analytics13.test
@@ -204,6 +204,32 @@ NULL
NULL
query I nosort
+select cti from obn order by cti nulls last limit 6;
+----
+2
+3
+4
+9
+12
+NULL
+
+query I nosort
+select cti from obn order by cti nulls last limit 4 offset 2;
+----
+4
+9
+12
+NULL
+
+query I nosort
+select cti from obn order by cti nulls last offset 3;
+----
+9
+12
+NULL
+NULL
+
+query I nosort
select cti from obn order by cti desc;
----
12
@@ -236,6 +262,25 @@ 4
3
2
+query I nosort
+select cti from obn order by cti desc nulls first limit 5;
+----
+NULL
+NULL
+12
+9
+4
+
+query I nosort
+select cti from obn order by cti desc nulls first offset 1;
+----
+NULL
+12
+9
+4
+3
+2
+
query I nosort
select cbi from obn order by cbi;
@@ -271,6 +316,32 @@ NULL
NULL
query I nosort
+select cbi from obn order by cbi nulls last limit 5;
+----
+2
+3
+4
+9
+12
+
+query I nosort
+select cbi from obn order by cbi nulls last limit 4 offset 2;
+----
+4
+9
+12
+NULL
+
+query I nosort
+select cbi from obn order by cbi nulls last offset 2;
+----
+4
+9
+12
+NULL
+NULL
+
+query I nosort
select cbi from obn order by cbi desc;
----
12
@@ -303,6 +374,35 @@ 4
3
2
+query I nosort
+select cbi from obn order by cbi desc nulls first limit 6;
+----
+NULL
+NULL
+12
+9
+4
+3
+
+query I nosort
+select cbi from obn order by cbi desc nulls first limit 5 offset 1;
+----
+NULL
+12
+9
+4
+3
+
+query I nosort
+select cbi from obn order by cbi desc nulls first offset 1;
+----
+NULL
+12
+9
+4
+3
+2
+
query R nosort
select crl from obn order by crl;
@@ -338,6 +438,22 @@ NULL
NULL
query R nosort
+select crl from obn order by crl nulls last limit 4;
+----
+2.000
+3.000
+4.000
+9.000
+
+query R nosort
+select crl from obn order by crl nulls last limit 4 offset 2;
+----
+4.000
+9.000
+12.000
+NULL
+
+query R nosort
select crl from obn order by crl desc;
----
12.000
@@ -370,6 +486,33 @@ 4.000
3.000
2.000
+query R nosort
+select crl from obn order by crl desc nulls first limit 5;
+----
+NULL
+NULL
+12.000
+9.000
+4.000
+
+query R nosort
+select crl from obn order by crl desc nulls first limit 4 offset 1;
+----
+NULL
+12.000
+9.000
+4.000
+
+query R nosort
+select crl from obn order by crl desc nulls first offset 1;
+----
+NULL
+12.000
+9.000
+4.000
+3.000
+2.000
+
query R nosort
select cdl from obn order by cdl;
@@ -394,6 +537,15 @@ 9.000
12.000
query R nosort
+select cdl from obn order by cdl nulls first limit 5;
+----
+NULL
+NULL
+2.000
+3.000
+4.000
+
+query R nosort
select cdl from obn order by cdl nulls last;
----
2.000
@@ -405,6 +557,14 @@ NULL
NULL
query R nosort
+select cdl from obn order by cdl nulls last offset 3;
+----
+9.000
+12.000
+NULL
+NULL
+
+query R nosort
select cdl from obn order by cdl desc;
----
12.000
@@ -427,6 +587,31 @@ NULL
NULL
query R nosort
+select cdl from obn order by cdl desc nulls last limit 6;
+----
+12.000
+9.000
+4.000
+3.000
+2.000
+NULL
+
+query R nosort
+select cdl from obn order by cdl desc nulls last limit 3 offset 3;
+----
+3.000
+2.000
+NULL
+
+query R nosort
+select cdl from obn order by cdl desc nulls last offset 3;
+----
+3.000
+2.000
+NULL
+NULL
+
+query R nosort
select cdl from obn order by cdl desc nulls first;
----
NULL
@@ -437,6 +622,24 @@ 4.000
3.000
2.000
+query R nosort
+select cdl from obn order by cdl desc nulls first limit 5;
+----
+NULL
+NULL
+12.000
+9.000
+4.000
+
+query R nosort
+select cdl from obn order by cdl desc nulls first limit 5 offset 1;
+----
+NULL
+12.000
+9.000
+4.000
+3.000
+
query T nosort
select cch from obn order by cch;
@@ -461,6 +664,16 @@ c4c
c9c
query T nosort
+select cch from obn order by cch nulls first offset 1;
+----
+NULL
+c12c
+c2c
+c3c
+c4c
+c9c
+
+query T nosort
select cch from obn order by cch nulls last;
----
c12c
@@ -472,6 +685,23 @@ NULL
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
