Bug #28378 query that worked in 5.0.27, doesn't work anymore in 5.0.41
Submitted: 11 May 2007 14:00 Modified: 19 May 2007 7:46
Reporter: Tomasz Janowski Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.41/5.1 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression

[11 May 2007 14:00] Tomasz Janowski 
Description:
I have a query that doesn't work properly on mysql 5.0.41. The same query works fine on mysql 5.0.27. The content is exactly the same on both servers but the results aren't.

How to repeat:
1. Download table dump: http://files-upload.com/220391/test.sql.gz.html 

Run following query both on 5.0.41 and 5.0.27

SELECT sm.id_user, count( * ) AS count 
FROM test AS sm 
WHERE sm.status =5 
AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND id_ticket = sm.id_ticket ) 
GROUP BY sm.id_user 
ORDER BY sm.id_user

Suggested fix:
Workaround:

SELECT sm.id_user, count( * ) AS count FROM
(
SELECT sm.id_user  FROM test AS sm WHERE sm.status =5 AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND id_ticket = sm.id_ticket ) ) sm
GROUP BY sm.id_user ORDER BY sm.id_user
[11 May 2007 14:01] Tomasz Janowski 
version and os provided
[11 May 2007 14:12] Tomasz Janowski 
Test table

Attachment: test.sql.gz (application/x-gzip, text), 341.23 KiB.
[11 May 2007 15:20] MySQL Verification Team 
Thank you for the bug report. Could you please provide the results you
got with both versions?. Thanks in advance.
[11 May 2007 15:26] Tomasz Janowski 
Server version:         5.0.27

mysql> SELECT sm.id_user, count( * ) AS count FROM test AS sm WHERE sm.status =5 AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND id_ticket = sm.id_ticket ) GROUP BY sm.id_user ORDER BY sm.id_user;
+---------+-------+
| id_user | count |
+---------+-------+
|      30 |     3 | 
|      34 |    11 | 
|      38 |     1 | 
|      39 |     1 | 
|      47 |     1 | 
|      51 |     1 | 
|      52 |     3 | 
|      54 |    47 | 
|      56 |     1 | 
|      58 |    16 | 
|      65 |     5 | 
|      66 |     6 | 
|      67 |     1 | 
|      72 |    10 | 
|      80 |     1 | 
|      84 |     1 | 
|      85 |     1 | 
|      86 |     2 | 
|      88 |     3 | 
|      89 |     7 | 
|      93 |     9 | 
|      94 |     9 | 
|      95 |    14 | 
|      98 |     3 | 
|     103 |     1 | 
|     105 |     1 | 
|     107 |     2 | 
|     108 |     6 | 
|     111 |     7 | 
|     113 |     5 | 
|     117 |    11 | 
|     126 |     3 | 
+---------+-------+
32 rows in set (2.47 sec)

--------------------------------------------------------

Server version:         5.0.41-log
mysql>  SELECT sm.id_user, count( * ) AS count FROM test AS sm WHERE sm.status =5 AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND id_ticket = sm.id_ticket ) GROUP BY sm.id_user ORDER BY sm.id_user;
Empty set (0.08 sec)
[11 May 2007 16:08] MySQL Verification Team 
Thank you for the feedback.

mysql> SELECT sm.id_user, count( * ) AS count
    -> FROM test AS sm
    -> WHERE sm.status =5
    -> AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND
    -> id_ticket = sm.id_ticket )
    -> GROUP BY sm.id_user
    -> ORDER BY sm.id_user
    -> ;
Empty set (0.13 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.42    |
+-----------+
1 row in set (0.00 sec)

mysql>
------------------------------------------------------------------------------------
mysql> SELECT sm.id_user, count( * ) AS count
    -> FROM test AS sm
    -> WHERE sm.status =5
    -> AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND
    -> id_ticket = sm.id_ticket )
    -> GROUP BY sm.id_user
    -> ORDER BY sm.id_user
    -> ;
+---------+-------+
| id_user | count |
+---------+-------+
|      30 |     3 |
|      34 |    11 |
|      38 |     1 |
|      39 |     1 |
|      47 |     1 |
|      51 |     1 |
|      52 |     3 |
|      54 |    47 |
|      56 |     1 |
|      58 |    16 |
|      65 |     5 |
|      66 |     6 |
|      67 |     1 |
|      72 |    10 |
|      80 |     1 |
|      84 |     1 |
|      85 |     1 |
|      86 |     2 |
|      88 |     3 |
|      89 |     7 |
|      93 |     9 |
|      94 |     9 |
|      95 |    14 |
|      98 |     3 |
|     103 |     1 |
|     105 |     1 |
|     107 |     2 |
|     108 |     6 |
|     111 |     7 |
|     113 |     5 |
|     117 |    11 |
|     126 |     3 |
+---------+-------+
32 rows in set (0.48 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.0.37-community-nt-log |
+-------------------------+
1 row in set (0.00 sec)

mysql>
-------------------------------------------------------------------------------------------
mysql> SELECT sm.id_user, count( * ) AS count
    -> FROM test AS sm
    -> WHERE sm.status =5
    -> AND sm.date = ( SELECT MAX( date ) FROM test WHERE id_user = sm.id_user AND
    -> id_ticket = sm.id_ticket )
    -> GROUP BY sm.id_user
    -> ORDER BY sm.id_user
    -> ;
Empty set (0.30 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.1.18-beta-nt |
+----------------+
1 row in set (0.00 sec)

mysql>
-----------------------------------------------------------------------------------------
|      93 |     9 |
|      94 |     9 |
|      95 |    14 |
|      98 |     3 |
|     103 |     1 |
|     105 |     1 |
|     107 |     2 |
|     108 |     6 |
|     111 |     7 |
|     113 |     5 |
|     117 |    11 |
|     126 |     3 |
+---------+-------+
32 rows in set (0.44 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.23    |
+-----------+
1 row in set (0.00 sec)

mysql>
[19 May 2007 7:46] Igor Babaev 
This bug is a duplicate of bug #28337.
I checked it with and without the patch for #28337 applied.