sql_join_cache.cc:2770: virtual bool JOIN_CACHE_HASHED::put_record(): Assertion `last_key_entry >= end_pos' failed in maria-5.3-mwl128

[Philip Stoev]

The following query:

SELECT MAX( table1 . `col_int` ) AS field1 FROM M AS table1 LEFT JOIN Y AS table2 LEFT JOIN K AS table3 ON table2 . `col_int_key` = table3 . `pk` ON table1 . `col_varchar_1024_utf8_key` = table3 . `col_varchar_10_latin1_key` LEFT OUTER JOIN Y AS table4 RIGHT JOIN E AS table5 ON table4 . `col_varchar_1024_latin1_key` = table5 . `col_varchar_10_latin1_key` LEFT JOIN Z AS table6 ON table5 . `col_int_key` = table6 . `col_int_key` ON table3 . `col_varchar_10_latin1_key` = table6 . `col_varchar_1024_latin1_key` LEFT OUTER JOIN F AS table7 RIGHT JOIN CC AS table8 RIGHT JOIN I AS table9 ON table8 . `col_varchar_10_utf8_key` = table9 . `col_varchar_1024_utf8` ON table7 . `col_int_key` = table9 . `pk` ON table4 . `pk` = table7 . `col_int_key` LEFT JOIN DD AS table10 LEFT OUTER JOIN EE AS table11 LEFT OUTER JOIN L AS table12 LEFT JOIN T AS table13 ON table12 . `col_int_key` = table13 . `pk` LEFT JOIN N AS table14 LEFT JOIN I AS table15 ON table14

caused the following assertion:

mysqld: sql_join_cache.cc:2770: virtual bool JOIN_CACHE_HASHED::put_record(): Assertion `last_key_entry >= end_pos' failed.

backtrace

# 2010-11-08T13:08:11 #8 0x00a72d98 in __assert_fail () from /lib/libc.so.6
# 2010-11-08T13:08:11 #9 0x082df328 in JOIN_CACHE_HASHED::put_record (this=0xb601850) at sql_join_cache.cc:2770
# 2010-11-08T13:08:11 #10 0x08325e7f in sub_select_cache (join=0xb843f10, join_tab=0xb87dd70, end_of_records=false) at sql_select.cc:13226
# 2010-11-08T13:08:11 #11 0x08326804 in evaluate_join_record (join=0xb843f10, join_tab=0xb87db98, error=0) at sql_select.cc:13620
# 2010-11-08T13:08:11 #12 0x08326190 in sub_select (join=0xb843f10, join_tab=0xb87db98, end_of_records=false) at sql_select.cc:13425
# 2010-11-08T13:08:11 #13 0x083255b5 in do_select (join=0xb843f10, fields=0xb848cdc, table=0x0, procedure=0x0) at sql_select.cc:12969
# 2010-11-08T13:08:11 #14 0x0830b049 in JOIN::exec (this=0xb843f10) at sql_select.cc:2384
# 2010-11-08T13:08:11 #15 0x0830b7af in mysql_select (thd=0xb6a6520, rref_pointer_array=0xb6a7fd8, tables=0xb7586a8, wild_num=0, fields=..., conds=0xb8b5b90, og_num=0, order=0x0,
# 2010-11-08T13:08:11 group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0xb8b5e40, unit=0xb6a7c0c, select_lex=0xb6a7eb0) at sql_select.cc:2594
# 2010-11-08T13:08:11 #16 0x08303dcd in handle_select (thd=0xb6a6520, lex=0xb6a7bac, result=0xb8b5e40, setup_tables_done_option=0) at sql_select.cc:286
# 2010-11-08T13:08:11 #17 0x0829fbd3 in execute_sqlcom_select (thd=0xb6a6520, all_tables=0xb7586a8) at sql_parse.cc:5102
# 2010-11-08T13:08:11 #18 0x082966d3 in mysql_execute_command (thd=0xb6a6520) at sql_parse.cc:2281
# 2010-11-08T13:08:11 #19 0x082a2144 in mysql_parse (thd=0xb6a6520,
# 2010-11-08T13:08:11 rawbuf=0xb7166a0 "SELECT MAX( table1 . `col_int` ) AS field1 FROM M AS table1 LEFT JOIN Y AS table2 LEFT JOIN K AS table3 ON table2 . `col_int_key` = table3 . `pk` ON table1 . `col_varchar_1024_utf8_key` = table3 . `col_varchar_10_latin1_key` LEFT OUTER JOIN Y AS table4 RIGHT JOIN E AS table5 ON table4 . `col_varchar_1024_latin1_key` = table5 . `col_varchar_10_latin1_key` LEFT JOIN Z AS table6 ON table5 . `col_int_key` = table6 . `col_int_key` ON table3 . `col_varchar_10_latin1_key` = table6 . `col_varchar_1024_latin1_key` LEFT OUTER JOIN F AS table7 RIGHT JOIN CC AS table8 RIGHT JOIN I AS table9 ON table8 . `col_varchar_10_utf8_key` = table9 . `col_varchar_1024_utf8` ON table7 . `col_int_key` = table9 . `pk` ON table4 . `pk` = table7 . `col_int_key` LEFT JOIN DD AS table10 LEFT OUTER JOIN EE AS table11 LEFT OUTER JOIN L AS table12 LEFT JOIN T AS table13 ON table12 . `col_int_key` = table13 . `pk` LEFT JOIN N AS table14 LEFT JOIN I AS table15 ON table14 . `col_int_key` = table15 . `col_int_key` ON table13 . `col_int_key` = table15 . `col_int_key` LEFT JOIN L AS table16 ON table13 . `col_varchar_1024_utf8_key` = table16 . `col_varchar_10_utf8_key` ON table11 . `pk` = table13 . `pk` ON table10 . `col_int` = table13 . `pk` ON table5 . `col_int` = table16 . `pk` WHERE ( ( ( table11 . `col_int` < table13 . `col_int` AND table2 . `col_int_key` IN (2, 7, 4) ) OR table2 . `col_int_key` <> 8 ) AND table6 . `col_int` IS NOT NULL )", length=1517,
# 2010-11-08T13:08:11 found_semicolon=0xa74c1228) at sql_parse.cc:6109
# 2010-11-08T13:08:11 #20 0x08294240 in dispatch_command (command=COM_QUERY, thd=0xb6a6520, packet=0xb6a88e1 "", packet_length=1523) at sql_parse.cc:1209
# 2010-11-08T13:08:11 #21 0x082936f2 in do_command (thd=0xb6a6520) at sql_parse.cc:902
# 2010-11-08T13:08:11 #22 0x082906a8 in handle_one_connection (arg=0xb6a6520) at sql_connect.cc:1154
# 2010-11-08T13:08:11 #23 0x00bea919 in start_thread () from /lib/libpthread.so.0
# 2010-11-08T13:08:11 #24 0x00b2ccbe in clone () from /lib/libc.so.6

[Philip Stoev (pstoev-askmonty)]

To reproduce the bug, place the attached vardir in mysql-test/ and then run

lib/v1/mysql-test-run.pl --mysqld=--innodb --mysqld=--join_cache_level=4 --mysqld=--join_buffer_size=1 --mysqld=--log-output=file --start-and-exit --start-dirty --vardir=/home/philips/bzr/maria-5.3-mwl128/mysql-test/var --master_port=19300 --skip-ndbcluster --mysqld=--loose-core-file-size=1 --fast 1st

This will start a server against which you can execute the original crashing query. Try it several times (including the server restart) if it does not work outright.

Due to the sporadic nature of this failure, automatic simplification was not possible. Valgrind only reports innodb-related warnings. Bug was not reproducible as an MTR test case or by using MyISAM only tables.

[Philip Stoev (pstoev-askmonty)]

MTR test case:

--disable_abort_on_error
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_abcdefjhjkl=on,index_condition_pushdown=on,abcdefjhjkl=on,loosescan=on,abcdefjhjkl=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_abcdefjhjkl=on,join_cache_hashed=on,join_cache_bka=on,table_abcdefjhjkl=on';
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 136;
SET SESSION debug = '';
--enable_abort_on_error

# Begin test case for query 0

--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
DROP TABLE /*! IF EXISTS */ t3;
DROP TABLE /*! IF EXISTS */ t4;
DROP TABLE /*! IF EXISTS */ t5;
DROP TABLE /*! IF EXISTS */ t6;
DROP TABLE /*! IF EXISTS */ t7;
--enable_warnings

