Mysql Order By affecting a Query with column alias, group by, and having

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.5.41 and 5.5.43 (which I had in production, over Linux)
So I downgraded my local MySQL to 5.5.* 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.5. 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.5.*

============================================================
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.5. 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.

I read something about MySQL Extensions to GROUP

The HAVING tipo = '1' should in no way allow output to include tipo=0, regardless of GROUP BY issues, etc.

File a bug report at http://bugs.mysql.com .

StackExchange Database Administrators Q#100987, answer score: 2