Bug #27777 | LEFT OUTER JOIN doesn't see column in ON clause | ||
---|---|---|---|
Submitted: | 12 Apr 2007 8:09 | Modified: | 12 Apr 2007 12:15 |
Reporter: | Cedric Wider | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.30 | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[12 Apr 2007 8:09]
Cedric Wider
[12 Apr 2007 8:11]
Cedric Wider
I just reformatted the query to this: SELECT count(*) FROM TBL_USER, TBL_SENDER JOIN TBL_MESSAGE ON TBL_SENDER.msg_id = TBL_MESSAGE.msg_id JOIN TBL_RECIPIENT ON TBL_SENDER.msg_id = TBL_RECIPIENT.msg_id LEFT OUTER JOIN TBL_SPAM ON TBL_SPAM.spam_id = TBL_SENDER.sender_id AND TBL_SPAM.user_id = recipient_id WHERE TBL_SPAM.spam_id IS NULL AND recipient_id = 1 and TBL_SENDER.sender_id = TBL_USER.user_id AND TBL_RECIPIENT.delete_id = 0 AND TBL_MESSAGE.delete_id = 0 AND draft = 0 AND stop_msg_delivery = 0 ; and this works...
[12 Apr 2007 11:10]
Valeriy Kravchuk
Thank you for taking the time to write to us, but this is, likely, not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/join.html: "Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are. ..." Doesn't it explain your results?
[12 Apr 2007 12:15]
Cedric Wider
Thank you Valeriy for you fast and patient reply. Of course it perfectly explains my results. Thanks again for not just replying "RTFM"