|
Re: sql_join_cache.cc:2770: virtual bool JOIN_CACHE_HASHED::put_record(): Assertion `last_key_entry >= end_pos' failed in maria-5.3-mwl128
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_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
col_int int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333)),
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000))
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (-636289024,'abcdefjhjkl',1,'like','was','g',-1908473856),(6,'i',2,'right','n','well',9),(8,'abcdefjhjkl',3,'abcdefjhjkl','on','abcdefjhjkl',84738048),(2,'well',4,'abcdefjhjkl','o','abcdefjhjkl',-1675952128),(1,'abcdefjhjkl',5,'with','b','p',NULL),(-1377239040,'abcdefjhjkl',6,'s','good','y',NULL),(317980672,'that\'s',7,'don\'t','good','n',NULL);
CREATE TABLE t3 (
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333)),
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000)),
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO t3 VALUES ('abcdefjhjkl',1,'me',4,'be',7,'l'),('right',2,'u',3,'abcdefjhjkl',2108620800,'x');
CREATE TABLE t4 (
col_int int(11) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000)),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333))
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
INSERT INTO t4 VALUES (-297795584,NULL,'c',1,'abcdefjhjkl','abcdefjhjkl','yes'),(1107623936,0,'r',2,'some','did','r'),(0,NULL,'abcdefjhjkl',3,'askweu','v','abcdefjhjkl'),(9,NULL,'something',4,'the','o','j'),(NULL,1532166144,'you',5,'go','k','d'),(2,3,'r',6,'w','s','my'),(NULL,160628736,'abcdefjhjkl',7,'q','up','b'),(NULL,0,'abcdefjhjkl',8,'r','some','abcdefjhjkl'),(6,252051456,'and',9,'got','then','abcdefjhjkl'),(404094976,5,'q',10,'abcdefjhjkl','x','abcdefjhjkl'),(1594359808,NULL,'f',11,'just','were','o');
INSERT INTO t4 VALUES (-964624384,NULL,'abcdefjhjkl',12,'abcdefjhjkl','abcdefjhjkl','f'),(2,NULL,'right',13,'just','him','t'),(7,NULL,'abcdefjhjkl',14,'well','abcdefjhjkl','don\'t'),(NULL,0,'e',15,'from','abcdefjhjkl','abcdefjhjkl'),(3,2055602176,'abcdefjhjkl',16,'look','abcdefjhjkl','i'),(NULL,-507314176,'I\'m',17,'abcdefjhjkl','r','k'),(892207104,6,'a',18,'were','to','go'),(4,NULL,'a',19,'i','abcdefjhjkl','n');
CREATE TABLE t5 (
col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
col_int int(11) DEFAULT NULL,
col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
pk int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(402)),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
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)
) ENGINE=Aria AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t5 VALUES ('abcdefjhjkl','m',5,'abcdefjhjkl',6,'yes',1),('abcdefjhjkl','here',526516224,'abcdefjhjkl',NULL,'I\'ll',2),('is','abcdefjhjkl',NULL,'abcdefjhjkl',NULL,'o',3),('abcdefjhjkl','it\'s',1243480064,'w',NULL,'k',4),('abcdefjhjkl','c',-1019805696,'abcdefjhjkl',NULL,'she',5),('if','not',5,'now',-1450835968,'abcdefjhjkl',6),('b','l',8,'abcdefjhjkl',-975831040,'abcdefjhjkl',7),('w','vmai',-1776025600,'so',NULL,'z',8),('would','good',8,'t',-343932928,'t',10);
CREATE TABLE t6 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_int int(11) 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,
col_varchar_1024_utf8_key varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_int_key (col_int_key),
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key(1000)),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(333))
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
INSERT INTO t6 VALUES (1,'abcdefjhjkl',7,3,'t','abcdefjhjkl','abcdefjhjkl'),(2,'abcdefjhjkl',6,NULL,'j','abcdefjhjkl','y'),(3,'v',NULL,NULL,'her','z','to'),(4,'yeah',7,-1255211008,'a','b','n'),(5,'abcdefjhjkl',7,NULL,'were','gpr','look'),(6,'e',NULL,NULL,'v','abcdefjhjkl','all'),(7,'abcdefjhjkl',1443168256,0,'that\'s','was','c'),(8,'time',1427046400,NULL,'out','I\'m','right');
CREATE TABLE t7 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int int(11) DEFAULT NULL,
col_varchar_1024_latin1_key varchar(1024) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
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_int_key int(11) DEFAULT NULL,
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key),
KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key),
KEY col_varchar_1024_utf8_key (col_varchar_1024_utf8_key(402)),
KEY col_int_key (col_int_key),
KEY col_varchar_10_latin1_key (col_varchar_10_latin1_key)
) ENGINE=Aria AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t7 VALUES (1,7,'j','abcdefjhjkl','yes',NULL,'p'),(2,1225719808,'abcdefjhjkl','i','abcdefjhjkl',2,'a'),(3,-698810368,'get','k','abcdefjhjkl',4,'l'),(4,NULL,'know','they','abcdefjhjkl',NULL,'as'),(5,-1041563648,'h','abcdefjhjkl','j',NULL,'abcdefjhjkl'),(6,NULL,'good','n','h',4,'have'),(7,NULL,'abcdefjhjkl','n','i',0,'i');
SELECT table1.col_int
FROM t6 AS table1 JOIN t2 AS table3 ON table1.col_varchar_1024_utf8_key = table3.col_varchar_10_latin1_key
LEFT JOIN
(t4 AS table4 JOIN t3 AS table5 ON table4.col_varchar_1024_latin1_key = table5.col_varchar_10_latin1_key )
JOIN t5 AS table6 ON table6.col_int_key
ON table3.col_varchar_10_latin1_key = table6.col_varchar_1024_latin1_key
LEFT JOIN t1 JOIN t7 AS table16 ON table16.col_varchar_10_utf8_key ON table5.col_int = table16.pk
WHERE table6.col_int ;
|
|
Re: sql_join_cache.cc:2770: virtual bool JOIN_CACHE_HASHED::put_record(): Assertion `last_key_entry >= end_pos' failed in maria-5.3-mwl128
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;
|
LPexportBug672497.xml
LPexportBug672497_var-bug672497.zip