MonetDB: default - Approved output and new sqlancer bug: full ou...

[Pedro Ferreira]

Changeset: 02ad4764b86b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=02ad4764b86b
Modified Files:
        sql/test/SQLancer/Tests/sqlancer02.sql
        sql/test/SQLancer/Tests/sqlancer04.stable.out
Branch: default
Log Message:

Approved output and new sqlancer bug: full outer join with wrong results. (I 
think it's from out2in optimizer)


diffs (104 lines):

diff --git a/sql/test/SQLancer/Tests/sqlancer02.sql 
b/sql/test/SQLancer/Tests/sqlancer02.sql
--- a/sql/test/SQLancer/Tests/sqlancer02.sql
+++ b/sql/test/SQLancer/Tests/sqlancer02.sql
@@ -133,3 +133,35 @@ ROLLBACK;
 SELECT 1 WHERE scale_up(CAST(0.89767724 AS REAL), 1); --error function 
scale_up not available for real,tinyint
 
 SELECT scale_up(0.2928163, 3);
+
+START TRANSACTION;
+CREATE TABLE "sys"."t0" ("c0" BOOLEAN NOT NULL);
+COPY 15 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+true
+false
+true
+false
+true
+false
+true
+true
+false
+true
+false
+false
+true
+false
+true
+
+SELECT count(*) FROM t0 FULL OUTER JOIN (SELECT 1 FROM t0) AS sub0 ON t0.c0;
+SELECT count(*) FROM t0 FULL OUTER JOIN (SELECT 1 FROM t0) AS sub0 ON t0.c0 
WHERE t0.c0;
+
+SELECT CAST(sum(- (((((abs(2))*("quarter"(DATE 
'1970-01-25'))))<<("minute"(INTERVAL '1279040638' SECOND))))) as BIGINT) FROM 
t0 FULL OUTER JOIN (SELECT greatest(least(INTERVAL '-33334168' SECOND, INTERVAL 
'41947202' SECOND), greatest(INTERVAL '1134741726' SECOND, INTERVAL 
'1713690410' SECOND)), t0.c0 FROM t0 WHERE t0.c0) AS sub0 ON t0.c0;
+SELECT CAST(SUM(agg0) as BIGINT) FROM (
+SELECT sum(- (((((abs(2))*("quarter"(DATE '1970-01-25'))))<<("minute"(INTERVAL 
'1279040638' SECOND))))) as agg0 FROM t0 FULL OUTER JOIN (SELECT 
greatest(least(INTERVAL '-33334168' SECOND, INTERVAL '41947202' SECOND), 
greatest(INTERVAL '1134741726' SECOND, INTERVAL '1713690410' SECOND)), t0.c0 
FROM t0 WHERE t0.c0) AS sub0 ON t0.c0 WHERE t0.c0
+UNION ALL
+SELECT sum(- (((((abs(2))*("quarter"(DATE '1970-01-25'))))<<("minute"(INTERVAL 
'1279040638' SECOND)))))  as agg0 FROM t0 FULL OUTER JOIN (SELECT 
greatest(least(INTERVAL '-33334168' SECOND, INTERVAL '41947202' SECOND), 
greatest(INTERVAL '1134741726' SECOND, INTERVAL '1713690410' SECOND)), t0.c0 
FROM t0 WHERE t0.c0) AS sub0 ON t0.c0 WHERE NOT (t0.c0)
+UNION ALL
+SELECT sum(- (((((abs(2))*("quarter"(DATE '1970-01-25'))))<<("minute"(INTERVAL 
'1279040638' SECOND)))))  as agg0 FROM t0 FULL OUTER JOIN (SELECT 
greatest(least(INTERVAL '-33334168' SECOND, INTERVAL '41947202' SECOND), 
greatest(INTERVAL '1134741726' SECOND, INTERVAL '1713690410' SECOND)), t0.c0 
FROM t0 WHERE t0.c0) AS sub0 ON t0.c0 WHERE (t0.c0) IS NULL
+) as asdf;
+ROLLBACK;
diff --git a/sql/test/SQLancer/Tests/sqlancer04.stable.out 
b/sql/test/SQLancer/Tests/sqlancer04.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer04.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer04.stable.out
@@ -74,6 +74,11 @@ stdout of test 'sqlancer04` in directory
 #START TRANSACTION;
 #CREATE TABLE "sys"."t0" ("c0" DOUBLE NOT NULL,CONSTRAINT "t0_c0_pkey" PRIMARY 
KEY ("c0"));
 #CREATE TABLE "sys"."t1" ("c0" DOUBLE);
