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

Question

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.*

• ypercubeᵀᴹ: You mean 5.5 I suppose. There is no 5.4 version.
• ypercubeᵀᴹ: About your issue, what are the primary keys of the tables? It would be best if you included the output of SHOW CREATE TABLE tablename; for both tables.
• ypercubeᵀᴹ: You don't need the GROUP BY at all if both the acreditado.acre_id and the users.id are primary keys in the respective tables.
• ypercubeᵀᴹ: And what exactly is the issue? Your ORDER BY acre_aprobado ASC is using a column that has a lot of duplicate values. It should not be expected to be consistent.
• helpse: @ypercube 1. Please refer to the SQLfiddle. 2. I do need a group by since I'm joining "acreditado" with another table, from which I'm getting a count. It's not on the fiddle since it's not needed. The issue can be reproduced without this other table. 3. Play within that fiddle. Remove and add the "order by" and see how the result changes. Order by should not change the rows in the result, it should only change the order. But in 5.6.* it's anulating the having (showing rows with tipo = 0) and it only happens in 5.6.* Not in 5.5.*

Answer 1

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 .

• helpse: That's what I thought. I already reported: bugs.mysql.com/bug.php?id=76988 Hoping to get a reply soon

Answer 2

I recently answered a post Does "deprecated" in MySQL means "not guaranteed" or "still guaranteed"? where I mentioned how ORDER BY NULL can have spurious behavior between Storage Engines and Major/Minor Versions (in your case).

Your question shows that you are doing what the end of my post says

Therefore, you should always test code in Dev/Staging server for any behavioral changes in SELECTs. There is no substitute from such testing.

Why the differences ? Who knows. It could be just an oversight or something intentional. Either way, when the output of certain types of ordering was left unchecked, every Developer/DBA just took ordering for grant.

Here is an example of something I always took for granted: I sometimes recommend using LEFT JOIN instead of INNER JOIN to keep the order of the keys in the left side of a query. Here are some posts of mine where I actually state this:

• Nov 28, 2011 : MySQL Records Turnover: How to do it?
• Apr 08, 2014 : How to fix slow LIMIT queries with big offsets without heavily rewriting them?
• Jan 06, 2015 : Slow query on mid-size tables with good index coverage

Now, imagine the advice in my 3 posts going obsolete should Oracle decide to explicitly change the default order of the left side of a LEFT JOIN.

My advice to you would be to be pessimistic about this and just put ORDER BY with GROUP BY clauses and create the necessary indexes to support them.

• helpse: Thanks for the help, but it does not solve the problem. It's a fact that you must test your code before productios, but the issue is still there. Why in previous versions the behavior is ok, i.e. order by effectively orders the result, and why in latest versions order by is not only ordering but modifying the results? Is it a feature change between versions or a bug? If it's the first, what has changed? And if it's a bug it must be solved. I already reported it.

Answer 3

You should run the query in this way. It will work correctly.

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`,tipo
HAVING tipo = '1' 
ORDER BY `a`.`acre_id`,`acre_aprobado` ASC 

This is depend on mysql server (optimization plan) whether check or not HAVING & ORDER BY clause columns which are not included in GROUP BY. To get result, from this strange behave of mysql-5.6 we have to sure to use tipo column condition. So we use tipo column in group by clause so that it checks HAVING. ORDER BY clause uses acre_id which is also in group_by caluse it work.

• helpse: I was going to ask why should I run the query in that way, but I tried it in the SQL fiddle and it doesn't work. It still shows rows with tipo = 0
• Hitesh Mundra: check this- sqlfiddle.com/#!9/c928c/62
• ypercubeᵀᴹ: You should add an explanation of why the query should be modified this way.
• helpse: @HiteshMundra Can u tell us why ur way does work? And why in 5.5.* there is no problem, and the issue is on 5.6.* ?
• Hitesh Mundra: @helpse I am not sure but mysql server 5.6 plan does have any optimize way to run queries like this exception so it doesn't use column (which are not in group by) in having/order by. Thats why i used tipo column in group by and make 100% possibility to used in having/order by.

Answer 4

According to the last comment at the bug report (http://bugs.mysql.com/bug.php?id=76988), it's a bug present in 5.6.24 (current release at the time of writting), and it has been fixed in current source code.

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.

Also, please refer to the link in the quote. It shows a similar bug report.