Hi Varun,
> Commit 3e29c5a1620deef2fa9b25a0d42b4c07fe59e96f
>
> MDEV-21265: IN predicate conversion to IN subquery should be allowed for a
> broader set of datatype comparison
>
> Allow materialization strategy when the collation of the columns of the
> left side of the IN subquery are not same as the one on the right side
> of the IN subquery but are compatible with each other.
> This allows conversion from utf8mb3 to utf8mb4 as the former is a subset of
> the later.
Please fix the comment to say what the patch does:
When collations on the inner and outer sides are the same, and the
character set of the inner side is a proper subset of the charset on the
outer.
>
> diff --git a/mysql-test/main/subselect4.result
> b/mysql-test/main/subselect4.result
> index 020a70642913..f3ae2398d45e 100644
> --- a/mysql-test/main/subselect4.result
> +++ b/mysql-test/main/subselect4.result
> @@ -2718,3 +2718,172 @@ Warning 1931 Query execution was
> interrupted. The query examined at least 3020 r
> SET join_cache_level= @save_join_cache_level;
> DROP TABLE t1,t2,t3,t4;
> # End of 10.2 tests
> +#
> +# MDEV-21265: IN predicate conversion to IN subquery should be allowed for a
> broader set of datatype comparison
> +#
> +CREATE TABLE t1 ( col1 varchar (32) collate utf8_general_ci);
> +CREATE TABLE t2 ( col1 varchar (32) collate utf8mb4_general_ci, key(col1));
> +INSERT INTO t1 select seq from seq_1_to_100;
> +INSERT INTO t2 values (_utf8mb4 X'F09F9883'), (1),('?');
...
> EXPLAIN FORMAT=JSON SELECT HEX(col1), col1 IN (SELECT col1 FROM t1) FROM t2;
Please insert the '?' into the table t1, not t2.
The idea of the check is to check for the following:
- We get smiley symbol from table t2.
- We make the lookup key from it. This results in an error and the '?' as
output.
- Then, a lookup is made in t1. And we are trying to provoke a wrong result by
having the '?' in t1.
OK to push into the custom-build tree when the above is addressed.
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
