summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleg Smirnov <olernov@gmail.com>2023-01-12 13:18:33 +0700
committerOleg Smirnov <olernov@gmail.com>2023-01-20 13:47:36 +0700
commitafb5deb9db44b52bd86512a8829a28ed52adcd0d (patch)
tree0cf8aef02b32b3ad7910f5c49cd50c480ee69a18
parentc256998b6e76ccb8f3237b13b8dcb7d60d12736d (diff)
downloadmariadb-git-afb5deb9db44b52bd86512a8829a28ed52adcd0d.tar.gz
MDEV-29294 Assertion `functype() == ((Item_cond *) new_item)->functype()' failed in Item_cond::remove_eq_conds on SELECT
Item_singlerow_subselect may be converted to Item_cond during optimization. So there is a possibility of constructing nested Item_cond_and or Item_cond_or which is not allowed (such conditions must be flattened). This commit checks if such kind of optimization has been applied and flattens the condition if needed
-rw-r--r--mysql-test/main/select.result56
-rw-r--r--mysql-test/main/select.test40
-rw-r--r--mysql-test/main/select_jcl6.result56
-rw-r--r--mysql-test/main/select_pkeycache.result56
-rw-r--r--sql/item_cmpfunc.cc83
-rw-r--r--sql/item_cmpfunc.h3
6 files changed, 268 insertions, 26 deletions
diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result
index 1562144b164..fc3a29094ae 100644
--- a/mysql-test/main/select.result
+++ b/mysql-test/main/select.result
@@ -5639,4 +5639,60 @@ EXECUTE stmt;
COUNT(DISTINCT a)
3
DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+# failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1249 Select 2 was reduced during optimization
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
End of 10.0 tests
diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test
index 31487a6844e..ed8783d40f4 100644
--- a/mysql-test/main/select.test
+++ b/mysql-test/main/select.test
@@ -4742,4 +4742,44 @@ EXECUTE stmt;
--enable_warnings
DROP TABLE t1;
+--echo #
+--echo # MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+--echo # failed in Item_cond::remove_eq_conds on SELECT
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+
+--echo # Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+
+--echo # Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+
+DROP TABLE t1;
+DROP VIEW v1, v2;
+
--echo End of 10.0 tests
diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result
index e144477b66e..e7c2e3e4807 100644
--- a/mysql-test/main/select_jcl6.result
+++ b/mysql-test/main/select_jcl6.result
@@ -5650,6 +5650,62 @@ EXECUTE stmt;
COUNT(DISTINCT a)
3
DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+# failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1249 Select 2 was reduced during optimization
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
End of 10.0 tests
set join_cache_level=default;
set @@optimizer_switch=@save_optimizer_switch_jcl6;
diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result
index 1562144b164..fc3a29094ae 100644
--- a/mysql-test/main/select_pkeycache.result
+++ b/mysql-test/main/select_pkeycache.result
@@ -5639,4 +5639,60 @@ EXECUTE stmt;
COUNT(DISTINCT a)
3
DROP TABLE t1;
+#
+# MDEV-29294: Assertion `functype() == ((Item_cond *) new_item)->functype()'
+# failed in Item_cond::remove_eq_conds on SELECT
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+# Test for nested OR conditions:
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+a
+1
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary
+3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+Warnings:
+Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note 1249 Select 2 was reduced during optimization
+Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 1 and (1 or <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select 3 from DUAL where `test`.`t1`.`a` = `test`.`t1`.`a`)) = 3)
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 AND
+ (3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1 AND
+(3 = 0 OR (SELECT a = 1 OR (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v1;
+a
+1
+# Test for nested AND conditions:
+SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+a
+1
+PREPARE stmt FROM 'SELECT * FROM t1 WHERE a = 1 OR
+ (3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3))';
+EXECUTE stmt;
+a
+1
+EXECUTE stmt;
+a
+1
+CREATE VIEW v2 AS SELECT * FROM t1 WHERE a = 1 OR
+(3 = 3 AND (SELECT a = 1 AND (SELECT 3 WHERE a = a) = 3));
+SELECT * FROM v2;
+a
+1
+DROP TABLE t1;
+DROP VIEW v1, v2;
End of 10.0 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 7fb2ff32842..6fbbb79c263 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4816,38 +4816,18 @@ Item_cond::fix_fields(THD *thd, Item **ref)
if (check_stack_overrun(thd, STACK_MIN_SIZE, buff))
return TRUE; // Fatal error flag is set!
- /*
- The following optimization reduces the depth of an AND-OR tree.
- E.g. a WHERE clause like
- F1 AND (F2 AND (F2 AND F4))
- is parsed into a tree with the same nested structure as defined
- by braces. This optimization will transform such tree into
- AND (F1, F2, F3, F4).
- Trees of OR items are flattened as well:
- ((F1 OR F2) OR (F3 OR F4)) => OR (F1, F2, F3, F4)
- Items for removed AND/OR levels will dangle until the death of the
- entire statement.
- The optimization is currently prepared statements and stored procedures
- friendly as it doesn't allocate any memory and its effects are durable
- (i.e. do not depend on PS/SP arguments).
- */
- while ((item=li++))
+
+ while (li++)
{
- while (item->type() == Item::COND_ITEM &&
- ((Item_cond*) item)->functype() == functype() &&
- !((Item_cond*) item)->list.is_empty())
- { // Identical function
- li.replace(((Item_cond*) item)->list);
- ((Item_cond*) item)->list.empty();
- item= *li.ref(); // new current item
- }
+ merge_sub_condition(li);
+ item= *li.ref();
if (abort_on_null)
item->top_level_item();
/*
replace degraded condition:
was: <field>
- become: <field> = 1
+ become: <field> != 0
*/
Item::Type type= item->type();
if (type == Item::FIELD_ITEM || type == Item::REF_ITEM)
@@ -4863,7 +4843,9 @@ Item_cond::fix_fields(THD *thd, Item **ref)
if (item->fix_fields_if_needed_for_bool(thd, li.ref()))
return TRUE; /* purecov: inspected */
- item= *li.ref(); // item can be substituted in fix_fields
+ merge_sub_condition(li);
+ item= *li.ref(); // may be substituted in fix_fields/merge_item_if_possible
+
used_tables_cache|= item->used_tables();
if (item->const_item() && !item->with_param &&
!item->is_expensive() && !cond_has_datetime_is_null(item))
@@ -4915,6 +4897,55 @@ Item_cond::fix_fields(THD *thd, Item **ref)
return FALSE;
}
+/**
+ @brief
+ Merge a lower-level condition pointed by the iterator into this Item_cond
+ if possible
+
+ @param li list iterator pointing to condition that must be
+ examined and merged if possible.
+
+ @details
+ If an item pointed by the iterator is an instance of Item_cond with the
+ same functype() as this Item_cond (i.e. both are Item_cond_and or both are
+ Item_cond_or) then the arguments of that lower-level item can be merged
+ into the list of arguments of this upper-level Item_cond.
+
+ This optimization reduces the depth of an AND-OR tree.
+ E.g. a WHERE clause like
+ F1 AND (F2 AND (F2 AND F4))
+ is parsed into a tree with the same nested structure as defined
+ by braces. This optimization will transform such tree into
+ AND (F1, F2, F3, F4).
+ Trees of OR items are flattened as well:
+ ((F1 OR F2) OR (F3 OR F4)) => OR (F1, F2, F3, F4)
+ Items for removed AND/OR levels will dangle until the death of the
+ entire statement.
+
+ The optimization is currently prepared statements and stored procedures
+ friendly as it doesn't allocate any memory and its effects are durable
+ (i.e. do not depend on PS/SP arguments).
+*/
+void Item_cond::merge_sub_condition(List_iterator<Item>& li)
+{
+ Item *item= *li.ref();
+
+ /*
+ The check for list.is_empty() is to catch empty Item_cond_and() items.
+ We may encounter Item_cond_and with an empty list, because optimizer code
+ strips multiple equalities, combines items, then adds multiple equalities
+ back
+ */
+ while (item->type() == Item::COND_ITEM &&
+ ((Item_cond*) item)->functype() == functype() &&
+ !((Item_cond*) item)->list.is_empty())
+ {
+ li.replace(((Item_cond*) item)->list);
+ ((Item_cond*) item)->list.empty();
+ item= *li.ref();
+ }
+}
+
bool
Item_cond::eval_not_null_tables(void *opt_arg)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 01834fe06d7..0641213c7c9 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -3046,6 +3046,9 @@ public:
Item *build_clone(THD *thd);
bool excl_dep_on_table(table_map tab_map);
bool excl_dep_on_grouping_fields(st_select_lex *sel);
+
+private:
+ void merge_sub_condition(List_iterator<Item>& li);
};
template <template<class> class LI, class T> class Item_equal_iterator;