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

Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'

    XMLWordPrintable

    Details

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a YEAR);
      INSERT INTO t1 VALUES (2010),(2011);
      SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1;

      returns

      +------+
      | cond |
      +------+
      |    0 |
      |    0 |
      +------+

      Now if I put the same expression into WHERE:

      SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';

      it erroneously returns one row:

      +------+
      | a    |
      +------+
      | 2010 |
      +------+

      The expected result is to have the expression evaluate into the same result in the SELECT list and in WHERE.

      The problem is that Item_func_nullif::const_item() returns true and its val_real() is called from eval_const_cond().
      In fact, it has constant items in args[0] and args[1]:

      (gdb) p args[0]->const_item()
      $14 = true
      (gdb) p args[1]->const_item()
      $15 = true

      but the returned value referenced by m_args0_copy is not a constant item:

      (gdb) p this->m_args0_copy
      $17 = (Item_field *) 0x7fff98001668

      The return argument should probably be stored in arg[2] instead, so the standard Item_func methods can see it and update Used_tables_cache taking into account the return value (not only the compared values).

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: