Changeset: 0a084ae8d84f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0a084ae8d84f
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_rewriter.c
sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
sql/test/miscellaneous/Tests/groupby_error.sql
sql/test/miscellaneous/Tests/groupby_error.stable.out
Branch: Jun2020
Log Message:
Moved isnull rewriter from previous commit to rel_simplify_predicates, because
it cannot be applied on projections. Added optimizations to
rel_simplify_predicates on literal atoms and made a temporary fix for
exp_rename function to not propagate has_no_nil property on column behind an
outer join
diffs (200 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
@@ -1268,7 +1268,10 @@ exp_rename(mvc *sql, sql_exp *e, sql_rel
sql->errstr[0] = 0;
if (!e && exp_is_atom(ne))
return ne;
- return exp_ref(sql ,e);
+ ne = exp_ref(sql, e);
+ if (is_outerjoin(t->op)) /* TODO if e is found on the left
side of the left join or the right of the right join the has_no_nil flag can be
kept */
+ set_has_nil(ne);
+ return ne;
case e_cmp:
if (e->flag == cmp_or || e->flag == cmp_filter) {
list *l = exps_rename(sql, e->l, f, t);
@@ -7460,30 +7463,42 @@ rel_simplify_predicates(mvc *sql, sql_re
return e;
}
}
- if (e->type == e_cmp && (e->flag == cmp_equal || e->flag ==
cmp_notequal)) {
+ if (is_compare(e->type) && is_theta_exp(e->flag)) {
sql_exp *l = e->l;
sql_exp *r = e->r;
- if (l->type == e_func) {
+ if (is_func(l->type) && (e->flag == cmp_equal ||
e->flag == cmp_notequal)) {
sql_subfunc *f = l->f;
/* rewrite isnull(x) = TRUE/FALSE => x =/<>
NULL */
- if (is_select(rel->op) && !f->func->s &&
!strcmp(f->func->base.name, "isnull") &&
- is_atom(r->type) && r->l) { /*
direct literal */
- atom *a = r->l;
- int flag = a->data.val.bval;
+ if (is_select(rel->op) && !f->func->s &&
!strcmp(f->func->base.name, "isnull")) {
list *args = l->l;
-
- assert(list_length(args) == 1);
- l = args->h->data;
- if (exp_subtype(l)) {
- r = exp_atom(sql->sa,
atom_general(sql->sa, exp_subtype(l), NULL));
- e = exp_compare(sql->sa, l, r,
e->flag);
- if (e && !flag)
- set_anti(e);
- if (e)
- set_semantics(e);
+ sql_exp *ie = args->h->data;
+
+ if (!has_nil(ie) ||
exp_is_not_null(sql, ie)) { /* is null on something that is never null, is
always false */
+ ie = exp_atom_bool(sql->sa, 0);
+ (*changes)++;
+ e->l = ie;
+ } else if (exp_is_null(sql, ie)) { /*
is null on something that is always null, is always true */
+ ie = exp_atom_bool(sql->sa, 1);
(*changes)++;
+ e->l = ie;
+ } else if (is_atom(r->type) && r->l) {
/* direct literal */
+ atom *a = r->l;
+ int flag = a->data.val.bval;
+ list *args = l->l;
+
+ assert(list_length(args) == 1);
+ l = args->h->data;
+ if (exp_subtype(l)) {
+ r = exp_atom(sql->sa,
atom_general(sql->sa, exp_subtype(l), NULL));
+ e =
exp_compare(sql->sa, l, r, e->flag);
+ if (e && !flag)
+ set_anti(e);
+ if (e)
+
set_semantics(e);
+ (*changes)++;
+ }
}
} else if (!f->func->s &&
!strcmp(f->func->base.name, "not")) {
if (is_atom(r->type) && r->l) { /*
direct literal */
@@ -7495,7 +7510,7 @@ rel_simplify_predicates(mvc *sql, sql_re
assert(list_length(args) == 1);
/* not(not(x)) = TRUE/FALSE =>
x = TRUE/FALSE */
- if (inner->type == e_func &&
+ if (is_func(inner->type) &&
!inf->func->s &&
!strcmp(inf->func->base.name, "not")) {
int anti = is_anti(e);
@@ -7507,7 +7522,7 @@ rel_simplify_predicates(mvc *sql, sql_re
if (anti) set_anti(e);
(*changes)++;
/* rewrite not(=/<>(a,b)) =
TRUE/FALSE => a=b of a<>b */
- } else if (inner->type ==
e_func &&
+ } else if (is_func(inner->type)
&&
!inf->func->s &&
(!strcmp(inf->func->base.name, "=") ||
!strcmp(inf->func->base.name, "<>"))) {
@@ -7533,6 +7548,21 @@ rel_simplify_predicates(mvc *sql, sql_re
}
}
}
+ } else if (is_atom(l->type) && is_atom(r->type) &&
!is_semantics(e)) {
+ if (exp_is_null(sql, l) || exp_is_null(sql, r))
{
+ e = exp_null(sql->sa, exp_subtype(l));
+ (*changes)++;
+ } else if (l->l && r->l) {
+ int res = atom_cmp(l->l, r->l);
+
+ if (res == 0)
+ e = exp_atom_bool(sql->sa,
(e->flag == cmp_equal || e->flag == cmp_gte || e->flag == cmp_lte) ? 1 : 0);
+ else if (res > 0)
+ e = exp_atom_bool(sql->sa,
(e->flag == cmp_gt || e->flag == cmp_gte || e->flag == cmp_notequal) ? 1 : 0);
+ else
+ e = exp_atom_bool(sql->sa,
(e->flag == cmp_lt || e->flag == cmp_lte || e->flag == cmp_notequal) ? 1 : 0);
+ (*changes)++;
+ }
}
}
}
diff --git a/sql/server/rel_rewriter.c b/sql/server/rel_rewriter.c
--- a/sql/server/rel_rewriter.c
+++ b/sql/server/rel_rewriter.c
@@ -167,24 +167,6 @@ rewrite_simplify_exp(mvc *sql, sql_rel *
return exp_atom_bool(sql->sa, 1);
}
}
- } else if (is_func(e->type) && list_length(e->l) == 1 && is_null(sf)) {
- list *args = e->l;
- sql_exp *ie = args->h->data;
-
- if (!has_nil(ie) || exp_is_false(sql, ie) || exp_is_true(sql,
ie) || exp_is_not_null(sql, ie)) { /* is null on something that is never null,
is always false */
- ie = exp_atom_bool(sql->sa, 0);
- if (exp_name(e))
- exp_prop_alias(sql->sa, ie, e);
- (*changes)++;
- return ie;
- }
- if (exp_is_null(sql, ie)) { /* is null on something that is
always null, is always true */
- ie = exp_atom_bool(sql->sa, 1);
- if (exp_name(e))
- exp_prop_alias(sql->sa, ie, e);
- (*changes)++;
- return ie;
- }
}
return e;
}
diff --git
a/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
b/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
--- a/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
+++ b/sql/test/BugDay_2005-10-06_2.9.3/Tests/bool_boolean.SF-935601.stable.out
@@ -25,7 +25,7 @@ stdout of test 'bool_boolean.SF-935601`
#select isnull(1);
% . # table_name
-% single_value # name
+% %1 # name
% boolean # type
% 5 # length
[ false ]
diff --git
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
---
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
+++
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
@@ -160,11 +160,11 @@ top N (
| | | | | | | | | | | | | | | | table(sys.table8) [ "table8"."t8pkcol" NOT
NULL HASHCOL as "clookup5"."t8pkcol" ] COUNT
| | | | | | | | | | | | | | | ) [ "table2"."t2colc115" = "clookup5"."t8pkcol"
NOT NULL HASHCOL ],
| | | | | | | | | | | | | | | table(sys.table9) [ "table9"."t9pkcol" NOT NULL
HASHCOL , "table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111" ] COUNT
-| | | | | | | | | | | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL =
"table9"."t9cola111" ],
+| | | | | | | | | | | | | | ) [ "table1"."t1pkcol" HASHCOL =
"table9"."t9cola111" ],
| | | | | | | | | | | | | | table(sys.table10) [ "table10"."t10pkcol" NOT NULL
HASHCOL , "table10"."t10cola1", "table10"."t10cola91" ] COUNT
-| | | | | | | | | | | | | ) [ "table9"."t9pkcol" NOT NULL HASHCOL =
"table10"."t10pkcol" NOT NULL HASHCOL ],
+| | | | | | | | | | | | | ) [ "table9"."t9pkcol" HASHCOL =
"table10"."t10pkcol" NOT NULL HASHCOL ],
| | | | | | | | | | | | | table(sys.table11) [ "table11"."t11pkcol" NOT NULL
HASHCOL , "table11"."t11cola91" ] COUNT
-| | | | | | | | | | | | ) [ "table9"."t9pkcol" NOT NULL HASHCOL =
"table11"."t11pkcol" NOT NULL HASHCOL ]
+| | | | | | | | | | | | ) [ "table9"."t9pkcol" HASHCOL = "table11"."t11pkcol"
NOT NULL HASHCOL ]
| | | | | | | | | | | ) [ "table1"."t1pkcol" NOT NULL HASHCOL ,
"table1"."t1cola1", "table1"."t1cola11", "table1"."t1cola12",
"table1"."t1cola82", "table1"."t1cola91", "table1"."t1cola101",
"table1"."t1cola114", "table1"."t1colb1", "table1"."t1colb111",
"table1"."t1colb112", "table1"."t1colb113", "table1"."t1colb114",
"table1"."t1colc91", "table1"."t1cold1", "table1"."t1cold111",
"table1"."t1cold112", "table1"."t1cold113", "table4"."t4cola1",
"table4"."t4cola2", "table4"."t4colb111", "table4"."t4colb112",
"table4"."t4colb114", "table4"."t4colb115", "table2"."t2cola1",
"table2"."t2cola10", "table2"."t2cola81", "table2"."t2cola82",
"table2"."t2cola113", "table2"."t2colc111", "table2"."t2colc112",
"table2"."t2colc113", "table2"."t2colc114", "table2"."t2colc115",
"table5"."t5cola1", "table5"."t5cola2", "table5"."t5cola3", "table5"."t5cola5",
"table5"."t5cola81", "table5"."t5cola113", "table5"."t5colb113",
"lookup1"."t6pkcol" NOT NULL HASHCOL , "lookup2"."t2colb111",
"lookup3"."t5colb112
", "lookup4"."t7colb113", "lookup5"."t8colb114", "lookup11"."t6pkcol" NOT NULL
HASHCOL , "lookup21"."t2colb112", "lookup31"."t5pkcol" NOT NULL HASHCOL ,
"lookup41"."t7pkcol" NOT NULL HASHCOL , "lookup51"."t8pkcol" NOT NULL HASHCOL ,
"clookup1"."t6pkcol" NOT NULL HASHCOL , "clookup2"."t2pkcol" NOT NULL HASHCOL ,
"clookup3"."t5pkcol" NOT NULL HASHCOL , "clookup4"."t7pkcol" NOT NULL HASHCOL ,
"clookup5"."t8pkcol" NOT NULL HASHCOL , "table9"."t9pkcol" NOT NULL HASHCOL ,
"table9"."t9cola1", "table9"."t9cola91", "table9"."t9cola111",
"table10"."t10pkcol" NOT NULL HASHCOL , "table10"."t10cola1",
"table10"."t10cola91", "table11"."t11pkcol" NOT NULL HASHCOL ,
"table11"."t11cola91" ],
| | | | | | | | | | | table(sys.table3) [ "table3"."t3pkcol" NOT NULL HASHCOL
as "a1"."t3pkcol", "table3"."t3cola1" as "a1"."t3cola1" ] COUNT
| | | | | | | | | | ) [ "a1"."t3pkcol" NOT NULL HASHCOL =
"table1"."t1cold111" ]
diff --git a/sql/test/miscellaneous/Tests/groupby_error.sql
b/sql/test/miscellaneous/Tests/groupby_error.sql
--- a/sql/test/miscellaneous/Tests/groupby_error.sql
+++ b/sql/test/miscellaneous/Tests/groupby_error.sql
@@ -16,6 +16,8 @@ INSERT INTO tab2 VALUES(64,77,40), (75,6
SELECT CAST(+ col1 * - col1 AS BIGINT) AS col2 FROM tab0 GROUP BY col2, col0,
col1 HAVING + - col0 / - AVG ( ALL + col2 ) - - - AVG ( DISTINCT + col0 ) +
col0 IS NULL;
SELECT DISTINCT + 40 / + + col0 AS col2 FROM tab0 GROUP BY col0, col0, col2
HAVING NOT ( NOT + - 80 BETWEEN NULL AND + - 73 ) OR NOT ( + col0 >= - COUNT (
* ) + - COUNT ( DISTINCT - col0 ) );
SELECT ALL * FROM tab0 AS cor0 WHERE col2 NOT IN ( 22, 18, CAST ( NULL AS
INTEGER ) + - 77 );
+SELECT CAST(58 + + 78 + - COALESCE ( ( + CASE 68 WHEN - 77 - - 38 THEN NULL
ELSE COUNT ( * ) END ), + 81 + - COUNT ( * ) + + CAST ( NULL AS INTEGER ), + +
34 * - 30 * + COUNT ( * ) ) * 15 * 38 AS BIGINT) AS col1;
+ --434
SELECT * FROM tab0 AS cor0 WHERE NOT - 39 <> 11; --empty
SELECT DISTINCT * FROM tab0 WHERE NOT - - 12 <> + + 96; --empty
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.out
b/sql/test/miscellaneous/Tests/groupby_error.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.out
@@ -92,6 +92,12 @@ stdout of test 'groupby_error` in direct
% col0, col1, col2 # name
% int, int, int # type
% 1, 1, 1 # length
+#SELECT CAST(58 + + 78 + - COALESCE ( ( + CASE 68 WHEN - 77 - - 38 THEN NULL
ELSE COUNT ( * ) END ), + 81 + - COUNT ( * ) + + CAST ( NULL AS INTEGER ), + +
34 * - 30 * + COUNT ( * ) ) * 15 * 38 AS BIGINT) AS col1;
+% . # table_name
+% col1 # name
+% bigint # type
+% 4 # length
+[ -434 ]
#SELECT * FROM tab0 AS cor0 WHERE NOT - 39 <> 11; --empty
% sys.cor0, sys.cor0, sys.cor0 # table_name
% col0, col1, col2 # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
