[MDEV-6629] MIN/MAX and "HAVING func(DISTINCT)": wrong results Created: 2014-08-22  Updated: 2022-11-08  Resolved: 2022-11-08

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.13
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 Description   

I know that this bug has been fixed by Oracle:
http://bugs.mysql.com/bug.php?id=69833

But their solution (as it is documented in the bug page) is funny. I'm opening this to ask you if you could please apply a better fix.

While the test case looks exotic, I realized that this causes bugs in an application when I use something like this:

SELECT col1, MIN(col2), MAX(col2)
FROM table
GROUP BY col1
HAVING COUNT(DISTINCT col1) > 1;

Both MIN and MAX give completely wrong results, which makes the report useless. The only workaround I was able to find is slow.

 Comments   
Comment by Sergei Petrunia [ 2015-02-24 ]

( Checked the source code: we have Mithun's fix, merged).

Comment by Sergei Golubchik [ 2022-11-08 ]

The second query

SELECT col1, MIN(col2), MAX(col2)
 
FROM table
 
GROUP BY col1
 
HAVING COUNT(DISTINCT col1) > 1;

appears to work correctly

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