+#select coalesce(c0, cast('a' as int)) from t0; --the cast operation shouldn't 
be executed
+% .%2 # table_name
+% %2 # name
+% double # type
+% 24 # length
 #create view v1(c0, c1) as (select distinct 
(((t0.c0)=(((1357695262)^(-922564194))))) = false, cast(coalesce(coalesce(0.3, 
0.4), "second"(timestamp '1970-01-10 11:54:13')) as double) from t0);
 #create view v2(c0) as (select distinct coalesce(abs(interval '-1976292283' 
month), cast(greatest(r'Dnwxjm4btQ9cp&\c''a_', r'(y7,{q?][NHU,') as interval 
month)) from t1, t0 where ((upper(r''))not ilike(lower(r'''wAg_ z 
''PPxXßgrd⍮G'))));
 #select all v2.c0 from v2 right outer join v1 on 
((r'8*By1q)*Oc<n')like(substr(r'', 1151312829, 406714197))) 
@@ -316,6 +321,49 @@ stdout of test 'sqlancer04` in directory
 % time # type
 % 8 # length
 #ROLLBACK;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" (
+#      "c0" INTERVAL SECOND,
+#      "c1" INTERVAL MONTH NOT NULL,
+#      CONSTRAINT "t0_c1_pkey" PRIMARY KEY ("c1"),
+#      CONSTRAINT "t0_c1_unique" UNIQUE ("c1"),
+#      CONSTRAINT "t0_c0_unique" UNIQUE ("c0")
+#);
+#COPY 4 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#NULL  1519431547
+#NULL  557549613
+#1798684773.000        124642631
+#2039246931.000        1575918952
+[ 4    ]
+#CREATE TABLE "sys"."t1" ("c0" INTERVAL SECOND NOT NULL,"c1" INTERVAL MONTH);
+#COPY 4 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#800031356.000 386570239
+#-725381573.000        1244714316
+#1942291856.000        NULL
+#125146072.000 NULL
+[ 4    ]
+#SELECT 1 FROM t1 WHERE CASE WHEN CASE t1.c1 WHEN t1.c1 THEN FALSE ELSE TRUE 
END THEN COALESCE(FALSE, FALSE) END;
+% .%3 # table_name
+% %3 # name
+% tinyint # type
+% 1 # length
+#SELECT t0.c1, t0.c0 FROM t1 FULL OUTER JOIN t0 ON CASE WHEN CASE t1.c1 WHEN 
t1.c1 THEN CAST(FALSE AS INT) ELSE abs(-1677579573) END 
+#THEN NOT (COALESCE(FALSE, FALSE)) WHEN ((COALESCE(1985884175, 53875539, 
r'-427000320'))/(- (-936496635))) THEN (((((FALSE)OR(TRUE)))OR(TRUE))) = FALSE 
ELSE (t1.c1) BETWEEN ASYMMETRIC (t0.c1) AND (t1.c1) END;
+% .t0, .t0 # table_name
+% c1,  c0 # name
+% month_interval,      sec_interval # type
+% 10,  14 # length
+[ 1519431547,  NULL    ]
+[ 557549613,   NULL    ]
+[ 124642631,   1798684773.000  ]
+[ 1575918952,  2039246931.000  ]
+[ 1519431547,  NULL    ]
+[ 557549613,   NULL    ]
+[ 124642631,   1798684773.000  ]
+[ 1575918952,  2039246931.000  ]
+[ NULL,        NULL    ]
+[ NULL,        NULL    ]
+#ROLLBACK;
 
 # 09:44:50 >  
 # 09:44:50 >  "Done."
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list