Changeset: e0215c4ce5c1 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/e0215c4ce5c1
Added Files:
sql/test/SQLancer/Tests/sqlancer18.test
Modified Files:
sql/server/rel_optimizer.c
sql/test/SQLancer/Tests/All
sql/test/SQLancer/Tests/sqlancer17.test
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: Jul2021
Log Message:
Sqlancer fix. If there's a projection between the selection and the set
relation, then the expressions to be pushed have to be checked. Instead wait
for the upper optimizer to push them under the projection, then push again
under the set
diffs (truncated from 701 to 300 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -4712,51 +4712,30 @@ rel_push_select_down(visitor *v, sql_rel
}
/* try push select under set relation */
- if (is_select(rel->op) && r && !rel_is_ref(r) && !is_single(r) &&
!list_empty(exps)) {
- sql_rel *u = r, *ou = u;
- sql_rel *ul = u->l;
- sql_rel *ur = u->r;
-
- if (!rel_is_ref(u) && !is_single(u) && u->op == op_project)
- u = u->l;
-
- if (u && is_set(u->op) && !is_single(u) && !list_empty(u->exps)
&& !rel_is_ref(u)) {
- ul = u->l;
- ur = u->r;
-
- ul = rel_dup(ul);
- ur = rel_dup(ur);
- if (!is_project(ul->op))
- ul = rel_project(v->sql->sa, ul,
- rel_projections(v->sql, ul, NULL, 1,
1));
- if (!is_project(ur->op))
- ur = rel_project(v->sql->sa, ur,
- rel_projections(v->sql, ur, NULL, 1,
1));
- rel_rename_exps(v->sql, u->exps, ul->exps);
- rel_rename_exps(v->sql, u->exps, ur->exps);
-
- if (u != ou) {
- ul = rel_project(v->sql->sa, ul, NULL);
- ul->exps = exps_copy(v->sql, ou->exps);
- rel_rename_exps(v->sql, ou->exps, ul->exps);
- set_processed(ul);
- ur = rel_project(v->sql->sa, ur, NULL);
- ur->exps = exps_copy(v->sql, ou->exps);
- rel_rename_exps(v->sql, ou->exps, ur->exps);
- set_processed(ur);
- }
-
- /* introduce selects under the set */
- ul = rel_select(v->sql->sa, ul, NULL);
- ul->exps = exps_copy(v->sql, exps);
- ur = rel_select(v->sql->sa, ur, NULL);
- ur->exps = exps_copy(v->sql, exps);
-
- rel = rel_inplace_setop(v->sql, rel, ul, ur, u->op,
rel_projections(v->sql, rel, NULL, 1, 1));
- if (need_distinct(u))
- set_distinct(rel);
- v->changes++;
- }
+ if (is_select(rel->op) && r && is_set(r->op) && !list_empty(r->exps) &&
!rel_is_ref(r) && !is_single(r) && !list_empty(exps)) {
+ sql_rel *u = r, *ul = u->l, *ur = u->r;
+
+ ul = rel_dup(ul);
+ ur = rel_dup(ur);
+ if (!is_project(ul->op))
+ ul = rel_project(v->sql->sa, ul,
+ rel_projections(v->sql, ul, NULL, 1, 1));
+ if (!is_project(ur->op))
+ ur = rel_project(v->sql->sa, ur,
+ rel_projections(v->sql, ur, NULL, 1, 1));
+ rel_rename_exps(v->sql, u->exps, ul->exps);
+ rel_rename_exps(v->sql, u->exps, ur->exps);
+
+ /* introduce selects under the set */
+ ul = rel_select(v->sql->sa, ul, NULL);
+ ul->exps = exps_copy(v->sql, exps);
+ ur = rel_select(v->sql->sa, ur, NULL);
+ ur->exps = exps_copy(v->sql, exps);
+
+ rel = rel_inplace_setop(v->sql, rel, ul, ur, u->op,
rel_projections(v->sql, rel, NULL, 1, 1));
+ if (need_distinct(u))
+ set_distinct(rel);
+ v->changes++;
}
return try_remove_empty_select(v, rel);
diff --git a/sql/test/SQLancer/Tests/All b/sql/test/SQLancer/Tests/All
--- a/sql/test/SQLancer/Tests/All
+++ b/sql/test/SQLancer/Tests/All
@@ -14,4 +14,5 @@ sqlancer13
sqlancer14
sqlancer15
sqlancer16
-KNOWNFAIL?sqlancer17
+sqlancer17
+KNOWNFAIL?sqlancer18
diff --git a/sql/test/SQLancer/Tests/sqlancer17.test
b/sql/test/SQLancer/Tests/sqlancer17.test
--- a/sql/test/SQLancer/Tests/sqlancer17.test
+++ b/sql/test/SQLancer/Tests/sqlancer17.test
@@ -33,263 +33,57 @@ statement ok
START TRANSACTION
statement ok
-CREATE TABLE "t0" ("c2" DATE, CONSTRAINT "t0_c2_pkey" PRIMARY KEY ("c2"))
-
-statement ok rowcount 2
-INSERT INTO "t0" VALUES (DATE '1970-01-04'), (DATE '1970-01-01')
-
-query T rowsort
-SELECT t0.c2 FROM t0 WHERE (t0.c2) IN (t0.c2, (VALUES (DATE '1969-12-10'),
(DATE '1970-01-01')))
-----
-1970-01-01
-1970-01-04
+create or replace view v18(vc0) as (select sql_max(1, 1) from ((select 2)
intersect all (select 4)) v0(vc0) where 1 not between v0.vc0 and 3*v0.vc0)
query I rowsort
-SELECT CAST(SUM(count) AS BIGINT) FROM (SELECT CAST((t0.c2) IN (t0.c2, (VALUES
(DATE '1969-12-10'), (DATE '1970-01-01'))) AS INT) as count FROM t0) as res
+SELECT v18.vc0 FROM v18 WHERE -7 NOT BETWEEN 0.4 AND v18.vc0
----
-2
+
+query I rowsort
+SELECT -7 NOT BETWEEN 0.4 AND v18.vc0 FROM v18
+----
statement ok
-CREATE TABLE "t1" ("c2" BIGINT NOT NULL, CONSTRAINT "t1_c2_pkey" PRIMARY KEY
("c2"))
-
-statement ok rowcount 4
-INSERT INTO "t1" VALUES (69), (-12), (9), (0)
+create or replace view v19(vc0) as (select vc0 from ((select 2) intersect all
(select 4)) v0(vc0) where 1 not between v0.vc0 and 3*v0.vc0)
query I rowsort
-SELECT -3 < least((SELECT 1 WHERE FALSE), (SELECT DISTINCT 2 FROM t1)) FROM t1
+SELECT v19.vc0 FROM v19 WHERE -7 NOT BETWEEN 0.4 AND v19.vc0
----
-True
-True
-True
-True
query I rowsort
-SELECT t1.c2 FROM t1 WHERE -3 < least((SELECT 1 WHERE FALSE), (SELECT 2 FROM
t1 GROUP BY DATE '1970-01-11'))
+SELECT -7 NOT BETWEEN 0.4 AND v19.vc0 FROM v19
----
--12
-0
-69
-9
+
+statement ok
+create or replace view v20(vc0,vc1) as (select sql_max(1, 1), vc0 from
((select 2) intersect all (select 4)) v0(vc0) where 1 not between v0.vc0 and
v0.vc0)
query I rowsort
-SELECT least((SELECT 1 WHERE FALSE), (SELECT DISTINCT 2 FROM t1)) > -3 FROM t1
-----
-True
-True
-True
-True
-
-query I rowsort
-SELECT t1.c2 FROM t1 WHERE least((SELECT 1 WHERE FALSE), (SELECT 2 FROM t1
GROUP BY DATE '1970-01-11')) > -3
+SELECT v20.vc0 FROM v20 WHERE 7 > v20.vc0 and v20.vc1 between 3 and 5
----
--12
-0
-69
-9
-
-statement ok
-ROLLBACK
-
-statement ok
-START TRANSACTION
-
-statement ok
-CREATE TABLE "t0" ("c3" INTERVAL SECOND)
-statement ok rowcount 9
-INSERT INTO "t0" VALUES (INTERVAL '6' SECOND),(INTERVAL '9' SECOND),(INTERVAL
'1' SECOND),
-(INTERVAL '0' SECOND),(INTERVAL '9' SECOND),(INTERVAL '4' SECOND),(INTERVAL
'6' SECOND),(INTERVAL '1' SECOND),(NULL)
-
-query I rowsort
-SELECT (SELECT 2 WHERE FALSE) = ANY(SELECT 3 WHERE FALSE)
-----
-False
-
-query T rowsort
-SELECT t0.c3 FROM t0 WHERE ifthenelse((SELECT 2 WHERE FALSE) = ANY(SELECT 3
WHERE FALSE), 2, 1)
+# '7 > v20.vc0' doesn't get pushed, while 'v20.vc1 between 3 and 5' does
+query T nosort
+plan SELECT 1 FROM v20 WHERE 7 > v20.vc0 and v20.vc1 between 3 and 5
----
-0:00:00
-0:00:01
-0:00:01
-0:00:04
-0:00:06
-0:00:06
-0:00:09
-0:00:09
-NULL
-
-query I rowsort
-SELECT CAST(SUM(count) AS BIGINT) FROM (SELECT CAST(ifthenelse((SELECT 2 WHERE
FALSE) = ANY(SELECT 3 WHERE FALSE), 2, 1) AS INT) as count FROM t0) as res
-----
-9
+project (
+| select (
+| | project (
+| | | intersect (
+| | | | project (
+| | | | | select (
+| | | | | | [ boolean "true" ]
+| | | | | ) [ tinyint "2" ! <= tinyint "1" ! <= tinyint "2" BETWEEN , tinyint
"3" <= tinyint "2" <= tinyint "5" BETWEEN ]
+| | | | ) [ tinyint "2" as "v0"."vc0" ],
+| | | | project (
+| | | | | select (
+| | | | | | [ boolean "true" ]
+| | | | | ) [ tinyint "4" ! <= tinyint "1" ! <= tinyint "4" BETWEEN , tinyint
"3" <= tinyint "4" <= tinyint "5" BETWEEN ]
+| | | | ) [ tinyint "4" as "v0"."vc0" ]
+| | | ) [ "v0"."vc0" NOT NULL ]
+| | ) [ "sys"."sql_max"(tinyint "1", tinyint "1") NOT NULL as "v20"."vc0" ]
+| ) [ tinyint "7" > "v20"."vc0" NOT NULL ]
+) [ tinyint "1" ]
statement ok
ROLLBACK
-statement ok
-START TRANSACTION
-
-statement ok
-CREATE TABLE "t2" ("c2" REAL)
-
-statement ok rowcount 142
-COPY 142 RECORDS INTO "t2" FROM stdin USING DELIMITERS E'\t',E'\n','"'
-<COPY_INTO_DATA>
-NULL
-0.18141033
-0.10420329
-NULL
--1.2681471e+09
-0.6239734
-NULL
-0.37928414
-0.7922281
-0.49325344
--1.7018686e+17
-0.070922926
-4
-8.981221e+07
-0.17222267
-0.21218215
-0.58062196
-0.419161
-0.84214187
-0.29322016
-0.29322016
--0
-0.7530656
-0
--1
--2.0448145e+09
-0.2540231
-0.49195638
-0.0023798633
-0.6432879
-7
-NULL
-0.61336726
-0.37398192
--9.4684614e+08
-0.18818615
-0.5136187
-3
-0.12575655
-0.29542512
-0.7088062
--8.094762e+08
-110847336
-0.3245087
-0.35633564
-0.311399
-0.9717446
--8
-1
-6
-0.43797848
-1.82855e+09
-0.5382776
-0.5785479
-NULL
-0.34842148
-0.35951182
-0
-0.3906244
-0.7785596
-0.9109514
-0.38354927
-0.28305355
-1.9593509e+08
-0.89934695
-0.9522356
-0.6559638
-0.2916218
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
