[MDEV-6707] Wrong result (extra row) with group by, multi-part key Created: 2014-09-06  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 10.3.4, 10.3, 10.4
Fix Version/s: 10.4, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: 11.0-sel, not-10.5+, upstream

Sprint: 5.5.59

 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`



 Comments   
Comment by Elena Stepanova [ 2014-09-06 ]

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.

Comment by Varun Gupta (Inactive) [ 2018-01-05 ] 

quick_group_min_max_select: index f2 (0), length: 9
 
using quick_range_select:
 
  quick range select, key f2, length: 9
 
    c/NULL <= X <= c/9
 
    c/9 <= X <= c
 
using 2 quick_ranges for MIN/MAX:

Comment by Sergei Petrunia [ 2018-01-10 ]

(varun please post your candidate patch here for the record)

I've read the patch and agree with the approach, I want a full patch for review. Input so far:

  • last_range and save_last_range are pointers, please use NULL, not 0
    - Adding QUICK_RANGE_SELECT::save_last_range seems ugly. This member is only updated in ::get_next_prefix, so it will sit uninitialized (and meaningless) in most of QUICK_RANGE_SELECT's use cases. How about storing it in QUICK_GROUP_MIN_MAX_SELECT and passing it to ::get_next_prefix as a parameter?
  • We need comments for the added code block as it is not at all obvious.
Comment by Varun Gupta (Inactive) [ 2018-01-10 ]

http://lists.askmonty.org/pipermail/commits/2018-January/011803.html

Comment by Sergei Petrunia [ 2018-01-18 ]

Review input provided via email. Please submit again for review when addressed.

Comment by Varun Gupta (Inactive) [ 2018-01-18 ]

Patch after addressing the review
http://lists.askmonty.org/pipermail/commits/2018-January/011846.html

Comment by Sergei Petrunia [ 2018-03-20 ]

More review feedback: https://lists.launchpad.net/maria-developers/msg11142.html

Comment by Varun Gupta (Inactive) [ 2018-04-09 ]

Patch after 2nd code review
http://lists.askmonty.org/pipermail/commits/2018-April/012326.html

Comment by Alice Sherepa [ 2022-11-29 ]

Currently, MariaDB 10.5+ returns correct results, wrong results on 10.3+10.4. And bb-11.0 3135acf0bba521cd032cbf18bc6d also returns an extra row.

Generated at Thu Feb 08 07:13:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.