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:
None 
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
Description:
When executing a query, an error message is displayed: ERROR 1815 (HY000): Internal error: Key not found.

How to repeat:
> create table t1(id int primary key, col1 int, col2 int, col3 int, key index_col1(col1), key index_col2(col2));

> 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));

It will report:
> ERROR 1815 (HY000): Internal error: Key not found

However, if I change the join order, the query returns the correct results. Like this:

> 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 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