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