Bug #111435 Hash join results in incorrect query results
Submitted: 15 Jun 2023 11:14 Modified: 15 Jun 2023 13:16
Reporter: yijun xie Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.32 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2023 11:14] yijun xie
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

The above sql files exists in the compressed file(two_table_data.rar);
You can get this compressed file in the attachment.

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;
[15 Jun 2023 11:15] yijun xie
two_table_data

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

[15 Jun 2023 13:03] yijun xie
repeat problem
[15 Jun 2023 13:16] MySQL Verification Team
Hi Mr. xie,

This bug report is a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=111414

Please , do not create more then one report for the same behaviour.

Duplicate.