revision-id: 9383cba08b8efeb7a70afe68502bfca96fbb251f (mariadb-10.3.6-171-g9383cba) parent(s): 793f27a046e2c2753dcad8793c75e27f445938fc author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-24 01:36:05 -0800 message: MDEV-18681 Server crashes in embedding_sjm Do not do substitution for best equal field in HAVING conditions. It's not needed. --- mysql-test/main/having.result | 18 ++++++++++++++++++ mysql-test/main/having.test | 20 ++++++++++++++++++++ sql/sql_select.cc | 19 +++++++++++-------- 3 files changed, 49 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/having.result b/mysql-test/main/having.result index b1cd776..89c91b0 100644 --- a/mysql-test/main/having.result +++ b/mysql-test/main/having.result @@ -847,3 +847,21 @@ t r DROP TABLE t1; DROP FUNCTION next_seq_value; DROP TABLE series; +# End of 10.3 tests +# +# MDEV-18681: AND formula in having with several occurances +# of the same field f in different conjunct AND f=constant +# +CREATE TABLE t1 (pk int, f varchar(1)); +INSERT INTO t1 VALUES (2,'x'), (7,'y'); +CREATE TABLE t2 (pk int); +INSERT INTO t2 VALUES (2), (3); +SELECT t.f +FROM (SELECT t1.* FROM (t1 JOIN t2 ON (t2.pk = t1.pk))) t +HAVING t.f != 112 AND t.f = 'x' AND t.f != 'a'; +f +x +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'x' +DROP TABLE t1,t2; +# End of 10.4 tests diff --git a/mysql-test/main/having.test b/mysql-test/main/having.test index 179af14..57900ec 100644 --- a/mysql-test/main/having.test +++ b/mysql-test/main/having.test @@ -890,3 +890,23 @@ SELECT t, next_seq_value() r FROM t1 FORCE INDEX(t) DROP TABLE t1; DROP FUNCTION next_seq_value; DROP TABLE series; + +--echo # End of 10.3 tests + +--echo # +--echo # MDEV-18681: AND formula in having with several occurances +--echo # of the same field f in different conjunct AND f=constant +--echo # + +CREATE TABLE t1 (pk int, f varchar(1)); +INSERT INTO t1 VALUES (2,'x'), (7,'y'); +CREATE TABLE t2 (pk int); +INSERT INTO t2 VALUES (2), (3); + +SELECT t.f +FROM (SELECT t1.* FROM (t1 JOIN t2 ON (t2.pk = t1.pk))) t +HAVING t.f != 112 AND t.f = 'x' AND t.f != 'a'; + +DROP TABLE t1,t2; + +--echo # End of 10.4 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e6a858b..5faadb1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -159,7 +159,8 @@ static COND *build_equal_items(JOIN *join, COND *cond, static COND* substitute_for_best_equal_field(THD *thd, JOIN_TAB *context_tab, COND *cond, COND_EQUAL *cond_equal, - void *table_join_idx); + void *table_join_idx, + bool do_substitution); static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top, bool in_sj); static bool check_interleaving_with_nj(JOIN_TAB *next); @@ -2304,7 +2305,7 @@ int JOIN::optimize_stage2() if (conds) { conds= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, conds, - cond_equal, map2table); + cond_equal, map2table, true); if (unlikely(thd->is_error())) { error= 1; @@ -2320,7 +2321,7 @@ int JOIN::optimize_stage2() if (having) { having= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, having, - having_equal, map2table); + having_equal, map2table, false); if (thd->is_error()) { error= 1; @@ -2347,7 +2348,7 @@ int JOIN::optimize_stage2() *tab->on_expr_ref= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB, *tab->on_expr_ref, tab->cond_equal, - map2table); + map2table, true); if (unlikely(thd->is_error())) { error= 1; @@ -2377,7 +2378,7 @@ int JOIN::optimize_stage2() while (equals) { ref_item= substitute_for_best_equal_field(thd, tab, ref_item, - equals, map2table); + equals, map2table, true); if (unlikely(thd->is_fatal_error)) DBUG_RETURN(1); @@ -15418,7 +15419,8 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels, static COND* substitute_for_best_equal_field(THD *thd, JOIN_TAB *context_tab, COND *cond, COND_EQUAL *cond_equal, - void *table_join_idx) + void *table_join_idx, + bool do_substitution) { Item_equal *item_equal; COND *org_cond= cond; // Return this in case of fatal error @@ -15447,7 +15449,8 @@ static COND* substitute_for_best_equal_field(THD *thd, JOIN_TAB *context_tab, { Item *new_item= substitute_for_best_equal_field(thd, context_tab, item, cond_equal, - table_join_idx); + table_join_idx, + do_substitution); /* This works OK with PS/SP re-execution as changes are made to the arguments of AND/OR items only @@ -15529,7 +15532,7 @@ static COND* substitute_for_best_equal_field(THD *thd, JOIN_TAB *context_tab, cond= eliminate_item_equal(thd, 0, cond_equal, item_equal); return cond ? cond : org_cond; } - else + else if (do_substitution) { while (cond_equal) {