MariaDB

Second assertion `keypart_map' failed in maria_rkey with semijoin

Bug #887468 reported by Philip Stoev on 2011-11-08

This bug affects 1 person

Affects		Status	Importance	Assigned to	Milestone
	MariaDB	Fix Released	Critical	Sergey Petrunia	MariaDB 5.3

Bug Description

Even with the fix for bug 860535

this query:

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
SELECT t3.col_varchar_key FROM t3, t4
);

asserts as follows:

mysqld: ma_rkey.c:69: maria_rkey: Assertion `keypart_map' failed.

#5 0x00000035a6e340d5 in abort () from /lib64/libc.so.6
#6 0x00000035a6e2b8b5 in __assert_fail () from /lib64/libc.so.6
#7 0x00000000009e6625 in maria_rkey (info=0x7f80600f53c8,
    buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", inx=5, key_data=0x7f80600c3990 "", keypart_map=0, search_flag=HA_READ_KEY_EXACT) at ma_rkey.c:69
#8 0x00000000009c4975 in ha_maria::index_read_map (this=0x7f8060041670,
    buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", key=0x7f80600c3990 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT) at ha_maria.cc:2266
#9 0x00000000005a9a36 in handler::ha_index_read_map (this=0x7f8060041670,
    buf=0x7f8060041d70 "\377\377\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217\217", key=0x7f80600c3990 "", keypart_map=0, find_flag=HA_READ_KEY_EXACT) at sql_class.h:3655
#10 0x0000000000766fea in join_read_always_key (tab=0x7f80600c2d10) at sql_select.cc:15803
#11 0x000000000076557d in sub_select (join=0x7f80600b5c70, join_tab=0x7f80600c2d10, end_of_records=false) at sql_select.cc:15129
#12 0x0000000000764d52 in do_select (join=0x7f80600b5c70, fields=0x254bca8, table=0x0, procedure=0x0) at sql_select.cc:14795
#13 0x0000000000744f90 in JOIN::exec (this=0x7f80600b5c70) at sql_select.cc:2679
#14 0x0000000000745811 in mysql_select (thd=0x2549218, rref_pointer_array=0x254bdf0, tables=0x7f80600188b8, wild_num=1, fields=..., conds=0x7f806001a440,
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x7f80600a9410, unit=0x254b6b8, select_lex=0x254bba0)
    at sql_select.cc:2900
#15 0x000000000073c6c7 in handle_select (thd=0x2549218, lex=0x254b618, result=0x7f80600a9410, setup_tables_done_option=0) at sql_select.cc:283
#16 0x00000000006c88cb in execute_sqlcom_select (thd=0x2549218, all_tables=0x7f80600188b8) at sql_parse.cc:5112
#17 0x00000000006bf62a in mysql_execute_command (thd=0x2549218) at sql_parse.cc:2250
#18 0x00000000006cb417 in mysql_parse (thd=0x2549218,
    rawbuf=0x7f8060018600 "SELECT * \nFROM t1\nJOIN t2\nON ( t2.col_int_key = t1.pk )\nWHERE t1.col_varchar_key IN (\nSELECT t3.col_varchar_key FROM t3, t4\n)", length=126, found_semicolon=0x7f806fffdc68) at sql_parse.cc:6113
#19 0x00000000006bce67 in dispatch_command (command=COM_QUERY, thd=0x2549218, packet=0x25c3f09 "", packet_length=126) at sql_parse.cc:1221
#20 0x00000000006bc1e7 in do_command (thd=0x2549218) at sql_parse.cc:916
#21 0x00000000006b90c3 in handle_one_connection (arg=0x2549218) at sql_connect.cc:1191
#22 0x00000035a7207761 in start_thread () from /lib64/libpthread.so.0
#23 0x00000035a6ee098d in clone () from /lib64/libc.so.6

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ref col_varchar_key NULL NULL NULL 3 Using where; Using index; Start temporary
1 PRIMARY t4 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
1 PRIMARY t1 ref PRIMARY,col_varchar_key col_varchar_key 4 test.t3.col_varchar_key 3 End temporary
1 PRIMARY t2 ref col_int_key col_int_key 5 test.t1.pk 3

minimal switch: semijoin=on?
full switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=on,loosescan=on,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

bzr version-info:
revision-id: <email address hidden>
date: 2011-11-07 16:39:02 +0400
build-date: 2011-11-08 11:03:47 +0200
revno: 3273
branch-nick: maria-5.3

Revision history for this message

Philip Stoev (pstoev-askmonty) wrote on 2011-11-08:

Download full text (15.9 KiB)

Test case. Due to the instability of the plan and the impossibility to force particular semijoin plans, further simplification was not possible.

