3

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

Improve this question
8
  • 1
    You mean 5.5 I suppose. There is no 5.4 version.
    – ypercubeᵀᴹ
    May 8, 2015 at 16:01
  • 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ᵀᴹ
    May 8, 2015 at 16:03
  • 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ᵀᴹ
    May 8, 2015 at 16:09
  • 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.
    – ypercubeᵀᴹ
    May 8, 2015 at 16:18
  • @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.*
    – helpse
    May 8, 2015 at 17:13

4 Answers 4

Reset to default
2

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 .

Improve this answer
1
1

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:

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.

Improve this answer
1
  • 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.
    – helpse
    May 8, 2015 at 19:57
0

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.

Improve this answer
6
  • 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
    – helpse
    May 8, 2015 at 21:36
  • 1
    check this- sqlfiddle.com/#!9/c928c/62
    – Hitesh Mundra
    May 8, 2015 at 21:44
  • You should add an explanation of why the query should be modified this way.
    – ypercubeᵀᴹ
    May 9, 2015 at 5:46
  • @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.* ?
    – helpse
    May 9, 2015 at 22:41
  • @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.
    – Hitesh Mundra
    May 10, 2015 at 10:31
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.