| 1 |
drop table if exists t1,t2,t3,t4; |
| 2 |
set @maria_mrr_tmp=@@optimizer_switch; |
| 3 |
set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on'; |
| 4 |
set @mrr_buffer_size_save= @@mrr_buffer_size; |
| 5 |
set @save_storage_engine= @@default_storage_engine; |
| 6 |
set default_storage_engine=aria; |
| 7 |
create table t1(a int); |
| 8 |
show create table t1; |
| 9 |
Table Create Table |
| 10 |
t1 CREATE TABLE `t1` ( |
| 11 |
`a` int(11) DEFAULT NULL |
| 12 |
) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci PAGE_CHECKSUM=1 |
| 13 |
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
| 14 |
create table t2(a int); |
| 15 |
insert into t2 select A.a + 10*(B.a + 10*C.a) from t1 A, t1 B, t1 C; |
| 16 |
create table t3 ( |
| 17 |
a char(8) not null, b char(8) not null, filler char(200), |
| 18 |
key(a) |
| 19 |
); |
| 20 |
insert into t3 select @a:=concat('c-', 1000+ A.a, '=w'), @a, 'filler' from t2 A; |
| 21 |
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 2000+A.a, '=w'), |
| 22 |
'filler-1' from t2 A; |
| 23 |
insert into t3 select concat('c-', 1000+A.a, '=w'), concat('c-', 3000+A.a, '=w'), |
| 24 |
'filler-2' from t2 A; |
| 25 |
select a,filler from t3 where a >= 'c-9011=w'; |
| 26 |
a filler |
| 27 |
select a,filler from t3 where a >= 'c-1011=w' and a <= 'c-1015=w'; |
| 28 |
a filler |
| 29 |
c-1011=w filler |
| 30 |
c-1012=w filler |
| 31 |
c-1013=w filler |
| 32 |
c-1014=w filler |
| 33 |
c-1015=w filler |
| 34 |
c-1011=w filler-1 |
| 35 |
c-1012=w filler-1 |
| 36 |
c-1013=w filler-1 |
| 37 |
c-1014=w filler-1 |
| 38 |
c-1015=w filler-1 |
| 39 |
c-1011=w filler-2 |
| 40 |
c-1012=w filler-2 |
| 41 |
c-1013=w filler-2 |
| 42 |
c-1014=w filler-2 |
| 43 |
c-1015=w filler-2 |
| 44 |
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or |
| 45 |
(a>='c-1014=w' and a <= 'c-1015=w'); |
| 46 |
a filler |
| 47 |
c-1011=w filler |
| 48 |
c-1012=w filler |
| 49 |
c-1013=w filler |
| 50 |
c-1014=w filler |
| 51 |
c-1015=w filler |
| 52 |
c-1011=w filler-1 |
| 53 |
c-1012=w filler-1 |
| 54 |
c-1013=w filler-1 |
| 55 |
c-1014=w filler-1 |
| 56 |
c-1015=w filler-1 |
| 57 |
c-1011=w filler-2 |
| 58 |
c-1012=w filler-2 |
| 59 |
c-1013=w filler-2 |
| 60 |
c-1014=w filler-2 |
| 61 |
c-1015=w filler-2 |
| 62 |
insert into t3 values ('c-1013=z', 'c-1013=z', 'err'); |
| 63 |
insert into t3 values ('a-1014=w', 'a-1014=w', 'err'); |
| 64 |
select a,filler from t3 where (a>='c-1011=w' and a <= 'c-1013=w') or |
| 65 |
(a>='c-1014=w' and a <= 'c-1015=w'); |
| 66 |
a filler |
| 67 |
c-1011=w filler |
| 68 |
c-1012=w filler |
| 69 |
c-1013=w filler |
| 70 |
c-1014=w filler |
| 71 |
c-1015=w filler |
| 72 |
c-1011=w filler-1 |
| 73 |
c-1012=w filler-1 |
| 74 |
c-1013=w filler-1 |
| 75 |
c-1014=w filler-1 |
| 76 |
c-1015=w filler-1 |
| 77 |
c-1011=w filler-2 |
| 78 |
c-1012=w filler-2 |
| 79 |
c-1013=w filler-2 |
| 80 |
c-1014=w filler-2 |
| 81 |
c-1015=w filler-2 |
| 82 |
delete from t3 where b in ('c-1013=z', 'a-1014=w'); |
| 83 |
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or |
| 84 |
a='c-1014=w' or a='c-1015=w'; |
| 85 |
a filler |
| 86 |
c-1011=w filler |
| 87 |
c-1012=w filler |
| 88 |
c-1013=w filler |
| 89 |
c-1014=w filler |
| 90 |
c-1015=w filler |
| 91 |
c-1011=w filler-1 |
| 92 |
c-1012=w filler-1 |
| 93 |
c-1013=w filler-1 |
| 94 |
c-1014=w filler-1 |
| 95 |
c-1015=w filler-1 |
| 96 |
c-1011=w filler-2 |
| 97 |
c-1012=w filler-2 |
| 98 |
c-1013=w filler-2 |
| 99 |
c-1014=w filler-2 |
| 100 |
c-1015=w filler-2 |
| 101 |
insert into t3 values ('c-1013=w', 'del-me', 'inserted'); |
| 102 |
select a,filler from t3 where a='c-1011=w' or a='c-1012=w' or a='c-1013=w' or |
| 103 |
a='c-1014=w' or a='c-1015=w'; |
| 104 |
a filler |
| 105 |
c-1011=w filler |
| 106 |
c-1012=w filler |
| 107 |
c-1013=w filler |
| 108 |
c-1014=w filler |
| 109 |
c-1015=w filler |
| 110 |
c-1011=w filler-1 |
| 111 |
c-1012=w filler-1 |
| 112 |
c-1013=w filler-1 |
| 113 |
c-1014=w filler-1 |
| 114 |
c-1015=w filler-1 |
| 115 |
c-1011=w filler-2 |
| 116 |
c-1012=w filler-2 |
| 117 |
c-1013=w filler-2 |
| 118 |
c-1014=w filler-2 |
| 119 |
c-1015=w filler-2 |
| 120 |
c-1013=w inserted |
| 121 |
delete from t3 where b='del-me'; |
| 122 |
alter table t3 add primary key(b); |
| 123 |
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or |
| 124 |
b IN ('c-1019=w', 'c-1020=w', 'c-1021=w', |
| 125 |
'c-1022=w', 'c-1023=w', 'c-1024=w'); |
| 126 |
b filler |
| 127 |
c-1011=w filler |
| 128 |
c-1012=w filler |
| 129 |
c-1013=w filler |
| 130 |
c-1014=w filler |
| 131 |
c-1015=w filler |
| 132 |
c-1016=w filler |
| 133 |
c-1017=w filler |
| 134 |
c-1018=w filler |
| 135 |
c-1019=w filler |
| 136 |
c-1020=w filler |
| 137 |
c-1021=w filler |
| 138 |
c-1022=w filler |
| 139 |
c-1023=w filler |
| 140 |
c-1024=w filler |
| 141 |
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1020=w') or |
| 142 |
b IN ('c-1021=w', 'c-1022=w', 'c-1023=w'); |
| 143 |
b filler |
| 144 |
c-1011=w filler |
| 145 |
c-1012=w filler |
| 146 |
c-1013=w filler |
| 147 |
c-1014=w filler |
| 148 |
c-1015=w filler |
| 149 |
c-1016=w filler |
| 150 |
c-1017=w filler |
| 151 |
c-1018=w filler |
| 152 |
c-1019=w filler |
| 153 |
c-1020=w filler |
| 154 |
c-1021=w filler |
| 155 |
c-1022=w filler |
| 156 |
c-1023=w filler |
| 157 |
select b,filler from t3 where (b>='c-1011=w' and b<= 'c-1018=w') or |
| 158 |
b IN ('c-1019=w', 'c-1020=w') or |
| 159 |
(b>='c-1021=w' and b<= 'c-1023=w'); |
| 160 |
b filler |
| 161 |
c-1011=w filler |
| 162 |
c-1012=w filler |
| 163 |
c-1013=w filler |
| 164 |
c-1014=w filler |
| 165 |
c-1015=w filler |
| 166 |
c-1016=w filler |
| 167 |
c-1017=w filler |
| 168 |
c-1018=w filler |
| 169 |
c-1019=w filler |
| 170 |
c-1020=w filler |
| 171 |
c-1021=w filler |
| 172 |
c-1022=w filler |
| 173 |
c-1023=w filler |
| 174 |
drop table if exists t4; |
| 175 |
create table t4 (a varchar(10), b int, c char(10), filler char(200), |
| 176 |
key idx1 (a, b, c)) charset=latin1; |
| 177 |
insert into t4 (filler) select concat('NULL-', 15-a) from t2 order by a limit 15; |
| 178 |
insert into t4 (a,b,c,filler) |
| 179 |
select 'b-1',NULL,'c-1', concat('NULL-', 15-a) from t2 order by a limit 15; |
| 180 |
insert into t4 (a,b,c,filler) |
| 181 |
select 'b-1',NULL,'c-222', concat('NULL-', 15-a) from t2 order by a limit 15; |
| 182 |
insert into t4 (a,b,c,filler) |
| 183 |
select 'bb-1',NULL,'cc-2', concat('NULL-', 15-a) from t2 order by a limit 15; |
| 184 |
insert into t4 (a,b,c,filler) |
| 185 |
select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; |
| 186 |
explain |
| 187 |
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' |
| 188 |
or c='no-such-row2'); |
| 189 |
id select_type table type possible_keys key key_len ref rows Extra |
| 190 |
1 SIMPLE t4 range idx1 idx1 29 NULL 16 Using index condition; Rowid-ordered scan |
| 191 |
select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' |
| 192 |
or c='no-such-row2'); |
| 193 |
a b c filler |
| 194 |
NULL NULL NULL NULL-15 |
| 195 |
NULL NULL NULL NULL-14 |
| 196 |
NULL NULL NULL NULL-13 |
| 197 |
NULL NULL NULL NULL-12 |
| 198 |
NULL NULL NULL NULL-11 |
| 199 |
NULL NULL NULL NULL-10 |
| 200 |
NULL NULL NULL NULL-9 |
| 201 |
NULL NULL NULL NULL-8 |
| 202 |
NULL NULL NULL NULL-7 |
| 203 |
NULL NULL NULL NULL-6 |
| 204 |
NULL NULL NULL NULL-5 |
| 205 |
NULL NULL NULL NULL-4 |
| 206 |
NULL NULL NULL NULL-3 |
| 207 |
NULL NULL NULL NULL-2 |
| 208 |
NULL NULL NULL NULL-1 |
| 209 |
explain |
| 210 |
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); |
| 211 |
id select_type table type possible_keys key key_len ref rows Extra |
| 212 |
1 SIMPLE t4 range idx1 idx1 29 NULL 32 Using index condition; Rowid-ordered scan |
| 213 |
select * from t4 where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); |
| 214 |
a b c filler |
| 215 |
b-1 NULL c-1 NULL-15 |
| 216 |
b-1 NULL c-1 NULL-14 |
| 217 |
b-1 NULL c-1 NULL-13 |
| 218 |
b-1 NULL c-1 NULL-12 |
| 219 |
b-1 NULL c-1 NULL-11 |
| 220 |
b-1 NULL c-1 NULL-10 |
| 221 |
b-1 NULL c-1 NULL-9 |
| 222 |
b-1 NULL c-1 NULL-8 |
| 223 |
b-1 NULL c-1 NULL-7 |
| 224 |
b-1 NULL c-1 NULL-6 |
| 225 |
b-1 NULL c-1 NULL-5 |
| 226 |
b-1 NULL c-1 NULL-4 |
| 227 |
b-1 NULL c-1 NULL-3 |
| 228 |
b-1 NULL c-1 NULL-2 |
| 229 |
b-1 NULL c-1 NULL-1 |
| 230 |
bb-1 NULL cc-2 NULL-15 |
| 231 |
bb-1 NULL cc-2 NULL-14 |
| 232 |
bb-1 NULL cc-2 NULL-13 |
| 233 |
bb-1 NULL cc-2 NULL-12 |
| 234 |
bb-1 NULL cc-2 NULL-11 |
| 235 |
bb-1 NULL cc-2 NULL-10 |
| 236 |
bb-1 NULL cc-2 NULL-9 |
| 237 |
bb-1 NULL cc-2 NULL-8 |
| 238 |
bb-1 NULL cc-2 NULL-7 |
| 239 |
bb-1 NULL cc-2 NULL-6 |
| 240 |
bb-1 NULL cc-2 NULL-5 |
| 241 |
bb-1 NULL cc-2 NULL-4 |
| 242 |
bb-1 NULL cc-2 NULL-3 |
| 243 |
bb-1 NULL cc-2 NULL-2 |
| 244 |
bb-1 NULL cc-2 NULL-1 |
| 245 |
select * from t4 ignore index(idx1) where (a ='b-1' or a='bb-1') and b IS NULL and (c='c-1' or c='cc-2'); |
| 246 |
a b c filler |
| 247 |
b-1 NULL c-1 NULL-15 |
| 248 |
b-1 NULL c-1 NULL-14 |
| 249 |
b-1 NULL c-1 NULL-13 |
| 250 |
b-1 NULL c-1 NULL-12 |
| 251 |
b-1 NULL c-1 NULL-11 |
| 252 |
b-1 NULL c-1 NULL-10 |
| 253 |
b-1 NULL c-1 NULL-9 |
| 254 |
b-1 NULL c-1 NULL-8 |
| 255 |
b-1 NULL c-1 NULL-7 |
| 256 |
b-1 NULL c-1 NULL-6 |
| 257 |
b-1 NULL c-1 NULL-5 |
| 258 |
b-1 NULL c-1 NULL-4 |
| 259 |
b-1 NULL c-1 NULL-3 |
| 260 |
b-1 NULL c-1 NULL-2 |
| 261 |
b-1 NULL c-1 NULL-1 |
| 262 |
bb-1 NULL cc-2 NULL-15 |
| 263 |
bb-1 NULL cc-2 NULL-14 |
| 264 |
bb-1 NULL cc-2 NULL-13 |
| 265 |
bb-1 NULL cc-2 NULL-12 |
| 266 |
bb-1 NULL cc-2 NULL-11 |
| 267 |
bb-1 NULL cc-2 NULL-10 |
| 268 |
bb-1 NULL cc-2 NULL-9 |
| 269 |
bb-1 NULL cc-2 NULL-8 |
| 270 |
bb-1 NULL cc-2 NULL-7 |
| 271 |
bb-1 NULL cc-2 NULL-6 |
| 272 |
bb-1 NULL cc-2 NULL-5 |
| 273 |
bb-1 NULL cc-2 NULL-4 |
| 274 |
bb-1 NULL cc-2 NULL-3 |
| 275 |
bb-1 NULL cc-2 NULL-2 |
| 276 |
bb-1 NULL cc-2 NULL-1 |
| 277 |
drop table t1, t2, t3, t4; |
| 278 |
create table t1 (a int, b int not null,unique key (a,b),index(b)); |
| 279 |
insert ignore into t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(null,7),(9,9),(8,8),(7,7),(null,9),(null,9),(6,6); |
| 280 |
Warnings: |
| 281 |
Warning 1062 Duplicate entry '6-6' for key 'a' |
| 282 |
create table t2 like t1; |
| 283 |
insert into t2 select * from t1; |
| 284 |
alter table t1 modify b blob not null, add c int not null, drop key a, add unique key (a,b(20),c), drop key b, add key (b(10)); |
| 285 |
select * from t1 where a is null; |
| 286 |
a b c |
| 287 |
NULL 7 0 |
| 288 |
NULL 9 0 |
| 289 |
NULL 9 0 |
| 290 |
select * from t1 where (a is null or a > 0 and a < 3) and b > 7 limit 3; |
| 291 |
a b c |
| 292 |
NULL 9 0 |
| 293 |
NULL 9 0 |
| 294 |
select * from t1 where a is null and b=9 or a is null and b=7 limit 3; |
| 295 |
a b c |
| 296 |
NULL 7 0 |
| 297 |
NULL 9 0 |
| 298 |
NULL 9 0 |
| 299 |
drop table t1, t2; |
| 300 |
set default_storage_engine= @save_storage_engine; |
| 301 |
set @@mrr_buffer_size= @mrr_buffer_size_save; |
| 302 |
# |
| 303 |
# Crash in quick_range_seq_next() in maria-5.3-dsmrr-cpk with join_cache_level = {8,1} |
| 304 |
# |
| 305 |
set @save_join_cache_level= @@join_cache_level; |
| 306 |
SET SESSION join_cache_level = 8; |
| 307 |
CREATE TABLE `t1` ( |
| 308 |
`col_int_key` int(11) DEFAULT NULL, |
| 309 |
`col_datetime_key` datetime DEFAULT NULL, |
| 310 |
`col_varchar_key` varchar(1) DEFAULT NULL, |
| 311 |
`col_varchar_nokey` varchar(1) DEFAULT NULL, |
| 312 |
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) |
| 313 |
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; |
| 314 |
INSERT INTO `t1` VALUES (6,'2005-10-07 00:00:00','e','e'); |
| 315 |
INSERT INTO `t1` VALUES (51,'2000-07-15 05:00:34','f','f'); |
| 316 |
CREATE TABLE `t2` ( |
| 317 |
`col_int_key` int(11) DEFAULT NULL, |
| 318 |
`col_datetime_key` datetime DEFAULT NULL, |
| 319 |
`col_varchar_key` varchar(1) DEFAULT NULL, |
| 320 |
`col_varchar_nokey` varchar(1) DEFAULT NULL, |
| 321 |
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`) |
| 322 |
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1; |
| 323 |
INSERT INTO `t2` VALUES (2,'2004-10-11 18:13:16','w','w'); |
| 324 |
INSERT INTO `t2` VALUES (2,'1900-01-01 00:00:00','d','d'); |
| 325 |
SELECT table2 .`col_datetime_key` |
| 326 |
FROM t2 JOIN ( t1 table2 JOIN t2 table3 ON table3 .`col_varchar_key` < table2 .`col_varchar_key` ) ON table3 .`col_varchar_nokey` ; |
| 327 |
col_datetime_key |
| 328 |
Warnings: |
| 329 |
Warning 1292 Truncated incorrect BOOLEAN value: 'd' |
| 330 |
Warning 1292 Truncated incorrect BOOLEAN value: 'd' |
| 331 |
Warning 1292 Truncated incorrect BOOLEAN value: 'd' |
| 332 |
Warning 1292 Truncated incorrect BOOLEAN value: 'd' |
| 333 |
drop table t1, t2; |
| 334 |
set join_cache_level=@save_join_cache_level; |
| 335 |
CREATE TABLE t1( |
| 336 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 337 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 338 |
) ENGINE=ARIA; |
| 339 |
INSERT INTO t1 VALUES |
| 340 |
(1,9,'x'), (2,5,'g'), (3,1,'o'), (4,0,'g'), (5,1,'v'), |
| 341 |
(6,190,'m'), (7,6,'x'), (8,3,'c'), (9,4,'z'), (10,3,'i'), |
| 342 |
(11,186,'x'), (12,1,'g'), (13,8,'q'), (14,226,'m'), (15,133,'p'); |
| 343 |
CREATE TABLE t2( |
| 344 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 345 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 346 |
) ENGINE=ARIA; |
| 347 |
INSERT INTO t2 SELECT * FROM t1; |
| 348 |
INSERT INTO t2 VALUES (77, 333, 'z'); |
| 349 |
CREATE TABLE t3( |
| 350 |
pk int NOT NULL, i int NOT NULL, v varchar(1) NOT NULL, |
| 351 |
PRIMARY KEY (pk), INDEX idx (v, i) |
| 352 |
) ENGINE=ARIA; |
| 353 |
INSERT INTO t3 SELECT * FROM t1; |
| 354 |
INSERT INTO t3 VALUES |
| 355 |
(88, 442, 'y'), (99, 445, 'w'), (87, 442, 'z'), (98, 445, 'v'), (86, 442, 'x'), |
| 356 |
(97, 445, 't'), (85, 442, 'b'), (96, 445, 'l'), (84, 442, 'a'), (95, 445, 'k'); |
| 357 |
set @save_join_cache_level=@@join_cache_level; |
| 358 |
set join_cache_level=1; |
| 359 |
SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) |
| 360 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 361 |
COUNT(t1.v) |
| 362 |
120 |
| 363 |
EXPLAIN |
| 364 |
SELECT COUNT(t1.v) FROM t1, t2 IGNORE INDEX (idx), t3 IGNORE INDEX (idx) |
| 365 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 366 |
id select_type table type possible_keys key key_len ref rows Extra |
| 367 |
1 SIMPLE t1 index NULL idx 10 NULL 15 Using index |
| 368 |
1 SIMPLE t2 range PRIMARY PRIMARY 4 NULL 16 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) |
| 369 |
1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 25 Using index condition; Using where; Rowid-ordered scan; Using join buffer (flat, BNL join) |
| 370 |
SELECT COUNT(t1.v) FROM t1, t2, t3 |
| 371 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 372 |
COUNT(t1.v) |
| 373 |
120 |
| 374 |
EXPLAIN |
| 375 |
SELECT COUNT(t1.v) FROM t1, t2, t3 |
| 376 |
WHERE t3.v = t2.v AND t3.i < t2.i AND t3.pk > 0 AND t2.pk > 0; |
| 377 |
id select_type table type possible_keys key key_len ref rows Extra |
| 378 |
1 SIMPLE t2 range PRIMARY,idx PRIMARY 4 NULL 16 Using index condition; Rowid-ordered scan |
| 379 |
1 SIMPLE t3 ref PRIMARY,idx idx 6 test.t2.v 2 Using index condition; Using where |
| 380 |
1 SIMPLE t1 index NULL idx 10 NULL 15 Using index; Using join buffer (flat, BNL join) |
| 381 |
set join_cache_level=@save_join_cache_level; |
| 382 |
DROP TABLE t1,t2,t3; |
| 383 |
# |
| 384 |
# BUG#671361: virtual int Mrr_ordered_index_reader::refill_buffer(): Assertion `!know_key_tuple_params |
| 385 |
# (works only on Maria because we need 1024-byte long key) |
| 386 |
# |
| 387 |
SET SESSION join_cache_level = 6; |
| 388 |
SET SESSION join_buffer_size = 1024; |
| 389 |
CREATE TABLE t1 ( |
| 390 |
pk int(11) NOT NULL AUTO_INCREMENT, |
| 391 |
col_varchar_1024_latin1_key varchar(1024) DEFAULT NULL, |
| 392 |
PRIMARY KEY (pk), |
| 393 |
KEY col_varchar_1024_latin1_key (col_varchar_1024_latin1_key) |
| 394 |
) ENGINE=Aria; |
| 395 |
Warnings: |
| 396 |
Note 1071 Specified key was too long; max key length is 2300 bytes |
| 397 |
INSERT INTO t1 VALUES |
| 398 |
(1,'z'), (2,'abcdefjhjkl'), (3,'in'), (4,'abcdefjhjkl'), (6,'abcdefjhjkl'), |
| 399 |
(11,'zx'), (12,'abcdefjhjm'), (13,'jn'), (14,'abcdefjhjp'), (16,'abcdefjhjr'); |
| 400 |
CREATE TABLE t2 ( |
| 401 |
col_varchar_10_latin1 varchar(10) DEFAULT NULL |
| 402 |
) ENGINE=Aria; |
| 403 |
INSERT INTO t2 VALUES ('foo'), ('foo'); |
| 404 |
EXPLAIN SELECT count(*) |
| 405 |
FROM t1 AS table1, t2 AS table2 |
| 406 |
WHERE |
| 407 |
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; |
| 408 |
id select_type table type possible_keys key key_len ref rows Extra |
| 409 |
1 SIMPLE table2 ALL NULL NULL NULL NULL 2 Using where |
| 410 |
1 SIMPLE table1 ref PRIMARY,col_varchar_1024_latin1_key col_varchar_1024_latin1_key 2303 test.table2.col_varchar_10_latin1 1 Using where |
| 411 |
SELECT count(*) |
| 412 |
FROM t1 AS table1, t2 AS table2 |
| 413 |
WHERE |
| 414 |
table1.col_varchar_1024_latin1_key = table2.col_varchar_10_latin1 AND table1.pk<>0 ; |
| 415 |
count(*) |
| 416 |
0 |
| 417 |
drop table t1, t2; |
| 418 |
# |
| 419 |
# BUG#693747: Assertion multi_range_read.cc:908: int DsMrr_impl::dsmrr_init( |
| 420 |
# |
| 421 |
set @_save_join_cache_level= @@join_cache_level; |
| 422 |
set @_save_join_buffer_size= @@join_buffer_size; |
| 423 |
set join_cache_level=8; |
| 424 |
set join_buffer_size=10240; |
| 425 |
CREATE TABLE t1 ( |
| 426 |
f2 varchar(32) COLLATE latin1_swedish_ci, |
| 427 |
f3 int(11), |
| 428 |
f4 varchar(4096) COLLATE utf8_bin, |
| 429 |
f5 varchar(4096) COLLATE latin1_bin, |
| 430 |
KEY (f5) |
| 431 |
) ENGINE=Aria TRANSACTIONAL=0 ; |
| 432 |
Warnings: |
| 433 |
Note 1071 Specified key was too long; max key length is 2300 bytes |
| 434 |
# Fill the table with some data |
| 435 |
SELECT alias2.* , alias1.f2 |
| 436 |
FROM |
| 437 |
t1 AS alias1 |
| 438 |
LEFT JOIN t1 AS alias2 ON alias1.f2 = alias2.f5 |
| 439 |
WHERE |
| 440 |
alias2.f3 < 0; |
| 441 |
f2 f3 f4 f5 f2 |
| 442 |
set join_cache_level=@_save_join_cache_level; |
| 443 |
set join_buffer_size=@_save_join_buffer_size; |
| 444 |
set optimizer_switch=@maria_mrr_tmp; |
| 445 |
drop table t1; |