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

Wrong result while using index for group-by

    XMLWordPrintable

Details

    Description

      --source include/have_innodb.inc
       
      create table t (pk int primary key, a int, key(a)) engine=InnoDB;
      insert into t values (1,NULL),(2,8),(3,5),(4,NULL),(5,10);
       
      SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a;
       
      # Cleanup
      drop table t;
      

      10.5 acd23da4

      --source include/have_innodb.inc
       
      create table t (pk int primary key, a int, key(a)) engine=InnoDB;
      insert into t values (1,NULL),(2,8),(3,5),(4,NULL),(5,10);
       
      SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a;
       
      # Cleanup
      drop table t;
      

      10.5 acd23da4

      SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a;
      MIN(pk)	a
      3	5
      2	8
      5	10
      EXPLAIN EXTENDED SELECT MIN(pk), a FROM t WHERE pk <> 1 GROUP BY a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t	range	PRIMARY	a	5	NULL	6	100.00	Using where; Using index for group-by
      Warnings:
      Note	1003	select min(`test`.`t`.`pk`) AS `MIN(pk)`,`test`.`t`.`a` AS `a` from `test`.`t` where `test`.`t`.`pk` <> 1 group by `test`.`t`.`a`
      

      A row for (4,NULL) is missing.

      10.4 doesn't use index for group-by, and the result there is correct.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.