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

[5.1-5.2 only] Wrong result (missing rows) with group by, multi-part key, equality conditions (MySQL:70359)

    XMLWordPrintable

    Details

      Description

      The bug also exists in MariaDB 5.3.12, but it has been fixed in the current 5.3 tree. It still exists in 5.1 and 5.2 tree though. It also exists in all of 5.1-5.7 to date. I'm filing it for the record, to make it easier to find.

      CREATE TABLE t1 (a INT, b VARCHAR(1), INDEX(b,a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (2,'s'),(5,'h'),(3,'q'),(1,'a'),(3,'v'),
      (6,'u'),(7,'s'),(5,'y'),(1,'z'),(5,'i'),(2,'y');
       
      SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b;

      Actual result:

      SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b;
      b	max(a)
      i	5

      Expected result:

      b	max(a)
      i	5
      s	2
      y	2

      EXPLAIN:

      EXPLAIN EXTENDED
      SELECT b, max(a) FROM t1 WHERE b = 'i' OR a = 2 GROUP BY b;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	range	b	b	4	NULL	6	100.00	Using where; Using index for group-by
      Warnings:
      Note	1003	select `test`.`t1`.`b` AS `b`,max(`test`.`t1`.`a`) AS `max(a)` from `test`.`t1` where ((`test`.`t1`.`b` = 'i') or (`test`.`t1`.`a` = 2)) group by `test`.`t1`.`b`

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration