Description:
Query results are inconsistent when the optimizer option block_nested_loop is enabled and disabled.
How to repeat:
1. Import data.
mysql> source test.sql
The test file is attached.
2. Run the SQL statement.
mysql> use test;
Database changed
mysql> set optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM t1 a left join t1 b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from t1 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.11 sec)
mysql> set optimizer_switch='block_nested_loop=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a.datetime_col col1, COUNT(a.varchar_col) col4 FROM t1 a left join t1 b on (a.char_col,a.text_col) in (select char_col col1, text_col col2 from t1 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.10 sec)
The query results are inconsistent!
