diff options
| author | Varun Gupta <varun.gupta@mariadb.com> | 2020-05-26 17:57:07 +0530 |
|---|---|---|
| committer | Varun Gupta <varun.gupta@mariadb.com> | 2020-05-26 18:01:32 +0530 |
| commit | db1f73b26edaace41345f19bca507a7848aa39a8 (patch) | |
| tree | 946517a679d67d8aacb4843316adb9e4681f3357 | |
| parent | 76f4ae8295f63e4f3f7b8b72c2120d5da59b302c (diff) | |
| download | mariadb-git-db1f73b26edaace41345f19bca507a7848aa39a8.tar.gz | |
MDEV-17606: Query returns wrong results (while using CHARACTER SET utf8)
The issue here was that the left expr and right expr of the ANY subquery
had different character sets, so we were converting the left expr to utf8 character set.
So when this conversion was happening we were actually converting the item inside the cache,
it looked like <cache>(convert(t1.l1 using utf8)), which is incorrect.
To fix this problem we are going to store the reference of the left expr and convert that
to utf8 character set, it would look like convert(<cache>(`test`.`t1`.`l1`) using utf8)
| -rw-r--r-- | mysql-test/r/subselect4.result | 18 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 13 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 2 |
3 files changed, 32 insertions, 1 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 863105b24b6..22d4938fb78 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2566,3 +2566,21 @@ SELECT sum(a), t2.a, t2.b FROM t2 HAVING t2.a IN (SELECT t2.b FROM t1); sum(a) a b 6 1 1 DROP TABLE t1,t2; +# +# MDEV-17606: Query returns wrong results (while using CHARACTER SET utf8) +# +CREATE TABLE t1(l1 varchar(10), i2 int); +INSERT INTO t1 VALUES ('e',2),('o',6),('x',4); +CREATE TABLE t2 (v1 varchar(10) CHARACTER SET utf8, KEY v1 (v1(3))); +INSERT INTO t2 VALUES ('k'),('rid'),('f'),('x'); +EXPLAIN EXTENDED SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select `test`.`t1`.`l1` AS `l1`,`test`.`t1`.`i2` AS `i2` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`l1`,(<max>(select max(`test`.`t2`.`v1`) from `test`.`t2`) > convert(<cache>(`test`.`t1`.`l1`) using utf8)))) +SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +l1 i2 +e 2 +o 6 +DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index f0b1d16be7b..b7a9c95abe7 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2099,3 +2099,16 @@ SET @@sql_select_limit= @save_sql_select_limit; eval EXPLAIN EXTENDED $query; eval $query; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-17606: Query returns wrong results (while using CHARACTER SET utf8) +--echo # + +CREATE TABLE t1(l1 varchar(10), i2 int); +INSERT INTO t1 VALUES ('e',2),('o',6),('x',4); +CREATE TABLE t2 (v1 varchar(10) CHARACTER SET utf8, KEY v1 (v1(3))); +INSERT INTO t2 VALUES ('k'),('rid'),('f'),('x'); + +EXPLAIN EXTENDED SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +SELECT * FROM t1 where ( t1.l1 < ANY (SELECT MAX(t2.v1) FROM t2)); +DROP TABLE t1, t2; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 16ef8a192c5..ebe8e23add5 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2015,7 +2015,7 @@ bool Item_allany_subselect::transform_into_max_min(JOIN *join) The swap is needed for expressions of type 'f1 < ALL ( SELECT ....)' where we want to evaluate the sub query even if f1 would be null. */ - subs= func->create_swap(thd, *(optimizer->get_cache()), subs); + subs= func->create_swap(thd, expr, subs); thd->change_item_tree(place, subs); if (subs->fix_fields(thd, &subs)) DBUG_RETURN(true); |