-- source include/have_maria.inc # # MRR/Maria tests. # --disable_warnings drop table if exists t1,t2,t3,t4; --enable_warnings set @mrr_buffer_size_save= @@mrr_buffer_size; set @save_storage_engine= @@storage_engine; set storage_engine=aria; --source include/mrr_tests.inc set storage_engine= @save_storage_engine; set @@mrr_buffer_size= @mrr_buffer_size_save; --echo # --echo # Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1} --echo # set @save_join_cache_level= @@join_cache_level; SET SESSION join_cache_level = 8; CREATE TABLE `t1` ( `col_int_key` int(11) DEFAULT NULL, `col_datetime_key` datetime DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e'); INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f'); CREATE TABLE `t2` ( `col_int_key` int(11) DEFAULT NULL, `col_datetime_key` datetime DEFAULT NULL, `col_varchar_key` varchar(1) DEFAULT NULL, `col_varchar_nokey` varchar(1) DEFAULT NULL, KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) ) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w'); INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d'); SELECT table2 .`col_datetime_key` FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ; drop table t1, t2; set join_cache_level=@save_join_cache_level; # # Bug #665049: index condition pushdown with Maria # CREATE TABLE t1( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, PRIMARY KEY (pk), INDEX idx (v, i) ) ENGINE=ARIA; INSERT INTO t1 VALUES (1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), (6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), (11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'); CREATE TABLE t2( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, PRIMARY KEY (pk), INDEX idx (v, i) ) ENGINE=ARIA; INSERT INTO t2 SELECT * FROM t1; INSERT INTO t2 VALUES (77, 333, 'z'); CREATE TABLE t3( pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, PRIMARY KEY (pk), INDEX idx (v, i) ) ENGINE=ARIA; INSERT INTO t3 SELECT * FROM t1; INSERT INTO t3 VALUES (88, 442, 'y'), (99, 445, 'w') ; SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; EXPLAIN SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; SELECT COUNT(t1.v) FROM t1, t2, t3 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; EXPLAIN SELECT COUNT(t1.v) FROM t1, t2, t3 WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; DROP TABLE t1,t2,t3; --echo # --echo # BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params --echo # (works only on Maria because we need 1024-byte long key) --echo # SET SESSION optimizer_use_mrr = 'force'; SET SESSION join_cache_level = 6; SET SESSION join_buffer_size = 1024; CREATE TABLE t1 ( pk int(11) NOT NULL AUTO_INCREMENT, col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, PRIMARY KEY (pk), KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) ) ENGINE=Aria; INSERT INTO t1 VALUES (1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'); CREATE TABLE t2 ( col_varchar_10_latin1 varchar(10) DEFAULT NULL ) ENGINE=Aria; INSERT INTO t2 VALUES ('foo'), ('foo'); EXPLAIN SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; SELECT count(*) FROM t1 AS table1, t2 AS table2 WHERE table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; drop table t1, t2; --echo # --echo # BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init( --echo # set @_save_join_cache_level= @@join_cache_level; set @_save_join_buffer_size= @@join_buffer_size; set join_cache_level=8; set join_buffer_size=10000; CREATE TABLE t1 ( f2 varchar(32) COLLATE latin1_swedish_ci, f3 int(11), f4 varchar(1024) COLLATE utf8_bin, f5 varchar(1024) COLLATE latin1_bin, KEY (f5) ) ENGINE=Aria TRANSACTIONAL=0 ; --echo # Fill the table with some data --disable_query_log INSERT IGNORE INTO t1 VALUES ('cueikuirqr','0','f4-data','hcueikuirqrzflno'),('her','0','f4-data','ehcueikuirqrzfln'), ('YKAOE','0','f4-data','qieehcueikuirqrz'),('youre','0','f4-data','nkqieehcueikuirq'), ('b','0','f4-data','the'),('MGUDG','0','f4-data','m'), ('UXAGU','0','f4-data','HZXVA'),('bwbgsnkqie','0','f4-data','something'), ('s','0','f4-data','slelfhjawbwbgsnk'),('the','0','f4-data','if'), ('TDLKE','0','f4-data','MGWNJ'),('do','0','f4-data','see'), ('why','0','f4-data','mean'),('THKCG','0','f4-data','YFLDY'), ('x','0','f4-data','e'),('yncitaeysb','0','f4-data','tgyncitaeysbgucs'), ('ZEOXX','0','f4-data','jawbwbgsnkqieehc'),('hjawbwbgsn','0','f4-data','fhjawbwbgsnkqiee'), ('all','0','f4-data','sbgucsgqslelfhja'),('the','0','f4-data','would'), ('mtgyncitae','0','f4-data','ISNQQ'),('KNCUI','0','f4-data','want'), ('is','0','f4-data','i'),('out','0','f4-data','jvcmjlmtgyncitae'), ('it','0','f4-data','you'),('LHDIH','0','f4-data','txmtxyjvcmjlmtgy'), ('z','0','f4-data','ntxmtxyjvcmjlmtg'),('vyhnmvgmcn','0','f4-data','AIGQK'), ('ytvyhnmvgm','0','f4-data','z'),('t','0','f4-data','on'), ('xqegbytvyh','0','f4-data','ixqegbytvyhnmvgm'),('WGVRU','0','f4-data','h'), ('b','0','f4-data','z'),('who','0','f4-data','gddixqegbytvy'), ('PMLFL','0','f4-data','vgmcntxmtxyjvcmj'),('back','0','f4-data','n'), ('i','0','f4-data','PZGUB'),('f','0','f4-data','the'), ('PNXVP','0','f4-data','v'),('MAKKL','0','f4-data','CGCWF'), ('RMDAV','0','f4-data','v'),('l','0','f4-data','n'), ('rhnoypgddi','0','f4-data','VIZNE'),('t','0','f4-data','a'), ('like','0','f4-data','JSHPZ'),('pskeywslmk','0','f4-data','q'), ('QZZJJ','0','f4-data','c'),('atlxepskey','0','f4-data','YJRMA'), ('YUVOU','0','f4-data','eywslmkdrhnoypgd'),('some','0','f4-data','r'), ('c','0','f4-data','her'),('o','0','f4-data','EMURT'), ('if','0','f4-data','had'),('when','0','f4-data','CLVWT'), ('blfufrcdjm','0','f4-data','IZCZN'),('vutblfufrc','0','f4-data','how'), ('why','0','f4-data','I'),('IXLYQ','0','f4-data','weuwuvutblfufrcd'), ('here','0','f4-data','m'),('ZOCTJ','0','f4-data','IDSFD'), ('kqsweuwuvu','0','f4-data','oh'),('ykqsweuwuv','0','f4-data','zykqsweuwuvutblf'), ('zezykqsweu','0','f4-data','t'),('q','0','f4-data','o'), ('IBKAU','0','f4-data','oh'),('ivjisuzezy','0','f4-data','XHXKE'), ('xsivjisuze','0','f4-data','plxsivjisuzezykq'),('have','0','f4-data','uvplxsivjisuzezy'), ('on','0','f4-data','me'),('ijkfuvplxs','0','f4-data','OGEHV'), ('u','0','f4-data','okay'),('i','0','f4-data','pajzbbojshnijkfu'), ('of','0','f4-data','g'),('for','0','f4-data','Im'), ('or','0','f4-data','ZOJHX'),('n','0','f4-data','you'), ('that','0','f4-data','just'),('bbojshnijk','0','f4-data','JYGSJ'), ('k','0','f4-data','y'),('k','0','f4-data','y'), ('be','0','f4-data','m'),('fnbmxwicrk','0','f4-data','t'), ('yaffpegvav','0','f4-data','have'),('crkdymahya','0','f4-data','QQWQI'), ('t','0','f4-data','hnijkfuvplxsivji'),('dgxpajzbbo','0','f4-data','vavdgxpajzbbojsh'), ('g','0','f4-data','pegvavdgxpajzbbo'),('Im','0','f4-data','ffpegvavdgxpajzb'); --enable_query_log SELECT alias2.* , alias1.f2 FROM t1 AS alias1 LEFT JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 WHERE alias2.f3 < 0; set join_cache_level=@_save_join_cache_level; set join_buffer_size=@_save_join_buffer_size; drop table t1;