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

Community
  • 1
helpse
  • 131
  • 1
  • 5
  • 1
    You mean 5.5 I suppose. There is no 5.4 version. – ypercubeᵀᴹ May 08 '15 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 08 '15 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 08 '15 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 08 '15 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 08 '15 at 17:13
  • Tested in 5.6.16 and 5.6.21, and 5.5.41 and 5.5.43 – helpse May 08 '15 at 17:15
  • Sorry but the query you show has no join to any other table. How do you expect people to answer on something that we don't know about? – ypercubeᵀᴹ May 08 '15 at 17:15
  • It's not needed in the example. Just check what I say, comment/uncomment the order by. Can u tell me why, when I add the order by, it's anulating the having ? – helpse May 08 '15 at 17:17

4 Answers4

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 .

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • That's what I thought. I already reported: http://bugs.mysql.com/bug.php?id=76988 Hoping to get a reply soon – helpse May 08 '15 at 20:49
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.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 08 '15 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.

Hitesh Mundra
  • 791
  • 7
  • 7
  • 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 08 '15 at 21:36
  • 1
    check this- http://sqlfiddle.com/#!9/c928c/62 – Hitesh Mundra May 08 '15 at 21:44
  • You should add an explanation of why the query should be modified this way. – ypercubeᵀᴹ May 09 '15 at 05: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 09 '15 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 '15 at 10:31
  • Thanks @HiteshMundra. Please look at my answer, it shows further information regarding to this issue – helpse May 11 '15 at 04:30
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.

helpse
  • 131
  • 1
  • 5