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

MIN/MAX optimizer doesn't take into account type conversions

Details

    Description

      MIN/MAX optimizer doesn't take into account type conversions. This may cause query results to be different depending on whether there is an index.

      Testcase (not minimal, it may be possible to simplify further):

      create table t2 ( a char(10) not null, key(a)) engine=innodb;
      		 
       
      		 
      insert into t2 values ('foo-123');
      		 
      insert into t2 values ('bar-123');
      		 
      insert into t2 values ('baz-123');
      		 
      insert into t2 values ('abc-123');
      		 
      insert into t2 values ('-1234');
      		 
      insert into t2 values ('-99');
      		 
      insert into t2 values ('-99999');
      		 
       
      		 
      select max(a) from t2 where a < 432;
      		 
      select max(a) from t2 ignore index(a) where a < 432;
      		 

      MariaDB [j3]> select max(a) from t2 where a < 432;
      		 
      +--------+
      		 
      | max(a) |
      		 
      +--------+
      		 
      | -99999 |
      		 
      +--------+
      		 
      1 row in set (0.00 sec)
      		 

      MariaDB [j3]> select max(a) from t2 ignore index(a) where a < 432;
      		 
      +---------+
      		 
      | max(a)  |
      		 
      +---------+
      		 
      | foo-123 |
      		 
      +---------+
      		 
      1 row in set, 4 warnings (0.01 sec)

      EXPLAINs:

      MariaDB [j3]> explain select max(a) from t2 where a < 432;
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      		 
      |    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [j3]> explain select max(a) from t2 ignore index(a) where a < 432;
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      		 
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      		 
      |    1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
      		 
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      		 
      1 row in set (0.01 sec)

      Filed in the upstream as http://bugs.mysql.com/bug.php?id=70886

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia created issue -
          psergei Sergei Petrunia made changes -
          Field Original Value New Value
          Priority Major [ 3 ] Minor [ 4 ]
          psergei Sergei Petrunia added a comment -

          Setting priority to MINOR as this problem exists for a long time and didn't affect anybody.

          psergei Sergei Petrunia added a comment - Setting priority to MINOR as this problem exists for a long time and didn't affect anybody.
          psergei Sergei Petrunia made changes -
          Description MIN/MAX optimizer doesn't take into account type conversions. This may cause query results to be different depending on whether there is an index.

          Testcase (not minimal, it may be possible to simplify further):
          {noformat}
          create table t2 ( a char(10) not null, key(a)) engine=innodb;

          insert into t2 values ('foo-123');
          insert into t2 values ('bar-123');
          insert into t2 values ('baz-123');
          insert into t2 values ('abc-123');
          insert into t2 values ('-1234');
          insert into t2 values ('-99');
          insert into t2 values ('-99999');

          select max(a) from t2 where a < 432;
          select max(a) from t2 ignore index(a) where a < 432;
          {noformat}


          {noformat}
          MariaDB [j3]> select max(a) from t2 where a < 432;
          +--------+
          | max(a) |
          +--------+
          | -99999 |
          +--------+
          1 row in set (0.00 sec)
          {noformat}

          {noformat}
          MariaDB [j3]> select max(a) from t2 ignore index(a) where a < 432;
          +---------+
          | max(a) |
          +---------+
          | foo-123 |
          +---------+
          1 row in set, 4 warnings (0.01 sec)
          {noformat}

          EXPLAINs:
          {noformat}
          MariaDB [j3]> explain select max(a) from t2 where a < 432;
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          1 row in set (0.00 sec)

          MariaDB [j3]> explain select max(a) from t2 ignore index(a) where a < 432;
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          1 row in set (0.01 sec)
          {noformat}           		MIN/MAX optimizer doesn't take into account type conversions. This may cause query results to be different depending on whether there is an index.

          Testcase (not minimal, it may be possible to simplify further):
          {noformat}
          create table t2 ( a char(10) not null, key(a)) engine=innodb;

          insert into t2 values ('foo-123');
          insert into t2 values ('bar-123');
          insert into t2 values ('baz-123');
          insert into t2 values ('abc-123');
          insert into t2 values ('-1234');
          insert into t2 values ('-99');
          insert into t2 values ('-99999');

          select max(a) from t2 where a < 432;
          select max(a) from t2 ignore index(a) where a < 432;
          {noformat}


          {noformat}
          MariaDB [j3]> select max(a) from t2 where a < 432;
          +--------+
          | max(a) |
          +--------+
          | -99999 |
          +--------+
          1 row in set (0.00 sec)
          {noformat}

          {noformat}
          MariaDB [j3]> select max(a) from t2 ignore index(a) where a < 432;
          +---------+
          | max(a) |
          +---------+
          | foo-123 |
          +---------+
          1 row in set, 4 warnings (0.01 sec)
          {noformat}

          EXPLAINs:
          {noformat}
          MariaDB [j3]> explain select max(a) from t2 where a < 432;
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          1 row in set (0.00 sec)

          MariaDB [j3]> explain select max(a) from t2 ignore index(a) where a < 432;
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
          +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
          1 row in set (0.01 sec)
          {noformat}

          Filed in the upstream as http://bugs.mysql.com/bug.php?id=70886
          psergei Sergei Petrunia made changes -
          Labels upstream
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 29711 ] MariaDB v2 [ 44594 ]
          elenst Elena Stepanova made changes -
          elenst Elena Stepanova made changes -
          Component/s Optimizer [ 10200 ]
          Fix Version/s 10.0 [ 16000 ]
          Affects Version/s 10.0 [ 16000 ]
          Affects Version/s 5.5 [ 15800 ]
          Affects Version/s 5.5.33a [ 13500 ]
          elenst Elena Stepanova made changes -
          Labels upstream upstream verified
          elenst Elena Stepanova made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.0 [ 16000 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44594 ] MariaDB v3 [ 61260 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 61260 ] MariaDB v4 [ 139559 ]

          People

            Unassigned Unassigned
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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