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

MIN/MAX aggregation over an indexed column may return wrong result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5.9, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.2.38, 10.3.29, 10.4.19, 10.5.10
    • Optimizer
    • None

    Description

      For a DB Table having a id field which is the table’s auto-incremented primary key
      SQL-query:
      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)

      gives the result:
      max(id)
      267
      , which is incorrect as id BETWEEN 267 AND 287 should be equivalent to (id >= 267 AND id <= 287 and the max id value that satisfies the condition is 287, not 267

      At the same time SQL-query
      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id >= 267 AND id <= 287)
      max(id)

      gives the correct result:
      max(id)
      287

      here is a minimal example to create DB table and reproduce the issue

      CREATE TABLE _between_bug2 (id int(13) auto_increment primary key);
      INSERT INTO _between_bug2 (id) VALUES (267);
      INSERT INTO _between_bug2 (id) VALUES (287);
      INSERT INTO _between_bug2 (id) VALUES (303);

      SELECT max(id) FROM _between_bug2 WHERE id < 303 and (id BETWEEN 267 AND 287)

      and here is an SQL fiddle to reproduce it

      Attachments

        Activity

          alice Alice Sherepa added a comment - - edited

          Thanks! Reproducible on 5.5-10.5 as described

          MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id < 288 and (id BETWEEN 267 AND 287);
          +---------+
          | max(id) |
          +---------+
          |     267 |
          +---------+
           
          MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id <= 287 and (id BETWEEN 267 AND 287);
          +---------+
          | max(id) |
          +---------+
          |     287 |
          +---------+
          

          alice Alice Sherepa added a comment - - edited Thanks! Reproducible on 5.5-10.5 as described MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id < 288 and (id BETWEEN 267 AND 287); +---------+ | max(id) | +---------+ | 267 | +---------+   MariaDB [test]> SELECT max(id) FROM _between_bug2 WHERE id <= 287 and (id BETWEEN 267 AND 287); +---------+ | max(id) | +---------+ | 287 | +---------+
          igor Igor Babaev added a comment -

          The problem can be reproduced with any index. Here' s a test case demonstrating this.

          create table t1 (a int, index idx(a)) engine=myisam;
          insert into t1 values (267), (273),  (287), (303), (308);
          select max(a) from t1 where a < 303 and (a between 267 AND 287);
          

          MariaDB [test]> select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +--------+
          | max(a) |
          +--------+
          |    273 |
          +--------+
          

          We see that MIN/MAX optimization is applied here:

          MariaDB [test]> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | 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 |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          

          Interesting that the following query

          select max(a) from t1 where a <= 303 and (a between 267 AND 287);
          

          works fine and with the same optimization applied

           
          MariaDB [test]> select max(a) from t1 where a <= 303 and (a between 267 AND 287);
          +--------+
          | max(a) |
          +--------+
          |    287 |
          +--------+
           
          MariaDB [test]> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287);
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          | 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 |
          +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          

          igor Igor Babaev added a comment - The problem can be reproduced with any index. Here' s a test case demonstrating this. create table t1 (a int , index idx(a)) engine=myisam; insert into t1 values (267), (273), (287), (303), (308); select max (a) from t1 where a < 303 and (a between 267 AND 287); MariaDB [test]> select max(a) from t1 where a < 303 and (a between 267 AND 287); +--------+ | max(a) | +--------+ | 273 | +--------+ We see that MIN/MAX optimization is applied here: MariaDB [test]> explain select max(a) from t1 where a < 303 and (a between 267 AND 287); +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 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 | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ Interesting that the following query select max (a) from t1 where a <= 303 and (a between 267 AND 287); works fine and with the same optimization applied   MariaDB [test]> select max(a) from t1 where a <= 303 and (a between 267 AND 287); +--------+ | max(a) | +--------+ | 287 | +--------+   MariaDB [test]> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287); +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 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 | +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
          igor Igor Babaev added a comment -

          MySQL-8.0.21 has the same bug:

          mysql> select version();
          +--------------+
          | version()    |
          +--------------+
          | 8.0.21-debug |
          +--------------+
          1 row in set (0.00 sec)
           
          mysql> create table t1 (a int, index idx(a)) engine=myisam;
          Query OK, 0 rows affected (0.01 sec)
           
          mysql> insert into t1 values (267), (273),  (287), (303), (308);
          Query OK, 5 rows affected (0.00 sec)
          Records: 5  Duplicates: 0  Warnings: 0
           
          mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +--------+
          | max(a) |
          +--------+
          |    273 |
          +--------+
          1 row in set (0.00 sec)
           
          mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          1 row in set, 1 warning (0.00 sec)
           
          mysql> select max(a) from t1 where a <= 303 and (a between 267 AND 287);
          +--------+
          | max(a) |
          +--------+
          |    287 |
          +--------+
          1 row in set (0.01 sec)
           
          mysql> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287);
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          1 row in set, 1 warning (0.00 sec)
          

          igor Igor Babaev added a comment - MySQL-8.0.21 has the same bug: mysql> select version(); +--------------+ | version() | +--------------+ | 8.0.21-debug | +--------------+ 1 row in set (0.00 sec)   mysql> create table t1 (a int, index idx(a)) engine=myisam; Query OK, 0 rows affected (0.01 sec)   mysql> insert into t1 values (267), (273), (287), (303), (308); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0   mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287); +--------+ | max(a) | +--------+ | 273 | +--------+ 1 row in set (0.00 sec)   mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec)   mysql> select max(a) from t1 where a <= 303 and (a between 267 AND 287); +--------+ | max(a) | +--------+ | 287 | +--------+ 1 row in set (0.01 sec)   mysql> explain select max(a) from t1 where a <= 303 and (a between 267 AND 287); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec)

          Same with 8.0.23:

          openxs@ao756:~/dbs/8.0$ bin/mysql -uroot test  --socket=/tmp/mysql8.sock
          Reading table information for completion of table and column names
          You can turn off this feature to get a quicker startup with -A
           
          Welcome to the MySQL monitor.  Commands end with ; or \g.
          Your MySQL connection id is 10
          Server version: 8.0.23 Source distribution
           
          Copyright (c) 2000, 2021, Oracle and/or its affiliates.
           
          Oracle is a registered trademark of Oracle Corporation and/or its
          affiliates. Other names may be trademarks of their respective
          owners.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          mysql> drop table if exists t1;
          Query OK, 0 rows affected (1.57 sec)
           
          mysql> create table t1 (a int, index idx(a)) engine=myisam;
          Query OK, 0 rows affected (0.15 sec)
           
          mysql>
          mysql> insert into t1 values (267), (273),  (287), (303), (308);
          Query OK, 5 rows affected (0.09 sec)
          Records: 5  Duplicates: 0  Warnings: 0
           
          mysql>
          mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +--------+
          | max(a) |
          +--------+
          |    273 |
          +--------+
          1 row in set (0.02 sec)
           
          mysql> select max(a) from t1 where a < 303 and (a >= 267 AND a <= 287);
          +--------+
          | max(a) |
          +--------+
          |    287 |
          +--------+
          1 row in set (0.00 sec)
           
          mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287);
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
          +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
          1 row in set, 1 warning (0.00 sec)
          

          valerii Valerii Kravchuk added a comment - Same with 8.0.23: openxs@ao756:~/dbs/8.0$ bin/mysql -uroot test --socket=/tmp/mysql8.sock Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.23 Source distribution   Copyright (c) 2000, 2021, Oracle and/or its affiliates.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> drop table if exists t1; Query OK, 0 rows affected (1.57 sec)   mysql> create table t1 (a int, index idx(a)) engine=myisam; Query OK, 0 rows affected (0.15 sec)   mysql> mysql> insert into t1 values (267), (273), (287), (303), (308); Query OK, 5 rows affected (0.09 sec) Records: 5 Duplicates: 0 Warnings: 0   mysql> mysql> select max(a) from t1 where a < 303 and (a between 267 AND 287); +--------+ | max(a) | +--------+ | 273 | +--------+ 1 row in set (0.02 sec)   mysql> select max(a) from t1 where a < 303 and (a >= 267 AND a <= 287); +--------+ | max(a) | +--------+ | 287 | +--------+ 1 row in set (0.00 sec)   mysql> explain select max(a) from t1 where a < 303 and (a between 267 AND 287); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec)

          Yes, the bug also appears in MySQL and we've reported it.

          sergeylebedev Sergey Lebedev added a comment - Yes, the bug also appears in MySQL and we've reported it.
          igor Igor Babaev added a comment -

          A similar problem can be observed here:

          MariaDB [test]> select min(a) from t1 where a > 267 and (a between 273 AND 303);
          +--------+
          | min(a) |
          +--------+
          |    287 |
          +--------+
          1 row in set (0.00 sec)
           
          MariaDB [test]> select min(a) from t1 where a >= 267 and (a between 273 AND 303);
          +--------+
          | min(a) |
          +--------+
          |    273 |
          +--------+
          1 row in set (0.00 sec)
          

          igor Igor Babaev added a comment - A similar problem can be observed here: MariaDB [test]> select min(a) from t1 where a > 267 and (a between 273 AND 303); +--------+ | min(a) | +--------+ | 287 | +--------+ 1 row in set (0.00 sec)   MariaDB [test]> select min(a) from t1 where a >= 267 and (a between 273 AND 303); +--------+ | min(a) | +--------+ | 273 | +--------+ 1 row in set (0.00 sec)
          shulga Dmitry Shulga added a comment -

          Approved

          shulga Dmitry Shulga added a comment - Approved
          igor Igor Babaev added a comment -

          A fix for this bug was pushed into 10.2

          igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2

          People

            igor Igor Babaev
            sergeylebedev Sergey Lebedev
            Votes:
            1 Vote for this issue
            Watchers:
            6 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.