| Bug #109311 | Wrong result | ||
|---|---|---|---|
| Submitted: | 8 Dec 2022 3:40 | Modified: | 9 Dec 2022 3:36 |
| Reporter: | Xiong Wang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.0.22 8.0.30, 8.0.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[8 Dec 2022 6:52]
MySQL Verification Team
Hello Xiong Wang, Thank you for the report and feedback. regards, Umesh
[9 Dec 2022 3:36]
Xiong Wang
For semijoin, the result set is incorrect too.
mysql> explain select 1 as a from (select datetime_col as c1 from t1 as ref_1) as subq_0 where EXISTS ( select 1 as cx from t as ref_6 left join (select subq_0.c1 as c0 from t as ref_7 ) as subq_1 on (false) where subq_1.c0 is NULL);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | ref_1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | ref_6 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | ref_7 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(ref_1) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
3 rows in set, 2 warnings (0.00 sec)
mysql> explain format=tree select 1 as a from (select datetime_col as c1 from t1 as reff_1 ) as subq_0 where EXISTS ( select 1 as cx from t as ref_6 left join (select sub
bq_0.c1 as c0 from t as ref_7 ) as subq_1 on (false) where subq_1.c0 is NULL);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop semijoin (cost=0.70 rows=1)
-> Filter: (ref_1.datetime_col is null) (cost=0.35 rows=1)
-> Table scan on ref_1 (cost=0.35 rows=1)
-> Nested loop left join (cost=0.70 rows=1)
-> Table scan on ref_6 (cost=0.35 rows=1)
-> Zero rows (Impossible filter) (cost=0.35..0.35 rows=1)
|
mysql> select 1 as a from (select datetime_col as c1 from t1 as ref_1 ) as subq_0 where EXISTS ( select 1 as cx from t as ref_6 left join (select subq_0.c1 as c0
from t as ref_7 ) as subq_1 on (false) where subq_1.c0 is NULL);
Empty set (0.00 sec) // One row should be returned.
I considered the following transmission is incorrect. "Filter: (ref_1.datetime_col is null) " should not be attached to ref_1when convert to semijoin.
-> Filter: (ref_1.datetime_col is null) (cost=0.35 rows=1)
-> Table scan on ref_1 (cost=0.35 rows=1)
Description: mysql> CREATE TABLE t1(datetime_col datetime(6)); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t1 -> VALUES('1998-09-02 13:20:00.000000'); Query OK, 1 row affected (0.01 sec) mysql> CREATE TABLE t (a char(4), -> b char(4)); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO t -> VALUES(NULL, NULL); Query OK, 1 row affected (0.01 sec) mysql> mysql> SELECT 1 AS a -> FROM -> (SELECT datetime_col AS c1 -> FROM t1 AS ref_1) AS subq_0, -> LATERAL -> (SELECT /*+no_bnl()*/ 1 AS cxx -> FROM t AS ref_6 -> LEFT JOIN -> (SELECT subq_0.c1 AS c0 -> FROM t AS ref_7) AS subq_1 ON (FALSE) -> WHERE subq_1.c0 IS NULL) AS subq_2; Empty set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 8.0.30-debug | +--------------+ 1 row in set (0.00 sec) How to repeat: CREATE TABLE t1(datetime_col datetime(6)); INSERT INTO t1 VALUES('1998-09-02 13:20:00.000000'); CREATE TABLE t (a char(4), b char(4)); INSERT INTO t VALUES(NULL, NULL); SELECT 1 AS a FROM (SELECT datetime_col AS c1 FROM t1 AS ref_1) AS subq_0, LATERAL (SELECT /*+no_bnl()*/ 1 AS cxx FROM t AS ref_6 LEFT JOIN (SELECT subq_0.c1 AS c0 FROM t AS ref_7) AS subq_1 ON (FALSE) WHERE subq_1.c0 IS NULL) AS subq_2; Suggested fix: Condition 'subq_1.c0 IS NULL' has a side effect on query result set.