Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
The following query:
SELECT table2 .`col_date_key`
FROM CC table1 JOIN (
SELECT *
FROM B ) table2 ON table1 .`col_varchar_key`
WHERE ( table1 .`pk` , table1 .`col_int_key` ) NOT IN (
SELECT `col_int_nokey` , MAX( `col_int_nokey` )
FROM CC ) ;
crashes as follows:
#3 0x0827e86a in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x0823ad8f in Ordered_key::get_field_idx (this=0x65010e, i=0) at item_subselect.h:955
#6 0x08239803 in subselect_rowid_merge_engine::init (this=0xb5f949c8, non_null_key_parts=0x0, partial_match_key_parts=0xb5f8bcd0) at item_subselect.cc:4861
#7 0x08237e66 in subselect_hash_sj_engine::exec (this=0xb5f8bc88) at item_subselect.cc:4162
#8 0x0822fb79 in Item_subselect::exec (this=0xb5f597b0) at item_subselect.cc:481
#9 0x0822fd72 in Item_in_subselect::exec (this=0xb5f597b0) at item_subselect.cc:578
#10 0x082315dc in Item_in_subselect::val_bool (this=0xb5f597b0) at item_subselect.cc:1246
#11 0x081cf276 in Item::val_bool_result (this=0xb5f597b0) at item.h:783
#12 0x081fab3f in Item_in_optimizer::val_int (this=0xb5f8a328) at item_cmpfunc.cc:1873
#13 0x081cf218 in Item::val_int_result (this=0xb5f8a328) at item.h:779
#14 0x081cb0da in Item_cache_int::cache_value (this=0xb5f8e170) at item.cc:7720
#15 0x081d2e6a in Item_cache_wrapper::cache (this=0xb5f8e0f8) at item.cc:6725
#16 0x081c8cb5 in Item_cache_wrapper::val_bool (this=0xb5f8e0f8) at item.cc:6889
#17 0x081f6e5d in Item_func_not::val_int (this=0xb5f59918) at item_cmpfunc.cc:287
#18 0x081b8d6e in Item::val_bool (this=0xb5f59918) at item.cc:187
#19 0x082027f9 in Item_cond_and::val_int (this=0xb5f8b938) at item_cmpfunc.cc:4548
#20 0x0831d5b8 in evaluate_join_record (join=0xb5f80330, join_tab=0xb5f8b540, error=0) at sql_select.cc:13181
#21 0x0831d229 in sub_select (join=0xb5f80330, join_tab=0xb5f8b540, end_of_records=false) at sql_select.cc:13087
#22 0x0831c62f in do_select (join=0xb5f80330, fields=0xab2df0c, table=0x0, procedure=0x0) at sql_select.cc:12633
#23 0x083029f3 in JOIN::exec (this=0xb5f80330) at sql_select.cc:2355
#24 0x08303129 in mysql_select (thd=0xab2c4f8, rref_pointer_array=0xab2df90, tables=0xb5f57718, wild_num=0, fields=..., conds=0xb5f59918, og_num=0,
order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb5f80300, unit=0xab2dbd4, select_lex=0xab2de74)
at sql_select.cc:2556
#25 0x082fb71b in handle_select (thd=0xab2c4f8, lex=0xab2db78, result=0xb5f80300, setup_tables_done_option=0) at sql_select.cc:276
#26 0x0829a164 in execute_sqlcom_select (thd=0xab2c4f8, all_tables=0xb5f57718) at sql_parse.cc:5081
#27 0x08290b3c in mysql_execute_command (thd=0xab2c4f8) at sql_parse.cc:2265
#28 0x0829c325 in mysql_parse (thd=0xab2c4f8,
inBuf=0xb5f31430 "SELECT table2 .`col_date_key`\nFROM CC table1 JOIN (\nSELECT *\nFROM B ) table2 ON table1 .`col_varchar_key`\nWHERE ( table1 .`pk` , table1 .`col_int_key` ) NOT IN (\nSELECT `col_int_nokey` , MAX("..., length=230, found_semicolon=0xb60ff230) at sql_parse.cc:6027
#29 0x0828e5f2 in dispatch_command (command=COM_QUERY, thd=0xab2c4f8, packet=0xab2e519 "", packet_length=233) at sql_parse.cc:1184
#30 0x0828dae0 in do_command (thd=0xab2c4f8) at sql_parse.cc:890
#31 0x0828ac78 in handle_one_connection (arg=0xab2c4f8) at sql_connect.cc:1153
#32 0x00a08919 in start_thread () from /lib/libpthread.so.0
#33 0x00951e5e in clone () from /lib/libc.so.6
explain:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00
1 PRIMARY table1 ALL NULL NULL NULL NULL 20 100.00 Using where
3 SUBQUERY CC ALL NULL NULL NULL NULL 20 100.00
2 DERIVED B system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select '1900-01-01' AS `col_date_key` from `test`.`CC` `table1` join (select '1' AS `pk`,'1' AS `col_int_nokey`,'7' AS `col_int_key`,'1900-01-01' AS `col_date_key`,'f' AS `col_varchar_key` from `test`.`B`) `table2` where ((not(<in_optimizer>((`test`.`table1`.`pk`,`test`.`table1`.`col_int_key`),(`test`.`table1`.`pk`,`test`.`table1`.`col_int_key`) in ( <materialize> (select `test`.`CC`.`col_int_nokey`,max(`test`.`CC`.`col_int_nokey`) from `test`.`CC` ), <primary_index_lookup>(`test`.`table1`.`pk` in <temporary table> on distinct_key where ((`test`.`table1`.`pk` = `materialized subselect`.`col_int_nokey`) and (`test`.`table1`.`col_int_key` = `materialized subselect`.`MAX( ``col_int_nokey`` )`))))))) and `test`.`table1`.`col_varchar_key`)
Note that the EXPLAIN does not reveal that partial match has been used. Instead, materialization is mentioned in the extended EXPLAIN.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Crash in Ordered_key::get_field_idx with partial_match_rowid_merge=on
This is a partial_match_rowid_merge specific bug.
Launchpad bug id: 613009
Re: Crash in Ordered_key::get_field_idx with partial_match_rowid_merge=on
Test case :
SET SESSION optimizer_switch='partial_match_rowid_merge=on,partial_match_table_scan=off,semijoin=off';
CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,NULL,'v');
INSERT INTO `CC` VALUES (11,1,9,'2006-06-14','r');
INSERT INTO `CC` VALUES (12,5,9,'2002-09-12','a');
INSERT INTO `CC` VALUES (13,3,186,'2005-02-15','m');
INSERT INTO `CC` VALUES (14,6,NULL,NULL,'y');
INSERT INTO `CC` VALUES (15,92,2,'2008-11-04','j');
INSERT INTO `CC` VALUES (16,7,3,'2004-09-04','d');
INSERT INTO `CC` VALUES (17,NULL,0,'2006-06-05','z');
INSERT INTO `CC` VALUES (18,3,133,'1900-01-01','e');
INSERT INTO `CC` VALUES (19,5,1,'1900-01-01','h');
INSERT INTO `CC` VALUES (20,1,8,'1900-01-01','b');
INSERT INTO `CC` VALUES (21,2,5,'2005-01-13','s');
INSERT INTO `CC` VALUES (22,NULL,5,'2006-05-21','e');
INSERT INTO `CC` VALUES (23,1,8,'2003-09-08','j');
INSERT INTO `CC` VALUES (24,0,6,'2006-12-23','e');
INSERT INTO `CC` VALUES (25,210,51,'2006-10-15','f');
INSERT INTO `CC` VALUES (26,8,4,'2005-04-06','v');
INSERT INTO `CC` VALUES (27,7,7,'2008-04-07','x');
INSERT INTO `CC` VALUES (28,5,6,'2006-10-10','m');
INSERT INTO `CC` VALUES (29,NULL,4,'1900-01-01','c');
CREATE TABLE `B` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
`col_int_key` int(11) DEFAULT NULL,
`col_date_key` date DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_date_key` (`col_date_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,1,7,'1900-01-01','f');
SELECT table2 .`col_date_key`
FROM CC table1 JOIN (
SELECT *
FROM B ) table2 ON table1 .`col_varchar_key`
WHERE ( table1 .`pk` , table1 .`col_int_key` ) NOT IN (
SELECT `col_int_nokey` , MAX( `col_int_nokey` )
FROM CC ) ;