| Bug #118217 | Issues with the NULLIF function. | ||
|---|---|---|---|
| Submitted: | 16 May 16:11 | Modified: | 26 May 10:17 |
| Reporter: | QiFan Liu | Email Updates: | |
| Status: | Analyzing | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.4.5 | OS: | Ubuntu |
| Assigned to: | MySQL Verification Team | CPU Architecture: | x86 |
[26 May 8:11]
MySQL Verification Team
Hello QiFan Liu, Thank you for the bug report. Could you please provide error message caused by NULLIF()? Regards, Ashwini Patil
[26 May 10:14]
QiFan Liu
Thank you for your reminder!
I just checked it once and found that the SQL file information I submitted was incorrect. The correct SQL file structure is as follows:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `t0`
--
DROP TABLE IF EXISTS `t0`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t0` (
`c0` float /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf',
UNIQUE KEY `c0` (`c0`),
UNIQUE KEY `i0` (`c0`) /*!80000 INVISIBLE */,
KEY `i1` (`c0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=REDUNDANT ;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t0`
--
LOCK TABLES `t0` WRITE;
/*!40000 ALTER TABLE `t0` DISABLE KEYS */;
INSERT INTO `t0` VALUES (NULL),(-1558910000),(0);
/*!40000 ALTER TABLE `t0` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
`c0` text COMMENT 'asdf'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES ('604022688'),('eai,'),('604022688'),(NULL),('-1803942211'),('0.04392132400214488'),(NULL),('-2100740081'),('604022688'),('1333789647'),(''),(NULL),('1333789647');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
The data in t0 is
mysql> select * from t0;
+-------------+
| c0 |
+-------------+
| NULL |
| -1558910000 |
| 0 |
+-------------+
mysql> desc t0;
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| c0 | float | YES | UNI | NULL | |
+-------+-------+------+-----+---------+-------+
example_1:
For the table structure generated by the above SQL file, when I execute sql1:
select nullif(ref_0.c0, "any text") as c1 from database0.t0 as ref_0;
The result I get is:
+-------------+
| c1 |
+-------------+
| NULL |
| -1558910000 |
| NULL |
+-------------+
However, logically, the expected result should be:
+-------------+
| c0 |
+-------------+
| NULL |
| -1558910000 |
| 0 |
+-------------+
Explanation:
For the first row (c0 = NULL), since NULL is not equal to the string "any text", the result will be NULL.
For the second row (c0 = -1558910000), since the value is not equal to "any text", the result will be -1558910000.
For the third row (c0 = 0), since 0 is not equal to "any text", the result will be 0.
Regarding the above issue, my view is that the database may implicitly convert 0 to NULL when using the NULLIF function to compare 0 with a string type value. Even so, from the execution logic of NULLIF, the return value should be 0 instead of NULL.
example_2:
In the same table, When I execute sql2:
select nullif(ref_0.c0, -1558910000) as c1 from database0.t0 as ref_0;
The result I get is:
+-------------+
| c1 |
+-------------+
| NULL |
| -1558910000 |
| 0 |
+-------------+
However, logically, the expected result should be:
+-------------+
| c0 |
+-------------+
| NULL |
| NULL |
| 0 |
+-------------+
Explanation:
For the first row (c0 = NULL), since NULL is not equal to -1558910000, the result will be NULL.
For the second row (c0 = -1558910000), since the value is equal to -1558910000, NULLIF returns NULL.
For the third row (c0 = 0), since 0 is not equal to -1558910000, the result will be 0.
example_3:
In the same table, When I execute sql3:
select nullif(ref_0.c0, coalesce(ref_0.c0, (select ref_1.c0 from database0.t1 as ref_1 limit 1))) as c1 from database0.t0 as ref_0;
The result I get is:
+-------------+
| c1 |
+-------------+
| NULL |
| -1558910000 |
| NULL |
+-------------+
However, logically, the expected result should be:
+------+
| c1 |
+------+
| NULL |
| NULL |
| NULL |
+------+
Explanation:
When ref_0. c0 is not NULL, coalesce is ref_0. c0, and thus
nullif(ref_0. c0, ref_0.c0) returns NULL.
When ref_0. c0 is NULL, coalesce takes (select c0 from database0.t1), and
then nullif(NULL, subquery value) returns also NULL.
Furthermore, sql3 can be rewritten as the following logically equivalent query(in the same table) :
SELECT CASE
WHEN c0 = CASE
WHEN c0 IS NOT NULL THEN c0
ELSE ((SELECT c0 FROM t1 limit1))
END
THEN NULL
ELSE c0
END AS c1
FROM t0;
The result of this query is
+------+
| c1 |
+------+
| NULL |
| NULL |
| NULL |
+------+
It can be found that when the nested form of the NULLIF function and the COALESCE function is replaced by the logically equivalent CASE WHEN expression, the returned result is correct.
Based on the above three examples, I think there is an issue with the NULLIF function during comparison, but I am unsure about the root cause of the problem.
Please let me know if you need any additional details or documentation.
Thank you for your attention to this matter.
Description: Query Errors Caused by NULLIF function: For the following example1, NULLIF should return all zeros, but it actually returns all NULLs. For the following example2: When ref_0.c0 is not NULL, coalesce is ref_0.c0, and thus nullif(ref_0.c0, ref_0.c0) returns NULL. When ref_0.c0 is NULL, coalesce takes (select c0 from database0.t1), and then nullif(NULL, subquery value) returns also NULL. However, the select result returns a non-null value. How to repeat: example1: mysql> select nullif(0, "any text") as c1 from database0.t0 as ref_0; +------+ | c1 | +------+ | NULL | | NULL | | NULL | +------+ 3 rows in set, 1 warning (0.01 sec) example2: mysql> select nullif(ref_0.c0, coalesce(ref_0.c0, (select * from t1 limit 1))) as c1 from database0.t0 as ref_0; +-------------+ | c1 | +-------------+ | NULL | | -1558910000 | | NULL | +-------------+ 3 rows in set (0.00 sec) Here is the content of the SQL file exported from the database: -- MySQL dump 10.13 Distrib 8.4.5, for Linux (x86_64) -- -- Host: localhost Database: database0 -- ------------------------------------------------------ -- Server version 8.4.5 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `t0` -- DROP TABLE IF EXISTS `t0`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t0` ( `c0` float /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', UNIQUE KEY `c0` (`c0`), UNIQUE KEY `i0` (`c0`) /*!80000 INVISIBLE */, KEY `i1` (`c0`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=REDUNDANT; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t0` -- LOCK TABLES `t0` WRITE; /*!40000 ALTER TABLE `t0` DISABLE KEYS */; INSERT INTO `t0` VALUES (NULL),(-1558910000),(0); /*!40000 ALTER TABLE `t0` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `t1` -- DROP TABLE IF EXISTS `t1`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t1` ( `c0` text COMMENT 'asdf' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t1` -- LOCK TABLES `t1` WRITE; /*!40000 ALTER TABLE `t1` DISABLE KEYS */; INSERT INTO `t1` VALUES ('604022688'),('eai,'),('604022688'),(NULL),('-1803942211'),('0.04392132400214488'),(NULL),('-2100740081'),('604022688'),('1333789647'),(''),(NULL),('1333789647'); /*!40000 ALTER TABLE `t1` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `t2` -- DROP TABLE IF EXISTS `t2`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t2` ( `c0` double unsigned zerofill DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_PERSISTENT=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t2` -- LOCK TABLES `t2` WRITE; /*!40000 ALTER TABLE `t2` DISABLE KEYS */; /*!40000 ALTER TABLE `t2` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `t4` -- DROP TABLE IF EXISTS `t4`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `t4` ( `c0` tinytext /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT DYNAMIC */ COMMENT 'asdf', `c1` float unsigned zerofill /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', `c2` varchar(500) /*!50606 STORAGE MEMORY */ DEFAULT NULL COMMENT 'asdf', UNIQUE KEY `c1` (`c1`), UNIQUE KEY `i0` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci PACK_KEYS=0 STATS_PERSISTENT=1 CHECKSUM=1 ROW_FORMAT=DYNAMIC; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `t4` -- LOCK TABLES `t4` WRITE; /*!40000 ALTER TABLE `t4` DISABLE KEYS */; INSERT INTO `t4` VALUES ('1320358614',NULL,'0.3396492786731846'),('0.3396492786731846',001321520000,NULL),(NULL,NULL,NULL),(NULL,NULL,'\r, J12K)l'); /*!40000 ALTER TABLE `t4` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2025-05-16 16:06:31