MySQL Bugs: #76988: Order By affecting a Query with column alias, group by, and having

Bug #76988	Order By affecting a Query with column alias, group by, and having
Submitted:	8 May 2015 15:49	Modified:	15 May 2015 15:16
Reporter:	Sergio Melendez	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.16, 5.6.24	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	column alias, GROUP BY, having, order by

Description:
I have a query with column alias, group by and having. It's getting altered when I add an order by.

This strange behavior is only happening in 5.6.16 and 5.6.21 (which I was using in development, over Windows)

It was not happening on 5.4.41 and 5.4.43 (which I had in production, over Linux)
So I downgraded my local MySQL to 5.4.* and the query worked as expected.

How to repeat:
I'm not sure on how to repeat, but I will do my best in order to show this strange behavior

My query:

============================================================
SELECT 
a.*
, if(u.status = 1 AND a.acre_aprobado = 0, 5, u.status) tipo
FROM acreditado a 
JOIN users u ON (`a`.`acre_id` = `u`.`id`) 
WHERE `acre_test` = 0 
GROUP BY `a`.`acre_id` 
HAVING tipo = '1' 
#ORDER BY `acre_aprobado` ASC 
============================================================

The result in both 5.4.* and 5.6.*: (without the order by)

============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

Now the result when I add order by:

5.4.*
============================================================
acre_id | obac_id | ... | tipo
27      | 23      | ... | 1
37      | 22      | ... | 1
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

5.6.*
============================================================
acre_id | obac_id | ... | tipo
25      | 24      | ... | 0
32      | 24      | ... | 0
44      | 22      | ... | 1
46      | 22      | ... | 1
============================================================

As you can see, in 5.4.* everything works as expected, but in 5.6.* order by is modifying the result, and it's even making "having" don't filter

I created an SQL fiddle

http://sqlfiddle.com/#!9/c928c/8

First run the query as is, and then uncomment "order by" and run it.

by 5.4.* I mean 5.5.* .Please notice.

Thank you for the bug report. I was able to repeat with 5.6.24 version but not more with current source probably fixed for bug http://bugs.mysql.com/bug.php?id=69638 (5.6.25) so please wait for next release. Thanks.

Hello Miguel, thanks for your reply. I downgraded to 5.5.* in development, but it's not the bes thing I can do. Do you know when will we have 5.6.25? Please let me know. Best regards, Sergio

it was solved? in release 5.6.25?