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

Wrong result (extra row) with group by, multi-part key

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13, 10.1.0
        • Fix Version/s: 10.0, 5.5
        • Component/s: None
        • Labels:

          Description

          Test case:

          CREATE TABLE t1 (f1 INT, f2 VARCHAR(1), KEY(f2,f1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
(7,'v'),(0,'s'),(9,'l'),(4,'c');

SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2;

          Actual result:

          MAX(f1)	f2
4	c
4	c

          Expected result:

          MAX(f1)	f2
4	c

          Note: The test case is not minimal, first 4 rows should be enough to reproduce the issue on MariaDB. But this test case is universal for all of MySQL/MariaDB versions (MySQL 5.7 requires all the rows)

          EXPLAIN:

          SELECT MAX(f1), f2 FROM t1 WHERE f2 LIKE 'c%' AND f1 <> 9 GROUP BY f2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	range	f2	f2	9	NULL	2	100.00	Using where; Using index for group-by
Warnings:
Note	1003	select max(`test`.`t1`.`f1`) AS `MAX(f1)`,`test`.`t1`.`f2` AS `f2` from `test`.`t1` where ((`test`.`t1`.`f2` like 'c%') and (`test`.`t1`.`f1` <> 9)) group by `test`.`t1`.`f2`

            Gliffy Diagrams

              Attachments

                Issue Links

                links to

                Web Link Bug #73825 - Wrong result (extra row) with group by, multi-part key

                  Activity

                  Hide
                  Permalink
                  elenst Elena Stepanova added a comment -

                  While the bug is upstream (I filed it as http://bugs.mysql.com/bug.php?id=73825), the fate of a similar bug http://bugs.mysql.com/bug.php?id=70359 leaves little hope for this one, so I'm assigning it for evaluation – whether we really want to wait another year, or fix it on our own.

                  Show
                  elenst Elena Stepanova added a comment - While the bug is upstream (I filed it as http://bugs.mysql.com/bug.php?id=73825 ), the fate of a similar bug http://bugs.mysql.com/bug.php?id=70359 leaves little hope for this one, so I'm assigning it for evaluation – whether we really want to wait another year, or fix it on our own.

                    People

                    • Assignee:
                      psergey Sergei Petrunia
                      Reporter:
                      elenst Elena Stepanova
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      2 Start watching this issue

                      Dates

                      • Created:
                        Updated: