diff options
| -rw-r--r-- | mysql-test/main/select.result | 56 | ||||
| -rw-r--r-- | mysql-test/main/select.test | 40 | ||||
| -rw-r--r-- | mysql-test/main/select_jcl6.result | 56 | ||||
| -rw-r--r-- | mysql-test/main/select_pkeycache.result | 56 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 83 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 3 |
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; |