MySQL Bugs: #111414: HASH JOIN results in wrong query results

Bug #111414	HASH JOIN results in wrong query results
Submitted:	14 Jun 2023 9:26	Modified:	16 Jun 2023 9:32
Reporter:	yijun xie	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There are two table structures here. The only difference between the two tables is that the second table only has primary key indexes. Insert the same data on two tables. And execute the same select statement on the two tables respectively。
Expected: the query results of the two tables are the same 
Actual: the query results are different. The second table gets wrong results through hash join.

the second table only has primary key indexes result：
mysql> explain SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_2_all_type2index a left join tbl_2_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_2_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;
+----+-------------+----------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table                | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+----------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | a                    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  107 |   100.00 | Using temporary; Using filesort; Start temporary          |
|  1 | SIMPLE      | tbl_2_all_type2index | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  107 |   100.00 | Using where; Using join buffer (hash join)                |
|  1 | SIMPLE      | b                    | NULL       | index | NULL          | PRIMARY | 4       | NULL |  107 |   100.00 | Using index; End temporary; Using join buffer (hash join) |
+----+-------------+----------------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_2_all_type2index a left join tbl_2_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_2_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;
+----------------------------+------+
| col1                       | col4 |
+----------------------------+------+
| 1000-01-01 00:00:00.000000 |  535 |
| 1992-03-04 12:20:00.000000 |    0 |
| 1995-09-02 13:20:00.000000 |    0 |
| 1997-09-02 13:20:00.000000 |    0 |
| 1999-09-02 13:20:00.000000 |    0 |
| 2001-01-01 00:00:00.000000 |  535 |
| 2001-02-01 00:00:00.000000 |  535 |
| 2001-03-02 00:00:00.000000 |  535 |
| 2001-04-06 00:00:00.000000 |  535 |
| 2001-05-09 00:00:00.000000 |  535 |
+----------------------------+------+
10 rows in set (0.24 sec)

first table result：

mysql> explain SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_1_all_type2index a left join tbl_1_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_1_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;
+----+-------------+----------------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------+-------------------+------+----------+---------------------------------------------+
| id | select_type | table                | partitions | type  | possible_keys                                                                                                                                                                            | key                 | key_len | ref               | rows | filtered | Extra                                       |
+----+-------------+----------------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------+-------------------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | a                    | NULL       | ALL   | ndx_id_datetime_col,ndx_int_datetime_col,ndx_decimal_datetime_col,ndx_float_datetime_col,ndx_datetime_timestamp_col,ndx_datetime_time_col,ndx_datetime_char_col,ndx_datetime_varchar_col | NULL                | NULL    | NULL              |  107 |   100.00 | Using temporary; Using filesort             |
|  1 | SIMPLE      | tbl_1_all_type2index | NULL       | ref   | ndx_bigint_char_col,ndx_char_varchar_col                                                                                                                                                 | ndx_bigint_char_col | 13      | testok.a.char_col |   17 |   100.00 | Using where; Start temporary; End temporary |
|  1 | SIMPLE      | b                    | NULL       | index | NULL                                                                                                                                                                                     | ndx_id_bigint_col   | 4       | NULL              |  107 |   100.00 | Using index                                 |
+----+-------------+----------------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+---------+-------------------+------+----------+---------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_1_all_type2index a left join tbl_1_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_1_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;
+----------------------------+-------+
| col1                       | col4  |
+----------------------------+-------+
| 1000-01-01 00:00:00.000000 |  2675 |
| 1992-03-04 12:20:00.000000 |     0 |
| 1995-09-02 13:20:00.000000 |     0 |
| 1997-09-02 13:20:00.000000 |     0 |
| 1999-09-02 13:20:00.000000 |     0 |
| 2001-01-01 00:00:00.000000 | 29425 |
| 2001-02-01 00:00:00.000000 |  2675 |
| 2001-03-02 00:00:00.000000 | 29425 |
| 2001-04-06 00:00:00.000000 | 29425 |
| 2001-05-09 00:00:00.000000 | 29425 |
+----------------------------+-------+
10 rows in set (0.18 sec)

How to repeat:
source tbl_1_all_type2index.sql
source tbl_2_all_type2index.sql

create view view_tbl_1_all_type2index as select * from tbl_1_all_type2index;
SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_1_all_type2index a left join tbl_1_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_1_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;

create view view_tbl_2_all_type2index as select * from tbl_2_all_type2index ;
SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM tbl_2_all_type2index a left join tbl_2_all_type2index b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from view_tbl_2_all_type2index a order by 1,2) group by 1 order by 1,2 limit 10;

two table data

Attachment: testok_tbl_2_all_type2index.sql (application/octet-stream, text), 41.55 KiB.

