| Bug #112816 | Inconsistent results when JOINed in different order | ||
|---|---|---|---|
| Submitted: | 24 Oct 2023 16:15 | Modified: | 3 Nov 2023 9:57 |
| Reporter: | Wang Ke | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0, 8.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | incorrect result | ||
[24 Oct 2023 16:15]
Wang Ke
[25 Oct 2023 10:38]
MySQL Verification Team
Hi Mr. Ke, Thank you for your bug report. Not only that the EXPLAIN's are changed , but the results are changed. We do not think that optimiser hints should be allowed to change results. It affects both the latest 8.0 and 8.1. Verified as reported. We also increased the severity of the bug.
[3 Nov 2023 9:57]
Wang Ke
For your information, it is supposed that this is caused by using both where condition and hash join buffer. Another similar test case can be provided for you: ``` mysql> CREATE TABLE t1 ( c2 YEAR DEFAULT 1 , c3 YEAR DEFAULT 1 ) ; Query OK, 0 rows affected (0.16 sec) mysql> INSERT INTO t1 VALUES ( 'x' NOT LIKE 'x' ESCAPE 'x' , CAST( 1 / 1 AS CHAR CHAR SET gbk ) ) ; Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM ( SELECT ra0.c2 AS ca2 FROM t1 AS ra0 ) AS ra1 , LATERAL ( SELECT * FROM t1 AS ra2 LEFT JOIN ( SELECT ra1.ca2 AS ca0 FROM t1 AS ra3 ) AS ra4 ON ( false ) WHERE ra4.ca0 IS NULL ) AS subq_2 ; Empty set (0.01 sec) mysql> SELECT /*+ JOIN_ORDER(ra2, ra3, ra0) */ * FROM ( SELECT ra0.c2 AS ca2 FROM t1 AS ra0 ) AS ra1 , LATERAL ( SELECT * FROM t1 AS ra2 LEFT JOIN ( SELECT ra1.ca2 AS ca0 FROM t1 AS ra3 ) AS ra4 ON ( false ) WHERE ra4.ca0 IS NULL ) A S subq_2 ; +------+------+------+------+ | ca2 | c2 | c3 | ca0 | +------+------+------+------+ | 0000 | 0000 | 2001 | NULL | +------+------+------+------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT * FROM ( SELECT ra0.c2 AS ca2 FROM t1 AS ra0 ) AS ra1 , LATERAL ( SELECT * FROM t1 AS ra2 LEFT JOIN ( SELECT ra1.ca2 AS ca0 FROM t1 AS ra3 ) AS ra4 ON ( false ) WHERE ra4.ca0 IS NULL ) AS subq_2 ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | ra0 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | ra2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using join buffer (hash join) | | 1 | SIMPLE | ra3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 3 rows in set, 2 warnings (0.00 sec) mysql> EXPLAIN SELECT /*+ JOIN_ORDER(ra2, ra3, ra0) */ * FROM ( SELECT ra0.c2 AS ca2 FROM t1 AS ra0 ) AS ra1 , LATERAL ( SELECT * FROM t1 AS ra2 LEFT JOIN ( SELECT ra1.ca2 AS ca0 FROM t1 AS ra3 ) AS ra4 ON ( false ) WHERE ra4.ca0 IS NULL +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | ra2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | ra3 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | | 1 | SIMPLE | ra0 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 3 rows in set, 2 warnings (0.01 sec) ```
[3 Nov 2023 12:24]
MySQL Verification Team
Hi Mr. Ke, Thank you for your additional info. It has been copied to our internal bugs database.
[13 Dec 2023 11:37]
shan he
Could you please confirm whether the simplification process we applied has been helpful for developers in diagnosing and addressing this issue? Although not obvious for this example, it removes " WHERE false". Here's our output ***** CREATE TABLE t0 ( c0 NUMERIC ( 1 ) NOT NULL , c1 VARCHAR ( 16 ) NOT NULL) ; INSERT IGNORE INTO t0 (c1) VALUES (1); SELECT ra1.c1 FROM t0 ra1 WHERE ra1.c1 IN ( SELECT CAST( ra4.c1 AS SIGNED ) FROM t0 ra2 LEFT JOIN ( SELECT ra1.c1 FROM t0 ra3 ) ra4 ON false ) ; +----+ | c1 | +----+ | 1 | +----+ SELECT /*+ JOIN_ORDER(ra2, ra3, ra1) */ ra1.c1 FROM t0 ra1 WHERE ra1.c1 IN ( SELECT CAST( ra4.c1 AS SIGNED ) FROM t0 ra2 LEFT JOIN ( SELECT ra1.c1 FROM t0 ra3 ) ra4 ON false ) ; Empty set (0.00 sec)
[13 Dec 2023 11:53]
MySQL Verification Team
Hi Mr. Wang, We have copied your comments and questions to our internal bug repository. We do not know when your report will be processed. Each Development team has its own schedule and it is changed on weekly basis. Hence, we do not know when we shall have any news on this bug report.
[13 Dec 2023 12:00]
shan he
Thanks！I just want to know as soon as possible if my tool can help developers diagnose bugs.
