MySQL Bugs: #119032: Inconsistency with MySQL 8.0.36 in MySQL 9.2.0: Equivalent INTERSECT query does not match INNER JOIN result and returns

Bug #119032	Inconsistency with MySQL 8.0.36 in MySQL 9.2.0: Equivalent INTERSECT query does not match INNER JOIN result and returns
Submitted:	18 Sep 7:52	Modified:	19 Sep 15:49
Reporter:	策吕	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	9.2.0	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Description:
Newer versions of MySQL exhibit logically inconsistent behavior when the ON clause of a JOIN contains a non-standard condition, such as using LIKE for Boolean values.
A simple INNER JOIN query correctly returns the empty set, but a logically equivalent query using LEFT JOIN and RIGHT JOIN combined with INTERSECT incorrectly returns a row of data.
This behavior is not only inconsistent with older versions and other compatible databases (TiDB), but more importantly, it violates the logical consistency of the database itself.
------------------------------------------------------------------------------
mysql> SELECT DISTINCT t0.c0 FROM  t0 INNER JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))));
Empty set, 34 warnings (0.00 sec)

mysql> (SELECT DISTINCT t0.c0 FROM  t0 LEFT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))))) INTERSECT (SELECT DISTINCT t0.c0 FROM  t0 RIGHT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0)))));
+------------+
| c0         |
+------------+
| 1470820000 |
+------------+
1 row in set, 105 warnings (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.2.0     |
+-----------+
1 row in set (0.00 sec)
------------------------------------------------------------------------------
mysql> SELECT DISTINCT t0.c0 FROM  t0 INNER JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))));
Empty set, 34 warnings (0.00 sec)

mysql> (SELECT DISTINCT t0.c0 FROM  t0 LEFT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))))) INTERSECT (SELECT DISTINCT t0.c0 FROM  t0 RIGHT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0)))));
Empty set

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

------------------------------------------------------------------------------
mysql> SELECT DISTINCT t0.c0 FROM  t0 INNER JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))));
Empty set, 34 warnings (0.01 sec)

mysql> (SELECT DISTINCT t0.c0 FROM  t0 LEFT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))))) INTERSECT (SELECT DISTINCT t0.c0 FROM  t0 RIGHT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0)))));
Empty set, 3 warnings (0.01 sec)

mysql> select version();
+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 8.0.11-TiDB-v9.0.0-beta.2.pre-193-g4852c06 |
+--------------------------------------------+
1 row in set (0.00 sec)

------------------------------------------------------------------------------

------------------------------------------------------------------------------

How to repeat:
DROP DATABASE IF EXISTS database19;
CREATE DATABASE database19;
USE database19;

CREATE TABLE t0(c0 FLOAT UNSIGNED ZEROFILL NOT NULL DEFAULT 0.2769381711867116 PRIMARY KEY );

INSERT INTO t0 VALUES (0.1597161269174473);

UPDATE t0 SET c0=1059597701;
INSERT IGNORE  INTO t0 VALUES (-1.569657336E9);
INSERT IGNORE  INTO t0 VALUES (-1.857696944E9);

UPDATE t0 SET c0=0.3725581590190179 WHERE t0.c0;
INSERT IGNORE  INTO t0(c0) VALUES (0.384522140468033) ON DUPLICATE KEY UPDATE c0=CRC32(((true)OR(CAST(DEFAULT(t0.c0) AS BINARY))));
REPLACE INTO t0 VALUES (0.3496443675431833);

REPLACE INTO t0(c0) VALUES (0.29093115225579247);

INSERT INTO t0(c0) VALUES (0.970217544041955) ON DUPLICATE KEY UPDATE c0=NULL;
INSERT INTO t0(c0) VALUES (0.8437727278336055) ON DUPLICATE KEY UPDATE c0=-1236734732;

INSERT IGNORE  INTO t0 VALUES (0.7657048912258787) ON DUPLICATE KEY UPDATE c0=DEFAULT(t0.c0);

REPLACE INTO t0 VALUES (0.9781488209063205);

REPLACE INTO t0(c0) VALUES (0.9852368398662722), (0.2278805620301464);
CREATE ALGORITHM=MERGE VIEW v0(c0) AS SELECT DISTINCT (- (RIGHT(DEFAULT(t0.c0), t0.c0))) FROM t0;
REPLACE INTO t0(c0) VALUES (0.9273847301637799);
INSERT IGNORE  INTO t0 VALUES (0.9945300209704869), (0.38186465071649467) ON DUPLICATE KEY UPDATE c0=t0.c0;
REPLACE INTO t0(c0) VALUES (7.48428534E8);
INSERT IGNORE  INTO t0 VALUES (0.6592656160523921);
REPLACE INTO t0 VALUES (0.34374077241187473);
REPLACE INTO t0 VALUES (0.7075188457588408), (0.0);
INSERT IGNORE  INTO t0 VALUES (4.01819261E8) ON DUPLICATE KEY UPDATE c0=(CASE t0.c0 WHEN (((~ (0.9726198035507718))) IS NOT NULL) THEN t0.c0 ELSE '-1526197707' END );
INSERT INTO t0 VALUES (0.4949227486159432), (0.9585106879243355);
INSERT INTO t0 VALUES (0.35600122885366436);
INSERT INTO t0(c0) VALUES (0.966793602178512);
INSERT INTO t0(c0) VALUES (1.470819248E9);

REPLACE INTO t0(c0) VALUES (0.18812104576562083);

UPDATE t0 SET c0=t0.c0;

INSERT INTO t0 VALUES (0.06921909648068136) ON DUPLICATE KEY UPDATE c0=((((t0.c0)REGEXP(IS_IPV4(USER()))))^((((CASE 673136133 WHEN -514369273 THEN NULL ELSE t0.c0 END )) IS NULL)));
# CREATE INDEX i71 ON t0(c0 DESC);
INSERT IGNORE  INTO t0 VALUES (0.5187245790422262);
REPLACE INTO t0 VALUES (0.4062996657869269), (0.7368088692988315);

UPDATE t0 SET c0=858044288 WHERE NULL;

INSERT INTO t0 VALUES (0.6126066551648557), (0.2568208410396676) ON DUPLICATE KEY UPDATE c0=((NULL)AND(t0.c0));
INSERT IGNORE  INTO t0 VALUES (0.5837796270366051);
INSERT INTO t0 VALUES (0.241895117334178) ON DUPLICATE KEY UPDATE c0=((DEFAULT(t0.c0))REGEXP(t0.c0));
INSERT INTO t0(c0) VALUES (0.6513117325873932);

-- cardinality: 0
SELECT DISTINCT t0.c0 FROM  t0 INNER JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))));
-- cardinality: 1
(SELECT DISTINCT t0.c0 FROM  t0 LEFT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0))))) INTERSECT (SELECT DISTINCT t0.c0 FROM  t0 RIGHT JOIN v0 ON ((((true)<(137271884)))NOT LIKE(((CAST(v0.c0 AS DATETIME))&(v0.c0)))));