| Bug #3188 | Ambiguous Column in Subselect crashes server | ||
|---|---|---|---|
| Submitted: | 16 Mar 2004 8:10 | Modified: | 23 Mar 2004 0:45 |
| Reporter: | Sean Daniels | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.1a/4.1.2 | OS: | Windows (Windows 2000) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[16 Mar 2004 8:17]
MySQL Verification Team
Could you please provide a complete test case with table schema. Thanks.
[16 Mar 2004 8:29]
Sean Daniels
Test case table schema: # Dump of table tb_clients # ------------------------------------------------------------ CREATE TABLE `tb_clients` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) default '', `login` varchar(20) default '', `pw` varchar(20) default '', `create_date` datetime default '0000-00-00 00:00:00', `delete_date` datetime default NULL, `directory` varchar(30) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # Dump of table tb_projects # ------------------------------------------------------------ CREATE TABLE `tb_projects` ( `id` int(11) NOT NULL auto_increment, `name` varchar(200) NOT NULL default '', `typeID` int(11) NOT NULL default '0', `statusID` int(11) NOT NULL default '0', `salesRepID` int(11) default NULL, `projectLeadID` int(11) default NULL, `clientID` int(11) default NULL, `url` varchar(100) default NULL, `contactName` varchar(50) default NULL, `contactPhone` varchar(30) default NULL, `contactEmail` varchar(100) default NULL, `descripShrt` text, `initDate` datetime NOT NULL default '0000-00-00 00:00:00', `contractDate` datetime default NULL, `startDate` datetime default NULL, `endDateEstimate` datetime default NULL, `endDateActual` datetime default NULL, `amountProposed` decimal(12,2) default NULL, `amountContract` decimal(12,2) default NULL, `update_date` timestamp NOT NULL, `create_date` timestamp NOT NULL, `delete_date` datetime default NULL, `isactive` tinyint(1) NOT NULL default '1', `billingSchedule` longtext, `alarmNote` varchar(200) default '', `amountBalance` decimal(12,2) default '0.00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; # Dump of table tb_projects_pages # ------------------------------------------------------------ CREATE TABLE `tb_projects_pages` ( `pageID` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `title` varchar(50) default NULL, `notes` text, `allowImages` int(11) default NULL, `circuit` varchar(20) default NULL, `fuseaction` varchar(20) default NULL, `metaDescrip` text, `metaKeywords` text, `submitterEmail` varchar(100) default NULL, `updateDate` timestamp NOT NULL, `createDate` datetime NOT NULL default '0000-00-00 00:00:00', `dueDate` datetime default NULL, `deleteDate` datetime default NULL, `status` varchar(50) NOT NULL default 'Pending Submission', `projectID` int(11) NOT NULL default '0', `devNotes` text, PRIMARY KEY (`pageID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Run the following query to crash the server: select p.id, p.name, c.name as client, (select count(pageID) from tb_projects_pages where projectID = p.id and delete_date is null) as totalPages from tb_projects p inner join tb_clients c on (c.id = p.clientID) Note that both tb_clients and tb_projects have a delete_date column, but tb_projects_pages does not.
[16 Mar 2004 8:43]
MySQL Verification Team
Thank you for the bug report I was able to repeat on 4.1.2 server
2 - 3 days older bk tree.
/item.cc
--866--
if ((tmp= find_field_in_tables(thd, this,
table_list, &where,
0)) != not_found_field)
{
prev_subselect_item->used_tables_cache|= tmp->table->map;
prev_subselect_item->const_item_cache= 0;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
break;
}
------------------------------------------------------------------------
CALL STACK:
> mysqld.exe!Item_field::fix_fields(THD * thd=0x02eeee60, st_table_list * tables=0x02ef0640, Item * * ref=0x02ef08bc) Line 870 + 0x3 C++
mysqld.exe!Item_func::fix_fields(THD * thd=0x02eeee60, st_table_list * tables=0x02ef0640, Item * * ref=0x02ef097c) Line 209 + 0x1d C++
mysqld.exe!Item_cond::fix_fields(THD * thd=0x02eeee60, st_table_list * tables=0x02ef0640, Item * * ref=0x02ef22c0) Line 1779 + 0x27 C++
mysqld.exe!setup_conds(THD * thd=0x02eeee60, st_table_list * tables=0x02ef0640, Item * * conds=0x02ef22c0) Line 2326 + 0x1b C++
mysqld.exe!setup_without_group(THD * thd=0x02eeee60, Item * * ref_pointer_array=0x02ef22f8, st_table_list * tables=0x02ef0640, List<Item> & fields={...}, List<Item> & all_fields={...}, Item * * conds=0x02ef22c0, st_order * order=0x00000000, st_order * group=0x00000000, int * hidden_group_fields=0x02ef2238) Line 263 + 0x11 C++
mysqld.exe!JOIN::prepare(Item * * * rref_pointer_array=0x02ef03f0, st_table_list * tables_init=0x02ef0640, unsigned int wild_num=0, Item * conds_init=0x02ef08f0, unsigned int og_num=0, st_order * order_init=0x00000000, st_order * group_init=0x00000000, Item * having_init=0x00000000, st_order * proc_param_init=0x00000000, st_select_lex * select_lex_arg=0x02ef0308, st_select_lex_unit * unit_arg=0x02ef0420) Line 308 + 0x112 C++
mysqld.exe!subselect_single_select_engine::prepare() Line 1072 + 0x8b C++
mysqld.exe!Item_subselect::fix_fields(THD * thd_param=0x02eeee60, st_table_list * tables=0x02ef0a98, Item * * ref=0x02ef0a4c) Line 109 + 0x11 C++
mysqld.exe!setup_fields(THD * thd=0x02eeee60, Item * * ref_pointer_array=0x02ef1870, st_table_list * tables=0x02ef0a98, List<Item> & fields={...}, int set_query_id=1, List<Item> * sum_func_list=0x02ef17d0, int allow_sum_func=1) Line 2144 + 0x1c C++
mysqld.exe!JOIN::prepare(Item * * * rref_pointer_array=0x02eef05c, st_table_list * tables_init=0x02ef0a98, unsigned int wild_num=0, Item * conds_init=0x00000000, unsigned int og_num=0, st_order * order_init=0x00000000, st_order * group_init=0x00000000, Item * having_init=0x00000000, st_order * proc_param_init=0x00000000, st_select_lex * select_lex_arg=0x02eeef74, st_select_lex_unit * unit_arg=0x02eeee84) Line 308 + 0xb1 C++
mysqld.exe!mysql_select(THD * thd=0x02eeee60, Item * * * rref_pointer_array=0x02eef05c, st_table_list * tables=0x02ef0a98, unsigned int wild_num=0, List<Item> & fields={...}, Item * conds=0x00000000, unsigned int og_num=0, st_order * order=0x00000000, st_order * group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned long select_options=8669696, select_result * result=0x02ef0e68, st_select_lex_unit * unit=0x02eeee84, st_select_lex * select_lex=0x02eeef74) Line 1588 + 0x34 C++
mysqld.exe!handle_select(THD * thd=0x02eeee60, st_lex * lex=0x02eeee78, select_result * result=0x02ef0e68) Line 190 + 0x87 C++
mysqld.exe!mysql_execute_command(THD * thd=0x02eeee60) Line 1946 + 0x11 C++
mysqld.exe!mysql_parse(THD * thd=0x02eeee60, char * inBuf=0x02ef00c8, unsigned int length=219) Line 3935 + 0x9 C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x02eeee60, char * packet=0x02ef4021, unsigned int packet_length=220) Line 1437 + 0x1d C++
mysqld.exe!do_command(THD * thd=0x02eeee60) Line 1253 + 0x31 C++
mysqld.exe!handle_one_connection(void * arg=0x02eeee60) Line 1003 + 0x9 C++
mysqld.exe!pthread_start(void * param=0x00e3ccb0) Line 63 + 0x7 C
mysqld.exe!_threadstart(void * ptd=0x02eec470) Line 173 + 0xd C
kernel32.dll!77e6d33b()
[18 Mar 2004 3:32]
Alexey Botchkov
bk commit - 4.1 tree (hf:1.1792)
[23 Mar 2004 0:45]
Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: Looking for an ambiguous column that does not exist in the subselect table but does exist in any other table in the query causes MySQL to crash rather than return a "ambiguous column" error. How to repeat: select p.id, p.name, (select count(pageID) from tb_projects_pages where projectID = p.id and delete_date is null) as totalPages from tb_projects p In the above example, tb_projects has a column "delete_date" but tb_projects_pages does not. Running the query causes MySQL to crash. Suggested fix: Return ambiguous column error instead.