maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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