diff options
| -rw-r--r-- | mysql-test/main/func_in.result | 213 | ||||
| -rw-r--r-- | mysql-test/main/func_in.test | 117 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb_mysql.result | 6 | ||||
| -rw-r--r-- | sql/item.h | 2 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 51 | ||||
| -rw-r--r-- | sql/item_cmpfunc.h | 1 | ||||
| -rw-r--r-- | sql/sql_select.cc | 92 | ||||
| -rw-r--r-- | sql/sql_select.h | 6 |
8 files changed, 484 insertions, 4 deletions
diff --git a/mysql-test/main/func_in.result b/mysql-test/main/func_in.result index dd7125c393a..672e45e877b 100644 --- a/mysql-test/main/func_in.result +++ b/mysql-test/main/func_in.result @@ -553,7 +553,7 @@ Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DECIMAL value: 'b' explain select f2 from t2 where f2 in ('a','b'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range t2f2 t2f2 5 NULL 1 Using where; Using index +1 SIMPLE t2 ref t2f2 t2f2 5 const 1 Using index Warnings: Warning 1292 Truncated incorrect DECIMAL value: 'a' Warning 1292 Truncated incorrect DECIMAL value: 'b' @@ -942,5 +942,216 @@ SELECT ('0x',1) IN ((0,1),(1,1)); Warnings: Warning 1292 Truncated incorrect DECIMAL value: '0x' # +# MDEV-29662 same values in `IN` set vs equal comparison produces +# the different performance +# +CREATE TABLE t1 (a INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1 WHERE a IN (1,1); +a +1 +# 'const' access since 'a IN (1,1)' is converted to equality 'a=1' +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +EXPLAIN SELECT * FROM t1 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +SELECT * FROM t1 WHERE a IN (1,1,2); +a +1 +2 +# Conversion to equality is impossible due to different values +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index +SELECT * FROM t1 WHERE a IN (1,NULL,1); +a +1 +# Conversion to equality is impossible due to NULL in the IN list +EXPLAIN SELECT * FROM t1 WHERE a IN (1,NULL,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +a +1 +3 +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +EXPLAIN SELECT * FROM t1 WHERE a != 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +a +2 +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 3 Using where; Using index +SELECT * FROM t1 WHERE a NOT IN (1,2,NULL,NULL); +a +# No conversion is possible since elements are not constant +SELECT * FROM t1 WHERE a IN ((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); +a +3 +EXPLAIN SELECT * FROM t1 WHERE a IN +((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using index +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +# There must be no conversion here: +SELECT * FROM t1 WHERE a IN (3,2,3,3,1,2,3); +a +1 +2 +3 +# Prepared statement +PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN (3,3,3)"; +EXECUTE stmt; +a +3 +EXECUTE stmt; +a +3 +DEALLOCATE PREPARE stmt; +# Conversion to equality since SELECT 2 is evaluated as const +SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +a +2 +EXPLAIN SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +CREATE TABLE t2 (a INT, b VARCHAR(10), PRIMARY KEY(a,b)); +INSERT INTO t2 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +a b +1 abc +# 'const' access due to conversion to equality +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +a b +2 def +# No conversion due to different values +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 16 NULL 2 Using where; Using index +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); +a b +2 def +# No conversion due to NULL +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range PRIMARY PRIMARY 16 NULL 1 Using where; Using index +SELECT * FROM t2 WHERE (a,b) NOT IN ((2,'def'),(2,'def'),(2,NULL)); +a b +1 abc +3 ghi +SELECT * FROM t2 WHERE a IN (1,1,1,1); +a b +1 abc +EXPLAIN SELECT * FROM t2 WHERE a IN (1,1,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index +EXPLAIN SELECT * FROM t2 WHERE a = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using index +SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +a b +2 def +3 ghi +EXPLAIN SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL PRIMARY 16 NULL 3 Using where; Using index +EXPLAIN SELECT * FROM t2 WHERE b != 'abc'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL PRIMARY 16 NULL 3 Using where; Using index +# Prepared statements +PREPARE stmt FROM "EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'))"; +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 const PRIMARY PRIMARY 16 const,const 1 Using index +DEALLOCATE PREPARE stmt; +CREATE TABLE t3(a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3); +PREPARE stmt FROM "EXPLAIN SELECT * FROM t3 WHERE a IN (?,?,?)"; +EXECUTE stmt USING 1,1,1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 Using index +EXECUTE stmt USING 2,3,4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 3 Using where; Using index +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2, t3; +# Nested joins +CREATE TABLE t1 (a INT, b VARCHAR(10), PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +CREATE TABLE t2 (a INT, b VARCHAR(20), PRIMARY KEY(a)); +INSERT INTO t2 (a) VALUES (2),(3); +CREATE TABLE t3 (a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a INT); +INSERT INTO t4 VALUES (2),(3); +# Conversion to equalities +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,2,2) +AND t3.a IN (1,1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 +# No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,3) +AND t3.a IN (1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (incremental, BNL join) +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using join buffer (incremental, BNL join) +# Conversion to equalities +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) +ON t1.a = t2.a WHERE t1.a IN (2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 4 Using index +# No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) +ON t1.a = t2.a WHERE t1.a IN (1,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +1 SIMPLE t4 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 4 Using index +# View +CREATE VIEW v1 AS SELECT t1.*, t2.b AS t2_b FROM t1 LEFT JOIN t2 +ON t1.a = t2.a; +EXPLAIN SELECT * FROM v1 WHERE a IN (2,2,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 +EXPLAIN SELECT * FROM v1 WHERE a IN (1,2,3); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 3 Using index condition +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# Stored procedures +CREATE PROCEDURE p1(pa INT, pb INT) +EXPLAIN SELECT * FROM t1 WHERE a IN (pa, pb); +CALL p1(1,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +CALL p1(2,1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using index condition +DROP TABLE t1, t2, t3, t4; +DROP VIEW v1; +DROP PROCEDURE p1; +# # End of 10.4 tests # diff --git a/mysql-test/main/func_in.test b/mysql-test/main/func_in.test index 2581a07ac09..99161e76fb8 100644 --- a/mysql-test/main/func_in.test +++ b/mysql-test/main/func_in.test @@ -721,6 +721,123 @@ SELECT '0x' IN (0,1); SELECT ('0x',1) IN ((0,1)); SELECT ('0x',1) IN ((0,1),(1,1)); +--echo # +--echo # MDEV-29662 same values in `IN` set vs equal comparison produces +--echo # the different performance +--echo # +CREATE TABLE t1 (a INT, PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1 WHERE a IN (1,1); +--echo # 'const' access since 'a IN (1,1)' is converted to equality 'a=1' +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1); +EXPLAIN SELECT * FROM t1 WHERE a = 1; +SELECT * FROM t1 WHERE a IN (1,1,2); +--echo # Conversion to equality is impossible due to different values +EXPLAIN SELECT * FROM t1 WHERE a IN (1,1,2); +SELECT * FROM t1 WHERE a IN (1,NULL,1); +--echo # Conversion to equality is impossible due to NULL in the IN list +EXPLAIN SELECT * FROM t1 WHERE a IN (1,NULL,1); + +SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (2,2,2,2,2,2); +EXPLAIN SELECT * FROM t1 WHERE a != 3; +SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +EXPLAIN SELECT * FROM t1 WHERE a NOT IN (3,3,1,1); +SELECT * FROM t1 WHERE a NOT IN (1,2,NULL,NULL); + +--echo # No conversion is possible since elements are not constant +SELECT * FROM t1 WHERE a IN ((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); +EXPLAIN SELECT * FROM t1 WHERE a IN + ((SELECT MAX(a) FROM t1), (SELECT MAX(a) FROM t1)); + +--echo # There must be no conversion here: +SELECT * FROM t1 WHERE a IN (3,2,3,3,1,2,3); + +--echo # Prepared statement +PREPARE stmt FROM "SELECT * FROM t1 WHERE a IN (3,3,3)"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # Conversion to equality since SELECT 2 is evaluated as const +--disable_warnings +SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +EXPLAIN SELECT * FROM t1 WHERE a IN ((SELECT 2), (SELECT 2)); +--enable_warnings + +CREATE TABLE t2 (a INT, b VARCHAR(10), PRIMARY KEY(a,b)); +INSERT INTO t2 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +--echo # 'const' access due to conversion to equality +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc')); +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +--echo # No conversion due to different values +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,'XYZ')); +SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); +--echo # No conversion due to NULL +EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((2,'def'),(2,'def'),(2,NULL)); + +SELECT * FROM t2 WHERE (a,b) NOT IN ((2,'def'),(2,'def'),(2,NULL)); + +SELECT * FROM t2 WHERE a IN (1,1,1,1); +EXPLAIN SELECT * FROM t2 WHERE a IN (1,1,1,1); +EXPLAIN SELECT * FROM t2 WHERE a = 1; + +SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +EXPLAIN SELECT * FROM t2 WHERE b NOT IN ('abc','abc'); +EXPLAIN SELECT * FROM t2 WHERE b != 'abc'; + +--echo # Prepared statements +PREPARE stmt FROM "EXPLAIN SELECT * FROM t2 WHERE (a,b) IN ((1,'abc'),(1,'abc'))"; +EXECUTE stmt; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +CREATE TABLE t3(a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3); +PREPARE stmt FROM "EXPLAIN SELECT * FROM t3 WHERE a IN (?,?,?)"; +EXECUTE stmt USING 1,1,1; +EXECUTE stmt USING 2,3,4; +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2, t3; + +--echo # Nested joins +CREATE TABLE t1 (a INT, b VARCHAR(10), PRIMARY KEY(a)); +INSERT INTO t1 VALUES (1,'abc'),(2,'def'),(3,'ghi'); +CREATE TABLE t2 (a INT, b VARCHAR(20), PRIMARY KEY(a)); +INSERT INTO t2 (a) VALUES (2),(3); +CREATE TABLE t3 (a INT, PRIMARY KEY(a)); +INSERT INTO t3 VALUES (1),(2),(3),(4); +CREATE TABLE t4 (a INT); +INSERT INTO t4 VALUES (2),(3); +--echo # Conversion to equalities +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,2,2) + AND t3.a IN (1,1,1); +--echo # No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM (t1,t2) LEFT JOIN (t3,t4) ON t1.a IN (2,3) + AND t3.a IN (1,2); +--echo # Conversion to equalities +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) + ON t1.a = t2.a WHERE t1.a IN (2,2,2); +--echo # No conversion to equalities due to different values in IN() +EXPLAIN SELECT * FROM t1 LEFT JOIN ((t2, t3) LEFT JOIN t4 ON t2.a = t4.a) + ON t1.a = t2.a WHERE t1.a IN (1,3); + +--echo # View +CREATE VIEW v1 AS SELECT t1.*, t2.b AS t2_b FROM t1 LEFT JOIN t2 + ON t1.a = t2.a; +EXPLAIN SELECT * FROM v1 WHERE a IN (2,2,2); +EXPLAIN SELECT * FROM v1 WHERE a IN (1,2,3); + +--echo # Stored procedures +CREATE PROCEDURE p1(pa INT, pb INT) + EXPLAIN SELECT * FROM t1 WHERE a IN (pa, pb); +CALL p1(1,1); +CALL p1(2,1); + +DROP TABLE t1, t2, t3, t4; +DROP VIEW v1; +DROP PROCEDURE p1; --echo # --echo # End of 10.4 tests diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index d205a594249..aa8cc118ce6 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2190,7 +2190,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where; Using filesort +2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where DROP TABLE t1; CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) ENGINE=InnoDB; @@ -2204,7 +2204,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where; Using filesort +2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where DROP TABLE t1; CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), KEY (c3), KEY (c2, c3)) @@ -2219,7 +2219,7 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1) FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 -2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where; Using filesort +2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where DROP TABLE t1; End of 5.1 tests # diff --git a/sql/item.h b/sql/item.h index b8cf8a2ef72..04e469e8fde 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2481,6 +2481,8 @@ public: { return this; } virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg) { return this; } + virtual Item *in_predicate_to_equality_transformer(THD *thd, uchar *arg) + { return this; } virtual Item *field_transformer_for_having_pushdown(THD *thd, uchar *arg) { return this; } virtual Item *multiple_equality_transformer(THD *thd, uchar *arg) diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index fa016d439be..5ee29bd5dfb 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -35,6 +35,7 @@ #define PCRE2_STATIC 1 /* Important on Windows */ #include "pcre2.h" /* pcre2 header file */ +#include "my_json_writer.h" /* Compare row signature of two expressions @@ -4355,6 +4356,56 @@ Item_func_in::fix_fields(THD *thd, Item **ref) } +Item *Item_func_in::in_predicate_to_equality_transformer(THD *thd, uchar *arg) +{ + if (!array || have_null || !all_items_are_consts(args + 1, arg_count - 1)) + return this; /* Transformation is not applicable */ + + /* + If all elements in the array of constant values are equal and there are + no NULLs in the list then clause + - "a IN (e1,..,en)" can be converted to "a = e1" + - "a NOT IN (e1,..,en)" can be converted to "a != e1". + This means an object of Item_func_in can be replaced with an object of + Item_func_eq for IN (e1,..,en) clause or Item_func_ne for + NOT IN (e1,...,en). + */ + + /* + Since the array is sorted it's enough to compare the first and the last + elements to tell whether all elements are equal + */ + if (array->compare_elems(0, array->used_count - 1)) + { + /* Not all elements are equal, transformation is not possible */ + return this; + } + + Json_writer_object trace_wrapper(thd); + trace_wrapper.add("transformation", "in_predicate_to_equality") + .add("before", this); + + Item *new_item= nullptr; + if (negated) + new_item= new (thd->mem_root) Item_func_ne(thd, args[0], args[1]); + else + new_item= new (thd->mem_root) Item_func_eq(thd, args[0], args[1]); + if (new_item) + { + new_item->set_name(thd, name); + if (new_item->fix_fields(thd, &new_item)) + { + /* + If there are any problems during fixing fields, there is no need to + return an error, just discard the transformation + */ + new_item= this; + } + } + trace_wrapper.add("after", new_item); + return new_item; +} + bool Item_func_in::eval_not_null_tables(void *opt_arg) { diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 5bc0c839c90..098bad90166 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2630,6 +2630,7 @@ public: bool to_be_transformed_into_in_subq(THD *thd); bool create_value_list_for_tvc(THD *thd, List< List<Item> > *values); Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg) override; + Item *in_predicate_to_equality_transformer(THD *thd, uchar *arg) override; uint32 max_length_of_left_expr(); }; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 9ca8a510a98..e416be036d1 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2218,6 +2218,9 @@ JOIN::optimize_inner() ignore_on_expr= true; break; } + + transform_in_predicates_into_equalities(thd); + conds= optimize_cond(this, conds, join_list, ignore_on_expr, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); @@ -30468,6 +30471,95 @@ static bool process_direct_rownum_comparison(THD *thd, SELECT_LEX_UNIT *unit, DBUG_RETURN(false); } +/** + @brief + Transform IN predicates having equal constant elements to equalities + + @param thd The context of the statement + + @details + If all elements in an IN predicate are constant and equal to each other + then clause + - "a IN (e1,..,en)" can be transformed to "a = e1" + - "a NOT IN (e1,..,en)" can be transformed to "a != e1". + This means an object of Item_func_in can be replaced with an object of + Item_func_eq for IN (e1,..,en) clause or Item_func_ne for + NOT IN (e1,...,en). + Such a replacement allows the optimizer to choose a better execution plan. + + This methods applies such transformation for each IN predicate of the WHERE + condition and ON expressions of this join where possible + + @retval + false success + true failure +*/ +bool JOIN::transform_in_predicates_into_equalities(THD *thd) +{ + DBUG_ENTER("JOIN::transform_in_predicates_into_equalities"); + DBUG_RETURN(transform_all_conds_and_on_exprs( + thd, &Item::in_predicate_to_equality_transformer)); +} + + +/** + @brief + Transform all items in WHERE and ON expressions using a given transformer + + @param thd The context of the statement + transformer Pointer to the transformation function + + @details + For each item of the WHERE condition and ON expressions of the SELECT + for this join the method performs the intransformation using the given + transformation function + + @retval + false success + true failure +*/ +bool JOIN::transform_all_conds_and_on_exprs(THD *thd, + Item_transformer transformer) +{ + if (conds) + { + conds= conds->transform(thd, transformer, (uchar *) 0); + if (!conds) + return true; + } + if (join_list) + { + if (transform_all_conds_and_on_exprs_in_join_list(thd, join_list, + transformer)) + return true; + } + return false; +} + + +bool JOIN::transform_all_conds_and_on_exprs_in_join_list( + THD *thd, List<TABLE_LIST> *join_list, Item_transformer transformer) +{ + TABLE_LIST *table; + List_iterator<TABLE_LIST> li(*join_list); + + while ((table= li++)) + { + if (table->nested_join) + { + if (transform_all_conds_and_on_exprs_in_join_list( + thd, &table->nested_join->join_list, transformer)) + return true; + } + if (table->on_expr) + { + table->on_expr= table->on_expr->transform(thd, transformer, (uchar *) 0); + if (!table->on_expr) + return true; + } + } + return false; +} /** diff --git a/sql/sql_select.h b/sql/sql_select.h index 3a28c431df5..5aa775f4a2d 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1792,6 +1792,12 @@ private: bool make_aggr_tables_info(); bool add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *fields); void init_join_cache_and_keyread(); + bool transform_in_predicates_into_equalities(THD *thd); + bool transform_all_conds_and_on_exprs(THD *thd, + Item_transformer transformer); + bool transform_all_conds_and_on_exprs_in_join_list(THD *thd, + List<TABLE_LIST> *join_list, + Item_transformer transformer); }; enum enum_with_bush_roots { WITH_BUSH_ROOTS, WITHOUT_BUSH_ROOTS}; |