| 1 |
-- source include/have_maria.inc |
| 2 |
# |
| 3 |
# MRR/Maria tests. |
| 4 |
# |
| 5 |
|
| 6 |
--disable_warnings |
| 7 |
drop table if exists t1,t2,t3,t4; |
| 8 |
--enable_warnings |
| 9 |
|
| 10 |
set @maria_mrr_tmp=@@optimizer_switch; |
| 11 |
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; |
| 12 |
|
| 13 |
set @mrr_buffer_size_save= @@mrr_buffer_size; |
| 14 |
|
| 15 |
set @save_storage_engine= @@default_storage_engine; |
| 16 |
set default_storage_engine=aria; |
| 17 |
|
| 18 |
--source include/mrr_tests.inc |
| 19 |
set default_storage_engine= @save_storage_engine; |
| 20 |
|
| 21 |
set @@mrr_buffer_size= @mrr_buffer_size_save; |
| 22 |
|
| 23 |
--echo # |
| 24 |
--echo # Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1} |
| 25 |
--echo # |
| 26 |
set @save_join_cache_level= @@join_cache_level; |
| 27 |
SET SESSION join_cache_level = 8; |
| 28 |
CREATE TABLE `t1` ( |
| 29 |
`col_int_key` int(11) DEFAULT NULL, |
| 30 |
`col_datetime_key` datetime DEFAULT NULL, |
| 31 |
`col_varchar_key` varchar(1) DEFAULT NULL, |
| 32 |
`col_varchar_nokey` varchar(1) DEFAULT NULL, |
| 33 |
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) |
| 34 |
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; |
| 35 |
INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e'); |
| 36 |
INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f'); |
| 37 |
CREATE TABLE `t2` ( |
| 38 |
`col_int_key` int(11) DEFAULT NULL, |
| 39 |
`col_datetime_key` datetime DEFAULT NULL, |
| 40 |
`col_varchar_key` varchar(1) DEFAULT NULL, |
| 41 |
`col_varchar_nokey` varchar(1) DEFAULT NULL, |
| 42 |
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) |
| 43 |
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; |
| 44 |
INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w'); |
| 45 |
INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d'); |
| 46 |
SELECT table2 .`col_datetime_key` |
| 47 |
FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ; |
| 48 |
|
| 49 |
drop table t1, t2; |
| 50 |
set join_cache_level=@save_join_cache_level; |
| 51 |
|
| 52 |
# |
| 53 |
# Bug #665049: index condition pushdown with Maria |
| 54 |
# |
| 55 |
|
| 56 |
CREATE TABLE t1( |
| 57 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 58 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 59 |
) ENGINE=ARIA; |
| 60 |
INSERT INTO t1 VALUES |
| 61 |
(1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), |
| 62 |
(6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), |
| 63 |
(11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'); |
| 64 |
|
| 65 |
CREATE TABLE t2( |
| 66 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 67 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 68 |
) ENGINE=ARIA; |
| 69 |
INSERT INTO t2 SELECT * FROM t1; |
| 70 |
INSERT INTO t2 VALUES (77, 333, 'z'); |
| 71 |
|
| 72 |
CREATE TABLE t3( |
| 73 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 74 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 75 |
) ENGINE=ARIA; |
| 76 |
INSERT INTO t3 SELECT * FROM t1; |
| 77 |
INSERT INTO t3 VALUES |
| 78 |
(88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'), |
| 79 |
(97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k'); |
| 80 |
|
| 81 |
set @save_join_cache_level=@@join_cache_level; |
| 82 |
set join_cache_level=1; |
| 83 |
|
| 84 |
SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) |
| 85 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 86 |
EXPLAIN |
| 87 |
SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) |
| 88 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 89 |
|
| 90 |
SELECT COUNT(t1.v) FROM t1, t2, t3 |
| 91 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 92 |
EXPLAIN |
| 93 |
SELECT COUNT(t1.v) FROM t1, t2, t3 |
| 94 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 95 |
|
| 96 |
set join_cache_level=@save_join_cache_level; |
| 97 |
|
| 98 |
DROP TABLE t1,t2,t3; |
| 99 |
|
| 100 |
--echo # |
| 101 |
--echo # BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params |
| 102 |
--echo # (works only on Maria because we need 1024-byte long key) |
| 103 |
--echo # |
| 104 |
|
| 105 |
SET SESSION join_cache_level = 6; |
| 106 |
SET SESSION join_buffer_size = 1024; |
| 107 |
CREATE TABLE t1 ( |
| 108 |
pk int(11) NOT NULL AUTO_INCREMENT, |
| 109 |
col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, |
| 110 |
PRIMARY KEY (pk), |
| 111 |
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) |
| 112 |
) ENGINE=Aria; |
| 113 |
|
| 114 |
INSERT INTO t1 VALUES |
| 115 |
(1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'), |
| 116 |
(11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr'); |
| 117 |
|
| 118 |
CREATE TABLE t2 ( |
| 119 |
col_varchar_10_latin1 varchar(10) DEFAULT NULL |
| 120 |
) ENGINE=Aria; |
| 121 |
INSERT INTO t2 VALUES ('foo'), ('foo'); |
| 122 |
|
| 123 |
EXPLAIN SELECT count(*) |
| 124 |
FROM t1 AS table1, t2 AS table2 |
| 125 |
WHERE |
| 126 |
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; |
| 127 |
|
| 128 |
SELECT count(*) |
| 129 |
FROM t1 AS table1, t2 AS table2 |
| 130 |
WHERE |
| 131 |
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; |
| 132 |
|
| 133 |
drop table t1, t2; |
| 134 |
|
| 135 |
--echo # |
| 136 |
--echo # BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init( |
| 137 |
--echo # |
| 138 |
set @_save_join_cache_level= @@join_cache_level; |
| 139 |
set @_save_join_buffer_size= @@join_buffer_size; |
| 140 |
|
| 141 |
set join_cache_level=8; |
| 142 |
set join_buffer_size=10240; |
| 143 |
|
| 144 |
CREATE TABLE t1 ( |
| 145 |
f2 varchar(32) COLLATE latin1_swedish_ci, |
| 146 |
f3 int(11), |
| 147 |
f4 varchar(4096) COLLATE utf8_bin, |
| 148 |
f5 varchar(4096) COLLATE latin1_bin, |
| 149 |
KEY (f5) |
| 150 |
) ENGINE=Aria TRANSACTIONAL=0 ; |
| 151 |
|
| 152 |
--echo # Fill the table with some data |
| 153 |
--disable_query_log |
| 154 |
INSERT IGNORE INTO t1 VALUES |
| 155 |
('cueikuirqr','0','f4-data','hcueikuirqrzflno'),('her','0','f4-data','ehcueikuirqrzfln'), |
| 156 |
('YKAOE','0','f4-data','qieehcueikuirqrz'),('youre','0','f4-data','nkqieehcueikuirq'), |
| 157 |
('b','0','f4-data','the'),('MGUDG','0','f4-data','m'), |
| 158 |
('UXAGU','0','f4-data','HZXVA'),('bwbgsnkqie','0','f4-data','something'), |
| 159 |
('s','0','f4-data','slelfhjawbwbgsnk'),('the','0','f4-data','if'), |
| 160 |
('TDLKE','0','f4-data','MGWNJ'),('do','0','f4-data','see'), |
| 161 |
('why','0','f4-data','mean'),('THKCG','0','f4-data','YFLDY'), |
| 162 |
('x','0','f4-data','e'),('yncitaeysb','0','f4-data','tgyncitaeysbgucs'), |
| 163 |
('ZEOXX','0','f4-data','jawbwbgsnkqieehc'),('hjawbwbgsn','0','f4-data','fhjawbwbgsnkqiee'), |
| 164 |
('all','0','f4-data','sbgucsgqslelfhja'),('the','0','f4-data','would'), |
| 165 |
('mtgyncitae','0','f4-data','ISNQQ'),('KNCUI','0','f4-data','want'), |
| 166 |
('is','0','f4-data','i'),('out','0','f4-data','jvcmjlmtgyncitae'), |
| 167 |
('it','0','f4-data','you'),('LHDIH','0','f4-data','txmtxyjvcmjlmtgy'), |
| 168 |
('z','0','f4-data','ntxmtxyjvcmjlmtg'),('vyhnmvgmcn','0','f4-data','AIGQK'), |
| 169 |
('ytvyhnmvgm','0','f4-data','z'),('t','0','f4-data','on'), |
| 170 |
('xqegbytvyh','0','f4-data','ixqegbytvyhnmvgm'),('WGVRU','0','f4-data','h'), |
| 171 |
('b','0','f4-data','z'),('who','0','f4-data','gddixqegbytvy'), |
| 172 |
('PMLFL','0','f4-data','vgmcntxmtxyjvcmj'),('back','0','f4-data','n'), |
| 173 |
('i','0','f4-data','PZGUB'),('f','0','f4-data','the'), |
| 174 |
('PNXVP','0','f4-data','v'),('MAKKL','0','f4-data','CGCWF'), |
| 175 |
('RMDAV','0','f4-data','v'),('l','0','f4-data','n'), |
| 176 |
('rhnoypgddi','0','f4-data','VIZNE'),('t','0','f4-data','a'), |
| 177 |
('like','0','f4-data','JSHPZ'),('pskeywslmk','0','f4-data','q'), |
| 178 |
('QZZJJ','0','f4-data','c'),('atlxepskey','0','f4-data','YJRMA'), |
| 179 |
('YUVOU','0','f4-data','eywslmkdrhnoypgd'),('some','0','f4-data','r'), |
| 180 |
('c','0','f4-data','her'),('o','0','f4-data','EMURT'), |
| 181 |
('if','0','f4-data','had'),('when','0','f4-data','CLVWT'), |
| 182 |
('blfufrcdjm','0','f4-data','IZCZN'),('vutblfufrc','0','f4-data','how'), |
| 183 |
('why','0','f4-data','I'),('IXLYQ','0','f4-data','weuwuvutblfufrcd'), |
| 184 |
('here','0','f4-data','m'),('ZOCTJ','0','f4-data','IDSFD'), |
| 185 |
('kqsweuwuvu','0','f4-data','oh'),('ykqsweuwuv','0','f4-data','zykqsweuwuvutblf'), |
| 186 |
('zezykqsweu','0','f4-data','t'),('q','0','f4-data','o'), |
| 187 |
('IBKAU','0','f4-data','oh'),('ivjisuzezy','0','f4-data','XHXKE'), |
| 188 |
('xsivjisuze','0','f4-data','plxsivjisuzezykq'),('have','0','f4-data','uvplxsivjisuzezy'), |
| 189 |
('on','0','f4-data','me'),('ijkfuvplxs','0','f4-data','OGEHV'), |
| 190 |
('u','0','f4-data','okay'),('i','0','f4-data','pajzbbojshnijkfu'), |
| 191 |
('of','0','f4-data','g'),('for','0','f4-data','Im'), |
| 192 |
('or','0','f4-data','ZOJHX'),('n','0','f4-data','you'), |
| 193 |
('that','0','f4-data','just'),('bbojshnijk','0','f4-data','JYGSJ'), |
| 194 |
('k','0','f4-data','y'),('k','0','f4-data','y'), |
| 195 |
('be','0','f4-data','m'),('fnbmxwicrk','0','f4-data','t'), |
| 196 |
('yaffpegvav','0','f4-data','have'),('crkdymahya','0','f4-data','QQWQI'), |
| 197 |
('t','0','f4-data','hnijkfuvplxsivji'),('dgxpajzbbo','0','f4-data','vavdgxpajzbbojsh'), |
| 198 |
('g','0','f4-data','pegvavdgxpajzbbo'),('Im','0','f4-data','ffpegvavdgxpajzb'); |
| 199 |
--enable_query_log |
| 200 |
|
| 201 |
|
| 202 |
SELECT alias2.* , alias1.f2 |
| 203 |
FROM |
| 204 |
t1 AS alias1 |
| 205 |
LEFT JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 |
| 206 |
WHERE |
| 207 |
alias2.f3 < 0; |
| 208 |
|
| 209 |
set join_cache_level=@_save_join_cache_level; |
| 210 |
set join_buffer_size=@_save_join_buffer_size; |
| 211 |
set optimizer_switch=@maria_mrr_tmp; |
| 212 |
|
| 213 |
drop table t1; |