MySQL Bugs: #28378: query that worked in 5.0.27, doesn't work anymore in 5.0.41

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

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

version and os provided

Test table

Attachment: test.sql.gz (application/x-gzip, text), 341.23 KiB.

Thank you for the bug report. Could you please provide the results you
got with both versions?. Thanks in advance.

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)

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>

This bug is a duplicate of bug #28337.
I checked it with and without the patch for #28337 applied.