maria-developers team mailing list archive
Mailing list archive
Re: MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
> 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
> 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.
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog