Changeset: aad585ec2fed for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/aad585ec2fed
Added Files:
sql/test/Tests/orderby-nulls-first-last.test
Modified Files:
sql/test/Tests/All
Branch: Jan2022
Log Message:
Adding test for testing ORDER BY x [ DESC ] NULLS { FIRST | LAST }
diffs (truncated from 862 to 300 lines):
diff --git a/sql/test/Tests/All b/sql/test/Tests/All
--- a/sql/test/Tests/All
+++ b/sql/test/Tests/All
@@ -143,6 +143,7 @@ savepoints2
union
## Queries from the old AS3AP benchmark.
## Since we don't have the data, let's just execute the queries here
-## Commented-out queries suffer from the problem reported in Issue #7023
as3ap
+orderby-nulls-first-last
+
diff --git a/sql/test/Tests/orderby-nulls-first-last.test
b/sql/test/Tests/orderby-nulls-first-last.test
new file mode 100644
--- /dev/null
+++ b/sql/test/Tests/orderby-nulls-first-last.test
@@ -0,0 +1,845 @@
+-- tests voor ORDER BY x [ ASC | DESC ] NULLS { FIRST | LAST }
+
+-- FYI: other scripts where NULLS [ FIRST | LAST ] are tested:
+-- sql/test/analytics/Tests/analytics13.test
+-- sql/test/miscellaneous/Tests/groupings.test
+-- sql/test/subquery/Tests/subquery3.test
+-- sql/test/subquery/Tests/subquery4.test
+-- sql/test/SQLancer/Tests/sqlancer05.test
+-- sql/test/SQLancer/Tests/sqlancer11.test
+
+
+statement ok
+create table obn (cti tinyint, cbi bigint, crl real, cdl double, cch char(44),
cvc varchar(99), cstr string, ctm time, cdt date, cts timestamp(0));
+
+statement ok rowcount 1
+insert into obn
values(4,4,4,4,'c4c','vc4','str4','04:04:04','2004-04-04','2004-04-04
04:04:04');
+
+statement ok rowcount 1
+insert into obn
values(9,9,9,9,'c9c','vc9','str9','09:09:09','2009-09-09','2009-09-09
09:09:09');
+
+statement ok rowcount 1
+insert into obn values(null,null,null,null,null,null,null,null,null,null);
+
+statement ok rowcount 1
+insert into obn
values(12,12,12,12,'c12c','vc12','str12','12:12:12','2012-12-12','2012-12-12
12:12:12');
+
+statement ok rowcount 1
+insert into obn
values(3,3,3,3,'c3c','vc3','str3','03:03:03','2003-03-03','2003-03-03
03:03:03');
+
+statement ok rowcount 1
+insert into obn values(null,null,null,null,null,null,null,null,null,null);
+
+statement ok rowcount 1
+insert into obn
values(2,2,2,2,'c2c','vc2','str2','02:02:02','2002-02-02','2002-02-02
02:02:02');
+
+query IIRRTTTTTT nosort
+select * from obn;
+----
+4
+4
+4.000
+4.000
+c4c
+vc4
+str4
+04:04:04
+2004-04-04
+2004-04-04 04:04:04
+9
+9
+9.000
+9.000
+c9c
+vc9
+str9
+09:09:09
+2009-09-09
+2009-09-09 09:09:09
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+12
+12
+12.000
+12.000
+c12c
+vc12
+str12
+12:12:12
+2012-12-12
+2012-12-12 12:12:12
+3
+3
+3.000
+3.000
+c3c
+vc3
+str3
+03:03:03
+2003-03-03
+2003-03-03 03:03:03
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+2
+2
+2.000
+2.000
+c2c
+vc2
+str2
+02:02:02
+2002-02-02
+2002-02-02 02:02:02
+
+query IIRRTTTTTT nosort
+select * from obn order by cti;
+----
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+2
+2
+2.000
+2.000
+c2c
+vc2
+str2
+02:02:02
+2002-02-02
+2002-02-02 02:02:02
+3
+3
+3.000
+3.000
+c3c
+vc3
+str3
+03:03:03
+2003-03-03
+2003-03-03 03:03:03
+4
+4
+4.000
+4.000
+c4c
+vc4
+str4
+04:04:04
+2004-04-04
+2004-04-04 04:04:04
+9
+9
+9.000
+9.000
+c9c
+vc9
+str9
+09:09:09
+2009-09-09
+2009-09-09 09:09:09
+12
+12
+12.000
+12.000
+c12c
+vc12
+str12
+12:12:12
+2012-12-12
+2012-12-12 12:12:12
+
+query I nosort
+select cti from obn order by cti nulls first;
+----
+NULL
+NULL
+2
+3
+4
+9
+12
+
+query I nosort
+select cti from obn order by cti nulls last;
+----
+2
+3
+4
+9
+12
+NULL
+NULL
+
+query I nosort
+select cti from obn order by cti desc;
+----
+12
+9
+4
+3
+2
+NULL
+NULL
+
+query I nosort
+select cti from obn order by cti desc nulls last;
+----
+12
+9
+4
+3
+2
+NULL
+NULL
+
+query I nosort
+select cti from obn order by cti desc nulls first;
+----
+NULL
+NULL
+12
+9
+4
+3
+2
+
+
+query I nosort
+select cbi from obn order by cbi;
+----
+NULL
+NULL
+2
+3
+4
+9
+12
+
+query I nosort
+select cbi from obn order by cbi nulls first;
+----
+NULL
+NULL
+2
+3
+4
+9
+12
+
+query I nosort
+select cbi from obn order by cbi nulls last;
+----
+2
+3
+4
+9
+12
+NULL
+NULL
+
+query I nosort
+select cbi from obn order by cbi desc;
+----
+12
+9
+4
+3
+2
+NULL
+NULL
+
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
