Wednesday, December 28, 2005

What could be causing this?

I recently migrated a Nucleus CMS from MySQL 4.x to MySQL 5.0 but upon migration, I get the following message

ERROR 1054 (42S22): Unknown column 'i.inumber' in 'on clause'


when running the query:

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, m.mname as author, m.mrealname as authorname, i.itime, i.imore as more, m.mnumber as authorid, m.memail as authormail, m.murl as authorurl, c.cname as category, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i, nucleus_member as m, nucleus_category as c LEFT join nucleus_plug_tags as t ON t.item_id=i.itemid WHERE i.iauthor=m.mnumber and i.iblog=1 and i.icat=c.catid and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



The interesting thing is that the column exists in the table and no change has been made. Any ideas what would be causing this?

Thanks

Update

Just noticed that the following works

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, i.itime, i.imore as more, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i LEFT join nucleus_plug_tags as t ON t.item_id=i.inumber WHERE i.iblog=1 and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



What could be causing this? When I shorten the query, it works but not when other tables are involved?

Any ideas??

6 comments:

Gary Richardson said...

I've noticed that mixing , and JOIN keywords causes this error.. We ran into this in a 4.0.20 to 5.0.16 upgrade.. Don't mix and match , and JOIN in your table list and it should work..

Frankly Speaking! said...

Thanks Gary,
you saved the day. I was thinking that it had something to do with the upgrade. Will post the updated query soon.

Thanks
Frank

Pieter Vos said...

Just set the SQL to have the tables between parentesis:

... FROM (nucleus_item as i, nucleus_member as m, nucleus_category as c ) ....

There have been some changes in Mysql, and it worked not in all v4.1.x. Have a look at this link: http://dev.mysql.com/doc/refman/5.0/en/join.html

Basically enclosing the table names in parentesis solves the problem, as the parser threads first comas with the joins.

Regards

Scott Noyes said...

http://dev.mysql.com/doc/refman/5.0/en/join.html
Read the section which begins, "Before MySQL 5.0.12, the comma operator (,) and JOIN both had the same precedence"

Frankly Speaking! said...

Thank you Scott for the link.

Frankly Speaking! said...

Thanks Peter for the additional information.

I appreciate everyone taking the time to help me out. Please let me know if I can be of any assistance.

Frank