Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL)
Description
This script returns 2:
SET sql_mode=ORACLE; |
SELECT DECODE(NULL,NULL,1,2) AS a FROM DUAL; |
+------+
|
| a |
|
+------+
|
| 2 |
|
+------+
|
DECODE is internally implemented using Item_func_case, which treats two NULL values as not equal, so therefore the default value 2 is returned.
Oracle database works differently:
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.
The above query returns 1 in Oracle:
SELECT DECODE(NULL,NULL,1,2) AS a FROM DUAL; |
A
|
----------
|
1
|
This script demonstrates the same problem, now using a table column instead of an explicit NULL literal:
SET sql_mode=ORACLE; |
DROP TABLE t1; |
CREATE TABLE t1 (a VARCHAR(10)); |
INSERT INTO t1 VALUES (NULL); |
SELECT a, DECODE(a,NULL,1,2) AS da FROM t1; |
MariaDB returns:
+------+------+
|
| a | da |
|
+------+------+
|
| NULL | 2 |
|
+------+------+
|
Oracle database returns:
A DA
|
---------- ----------
|
1
|
DECODE should be fixed to treat two NULL values as equivalent.
Attachments
Issue Links
- is blocked by
-
MDEV-13864 Change Item_func_case to store the predicant in args[0]
- Closed
- relates to
-
MDEV-10342 Providing compatibility for basic SQL built-in functions
- Closed