summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOleg Smirnov <olernov@gmail.com>2022-10-04 16:45:51 +0700
committerOleg Smirnov <olernov@gmail.com>2022-10-26 11:01:56 +0700
commit5027cb2b74a0b37cbdd3ad190cb8b2bf738c0cde (patch)
tree436c391211add417c57939bf65d878768360a6dc
parentb7fe6179e82d89da55b48f6fb3061f5f4066535b (diff)
downloadmariadb-git-5027cb2b74a0b37cbdd3ad190cb8b2bf738c0cde.tar.gz
MDEV-29662 Replace same values in 'IN' list with an equality
If all elements in the list of 'IN' or 'NOT IN' clause are equal and there are no NULLs 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 and Item_func_ne for NOT IN (e1,...,en). Such a replacement allows the optimizer to choose a better execution plan
-rw-r--r--mysql-test/main/func_in.result213
-rw-r--r--mysql-test/main/func_in.test117
-rw-r--r--mysql-test/suite/innodb/r/innodb_mysql.result6
-rw-r--r--sql/item.h2
-rw-r--r--sql/item_cmpfunc.cc51
-rw-r--r--sql/item_cmpfunc.h1
-rw-r--r--sql/sql_select.cc92
-rw-r--r--sql/sql_select.h6
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};