Description:
Looks like tables order is sensitive in FROM, thus, while writing the sql query in some cases putting one table after another affects the query and mysql starts replying with "unknown column".
Was detected only twice, with invision power board 1.1.2 (most likely other versions also use this query) and 4images gallery (unknown version, reported by a customer, fixed by the customer don't know how)
I've tried to simplify the query by maximum to underline the situation.
How to repeat:
install invision power board 1.1.2 or just create a database with 3 tables
CREATE TABLE `ibf_categories` (
`id` smallint(5) NOT NULL default '0',
`position` tinyint(3) default NULL,
`state` varchar(10) default NULL,
`name` varchar(128) NOT NULL default '',
`description` text,
`image` varchar(128) default NULL,
`url` varchar(128) default NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `ibf_moderators` (
`mid` bigint(20) NOT NULL auto_increment,
`forum_id` int(5) NOT NULL default '0',
`member_name` varchar(32) NOT NULL default '',
`member_id` varchar(32) NOT NULL default '0',
`edit_post` tinyint(1) default NULL,
`edit_topic` tinyint(1) default NULL,
`delete_post` tinyint(1) default NULL,
`delete_topic` tinyint(1) default NULL,
`view_ip` tinyint(1) default NULL,
`open_topic` tinyint(1) default NULL,
`close_topic` tinyint(1) default NULL,
`mass_move` tinyint(1) default NULL,
`mass_prune` tinyint(1) default NULL,
`move_topic` tinyint(1) default NULL,
`pin_topic` tinyint(1) default NULL,
`unpin_topic` tinyint(1) default NULL,
`post_q` tinyint(1) default NULL,
`topic_q` tinyint(1) default NULL,
`allow_warn` tinyint(1) default NULL,
`edit_user` tinyint(1) NOT NULL default '0',
`is_group` tinyint(1) default '0',
`group_id` smallint(3) NOT NULL default '0',
`group_name` varchar(200) default NULL,
`split_merge` tinyint(1) default '0',
PRIMARY KEY (`mid`),
KEY `forum_id` (`forum_id`),
KEY `group_id` (`group_id`),
KEY `member_id` (`member_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `ibf_forums` (
`id` smallint(5) NOT NULL default '0',
`topics` mediumint(6) default NULL,
`posts` mediumint(6) default NULL,
`last_post` int(10) default NULL,
`last_poster_id` int(10) default NULL,
`last_poster_name` varchar(32) default NULL,
`name` varchar(128) NOT NULL default '',
`description` text,
`position` tinyint(2) default NULL,
`use_ibc` tinyint(1) default NULL,
`use_html` tinyint(1) default NULL,
`status` varchar(10) default NULL,
`start_perms` varchar(255) default NULL,
`reply_perms` varchar(255) default NULL,
`read_perms` varchar(255) default NULL,
`password` varchar(32) default NULL,
`category` smallint(5) NOT NULL default '0',
`last_title` varchar(128) default NULL,
`last_id` int(10) default NULL,
`sort_key` varchar(32) default NULL,
`sort_order` varchar(32) default NULL,
`prune` tinyint(3) default NULL,
`show_rules` tinyint(1) default NULL,
`upload_perms` varchar(255) default NULL,
`preview_posts` tinyint(1) default NULL,
`allow_poll` tinyint(1) NOT NULL default '1',
`allow_pollbump` tinyint(1) NOT NULL default '0',
`inc_postcount` tinyint(1) NOT NULL default '1',
`skin_id` int(10) default NULL,
`parent_id` mediumint(5) default '-1',
`subwrap` tinyint(1) default '0',
`sub_can_post` tinyint(1) default '1',
PRIMARY KEY (`id`),
KEY `category` (`category`),
KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
try to run
select * from ibf_forums, ibf_categories LEFT JOIN ibf_moderators ON (ibf_forums.id = ibf_moderators.forum_id) limit 1;
It replies with:
ERROR 1054 (42S22): Unknown column 'ibf_forums.id' in 'on clause'
changing the table order by putting ibf_forums AFTER ibf_categories fixes the situation, but this solution was found just by playing around with the query
