Assertion `cp + len <= buff + buff_size' failed in JOIN_CACHE::write_record_data with derived_merge=ON

[Philip Stoev]

The following query:

SELECT t2.d
FROM t1, t2 , (
        SELECT t3.*
        FROM t3, t4, t5
) AS alias3
WHERE alias3.e = t2.d ;

asserts when run with derived_merge=on. The required dataset is somewhat larger, which points to some sort of join cache overflow. Valgrind does not report any warnings.

Assertion:

mysqld: sql_join_cache.cc:1378: uint JOIN_CACHE::write_record_data(uchar*, bool*): Assertion `cp + len <= buff + buff_size' failed.

backtrace:

#8 0x006b0d98 in __assert_fail () from /lib/libc.so.6
#9 0x082f1e47 in JOIN_CACHE::write_record_data (this=0xae535e78, link=0x0, is_full=0xae8dfceb) at sql_join_cache.cc:1378
#10 0x082f22f6 in JOIN_CACHE::put_record (this=0xae535e78) at sql_join_cache.cc:1530
#11 0x0833d6ce in sub_select_cache (join=0xae53e598, join_tab=0xae532128, end_of_records=false) at sql_select.cc:14900
#12 0x082f35af in JOIN_CACHE::generate_full_extensions (this=0xae535d30, rec_ptr=0xae56a608 "\001d\004") at sql_join_cache.cc:2356
#13 0x082f3307 in JOIN_CACHE::join_matching_records (this=0xae535d30, skip_last=false) at sql_join_cache.cc:2248
#14 0x082f2dc7 in JOIN_CACHE::join_records (this=0xae535d30, skip_last=false) at sql_join_cache.cc:2045
#15 0x0833d604 in sub_select_cache (join=0xae53e598, join_tab=0xae531f24, end_of_records=true) at sql_select.cc:14887
#16 0x0833d81c in sub_select (join=0xae53e598, join_tab=0xae531d20, end_of_records=true) at sql_select.cc:15049
#17 0x0833d630 in sub_select_cache (join=0xae53e598, join_tab=0xae531d20, end_of_records=true) at sql_select.cc:14889
#18 0x0833d81c in sub_select (join=0xae53e598, join_tab=0xae531b1c, end_of_records=true) at sql_select.cc:15049
#19 0x0833d81c in sub_select (join=0xae53e598, join_tab=0xae531918, end_of_records=true) at sql_select.cc:15049
#20 0x0833d30b in do_select (join=0xae53e598, fields=0x9dfd1b4, table=0x0, procedure=0x0) at sql_select.cc:14771
#21 0x08321c11 in JOIN::exec (this=0xae53e598) at sql_select.cc:2679
#22 0x0832243e in mysql_select (thd=0x9dfb6e0, rref_pointer_array=0x9dfd25c, tables=0xae5130a0, wild_num=0, fields=..., conds=0xae514b38, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xae514cb0, unit=0x9dfce40, select_lex=0x9dfd120)
    at sql_select.cc:2900
#23 0x0831a20f in handle_select (thd=0x9dfb6e0, lex=0x9dfcde4, result=0xae514cb0, setup_tables_done_option=0) at sql_select.cc:283
#24 0x082b49dc in execute_sqlcom_select (thd=0x9dfb6e0, all_tables=0xae5130a0) at sql_parse.cc:5112
#25 0x082ab799 in mysql_execute_command (thd=0x9dfb6e0) at sql_parse.cc:2250
#26 0x082b700b in mysql_parse (thd=0x9dfb6e0,
    rawbuf=0xae512ec0 "SELECT t2.d\nFROM t1, t2 , (\nSELECT t3.*\nFROM t3, t4, t5 \n) AS alias3\nWHERE alias3.e = t2.d", length=90,
    found_semicolon=0xae8e1228) at sql_parse.cc:6112
#27 0x082a93e8 in dispatch_command (command=COM_QUERY, thd=0x9dfb6e0, packet=0x9e54321 "", packet_length=91) at sql_parse.cc:1221
#28 0x082a8843 in do_command (thd=0x9dfb6e0) at sql_parse.cc:916
#29 0x082a5827 in handle_one_connection (arg=0x9dfb6e0) at sql_connect.cc:1191
#30 0x00821919 in start_thread () from /lib/libpthread.so.0
#31 0x0076acce in clone () from /lib/libc.so.6

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3
1 SIMPLE t3 ref e e 3 test.t2.d 1 Using index
1 SIMPLE t4 ALL NULL NULL NULL NULL 17 Using join buffer (flat, BNL join)
1 SIMPLE t1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
1 SIMPLE t5 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)

minimal optimizer switch: derived_merge=on;
full optimizer_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=on,derived_merge=on,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=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-10-14 03:56:41 -0700
build-date: 2011-10-14 19:00:28 +0300
revno: 3235
branch-nick: maria-5.3

test case:

