Description:
MySQL may return incorrect results with duplicated rows when an IN subquery is transformed using semijoin materialization and the subquery references a view column that is defined using a function or expression, such as COLLATE.
During semijoin materialization, MySQL rewrites columns involved in the IN predicate into the form of subqueryN`.`COLUMN. However, when the subquery references a view and the referenced column in the view contains a function/expression, for example:
sql
CREATE VIEW v2 AS
SELECT t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1
FROM t2;
MySQL may fail to match the corresponding subquery column correctly.
As a result, the IN predicate condition can be lost during hash join / semijoin processing, causing incorrect duplicated rows to be returned.
Test Case
sql
SET optimizer_switch='duplicateweedout=off,firstmatch=off,loosescan=off';
CREATE TABLE t1(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4,
c2 CHAR(20) CHARACTER SET utf8mb4
);
ALTER TABLE t1 ADD INDEX ci(c1);
CREATE TABLE t2(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4
);
CREATE TABLE t3(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4
);
INSERT INTO t1 VALUES
(1, 'bbb', 'aaa'),
(2, 'bbb', 'aaa'),
(3, 'ccc', 'aaa');
INSERT INTO t2 VALUES
(1, 'xxx'),
(2, 'bbb'),
(3, 'yyy');
INSERT INTO t3 VALUES
(1, 'aaa'),
(2, 'aaa'),
(3, 'aaa');
CREATE VIEW v2 AS
SELECT
t2.pk AS pk,
t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1,
t3.c1 AS t3c
FROM t2
JOIN t3 ON t2.pk = t3.pk;
SELECT t1.pk
FROM t1 IGNORE INDEX(ci)
WHERE t1.c1 COLLATE utf8mb4_0900_ai_ci IN (
SELECT c1
FROM v2
WHERE v2.t3c = t1.c2
);
Expected Result
The query should return only rows from t1 where t1.c1 matches the result of the subquery.
sql
+------+
| pk |
+------+
| 1 |
| 2 |
+------+
2 rows in set
Actual Result
MySQL returns duplicated rows, including rows that should not satisfy the IN predicate:
sql
+------+
| pk |
+------+
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
+------+
9 rows in set
In particular, pk = 3 should not be returned because t1.c1 = 'ccc' does not match any c1 value from the subquery under the specified condition.
How to repeat:
SET optimizer_switch='duplicateweedout=off,firstmatch=off,loosescan=off';
CREATE TABLE t1(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4,
c2 CHAR(20) CHARACTER SET utf8mb4
);
ALTER TABLE t1 ADD INDEX ci(c1);
CREATE TABLE t2(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4
);
CREATE TABLE t3(
pk INT,
c1 CHAR(20) CHARACTER SET utf8mb4
);
INSERT INTO t1 VALUES
(1, 'bbb', 'aaa'),
(2, 'bbb', 'aaa'),
(3, 'ccc', 'aaa');
INSERT INTO t2 VALUES
(1, 'xxx'),
(2, 'bbb'),
(3, 'yyy');
INSERT INTO t3 VALUES
(1, 'aaa'),
(2, 'aaa'),
(3, 'aaa');
CREATE VIEW v2 AS
SELECT
t2.pk AS pk,
t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1,
t3.c1 AS t3c
FROM t2
JOIN t3 ON t2.pk = t3.pk;
SELECT t1.pk
FROM t1 IGNORE INDEX(ci)
WHERE t1.c1 COLLATE utf8mb4_0900_ai_ci IN (
SELECT c1
FROM v2
WHERE v2.t3c = t1.c2
);
Suggested fix:
I fixed it in a simple way, but I'm not sure whether it covers all cases.
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index d381cff..9bf7d67 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -8758,7 +8758,7 @@ Item *Item_equal::equality_substitution_transformer(uchar *arg) {
// Iterate over the fields selected from the subquery
uint fieldno = 0;
for (Item *existing : sj_nest->nested_join->sj_inner_exprs) {
- if (existing->real_item()->eq(item, false))
+ if (existing->real_item()->eq(item->real_item(), false))
added_fields.push_back(sj_nest->nested_join->sjm.mat_fields[fieldno]);
fieldno++;
}
@@ -8789,7 +8789,7 @@ Item *Item_func_eq::equality_substitution_transformer(uchar *arg) {
// Iterate over the fields selected from the subquery
uint fieldno = 0;
for (Item *existing : sj_nest->nested_join->sj_inner_exprs) {
- if (existing->real_item()->eq(args[1], false) &&
+ if (existing->real_item()->eq(args[1]->real_item(), false) &&
(args[0]->used_tables() & ~sj_nest->sj_inner_tables))
current_thd->change_item_tree(
args + 1, sj_nest->nested_join->sjm.mat_fields[fieldno]);
Description: MySQL may return incorrect results with duplicated rows when an IN subquery is transformed using semijoin materialization and the subquery references a view column that is defined using a function or expression, such as COLLATE. During semijoin materialization, MySQL rewrites columns involved in the IN predicate into the form of subqueryN`.`COLUMN. However, when the subquery references a view and the referenced column in the view contains a function/expression, for example: sql CREATE VIEW v2 AS SELECT t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1 FROM t2; MySQL may fail to match the corresponding subquery column correctly. As a result, the IN predicate condition can be lost during hash join / semijoin processing, causing incorrect duplicated rows to be returned. Test Case sql SET optimizer_switch='duplicateweedout=off,firstmatch=off,loosescan=off'; CREATE TABLE t1( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4, c2 CHAR(20) CHARACTER SET utf8mb4 ); ALTER TABLE t1 ADD INDEX ci(c1); CREATE TABLE t2( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4 ); CREATE TABLE t3( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4 ); INSERT INTO t1 VALUES (1, 'bbb', 'aaa'), (2, 'bbb', 'aaa'), (3, 'ccc', 'aaa'); INSERT INTO t2 VALUES (1, 'xxx'), (2, 'bbb'), (3, 'yyy'); INSERT INTO t3 VALUES (1, 'aaa'), (2, 'aaa'), (3, 'aaa'); CREATE VIEW v2 AS SELECT t2.pk AS pk, t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1, t3.c1 AS t3c FROM t2 JOIN t3 ON t2.pk = t3.pk; SELECT t1.pk FROM t1 IGNORE INDEX(ci) WHERE t1.c1 COLLATE utf8mb4_0900_ai_ci IN ( SELECT c1 FROM v2 WHERE v2.t3c = t1.c2 ); Expected Result The query should return only rows from t1 where t1.c1 matches the result of the subquery. sql +------+ | pk | +------+ | 1 | | 2 | +------+ 2 rows in set Actual Result MySQL returns duplicated rows, including rows that should not satisfy the IN predicate: sql +------+ | pk | +------+ | 1 | | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | +------+ 9 rows in set In particular, pk = 3 should not be returned because t1.c1 = 'ccc' does not match any c1 value from the subquery under the specified condition. How to repeat: SET optimizer_switch='duplicateweedout=off,firstmatch=off,loosescan=off'; CREATE TABLE t1( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4, c2 CHAR(20) CHARACTER SET utf8mb4 ); ALTER TABLE t1 ADD INDEX ci(c1); CREATE TABLE t2( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4 ); CREATE TABLE t3( pk INT, c1 CHAR(20) CHARACTER SET utf8mb4 ); INSERT INTO t1 VALUES (1, 'bbb', 'aaa'), (2, 'bbb', 'aaa'), (3, 'ccc', 'aaa'); INSERT INTO t2 VALUES (1, 'xxx'), (2, 'bbb'), (3, 'yyy'); INSERT INTO t3 VALUES (1, 'aaa'), (2, 'aaa'), (3, 'aaa'); CREATE VIEW v2 AS SELECT t2.pk AS pk, t2.c1 COLLATE utf8mb4_0900_ai_ci AS c1, t3.c1 AS t3c FROM t2 JOIN t3 ON t2.pk = t3.pk; SELECT t1.pk FROM t1 IGNORE INDEX(ci) WHERE t1.c1 COLLATE utf8mb4_0900_ai_ci IN ( SELECT c1 FROM v2 WHERE v2.t3c = t1.c2 ); Suggested fix: I fixed it in a simple way, but I'm not sure whether it covers all cases. diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index d381cff..9bf7d67 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -8758,7 +8758,7 @@ Item *Item_equal::equality_substitution_transformer(uchar *arg) { // Iterate over the fields selected from the subquery uint fieldno = 0; for (Item *existing : sj_nest->nested_join->sj_inner_exprs) { - if (existing->real_item()->eq(item, false)) + if (existing->real_item()->eq(item->real_item(), false)) added_fields.push_back(sj_nest->nested_join->sjm.mat_fields[fieldno]); fieldno++; } @@ -8789,7 +8789,7 @@ Item *Item_func_eq::equality_substitution_transformer(uchar *arg) { // Iterate over the fields selected from the subquery uint fieldno = 0; for (Item *existing : sj_nest->nested_join->sj_inner_exprs) { - if (existing->real_item()->eq(args[1], false) && + if (existing->real_item()->eq(args[1]->real_item(), false) && (args[0]->used_tables() & ~sj_nest->sj_inner_tables)) current_thd->change_item_tree( args + 1, sj_nest->nested_join->sjm.mat_fields[fieldno]);