Bug #102040 Different results of equivalent SQL querys
Submitted: 21 Dec 2020 13:55 Modified: 21 Dec 2020 15:44
Reporter: yangyang wang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.18 OS:Linux
Assigned to: CPU Architecture:x86

[21 Dec 2020 13:55] yangyang wang
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.
[21 Dec 2020 14:20] MySQL Verification Team
Please check with the latest release 8.0.22 and provide the create table and insert data sql file script. Thanks.
[21 Dec 2020 15:32] yangyang wang
I have checked with the latest release 8.0.22 and it seems to have be fixed. If it is really a bug 8.0.18, could you please provide me the fix patch of it? Thanks.
[21 Dec 2020 15:44] MySQL Verification Team
Thank you for the feedback. Closing as not a bug.
[21 Dec 2020 15:49] MySQL Verification Team
I don't know the specific patch which fixed the issue however you could download the 8.0.22 source and check.