Bug #18286 Unknown Column on ON clause

• Submitted: 16 Mar 2006 17:16
• Modified: 16 Mar 2006 17:40
• Reporter: Michael Furdyk
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.19
• OS: Linux (RH EL 3)
• CPU Architecture: Any

[16 Mar 2006 17:16] Michael Furdyk

Description:
Several queries that should be fine (checked the Incompatibilities) are returning unknown columns on columns that do exist...

Query: SELECT s.SID, t.STitle, t.SText, s.Link, sl.LinkPhrase, s.Image, s.SID * 0 + rand() as rand_col FROM tig.Spotlights s, tig_en.Spotlights t LEFT JOIN tig_en.SpotlightsLink sl ON sl.SLID = s.SLinkID WHERE s.Image != '' AND s.SID = t.SID AND s.STypeID = '9' AND s.StartDate <= CURDATE() AND s.ExpireDate > CURDATE() ORDER BY rand_col LIMIT 1
MySQL Error: Unknown column 's.SLinkID' in 'on clause'

How to repeat:
The query should be straightforward, just a LEFT JOIN...

[16 Mar 2006 17:40] Michael Furdyk

Never mind - this issue is actually just a MySQL syntax fix...

SELECT s.SID, t.STitle, t.SText, s.Link, sl.LinkPhrase, s.Image, s.SID * 0 + rand() as rand_col FROM tig.Spotlights s JOIN tig_en.Spotlights t LEFT JOIN tig_en.SpotlightsLink sl ON sl.SLID = s.SLinkID WHERE s.Image != '' AND s.SID = t.SID AND s.STypeID = '9' AND s.StartDate <= CURDATE() AND s.ExpireDate > CURDATE() ORDER BY rand_col LIMIT 1

Using JOIN instead of , works - documentation needs to be updated

[16 Mar 2006 17:40] Michael Furdyk

See comment