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

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

    XMLWordPrintable

    Details

      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

          Issue Links

            Activity

              People

              Assignee:
              monty Michael Widenius
              Reporter:
              iridakos Lazarus Lazaridis
              Votes:
              0 Vote for this issue
              Watchers:
              7 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.