two table data

Attachment: test.rar (application/octet-stream, text), 6.02 KiB.

Hi Mr. xie,

Thank you for your bug report.

However, we can not repeat it. We can not repeat your tests since we have got only the CREATE TABLE for tbl_2_all_type2index table. Please, upload also the SQL for tbl_1_all_type2index.

Waiting for your feedback.

two_table_data

Attachment: two_table_data.rar (application/octet-stream, text), 6.02 KiB.

This file(two_table_data.rar) contains information for both tables。
The specific information of the file is as follows：

[15 Jun 1:19] yijun xie
two_table_data

Attachment: two_table_data.rar (application/octet-stream, text), 6.02 KiB.

You can get this file in the attachment.

Hi Mr. xie,

Sorry, but we can not use RAR format.

Can you send us both CREATE TABLE in an .sql file ????

table1

Attachment: testok_tbl_1_all_type2index.sql (application/octet-stream, text), 44.00 KiB.

table

Attachment: testok_tbl_2_all_type2index.sql (application/octet-stream, text), 41.55 KiB.

table2

Attachment: testok_tbl_2_all_type2index.sql (application/octet-stream, text), 41.55 KiB.

First of all, I am very sorry for sending the rar file. Secondly, I sent the sql files of table1 and table2. But I accidentally sent table2 twice. You can just take table1 and table2.Looking forward to your reply.

Hi Mr. xie,

You have finally provided us with all that is needed. With the caveat that the names of the tables in your report are not the same as those in the dumps of the tables.

However, with 8.0.33 we get the following results:

+----------------------------+------+
| col1                       | col4 |
+----------------------------+------+
| 1000-01-01 00:00:00.000000 |  535 |
| 1992-03-04 12:20:00.000000 |    0 |
| 1995-09-02 13:20:00.000000 |    0 |
| 1997-09-02 13:20:00.000000 |    0 |
| 1999-09-02 13:20:00.000000 |    0 |
| 2001-01-01 00:00:00.000000 |  535 |
| 2001-02-01 00:00:00.000000 |  535 |
| 2001-03-02 00:00:00.000000 |  535 |
| 2001-04-06 00:00:00.000000 |  535 |
| 2001-05-09 00:00:00.000000 |  535 |
+----------------------------+------+
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    1 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | b     | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using index                     |
|  1 | SIMPLE      | a     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    1 |   100.00 | Using where; FirstMatch(b)      |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
+----------------------------+------+
| col1                       | col4 |
+----------------------------+------+
| 1000-01-01 00:00:00.000000 |  535 |
| 1992-03-04 12:20:00.000000 |    0 |
| 1995-09-02 13:20:00.000000 |    0 |
| 1997-09-02 13:20:00.000000 |    0 |
| 1999-09-02 13:20:00.000000 |    0 |
| 2001-01-01 00:00:00.000000 |  535 |
| 2001-02-01 00:00:00.000000 |  535 |
| 2001-03-02 00:00:00.000000 |  535 |
| 2001-04-06 00:00:00.000000 |  535 |
| 2001-05-09 00:00:00.000000 |  535 |
+----------------------------+------+
+----+--------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------+------+----------+--------------------------------------------+
| id | select_type  | table       | partitions | type  | possible_keys                                                                                                                                                                            | key               | key_len | ref  | rows | filtered | Extra                                      |
+----+--------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE       | a           | NULL       | ALL   | ndx_id_datetime_col,ndx_int_datetime_col,ndx_decimal_datetime_col,ndx_float_datetime_col,ndx_datetime_timestamp_col,ndx_datetime_time_col,ndx_datetime_char_col,ndx_datetime_varchar_col | NULL              | NULL    | NULL |    5 |   100.00 | Using temporary; Using filesort            |
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL                                                                                                                                                                                     | NULL              | NULL    | NULL | NULL |   100.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE       | b           | NULL       | index | NULL                                                                                                                                                                                     | ndx_id_bigint_col | 4       | NULL |    5 |   100.00 | Using index; Using join buffer (hash join) |
|  2 | MATERIALIZED | a           | NULL       | ALL   | ndx_bigint_char_col,ndx_char_varchar_col                                                                                                                                                 | NULL              | NULL    | NULL |    5 |   100.00 | NULL                                       |
+----+--------------+-------------+------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+------+------+----------+--------------------------------------------+

Results are identical , as you can see .......

EXPLAINs are different, because the other table has no indices !!!!!

Do note that we used our own production binary, which can be obtained from dev.mysql.com.

Can't repeat.

Thank you for your reply. I installed version 8.0.33 of mysql, and I still found the problem about hash join. I am currently simplifying the operation steps to make the problem easier to reproduce.