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

Wrong query results when `using index for group-by`

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5, 10.6
    • Fix Version/s: 10.5, 10.6
    • Component/s: Optimizer
    • Labels:
    • Environment:
      Running Docker containers of the official MariaDB images under Debian

      Description

      Assume the following settings (defaults):

      select @@SQL_MODE;
      +-------------------------------------------------------------------------------------------+
      | @@SQL_MODE                                                                                |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
       
      select @@GLOBAL.SQL_MODE;
      +-------------------------------------------------------------------------------------------+
      | @@GLOBAL.SQL_MODE                                                                         |
      +-------------------------------------------------------------------------------------------+
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      and the following database state:

      /* create the database */
      CREATE DATABASE IF NOT EXISTS bug_investigation;
      use bug_investigation;
       
      /* create the table */
      DROP TABLE IF EXISTS `cats`;
      CREATE TABLE `cats` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `owner_id` int(11) DEFAULT NULL,
        `foo` tinyint(1) DEFAULT 0,
        `whatever` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `index_cats_on_owner_id_and_foo` (`owner_id`,`foo`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      /* Insert data */
      INSERT INTO cats (owner_id, foo, whatever) VALUES (1, TRUE, "yello");
      INSERT INTO cats (owner_id, foo, whatever) VALUES (1, FALSE, "yello");
      INSERT INTO cats (owner_id, foo, whatever) VALUES (2, TRUE, "yello");
      INSERT INTO cats (owner_id, foo, whatever) VALUES (2, TRUE, "yello");
      INSERT INTO cats (owner_id, foo, whatever) VALUES (2, FALSE, "yello");
       
      SELECT * FROM cats;
      +----+----------+------+----------+
      | id | owner_id | foo  | whatever |
      +----+----------+------+----------+
      |  1 |        1 |    1 | yello    |
      |  2 |        1 |    0 | yello    |
      |  3 |        2 |    1 | yello    |
      |  4 |        2 |    1 | yello    |
      |  5 |        2 |    0 | yello    |
      +----+----------+------+----------+
      5 rows in set (0.001 sec)
      
      

      and note the following query:

      SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      

      10.3

      The aforementioned query gives the following results:

      MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      +----------+
      | owner_id |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.000 sec)
      

      and explanation:

      MariaDB [bug_investigation]> EXPLAIN SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
      | id   | select_type | table | type  | possible_keys | key                            | key_len | ref  | rows | Extra                    |
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
      |    1 | SIMPLE      | cats  | index | NULL          | index_cats_on_owner_id_and_foo | 7       | NULL |    5 | Using where; Using index |
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+--------------------------+
      1 row in set (0.001 sec)
      

      10.5.x

      Starting from 10.5.3 and after (at least till 10.6.4 which I checked), the results are different.

      MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      Empty set (0.000 sec)
      

      and explained:

      MariaDB [bug_investigation]> EXPLAIN SELECT DISTINCT owner_id FROM cats WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
      | id   | select_type | table | type  | possible_keys | key                            | key_len | ref  | rows | Extra                                 |
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
      |    1 | SIMPLE      | cats  | range | NULL          | index_cats_on_owner_id_and_foo | 7       | NULL | 6    | Using where; Using index for group-by |
      +------+-------------+-------+-------+---------------+--------------------------------+---------+------+------+---------------------------------------+
      1 row in set (0.001 sec)
      

      The same query but ignoring the used index, returns the correct results.

      MariaDB [bug_investigation]> SELECT DISTINCT owner_id FROM cats IGNORE INDEX (index_cats_on_owner_id_and_foo) WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1);
      +----------+
      | owner_id |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.000 sec)
      

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            iridakos Lazarus Lazaridis
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration