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 BY from the post Why does MySQL allow HAVING to use SELECT aliases? that might have some to do with this issue, but I wonder why the different behavior between 5.5.* and 5.6.*
SHOW CREATE TABLE tablename;
for both tables.GROUP BY
at all if both theacreditado.acre_id
and theusers.id
are primary keys in the respective tables.ORDER BY acre_aprobado ASC
is using a column that has a lot of duplicate values. It should not be expected to be consistent.