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

GROUP_MIN_MAX optimization is erroneously applied in some cases

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.14
    • 10.0.15
    • Optimizer
    • None

    Description

      Problem 1:
      I create a table and populate it with data like this:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (1,'2001-01-01');
      		 
      INSERT INTO t1 VALUES (1,'2001-01-02');
      		 
      INSERT INTO t1 VALUES (1,'2001-01-03');
      		 
      INSERT INTO t1 VALUES (1,' 2001-01-04');
      		 
      INSERT INTO t1 VALUES (2,'2001-01-01');
      		 
      INSERT INTO t1 VALUES (2,'2001-01-02');
      		 
      INSERT INTO t1 VALUES (2,'2001-01-03');
      		 
      INSERT INTO t1 VALUES (2,' 2001-01-04');
      		 
      INSERT INTO t1 VALUES (3,'2001-01-01');
      		 
      INSERT INTO t1 VALUES (3,'2001-01-02');
      		 
      INSERT INTO t1 VALUES (3,'2001-01-03');
      		 
      INSERT INTO t1 VALUES (3,' 2001-01-04');
      		 
      INSERT INTO t1 VALUES (4,'2001-01-01');
      		 
      INSERT INTO t1 VALUES (4,'2001-01-02');
      		 
      INSERT INTO t1 VALUES (4,'2001-01-03');
      		 
      INSERT INTO t1 VALUES (4,' 2001-01-04');

      Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
      ('*space*2001-01-04'), while the other values have no spaces.

      Now I run this query:

      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;

      returns the following result:

      +----+-------------+-------------+
      		 
      | id | MIN(a)      | MAX(a)      |
      		 
      +----+-------------+-------------+
      		 
      |  1 |  2001-01-04 |  2001-01-04 |
      		 
      |  2 |  2001-01-04 |  2001-01-04 |
      		 
      |  3 |  2001-01-04 |  2001-01-04 |
      		 
      |  4 |  2001-01-04 |  2001-01-04 |
      		 
      +----+-------------+-------------+

      Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

      Now if I add an index and re-run the query:

      ALTER TABLE t1 ADD KEY(id,a);
      		 
      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;

      it returns empty set. Looks wrong. The result set should not change when a new index is added.

      Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

      Problem 2:

      I create a table with a ENUM column with values ordered in counter-alphabetic order:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
      		 
      INSERT INTO t1 VALUES (1,'01');
      		 
      INSERT INTO t1 VALUES (1,'02');
      		 
      INSERT INTO t1 VALUES (1,'03');
      		 
      INSERT INTO t1 VALUES (1,'04');
      		 
      INSERT INTO t1 VALUES (2,'01');
      		 
      INSERT INTO t1 VALUES (2,'02');
      		 
      INSERT INTO t1 VALUES (2,'03');
      		 
      INSERT INTO t1 VALUES (2,'04');
      		 
      INSERT INTO t1 VALUES (3,'01');
      		 
      INSERT INTO t1 VALUES (3,'02');
      		 
      INSERT INTO t1 VALUES (3,'03');
      		 
      INSERT INTO t1 VALUES (3,'04');
      		 
      INSERT INTO t1 VALUES (4,'01');
      		 
      INSERT INTO t1 VALUES (4,'02');
      		 
      INSERT INTO t1 VALUES (4,'03');
      		 
      INSERT INTO t1 VALUES (4,'04');
      		 
      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;

      The query returns:

      +----+--------+--------+
      		 
      | id | MIN(a) | MAX(a) |
      		 
      +----+--------+--------+
      		 
      |  1 | 02     | 04     |
      		 
      |  2 | 02     | 04     |
      		 
      |  3 | 02     | 04     |
      		 
      |  4 | 02     | 04     |
      		 
      +----+--------+--------+

      Looks good so far.
      Now I add a key and rerun the query:

      ALTER TABLE t1 ADD KEY(id,a);
      		 
      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;

      It returns empty set. This is wrong.

      Explain tells that only 9 out of 16 rows are examined, and tracing reveals that GROUP_MIN_MAX optimization is erroneously enabled to optimize this.

      mysql> EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='03' GROUP BY id;
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      		 
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      		 
      |    1 | SIMPLE      | t1    | range | NULL          | id   | 6       | NULL |    9 | Using where; Using index for group-by |
      		 
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      		 
      1 row in set (0.00 sec)

      Attachments

        Issue Links

          blocks

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6983 BIGINT 99991231000000 to TIME'00:00:00' comparison is not consistent

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

            		 I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.
            bar Alexander Barkov made changes -
            Description I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

            		 *Problem 1:*
            I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

            *Problem 2:*

            I create a table with a ENUM column with values ordered in counter-alphabetic order:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'01');
            INSERT INTO t1 VALUES (1,'02');
            INSERT INTO t1 VALUES (1,'03');
            INSERT INTO t1 VALUES (1,'04');
            INSERT INTO t1 VALUES (2,'01');
            INSERT INTO t1 VALUES (2,'02');
            INSERT INTO t1 VALUES (2,'03');
            INSERT INTO t1 VALUES (2,'04');
            INSERT INTO t1 VALUES (3,'01');
            INSERT INTO t1 VALUES (3,'02');
            INSERT INTO t1 VALUES (3,'03');
            INSERT INTO t1 VALUES (3,'04');
            INSERT INTO t1 VALUES (4,'01');
            INSERT INTO t1 VALUES (4,'02');
            INSERT INTO t1 VALUES (4,'03');
            INSERT INTO t1 VALUES (4,'04');
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            The query returns:
            {noformat}
            +----+--------+--------+
            | id | MIN(a) | MAX(a) |
            +----+--------+--------+
            | 1 | 02 | 04 |
            | 2 | 02 | 04 |
            | 3 | 02 | 04 |
            | 4 | 02 | 04 |
            +----+--------+--------+
            {noformat}
            Looks good so far.
            Now I add a key an rerun the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            It returns empty set. This is wrong.

            Explain tells that only 9 out of 16 rows are examined, and tracing reveals that GROUP_MIN_MAX optimization is erroneously enabled to optimize this.
            {noformat}
            mysql> EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='03' GROUP BY id;
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | 1 | SIMPLE | t1 | range | NULL | id | 6 | NULL | 9 | Using where; Using index for group-by |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            bar Alexander Barkov made changes -
            Description *Problem 1:*
            I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

            *Problem 2:*

            I create a table with a ENUM column with values ordered in counter-alphabetic order:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'01');
            INSERT INTO t1 VALUES (1,'02');
            INSERT INTO t1 VALUES (1,'03');
            INSERT INTO t1 VALUES (1,'04');
            INSERT INTO t1 VALUES (2,'01');
            INSERT INTO t1 VALUES (2,'02');
            INSERT INTO t1 VALUES (2,'03');
            INSERT INTO t1 VALUES (2,'04');
            INSERT INTO t1 VALUES (3,'01');
            INSERT INTO t1 VALUES (3,'02');
            INSERT INTO t1 VALUES (3,'03');
            INSERT INTO t1 VALUES (3,'04');
            INSERT INTO t1 VALUES (4,'01');
            INSERT INTO t1 VALUES (4,'02');
            INSERT INTO t1 VALUES (4,'03');
            INSERT INTO t1 VALUES (4,'04');
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            The query returns:
            {noformat}
            +----+--------+--------+
            | id | MIN(a) | MAX(a) |
            +----+--------+--------+
            | 1 | 02 | 04 |
            | 2 | 02 | 04 |
            | 3 | 02 | 04 |
            | 4 | 02 | 04 |
            +----+--------+--------+
            {noformat}
            Looks good so far.
            Now I add a key an rerun the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            It returns empty set. This is wrong.

            Explain tells that only 9 out of 16 rows are examined, and tracing reveals that GROUP_MIN_MAX optimization is erroneously enabled to optimize this.
            {noformat}
            mysql> EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='03' GROUP BY id;
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | 1 | SIMPLE | t1 | range | NULL | id | 6 | NULL | 9 | Using where; Using index for group-by |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            		 *Problem 1:*
            I create a table and populate it with data like this:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'2001-01-01');
            INSERT INTO t1 VALUES (1,'2001-01-02');
            INSERT INTO t1 VALUES (1,'2001-01-03');
            INSERT INTO t1 VALUES (1,' 2001-01-04');
            INSERT INTO t1 VALUES (2,'2001-01-01');
            INSERT INTO t1 VALUES (2,'2001-01-02');
            INSERT INTO t1 VALUES (2,'2001-01-03');
            INSERT INTO t1 VALUES (2,' 2001-01-04');
            INSERT INTO t1 VALUES (3,'2001-01-01');
            INSERT INTO t1 VALUES (3,'2001-01-02');
            INSERT INTO t1 VALUES (3,'2001-01-03');
            INSERT INTO t1 VALUES (3,' 2001-01-04');
            INSERT INTO t1 VALUES (4,'2001-01-01');
            INSERT INTO t1 VALUES (4,'2001-01-02');
            INSERT INTO t1 VALUES (4,'2001-01-03');
            INSERT INTO t1 VALUES (4,' 2001-01-04');
            {code}

            Notice, the values with ' 2001-01-04' in the column 'a' have leading spaces
            ('*space*2001-01-04'), while the other values have no spaces.

            Now I run this query:
            {code}
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            returns the following result:
            {noformat}
            +----+-------------+-------------+
            | id | MIN(a) | MAX(a) |
            +----+-------------+-------------+
            | 1 | 2001-01-04 | 2001-01-04 |
            | 2 | 2001-01-04 | 2001-01-04 |
            | 3 | 2001-01-04 | 2001-01-04 |
            | 4 | 2001-01-04 | 2001-01-04 |
            +----+-------------+-------------+
            {noformat}
            Looks fine so far. Notice, the column is VARCHAR and comparison is done as DATE.

            Now if I add an index and re-run the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
            {code}
            it returns empty set. Looks wrong. The result set should not change when a new index is added.

            Tracing the code reveals that GROUP_MIN_MAX optimization is applied after adding the index. This is wrong, because comparison is done according to DATE in this example. The index on a VARCHAR column should not be used to optimize DATE operations.

            *Problem 2:*

            I create a table with a ENUM column with values ordered in counter-alphabetic order:
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'01');
            INSERT INTO t1 VALUES (1,'02');
            INSERT INTO t1 VALUES (1,'03');
            INSERT INTO t1 VALUES (1,'04');
            INSERT INTO t1 VALUES (2,'01');
            INSERT INTO t1 VALUES (2,'02');
            INSERT INTO t1 VALUES (2,'03');
            INSERT INTO t1 VALUES (2,'04');
            INSERT INTO t1 VALUES (3,'01');
            INSERT INTO t1 VALUES (3,'02');
            INSERT INTO t1 VALUES (3,'03');
            INSERT INTO t1 VALUES (3,'04');
            INSERT INTO t1 VALUES (4,'01');
            INSERT INTO t1 VALUES (4,'02');
            INSERT INTO t1 VALUES (4,'03');
            INSERT INTO t1 VALUES (4,'04');
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            The query returns:
            {noformat}
            +----+--------+--------+
            | id | MIN(a) | MAX(a) |
            +----+--------+--------+
            | 1 | 02 | 04 |
            | 2 | 02 | 04 |
            | 3 | 02 | 04 |
            | 4 | 02 | 04 |
            +----+--------+--------+
            {noformat}
            Looks good so far.
            Now I add a key and rerun the query:
            {code}
            ALTER TABLE t1 ADD KEY(id,a);
            SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
            {code}
            It returns empty set. This is wrong.

            Explain tells that only 9 out of 16 rows are examined, and tracing reveals that GROUP_MIN_MAX optimization is erroneously enabled to optimize this.
            {noformat}
            mysql> EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='03' GROUP BY id;
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            | 1 | SIMPLE | t1 | range | NULL | id | 6 | NULL | 9 | Using where; Using index for group-by |
            +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
            1 row in set (0.00 sec)
            {noformat}
            serg Sergei Golubchik made changes -
            Affects Version/s 10.0.14 [ 17101 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0 [ 16000 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Sergei Golubchik [ serg ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            Assignee Sergei Golubchik [ serg ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            Component/s Optimizer [ 10200 ]
            Fix Version/s 10.0.15 [ 17300 ]
            Fix Version/s 10.0 [ 16000 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 57603 ] MariaDB v3 [ 65318 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 65318 ] MariaDB v4 [ 148385 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.