Bug #119059 Inconsistent result behavior when using EXCEPT equivalent NATRUAL JOIN

• Submitted: 23 Sep 3:44
• Modified: 23 Sep 17:04
• Reporter: 策 吕
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.2.0, 9.4.0
• OS: Any
• CPU Architecture: Any

[23 Sep 3:44] 策 吕

Description:
When I use the second SELECT statement with the EXCEPT keyword to equate the first SELECT statement, it returns inconsistent results!!!

I'm using MariaDB(10.11.11-MariaDB-ubu2204) both SELECT statements return empty set.

mysql> SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL JOIN t1;
Empty set (0.00 sec)

mysql> (SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
    -> EXCEPT
    -> (
    ->     (SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
    ->     EXCEPT
    ->     (SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL RIGHT JOIN t1)
    -> );
+--------------------+
| ref0               |
+--------------------+
| 0.3565946270598622 |
+--------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.2.0     |
+-----------+
1 row in set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS database12;
CREATE DATABASE database12;
USE database12;
CREATE TABLE IF NOT EXISTS t0(c0 DOUBLE ZEROFILL NOT NULL) ;
CREATE TABLE t1(c0 LONGTEXT  NOT NULL) ;

REPLACE INTO t0(c0) VALUES(0.3565946270598622);

INSERT IGNORE INTO t1(c0) VALUES(497982460);

-- select * from t0;
-- select * from t1;
SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL JOIN t1;
(SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
EXCEPT
((SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
EXCEPT
(SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL RIGHT JOIN t1));

[23 Sep 17:04] MySQL Verification Team

Thank you for the report and a test case

mysql> SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL JOIN t1;
Empty set (0.000 sec)

mysql> (SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
    -> EXCEPT
    -> ((SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL LEFT JOIN t1)
    -> EXCEPT
    -> (SELECT DISTINCTROW t0.c0 AS ref0 FROM t0 NATURAL RIGHT JOIN t1));
+--------------------+
| ref0               |
+--------------------+
| 0.3565946270598622 |
+--------------------+
1 row in set (0.002 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.000 sec)

mysql>