Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13863

sql_mode=ORACLE: DECODE does not treat two NULLs as equivalent

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration