Bug #115804 | Key not found error when executing query | ||
---|---|---|---|
Submitted: | 9 Aug 2024 5:10 | Modified: | 9 Aug 2024 12:59 |
Reporter: | Jingqi Tian (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0, 8.0.39 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[9 Aug 2024 5:10]
Jingqi Tian
[9 Aug 2024 7:12]
MySQL Verification Team
Hello Jingqi Tian, Thank you for the report and feedback. I quickly tried it on current GA(8.0.39) but not seeing any issue as reported. Am I missing anything? Thank you. -- bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> create table t1(id int primary key, col1 int, col2 int, col3 int, key index_col1(col1), key index_col2(col2)); Query OK, 0 rows affected (0.03 sec) mysql> select /*+ JOIN_ORDER(d2@subq1, d1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec) mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select /*+ JOIN_ORDER(d2@subq1, d1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec) mysql> select /*+ JOIN_ORDER(d1, d2@subq1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec) regards, Umesh
[9 Aug 2024 10:42]
Jingqi Tian
Sorry, I missed a condition, you should turn off duplicateweedout optimization. Before executing this SQL: set optimizer_switch='duplicateweedout=off';
[9 Aug 2024 11:15]
Jingqi Tian
这个bug与semi join的策略有关,仅当策略为MaterializeScan时,会触发这个bug。
[9 Aug 2024 11:17]
Jingqi Tian
Please ignore the previous comment. This bug is related to the semi join strategy and is triggered only when the strategy is MaterializeScan.
[9 Aug 2024 12:59]
MySQL Verification Team
Thank you for the details. - bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.39 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table t1(id int primary key, col1 int, col2 int, col3 int, key index_col1(col1), key index_col2(col2)); Query OK, 0 rows affected (0.03 sec) mysql> set optimizer_switch='duplicateweedout=off'; Query OK, 0 rows affected (0.00 sec) mysql> select /*+ JOIN_ORDER(d2@subq1, d1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); ERROR 1815 (HY000): Internal error: Key not found mysql> mysql> select /*+ JOIN_ORDER(d1, d2@subq1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec)
[9 Aug 2024 13:08]
MySQL Verification Team
Checked 8.4.2 build, looks like this is fixed: bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.4.2 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table t1(id int primary key, col1 int, col2 int, col3 int, key index_col1(col1), key index_col2(col2)); Query OK, 0 rows affected (0.03 sec) mysql> set optimizer_switch='duplicateweedout=off'; Query OK, 0 rows affected (0.00 sec) mysql> select /*+ JOIN_ORDER(d2@subq1, d1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec) mysql> select /*+ JOIN_ORDER(d1, d2@subq1) */ 1 from t1 d1 where exists (select /*+ QB_NAME(subq1) */ 1 from t1 d2 where d1.col1 = (select d3.col1 from t1 d3 where d3.col2 = d2.col2 limit 1)); Empty set (0.00 sec) mysql>
[15 Aug 2024 11:02]
Dag Wanvik
Posted by developer: Bisecting shows that this bug was fixed by this commit: commit 0760966129517e49486d46503129a8e6d52a765b Author: Amit Khandekar <amit.khandekar@oracle.com> Date: Fri Jun 30 13:35:14 2023 +0200 Bug#35535934: Subquery in Semi-join inner expressions causes assert failure