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;
