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.
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.