Bug #107629 Incorrect result when using index and materialization-lookup
Submitted: 22 Jun 8:41 Modified: 22 Jun 10:05
Reporter: Wang Ke Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any
Tags: incorrect result, materialization

[22 Jun 8:41] Wang Ke
Description:
I found a test case which may cause mysql server produce incorrect result, and the behavior is attached as follows:

```
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29-debug-asan Source distribution

Copyright (c) 2000, 2022, 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> drop database mytest;
Query OK, 1 row affected (0.12 sec)

mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)

mysql> use mytest;
Database changed
mysql> CREATE TABLE t0 ( c0 DECIMAL ( 10 , 0 ) NOT NULL , PRIMARY KEY ( c0 ) , UNIQUE KEY c0 ( c0 ) ) ;
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO t0 VALUES ( 123 );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+-----+
| c0  |
+-----+
| 123 |
+-----+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
| id | select_type        | table | partitions | type            | possible_keys | key     | key_len | ref           | rows | filtered | Extra                                |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
|  1 | PRIMARY            | ra0   | NULL       | index           | NULL          | PRIMARY | 5       | NULL          |    1 |   100.00 | Using index                          |
|  1 | PRIMARY            | ra1   | NULL       | eq_ref          | PRIMARY,c0    | PRIMARY | 5       | mytest.ra0.c0 |    1 |   100.00 | Using where; Not exists; Using index |
|  3 | DEPENDENT SUBQUERY | ra2   | NULL       | unique_subquery | PRIMARY,c0    | PRIMARY | 5       | func          |    1 |   100.00 | Using index                          |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+---------------+------+----------+--------------------------------------+
3 rows in set, 1 warning (0.01 sec)

mysql> SET SESSION optimizer_switch='loosescan=off,firstmatch=off,duplicateweedout=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
Empty set (0.00 sec)

mysql> EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
| id | select_type        | table       | partitions | type            | possible_keys       | key                 | key_len | ref           | rows | filtered | Extra                   |
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
|  1 | PRIMARY            | ra0         | NULL       | index           | NULL                | PRIMARY             | 5       | NULL          |    1 |   100.00 | Using index             |
|  1 | PRIMARY            | <subquery2> | NULL       | eq_ref          | <auto_distinct_key> | <auto_distinct_key> | 6       | mytest.ra0.c0 |    1 |   100.00 | Using where; Not exists |
|  2 | MATERIALIZED       | ra1         | NULL       | index           | PRIMARY,c0          | PRIMARY             | 5       | NULL          |    1 |   100.00 | Using index             |
|  3 | DEPENDENT SUBQUERY | ra2         | NULL       | unique_subquery | PRIMARY,c0          | PRIMARY             | 5       | func          |    1 |   100.00 | Using index             |
+----+--------------------+-------------+------------+-----------------+---------------------+---------------------+---------+---------------+------+----------+-------------------------+
4 rows in set, 1 warning (0.00 sec)

```

When I close some optimization option to force the optimizer use materialization-lookup, I get inconsistent result. The server fails to fetch the row in the second query.

How to repeat:
Test case:

```
CREATE TABLE t0 ( c0 DECIMAL, PRIMARY KEY ( c0 ) ) ;
INSERT INTO t0 VALUES ( 123 );
SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
SET SESSION optimizer_switch='loosescan=off,firstmatch=off,duplicateweedout=off';
SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
EXPLAIN SELECT ra0 . c0 FROM t0 AS ra0 WHERE ra0 . c0 NOT IN ( SELECT ra1 . c0 FROM t0 ra1 WHERE ( ra1 . c0 IN ( SELECT ra2 . c0 AS ca0 FROM t0 ra2 ) ) = ( ra1 . c0 ) ) ;
```
[22 Jun 8:46] Wang Ke
By the way, I think it's similar to Bug #107576.

I don't know if the cause of the error is the same. If you have confirmed that it is the same bug, please let me know the cause of the error.
[22 Jun 10:05] MySQL Verification Team
Hello Ke Wang,

Thank you for the report and test case.

regards,
Umesh