Description:
Hello mysql team:
This is a bug about subquery strategy, details are as follows.
How to repeat:
Can be repeated in 9.0.1 and 8.0.39:
-- Init data:
CREATE TABLE t_egwehw_ ( c_h2kv int NOT NULL, c_f5nbt3_2kw int, c_ox6_xav text, PRIMARY KEY (c_f5nbt3_2kw), UNIQUE (c_f5nbt3_2kw) );
ALTER TABLE t_egwehw_ ADD COLUMN c_maju7ns double;
INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns) VALUES (1624010630, -1536378033, CAST(NULL AS char), 65534.4), (-2139138529, -782859040, 'wreu7z1', 2.62), (77597713, -865354496, 'dc62f', 0.7), (1, -154445972, '_a_', -18446744073709551615.5);
ALTER TABLE t_egwehw_ ADD COLUMN c_chdy double;
INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns, c_chdy) VALUES (-451958935, -28, 'roku', 9223372036854775808.4, 65535.3), (-1420356508, -2122253581, 'v4z4', 89.18, 96.49), (-6, -1259327575, 'ef', 12.67, 1.47), (-19, 1872162897, 't', 2147483648.100000, 10.65);
INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns, c_chdy) VALUES (766182265, 318549905, 'bo', CAST(NULL AS double), 14.53), (1537109178, -977012509, CAST(NULL AS char), 32766.2, 256.9), (2069848164, -1342527160, 'pj2', 49.22, 60.28), (-23, -883975851, 'o0rc', CAST(NULL AS double), 70.10);
ALTER TABLE t_egwehw_ ADD COLUMN c_kerq648 int;
-- SQL:
WITH cte_17 AS ( SELECT ref_45.c_f5nbt3_2kw AS c0, ref_45.c_ox6_xav AS c2 FROM t_egwehw_ ref_45 ), cte_28 AS ( SELECT ref_68.c_kerq648 AS c2, RIGHT(CAST(( SELECT c_ox6_xav FROM t_egwehw_ ORDER BY c_ox6_xav LIMIT 1 OFFSET 6 ) AS char), CAST(ref_68.c_kerq648 AS signed)) AS c3, ref_68.c_f5nbt3_2kw AS c4 FROM t_egwehw_ ref_68 ) SELECT 1 AS c5 FROM cte_17 ref_229 WHERE CAST(CAST(CAST(ref_229.c2 AS char) AS signed) + CAST(ref_229.c0 AS decimal) AS decimal) NOT IN ( SELECT /*+subquery(intoexists)*/ ref_230.c4 AS c0 FROM cte_28 ref_230 WHERE CAST(CAST(ref_230.c3 LIKE 'mvb_o%2' AS unsigned) <> CAST(ref_230.c2 AS signed) AS unsigned) ORDER BY c0 DESC );
Empty set, 10 warnings (0.00 sec)
notice that we choose INTOEXISTS strategy for NOT IN Subquery, if choose MATERIALIZATION:
WITH cte_17 AS ( SELECT ref_45.c_f5nbt3_2kw AS c0, ref_45.c_ox6_xav AS c2 FROM t_egwehw_ ref_45 ), cte_28 AS ( SELECT ref_68.c_kerq648 AS c2, RIGHT(CAST(( SELECT c_ox6_xav FROM t_egwehw_ ORDER BY c_ox6_xav LIMIT 1 OFFSET 6 ) AS char), CAST(ref_68.c_kerq648 AS signed)) AS
c3, ref_68.c_f5nbt3_2kw AS c4 FROM t_egwehw_ ref_68 ) SELECT 1 AS c5 FROM cte_17 ref_229 WHERE CAST(CAST(CAST(ref_229.c2 AS char) AS signed) + CAST(ref_229.c0 AS decimal) AS decimal) NOT IN ( SELECT /*+subquery(materialization)*/ ref_230.c4 AS c0 FROM cte_28 ref_230 WHERE CAST(CAST(ref_230.c3 LIKE 'mvb_o%2' AS unsigned) <> CAST(ref_230.c2 AS signed) AS unsigned) ORDER BY c0 DESC );
+----+
| c5 |
+----+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+----+
12 rows in set, 20 warnings (0.00 sec)
Suggested fix:
It is about condition `0 <> cast((cast((ref_230.c3 like 'mvb_o%2') as unsigned) <> cast(ref_230.c2 as signed)) as unsigned)` is ignored in INTOEXISTS strategy.
Here is my fix, based on 9.0.1:
--- a/sql/sql_executor.cc
+++ b/sql/sql_executor.cc
@@ -3407,8 +3407,7 @@ void JOIN::create_access_paths_for_index_subquery() {
path = NewMaterializedTableFunctionAccessPath(thd, first_qep_tab->table(),
tl->table_function, path);
} else {
- path = GetAccessPathForDerivedTable(thd, first_qep_tab,
- first_qep_tab->access_path());
+ path = GetAccessPathForDerivedTable(thd, first_qep_tab, path);
}
}
Description: Hello mysql team: This is a bug about subquery strategy, details are as follows. How to repeat: Can be repeated in 9.0.1 and 8.0.39: -- Init data: CREATE TABLE t_egwehw_ ( c_h2kv int NOT NULL, c_f5nbt3_2kw int, c_ox6_xav text, PRIMARY KEY (c_f5nbt3_2kw), UNIQUE (c_f5nbt3_2kw) ); ALTER TABLE t_egwehw_ ADD COLUMN c_maju7ns double; INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns) VALUES (1624010630, -1536378033, CAST(NULL AS char), 65534.4), (-2139138529, -782859040, 'wreu7z1', 2.62), (77597713, -865354496, 'dc62f', 0.7), (1, -154445972, '_a_', -18446744073709551615.5); ALTER TABLE t_egwehw_ ADD COLUMN c_chdy double; INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns, c_chdy) VALUES (-451958935, -28, 'roku', 9223372036854775808.4, 65535.3), (-1420356508, -2122253581, 'v4z4', 89.18, 96.49), (-6, -1259327575, 'ef', 12.67, 1.47), (-19, 1872162897, 't', 2147483648.100000, 10.65); INSERT INTO t_egwehw_ (c_h2kv, c_f5nbt3_2kw, c_ox6_xav, c_maju7ns, c_chdy) VALUES (766182265, 318549905, 'bo', CAST(NULL AS double), 14.53), (1537109178, -977012509, CAST(NULL AS char), 32766.2, 256.9), (2069848164, -1342527160, 'pj2', 49.22, 60.28), (-23, -883975851, 'o0rc', CAST(NULL AS double), 70.10); ALTER TABLE t_egwehw_ ADD COLUMN c_kerq648 int; -- SQL: WITH cte_17 AS ( SELECT ref_45.c_f5nbt3_2kw AS c0, ref_45.c_ox6_xav AS c2 FROM t_egwehw_ ref_45 ), cte_28 AS ( SELECT ref_68.c_kerq648 AS c2, RIGHT(CAST(( SELECT c_ox6_xav FROM t_egwehw_ ORDER BY c_ox6_xav LIMIT 1 OFFSET 6 ) AS char), CAST(ref_68.c_kerq648 AS signed)) AS c3, ref_68.c_f5nbt3_2kw AS c4 FROM t_egwehw_ ref_68 ) SELECT 1 AS c5 FROM cte_17 ref_229 WHERE CAST(CAST(CAST(ref_229.c2 AS char) AS signed) + CAST(ref_229.c0 AS decimal) AS decimal) NOT IN ( SELECT /*+subquery(intoexists)*/ ref_230.c4 AS c0 FROM cte_28 ref_230 WHERE CAST(CAST(ref_230.c3 LIKE 'mvb_o%2' AS unsigned) <> CAST(ref_230.c2 AS signed) AS unsigned) ORDER BY c0 DESC ); Empty set, 10 warnings (0.00 sec) notice that we choose INTOEXISTS strategy for NOT IN Subquery, if choose MATERIALIZATION: WITH cte_17 AS ( SELECT ref_45.c_f5nbt3_2kw AS c0, ref_45.c_ox6_xav AS c2 FROM t_egwehw_ ref_45 ), cte_28 AS ( SELECT ref_68.c_kerq648 AS c2, RIGHT(CAST(( SELECT c_ox6_xav FROM t_egwehw_ ORDER BY c_ox6_xav LIMIT 1 OFFSET 6 ) AS char), CAST(ref_68.c_kerq648 AS signed)) AS c3, ref_68.c_f5nbt3_2kw AS c4 FROM t_egwehw_ ref_68 ) SELECT 1 AS c5 FROM cte_17 ref_229 WHERE CAST(CAST(CAST(ref_229.c2 AS char) AS signed) + CAST(ref_229.c0 AS decimal) AS decimal) NOT IN ( SELECT /*+subquery(materialization)*/ ref_230.c4 AS c0 FROM cte_28 ref_230 WHERE CAST(CAST(ref_230.c3 LIKE 'mvb_o%2' AS unsigned) <> CAST(ref_230.c2 AS signed) AS unsigned) ORDER BY c0 DESC ); +----+ | c5 | +----+ | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | +----+ 12 rows in set, 20 warnings (0.00 sec) Suggested fix: It is about condition `0 <> cast((cast((ref_230.c3 like 'mvb_o%2') as unsigned) <> cast(ref_230.c2 as signed)) as unsigned)` is ignored in INTOEXISTS strategy. Here is my fix, based on 9.0.1: --- a/sql/sql_executor.cc +++ b/sql/sql_executor.cc @@ -3407,8 +3407,7 @@ void JOIN::create_access_paths_for_index_subquery() { path = NewMaterializedTableFunctionAccessPath(thd, first_qep_tab->table(), tl->table_function, path); } else { - path = GetAccessPathForDerivedTable(thd, first_qep_tab, - first_qep_tab->access_path()); + path = GetAccessPathForDerivedTable(thd, first_qep_tab, path); } }