Description:
There are tow equivalent SQL querys, but results is different:
mysql> SELECT subq_1.c_ytd_payment
-> FROM (SELECT c_ytd_payment FROM t1 LIMIT 2) AS subq_1
-> INNER JOIN t1
-> LEFT JOIN t2
-> LEFT JOIN t1 AS ref_40 ON (EXISTS (SELECT 1 FROM t0 LIMIT 1))
-> ON (FALSE)
-> ON (TRUE)
-> GROUP BY 1;
+---------------+
| c_ytd_payment |
+---------------+
| 5240194.79 |
+---------------+
1 row in set (0.01 sec)
mysql> SELECT subq_1.c_ytd_payment
-> FROM (SELECT c_ytd_payment FROM t1 LIMIT 2) AS subq_1
-> INNER JOIN t1
-> LEFT JOIN t2
-> LEFT JOIN t1 AS ref_40 ON (TRUE)
-> ON (FALSE)
-> ON (TRUE)
-> GROUP BY 1
-> ;
+---------------+
| c_ytd_payment |
+---------------+
| 5213785.98 |
| 5240194.79 |
+---------------+
2 rows in set (0.00 sec)
And the explain of these SQL querys is different as blow:
mysql> explain SELECT subq_1.c_ytd_payment
-> FROM (SELECT c_ytd_payment FROM t1 LIMIT 2) AS subq_1
-> INNER JOIN t1
-> LEFT JOIN t2
-> LEFT JOIN t1 AS ref_40 ON (EXISTS (SELECT 1 FROM t0 LIMIT 1))
-> ON (FALSE)
-> ON (TRUE)
-> GROUP BY 1;
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary |
| 1 | PRIMARY | t1 | NULL | index | NULL | ndx_c_discount | 3 | NULL | 84 | 100.00 | Using index; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | t2 | NULL | index | NULL | PRIMARY | 4 | NULL | 124 | 100.00 | Using where; Using index |
| 1 | PRIMARY | t0 | NULL | index | NULL | ndx_c_discount | 3 | NULL | 15 | 100.00 | Using where; Using index; FirstMatch(t2) |
| 1 | PRIMARY | ref_40 | NULL | index | NULL | ndx_c_discount | 3 | NULL | 84 | 100.00 | Using index |
| 2 | DERIVED | t1 | NULL | index | NULL | ndx_c_ytd_payment | 7 | NULL | 84 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+----------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
mysql> explain SELECT subq_1.c_ytd_payment
-> FROM (SELECT c_ytd_payment FROM t1 LIMIT 2) AS subq_1
-> INNER JOIN t1
-> LEFT JOIN t2
-> LEFT JOIN t1 AS ref_40 ON (TRUE)
-> ON (FALSE)
-> ON (TRUE)
-> GROUP BY 1;
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary |
| 1 | PRIMARY | t1 | NULL | index | NULL | ndx_c_discount | 3 | NULL | 84 | 100.00 | Using index; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | t2 | NULL | index | NULL | PRIMARY | 4 | NULL | 124 | 100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | ref_40 | NULL | index | NULL | ndx_c_discount | 3 | NULL | 84 | 100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
| 2 | DERIVED | t1 | NULL | index | NULL | ndx_c_ytd_payment | 7 | NULL | 84 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------------+---------+------+------+----------+-----------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
How to repeat:
Load data and execute those SQL querys above.