Changeset: 4317b2ef8acc for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/4317b2ef8acc
Modified Files:
.hgtags
sql/test/BugTracker-2022/Tests/All
sql/test/Tests/All
Branch: Sep2022
Log Message:
Merge with Jan2022 branch.
diffs (truncated from 1063 to 300 lines):
diff --git a/.hgtags b/.hgtags
--- a/.hgtags
+++ b/.hgtags
@@ -777,3 +777,4 @@ 26ffeb5af5240c807a6583fd6eb4ee2c9cd210d2
3191fee438036ac0da1e6f31481b9681399123b5 Jan2022_21
3191fee438036ac0da1e6f31481b9681399123b5 Jan2022_SP4_release
bdd04053973daa8a786a30e07b2bf1bd8aa6ebf1 Jul2021_27
+bdd04053973daa8a786a30e07b2bf1bd8aa6ebf1 Jul2021_SP8_release
diff --git a/sql/test/BugTracker-2022/Tests/All
b/sql/test/BugTracker-2022/Tests/All
--- a/sql/test/BugTracker-2022/Tests/All
+++ b/sql/test/BugTracker-2022/Tests/All
@@ -13,3 +13,4 @@ NOT_WIN32?cudf-grouped-aggr.Bug-7285
create_func_with_case_and_in
single_row_in_rel_order_by_column.Bug-7306
push-aggr-down-removes-distinct-on-union-all.Bug-7318
+temp-table-create-index-drop-issue
diff --git
a/sql/test/BugTracker-2022/Tests/temp-table-create-index-drop-issue.test
b/sql/test/BugTracker-2022/Tests/temp-table-create-index-drop-issue.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2022/Tests/temp-table-create-index-drop-issue.test
@@ -0,0 +1,180 @@
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+
+query III rowsort
+SELECT id, depend_id, depend_type FROM sys.dependencies WHERE (id, depend_id,
depend_type) NOT IN (SELECT v.id, v.used_by_id, v.depend_type FROM
sys.dependencies_vw v);
+----
+
+query IIII rowsort
+SELECT auth_id, privileges, grantor, grantable FROM sys.privileges WHERE
(obj_id) NOT IN (SELECT id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id
FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM
sys.functions) as t);
+----
+
+
+statement ok
+CREATE GLOBAL TEMP TABLE tmp.glbl_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2
VARCHAR(99) UNIQUE);
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+glbl_nopk_twoucs
+20
+1
+0
+1
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+glbl_nopk_twoucs_id2_unique
+glbl_nopk_twoucs_name2_unique
+
+statement ok
+CREATE INDEX glbl_nopk_twoucs_i ON tmp.glbl_nopk_twoucs (id2, name2);
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+glbl_nopk_twoucs_i
+glbl_nopk_twoucs_id2_unique
+glbl_nopk_twoucs_name2_unique
+
+statement ok
+DROP INDEX glbl_nopk_twoucs_i;
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+glbl_nopk_twoucs_id2_unique
+glbl_nopk_twoucs_name2_unique
+
+statement ok
+DROP TABLE tmp.glbl_nopk_twoucs;
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+
+query III rowsort
+SELECT id, depend_id, depend_type FROM sys.dependencies WHERE (id, depend_id,
depend_type) NOT IN (SELECT v.id, v.used_by_id, v.depend_type FROM
sys.dependencies_vw v);
+----
+
+query IIII rowsort
+SELECT auth_id, privileges, grantor, grantable FROM sys.privileges WHERE
(obj_id) NOT IN (SELECT id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id
FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM
sys.functions) as t);
+----
+
+
+
+statement ok
+DROP TABLE IF EXISTS tmp_nopk_twoucs
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system);
+----
+
+statement ok
+CREATE LOCAL TEMP TABLE tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2
VARCHAR(99) UNIQUE);
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+tmp_nopk_twoucs
+30
+1
+0
+1
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system);
+----
+tmp_nopk_twoucs_id2_unique
+tmp_nopk_twoucs_name2_unique
+
+statement ok
+DROP TABLE tmp_nopk_twoucs;
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system);
+----
+
+query III rowsort
+SELECT id, depend_id, depend_type FROM sys.dependencies WHERE (id, depend_id,
depend_type) NOT IN (SELECT v.id, v.used_by_id, v.depend_type FROM
sys.dependencies_vw v);
+----
+
+-- next is the situation which fails
+statement ok
+CREATE LOCAL TEMP TABLE tmp_nopk_twoucs (id2 INT NOT NULL UNIQUE, name2
VARCHAR(99) UNIQUE);
+
+statement ok
+GRANT INSERT, DELETE ON TABLE tmp.tmp_nopk_twoucs TO monetdb;
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+tmp_nopk_twoucs
+30
+1
+0
+1
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+tmp_nopk_twoucs_id2_unique
+tmp_nopk_twoucs_name2_unique
+
+statement ok
+CREATE INDEX tmp_nopk_twoucs_i ON tmp.tmp_nopk_twoucs (id2, name2);
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+tmp_nopk_twoucs_i
+tmp_nopk_twoucs_id2_unique
+tmp_nopk_twoucs_name2_unique
+
+statement ok
+DROP INDEX tmp_nopk_twoucs_i;
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+tmp_nopk_twoucs_id2_unique
+tmp_nopk_twoucs_name2_unique
+
+statement ok
+DROP TABLE tmp_nopk_twoucs;
+
+query TIIII rowsort
+select name, type, commit_action, access, temporary from sys.tables where not
system and schema_id in (select id from sys.schemas where name = 'tmp');
+----
+
+query T rowsort
+select name from tmp.idxs where table_id in (select id from sys.tables where
not system) union all select name from sys.idxs where table_id in (select id
from sys.tables where not system);
+----
+
+query III rowsort
+SELECT id, depend_id, depend_type FROM sys.dependencies WHERE (id, depend_id,
depend_type) NOT IN (SELECT v.id, v.used_by_id, v.depend_type FROM
sys.dependencies_vw v);
+----
+
+query IIII rowsort
+SELECT auth_id, privileges, grantor, grantable FROM sys.privileges WHERE
(obj_id) NOT IN (SELECT id FROM (SELECT id FROM sys.schemas UNION ALL SELECT id
FROM sys._tables UNION ALL SELECT id FROM sys._columns UNION ALL SELECT id FROM
sys.functions) as t);
+----
+
+
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
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
