Hi Varun, Please find some cosmetic input below. Ok to push after addresed.

diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index 6f5eb1f2985f..e9da028618f6 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2238,3 +2238,38 @@ SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4;

--echo # End of 10.2 tests + +--echo # +--echo # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison +--echo # + +CREATE TABLE t1(a VARCHAR(50) collate utf8_general_ci, b INT); +INSERT INTO t1 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5); + +CREATE TABLE t2(a VARCHAR(50) collate utf8mb4_general_ci, b INT); +INSERT INTO t2 VALUES ('abc',1), ('def', 2), ('ghi', 3), ('jkl', 4), ('mno', 5); + +set in_predicate_conversion_threshold=2; + ...

+EXPLAIN +SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abx',1),('def',2), ('abc', 3)); + +set names default; +DROP TABLE t1,t2; + +--echo # End of 10.3 tests

Please restore the value of in_predicate_conversion_threshold.

diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 1cea7291c9ef..d1b2f86088d9 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -573,6 +573,13 @@ class Item_in_subselect :public Item_exists_subselect TRUE<=>registered in the list of semijoins in outer select */ bool is_registered_semijoin; + + Item_func_not_all *upper_item; // point on NOT/NOP before ALL/SOME subquery

Is this necessary to touch the above member? I think it's better leave it as is.

+ + /* + SET to TRUE if IN subququery is converted from an IN rredicate + */ + bool converted_from_in_predicate; ...

diff --git a/sql/sql_type.h b/sql/sql_type.h index 907225b7c505..a1403165fe38 100644 --- a/sql/sql_type.h +++ b/sql/sql_type.h @@ -1291,9 +1291,21 @@ class Type_handler Item *target_expr, Item *target_value, Item_bool_func2 *source, Item *source_expr, Item *source_const) const= 0; + + /* + @brief + Check if an IN subquery allows materialization or not + @param + inner expression on the inner side of the IN subquery + outer expression on the inner side of the IN subquery

Typo. Should it should be 'outer' ?

+ is_in_predicate SET to true if IN subquery was converted from an + IN predicate or we are checking if materialization + strategy can be used for an IN predicate + */ virtual bool subquery_type_allows_materialization(const Item *inner, - const Item *outer) const= 0; + const Item *outer, + bool is_in_predicate) const= 0; /** Make a simple constant replacement item for a constant "src", so the new item can futher be used for comparison with "cmp", e.g.:

BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog