MySQL Bugs: #30218: After MySQL has upgraded from 4.1.20 to 5.0.x, the following SQL does not work.

Bug #30218	After MySQL has upgraded from 4.1.20 to 5.0.x, the following SQL does not work.
Submitted:	3 Aug 2007 2:19	Modified:	3 Aug 2007 8:43
Reporter:	Eli K	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.20 / 5.0.x	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The following code returned the desired set in version 4.1.20. After an upgrade to any 5.0 version, same code returns an empty set:

$sql = "(SELECT M.*
       FROM cont_modules M, cont_group_permissions GP1
         LEFT JOIN cont_group_permissions GP2 on M.module_id=GP2.module_id AND GP1.group_id=GP2.group_id
       WHERE M.sequence > 0 AND GP1.module_id='3' AND GP1.permission='Y' 
          AND (GP2.permission is NULL OR GP2.permission='Y' )
          AND GP1.group_id=" . session_get("group_id") . ")
       UNION
       (SELECT M.*
       FROM cont_modules M, cont_group_permissions GP
       WHERE M.sequence > 0 AND GP.module_id=M.module_id AND GP.permission='Y' AND GP.group_id=" . session_get("group_id") . ")
       ORDER BY sequence";

How to repeat:
Try the code in both versions

Suggested fix:
There could be a command that is unsupported in the new version

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about incompatibility changes in JOIN processing at http://dev.mysql.com/doc/refman/5.0/en/join.html

Expression "FROM cont_modules M, cont_group_permissions GP1 LEFT JOIN cont_group_permissions GP2 on M.module_id=GP2.module_id AND GP1.group_id=GP2.group_id" looks redundant. If you are not agree provide output of SHOW CREATE TABLE for every table used in the SELECT statement, dump of some rows from these tables we can see difference with and reopen the bug.