CREATE TABLE t1 ( d varchar(1) NOT NULL) ;
INSERT INTO t1 VALUES ('j'),('v'),('c'),('m'),('d'),('d'),('y'),('t'),('d'),('s'),('r'),('m'),('b'),('x'),('g'),('p'),('q'),('w'),('d'),('e');

CREATE TABLE t2 ( h time NOT NULL, d varchar(1) NOT NULL) ;
INSERT INTO t2 VALUES ('05:03:03','w'),('02:59:24','d'),('00:01:58','e');

CREATE TABLE t3 ( b int NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL, KEY (e,b)) ;
INSERT INTO t3 VALUES (4,'x','x'),(7,'g','g'),(4,'p','p'),(1,'q','q'),(9,'w','w'),(4,'d','d'),(8,'e','e');

CREATE TABLE t4 ( b int NOT NULL, e varchar(1) NOT NULL) ;
INSERT INTO t4 VALUES (8,'m'),(9,'d'),(24,'d'),(6,'y'),(1,'t'),(6,'d'),(2,'s'),(4,'r'),(8,'m'),(4,'b'),(4,'x'),(7,'g'),(4,'p'),(1,'q'),(9,'w'),(4,'d'),(8,'e');

CREATE TABLE t5 ( a int NOT NULL, c int NOT NULL, b int NOT NULL, f date NOT NULL, g date NOT NULL, h time NOT NULL, j time NOT NULL, k datetime NOT NULL, l datetime NOT NULL, e varchar(1) NOT NULL, d varchar(1) NOT NULL) ;
INSERT INTO t5 VALUES (1,4,0,'0000-00-00','0000-00-00','21:22:34','21:22:34','2002-02-13 17:30','2002-02-13 17:30','j','j'),(2,6,8,'2004-09-18','2004-09-18','10:50:38','10:50:38','2008-09-27 00:34','2008-09-27 00:34','v','v'),(3,3,1,'2009-12-01','2009-12-01','00:21:38','00:21:38','2007-05-28 00:00','2007-05-28 00:00','c','c'),(4,5,8,'2004-12-17','2004-12-17','04:08:02','04:08:02','2009-07-25 09:21','2009-07-25 09:21','m','m'),(5,3,9,'2000-03-14','2000-03-14','16:25:11','16:25:11','2002-01-16 00:00','2002-01-16 00:00','d','d'),(6,246,24,'2000-10-08','2000-10-08','10:14:58','10:14:58','2006-10-12 04:32','2006-10-12 04:32','d','d'),(7,2,6,'2006-05-25','2006-05-25','19:47:59','19:47:59','2001-02-15 03:08','2001-02-15 03:08','y','y'),(8,9,1,'2008-01-23','2008-01-23','11:14:24','11:14:24','2004-10-02 20:31','2004-10-02 20:31','t','t'),(9,3,6,'2007-06-18','2007-06-18','00:00:00','00:00:00','2002-08-20 22:48','2002-08-20 22:48','d','d'),(10,8,2,'2002-10-13','2002-10-13','00:00:00','00:00:00','1900-01-01 00:00','1900-01-01 00:00','s','s'),(11,1,4,'1900-01-01','1900-01-01','15:57:25','15:57:25','2005-08-15 00:00','2005-08-15 00:00','r','r'),(12,8,8,'0000-00-00','0000-00-00','07:05:51','07:05:51','1900-01-01 00:00','1900-01-01 00:00','m','m'),(13,8,4,'2006-03-09','2006-03-09','19:22:21','19:22:21','2008-05-16 08:09','2008-05-16 08:09','b','b'),(14,5,4,'2001-06-05','2001-06-05','03:53:16','03:53:16','2001-01-20 12:47','2001-01-20 12:47','x','x'),(15,7,7,'2006-05-28','2006-05-28','09:16:38','09:16:38','2008-07-02 00:00','2008-07-02 00:00','g','g'),(16,5,4,'2001-04-19','2001-04-19','15:37:26','15:37:26','1900-01-01 00:00','1900-01-01 00:00','p','p'),(17,1,1,'1900-01-01','1900-01-01','00:00:00','00:00:00','2002-12-08 11:34','2002-12-08 11:34','q','q'),(18,6,9,'2004-08-20','2004-08-20','05:03:03','05:03:03','1900-01-01 00:00','1900-01-01 00:00','w','w'),(19,2,4,'2004-10-10','2004-10-10','02:59:24','02:59:24','1900-01-01 00:00','1900-01-01 00:00','d','d'),(20,9,8,'2000-04-02','2000-04-02','00:01:58','00:01:58','2002-08-25 20:35','2002-08-25 20:35','e','e');

SET SESSION optimizer_switch='derived_merge=ON';

SELECT t2.d
FROM t1, t2 , (
        SELECT t3.*
        FROM t3, t4, t5
) AS alias3
WHERE alias3.e = t2.d ;