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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
