| Bug #119203 | An expression that returns NULL instead of NULL is used to perform the <=> operation. the sql retrun wrong result. | ||
|---|---|---|---|
| Submitted: | 21 Oct 2025 12:46 | Modified: | 4 Nov 2025 23:25 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Oct 2025 1:38]
Alice Alice
there are something wrong with the Optimizer.
mysql> explain analyze SELECT t0.c0, t0.c1, t0.c4, t0.c2 FROM t0 WHERE ((t0.c1)<=>((('A')<<(NULL))));
+---------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------+
| -> Rows fetched before execution (cost=0..0 rows=1) (actual time=108e-6..170e-6 rows=1 loops=1)
|
+---------------------------------------------------------------------------------------------------+
[4 Nov 2025 23:25]
Roy Lyseng
Verified as described.
Description: An expression that returns NULL instead of NULL is used to perform the <=> operation. the sql retrun wrong result. How to repeat: DROP TABLE IF EXISTS `t0`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t0` ( `c0` decimal(10,0) NOT NULL, `c1` tinyint(1) DEFAULT NULL, `c2` char(1) NOT NULL, `c4` varchar(367) DEFAULT NULL, UNIQUE KEY `c0` (`c0`), UNIQUE KEY `c1` (`c1`), KEY `i0` (`c1`,`c2`,`c0`,`c4`(38) DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; LOCK TABLES `t0` WRITE; /*!40000 ALTER TABLE `t0` DISABLE KEYS */; INSERT INTO `t0` VALUES (1796216920,0,'F','1385222045'),(-228977102,1,'-','xCv_'),(1376810223,NULL,'<',NULL),(1431064911,NULL,'^','>UkuM字'),(-2134605314,NULL,'b','17949495'),(-550554581,NULL,'7','1879972024'); /*!40000 ALTER TABLE `t0` ENABLE KEYS */; UNLOCK TABLES; mysql> SELECT t0.c0, t0.c1, t0.c4, t0.c2 FROM t0; +-------------+------+------------+----+ | c0 | c1 | c4 | c2 | +-------------+------+------------+----+ | -2134605314 | NULL | 17949495 | b | | -550554581 | NULL | 1879972024 | 7 | | -228977102 | 1 | xCv_ | - | | 1376810223 | NULL | NULL | < | | 1431064911 | NULL | >UkuM字 | ^ | | 1796216920 | 0 | 1385222045 | F | +-------------+------+------------+----+ 6 rows in set (0.00 sec) mysql> SELECT t0.c0, t0.c1, t0.c4, t0.c2 FROM t0 WHERE ((t0.c1)<=>NULL); +-------------+------+------------+----+ | c0 | c1 | c4 | c2 | +-------------+------+------------+----+ | -2134605314 | NULL | 17949495 | b | | -550554581 | NULL | 1879972024 | 7 | | 1376810223 | NULL | NULL | < | | 1431064911 | NULL | >UkuM字 | ^ | +-------------+------+------------+----+ 4 rows in set (0.00 sec) mysql> select ((('A')<<(NULL))); +-------------------+ | ((('A')<<(NULL))) | +-------------------+ | NULL | +-------------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT t0.c0, t0.c1, t0.c4, t0.c2 FROM t0 WHERE ((t0.c1)<=>((('A')<<(NULL)))); +-------------+------+----------+----+ | c0 | c1 | c4 | c2 | +-------------+------+----------+----+ | -2134605314 | NULL | 17949495 | b | +-------------+------+----------+----+ 1 row in set, 2 warnings (0.00 sec)