Changeset: 2a9ed54b4634 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2a9ed54b4634
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/test/SQLancer/Tests/sqlancer07.stable.out
sql/test/SQLancer/Tests/sqlancer07.stable.out.int128
Branch: octbugs
Log Message:
fix more sqlancer bugs.
Make sure we don't push semijoins down outer joins (or wrong sides of
left/right outer joins)
flatten simple selects a bit (1, select 2) -> 1,2 values list
fixed buts in rewrite_values (ie propagate single not always set single)
moved rewrite_values before rewrite_complex
diffs (264 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
@@ -4865,7 +4865,7 @@ rel_push_semijoin_down_or_up(visitor *v,
l = l->l;
*/
- if (!is_join(l->op) || rel_is_ref(l))
+ if (!is_join(l->op) || is_full(l->op) || rel_is_ref(l))
return rel;
lop = l->op;
@@ -4897,6 +4897,10 @@ rel_push_semijoin_down_or_up(visitor *v,
if (!right && !left)
return rel;
}
+ if (left && is_right(lop))
+ return rel;
+ if (right && is_left(lop))
+ return rel;
nsexps = exps_copy(v->sql, rel->exps);
njexps = exps_copy(v->sql, l->exps);
if (left)
@@ -4907,7 +4911,7 @@ rel_push_semijoin_down_or_up(visitor *v,
if (left)
l = rel_crossproduct(v->sql->sa, l, rel_dup(lr), lop);
else
- l = rel_crossproduct(v->sql->sa, l, rel_dup(ll), lop);
+ l = rel_crossproduct(v->sql->sa, rel_dup(ll), l, lop);
l->exps = njexps;
rel_destroy(rel);
rel = l;
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -1822,6 +1822,17 @@ rel_exists_exp(sql_query *query, sql_rel
return NULL;
}
+static int
+is_project_true(sql_rel *r)
+{
+ if (r && !r->l && list_length(r->exps) == 1) {
+ sql_exp *e = r->exps->h->data;
+ if (exp_is_atom(e) && exp_is_true(e))
+ return 1;
+ }
+ return 0;
+}
+
static sql_exp *
rel_in_value_exp(sql_query *query, sql_rel **rel, symbol *sc, int f)
{
@@ -1880,6 +1891,10 @@ rel_in_value_exp(sql_query *query, sql_r
if (r->nrcols != ek.type)
return sql_error(sql, 02,
SQLSTATE(42000) "Subquery has too %s columns", (r->nrcols < ek.type) ? "few" :
"many");
re = exp_rel_label(sql, re);
+ } else if (exp_is_rel(re)) {
+ sql_rel *r = exp_rel_get_rel(sql->sa, re);
+ if (is_project(r->op) && is_project_true(r->l)
&& list_length(r->exps) == 1)
+ re = r->exps->h->data;
}
append(vals, re);
}
@@ -5835,7 +5850,7 @@ rel_crossquery(sql_query *query, sql_rel
rname2 = rel_name(t2);
if (rname1 && rname2 && strcmp(rname1, rname2) == 0)
return sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' on both
sides of the CROSS JOIN expression", rname1);
-
+
if (refs) {
if (list_find(refs, (char *)rname1, (fcmp) &strcmp))
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
relation name \"%s\" specified more than once", rname1);
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -3212,11 +3212,12 @@ rewrite_complex(visitor *v, sql_rel *rel
static sql_rel *
rewrite_values(visitor *v, sql_rel *rel)
{
+ int single = is_single(rel);
if (!is_simple_project(rel->op) || list_empty(rel->exps))
return rel;
sql_exp *e = rel->exps->h->data;
- if (!is_values(e) || list_length(exp_get_values(e))<=1 ||
!rel_has_freevar(v->sql, rel))
+ if (!is_values(e) || list_length(exp_get_values(e))<=1 ||
(!exp_has_freevar(v->sql, e) && !exp_has_rel(e)))
return rel;
list *exps = sa_list(v->sql->sa);
@@ -3246,7 +3247,8 @@ rewrite_values(visitor *v, sql_rel *rel)
cur = nrel;
}
rel = cur;
- set_single(rel);
+ if (single)
+ set_single(rel);
}
return rel;
}
@@ -3278,6 +3280,8 @@ rel_unnest(mvc *sql, sql_rel *rel)
rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_rank, false);
rel = rel_visitor_bottomup(&v, rel, &rewrite_outer2inner_union);
+ rel = rel_visitor_bottomup(&v, rel, &rewrite_values); /* must come
before rewrite_exp_rel */
+
// remove empty project/groupby !
rel = rel_visitor_bottomup(&v, rel, &rewrite_empty_project);
rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_complex, true);
@@ -3285,7 +3289,6 @@ rel_unnest(mvc *sql, sql_rel *rel)
rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_ifthenelse, false);
/* add isnull handling */
rel = rel_exp_visitor_bottomup(&v, rel, &reset_exp_used, false);
/* reset used flag from ifthenelse re-writer, so it can be used again by the
rel_dce optimizer */
- rel = rel_visitor_bottomup(&v, rel, &rewrite_values); /* must come
before rewrite_exp_rel */
rel = rel_exp_visitor_bottomup(&v, rel, &rewrite_exp_rel, true);
rel = rel_visitor_bottomup(&v, rel, &rewrite_join2semi); /*
where possible convert anyequal functions into marks */
if (v.changes > 0)
diff --git a/sql/test/SQLancer/Tests/sqlancer07.stable.out
b/sql/test/SQLancer/Tests/sqlancer07.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer07.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer07.stable.out
@@ -152,12 +152,12 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES
('4'),('4'),('3eSU8,'),(''),('5E~쟱'),('~'),('1386006226'),('0.19005213960704492'),('''{Mdd뒆VB'''),('\015%L%]'),('+'),(''),('㕚o+k');
[ 13 ]
#select t0.c1 from t0 where (5) in (case when t0.c1 = 'a' then 1 end, (select
3));
-% .t0 # table_name
+% sys.t0 # table_name
% c1 # name
% varchar # type
% 0 # length
#select t0.c1 from t0 where (5) in ((select 3), case when t0.c1 = 'a' then 1
end);
-% .t0 # table_name
+% sys.t0 # table_name
% c1 # name
% varchar # type
% 0 # length
@@ -201,8 +201,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES (596983192), (-601428889), (1688368391);
[ 3 ]
#SELECT 1 FROM t2, t0 CROSS JOIN t1 WHERE t0.c0 % (SELECT 1 WHERE FALSE) <=
t1.c0;
-% .%4 # table_name
-% %4 # name
+% .%3 # table_name
+% %3 # name
% tinyint # type
% 1 # length
#SELECT t1.c0 FROM t2, t0 CROSS JOIN t1 WHERE ((((t0.c0)%((SELECT DISTINCT
t0.c0 FROM t1, t0, t2 WHERE FALSE))))<=(t1.c0));
@@ -321,8 +321,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t1" VALUES (1);
[ 1 ]
#SELECT 1 FROM t0 WHERE (EXISTS (SELECT 1 WHERE FALSE)) IN ((SELECT FALSE));
-- rel_push_semijoin_down_or_up is causing the issue
-% .%11 # table_name
-% %11 # name
+% .%7 # table_name
+% %7 # name
% tinyint # type
% 1 # length
#SELECT t0.c0 FROM t0 WHERE (EXISTS (SELECT TRUE, 0.3, INTERVAL '5' MONTH,
TIME '16:59:58' WHERE FALSE)) IN ((SELECT DISTINCT FALSE FROM t1), (('0.9')NOT
ILIKE('')));
@@ -426,8 +426,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES (446453788), (1973525566), (1604369323),
(-907882617), (1067873323), (1615259391), (1286378024);
[ 7 ]
#SELECT 1 FROM t0 RIGHT OUTER JOIN t1 ON 3 IN ((SELECT 2), t1.c0);
-% .%10 # table_name
-% %10 # name
+% .%6 # table_name
+% %6 # name
% tinyint # type
% 1 # length
[ 1 ]
@@ -454,6 +454,18 @@ stdout of test 'sqlancer07` in directory
% %10 # name
% tinyint # type
% 1 # length
+#SELECT 1 FROM (SELECT 1) as v3(c0) RIGHT JOIN (SELECT 2) as v4(c0) ON (0.6)
NOT IN (0.5, (SELECT 0.2));
+% .%7 # table_name
+% %7 # name
+% tinyint # type
+% 1 # length
+[ 1 ]
+#SELECT * FROM (SELECT 1) as v3(c0) LEFT JOIN (SELECT 2) as v4(c0) ON (0.6)
NOT IN ((SELECT v4.c0), 0.5);
+% .v3, .v4 # table_name
+% c0, c0 # name
+% tinyint, tinyint # type
+% 1, 1 # length
+[ 1, 2 ]
#SELECT FALSE FROM v3, v0 FULL OUTER JOIN v4 ON sql_max(FALSE, (0.6902854) NOT
IN (0.5854332103580835, CASE WHEN v4.vc0 THEN (SELECT 0.023816515 WHERE TRUE)
END));
% .%16 # table_name
% %16 # name
diff --git a/sql/test/SQLancer/Tests/sqlancer07.stable.out.int128
b/sql/test/SQLancer/Tests/sqlancer07.stable.out.int128
--- a/sql/test/SQLancer/Tests/sqlancer07.stable.out.int128
+++ b/sql/test/SQLancer/Tests/sqlancer07.stable.out.int128
@@ -152,12 +152,12 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES
('4'),('4'),('3eSU8,'),(''),('5E~쟱'),('~'),('1386006226'),('0.19005213960704492'),('''{Mdd뒆VB'''),('\015%L%]'),('+'),(''),('㕚o+k');
[ 13 ]
#select t0.c1 from t0 where (5) in (case when t0.c1 = 'a' then 1 end, (select
3));
-% .t0 # table_name
+% sys.t0 # table_name
% c1 # name
% varchar # type
% 0 # length
#select t0.c1 from t0 where (5) in ((select 3), case when t0.c1 = 'a' then 1
end);
-% .t0 # table_name
+% sys.t0 # table_name
% c1 # name
% varchar # type
% 0 # length
@@ -201,8 +201,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES (596983192), (-601428889), (1688368391);
[ 3 ]
#SELECT 1 FROM t2, t0 CROSS JOIN t1 WHERE t0.c0 % (SELECT 1 WHERE FALSE) <=
t1.c0;
-% .%4 # table_name
-% %4 # name
+% .%3 # table_name
+% %3 # name
% tinyint # type
% 1 # length
#SELECT t1.c0 FROM t2, t0 CROSS JOIN t1 WHERE ((((t0.c0)%((SELECT DISTINCT
t0.c0 FROM t1, t0, t2 WHERE FALSE))))<=(t1.c0));
@@ -321,8 +321,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t1" VALUES (1);
[ 1 ]
#SELECT 1 FROM t0 WHERE (EXISTS (SELECT 1 WHERE FALSE)) IN ((SELECT FALSE));
-- rel_push_semijoin_down_or_up is causing the issue
-% .%11 # table_name
-% %11 # name
+% .%7 # table_name
+% %7 # name
% tinyint # type
% 1 # length
#SELECT t0.c0 FROM t0 WHERE (EXISTS (SELECT TRUE, 0.3, INTERVAL '5' MONTH,
TIME '16:59:58' WHERE FALSE)) IN ((SELECT DISTINCT FALSE FROM t1), (('0.9')NOT
ILIKE('')));
@@ -426,8 +426,8 @@ stdout of test 'sqlancer07` in directory
#INSERT INTO "t2" VALUES (446453788), (1973525566), (1604369323),
(-907882617), (1067873323), (1615259391), (1286378024);
[ 7 ]
#SELECT 1 FROM t0 RIGHT OUTER JOIN t1 ON 3 IN ((SELECT 2), t1.c0);
-% .%10 # table_name
-% %10 # name
+% .%6 # table_name
+% %6 # name
% tinyint # type
% 1 # length
[ 1 ]
@@ -454,12 +454,25 @@ stdout of test 'sqlancer07` in directory
% %10 # name
% tinyint # type
% 1 # length
+#SELECT 1 FROM (SELECT 1) as v3(c0) RIGHT JOIN (SELECT 2) as v4(c0) ON (0.6)
NOT IN (0.5, (SELECT 0.2));
+% .%7 # table_name
+% %7 # name
+% tinyint # type
+% 1 # length
+[ 1 ]
+#SELECT * FROM (SELECT 1) as v3(c0) LEFT JOIN (SELECT 2) as v4(c0) ON (0.6)
NOT IN ((SELECT v4.c0), 0.5);
+% .v3, .v4 # table_name
+% c0, c0 # name
+% tinyint, tinyint # type
+% 1, 1 # length
+[ 1, 2 ]
#SELECT FALSE FROM v3, v0 FULL OUTER JOIN v4 ON sql_max(FALSE, (0.6902854) NOT
IN (0.5854332103580835, CASE WHEN v4.vc0 THEN (SELECT 0.023816515 WHERE TRUE)
END));
% .%16 # table_name
% %16 # name
% boolean # type
% 5 # length
[ false ]
+[ false ]
#ROLLBACK;
#SELECT CAST(((24829)+(((0.9767751031140547)*(0.7479400824095245)))) AS
DOUBLE) IS NULL;
% .%3 # table_name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
