| Bug #118949 | MySQL 9.2.0: Complex CASE in JOIN makes INNER vs INTERSECT yield different rows | ||
|---|---|---|---|
| Submitted: | 5 Sep 13:57 | Modified: | 8 Sep 6:49 |
| Reporter: | 策 吕 | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 9.2.0 | OS: | Linux (ubuntu 20.04) |
| Assigned to: | CPU Architecture: | Any | |
[5 Sep 19:45]
Filipe Silva
Hi 策 吕, Thank you for the report. Could you please provide clearer, more descriptive and accurate bug title (synopsis) in the bugs you report? Thank you—we appreciate your help improving the quality of our reports.
[6 Sep 12:20]
策 吕
Hi developers,
Title now shortened to:
MySQL 9.2.0: Complex CASE in JOIN makes INNER vs INTERSECT yield different rows
Here are my findings in more detail:
First of all I found this result on MySQL version 9.2.0 when using the test sample above:
mysql> SELECT DISTINCT t70.c0 FROM t70 INNER JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END );
+----+
| c0 |
+----+
| 0 |
+----+
mysql> (SELECT DISTINCT t70.c0 FROM t70 LEFT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END )) INTERSECT (SELECT DISTINCT t70.c0 FROM t70 RIGHT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END ));
+----+
| c0 |
+----+
| 0 |
| 1 |
+----+
Obviously, in my design, the results returned by the first query should be consistent with the results returned by the second query, with this in mind I ran the same test sample on MariaDB again, and found that MariaDB returned consistent results, ie:
mariadb> SELECT DISTINCT t70.c0 FROM t70 INNER JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END );
+----+
| c0 |
+----+
| 0 |
+----+
mariadb> (SELECT DISTINCT t70.c0 FROM t70 LEFT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END )) INTERSECT (SELECT DISTINCT t70.c0 FROM t70 RIGHT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END ));
+----+
| c0 |
+----+
| 0 |
+----+
What's more，I tested it on MySQL version 8.0.36 (windows) and found that it is also all 0, which is consistent with the performance of MariaDB's results, and I also tested it on the latest version of TiDB and also found that the final results are all 0.
[8 Sep 6:49]
MySQL Verification Team
Thanks for the report. Verified as described.
Description: When I inserted some data, I am getting inconsistent results when I do a logical equivalent query and it is thought outside that this is a bug in mysql because outside the equivalent query on mariadb and tidb shows the same results. mysql> SELECT DISTINCT t70.c0 FROM t70 INNER JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END ); +----+ | c0 | +----+ | 0 | +----+ 1 row in set, 2 warnings (0.00 sec) mysql> (SELECT DISTINCT t70.c0 FROM t70 LEFT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END )) INTERSECT (SELECT DISTINCT t70.c0 FROM t70 RIGHT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END )); +----+ | c0 | +----+ | 0 | | 1 | +----+ 2 rows in set, 4 warnings (0.01 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec) --------------------------------------------------------------- Mariadb version() : MariaDB [database12]> select version(); +--------------------------+ | version() | +--------------------------+ | 10.11.11-MariaDB-ubu2204 | +--------------------------+ 1 row in set (0.000 sec) Tidb version(): 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 database12; CREATE DATABASE database12; USE database12; CREATE TABLE t0(c0 BOOL NOT NULL DEFAULT false , PRIMARY KEY(c0)); CREATE TABLE t70 LIKE t0; INSERT IGNORE INTO t70 VALUES (false), (false), (true), (true); INSERT IGNORE INTO t0(c0) VALUES (false) ON DUPLICATE KEY UPDATE c0=402420952; REPLACE INTO t70 VALUES (false); REPLACE INTO t0(c0) VALUES (false); DELETE FROM t0 WHERE false ORDER BY ((t0.c0)>>(t0.c0)) DESC LIMIT 462188807; REPLACE INTO t0(c0) VALUES (true); UPDATE t0 SET c0=t0.c0 WHERE (((CASE -1012993276 WHEN t0.c0 THEN NULL ELSE (CASE true WHEN 647261483 THEN '' ELSE true END ) END ))REGEXP(t0.c0)); INSERT IGNORE INTO t70(c0) VALUES (true), (true); INSERT IGNORE INTO t0 VALUES (NULL), (true) ON DUPLICATE KEY UPDATE c0=t0.c0; REPLACE INTO t0 VALUES (false); UPDATE t70 SET c0='-437186391' WHERE false; REPLACE INTO t70(c0) VALUES (false); REPLACE INTO t0(c0) VALUES (false); REPLACE INTO t70(c0) VALUES (true); REPLACE INTO t0 VALUES (false); ALTER TABLE t70 DISABLE KEYS; REPLACE INTO t70 VALUES (false); CREATE INDEX i14 ON t70(c0 ASC); REPLACE INTO t70 VALUES (true); REPLACE INTO t70 VALUES (false); REPLACE INTO t70 VALUES (true); ALTER TABLE t0 ENABLE KEYS; ALTER TABLE t70 ORDER BY c0 ASC; REPLACE INTO t0(c0) VALUES (true); REPLACE INTO t70(c0) VALUES (false); REPLACE INTO t0 VALUES (true); REPLACE INTO t70 VALUES (false); -- cardinality: 2 SELECT DISTINCT t70.c0 FROM t70 INNER JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END ); -- cardinality: 1 (SELECT DISTINCT t70.c0 FROM t70 LEFT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END )) INTERSECT (SELECT DISTINCT t70.c0 FROM t70 RIGHT JOIN t0 ON (CASE t0.c0 WHEN (((BINARY (false)))NOT LIKE(RADIANS(979536240))) THEN BIN(((((t70.c0)|(t70.c0)))<=(''))) WHEN CAST('(S/]Cf\nI|' AS DATE) THEN t0.c0 ELSE ((((-1.93103105E8)<(t70.c0)))<(BIN(t70.c0))) END ));