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

Equal field propagation is not used for VARCHAR when it safely could

    Details

      Description

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (10),(11),(12);
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND IF(a=10,1,0)=1;
      SHOW WARNINGS;

      returns

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

      I.e. the field equal value of 10 was propagated into IF, then the condition part with IF was evaluated as a constant and removed from the condition.

      If I use VARCHAR in the same scenario:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a VARCHAR(10));
      INSERT INTO t1 VALUES ('10'),('11'),('12');
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='10' AND IF(a='10',1,0)=1;
      SHOW WARNINGS;

      it returns:

      +-------+------+----------------------------------------------------------------------------------------------------------------------------+
      | Level | Code | Message                                                                                                                    |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------+
      | Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '10') and (if((`test`.`t1`.`a` = '10'),1,0) = 1)) |
      +-------+------+----------------------------------------------------------------------------------------------------------------------------+

      Propagation did not work.

      Tracing in gdb shows the following:

      (gdb) b Item_field::subst_argument_checker(unsigned char**) 
       
      Breakpoint 3 at 0x555555c46d98: file /home/bar/maria-git/server/sql/item.cc, line 5361.
      (gdb) c
      Continuing.
       
      Breakpoint 3, Item_field::subst_argument_checker (this=0x7fff9c007f70, 
          arg=0x7fffd9f04a00) at /home/bar/maria-git/server/sql/item.cc:5361
      5361	  return *arg &&
      (gdb) p (Subst_constraint) *arg
      $3 = Item::IDENTITY_SUBST

      I.e. Item_field::subst_argument_checker() is called with a wrong Subst_constaint value.
      The correct value should be Item::ANY_SUBST, because the field appears in a comparison context.

        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: