Changeset: 3ce63d4e0922 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3ce63d4e0922
Modified Files:
sql/test/mergetables/Tests/sqlsmith-exists.sql
sql/test/mergetables/Tests/sqlsmith-exists.stable.out
Branch: default
Log Message:
Removing system tables from sqlsmith-exists test, and making it portable to
other databases
diffs (truncated from 619 to 300 lines):
diff --git a/sql/test/mergetables/Tests/sqlsmith-exists.sql
b/sql/test/mergetables/Tests/sqlsmith-exists.sql
--- a/sql/test/mergetables/Tests/sqlsmith-exists.sql
+++ b/sql/test/mergetables/Tests/sqlsmith-exists.sql
@@ -1,62 +1,74 @@
START TRANSACTION;
CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6
INT, col7 INT, col8 INT);
INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+CREATE TABLE LongTable (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6
INT, col7 INT, col8 INT);
+INSERT INTO LongTable VALUES (1,7,2,1,1,909,1,1), (2,7,2,2,3,4,4,6),
(NULL,5,4,81,NULL,5,-10,1), (-90,NULL,0,NULL,2,0,1,NULL);
+CREATE TABLE integers(i INTEGER);
+INSERT INTO integers VALUES (1), (2), (3), (NULL);
+CREATE TABLE tbl_ProductSales (col1 INT, col2 varchar(64), col3 varchar(64),
col4 INT, col5 REAL, col6 date);
+INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200, 1.2, date
'2015-12-12'),(2,'Game','PKO Game',400, -1.0, date
'2012-02-10'),(3,'Fashion','Shirt',500, NULL, date '1990-01-01'),
+(4,'Fashion','Shorts',100, 102.45, date '2000-03-08'),(5,'Sport','Ball',0,
224.78, NULL);
+CREATE TABLE analytics (aa INT, bb INT, cc BIGINT);
+INSERT INTO analytics VALUES (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 2, NULL), (3, 2, 3), (4, 1, 4), (6, 3, 6), (8, 2, 8), (NULL, 4, NULL);
+CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);
+INSERT INTO tab0 VALUES (97,1,99), (15,81,47),(87,21,10);
+CREATE TABLE tab1(col0 INTEGER, col1 INTEGER, col2 INTEGER);
+INSERT INTO tab1 VALUES (51,14,96), (85,5,59), (91,47,68);
+CREATE TABLE tab2(col0 INTEGER, col1 INTEGER, col2 INTEGER);
+INSERT INTO tab2 VALUES (64,77,40), (75,67,58),(46,51,23);
select
-cast(coalesce(ref_0.id, ref_0.owner) as int) as c0, ref_0.system as c1,
+cast(coalesce(ref_0.col2, ref_0.col3) as int) as c0, ref_0.col4 as c1,
50 as c2,
-ref_0.name as c3
+ref_0.col1 as c3
from
-sys.schemas as ref_0
+another_T as ref_0
where (false)
or (exists (
select
- ref_0.system as c0,
- ref_0.id as c1,
- ref_1.type_name as c2,
- ref_2.owner as c3,
- ref_2.optimize as c4,
- ref_1.function_type as c5,
- cast(nullif(ref_1.function_type, ref_1.function_id) as int) as c6,
- ref_1.type_name as c7,
+ ref_0.col4 as c0,
+ ref_0.col2 as c1,
+ ref_1.col6 as c2,
+ ref_2.col1 as c3,
+ ref_2.col5 as c4,
+ ref_1.col4 as c5,
+ cast(nullif(ref_1.col4, ref_1.col1) as int) as c6,
+ ref_1.col6 as c7,
73 as c8,
- ref_2.query as c9,
- ref_1.function_name as c10,
- ref_1.function_id as c11,
- ref_1.type_id as c12,
- ref_2.pipe as c13,
+ ref_2.col2 as c9,
+ ref_1.col2 as c10,
+ ref_1.col1 as c11,
+ ref_1.col5 as c12,
+ ref_2.col3 as c13,
case when exists (
select
- ref_0.owner as c0,
- ref_2.query as c1,
- ref_2.query as c2,
- ref_0.authorization as c3,
- ref_2.plan as c4,
+ ref_0.col3 as c0,
+ ref_2.col2 as c1,
+ ref_2.col2 as c2,
+ ref_0.col5 as c3,
+ ref_2.col4 as c4,
ref_3.col8 as c5,
- ref_0.owner as c6,
- ref_2.mal as c7,
+ ref_0.col3 as c6,
+ ref_2.col6 as c7,
ref_3.col2 as c8,
- ref_0.name as c9,
- ref_2.defined as c10,
- ref_1.function_id as c11,
- ref_1.depend_type as c12,
- ref_1.function_type as c13,
- ref_2.plan as c14
+ ref_0.col1 as c9,
+ ref_0.col6 as c10,
+ ref_1.col1 as c11,
+ ref_1.col3 as c12,
+ ref_1.col4 as c13,
+ ref_2.col4 as c14
from
- sys.another_t as ref_3
+ another_t as ref_3
where ref_3.col3 is not null
- ) then ref_1.function_id
- else ref_1.function_id end as c14
+ ) then ref_1.col1
+ else ref_1.col1 end as c14
from
- sys.dependency_functions_on_types as ref_1
- inner join sys.querylog_catalog as ref_2
- on (ref_0.owner is not null)
+ LongTable as ref_1
+ inner join tbl_ProductSales as ref_2
+ on (ref_0.col3 is not null)
where false)
)
- limit 97;
-
-CREATE TABLE tab1(col0 INTEGER, col1 INTEGER, col2 INTEGER);
-INSERT INTO tab1 VALUES (51,14,96), (85,5,59), (91,47,68);
+ limit 97; --empty on PostgreSQL
select
subq_0.c0 as c0,
@@ -79,13 +91,13 @@ from
ref_0.action as c2
from
tmp.keys as ref_0
- right join sys.dependency_schemas_on_users as ref_1
+ right join dependency_schemas_on_users as ref_1
on (exists (
select
ref_0.table_id as c0,
ref_1.depend_type as c1
from
- sys.tab1 as ref_2
+ tab1 as ref_2
where (exists (
select
ref_3.owner_name as c0,
@@ -99,7 +111,7 @@ from
ref_2.col0 as c8,
88 as c9
from
- sys.dependency_owners_on_schemas as ref_3
+ dependency_owners_on_schemas as ref_3
where 69 is null))
and ((false)
or ((true)
@@ -111,14 +123,13 @@ from
ref_0.rkey as c3,
ref_4.system as c4
from
- sys._tables as ref_4
+ _tables as ref_4
where ref_2.col2 is not null))))))
where (ref_0.name is null)
and (76 is null)
limit 101) as subq_0
where true
-limit 67
-;
+limit 67;
SELECT subq_1.c0 AS c0,
subq_1.c0 AS c1,
@@ -127,19 +138,19 @@ SELECT subq_1.c0 AS c0,
subq_1.c0 AS c4,
subq_0.c0 AS c5
FROM (SELECT ref_0.function_type_name AS c0
- FROM sys.function_types AS ref_0
+ FROM function_types AS ref_0
WHERE false
LIMIT 143) AS subq_0
LEFT JOIN (SELECT ref_1.table_id AS c0
- FROM sys.table_partitions AS ref_1
+ FROM table_partitions AS ref_1
WHERE ( false )
AND ( EXISTS
(SELECT ref_2.fk_name AS c0,
ref_1.column_id AS c1,
ref_1.type AS c2
- FROM sys.dependency_tables_on_foreignkeys AS
+ FROM dependency_tables_on_foreignkeys AS
ref_2
- INNER JOIN sys.dependency_keys_on_foreignkeys
AS
+ INNER JOIN dependency_keys_on_foreignkeys AS
ref_3
ON ( ( false )
AND ( ( ref_3.fk_name IS NULL )
@@ -189,7 +200,7 @@ FROM (SELECT ref_0.function_type_name
c4,
ref_1.table_id AS
c5
- FROM sys.table_types AS ref_4
+ FROM table_types AS ref_4
WHERE ( true )
AND ( ref_2.table_name
IS NULL )
@@ -212,32 +223,24 @@ select
subq_0.c0 as c7
from
(select
- ref_4.type as c0
+ ref_4.col1 as c0
from
- sys.storagemodel as ref_0
- right join sys.triggers as ref_1
- right join sys.environment as ref_2
+ tab1 as ref_0
+ right join analytics as ref_1
+ right join tab2 as ref_2
on (exists (
select
- ref_2.name as c0,
- ref_2.value as c1
+ ref_2.col0 as c0,
+ ref_2.col1 as c1
from
- sys.dependency_owners_on_schemas as ref_3
- where ref_1.table_id is null))
- on (ref_0.type = ref_2.name )
- inner join sys.idxs as ref_4
- on (ref_1.time is not null)
+ tbl_ProductSales as ref_3
+ where ref_1.aa is null))
+ on (ref_0.col1 = ref_2.col0 )
+ inner join tab0 as ref_4
+ on (ref_1.cc is not null)
where true) as subq_0
where subq_0.c0 is not null
-limit 107
-;
-
-CREATE TABLE integers(i INTEGER);
-INSERT INTO integers VALUES (1), (2), (3), (NULL);
-CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
-INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
-create table analytics (aa int, bb int, cc bigint);
-insert into analytics values (15, 3, 15), (3, 1, 3), (2, 1, 2), (5, 3, 5),
(NULL, 2, NULL), (3, 2, 3), (4, 1, 4), (6, 3, 6), (8, 2, 8), (NULL, 4, NULL);
+limit 107; --empty on PostgreSQL
SELECT subq_1.c3 AS c0
FROM
@@ -270,7 +273,7 @@ FROM
ref_0.i AS c8,
ref_0.i AS c9,
ref_0.i AS c10
- FROM sys.integers AS ref_0
+ FROM integers AS ref_0
WHERE (ref_0.i IS NOT NULL)
OR (TRUE)) AS subq_0
WHERE CASE
@@ -282,7 +285,7 @@ FROM
subq_0.c1 AS c2,
subq_0.c3 AS c3,
subq_0.c10 AS c4
- FROM sys.value_partitions AS ref_1
+ FROM value_partitions AS ref_1
WHERE (ref_1.value IS NULL)
OR ((subq_0.c2 IS NULL)
OR ((FALSE)
@@ -293,15 +296,15 @@ FROM
AND (((((TRUE)
AND (((EXISTS
(SELECT 69 AS c0,
- ref_2.product_category AS c1,
- ref_2.product_name AS c2,
- ref_2.totalsales AS c3,
+ ref_2.col1 AS c1,
+ ref_2.col2 AS c2,
+ ref_2.col3 AS c3,
subq_0.c0 AS c4,
- ref_2.colid AS c5,
+ ref_2.col4 AS c5,
subq_0.c5 AS c6,
subq_0.c8 AS c7,
subq_0.c10 AS c8
- FROM sys.tbl_productsales AS ref_2
+ FROM tbl_productsales AS ref_2
WHERE (FALSE)
OR ((FALSE)
AND ((TRUE)
@@ -326,12 +329,12 @@ FROM
subq_0.c4 AS c10,
ref_3.id AS c11,
subq_0.c5 AS c12
- FROM sys.sequences AS ref_3
+ FROM sequences AS ref_3
WHERE (ref_3.start IS NULL)
OR (EXISTS
(SELECT subq_0.c1 AS c0,
ref_3.name AS c1
- FROM sys.analytics AS ref_4
+ FROM analytics AS ref_4
WHERE (TRUE)
AND (FALSE)))))
OR ((TRUE)
@@ -369,7 +372,7 @@ from
ref_0.table_schema_id as c8,
ref_0.table_schema_id as c9
from
- sys.dependency_tables_on_indexes as ref_0
+ dependency_tables_on_indexes as ref_0
where true) as subq_0
where subq_0.c8 is not null
limit 46) as subq_1
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
