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

NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0
        • Fix Version/s: 10.1.4
        • Component/s: Optimizer, Temporal Types
        • Labels:
          None

          Description

          Found this bug while working on MDEV-6990 and MDEV-6989.

          Item_func_ifnull::fix_length_and_dec() has this code:

           m_args0_copy->cmp_context= args[1]->cmp_context=
   item_cmp_type(m_args0_copy->result_type(), args[1]->result_type());

          It is pointless to set cmp_context for m_args0_copy, because it does not take part it any comparison. It's only used to return a value.
          It should be args[0]->cmp_context set instead.

          Because args[0]->cmp_context is not set there is a difference in:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a YEAR);
INSERT INTO t1 VALUES (2010),(2020);
SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL;
SHOW WARNINGS;

          and

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a YEAR);
INSERT INTO t1 VALUES (2010),(2020);
SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL;
SHOW WARNINGS;

          The first script returns:

          +-------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                  |
+-------+------+--------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and isnull(nullif(2010,`test`.`t1`.`a`))) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------+

          The second script returns:

          +-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message                                                                               |
+-------+------+---------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) |
+-------+------+---------------------------------------------------------------------------------------+

          In the first example with IFNULL, can_change_cond_ref_to_const() fails to optimize the condition.
          In the second example with CASE, can_change_cond_ref_to_const() correctly optimizes away the second part of the condition.

          Additionally, the code in Item_func_ifnull::fix_length_and_dec() and in Item_bool_func2::fix_length_and_dec() is not identical in more pieces, which exposes in this behaviour difference.

          This script:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a;

          returns no warnings.

          While this one:

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 AS SELECT CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL ELSE TIMESTAMP'2001-01-01 00:00:00' END AS a;

          correctly returns a warning:

          mysql> SHOW WARNINGS;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1292 | Incorrect datetime value: '1' |
+---------+------+-------------------------------+

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

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

                    Dates

                    • Created:
                      Updated:
                      Resolved: