MySQL Bugs: #112985: Inconsistent results when switching hash join optimization

Bug #112985	Inconsistent results when switching hash join optimization
Submitted:	7 Nov 2023 8:33	Modified:	7 Nov 2023 15:07
Reporter:	Wang Ke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0, 8.1, 8.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	incorrect result

Description:
Hello, please look at the following test case in MySQL 8.2.0:

```
mysql> CREATE TABLE IF NOT EXISTS t0 ( c0 DECIMAL ZEROFILL NOT NULL NULL ) ;
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> INSERT INTO t0 ( c0 ) VALUES ( 1 ) , ( 0 ) , ( 1 ), (NULL) ;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ;
+------------+
| ca1        |
+------------+
| 0000000001 |
| 0000000000 |
| 0000000001 |
+------------+
3 rows in set, 3 warnings (0.00 sec)

mysql> SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT /*+ NO_BNL(ra1) */ ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ;
Empty set, 3 warnings (0.01 sec)

mysql> EXPLAIN SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
|  1 | SIMPLE      | ra0   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where                                                 |
|  1 | SIMPLE      | ra1   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; FirstMatch(ra0); Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------+
2 rows in set, 3 warnings (0.01 sec)

mysql> EXPLAIN SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT /*+ NO_BNL(ra1) */ ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ;
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE       | ra0         | NULL       | ALL    | NULL                | NULL                | NULL    | NULL               |    4 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 12      | mytest.ra0.c0,func |    1 |   100.00 | Using where |
|  2 | MATERIALIZED | ra1         | NULL       | ALL    | NULL                | NULL                | NULL    | NULL               |    4 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+--------------------+------+----------+-------------+
3 rows in set, 3 warnings (0.00 sec)

```

When switching the BNL optimization, we can get different result.

Since the result of the result of the CONCAT function should not be equal to the column c0, I think the correct result of the SELECT statement should be the second, that is, empty set.

When I analyzed the query plan, I noticed that it's also contains using where condition and hash join buffer. It is suspected that this is similar to the previous bug#112816.

But since the triggering condition is different, it's prudent for me to report this separately.

Hope to fix the bug in hash join as soon as possible.

Thanks!

How to repeat:
Test case:

```
CREATE TABLE IF NOT EXISTS t0 ( c0 DECIMAL ZEROFILL NOT NULL NULL ) ;
INSERT INTO t0 ( c0 ) VALUES ( 1 ) , ( 0 ) , ( 1 ), (NULL) ;
SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ; -- 3 rows
SELECT ra0.c0 ca1 FROM t0 ra0 WHERE ra0.c0 IN ( SELECT /*+ NO_BNL(ra1) */ ra1.c0 ca0 FROM t0 ra1 WHERE CONCAT( ra0.c0 , 'x' , ra0.c0 ) = ( ra1.c0 ) ) ; -- empty
```

Hi Mr. Ke,

Thank you for your bug report.

First of all, we can not reveal the internal bug numbers to the public. Anyway, only Oracle employees have access to the internal database, so it would be of no use with you.

We have found some problems with your test case. It is full of erroneous commands. You are inserting values that are not numbers at all. You are comparing values of different types.

Here is the output from your test case, with all the warnings:

---------------------------------------------------------

ca1
0000000001
0000000000
0000000001
Level	Code	Message
Warning	1292	Truncated incorrect DOUBLE value: '0000000001x0000000001'
Warning	1292	Truncated incorrect DOUBLE value: '0000000000x0000000000'
Warning	1292	Truncated incorrect DOUBLE value: '0000000001x0000000001'
Level	Code	Message
Warning	1366	Incorrect decimal value: '0000000001x0000000001' for column 'c0' at row 1
Warning	1366	Incorrect decimal value: '0000000000x0000000000' for column 'c0' at row 1
Warning	1366	Incorrect decimal value: '0000000001x0000000001' for column 'c0' at row 1
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ra0	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
1	SIMPLE	ra1	NULL	ALL	NULL	NULL	NULL	NULL	4	25.00	Using where; FirstMatch(ra0); Using join buffer (hash join)
Level	Code	Message
Note	1276	Field or reference 'test.ra0.c0' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.ra0.c0' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`ra0`.`c0` AS `ca1` from `test`.`t0` `ra0` semi join (`test`.`t0` `ra1`) where ((`test`.`ra1`.`c0` = `test`.`ra0`.`c0`) and (cast(concat(`test`.`ra0`.`c0`,'x',`test`.`ra0`.`c0`) as double) = cast(`test`.`ra0`.`c0` as double)))
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ra0	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
1	SIMPLE	<subquery2>	NULL	eq_ref	<auto_distinct_key>	<auto_distinct_key>	12	test.ra0.c0,func	1	100.00	Using where
2	MATERIALIZED	ra1	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
Level	Code	Message
Note	1276	Field or reference 'test.ra0.c0' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.ra0.c0' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select /*+ NO_BNL(`ra1`@`select#2`) */ `test`.`ra0`.`c0` AS `ca1` from `test`.`t0` `ra0` semi join (`test`.`t0` `ra1`) where ((`<subquery2>`.`ca0` = `test`.`ra0`.`c0`) and (`<subquery2>`.`c0` = `test`.`ra0`.`c0`) and (cast(concat(`test`.`ra0`.`c0`,'x',`test`.`ra0`.`c0`) as double) = cast(`<subquery2>`.`ca0` as double)))
---------------------------------------------------------

We corrected all your commands so that we do not get any warnings and where all the types are coherent.

With those corrections, we get the same output for both queries:

------------------------------------------------------------------

ca1
0000000002
------------------
------------------
ca1
0000000002
-----------------
-----------------
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ra0	NULL	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
1	SIMPLE	ra1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where; FirstMatch(ra0); Using join buffer (hash join)
Level	Code	Message
Note	1003	/* select#1 */ select `test`.`ra0`.`c0` AS `ca1` from `test`.`t0` `ra0` semi join (`test`.`t0` `ra1`) where ((`test`.`ra1`.`c0` = `test`.`ra0`.`c0`) and (`test`.`ra0`.`c0` > 1))
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	ra1	NULL	ALL	NULL	NULL	NULL	NULL	5	33.33	Using where; Start temporary
1	SIMPLE	ra0	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where; End temporary; Using join buffer (hash join)
Level	Code	Message
Note	1003	/* select#1 */ select /*+ NO_BNL(`ra1`@`select#2`) */ `test`.`ra0`.`c0` AS `ca1` from `test`.`t0` `ra0` semi join (`test`.`t0` `ra1`) where ((`test`.`ra0`.`c0` = `test`.`ra1`.`c0`) and (`test`.`ra1`.`c0` > 1))
-----------------------------------------------------------------

We do not think that this is a bug, but we shall do some more checks.

Hi Mr. Ke,

We have analysed further your test case.

We decided that it is a bug, in spite of the totally wrong values that you have inserted and compared.

It affects 8.0, 8.1 and 8.2.

This is now a S3 verified bug report.

Thank you for your patient explaination.

Indeed, considering the warning message, I noticed that the string was truncated and compared as DOUBLE with ra1.c0. In that case, the empty query result should be incorrect.

I would like to explain why this test case looks so "erroneous". That's because the test case was generated by a fuzzer and was simplified manually. In the process of simplification, we also found that not performing the erroneous behavior causing WARNING would result in the bug not being reproducible. I understand the behavior of dropping severity down, and it's probably rare for someone to perform this so-called "erroneous" behavior in a real-world application, and this bug is really difficult to trigger.

We're doing a study on DBMS bugs, and we asked you for internal bug IDs because, in [Github](https://github.com/mysql/mysql-server/commit/4fe07d436b800d8da72de561d4ee7f0072c05539), where mentioned Bug#35730982. We don't think this kind of serial number is information that needs to be kept secret, and our access to this number makes it easy to review the bug's corresponding patch in Github for our subsequent learning.

If you think that the internal bug id is something that needs to be kept private and inconvenient for public disclosure, that's just fine.

Thanks to Oracle for making MySQL better all the time!

Thank you for your kind words.