diff options
| author | Varun Gupta <varun.gupta@mariadb.com> | 2020-11-27 22:06:54 +0530 |
|---|---|---|
| committer | Varun Gupta <varun.gupta@mariadb.com> | 2020-11-30 17:16:43 +0530 |
| commit | b4379df5b485143209c35b9f6f07b00049c8d455 (patch) | |
| tree | 8f34d436dfaaa584cc416cf703c6bbacd50af385 /mysql-test/main/subselect4.result | |
| parent | f3b10354a97a0815d36abbfd25a5f10489a3eaab (diff) | |
| download | mariadb-git-b4379df5b485143209c35b9f6f07b00049c8d455.tar.gz | |
MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison
Allow materialization strategy when collations on the
inner and outer sides of an IN subquery are the same and the
character set of the inner side is a proper subset of the character
set on the outer side.
This allows conversion from utf8mb3 to utf8mb4
as the former is a subset of the later.
This is only allowed when IN predicate is converted to an IN subquery
Backported part of the patch (d6a00d9b18f) of MDEV-17905.
Diffstat (limited to 'mysql-test/main/subselect4.result')
| -rw-r--r-- | mysql-test/main/subselect4.result | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index d069f71601f..23b6e268d91 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2718,3 +2718,37 @@ 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(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 @save_in_predicate_conversion_threshold= @@in_predicate_conversion_threshold; +set in_predicate_conversion_threshold=2; +set names 'utf8mb4'; +# +# IN predicate to IN subquery is not allowed as materialization is not allowed +# The character set on the inner side is not equal to or a proper subset of the outer side +# +EXPLAIN +SELECT * FROM t1 WHERE (t1.a,t1.b) IN (('abx',1),('def',2), ('abc', 3)); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where +set names 'utf8'; +# +# IN predicate to IN subquery is performed as materialization is llowed +# The character set on the inner side is a proper subset of the outer side +# +EXPLAIN +SELECT * FROM t2 WHERE (t2.a,t2.b) IN (('abx',1),('def',2), ('abc', 3)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 16 func,func 1 Using where +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +set names default; +set @@in_predicate_conversion_threshold= @save_in_predicate_conversion_threshold; +DROP TABLE t1,t2; +# End of 10.3 tests |