CREATE TABLE t1 (
  col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) DEFAULT NULL,
  col_int int(11) DEFAULT NULL,
  col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (pk),
  KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(402)),
  KEY col_int_key (col_int_key),
  KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
  KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key),
  KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=Aria AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES ('did',1,3,8,'s','abcdefjhjkl','e'),('and',2,8,-76742656,'abcdefjhjkl','why','why'),('abcdefjhjkl',3,1399324672,1245511680,'will','abcdefjhjkl','you'),('abcdefjhjkl',4,8,7,'me','a','up'),('i',5,NULL,7,'could','m','t'),('abcdefjhjkl',6,0,NULL,'that','abcdefjhjkl','abcdefjhjkl'),('p',7,NULL,7,'abcdefjhjkl','w','abcdefjhjkl'),('a',8,NULL,4,'abcdefjhjkl','v','I\'m'),('for',9,-2120417280,7,'o','not','o'),('f',10,-793182208,1382219776,'can','were','o'),('tell',11,NULL,5,'abcdefjhjkl','her','abcdefjhjkl'),('f',12,NULL,-498860032,'abcdefjhjkl','w','abcdefjhjkl'),('a',13,NULL,1,'right','up','did'),('abcdefjhjkl',14,5,1211891712,'abcdefjhjkl','abcdefjhjkl','in');
INSERT INTO t1 VALUES ('as',15,5,8,'v','abcdefjhjkl','I'),('the',16,232587264,6,'well','abcdefjhjkl','as'),('abcdefjhjkl',17,9,1,'abcdefjhjkl','abcdefjhjkl','have'),('something',19,9,-242483200,'y','abcdefjhjkl','h'),('i',20,2050818048,6,'want','abcdefjhjkl','p');
INSERT INTO t1 VALUES ('abcdefjhjkl',18,-439091200,-15204352,'really','abcdefjhjkl','b');
CREATE TABLE t2 (
  col_int_key int(11) DEFAULT NULL,
  col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  col_varchar...

[Igor Babaev (igorb-seattle)]

This assertion failure happens only with tiny join buffers when due to a miscalculation of the the minimal possible size of the
buffer there is not enough space even for one record in it.

Here is a simplified version of the test case for the bug:

CREATE TABLE t1 (
  pk int PRIMARY KEY,
  v varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  INDEX idx (v)
);
INSERT INTO t1 VALUES
  (1,'abcdefjhjk'), (2,'i'),(3,'abcdefjhjk'), (4,'well'), (5,'abcdefjhjk'),
  (6,'abcdefjhjk'), (7,'that');

CREATE TABLE t2 (
  pk int PRIMARY KEY,
  i int DEFAULT NULL,
  v varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  INDEX idx (v)
);
INSERT INTO t2 VALUES
 (1,6,'yes'), (2,NULL,'will'), (3,NULL,'o'), (4,NULL,'k'), (5,NULL,'she'),
 (6,-1450835968,'abcdefjhjkl'), (7,-975831040,'abcdefjhjkl'), (8,NULL,'z'),
 (10,-343932928,'t'),
 (11,6,'yes'), (12,NULL,'will'), (13,NULL,'o'), (14,NULL,'k'), (15,NULL,'she'),
 (16,-1450835968,'abcdefjhjkl'), (17,-975831040,'abcdefjhjkl'), (18,NULL,'z'),
 (19,-343932928,'t');

CREATE TABLE t3 (
  pk int NOT NULL PRIMARY KEY,
  i int,
  v varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  INDEX idx (v(333))
);
INSERT INTO t3 VALUES
(1,7,'abcdefjhjkl'),(2,6,'y'), (3,NULL,'to'),(4,7,'n'),(5,7,'look'), (6,NULL,'all'),
(7,1443168256,'c'), (8,1427046400,'right'),
(11,7,'abcdefjhjkl'), (12,6,'y'), (13,NULL,'to'), (14,7,'n'), (15,7,'look'),
(16,NULL,'all'), (17,1443168256,'c'), (18,1427046400,'right'),
(21,7,'abcdefjhjkl'), (22,6,'y'), (23,NULL,'to'), (24,7,'n'), (25,7,'look'),
(26,NULL,'all'), (27,1443168256,'c'), (28,1427046400,'right'),
(31,7,'abcdefjhjkl'), (32,6,'y'), (33,NULL,'to'), (34,7,'n'), (35,7,'look'),
(36,NULL,'all'), (37,1443168256,'c'), (38,1427046400,'right');

SET SESSION join_cache_level = 4;
SET SESSION join_buffer_size = 192;

EXPLAIN
SELECT t3.i FROM t1,t2,t3
  WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;
SELECT t3.i FROM t1,t2,t3
  WHERE t1.v = t2.v AND t3.v = t1.v AND t2.i <> 0;

SET SESSION join_cache_level = DEFAULT;
SET SESSION join_buffer_size = DEFAULT;

DROP TABLE t1,t2,t3;