CREATE TABLE t1 ( 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_date_nokey date DEFAULT NULL, col_time_key time DEFAULT NULL, col_time_nokey time DEFAULT NULL, col_datetime_key datetime DEFAULT NULL, col_datetime_nokey datetime DEFAULT NULL, col_varchar_key varchar(1) DEFAULT NULL, col_varchar_nokey varchar(1) DEFAULT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_date_key (col_date_key), KEY col_time_key (col_time_key), KEY col_datetime_key (col_datetime_key), KEY col_varchar_key (col_varchar_key,col_int_key)) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,2,0,'2002-10-20','2002-10-20','03:47:16','03:47:16',NULL,NULL,NULL,NULL),(11,5,4,'2008-09-12','2008-09-12','01:41:48','01:41:48','1900-01-01 00:00:00','1900-01-01 00:00:00','d','d'),(12,7,8,'2006-06-16','2006-06-16','00:00:00','00:00:00',NULL,NULL,'g','g'),(13,6,NULL,'2004-09-18','2004-09-18','22:32:04','22:32:04','2007-08-22 14:10:22','2007-08-22 14:10:22','x','x'),(14,6,NULL,'1900-01-01','1900-01-01','16:44:14','16:44:14','2005-01-07 12:15:05','2005-01-07 12:15:05','f','f'),(15,2,0,'2005-09-13','2005-09-13','17:38:37','17:38:37','2008-07-18 00:00:00','2008-07-18 00:00:00','p','p'),(16,9,NULL,'2007-04-09','2007-04-09','08:46:48','08:46:48','2005-03-24 07:33:11','2005-03-24 07:33:11','j','j'),(17,6,8,'2000-09-20','2000-09-20','14:11:27','14:11:27','2003-06-13 23:19:49','2003-06-13 23:19:49','c','c'),(18,0,8,'1900-01-01','1900-01-01','07:55:54','07:55:54','2008-11-09 06:28:05','2008-11-09 06:28:05','z','z'),(19,3,6,NULL,NULL,'14:18:47','14:18:47','2009-02-11 10:59:50','2009-02-11 10:59:50','j','j'),(20,8,2,'2009-09-06','2009-09-06','00:00:00','00:00:00','2000-02-23 00:39:41','2000-02-23 00:39:41',NULL,NULL),(21,8,3,'2008-08-23','2008-08-23','01:57:54','01:57:54','1900-01-01 00:00:00','1900-01-01 00:00:00','p','p'),(22,6,1,'2009-03-24','2009-03-24','21:24:27','21:24:27','2009-07-03 13:47:15','2009-07-03 13:47:15','w','w'),(23,0,NULL,'2009-11-15','2009-11-15',NULL,NULL,'2001-04-11 12:39:06','2001-04-11 12:39:06','c','c'),(24,NULL,1,'2001-12-23','2001-12-23','21:34:24','21:34:24','2007-01-26 15:51:38','2007-01-26 15:51:38','j','j'),(25,158,10,'2007-02-11','2007-02-11','18:11:35','18:11:35','2003-04-27 19:48:50','2003-04-27 19:48:50','f','f'),(26,5,2,'2005-04-16','2005-04-16','09:21:51','09:21:51','2001-11-15 10:51:46','2001-11-15 10:51:46','v','v'),(27,163,103,'2007-05-12','2007-05-12','09:02:21','09:02:21','1900-01-01 00:00:00','1900-01-01 00:00:00','f','f'),(28,2,3,'2000-07-20','2000-07-20','22:40:03','22:40:03','2000-10-20 17:09:41','2000-10-20 17:09:41','q','q'),(29,8,6,'2008-08-14','2008-08-14','19:29:48','19:29:48','2005-10-03 00:00:00','2005-10-03 00:00:00','y','y');

