MariaDB

RQG: crash in Item_field::register_field_in_read_map with semijoin=off and prepared statements and materialization

Bug #611396 reported by Philip Stoev on 2010-07-29

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Fix Released	Wishlist	Timour Katchaounov	MariaDB 5.3

Bug Description

The following query:

SELECT table1 .`pk` field3 , (
SELECT `col_int_nokey`
FROM C
WHERE ( 7 , 6 ) IN (
SELECT `pk` , `col_int_nokey`
FROM C ) ) field9
FROM CC table1 JOIN D table2 ON table2 .`col_varchar_key` = table1 .`col_varchar_key`
WHERE table2 .`pk` = 8
GROUP BY field3 , field9

Crashes when executed twice as a prepared statement in maria 5.3 with semijoin=off . Maria 5.2 is not affected.

backtrace:

#3 0x0827e8f8 in handle_segfault (sig=11) at mysqld.cc:2703
#4 <signal handler called>
#5 0x081ba141 in Item_field::register_field_in_read_map (this=0xb5e6cbb8, arg=0xb5e0a8e8 "\340\257\340\265h\275", <incomplete sequence \340\265>)
    at item.cc:740
#6 0x081cf5b2 in Item::walk (this=0xb5e6cbb8, processor=&virtual Item::register_field_in_read_map(uchar*), walk_subquery=true,
    arg=0xb5e0a8e8 "\340\257\340\265h\275", <incomplete sequence \340\265>) at item.h:903
#7 0x0822f805 in Item_subselect::walk (this=0xb5e6cf68, processor=&virtual table offset 300, walk_subquery=true,
    argument=0xb5e0a8e8 "\340\257\340\265h\275", <incomplete sequence \340\265>) at item_subselect.cc:445
#8 0x081e0ada in Item_func::walk (this=0xb5e6e168, processor=&virtual table offset 300, walk_subquery=true,
    argument=0xb5e0a8e8 "\340\257\340\265h\275", <incomplete sequence \340\265>) at item_func.cc:239
#9 0x0822f757 in Item_subselect::walk (this=0xb5e6d080, processor=&virtual table offset 300, walk_subquery=true,
    argument=0xb5e0a8e8 "\340\257\340\265h\275", <incomplete sequence \340\265>) at item_subselect.cc:436
#10 0x083c31b9 in register_used_fields (param=0xb61d1148) at filesort.cc:1017
#11 0x083c1ead in find_all_keys (param=0xb61d1148, select=0xb5e33180, sort_keys=0xb5e7a598, buffpek_pointers=0xb61d0f80, tempfile=0xb61d1064, indexfile=0x0)
    at filesort.cc:558
#12 0x083c0ed1 in filesort (thd=0x9fcefb8, table=0xb5e0a8e8, sortorder=0xb5e334e8, s_length=2, select=0xb5e33180, max_rows=18446744073709551615,
    sort_positions=false, examined_rows=0xb61d12b0) at filesort.cc:246
#13 0x08324a74 in create_sort_index (thd=0x9fcefb8, join=0xb5e6f288, order=0xb5e6de40, filesort_limit=18446744073709551615,
    select_limit=18446744073709551615, is_order_by=false) at sql_select.cc:15849
#14 0x08302dd4 in JOIN::exec (this=0xb5e6f288) at sql_select.cc:2298
#15 0x08303802 in mysql_select (thd=0x9fcefb8, rref_pointer_array=0xb5e6b670, tables=0xb5e6d2a0, wild_num=0, fields=..., conds=0xb5e31ac0, og_num=2,
    order=0x0, group=0xb5e6de40, having=0x0, proc_param=0x0, select_options=2416200192, result=0xb5e6df40, unit=0xb5e6b2d4, select_lex=0xb5e6b56c)
    at sql_select.cc:2556
#16 0x082fbecf in handle_select (thd=0x9fcefb8, lex=0xb5e6b278, result=0xb5e6df40, setup_tables_done_option=0) at sql_select.cc:276
#17 0x0829a5f0 in execute_sqlcom_select (thd=0x9fcefb8, all_tables=0xb5e6d2a0) at sql_parse.cc:5081
#18 0x08290fd0 in mysql_execute_command (thd=0x9fcefb8) at sql_parse.cc:2265
#19 0x08342ce1 in Prepared_statement::execute (this=0xb5e5c528, expanded_query=0xb61d2410, open_cursor=false) at sql_prepare.cc:3588
#20 0x083421e0 in Prepared_statement::execute_loop (this=0xb5e5c528, expanded_query=0xb61d2410, open_cursor=false, packet=0x0, packet_end=0x0)
    at sql_prepare.cc:3263
