unknown column error, bug?

lajkonik86

SELECT cc. * , ccomp.name, sum( p.prints ) AS regionprints FROM csite_coupons cc, csite_companies ccomp LEFT JOIN csite_popularity p ON ( p.couponid = cc.id AND p.day > 1190030380 ) WHERE cc.status =1 AND ccomp.status =1 AND cc.start < 1190110704 AND cc.end > 1190110704 AND cc.company = ccomp.id GROUP BY cc.id ORDER BY regionprints DESC LIMIT 0 , 10

gives error:

Unknown column 'cc.id' in 'on clause'

why?

Lars_Berg

The table cc is not within the scope of the join clause. Rewrite it as

FROM `csite_coupons` cc join `csite_companies` ccomp 
on cc.company = ccomp.id
LEFT JOIN `csite_popularity` p ON ( p.couponid = cc.id AND p.day > 1190030380 )

Sxooter

lajkonik86 wrote:
SELECT cc. * , ccomp.name, sum( p.prints ) AS regionprints FROM csite_coupons cc, csite_companies ccomp LEFT JOIN csite_popularity p ON ( p.couponid = cc.id AND p.day > 1190030380 ) WHERE cc.status =1 AND ccomp.status =1 AND cc.start < 1190110704 AND cc.end > 1190110704 AND cc.company = ccomp.id GROUP BY cc.id ORDER BY regionprints DESC LIMIT 0 , 10

gives error:

Unknown column 'cc.id' in 'on clause'

why?

To elaborate on what Lars wrote, this WAS a bug, in that older versions of MySQL accepted that syntax. the join clause fires first, then the rest of the tables are joined. So, the first table, cc, isn't seen yet.

I would strongly suggest you use join syntax for all your joins, as it's easier to read and simpler to troubleshoot and debug.

Also, indenting your queries will make them easier to troubleshoot as well, for both you and us.

lajkonik86

SELECT cc. * , ccomp.name, sum( p.prints ) AS regionprints FROM csite_coupons cc
JOIN csite_companies ccomp ON ( cc.company = ccomp.id )
LEFT JOIN csite_popularity p ON ( p.couponid = cc.id AND p.day > 1190116780 )
WHERE cc.status =1 AND ccomp.status =1 AND cc.start < 1190234950 AND cc.end > 1190234950 GROUP BY cc.id ORDER BY regionprints DESC LIMIT 0 , 10

😃

it is working, I never realized this order of things. Any documentation on it?
I just always learned from other peoples code, apparently it was a common mistake 🙂

Sxooter

lajkonik86 wrote:
SELECT cc. * , ccomp.name, sum( p.prints ) AS regionprints FROM csite_coupons cc
JOIN csite_companies ccomp ON ( cc.company = ccomp.id )
LEFT JOIN csite_popularity p ON ( p.couponid = cc.id AND p.day > 1190116780 )
WHERE cc.status =1 AND ccomp.status =1 AND cc.start < 1190234950 AND cc.end > 1190234950 GROUP BY cc.id ORDER BY regionprints DESC LIMIT 0 , 10

😃

it is working, I never realized this order of things. Any documentation on it?

The SQL Spec. Honest, it's in there. somewhere. I've even seen it, but that document is so huge and technical that finding these kinds of things can take a while.

I just always learned from other peoples code, apparently it was a common mistake 🙂

Yeah, it's one of those things that mysql < 5 just swallowed and did. Somewhere along the development tree of MySQL 5.0.x they fixed it so it wouldn't do that anymore. I seem to recall it was somewhere in the range of 5.0.17/18/19.