CREATE TABLE t2 ( 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_date_nokey date DEFAULT NULL, col_tim...

Changed in maria:
milestone:	none → 5.3
assignee:	nobody → Sergey Petrunia (sergefp)

Sergey Petrunia (sergefp) on 2011-11-11

Changed in maria:
importance:	Undecided → Critical
status:	New → Confirmed

Revision history for this message

Sergey Petrunia (sergefp) wrote on 2011-11-25:

CREATE TABLE t1 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_varchar_key varchar(1) DEFAULT NULL,
  dummy char(30),
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t1
  (
  pk,
  col_varchar_key,
  col_int_key
)

VALUES
  (10,NULL,0),
  (11,'d',4),
  (12,'g',8),
  (13,'x',NULL),
  (14,'f',NULL),
  (15,'p',0),
  (16,'j',NULL),
  (17,'c',8),
  (18,'z',8),
  (19,'j',6),
  (20,NULL,2),
  (21,'p',3),
  (22,'w',1),
  (23,'c',NULL),
  (24,'j',1),
  (25,'f',10),
  (26,'v',2),
  (27,'f',103),
  (28,'q',3),
  (29,'y',6);

CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  dummy char(36),
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key)
) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t2 ( pk, col_int_key) VALUES
(1,8),
(2,2),
(3,9),
(4,6),
(5,NULL),
(6,NULL),
(7,48),
(8,228),
(9,3),
(10,5),
(11,39),
(12,6),
(13,8),
(14,3),
(15,NULL),
(16,2),
(17,6),
(18,3),
(19,1),
(20,4),
(21,3),
(22,1),
(23,NULL),
(24,97),
(25,0),
(26,0),
(27,9),
(28,5),
(29,9),
(30,0),
(31,2),
(32,172),
(33,NULL),
(34,5),
(35,119),
(36,1),
(37,4),
(38,8),
(39,NULL),
(40,6),
(41,5),
(42,5),
(43,1),
(44,7),
(45,2),
(46,8),
(47,9),
(48,NULL),
(49,NULL),
(50,3),
(51,172),
(52,NULL),
(53,6),
(54,6),
(55,5),
(56,4),
(57,3),
(58,2),
(59,7),
(60,4),
(61,6),
(62,0),
(63,8),
(64,5),
(65,8),
(66,2),
(67,9),
(68,7),
(69,5),
(70,7),
(71,0),
(72,4),
(73,3),
(74,1),
(75,0),
(76,6),
(77,2),
(78,NULL),
(79,8),
(80,NULL),
(81,NULL),
(82,NULL),
(83,3),
(84,7),
(85,3),
(86,5),
(87,5),
(88,1),
(89,2),
(90,1),
(91,7),
(92,1),
(93,9),
(94,9),
(95,8),
(96,3),
(97,7),
(98,4),
(99,9),
(100,0);

CREATE TABLE t3 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  dummy char(34),
  col_varchar_key varchar(1) DEFAULT NULL,
  col_int_key int(11) DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t3 (pk, col_varchar_key) VALUES
(1,'v'),
(2,'c'),
(3,NULL);

CREATE TABLE t4 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  dummy char (38),
  PRIMARY KEY (pk)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t4 (pk) VALUES
(1),
(2),
(3);

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
SELECT t3.col_varchar_key FROM t3, t4
);

CREATE TABLE t1 ( 
  pk int(11) NOT NULL AUTO_INCREMENT, 
  col_int_key int(11) DEFAULT NULL, 
  col_varchar_key varchar(1) DEFAULT NULL, 
  dummy char(30),
  PRIMARY KEY (pk), 
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t1 
  (
  pk,
  col_varchar_key, 
  col_int_key
)

VALUES 
  (10,NULL,0),
  (11,'d',4),
  (12,'g',8),
  (13,'x',NULL),
  (14,'f',NULL),
  (15,'p',0),
  (16,'j',NULL),
  (17,'c',8),
  (18,'z',8),
  (19,'j',6),
  (20,NULL,2),
  (21,'p',3),
  (22,'w',1),
  (23,'c',NULL),
  (24,'j',1),
  (25,'f',10),
  (26,'v',2),
  (27,'f',103),
  (28,'q',3),
  (29,'y',6);

CREATE TABLE t2 ( 
  pk int(11) NOT NULL AUTO_INCREMENT, 
  col_int_key int(11) DEFAULT NULL, 
  dummy char(36),
  PRIMARY KEY (pk), 
  KEY col_int_key (col_int_key)
) ENGINE=Aria AUTO_INCREMENT=101 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

CREATE TABLE t3 ( 
  pk int(11) NOT NULL AUTO_INCREMENT, 
  dummy char(34),
  col_varchar_key varchar(1) DEFAULT NULL, 
  col_int_key int(11) DEFAULT NULL, 
  PRIMARY KEY (pk), 
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t3 (pk, col_varchar_key) VALUES 
(1,'v'),
(2,'c'),
(3,NULL);

CREATE TABLE t4 ( 
  pk int(11) NOT NULL AUTO_INCREMENT, 
  dummy char (38),
  PRIMARY KEY (pk)
) ENGINE=Aria AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;

INSERT INTO t4 (pk) VALUES 
(1),
(2),
(3);

SELECT *
FROM t1
JOIN t2
ON ( t2.col_int_key = t1.pk )
WHERE t1.col_varchar_key IN (
        SELECT t3.col_varchar_key FROM t3, t4
);

Sergey Petrunia (sergefp) on 2011-11-26

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.