[Commits] cf0e887: MDEV-26553 NOT IN subquery construct crashing 10.1 and up
IgorBabaev
igor at mariadb.com
Thu Nov 25 09:19:22 EET 2021
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 at 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;
More information about the commits
mailing list