-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 5.5.40, 10.0.14
-
Fix Version/s: 10.1.2
-
Component/s: Temporal Types
-
Labels:None
The manual at https://mariadb.com/kb/en/mariadb/documentation/functions-and-operators/control-flow-functions/nullif/ says that NULLIF(expr1,expr2) is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
In fact it is not always true.
DROP TABLE IF EXISTS t1,t2; |
CREATE TABLE t1 (a TIME); |
CREATE TABLE t2 AS SELECT a,NULLIF(a,a), CASE WHEN a=a THEN NULL ELSE a END FROM t1; |
SHOW CREATE TABLE t2; |
returns:
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`a` time DEFAULT NULL,
|
`NULLIF(a,a)` varchar(10) DEFAULT NULL,
|
`CASE WHEN a=a THEN NULL ELSE a END` time DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, NULLIF erroneously created a VARCHAR column, while CASE correctly created a TIME column.