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

    XMLWordPrintable

Details

    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' |
      +---------+------+-------------------------------+

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.