#21 0x08340bcc in mysql_sql_stmt_execute (thd=0x9fcefb8) at sql_prepare.cc:2533
#22 0x08290ffa in mysql_execute_command (thd=0x9fcefb8) at sql_parse.cc:2274
#23 0x0829c7d1 in mysql_parse (thd=0x9fcefb8, inBuf=0xb5e31900 "EXECUTE st1", length=11, found_semicolon=0xb61d3230) at sql_parse.cc:6027
#24 0x0828ea02 in dispatch_command (command=COM_QUERY, thd=0x9fcefb8, packet=0x9fe7311 "EXECUTE st1", packet_length=11) at sql_parse.cc:1184
#25 0x0828dea8 in do_command (thd=0x9fcefb8) at sql_parse.cc:890
#26 0x0828b008 in handle_one_connection (arg=0x9fcefb8) at sql_connect.cc:1153
#27 0x00a08919 in start_thread () from /lib/libpthread.so.0
#28 0x00951e5e in clone () from /lib/libc.so.6

Tags:

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2010-07-29:

Download full text (6.6 KiB)

Test case:

Test case:

SET @@optimizer_switch='semijoin=off';
--disable_abort_on_error
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_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_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,'v');
INSERT INTO `CC` VALUES (11,1,9,'r');
INSERT INTO `CC` VALUES (12,5,9,'a');
INSERT INTO `CC` VALUES (13,3,186,'m');
INSERT INTO `CC` VALUES (14,6,NULL,'y');
INSERT INTO `CC` VALUES (15,92,2,'j');
INSERT INTO `CC` VALUES (16,7,3,'d');
INSERT INTO `CC` VALUES (17,NULL,0,'z');
INSERT INTO `CC` VALUES (18,3,133,'e');
INSERT INTO `CC` VALUES (19,5,1,'h');
INSERT INTO `CC` VALUES (20,1,8,'b');
INSERT INTO `CC` VALUES (21,2,5,'s');
INSERT INTO `CC` VALUES (22,NULL,5,'e');
INSERT INTO `CC` VALUES (23,1,8,'j');
INSERT INTO `CC` VALUES (24,0,6,'e');
INSERT INTO `CC` VALUES (25,210,51,'f');
INSERT INTO `CC` VALUES (26,8,4,'v');
INSERT INTO `CC` VALUES (27,7,7,'x');
INSERT INTO `CC` VALUES (28,5,6,'m');
INSERT INTO `CC` VALUES (29,NULL,4,'c');
CREATE TABLE `D` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
INSERT INTO `D` VALUES (1,6,NULL,'r');
INSERT INTO `D` VALUES (2,8,0,'c');
INSERT INTO `D` VALUES (3,6,0,'o');
INSERT INTO `D` VALUES (4,6,7,'c');
INSERT INTO `D` VALUES (5,3,8,'d');
INSERT INTO `D` VALUES (6,9,4,'v');
INSERT INTO `D` VALUES (7,2,6,'m');
INSERT INTO `D` VALUES (8,1,5,'j');
INSERT INTO `D` VALUES (9,8,NULL,'f');
INSERT INTO `D` VALUES (10,0,NULL,'n');
INSERT INTO `D` VALUES (11,9,8,'z');
INSERT INTO `D` VALUES (12,8,8,'h');
INSERT INTO `D` VALUES (13,NULL,8,'q');
INSERT INTO `D` VALUES (14,0,1,'w');
INSERT INTO `D` VALUES (15,5,1,'z');
INSERT INTO `D` VALUES (16,1,5,'j');
INSERT INTO `D` VALUES (17,1,2,'a');
INSERT INTO `D` VALUES (18,6,7,'m');
INSERT INTO `D` VALUES (19,6,6,'n');
INSERT INTO `D` VALUES (20,1,4,'e');
INSERT INTO `D` VALUES (21,8,7,'u');
INSERT INTO `D` VALUES (22,1,0,'s');
INSERT INTO `D` VALUES (23,0,9,'u');
INSERT INTO `D` VALUES (24,4,3,'r');
INSERT INTO `D` VALUES (25,9,5,'g');
INSERT INTO `D` VALUES (26,8,1,'o');
INSERT INTO `D` VALUES (27,5,1,'w');
INSERT INTO `D` VALUES (28,9,5,'b');
INSERT INTO `D` VALUES (29,5,9,NULL);
INSERT INTO `D` VALUES (30,NULL,2,'y');
INSERT INTO `D` VALUES (31,NULL,5,'y');
INSERT INTO `D` VALUES (32,105,248,'u');
INSERT INTO `D` VALUES (33,0,0,'p');
INSERT INTO `D` VALUES (34,3,8,'s');
INSERT INTO `D` VALUES (35,1,1,'e');
INSERT INTO `D` VALUES (36,75,255,'d');
INSERT INTO `D` VALUES (37,9,9,'d');
INSERT INTO `D` VALUES (38,7,9,'c');
INSERT INTO `D` VALUES (39,NULL,3,'b');
INSERT INTO `D` VALUES (40,NULL,9,'t');
INSERT INTO `D` VALUES (41,4,6,NULL);
INSERT INTO `D` VALUES (42,0,4,'y');
INSERT INTO `D` VALUES (43,204,60,'c');
INSERT INTO `D` VALUES (44,0,7,'d');
INSERT INTO `D` VALUES (45,9,1,'x');
INSERT INTO `D` VALUES (46,8,6,'p');
INSERT INTO `D` VALUES (47,7,4,'e');
INSERT INTO `D` VALUES (48,8,NULL,'g');
INSERT INTO `D` VALUES (49,NULL,8,'x');
INSERT INTO `D` VALUES (50,6,0,'s');
INSERT INTO `D` VALUES (51,5,8,'e');
INSERT INTO `D` VALUES (52,2,151,'l');
INSERT INTO `D` VALUES (53,3,7,'p');
INSERT INTO `D` VALUES (54,7,6,'h');
INSERT INTO `D` VALUES (55,NULL,NULL,'m');
INSERT INTO `D` VALUES (56,145,23,'n');
INSERT INTO `D` VALUES (57,0,2,'v');
INSERT INTO `D` VALUES (58,1,4,'b');
INSERT INTO `D` VALUES (59,7,NULL,'x');
INSERT INTO `D` VALUES (60,3,NULL,'r');
INSERT INTO `D` VALUES (61,NULL,77,'t');
INSERT INTO `D` VALUES (62,2,NULL,'w');
INSERT INTO `D` VALUES (63,2,NULL,'w');
INSERT INTO `D` VALUES (64,2,7,'k');
INSERT INTO `D` VALUES (65,8,1,'a');
INSERT INTO `D` VALUES (66,6,9,'t');
INSERT INTO `D` VALUES (67,1,6,'z');
INSERT INTO `D` VALUES (68,NULL,2,'e');
INSERT INTO `D` VALUES (69,1,3,'q');
INSERT INTO `D` VALUES (70,0,0,'e');
INSERT INTO `D` VALUES (71,4,NULL,'v');
INSERT INTO `D` VALUES (72,1,6,'d');
INSERT INTO `D` VALUES (73,1,3,'u');
INSERT INTO `D` VALUES (74,27,195,'o');
INSERT INTO `D` VALUES (75,4,5,'b');
INSERT INTO `D` VALUES (76,6,2,'c');
INSERT INTO `D` VALUES (77,2,7,'q');
INSERT INTO `D` VALUES (78,248,25,NULL);
INSERT INTO `D` VALUES (79,NULL,NULL,'h');
INSERT INTO `D` VALUES (80,9,0,'d');
INSERT INTO `D` VALUES (81,75,98,'w');
INSERT INTO `D` VALUES (82,2,6,'m');
INSERT INTO `D` VALUES (83,9,5,'i');
INSERT INTO `D` VALUES (84,4,0,'w');
INSERT INTO `D` VALUES (85,0,3,'f');
INSERT INTO `D` VALUES (86,0,1,'k');
INSERT INTO `D` VALUES (87,1,1,'v');
INSERT INTO `D` VALUES (88,119,147,'c');
INSERT INTO `D` VALUES (89,1,3,'y');
INSERT INTO `D` VALUES (90,7,3,'h');
INSERT INTO `D` VALUES (91,2,NULL,NULL);
INSERT INTO `D` VALUES (92,7,2,'t');
INSERT INTO `D` VALUES (93,2,1,'l');
INSERT INTO `D` VALUES (94,6,8,'a');
INSERT INTO `D` VALUES (95,4,8,'r');
INSERT INTO `D` VALUES (96,5,8,'s');
INSERT INTO `D` VALUES (97,7,0,'z');
INSERT INTO `D` VALUES (98,1,1,'j');
INSERT INTO `D` VALUES (99,7,8,'c');
INSERT INTO `D` VALUES (100,2,5,'f');
CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_nokey` int(11) DEFAULT NULL,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_key` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL,2,'w');
INSERT INTO `C` VALUES (2,7,9,'m');
INSERT INTO `C` VALUES (3,9,3,'m');
INSERT INTO `C` VALUES (4,7,9,'k');
INSERT INTO `C` VALUES (5,4,NULL,'r');
INSERT INTO `C` VALUES (6,2,9,'t');
INSERT INTO `C` VALUES (7,6,3,'j');
INSERT INTO `C` VALUES (8,8,8,'u');
INSERT INTO `C` VALUES (9,NULL,8,'h');
INSERT INTO `C` VALUES (10,5,53,'o');
INSERT INTO `C` VALUES (11,NULL,0,NULL);
INSERT INTO `C` VALUES (12,6,5,'k');
INSERT INTO `C` VALUES (13,188,166,'e');
INSERT INTO `C` VALUES (14,2,3,'n');
INSERT INTO `C` VALUES (15,1,0,'t');
INSERT INTO `C` VALUES (16,1,1,'c');
INSERT INTO `C` VALUES (17,0,9,'m');
INSERT INTO `C` VALUES (18,9,5,'y');
INSERT INTO `C` VALUES (19,NULL,6,'f');
INSERT INTO `C` VALUES (20,4,2,'d');

