revision-id: cf0e8870f157a2e41d0de700e8b1834bbbdeff79 (mariadb-10.2.31-1264-gcf0e887) parent(s): 114e18b8b68a00b3829ac231cc8f84187f529287 author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-24 23:19:22 -0800 message: MDEV-26553 NOT IN subquery construct crashing 10.1 and up This bug was introduced by commit be00e279c6061134a33a8099fd69d4304735d02e The commit was applied for the task MDEV-6480 that allowed to remove top level disjuncts from WHERE conditions if the range optimizer evaluated them as always equal to FALSE/NULL. If such disjuncts are removed the WHERE condition may become an AND formula and if this formula contains multiple equalities the field JOIN::item_equal must be updated to refer to these equalities. The above mentioned commit forgot to do this and it could cause crashes for some queries. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/r/range.result | 24 ++++++++++++++++++++++++ mysql-test/r/range_mrr_icp.result | 24 ++++++++++++++++++++++++ mysql-test/t/range.test | 25 ++++++++++++++++++++++++- sql/sql_select.cc | 6 ++++++ 4 files changed, 78 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 6a3850c..f1f949a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -3201,5 +3201,29 @@ pk i v a b 2 2 4 2 4 drop table t1, t2; # +# MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed +# +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); +1 +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or b = NULL); +1 +select c from t2 +where 2 not in (select 1 from t1 +where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); +c +5 +2 +3 +4 +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 24f42f3..645576f 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3213,6 +3213,30 @@ pk i v a b 2 2 4 2 4 drop table t1, t2; # +# MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed +# +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); +1 +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or b = NULL); +1 +select c from t2 +where 2 not in (select 1 from t1 +where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); +c +5 +2 +3 +4 +drop table t1,t2; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 890377e..7369b9d 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -2232,6 +2232,29 @@ select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); drop table t1, t2; --echo # ---echo # End of 10.2 tests +--echo # MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed --echo # +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); + +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); + +select 1 from t1 s1 + where 1 not in (select 1 from t1 + where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); + +select 1 from t1 s1 + where 1 not in (select 1 from t1 + where ((a = 1 or a = 2) and b = 1) or b = NULL); + +select c from t2 + where 2 not in (select 1 from t1 + where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); + +drop table t1,t2; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 54a2fac..4742211 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4504,7 +4504,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (*s->on_expr_ref) *s->on_expr_ref= select->cond; else + { join->conds= select->cond; + if (join->conds && join->conds->type() == Item::COND_ITEM && + ((Item_cond*) (join->conds))->functype() == + Item_func::COND_AND_FUNC) + join->cond_equal= &((Item_cond_and*) (join->conds))->m_cond_equal; + } s->quick=select->quick; s->needed_reg=select->needed_reg;