PREPARE st1 FROM "
SELECT table1 .`pk` field3  , (
SELECT `col_int_nokey`
FROM C
WHERE (  7  ,  6  )  IN (
SELECT `pk`  , `col_int_nokey`
FROM C  )  ) field9
FROM CC table1  JOIN D table2  ON table2 .`col_varchar_key`  = table1 .`col_varchar_key`
WHERE table2 .`pk`  =  8
GROUP  BY field3  , field9   ";

EXECUTE st1;
EXECUTE st1;

Changed in maria:
importance:	Undecided → High
milestone:	none → 5.3

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2011-02-26:

Still crashes in maria-5.3 and not in maria-5.3-mwl89

simplified test case:

CREATE TABLE t1 ( f1 int(11), f2 int(11)) ;

CREATE TABLE t2 ( f1 int(11), f4 varchar(1), PRIMARY KEY (f1)) ;
INSERT IGNORE INTO t2 VALUES ('23','j'),('24','e');

CREATE TABLE t3 ( f1 int(11), f4 varchar(1)) ;
INSERT IGNORE INTO t3 VALUES ('8','j');

SET SESSION optimizer_switch='semijoin=off';

PREPARE st1 FROM "
SELECT t2.f1, (
SELECT f2 FROM t1
WHERE ( 7 ) IN ( SELECT f1 FROM t1 )
)
FROM t2
JOIN t3 ON t3.f4 = t2.f4
WHERE t3.f1 = 8
GROUP BY 1 , 2
";
EXECUTE st1;
EXECUTE st1;

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2011-03-02:

Not repeatable with materialization=off . So, assigning to Timour.

Changed in maria:
assignee:	nobody → Timour Katchaounov (timour)
summary:	RQG: crash in Item_field::register_field_in_read_map with semijoin=off - and prepared statements + and prepared statements and materialization

Timour Katchaounov (timour) on 2011-03-08

Changed in maria:
status:	New → Confirmed
importance:	High → Wishlist

Revision history for this message

Timour Katchaounov (timour) wrote on 2011-03-08:

Since the bug is not present in 5.3-mwl89, I reduce the priority.

The most likely reason there is no crash in mwl89 is that in mwl89
all materialization-related transformations/initializations are
repeated for each re-execution (and are not stored in PS memory).

Timour Katchaounov (timour) on 2011-07-08

Changed in maria:
status:	Confirmed → Fix Committed

Daniel Bartholomew (dbart) on 2011-12-13

Changed in maria:
status:	Fix Committed → Fix Released

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.