| 1 |
# |
| 2 |
# Testing of potential problems in Aria |
| 3 |
# This code was initially taken from myisam.test |
| 4 |
# |
| 5 |
|
| 6 |
-- source include/have_maria.inc |
| 7 |
-- source include/have_partition.inc |
| 8 |
-- source include/have_sequence.inc |
| 9 |
--source include/test_db_charset_latin1.inc |
| 10 |
|
| 11 |
call mtr.add_suppression("Can't find record in '.*'"); |
| 12 |
|
| 13 |
select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA"; |
| 14 |
|
| 15 |
let $default_engine=`select @@global.default_storage_engine`; |
| 16 |
let $default_checksum=`select @@global.aria_page_checksum`; |
| 17 |
set global default_storage_engine=aria; |
| 18 |
set session default_storage_engine=aria; |
| 19 |
set global aria_page_checksum=0; |
| 20 |
let $default_log_file_size=`select @@global.aria_log_file_size`; |
| 21 |
set global aria_log_file_size=4294959104; |
| 22 |
|
| 23 |
# Initialise |
| 24 |
SET SQL_WARNINGS=1; |
| 25 |
|
| 26 |
# |
| 27 |
# Test problem with CHECK TABLE; |
| 28 |
# |
| 29 |
|
| 30 |
CREATE TABLE t1 ( |
| 31 |
STRING_DATA char(255) default NULL, |
| 32 |
KEY string_data (STRING_DATA) |
| 33 |
); |
| 34 |
|
| 35 |
INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); |
| 36 |
INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); |
| 37 |
INSERT INTO t1 VALUES ('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'); |
| 38 |
INSERT INTO t1 VALUES ('FGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'); |
| 39 |
INSERT INTO t1 VALUES ('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'); |
| 40 |
INSERT INTO t1 VALUES ('WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'); |
| 41 |
CHECK TABLE t1; |
| 42 |
drop table t1; |
| 43 |
|
| 44 |
# |
| 45 |
# Test auto_increment warning |
| 46 |
# |
| 47 |
create table t1 (a int primary key auto_increment) engine=aria; |
| 48 |
insert into t1 values (1); |
| 49 |
update t1 set a=0 where a=1; |
| 50 |
check table t1; |
| 51 |
select * from t1; |
| 52 |
drop table t1; |
| 53 |
|
| 54 |
# |
| 55 |
# Test problem with rows that are 65517-65520 bytes long |
| 56 |
# |
| 57 |
|
| 58 |
create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); |
| 59 |
|
| 60 |
let $1=100; |
| 61 |
disable_query_log; |
| 62 |
--disable_warnings |
| 63 |
SET SQL_WARNINGS=0; |
| 64 |
while ($1) |
| 65 |
{ |
| 66 |
eval insert ignore into t1 (b) values(repeat(char(65+$1),65550-$1)); |
| 67 |
dec $1; |
| 68 |
} |
| 69 |
SET SQL_WARNINGS=1; |
| 70 |
--enable_warnings |
| 71 |
--enable_query_log |
| 72 |
check table t1; |
| 73 |
repair table t1; |
| 74 |
delete from t1 where (a & 1); |
| 75 |
check table t1; |
| 76 |
repair table t1; |
| 77 |
check table t1; |
| 78 |
|
| 79 |
# FLUSH + REPAIR used to cause assertion failure in page cache |
| 80 |
flush table t1; |
| 81 |
repair table t1; |
| 82 |
drop table t1; |
| 83 |
|
| 84 |
# |
| 85 |
# Test bug: Two optimize in a row reset index cardinality |
| 86 |
# |
| 87 |
|
| 88 |
create table t1 (a int not null auto_increment, b int not null, primary key (a), index(b)); |
| 89 |
insert into t1 (b) values (1),(2),(2),(2),(2); |
| 90 |
optimize table t1; |
| 91 |
show index from t1; |
| 92 |
optimize table t1; |
| 93 |
show index from t1; |
| 94 |
drop table t1; |
| 95 |
|
| 96 |
# |
| 97 |
# Test of how ORDER BY works when doing it on the whole table |
| 98 |
# |
| 99 |
|
| 100 |
create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)); |
| 101 |
insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); |
| 102 |
explain select * from t1 order by a; |
| 103 |
explain select * from t1 order by b; |
| 104 |
explain select * from t1 order by c; |
| 105 |
explain select a from t1 order by a; |
| 106 |
explain select b from t1 order by b; |
| 107 |
explain select a,b from t1 order by b; |
| 108 |
explain select a,b from t1; |
| 109 |
explain select a,b,c from t1; |
| 110 |
drop table t1; |
| 111 |
|
| 112 |
# |
| 113 |
# Test of OPTIMIZE of locked and modified tables |
| 114 |
# |
| 115 |
set autocommit=0; |
| 116 |
begin; |
| 117 |
CREATE TABLE t1 (a INT); |
| 118 |
INSERT INTO t1 VALUES (1), (2), (3); |
| 119 |
LOCK TABLES t1 WRITE; |
| 120 |
INSERT INTO t1 VALUES (1), (2), (3); |
| 121 |
commit; |
| 122 |
set autocommit=1; |
| 123 |
UNLOCK TABLES; |
| 124 |
OPTIMIZE TABLE t1; |
| 125 |
DROP TABLE t1; |
| 126 |
|
| 127 |
# |
| 128 |
# Test of optimize, when only mi_sort_index (but not mi_repair*) is done |
| 129 |
# in ha_aria::repair, and index size is changed (decreased). |
| 130 |
# |
| 131 |
|
| 132 |
create table t1 ( t1 char(255), key(t1(250))); |
| 133 |
insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169'); |
| 134 |
insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203'); |
| 135 |
insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767'); |
| 136 |
insert t1 values ('242624262426242624262426242624262426242607890789078907890789078907890789078907'); |
| 137 |
insert t1 values ('256025602560256025602560256025602560256011701170117011701170117011701170117011'); |
| 138 |
insert t1 values ('276027602760276027602760276027602760276001610161016101610161016101610161016101'); |
| 139 |
insert t1 values ('281528152815281528152815281528152815281564956495649564956495649564956495649564'); |
| 140 |
insert t1 values ('292129212921292129212921292129212921292102100210021002100210021002100210021002'); |
| 141 |
insert t1 values ('380638063806380638063806380638063806380634483448344834483448344834483448344834'); |
| 142 |
insert t1 values ('411641164116411641164116411641164116411616301630163016301630163016301630163016'); |
| 143 |
insert t1 values ('420842084208420842084208420842084208420899889988998899889988998899889988998899'); |
| 144 |
insert t1 values ('438443844384438443844384438443844384438482448244824482448244824482448244824482'); |
| 145 |
insert t1 values ('443244324432443244324432443244324432443239613961396139613961396139613961396139'); |
| 146 |
insert t1 values ('485448544854485448544854485448544854485477847784778477847784778477847784778477'); |
| 147 |
insert t1 values ('494549454945494549454945494549454945494555275527552755275527552755275527552755'); |
| 148 |
insert t1 values ('538647864786478647864786478647864786478688918891889188918891889188918891889188'); |
| 149 |
insert t1 values ('565556555655565556555655565556555655565554845484548454845484548454845484548454'); |
| 150 |
insert t1 values ('607860786078607860786078607860786078607856665666566656665666566656665666566656'); |
| 151 |
insert t1 values ('640164016401640164016401640164016401640141274127412741274127412741274127412741'); |
| 152 |
insert t1 values ('719471947194719471947194719471947194719478717871787178717871787178717871787178'); |
| 153 |
insert t1 values ('742574257425742574257425742574257425742549604960496049604960496049604960496049'); |
| 154 |
insert t1 values ('887088708870887088708870887088708870887035963596359635963596359635963596359635'); |
| 155 |
insert t1 values ('917791779177917791779177917791779177917773857385738573857385738573857385738573'); |
| 156 |
insert t1 values ('933293329332933293329332933293329332933278987898789878987898789878987898789878'); |
| 157 |
insert t1 values ('963896389638963896389638963896389638963877807780778077807780778077807780778077'); |
| 158 |
delete from t1 where t1>'2'; |
| 159 |
insert t1 values ('70'), ('84'), ('60'), ('20'), ('76'), ('89'), ('49'), ('50'), |
| 160 |
('88'), ('61'), ('42'), ('98'), ('39'), ('30'), ('25'), ('66'), ('61'), ('48'), |
| 161 |
('80'), ('84'), ('98'), ('19'), ('91'), ('42'), ('47'); |
| 162 |
optimize table t1; |
| 163 |
check table t1; |
| 164 |
drop table t1; |
| 165 |
|
| 166 |
# |
| 167 |
# test of aria with huge number of packed fields |
| 168 |
# |
| 169 |
|
| 170 |
create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 |
| 171 |
int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17 |
| 172 |
int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int, |
| 173 |
i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34 |
| 174 |
int, i35 int, i36 int, i37 int, i38 int, i39 int, i40 int, i41 int, i42 int, |
| 175 |
i43 int, i44 int, i45 int, i46 int, i47 int, i48 int, i49 int, i50 int, i51 |
| 176 |
int, i52 int, i53 int, i54 int, i55 int, i56 int, i57 int, i58 int, i59 int, |
| 177 |
i60 int, i61 int, i62 int, i63 int, i64 int, i65 int, i66 int, i67 int, i68 |
| 178 |
int, i69 int, i70 int, i71 int, i72 int, i73 int, i74 int, i75 int, i76 int, |
| 179 |
i77 int, i78 int, i79 int, i80 int, i81 int, i82 int, i83 int, i84 int, i85 |
| 180 |
int, i86 int, i87 int, i88 int, i89 int, i90 int, i91 int, i92 int, i93 int, |
| 181 |
i94 int, i95 int, i96 int, i97 int, i98 int, i99 int, i100 int, i101 int, i102 |
| 182 |
int, i103 int, i104 int, i105 int, i106 int, i107 int, i108 int, i109 int, i110 |
| 183 |
int, i111 int, i112 int, i113 int, i114 int, i115 int, i116 int, i117 int, i118 |
| 184 |
int, i119 int, i120 int, i121 int, i122 int, i123 int, i124 int, i125 int, i126 |
| 185 |
int, i127 int, i128 int, i129 int, i130 int, i131 int, i132 int, i133 int, i134 |
| 186 |
int, i135 int, i136 int, i137 int, i138 int, i139 int, i140 int, i141 int, i142 |
| 187 |
int, i143 int, i144 int, i145 int, i146 int, i147 int, i148 int, i149 int, i150 |
| 188 |
int, i151 int, i152 int, i153 int, i154 int, i155 int, i156 int, i157 int, i158 |
| 189 |
int, i159 int, i160 int, i161 int, i162 int, i163 int, i164 int, i165 int, i166 |
| 190 |
int, i167 int, i168 int, i169 int, i170 int, i171 int, i172 int, i173 int, i174 |
| 191 |
int, i175 int, i176 int, i177 int, i178 int, i179 int, i180 int, i181 int, i182 |
| 192 |
int, i183 int, i184 int, i185 int, i186 int, i187 int, i188 int, i189 int, i190 |
| 193 |
int, i191 int, i192 int, i193 int, i194 int, i195 int, i196 int, i197 int, i198 |
| 194 |
int, i199 int, i200 int, i201 int, i202 int, i203 int, i204 int, i205 int, i206 |
| 195 |
int, i207 int, i208 int, i209 int, i210 int, i211 int, i212 int, i213 int, i214 |
| 196 |
int, i215 int, i216 int, i217 int, i218 int, i219 int, i220 int, i221 int, i222 |
| 197 |
int, i223 int, i224 int, i225 int, i226 int, i227 int, i228 int, i229 int, i230 |
| 198 |
int, i231 int, i232 int, i233 int, i234 int, i235 int, i236 int, i237 int, i238 |
| 199 |
int, i239 int, i240 int, i241 int, i242 int, i243 int, i244 int, i245 int, i246 |
| 200 |
int, i247 int, i248 int, i249 int, i250 int, i251 int, i252 int, i253 int, i254 |
| 201 |
int, i255 int, i256 int, i257 int, i258 int, i259 int, i260 int, i261 int, i262 |
| 202 |
int, i263 int, i264 int, i265 int, i266 int, i267 int, i268 int, i269 int, i270 |
| 203 |
int, i271 int, i272 int, i273 int, i274 int, i275 int, i276 int, i277 int, i278 |
| 204 |
int, i279 int, i280 int, i281 int, i282 int, i283 int, i284 int, i285 int, i286 |
| 205 |
int, i287 int, i288 int, i289 int, i290 int, i291 int, i292 int, i293 int, i294 |
| 206 |
int, i295 int, i296 int, i297 int, i298 int, i299 int, i300 int, i301 int, i302 |
| 207 |
int, i303 int, i304 int, i305 int, i306 int, i307 int, i308 int, i309 int, i310 |
| 208 |
int, i311 int, i312 int, i313 int, i314 int, i315 int, i316 int, i317 int, i318 |
| 209 |
int, i319 int, i320 int, i321 int, i322 int, i323 int, i324 int, i325 int, i326 |
| 210 |
int, i327 int, i328 int, i329 int, i330 int, i331 int, i332 int, i333 int, i334 |
| 211 |
int, i335 int, i336 int, i337 int, i338 int, i339 int, i340 int, i341 int, i342 |
| 212 |
int, i343 int, i344 int, i345 int, i346 int, i347 int, i348 int, i349 int, i350 |
| 213 |
int, i351 int, i352 int, i353 int, i354 int, i355 int, i356 int, i357 int, i358 |
| 214 |
int, i359 int, i360 int, i361 int, i362 int, i363 int, i364 int, i365 int, i366 |
| 215 |
int, i367 int, i368 int, i369 int, i370 int, i371 int, i372 int, i373 int, i374 |
| 216 |
int, i375 int, i376 int, i377 int, i378 int, i379 int, i380 int, i381 int, i382 |
| 217 |
int, i383 int, i384 int, i385 int, i386 int, i387 int, i388 int, i389 int, i390 |
| 218 |
int, i391 int, i392 int, i393 int, i394 int, i395 int, i396 int, i397 int, i398 |
| 219 |
int, i399 int, i400 int, i401 int, i402 int, i403 int, i404 int, i405 int, i406 |
| 220 |
int, i407 int, i408 int, i409 int, i410 int, i411 int, i412 int, i413 int, i414 |
| 221 |
int, i415 int, i416 int, i417 int, i418 int, i419 int, i420 int, i421 int, i422 |
| 222 |
int, i423 int, i424 int, i425 int, i426 int, i427 int, i428 int, i429 int, i430 |
| 223 |
int, i431 int, i432 int, i433 int, i434 int, i435 int, i436 int, i437 int, i438 |
| 224 |
int, i439 int, i440 int, i441 int, i442 int, i443 int, i444 int, i445 int, i446 |
| 225 |
int, i447 int, i448 int, i449 int, i450 int, i451 int, i452 int, i453 int, i454 |
| 226 |
int, i455 int, i456 int, i457 int, i458 int, i459 int, i460 int, i461 int, i462 |
| 227 |
int, i463 int, i464 int, i465 int, i466 int, i467 int, i468 int, i469 int, i470 |
| 228 |
int, i471 int, i472 int, i473 int, i474 int, i475 int, i476 int, i477 int, i478 |
| 229 |
int, i479 int, i480 int, i481 int, i482 int, i483 int, i484 int, i485 int, i486 |
| 230 |
int, i487 int, i488 int, i489 int, i490 int, i491 int, i492 int, i493 int, i494 |
| 231 |
int, i495 int, i496 int, i497 int, i498 int, i499 int, i500 int, i501 int, i502 |
| 232 |
int, i503 int, i504 int, i505 int, i506 int, i507 int, i508 int, i509 int, i510 |
| 233 |
int, i511 int, i512 int, i513 int, i514 int, i515 int, i516 int, i517 int, i518 |
| 234 |
int, i519 int, i520 int, i521 int, i522 int, i523 int, i524 int, i525 int, i526 |
| 235 |
int, i527 int, i528 int, i529 int, i530 int, i531 int, i532 int, i533 int, i534 |
| 236 |
int, i535 int, i536 int, i537 int, i538 int, i539 int, i540 int, i541 int, i542 |
| 237 |
int, i543 int, i544 int, i545 int, i546 int, i547 int, i548 int, i549 int, i550 |
| 238 |
int, i551 int, i552 int, i553 int, i554 int, i555 int, i556 int, i557 int, i558 |
| 239 |
int, i559 int, i560 int, i561 int, i562 int, i563 int, i564 int, i565 int, i566 |
| 240 |
int, i567 int, i568 int, i569 int, i570 int, i571 int, i572 int, i573 int, i574 |
| 241 |
int, i575 int, i576 int, i577 int, i578 int, i579 int, i580 int, i581 int, i582 |
| 242 |
int, i583 int, i584 int, i585 int, i586 int, i587 int, i588 int, i589 int, i590 |
| 243 |
int, i591 int, i592 int, i593 int, i594 int, i595 int, i596 int, i597 int, i598 |
| 244 |
int, i599 int, i600 int, i601 int, i602 int, i603 int, i604 int, i605 int, i606 |
| 245 |
int, i607 int, i608 int, i609 int, i610 int, i611 int, i612 int, i613 int, i614 |
| 246 |
int, i615 int, i616 int, i617 int, i618 int, i619 int, i620 int, i621 int, i622 |
| 247 |
int, i623 int, i624 int, i625 int, i626 int, i627 int, i628 int, i629 int, i630 |
| 248 |
int, i631 int, i632 int, i633 int, i634 int, i635 int, i636 int, i637 int, i638 |
| 249 |
int, i639 int, i640 int, i641 int, i642 int, i643 int, i644 int, i645 int, i646 |
| 250 |
int, i647 int, i648 int, i649 int, i650 int, i651 int, i652 int, i653 int, i654 |
| 251 |
int, i655 int, i656 int, i657 int, i658 int, i659 int, i660 int, i661 int, i662 |
| 252 |
int, i663 int, i664 int, i665 int, i666 int, i667 int, i668 int, i669 int, i670 |
| 253 |
int, i671 int, i672 int, i673 int, i674 int, i675 int, i676 int, i677 int, i678 |
| 254 |
int, i679 int, i680 int, i681 int, i682 int, i683 int, i684 int, i685 int, i686 |
| 255 |
int, i687 int, i688 int, i689 int, i690 int, i691 int, i692 int, i693 int, i694 |
| 256 |
int, i695 int, i696 int, i697 int, i698 int, i699 int, i700 int, i701 int, i702 |
| 257 |
int, i703 int, i704 int, i705 int, i706 int, i707 int, i708 int, i709 int, i710 |
| 258 |
int, i711 int, i712 int, i713 int, i714 int, i715 int, i716 int, i717 int, i718 |
| 259 |
int, i719 int, i720 int, i721 int, i722 int, i723 int, i724 int, i725 int, i726 |
| 260 |
int, i727 int, i728 int, i729 int, i730 int, i731 int, i732 int, i733 int, i734 |
| 261 |
int, i735 int, i736 int, i737 int, i738 int, i739 int, i740 int, i741 int, i742 |
| 262 |
int, i743 int, i744 int, i745 int, i746 int, i747 int, i748 int, i749 int, i750 |
| 263 |
int, i751 int, i752 int, i753 int, i754 int, i755 int, i756 int, i757 int, i758 |
| 264 |
int, i759 int, i760 int, i761 int, i762 int, i763 int, i764 int, i765 int, i766 |
| 265 |
int, i767 int, i768 int, i769 int, i770 int, i771 int, i772 int, i773 int, i774 |
| 266 |
int, i775 int, i776 int, i777 int, i778 int, i779 int, i780 int, i781 int, i782 |
| 267 |
int, i783 int, i784 int, i785 int, i786 int, i787 int, i788 int, i789 int, i790 |
| 268 |
int, i791 int, i792 int, i793 int, i794 int, i795 int, i796 int, i797 int, i798 |
| 269 |
int, i799 int, i800 int, i801 int, i802 int, i803 int, i804 int, i805 int, i806 |
| 270 |
int, i807 int, i808 int, i809 int, i810 int, i811 int, i812 int, i813 int, i814 |
| 271 |
int, i815 int, i816 int, i817 int, i818 int, i819 int, i820 int, i821 int, i822 |
| 272 |
int, i823 int, i824 int, i825 int, i826 int, i827 int, i828 int, i829 int, i830 |
| 273 |
int, i831 int, i832 int, i833 int, i834 int, i835 int, i836 int, i837 int, i838 |
| 274 |
int, i839 int, i840 int, i841 int, i842 int, i843 int, i844 int, i845 int, i846 |
| 275 |
int, i847 int, i848 int, i849 int, i850 int, i851 int, i852 int, i853 int, i854 |
| 276 |
int, i855 int, i856 int, i857 int, i858 int, i859 int, i860 int, i861 int, i862 |
| 277 |
int, i863 int, i864 int, i865 int, i866 int, i867 int, i868 int, i869 int, i870 |
| 278 |
int, i871 int, i872 int, i873 int, i874 int, i875 int, i876 int, i877 int, i878 |
| 279 |
int, i879 int, i880 int, i881 int, i882 int, i883 int, i884 int, i885 int, i886 |
| 280 |
int, i887 int, i888 int, i889 int, i890 int, i891 int, i892 int, i893 int, i894 |
| 281 |
int, i895 int, i896 int, i897 int, i898 int, i899 int, i900 int, i901 int, i902 |
| 282 |
int, i903 int, i904 int, i905 int, i906 int, i907 int, i908 int, i909 int, i910 |
| 283 |
int, i911 int, i912 int, i913 int, i914 int, i915 int, i916 int, i917 int, i918 |
| 284 |
int, i919 int, i920 int, i921 int, i922 int, i923 int, i924 int, i925 int, i926 |
| 285 |
int, i927 int, i928 int, i929 int, i930 int, i931 int, i932 int, i933 int, i934 |
| 286 |
int, i935 int, i936 int, i937 int, i938 int, i939 int, i940 int, i941 int, i942 |
| 287 |
int, i943 int, i944 int, i945 int, i946 int, i947 int, i948 int, i949 int, i950 |
| 288 |
int, i951 int, i952 int, i953 int, i954 int, i955 int, i956 int, i957 int, i958 |
| 289 |
int, i959 int, i960 int, i961 int, i962 int, i963 int, i964 int, i965 int, i966 |
| 290 |
int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 |
| 291 |
int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 |
| 292 |
int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 |
| 293 |
int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 |
| 294 |
int, i999 int, i1000 int, b blob) row_format=dynamic; |
| 295 |
insert into t1 values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 296 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 297 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 298 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 299 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 300 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 301 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 302 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 303 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 304 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 305 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 306 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 307 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 308 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 309 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 310 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 311 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 312 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 313 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 314 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 315 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 316 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 317 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 318 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 319 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 320 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 321 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 322 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 323 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 324 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 325 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 326 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 327 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 328 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 329 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 330 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 331 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 332 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, |
| 333 |
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); |
| 334 |
update t1 set b=repeat('a',256); |
| 335 |
update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0; |
| 336 |
check table t1; |
| 337 |
delete from t1 where i8=1; |
| 338 |
select i1,i2 from t1; |
| 339 |
check table t1; |
| 340 |
drop table t1; |
| 341 |
|
| 342 |
# |
| 343 |
# Test of REPAIR that once failed |
| 344 |
# |
| 345 |
CREATE TABLE `t1` ( |
| 346 |
`post_id` mediumint(8) unsigned NOT NULL auto_increment, |
| 347 |
`topic_id` mediumint(8) unsigned NOT NULL default '0', |
| 348 |
`post_time` datetime NOT NULL default '0000-00-00 00:00:00', |
| 349 |
`post_text` text NOT NULL, |
| 350 |
`icon_url` varchar(10) NOT NULL default '', |
| 351 |
`sign` tinyint(1) unsigned NOT NULL default '0', |
| 352 |
`post_edit` varchar(150) NOT NULL default '', |
| 353 |
`poster_login` varchar(35) NOT NULL default '', |
| 354 |
`ip` varchar(15) NOT NULL default '', |
| 355 |
PRIMARY KEY (`post_id`), |
| 356 |
KEY `post_time` (`post_time`), |
| 357 |
KEY `ip` (`ip`), |
| 358 |
KEY `poster_login` (`poster_login`), |
| 359 |
KEY `topic_id` (`topic_id`), |
| 360 |
FULLTEXT KEY `post_text` (`post_text`) |
| 361 |
) TRANSACTIONAL=0; |
| 362 |
|
| 363 |
INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); |
| 364 |
|
| 365 |
REPAIR TABLE t1; |
| 366 |
CHECK TABLE t1; |
| 367 |
drop table t1; |
| 368 |
|
| 369 |
# |
| 370 |
# Test of creating table with too long key |
| 371 |
# |
| 372 |
|
| 373 |
--error 1071 |
| 374 |
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), f varchar(255), g varchar(255), h varchar(255), i varchar(255), j varchar(255), KEY t1 (a, b, c, d, e, f, g, h, i, j)); |
| 375 |
--error 1070 |
| 376 |
CREATE TABLE t1 (a varchar(1), b varchar(1), key (a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b,a,b)); |
| 377 |
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), f varchar(255), g varchar(255), h varchar(255), i varchar(255), j varchar(255)); |
| 378 |
--error 1071 |
| 379 |
ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j); |
| 380 |
DROP TABLE t1; |
| 381 |
|
| 382 |
# |
| 383 |
# Test of cardinality of keys with NULL |
| 384 |
# |
| 385 |
|
| 386 |
CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); |
| 387 |
INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); |
| 388 |
INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); |
| 389 |
create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); |
| 390 |
INSERT into t2 values (1,1,1), (2,2,2); |
| 391 |
optimize table t1; |
| 392 |
check table t1; |
| 393 |
show index from t1; |
| 394 |
explain select * from t1,t2 where t1.a=t2.a; |
| 395 |
explain select * from t1,t2 force index(a) where t1.a=t2.a; |
| 396 |
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; |
| 397 |
explain select * from t1,t2 where t1.b=t2.b; |
| 398 |
explain select * from t1,t2 force index(c) where t1.a=t2.a; |
| 399 |
explain select * from t1 where a=0 or a=2; |
| 400 |
explain select * from t1 force index (a) where a=0 or a=2; |
| 401 |
explain select * from t1 where c=1; |
| 402 |
explain select * from t1 use index() where c=1; |
| 403 |
drop table t1,t2; |
| 404 |
|
| 405 |
# |
| 406 |
# Test bug when updating a split dynamic row where keys are not changed |
| 407 |
# |
| 408 |
|
| 409 |
create table t1 (a int not null auto_increment primary key, b varchar(255)); |
| 410 |
insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); |
| 411 |
update t1 set b=repeat(left(b,1),200) where a=1; |
| 412 |
delete from t1 where (a & 1)= 0; |
| 413 |
update t1 set b=repeat('e',200) where a=1; |
| 414 |
flush tables; |
| 415 |
check table t1; |
| 416 |
|
| 417 |
# |
| 418 |
# check updating with keys |
| 419 |
# |
| 420 |
|
| 421 |
disable_query_log; |
| 422 |
let $1 = 100; |
| 423 |
while ($1) |
| 424 |
{ |
| 425 |
eval insert into t1 (b) values (repeat(char(($1 & 32)+65), $1)); |
| 426 |
dec $1; |
| 427 |
} |
| 428 |
enable_query_log; |
| 429 |
update t1 set b=repeat(left(b,1),255) where a between 1 and 5; |
| 430 |
update t1 set b=repeat(left(b,1),10) where a between 32 and 43; |
| 431 |
update t1 set b=repeat(left(b,1),2) where a between 64 and 66; |
| 432 |
update t1 set b=repeat(left(b,1),65) where a between 67 and 70; |
| 433 |
check table t1; |
| 434 |
insert into t1 (b) values (repeat('z',100)); |
| 435 |
update t1 set b="test" where left(b,1) > 'n'; |
| 436 |
check table t1; |
| 437 |
drop table t1; |
| 438 |
|
| 439 |
# |
| 440 |
# Test space-stripping features |
| 441 |
# |
| 442 |
create table t1 ( a text not null, key a (a(20))); |
| 443 |
insert into t1 values ('aaa '),('aaa'),('aa'); |
| 444 |
check table t1; |
| 445 |
repair table t1; |
| 446 |
select concat(a,'.') from t1 where a='aaa'; |
| 447 |
select concat(a,'.') from t1 where binary a='aaa'; |
| 448 |
update t1 set a='bbb' where a='aaa'; |
| 449 |
select concat(a,'.') from t1; |
| 450 |
drop table t1; |
| 451 |
|
| 452 |
# |
| 453 |
# Test again but with dynamic format |
| 454 |
# |
| 455 |
create table t1 ( a text not null, key a (a(20))) row_format=dynamic; |
| 456 |
insert into t1 values ('aaa '),('aaa'),('aa'); |
| 457 |
check table t1; |
| 458 |
repair table t1; |
| 459 |
select concat(a,'.') from t1 where a='aaa'; |
| 460 |
select concat(a,'.') from t1 where binary a='aaa'; |
| 461 |
update t1 set a='bbb' where a='aaa'; |
| 462 |
select concat(a,'.') from t1; |
| 463 |
drop table t1; |
| 464 |
|
| 465 |
# |
| 466 |
# More space testing |
| 467 |
# |
| 468 |
|
| 469 |
create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); |
| 470 |
insert into t1 values('807780', '477', '165'); |
| 471 |
insert into t1 values('807780', '477', '162'); |
| 472 |
insert into t1 values('807780', '472', '162'); |
| 473 |
select * from t1 where a='807780' and b='477' and c='165'; |
| 474 |
drop table t1; |
| 475 |
|
| 476 |
# |
| 477 |
# Space-stripping in prefix_search |
| 478 |
# |
| 479 |
|
| 480 |
CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); |
| 481 |
INSERT t1 VALUES ("can \tcan"); |
| 482 |
INSERT t1 VALUES ("can can"); |
| 483 |
INSERT t1 VALUES ("can"); |
| 484 |
SELECT * FROM t1; |
| 485 |
CHECK TABLE t1; |
| 486 |
DROP TABLE t1; |
| 487 |
|
| 488 |
# |
| 489 |
# Verify blob handling |
| 490 |
# |
| 491 |
|
| 492 |
create table t1 (a blob); |
| 493 |
insert into t1 values('a '),('a'); |
| 494 |
select concat(a,'.') from t1 where a='a'; |
| 495 |
select concat(a,'.') from t1 where a='a '; |
| 496 |
alter table t1 add key(a(2)); |
| 497 |
select concat(a,'.') from t1 where a='a'; |
| 498 |
select concat(a,'.') from t1 where a='a '; |
| 499 |
drop table t1; |
| 500 |
|
| 501 |
# |
| 502 |
# Test text and unique |
| 503 |
# |
| 504 |
create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); |
| 505 |
insert into t1 (b) values ('a'),('b'),('c'); |
| 506 |
select concat(b,'.') from t1; |
| 507 |
update t1 set b='b ' where a=2; |
| 508 |
--error ER_DUP_ENTRY |
| 509 |
update t1 set b='b ' where a > 1; |
| 510 |
--error ER_DUP_ENTRY |
| 511 |
insert into t1 (b) values ('b'); |
| 512 |
select * from t1; |
| 513 |
delete from t1 where b='b'; |
| 514 |
select a,concat(b,'.') from t1; |
| 515 |
drop table t1; |
| 516 |
|
| 517 |
# |
| 518 |
# Test keys with 0 segments |
| 519 |
# |
| 520 |
create table t1 (a int not null); |
| 521 |
create table t2 (a int not null, primary key (a)); |
| 522 |
insert into t1 values (1); |
| 523 |
insert into t2 values (1),(2); |
| 524 |
select sql_big_result distinct t1.a from t1,t2 order by t2.a; |
| 525 |
select distinct t1.a from t1,t2 order by t2.a; |
| 526 |
select sql_big_result distinct t1.a from t1,t2; |
| 527 |
explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; |
| 528 |
explain select distinct t1.a from t1,t2 order by t2.a; |
| 529 |
drop table t1,t2; |
| 530 |
|
| 531 |
# |
| 532 |
# Test freshly imported table and LIMIT |
| 533 |
# |
| 534 |
create table t1 ( |
| 535 |
c1 varchar(32), |
| 536 |
key (c1) |
| 537 |
); |
| 538 |
alter table t1 disable keys; |
| 539 |
insert into t1 values ('a'), ('b'); |
| 540 |
select c1 from t1 order by c1 limit 1; |
| 541 |
drop table t1; |
| 542 |
|
| 543 |
# |
| 544 |
# Test join that could miss concurrently inserted row |
| 545 |
# Note that for the moment Aria only supports multiple writers if we have |
| 546 |
# static or dynamic row format |
| 547 |
# |
| 548 |
# Partial key. |
| 549 |
create table t1 (a int not null, primary key(a)) ROW_FORMAT=FIXED; |
| 550 |
create table t2 (a int not null, b int not null, primary key(a,b)) ROW_FORMAT=FIXED; |
| 551 |
insert into t1 values (1),(2),(3),(4),(5),(6); |
| 552 |
insert into t2 values (1,1),(2,1); |
| 553 |
set autocommit=0; |
| 554 |
begin; |
| 555 |
lock tables t1 read local, t2 read local; |
| 556 |
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; |
| 557 |
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK); |
| 558 |
insert into t2 values(2,0); |
| 559 |
commit; |
| 560 |
disconnect root; |
| 561 |
connection default; |
| 562 |
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; |
| 563 |
unlock tables; |
| 564 |
drop table t1,t2; |
| 565 |
set autocommit=1; |
| 566 |
# |
| 567 |
# Full key. |
| 568 |
CREATE TABLE t1 (c1 varchar(250) NOT NULL) ROW_FORMAT=DYNAMIC; |
| 569 |
CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)) ROW_FORMAT=DYNAMIC; |
| 570 |
INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); |
| 571 |
INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); |
| 572 |
LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; |
| 573 |
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 |
| 574 |
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; |
| 575 |
connect (con1,localhost,root,,); |
| 576 |
connection con1; |
| 577 |
INSERT INTO t2 VALUES ('test000001'), ('test000005'); |
| 578 |
disconnect con1; |
| 579 |
connection default; |
| 580 |
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 |
| 581 |
WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; |
| 582 |
unlock tables; |
| 583 |
DROP TABLE t1,t2; |
| 584 |
|
| 585 |
# |
| 586 |
# Test RTREE index |
| 587 |
# |
| 588 |
--error ER_WRONG_ARGUMENTS |
| 589 |
CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)); |
| 590 |
# INSERT INTO t1 VALUES (1,1),(1,1); |
| 591 |
# DELETE FROM rt WHERE a<1; |
| 592 |
# DROP TABLE IF EXISTS t1; |
| 593 |
|
| 594 |
create table t1 (a int, b varchar(200), c text not null) checksum=1; |
| 595 |
create table t2 (a int, b varchar(200), c text not null) checksum=0; |
| 596 |
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); |
| 597 |
insert t2 select * from t1; |
| 598 |
checksum table t1, t2, t3 quick; |
| 599 |
checksum table t1, t2, t3; |
| 600 |
checksum table t1, t2, t3 extended; |
| 601 |
#show table status; |
| 602 |
drop table t1,t2; |
| 603 |
|
| 604 |
create table t1 (a int, key (a)); |
| 605 |
show keys from t1; |
| 606 |
alter table t1 disable keys; |
| 607 |
show keys from t1; |
| 608 |
create table t2 (a int); |
| 609 |
let $i=1000; |
| 610 |
set @@rand_seed1=31415926,@@rand_seed2=2718281828; |
| 611 |
--disable_query_log |
| 612 |
while ($i) |
| 613 |
{ |
| 614 |
dec $i; |
| 615 |
insert t2 values (rand()*100000); |
| 616 |
} |
| 617 |
--enable_query_log |
| 618 |
insert t1 select * from t2; |
| 619 |
show keys from t1; |
| 620 |
alter table t1 enable keys; |
| 621 |
show keys from t1; |
| 622 |
alter table t1 engine=heap; |
| 623 |
alter table t1 disable keys; |
| 624 |
show keys from t1; |
| 625 |
drop table t1,t2; |
| 626 |
|
| 627 |
# |
| 628 |
# Index search for NULL in blob |
| 629 |
# |
| 630 |
create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); |
| 631 |
insert into t1 values (null,''), (null,''); |
| 632 |
explain select count(*) from t1 where a is null; |
| 633 |
insert into t1 values(1,'a'); |
| 634 |
explain select count(*) from t1 where a is null; |
| 635 |
explain select count(*) from t1 where a = 'a'; |
| 636 |
select count(*) from t1 where a is null; |
| 637 |
drop table t1; |
| 638 |
|
| 639 |
# |
| 640 |
# Test corruption Can't open file: 'table.MYI' (errno: 145) |
| 641 |
# |
| 642 |
create table t1 (c1 int, c2 varchar(4) not null default '', |
| 643 |
key(c2(3))) default charset=utf8; |
| 644 |
insert into t1 values (1,'A'), (2, 'B'), (3, 'A'); |
| 645 |
update t1 set c2='A B' where c1=2; |
| 646 |
check table t1; |
| 647 |
drop table t1; |
| 648 |
|
| 649 |
# |
| 650 |
# Test CHECKSUM TABLE |
| 651 |
# |
| 652 |
|
| 653 |
create table t1 (c1 int); |
| 654 |
insert into t1 values (1),(2),(3),(4); |
| 655 |
checksum table t1; |
| 656 |
delete from t1 where c1 = 1; |
| 657 |
create table t2 as select * from t1; |
| 658 |
# The following returns 0 with the bug in place. |
| 659 |
checksum table t1; |
| 660 |
# The above should give the same number as the following. |
| 661 |
checksum table t2; |
| 662 |
drop table t1, t2; |
| 663 |
|
| 664 |
CREATE TABLE t1 ( |
| 665 |
twenty int(4), |
| 666 |
hundred int(4) NOT NULL |
| 667 |
) CHECKSUM=1; |
| 668 |
INSERT INTO t1 VALUES (11,91); |
| 669 |
check table t1 extended; |
| 670 |
checksum table t1; |
| 671 |
checksum table t1 extended; |
| 672 |
alter table t1 row_format=fixed; |
| 673 |
checksum table t1; |
| 674 |
alter table t1 row_format=dynamic; |
| 675 |
checksum table t1; |
| 676 |
alter table t1 engine=myisam; |
| 677 |
checksum table t1; |
| 678 |
drop table t1; |
| 679 |
|
| 680 |
# |
| 681 |
# aria_stats_method variable. |
| 682 |
# |
| 683 |
|
| 684 |
show variables like 'aria_stats_method'; |
| 685 |
|
| 686 |
create table t1 (a int, key(a)); |
| 687 |
insert into t1 values (0),(1),(2),(3),(4); |
| 688 |
insert into t1 select NULL from t1; |
| 689 |
|
| 690 |
# default: NULLs considered inequal |
| 691 |
analyze table t1; |
| 692 |
show index from t1; |
| 693 |
insert into t1 values (11); |
| 694 |
delete from t1 where a=11; |
| 695 |
check table t1; |
| 696 |
show index from t1; |
| 697 |
|
| 698 |
# Set nulls to be equal: |
| 699 |
set aria_stats_method=nulls_equal; |
| 700 |
|
| 701 |
set @save_use_stat_tables= @@use_stat_tables; |
| 702 |
set @@use_stat_tables= COMPLEMENTARY; |
| 703 |
|
| 704 |
show variables like 'aria_stats_method'; |
| 705 |
insert into t1 values (11); |
| 706 |
delete from t1 where a=11; |
| 707 |
|
| 708 |
analyze table t1; |
| 709 |
show index from t1; |
| 710 |
|
| 711 |
insert into t1 values (11); |
| 712 |
delete from t1 where a=11; |
| 713 |
|
| 714 |
check table t1; |
| 715 |
show index from t1; |
| 716 |
|
| 717 |
set @@use_stat_tables= @save_use_stat_tables; |
| 718 |
# Set nulls back to be equal |
| 719 |
set aria_stats_method=DEFAULT; |
| 720 |
show variables like 'aria_stats_method'; |
| 721 |
insert into t1 values (11); |
| 722 |
delete from t1 where a=11; |
| 723 |
|
| 724 |
analyze table t1; |
| 725 |
show index from t1; |
| 726 |
|
| 727 |
insert into t1 values (11); |
| 728 |
delete from t1 where a=11; |
| 729 |
|
| 730 |
check table t1; |
| 731 |
show index from t1; |
| 732 |
|
| 733 |
drop table t1; |
| 734 |
|
| 735 |
# WL#2609, CSC#XXXX: ARIA |
| 736 |
set aria_stats_method=nulls_ignored; |
| 737 |
show variables like 'aria_stats_method'; |
| 738 |
|
| 739 |
set @save_use_stat_tables= @@use_stat_tables; |
| 740 |
set @@use_stat_tables= COMPLEMENTARY; |
| 741 |
|
| 742 |
create table t1 ( |
| 743 |
a char(3), b char(4), c char(5), d char(6), |
| 744 |
key(a,b,c,d) |
| 745 |
); |
| 746 |
insert into t1 values ('bcd','def1', NULL, 'zz'); |
| 747 |
insert into t1 values ('bcd','def2', NULL, 'zz'); |
| 748 |
insert into t1 values ('bce','def1', 'yuu', NULL); |
| 749 |
insert into t1 values ('bce','def2', NULL, 'quux'); |
| 750 |
analyze table t1; |
| 751 |
show index from t1; |
| 752 |
delete from t1; |
| 753 |
# This will give you different messages depending on if we are using |
| 754 |
# row base or stmt based replication as stmt base replication will use |
| 755 |
# truncate and row based will delete things row by row. |
| 756 |
--replace_result "Table is already up to date" "OK" |
| 757 |
analyze table t1; |
| 758 |
show index from t1; |
| 759 |
|
| 760 |
set @@use_stat_tables= @save_use_stat_tables; |
| 761 |
set aria_stats_method=DEFAULT; |
| 762 |
|
| 763 |
drop table t1; |
| 764 |
|
| 765 |
# |
| 766 |
# Test key value packing for TINYBLOBs |
| 767 |
# |
| 768 |
|
| 769 |
create table t1( |
| 770 |
cip INT NOT NULL, |
| 771 |
time TIME NOT NULL, |
| 772 |
score INT NOT NULL DEFAULT 0, |
| 773 |
bob TINYBLOB |
| 774 |
); |
| 775 |
|
| 776 |
insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); |
| 777 |
insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), |
| 778 |
(6, 'c', '00:06'); |
| 779 |
select * from t1 where bob is null and cip=1; |
| 780 |
create index bug on t1 (bob(22), cip, time); |
| 781 |
select * from t1 where bob is null and cip=1; |
| 782 |
drop table t1; |
| 783 |
|
| 784 |
# |
| 785 |
# Test COUNT(*) table with different INDEX |
| 786 |
# |
| 787 |
|
| 788 |
create table t1 ( |
| 789 |
id1 int not null auto_increment, |
| 790 |
id2 int not null default '0', |
| 791 |
t text not null, |
| 792 |
primary key (id1), |
| 793 |
key x (id2, t(32)) |
| 794 |
) engine=aria; # engine clause is redundant but it's to test its parsing |
| 795 |
insert into t1 (id2, t) values |
| 796 |
(10, 'abc'), (10, 'abc'), (10, 'abc'), |
| 797 |
(20, 'abc'), (20, 'abc'), (20, 'def'), |
| 798 |
(10, 'abc'), (10, 'abc'); |
| 799 |
select count(*) from t1 where id2 = 10; |
| 800 |
select count(id1) from t1 where id2 = 10; |
| 801 |
drop table t1; |
| 802 |
|
| 803 |
# |
| 804 |
# Test MIN and MAX functions in queries |
| 805 |
# |
| 806 |
|
| 807 |
CREATE TABLE t1(a TINYINT, KEY(a)); |
| 808 |
INSERT INTO t1 VALUES(1); |
| 809 |
SELECT MAX(a) FROM t1 IGNORE INDEX(a); |
| 810 |
ALTER TABLE t1 DISABLE KEYS; |
| 811 |
SELECT MAX(a) FROM t1; |
| 812 |
SELECT MAX(a) FROM t1 IGNORE INDEX(a); |
| 813 |
DROP TABLE t1; |
| 814 |
|
| 815 |
# |
| 816 |
# Test update of table joined to self |
| 817 |
# |
| 818 |
CREATE TABLE t1(a CHAR(9), b VARCHAR(7)); |
| 819 |
INSERT INTO t1(a) VALUES('xxxxxxxxx'),('xxxxxxxxx'); |
| 820 |
UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; |
| 821 |
SELECT * FROM t1; |
| 822 |
DROP TABLE t1; |
| 823 |
|
| 824 |
# |
| 825 |
# OPTIMIZE TABLE with multiple threads |
| 826 |
# |
| 827 |
SET @@aria_repair_threads=2; |
| 828 |
SHOW VARIABLES LIKE 'aria_repair%'; |
| 829 |
# |
| 830 |
# Test OPTIMIZE. This creates a new data file. |
| 831 |
CREATE TABLE t1 ( |
| 832 |
`_id` int(11) NOT NULL default '0', |
| 833 |
`url` text, |
| 834 |
`email` text, |
| 835 |
`description` text, |
| 836 |
`loverlap` int(11) default NULL, |
| 837 |
`roverlap` int(11) default NULL, |
| 838 |
`lneighbor_id` int(11) default NULL, |
| 839 |
`rneighbor_id` int(11) default NULL, |
| 840 |
`length_` int(11) default NULL, |
| 841 |
`sequence` mediumtext, |
| 842 |
`name` text, |
| 843 |
`_obj_class` text NOT NULL, |
| 844 |
PRIMARY KEY (`_id`), |
| 845 |
UNIQUE KEY `sequence_name_index` (`name`(50)), |
| 846 |
KEY (`length_`) |
| 847 |
) DEFAULT CHARSET=latin1 row_format=dynamic; |
| 848 |
# |
| 849 |
INSERT INTO t1 VALUES |
| 850 |
(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), |
| 851 |
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), |
| 852 |
(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), |
| 853 |
(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), |
| 854 |
(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), |
| 855 |
(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), |
| 856 |
(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), |
| 857 |
(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), |
| 858 |
(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); |
| 859 |
# |
| 860 |
SELECT _id FROM t1; |
| 861 |
DELETE FROM t1 WHERE _id < 8; |
| 862 |
SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; |
| 863 |
CHECK TABLE t1 EXTENDED; |
| 864 |
OPTIMIZE TABLE t1; |
| 865 |
CHECK TABLE t1 EXTENDED; |
| 866 |
SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; |
| 867 |
SELECT _id FROM t1; |
| 868 |
DROP TABLE t1; |
| 869 |
# |
| 870 |
# Test REPAIR QUICK. This retains the old data file. |
| 871 |
CREATE TABLE t1 ( |
| 872 |
`_id` int(11) NOT NULL default '0', |
| 873 |
`url` text, |
| 874 |
`email` text, |
| 875 |
`description` text, |
| 876 |
`loverlap` int(11) default NULL, |
| 877 |
`roverlap` int(11) default NULL, |
| 878 |
`lneighbor_id` int(11) default NULL, |
| 879 |
`rneighbor_id` int(11) default NULL, |
| 880 |
`length_` int(11) default NULL, |
| 881 |
`sequence` mediumtext, |
| 882 |
`name` text, |
| 883 |
`_obj_class` text NOT NULL, |
| 884 |
PRIMARY KEY (`_id`), |
| 885 |
UNIQUE KEY `sequence_name_index` (`name`(50)), |
| 886 |
KEY (`length_`) |
| 887 |
) DEFAULT CHARSET=latin1 row_format=dynamic; |
| 888 |
# |
| 889 |
INSERT INTO t1 VALUES |
| 890 |
(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), |
| 891 |
(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), |
| 892 |
(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), |
| 893 |
(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), |
| 894 |
(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), |
| 895 |
(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), |
| 896 |
(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), |
| 897 |
(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), |
| 898 |
(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); |
| 899 |
# |
| 900 |
SELECT _id FROM t1; |
| 901 |
DELETE FROM t1 WHERE _id < 8; |
| 902 |
SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; |
| 903 |
CHECK TABLE t1 EXTENDED; |
| 904 |
REPAIR TABLE t1 QUICK; |
| 905 |
CHECK TABLE t1 EXTENDED; |
| 906 |
SELECT table_name, engine, version, row_format, Table_rows, Data_free, create_options, table_comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' and TABLE_NAME='t1'; |
| 907 |
SELECT _id FROM t1; |
| 908 |
DROP TABLE t1; |
| 909 |
# |
| 910 |
SET @@aria_repair_threads=1; |
| 911 |
SHOW VARIABLES LIKE 'aria_repair%'; |
| 912 |
|
| 913 |
# |
| 914 |
# Test varchar |
| 915 |
# |
| 916 |
|
| 917 |
source include/varchar.inc; |
| 918 |
|
| 919 |
# |
| 920 |
# Some errors/warnings on create |
| 921 |
# |
| 922 |
|
| 923 |
create table t1 (v varchar(65530), key(v)); |
| 924 |
drop table if exists t1; |
| 925 |
set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for |
| 926 |
create table t1 (v varchar(65536)); |
| 927 |
show create table t1; |
| 928 |
drop table t1; |
| 929 |
set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for |
| 930 |
create table t1 (v varchar(65530) character set utf8); |
| 931 |
show create table t1; |
| 932 |
drop table t1; |
| 933 |
|
| 934 |
# ARIA specific varchar tests |
| 935 |
set statement sql_mode='' for |
| 936 |
create table t1 (v varchar(65535)); |
| 937 |
show create table t1; |
| 938 |
drop table t1; |
| 939 |
|
| 940 |
# |
| 941 |
# Test concurrent insert |
| 942 |
# First with static record length |
| 943 |
# |
| 944 |
set @save_concurrent_insert=@@concurrent_insert; |
| 945 |
set global concurrent_insert=1; |
| 946 |
create table t1 (a int) ROW_FORMAT=FIXED; |
| 947 |
insert into t1 values (1),(2),(3),(4),(5); |
| 948 |
lock table t1 read local; |
| 949 |
connect (con1,localhost,root,,); |
| 950 |
connection con1; |
| 951 |
# Insert in table without hole |
| 952 |
insert into t1 values(6),(7); |
| 953 |
connection default; |
| 954 |
unlock tables; |
| 955 |
delete from t1 where a>=3 and a<=4; |
| 956 |
lock table t1 read local; |
| 957 |
connection con1; |
| 958 |
set global concurrent_insert=2; |
| 959 |
# Insert in table with hole -> Should insert at end |
| 960 |
insert into t1 values (8),(9); |
| 961 |
connection default; |
| 962 |
unlock tables; |
| 963 |
# Insert into hole |
| 964 |
insert into t1 values (10),(11),(12); |
| 965 |
select * from t1; |
| 966 |
check table t1; |
| 967 |
drop table t1; |
| 968 |
disconnect con1; |
| 969 |
|
| 970 |
# Same test with dynamic record length |
| 971 |
create table t1 (a int, b varchar(30) default "hello") ROW_FORMAT=DYNAMIC; |
| 972 |
insert into t1 (a) values (1),(2),(3),(4),(5); |
| 973 |
lock table t1 read local; |
| 974 |
connect (con1,localhost,root,,); |
| 975 |
connection con1; |
| 976 |
# Insert in table without hole |
| 977 |
insert into t1 (a) values(6),(7); |
| 978 |
connection default; |
| 979 |
unlock tables; |
| 980 |
delete from t1 where a>=3 and a<=4; |
| 981 |
lock table t1 read local; |
| 982 |
connection con1; |
| 983 |
set global concurrent_insert=2; |
| 984 |
# Insert in table with hole -> Should insert at end |
| 985 |
insert into t1 (a) values (8),(9); |
| 986 |
connection default; |
| 987 |
unlock tables; |
| 988 |
# Insert into hole |
| 989 |
insert into t1 (a) values (10),(11),(12); |
| 990 |
select a from t1; |
| 991 |
check table t1; |
| 992 |
drop table t1; |
| 993 |
disconnect con1; |
| 994 |
set global concurrent_insert=@save_concurrent_insert; |
| 995 |
|
| 996 |
# |
| 997 |
# ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX |
| 998 |
# |
| 999 |
|
| 1000 |
create table t1 (a int, key(a)); |
| 1001 |
|
| 1002 |
insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL); |
| 1003 |
analyze table t1; |
| 1004 |
analyze table t1; |
| 1005 |
show keys from t1; |
| 1006 |
|
| 1007 |
alter table t1 disable keys; |
| 1008 |
alter table t1 enable keys; |
| 1009 |
show keys from t1; |
| 1010 |
|
| 1011 |
drop table t1; |
| 1012 |
|
| 1013 |
# |
| 1014 |
# Test temporary table with data directory option |
| 1015 |
# |
| 1016 |
connect (session1,localhost,root,,); |
| 1017 |
connect (session2,localhost,root,,); |
| 1018 |
|
| 1019 |
connection session1; |
| 1020 |
disable_query_log; |
| 1021 |
disable_warnings; |
| 1022 |
eval create temporary table t1 (a int) data directory="$MYSQLTEST_VARDIR/tmp" select 9 a; |
| 1023 |
enable_warnings; |
| 1024 |
enable_query_log; |
| 1025 |
disable_result_log; |
| 1026 |
show create table t1; |
| 1027 |
enable_result_log; |
| 1028 |
|
| 1029 |
connection session2; |
| 1030 |
disable_query_log; |
| 1031 |
|
| 1032 |
disable_warnings; |
| 1033 |
eval create temporary table t1 (a int) data directory="$MYSQLTEST_VARDIR/tmp" select 99 a; |
| 1034 |
enable_warnings; |
| 1035 |
enable_query_log; |
| 1036 |
disable_result_log; |
| 1037 |
show create table t1; |
| 1038 |
enable_result_log; |
| 1039 |
|
| 1040 |
connection default; |
| 1041 |
create table t1 (a int) select 42 a; |
| 1042 |
|
| 1043 |
connection session1; |
| 1044 |
select * from t1; |
| 1045 |
disconnect session1; |
| 1046 |
connection session2; |
| 1047 |
select * from t1; |
| 1048 |
disconnect session2; |
| 1049 |
connection default; |
| 1050 |
select * from t1; |
| 1051 |
drop table t1; |
| 1052 |
|
| 1053 |
--echo End of 4.1 tests |
| 1054 |
|
| 1055 |
# |
| 1056 |
# Test if PACK_KEYS option takes values greater than 1 while creating table |
| 1057 |
# |
| 1058 |
create table t1 (c1 int) pack_keys=0; |
| 1059 |
create table t2 (c1 int) pack_keys=1; |
| 1060 |
create table t3 (c1 int) pack_keys=default; |
| 1061 |
--error ER_PARSE_ERROR |
| 1062 |
create table t4 (c1 int) pack_keys=2; |
| 1063 |
drop table t1, t2, t3; |
| 1064 |
|
| 1065 |
# |
| 1066 |
# Bug#28476: force index on a disabled aria index gives error 124 |
| 1067 |
# |
| 1068 |
|
| 1069 |
CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)); |
| 1070 |
INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); |
| 1071 |
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; |
| 1072 |
ALTER TABLE t1 DISABLE KEYS; |
| 1073 |
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; |
| 1074 |
SELECT a FROM t1 USE INDEX (inx) WHERE a=1; |
| 1075 |
SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; |
| 1076 |
SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; |
| 1077 |
SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; |
| 1078 |
ALTER TABLE t1 ENABLE KEYS; |
| 1079 |
SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; |
| 1080 |
DROP TABLE t1; |
| 1081 |
|
| 1082 |
# |
| 1083 |
# Bug#4692 - DISABLE/ENABLE KEYS waste a space |
| 1084 |
# |
| 1085 |
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)); |
| 1086 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1087 |
SHOW TABLE STATUS LIKE 't1'; |
| 1088 |
INSERT INTO t1 VALUES (1,1); |
| 1089 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1090 |
SHOW TABLE STATUS LIKE 't1'; |
| 1091 |
ALTER TABLE t1 DISABLE KEYS; |
| 1092 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1093 |
SHOW TABLE STATUS LIKE 't1'; |
| 1094 |
ALTER TABLE t1 ENABLE KEYS; |
| 1095 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1096 |
SHOW TABLE STATUS LIKE 't1'; |
| 1097 |
ALTER TABLE t1 DISABLE KEYS; |
| 1098 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1099 |
SHOW TABLE STATUS LIKE 't1'; |
| 1100 |
ALTER TABLE t1 ENABLE KEYS; |
| 1101 |
--replace_column 6 # 7 # 8 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 19 # |
| 1102 |
SHOW TABLE STATUS LIKE 't1'; |
| 1103 |
#--exec ls -log var/mysqld.1/data/test/t1 |
| 1104 |
#--exec aria_chk -dvv var/mysqld.1/data/test/t1 |
| 1105 |
#--exec aria_chk -iev var/mysqld.1/data/test/t1 |
| 1106 |
--echo # Enable keys with parallel repair |
| 1107 |
SET @@aria_repair_threads=2; |
| 1108 |
ALTER TABLE t1 DISABLE KEYS; |
| 1109 |
ALTER TABLE t1 ENABLE KEYS; |
| 1110 |
SET @@aria_repair_threads=1; |
| 1111 |
CHECK TABLE t1 EXTENDED; |
| 1112 |
DROP TABLE t1; |
| 1113 |
|
| 1114 |
# |
| 1115 |
# Test doing delete with self-join |
| 1116 |
# |
| 1117 |
|
| 1118 |
CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)); |
| 1119 |
CREATE TABLE t2 LIKE t1; |
| 1120 |
|
| 1121 |
INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); |
| 1122 |
INSERT INTO t1 SELECT * FROM t2; |
| 1123 |
|
| 1124 |
SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; |
| 1125 |
SELECT * FROM t1; |
| 1126 |
DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; |
| 1127 |
SELECT * FROM t1; |
| 1128 |
|
| 1129 |
DROP TABLE t1, t2; |
| 1130 |
|
| 1131 |
# |
| 1132 |
# Bug#37310: 'on update CURRENT_TIMESTAMP' option crashes the table |
| 1133 |
# |
| 1134 |
CREATE TABLE t1 (a INT) ENGINE=ARIA CHECKSUM=1 ROW_FORMAT=DYNAMIC; |
| 1135 |
INSERT INTO t1 VALUES (0); |
| 1136 |
UPDATE t1 SET a=1; |
| 1137 |
SELECT a FROM t1; |
| 1138 |
CHECK TABLE t1; |
| 1139 |
INSERT INTO t1 VALUES (0), (5), (4), (2); |
| 1140 |
UPDATE t1 SET a=2; |
| 1141 |
SELECT a FROM t1; |
| 1142 |
CHECK TABLE t1; |
| 1143 |
DROP TABLE t1; |
| 1144 |
|
| 1145 |
--echo End of 5.0 tests |
| 1146 |
|
| 1147 |
|
| 1148 |
# |
| 1149 |
# Test of key_block_size |
| 1150 |
# |
| 1151 |
|
| 1152 |
create table t1 (a int not null, key `a` (a) key_block_size=1024); |
| 1153 |
show create table t1; |
| 1154 |
drop table t1; |
| 1155 |
|
| 1156 |
create table t1 (a int not null, key `a` (a) key_block_size=2048); |
| 1157 |
show create table t1; |
| 1158 |
drop table t1; |
| 1159 |
|
| 1160 |
create table t1 (a varchar(4096), key `a` (a)); |
| 1161 |
show create table t1; |
| 1162 |
drop table t1; |
| 1163 |
|
| 1164 |
create table t1 (a varchar(4096), key `a` (a) key_block_size=1024); |
| 1165 |
show create table t1; |
| 1166 |
drop table t1; |
| 1167 |
|
| 1168 |
create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=1024; |
| 1169 |
show create table t1; |
| 1170 |
alter table t1 key_block_size=2048; |
| 1171 |
show create table t1; |
| 1172 |
alter table t1 add c int, add key (c); |
| 1173 |
show create table t1; |
| 1174 |
alter table t1 key_block_size=4096; |
| 1175 |
show create table t1; |
| 1176 |
alter table t1 key_block_size=0; |
| 1177 |
show create table t1; |
| 1178 |
alter table t1 add d int, add key (d); |
| 1179 |
show create table t1; |
| 1180 |
drop table t1; |
| 1181 |
|
| 1182 |
create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=8192; |
| 1183 |
show create table t1; |
| 1184 |
drop table t1; |
| 1185 |
|
| 1186 |
create table t1 (a int not null, b varchar(4096), key (a) key_block_size=1024, key(b)) key_block_size=8192; |
| 1187 |
show create table t1; |
| 1188 |
drop table t1; |
| 1189 |
|
| 1190 |
create table t1 (a int not null, b int, key (a) key_block_size=1024, key(b) key_block_size=8192) key_block_size=16384; |
| 1191 |
show create table t1; |
| 1192 |
drop table t1; |
| 1193 |
|
| 1194 |
# Test limits and errors of key_block_size |
| 1195 |
|
| 1196 |
create table t1 (a int not null, key `a` (a) key_block_size=512); |
| 1197 |
show create table t1; |
| 1198 |
drop table t1; |
| 1199 |
|
| 1200 |
create table t1 (a varchar(4096), key `a` (a) key_block_size=1000000000000000000); |
| 1201 |
show create table t1; |
| 1202 |
drop table t1; |
| 1203 |
|
| 1204 |
create table t1 (a int not null, key `a` (a) key_block_size=1025); |
| 1205 |
show create table t1; |
| 1206 |
drop table t1; |
| 1207 |
|
| 1208 |
--error ER_PARSE_ERROR |
| 1209 |
create table t1 (a int not null, key key_block_size=1024 (a)); |
| 1210 |
--error ER_PARSE_ERROR |
| 1211 |
create table t1 (a int not null, key `a` key_block_size=1024 (a)); |
| 1212 |
|
| 1213 |
# |
| 1214 |
# Bug#22119 - Changing MI_KEY_BLOCK_LENGTH makes a wrong myisamchk |
| 1215 |
# |
| 1216 |
CREATE TABLE t1 ( |
| 1217 |
c1 INT, |
| 1218 |
c2 VARCHAR(300), |
| 1219 |
KEY (c1) KEY_BLOCK_SIZE 1024, |
| 1220 |
KEY (c2) KEY_BLOCK_SIZE 8192 |
| 1221 |
); |
| 1222 |
INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))), |
| 1223 |
(11, REPEAT('b', CEIL(RAND() * 300))), |
| 1224 |
(12, REPEAT('c', CEIL(RAND() * 300))), |
| 1225 |
(13, REPEAT('d', CEIL(RAND() * 300))), |
| 1226 |
(14, REPEAT('e', CEIL(RAND() * 300))), |
| 1227 |
(15, REPEAT('f', CEIL(RAND() * 300))), |
| 1228 |
(16, REPEAT('g', CEIL(RAND() * 300))), |
| 1229 |
(17, REPEAT('h', CEIL(RAND() * 300))), |
| 1230 |
(18, REPEAT('i', CEIL(RAND() * 300))), |
| 1231 |
(19, REPEAT('j', CEIL(RAND() * 300))), |
| 1232 |
(20, REPEAT('k', CEIL(RAND() * 300))), |
| 1233 |
(21, REPEAT('l', CEIL(RAND() * 300))), |
| 1234 |
(22, REPEAT('m', CEIL(RAND() * 300))), |
| 1235 |
(23, REPEAT('n', CEIL(RAND() * 300))), |
| 1236 |
(24, REPEAT('o', CEIL(RAND() * 300))), |
| 1237 |
(25, REPEAT('p', CEIL(RAND() * 300))), |
| 1238 |
(26, REPEAT('q', CEIL(RAND() * 300))), |
| 1239 |
(27, REPEAT('r', CEIL(RAND() * 300))), |
| 1240 |
(28, REPEAT('s', CEIL(RAND() * 300))), |
| 1241 |
(29, REPEAT('t', CEIL(RAND() * 300))), |
| 1242 |
(30, REPEAT('u', CEIL(RAND() * 300))), |
| 1243 |
(31, REPEAT('v', CEIL(RAND() * 300))), |
| 1244 |
(32, REPEAT('w', CEIL(RAND() * 300))), |
| 1245 |
(33, REPEAT('x', CEIL(RAND() * 300))), |
| 1246 |
(34, REPEAT('y', CEIL(RAND() * 300))), |
| 1247 |
(35, REPEAT('z', CEIL(RAND() * 300))); |
| 1248 |
INSERT INTO t1 SELECT * FROM t1; |
| 1249 |
INSERT INTO t1 SELECT * FROM t1; |
| 1250 |
CHECK TABLE t1; |
| 1251 |
REPAIR TABLE t1; |
| 1252 |
DELETE FROM t1 WHERE c1 >= 10; |
| 1253 |
CHECK TABLE t1; |
| 1254 |
DROP TABLE t1; |
| 1255 |
|
| 1256 |
# |
| 1257 |
# Bug#33222 - myisam-table drops rows when column is added |
| 1258 |
# and a char-field > 128 exists |
| 1259 |
# |
| 1260 |
# Test #1 - CHECK TABLE sees wrong record, REPAR TABLE deletes it. |
| 1261 |
# Using a CHAR column that can have > 127 characters. |
| 1262 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1263 |
CREATE TABLE t1 ( |
| 1264 |
c1 CHAR(130), |
| 1265 |
c2 VARCHAR(1) |
| 1266 |
) ENGINE=aria; |
| 1267 |
INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); |
| 1268 |
SELECT COUNT(*) FROM t1; |
| 1269 |
CHECK TABLE t1; |
| 1270 |
REPAIR TABLE t1; |
| 1271 |
SELECT COUNT(*) FROM t1; |
| 1272 |
CHECK TABLE t1; |
| 1273 |
DROP TABLE t1; |
| 1274 |
# |
| 1275 |
# Test #2 - same as test #1, but using EXTENDED. |
| 1276 |
# Using a CHAR column that can have > 127 characters. |
| 1277 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1278 |
CREATE TABLE t1 ( |
| 1279 |
c1 CHAR(130), |
| 1280 |
c2 VARCHAR(1) |
| 1281 |
) ENGINE=aria; |
| 1282 |
INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); |
| 1283 |
SELECT COUNT(*) FROM t1; |
| 1284 |
CHECK TABLE t1 EXTENDED; |
| 1285 |
REPAIR TABLE t1 EXTENDED; |
| 1286 |
SELECT COUNT(*) FROM t1; |
| 1287 |
CHECK TABLE t1 EXTENDED; |
| 1288 |
DROP TABLE t1; |
| 1289 |
# |
| 1290 |
# Test #3 - same as test #1, but using OPTIMIZE TABLE. |
| 1291 |
# Using a CHAR column that can have > 127 characters. |
| 1292 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1293 |
CREATE TABLE t1 ( |
| 1294 |
c1 CHAR(130), |
| 1295 |
c2 VARCHAR(1) |
| 1296 |
) ENGINE=aria; |
| 1297 |
INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); |
| 1298 |
# Insert more rows and delete one in the middle to force optimize. |
| 1299 |
INSERT INTO t1 VALUES('b', 'b'); |
| 1300 |
INSERT INTO t1 VALUES('c', 'b'); |
| 1301 |
DELETE FROM t1 WHERE c1='b'; |
| 1302 |
SELECT COUNT(*) FROM t1; |
| 1303 |
OPTIMIZE TABLE t1; |
| 1304 |
SELECT COUNT(*) FROM t1; |
| 1305 |
DROP TABLE t1; |
| 1306 |
# |
| 1307 |
# Test #4 - ALTER TABLE deletes rows. |
| 1308 |
# Using a CHAR column that can have > 127 characters. |
| 1309 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1310 |
# Using an index which can be disabled during bulk insert. |
| 1311 |
CREATE TABLE t1 ( |
| 1312 |
c1 CHAR(130), |
| 1313 |
c2 VARCHAR(1), |
| 1314 |
KEY (c1) |
| 1315 |
) ENGINE=aria; |
| 1316 |
# |
| 1317 |
# Insert 100 rows. This turns bulk insert on during the copy phase of |
| 1318 |
# ALTER TABLE. Bulk insert disables keys before the insert and re-enables |
| 1319 |
# them by repair after the insert. |
| 1320 |
--disable_query_log |
| 1321 |
let $count= 100; |
| 1322 |
--echo # Insert $count rows. Query log disabled. |
| 1323 |
while ($count) |
| 1324 |
{ |
| 1325 |
INSERT INTO t1 VALUES ('a', 'b'); |
| 1326 |
dec $count; |
| 1327 |
} |
| 1328 |
--enable_query_log |
| 1329 |
# |
| 1330 |
# Change most of the rows into long character values with > 127 characters. |
| 1331 |
UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; |
| 1332 |
SELECT COUNT(*) FROM t1; |
| 1333 |
ALTER TABLE t1 ENGINE=aria; |
| 1334 |
# |
| 1335 |
# With bug present, this shows that all long rows are gone. |
| 1336 |
SELECT COUNT(*) FROM t1; |
| 1337 |
CHECK TABLE t1; |
| 1338 |
CHECK TABLE t1 EXTENDED; |
| 1339 |
DROP TABLE t1; |
| 1340 |
# |
| 1341 |
# Test #5 - same as test #1 but UTF-8. |
| 1342 |
# Using a CHAR column that can have > 127 characters. |
| 1343 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1344 |
CREATE TABLE t1 ( |
| 1345 |
c1 CHAR(50), |
| 1346 |
c2 VARCHAR(1) |
| 1347 |
) ENGINE=aria DEFAULT CHARSET UTF8; |
| 1348 |
# Using Tamil Letter A, Unicode U+0B85 |
| 1349 |
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); |
| 1350 |
SELECT COUNT(*) FROM t1; |
| 1351 |
CHECK TABLE t1; |
| 1352 |
REPAIR TABLE t1; |
| 1353 |
SELECT COUNT(*) FROM t1; |
| 1354 |
CHECK TABLE t1; |
| 1355 |
DROP TABLE t1; |
| 1356 |
# |
| 1357 |
# Test #6 - same as test #2, but UTF-8. |
| 1358 |
# Using a CHAR column that can have > 127 characters. |
| 1359 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1360 |
CREATE TABLE t1 ( |
| 1361 |
c1 CHAR(50), |
| 1362 |
c2 VARCHAR(1) |
| 1363 |
) ENGINE=aria DEFAULT CHARSET UTF8; |
| 1364 |
# Using Tamil Letter A, Unicode U+0B85 |
| 1365 |
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); |
| 1366 |
SELECT COUNT(*) FROM t1; |
| 1367 |
CHECK TABLE t1 EXTENDED; |
| 1368 |
REPAIR TABLE t1 EXTENDED; |
| 1369 |
SELECT COUNT(*) FROM t1; |
| 1370 |
CHECK TABLE t1 EXTENDED; |
| 1371 |
DROP TABLE t1; |
| 1372 |
# |
| 1373 |
# Test #7 - same as test #3, but UTF-8. |
| 1374 |
# Using a CHAR column that can have > 127 characters. |
| 1375 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1376 |
CREATE TABLE t1 ( |
| 1377 |
c1 CHAR(50), |
| 1378 |
c2 VARCHAR(1) |
| 1379 |
) ENGINE=aria DEFAULT CHARSET UTF8; |
| 1380 |
# Using Tamil Letter A, Unicode U+0B85 |
| 1381 |
INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); |
| 1382 |
# Insert more rows and delete one in the middle to force optimize. |
| 1383 |
INSERT INTO t1 VALUES('b', 'b'); |
| 1384 |
INSERT INTO t1 VALUES('c', 'b'); |
| 1385 |
DELETE FROM t1 WHERE c1='b'; |
| 1386 |
SELECT COUNT(*) FROM t1; |
| 1387 |
OPTIMIZE TABLE t1; |
| 1388 |
SELECT COUNT(*) FROM t1; |
| 1389 |
DROP TABLE t1; |
| 1390 |
# |
| 1391 |
# Test #8 - same as test #4, but UTF-8. |
| 1392 |
# Using a CHAR column that can have > 42 UTF-8 characters. |
| 1393 |
# Using a VARCHAR to create a table with dynamic row format. |
| 1394 |
# Using an index which can be disabled during bulk insert. |
| 1395 |
CREATE TABLE t1 ( |
| 1396 |
c1 CHAR(50), |
| 1397 |
c2 VARCHAR(1), |
| 1398 |
KEY (c1) |
| 1399 |
) ENGINE=aria DEFAULT CHARSET UTF8; |
| 1400 |
# |
| 1401 |
# Insert 100 rows. This turns bulk insert on during the copy phase of |
| 1402 |
# ALTER TABLE. Bulk insert disables keys before the insert and re-enables |
| 1403 |
# them by repair after the insert. |
| 1404 |
--disable_query_log |
| 1405 |
let $count= 100; |
| 1406 |
--echo # Insert $count rows. Query log disabled. |
| 1407 |
while ($count) |
| 1408 |
{ |
| 1409 |
INSERT INTO t1 VALUES ('a', 'b'); |
| 1410 |
dec $count; |
| 1411 |
} |
| 1412 |
--enable_query_log |
| 1413 |
# |
| 1414 |
# Change most of the rows into long character values with > 42 characters. |
| 1415 |
# Using Tamil Letter A, Unicode U+0B85 |
| 1416 |
UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; |
| 1417 |
SELECT COUNT(*) FROM t1; |
| 1418 |
ALTER TABLE t1 ENGINE=aria; |
| 1419 |
# |
| 1420 |
# With bug present, this shows that all long rows are gone. |
| 1421 |
SELECT COUNT(*) FROM t1; |
| 1422 |
CHECK TABLE t1; |
| 1423 |
CHECK TABLE t1 EXTENDED; |
| 1424 |
DROP TABLE t1; |
| 1425 |
|
| 1426 |
# |
| 1427 |
# Bug#29182 - MyISAMCHK reports wrong character set |
| 1428 |
# |
| 1429 |
CREATE TABLE t1 ( |
| 1430 |
c1 VARCHAR(10) NOT NULL, |
| 1431 |
c2 CHAR(10) DEFAULT NULL, |
| 1432 |
c3 VARCHAR(10) NOT NULL, |
| 1433 |
KEY (c1), |
| 1434 |
KEY (c2) |
| 1435 |
) ENGINE=aria DEFAULT CHARSET=utf8 PACK_KEYS=0; |
| 1436 |
let $MYSQLD_DATADIR= `select @@datadir`; |
| 1437 |
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR |
| 1438 |
--exec $MARIA_CHK -d --ignore-control-file $MYSQLD_DATADIR/test/t1 |
| 1439 |
DROP TABLE t1; |
| 1440 |
|
| 1441 |
# Test warnings with transactional=1 with MyISAM |
| 1442 |
# |
| 1443 |
create table t1 (n int not null, c char(1)) transactional=1; |
| 1444 |
show create table t1; |
| 1445 |
drop table t1; |
| 1446 |
|
| 1447 |
# |
| 1448 |
# Test of BUG#35570 CHECKSUM TABLE unreliable if LINESTRING field |
| 1449 |
# (same content / differen checksum) |
| 1450 |
# |
| 1451 |
|
| 1452 |
CREATE TABLE t1 (line LINESTRING NOT NULL) engine=aria; |
| 1453 |
INSERT INTO t1 VALUES (GeomFromText("LINESTRING(0 0)")); |
| 1454 |
checksum table t1; |
| 1455 |
CREATE TABLE t2 (line LINESTRING NOT NULL) engine=aria; |
| 1456 |
INSERT INTO t2 VALUES (GeomFromText("LINESTRING(0 0)")); |
| 1457 |
checksum table t2; |
| 1458 |
CREATE TABLE t3 select * from t1; |
| 1459 |
checksum table t3; |
| 1460 |
drop table t1,t2,t3; |
| 1461 |
--echo End of 5.1 tests |
| 1462 |
|
| 1463 |
# |
| 1464 |
# from bug37276_reduced_corruption.sql |
| 1465 |
# |
| 1466 |
create table t2(a varchar(255),key(a))engine=aria row_format=dynamic transactional=0; |
| 1467 |
insert into t2 values (repeat('o',124)), (repeat('h',226)), (repeat('i',236)), |
| 1468 |
(repeat('l',234)), (repeat('b',13)), (repeat('g',236)), (repeat('y',205)), |
| 1469 |
(repeat('c',99)), (repeat('g',145)), (repeat('o',131)), (repeat('e',63)), |
| 1470 |
(repeat('q',155)), (repeat('k',87)), (repeat('i',54)), (repeat('p',84)), |
| 1471 |
(repeat('m',119)), (repeat('c',2)), (repeat('a',174)), (repeat('g',160)), |
| 1472 |
(repeat('t',147)), (repeat('n',107)); |
| 1473 |
insert into t2 values ('nupdjlafwfvuuvruxkyjxpmupihzgspkaybijztkeukgzzkrxmd'); |
| 1474 |
insert into t2 values (repeat('g',40)), (repeat('i',173)), (repeat('q',126)), |
| 1475 |
(repeat('i',217)), (repeat('f',161)), (repeat('i',28)), (repeat('a',35)), |
| 1476 |
(repeat('y',27)), (repeat('o',100)), (repeat('o',175)), (repeat('f',69)), |
| 1477 |
(repeat('k',156)), (repeat('n',220)), (repeat('q',247)), (repeat('y',180)), |
| 1478 |
(repeat('v',209)), (repeat('m',169)), (repeat('y',170)), (repeat('r',151)), |
| 1479 |
(repeat('d',38)), (repeat('g',64)), (repeat('k',77)), (repeat('l',150)), |
| 1480 |
(repeat('s',150)), (repeat('u',127)), (repeat('l',15)), (repeat('m',33)), |
| 1481 |
(repeat('r',177)), (repeat('v',197)), (repeat('k',62)), (repeat('h',219)), |
| 1482 |
(repeat('u',161)), (repeat('y',118)), (repeat('i',184)), (repeat('z',202)), |
| 1483 |
(repeat('j',113)), (repeat('q',95)), (repeat('q',164)), (repeat('e',54)), |
| 1484 |
(repeat('e',60)), (repeat('l',203)), (repeat('g',77)), (repeat('y',44)), |
| 1485 |
(repeat('j',196)), (repeat('t',45)), (repeat('l',190)), (repeat('l',89)), |
| 1486 |
(repeat('q',45)), (repeat('e',191)), (repeat('t',38)), (repeat('f',148)), |
| 1487 |
(repeat('c',25)), (repeat('v',97)), (repeat('i',83)), (repeat('s',166)), |
| 1488 |
(repeat('d',96)), (repeat('v',82)), (repeat('n',127)), (repeat('i',201)), |
| 1489 |
(repeat('x',184)), (repeat('d',76)), (repeat('u',17)), (repeat('a',178)); |
| 1490 |
insert into t2 values ('hwvfiavnmufgbulapzrolonwxufheqymvjncnczlzcjokzqlsvmomcjzgzwzquyxpunxdmotdczocwliaprpubwaeccsulvittgizcutxxb'); |
| 1491 |
insert into t2 values (repeat('x',28)), (repeat('p',21)), (repeat('k',241)), |
| 1492 |
(repeat('i',243)), (repeat('b',172)), (repeat('z',94)), (repeat('i',218)), |
| 1493 |
(repeat('a',177)), (repeat('g',251)), (repeat('q',161)), (repeat('x',231)), |
| 1494 |
(repeat('p',51)), (repeat('f',141)), (repeat('m',28)), (repeat('r',77)), |
| 1495 |
(repeat('h',56)), (repeat('k',23)), (repeat('f',198)), (repeat('o',243)), |
| 1496 |
(repeat('d',160)), (repeat('h',82)); |
| 1497 |
|
| 1498 |
check table t2 extended; |
| 1499 |
drop table t2; |
| 1500 |
|
| 1501 |
CREATE TABLE t1 ( |
| 1502 |
col0 float DEFAULT NULL, |
| 1503 |
col1 date DEFAULT NULL, |
| 1504 |
col2 double DEFAULT NULL, |
| 1505 |
col3 decimal(10,0) DEFAULT NULL, |
| 1506 |
col4 char(218) DEFAULT NULL, |
| 1507 |
col5 year(4) DEFAULT NULL, |
| 1508 |
col6 datetime DEFAULT NULL, |
| 1509 |
col7 varchar(39) DEFAULT NULL, |
| 1510 |
col8 double DEFAULT NULL, |
| 1511 |
col9 decimal(10,0) DEFAULT NULL, |
| 1512 |
col10 enum('test1','test2','test3') DEFAULT NULL, |
| 1513 |
col11 year(4) DEFAULT NULL, |
| 1514 |
col12 tinytext, |
| 1515 |
col13 tinyblob, |
| 1516 |
col14 date DEFAULT NULL, |
| 1517 |
col15 smallint(6) DEFAULT NULL, |
| 1518 |
col16 varchar(81) DEFAULT NULL, |
| 1519 |
col17 tinytext, |
| 1520 |
col18 blob, |
| 1521 |
col19 double DEFAULT NULL, |
| 1522 |
col20 double DEFAULT NULL, |
| 1523 |
col21 varchar(216) DEFAULT NULL, |
| 1524 |
col22 enum('test1','test2','test3') DEFAULT NULL, |
| 1525 |
col23 decimal(10,0) DEFAULT NULL, |
| 1526 |
col24 text, |
| 1527 |
col25 varchar(118) DEFAULT NULL, |
| 1528 |
col26 tinytext, |
| 1529 |
col27 tinyblob, |
| 1530 |
col28 double DEFAULT NULL, |
| 1531 |
col29 tinyint(4) DEFAULT NULL, |
| 1532 |
col30 longtext, |
| 1533 |
col31 tinyint(1) DEFAULT NULL, |
| 1534 |
col32 char(212) DEFAULT NULL, |
| 1535 |
col33 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 1536 |
col34 year(4) DEFAULT NULL, |
| 1537 |
col35 tinyint(1) DEFAULT NULL, |
| 1538 |
col36 enum('test1','test2','test3') DEFAULT NULL, |
| 1539 |
col37 decimal(10,0) DEFAULT NULL, |
| 1540 |
col38 tinyint(4) DEFAULT NULL, |
| 1541 |
col39 double DEFAULT NULL, |
| 1542 |
col40 decimal(10,0) DEFAULT NULL, |
| 1543 |
col41 enum('test1','test2','test3') DEFAULT NULL, |
| 1544 |
col42 longblob, |
| 1545 |
col43 text, |
| 1546 |
col44 blob, |
| 1547 |
col45 year(4) DEFAULT NULL, |
| 1548 |
col46 longtext, |
| 1549 |
col47 int(11) DEFAULT NULL, |
| 1550 |
col48 set('test1','test2','test3') DEFAULT NULL, |
| 1551 |
col49 bigint(20) DEFAULT NULL, |
| 1552 |
col50 date DEFAULT NULL, |
| 1553 |
col51 tinyblob, |
| 1554 |
col52 float DEFAULT NULL, |
| 1555 |
col53 year(4) DEFAULT NULL, |
| 1556 |
col54 decimal(10,0) DEFAULT NULL, |
| 1557 |
col55 tinyblob, |
| 1558 |
col56 float DEFAULT NULL, |
| 1559 |
col57 bigint(20) DEFAULT NULL, |
| 1560 |
col58 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 1561 |
col59 enum('test1','test2','test3') DEFAULT NULL, |
| 1562 |
col60 bigint(20) DEFAULT NULL, |
| 1563 |
col61 year(4) DEFAULT NULL, |
| 1564 |
col62 year(4) DEFAULT NULL, |
| 1565 |
col63 double DEFAULT NULL, |
| 1566 |
col64 tinytext, |
| 1567 |
col65 tinyint(4) DEFAULT NULL, |
| 1568 |
col66 longtext, |
| 1569 |
col67 time DEFAULT NULL, |
| 1570 |
col68 bigint(20) DEFAULT NULL, |
| 1571 |
col69 char(142) DEFAULT NULL, |
| 1572 |
col70 longtext, |
| 1573 |
col71 time DEFAULT NULL, |
| 1574 |
col72 year(4) DEFAULT NULL, |
| 1575 |
col73 longblob, |
| 1576 |
col74 enum('test1','test2','test3') DEFAULT NULL, |
| 1577 |
col75 decimal(10,0) DEFAULT NULL, |
| 1578 |
col76 smallint(6) DEFAULT NULL, |
| 1579 |
col77 tinytext, |
| 1580 |
col78 date DEFAULT NULL, |
| 1581 |
col79 double DEFAULT NULL, |
| 1582 |
col80 tinyint(4) DEFAULT NULL, |
| 1583 |
col81 float DEFAULT NULL, |
| 1584 |
col82 bigint(20) DEFAULT NULL, |
| 1585 |
col83 double DEFAULT NULL, |
| 1586 |
col84 varchar(124) DEFAULT NULL, |
| 1587 |
col85 double DEFAULT NULL, |
| 1588 |
col86 tinyblob, |
| 1589 |
col87 tinyblob, |
| 1590 |
col88 double DEFAULT NULL, |
| 1591 |
col89 date DEFAULT NULL, |
| 1592 |
col90 decimal(10,0) DEFAULT NULL, |
| 1593 |
col91 set('test1','test2','test3') DEFAULT NULL, |
| 1594 |
col92 blob, |
| 1595 |
col93 char(174) DEFAULT NULL, |
| 1596 |
col94 double DEFAULT NULL, |
| 1597 |
col95 tinytext, |
| 1598 |
col96 decimal(10,0) DEFAULT NULL, |
| 1599 |
col97 year(4) DEFAULT NULL, |
| 1600 |
col98 tinyblob, |
| 1601 |
col99 datetime DEFAULT NULL, |
| 1602 |
col100 longblob, |
| 1603 |
col101 date DEFAULT NULL, |
| 1604 |
col102 float DEFAULT NULL, |
| 1605 |
col103 float DEFAULT NULL, |
| 1606 |
col104 int(11) DEFAULT NULL, |
| 1607 |
col105 longblob, |
| 1608 |
col106 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', |
| 1609 |
col107 float DEFAULT NULL, |
| 1610 |
col108 text, |
| 1611 |
col109 float DEFAULT NULL, |
| 1612 |
col110 decimal(10,0) DEFAULT NULL, |
| 1613 |
col111 double DEFAULT NULL, |
| 1614 |
col112 double DEFAULT NULL, |
| 1615 |
col113 blob, |
| 1616 |
col114 varchar(152) DEFAULT NULL, |
| 1617 |
col115 bigint(20) DEFAULT NULL, |
| 1618 |
col116 decimal(10,0) DEFAULT NULL, |
| 1619 |
col117 mediumint(9) DEFAULT NULL, |
| 1620 |
col118 tinytext, |
| 1621 |
col119 tinyblob, |
| 1622 |
col120 int(11) DEFAULT NULL, |
| 1623 |
col121 bigint(20) DEFAULT NULL, |
| 1624 |
col122 double DEFAULT NULL, |
| 1625 |
col123 date DEFAULT NULL, |
| 1626 |
col124 longtext, |
| 1627 |
col125 longtext, |
| 1628 |
col126 double DEFAULT NULL, |
| 1629 |
col127 varchar(84) DEFAULT NULL, |
| 1630 |
col128 text, |
| 1631 |
col129 double DEFAULT NULL, |
| 1632 |
col130 enum('test1','test2','test3') DEFAULT NULL, |
| 1633 |
col131 time DEFAULT NULL, |
| 1634 |
col132 year(4) DEFAULT NULL, |
| 1635 |
col133 blob, |
| 1636 |
col134 tinytext, |
| 1637 |
col135 double DEFAULT NULL, |
| 1638 |
col136 tinytext, |
| 1639 |
col137 bigint(20) DEFAULT NULL, |
| 1640 |
col138 datetime DEFAULT NULL, |
| 1641 |
col139 double DEFAULT NULL, |
| 1642 |
col140 decimal(10,0) DEFAULT NULL, |
| 1643 |
col141 longtext, |
| 1644 |
col142 tinyint(1) DEFAULT NULL, |
| 1645 |
col143 time DEFAULT NULL, |
| 1646 |
col144 time DEFAULT NULL, |
| 1647 |
col145 float DEFAULT NULL, |
| 1648 |
col146 longblob, |
| 1649 |
col147 float DEFAULT NULL, |
| 1650 |
col148 text, |
| 1651 |
col149 mediumint(9) DEFAULT NULL, |
| 1652 |
col150 tinyblob, |
| 1653 |
col151 tinyblob, |
| 1654 |
col152 tinytext, |
| 1655 |
col153 tinyblob, |
| 1656 |
col154 tinyblob, |
| 1657 |
col155 tinytext, |
| 1658 |
col156 tinyint(1) DEFAULT NULL, |
| 1659 |
col157 tinytext, |
| 1660 |
col158 time DEFAULT NULL, |
| 1661 |
col159 date DEFAULT NULL, |
| 1662 |
col160 longtext, |
| 1663 |
col161 enum('test1','test2','test3') DEFAULT NULL, |
| 1664 |
col162 text, |
| 1665 |
col163 decimal(10,0) DEFAULT NULL, |
| 1666 |
col164 time DEFAULT NULL, |
| 1667 |
col165 longblob, |
| 1668 |
col166 tinyint(4) DEFAULT NULL, |
| 1669 |
col167 bigint(20) DEFAULT NULL, |
| 1670 |
col168 decimal(10,0) DEFAULT NULL, |
| 1671 |
col169 smallint(6) DEFAULT NULL, |
| 1672 |
col170 tinytext, |
| 1673 |
col171 tinyint(4) DEFAULT NULL, |
| 1674 |
col172 tinyint(1) DEFAULT NULL, |
| 1675 |
col173 tinytext, |
| 1676 |
col174 decimal(10,0) DEFAULT NULL, |
| 1677 |
col175 double DEFAULT NULL |
| 1678 |
) engine=aria; |
| 1679 |
|
| 1680 |
insert ignore into t1 set |
| 1681 |
col10=abs(28449) % 2, |
| 1682 |
col11='1973', |
| 1683 |
col12=if(abs(-30039)%100<20,null,'forgery\'s'), |
| 1684 |
col13=if(abs(24672)%100<20,null,'adductor\'s'), |
| 1685 |
col16=if(abs(26872)%100<20,null,'0xf810e016ee0b78e1ce8b1c6cf8d2e82bf8507453768a3908dc20cecfc9a0ac0ac00079d0645a4c'), |
| 1686 |
col18=if(abs(-15854)%100<20,null,'unattractiveness'), |
| 1687 |
col19=if(abs(4287439673.9896235000)%100<20,null,25288), |
| 1688 |
col20=if(abs(4290800136.7527390000)%100<20,null,8887), |
| 1689 |
col21=if(abs(-26086)%100<20,null,'0x2334181c6068aab18b348ecc1e2600b81e1c5f821eee3e204824'), |
| 1690 |
col22=abs(-21921) % 2, |
| 1691 |
col23=if(abs(1503277.6900540178)%100<20,null,-23298), |
| 1692 |
col25=if(abs(29674)%100<20,null,'0xc8d094f888ee20c83baef8d9380a168d40f4906e742a4dc0daeacb809e64095c71d510c7c0f83a6a0a04b8d6a0d9bea2dc3d4bd44d9c5002e440707c40ead8b3eb20a100a8524b1616a338a440ea02a25a08041810a08cac087cd47b4a79f08730946c5800600ae45e1c08f637'), |
| 1693 |
col26=if(abs(28642)%100<20,null,'insubstantiality\'s'), |
| 1694 |
col27=if(abs(-3188)%100<20,null,'exine\'s'), |
| 1695 |
col29=if(abs(-47)%100<20,null,-24131), |
| 1696 |
col32=if(abs(-3658)%100<20,null,'shortener\'s'), |
| 1697 |
col34='1917', |
| 1698 |
col36=abs(27782) % 2, |
| 1699 |
col37=if(abs(4864972.0244758446)%100<20,null,32302), |
| 1700 |
col38=if(abs(97)%100<20,null,-14079), |
| 1701 |
col39=if(abs(3362872.0521256141)%100<20,null,27191), |
| 1702 |
col40=if(abs(3348292.2110660113)%100<20,null,-1163), |
| 1703 |
col41=abs(-18533) % 2, |
| 1704 |
col42=if(abs(2094)%100<20,null,'Montparnasse'), |
| 1705 |
col43=if(abs(-15983)%100<20,null,'Massasoit\'s'), |
| 1706 |
col44=if(abs(2497)%100<20,null,'lags'), |
| 1707 |
col45='2057', |
| 1708 |
col46=if(abs(-31691)%100<20,null,'miscegenation\'s'), |
| 1709 |
col47=if(abs(-1269564297)%100<20,null,1089), |
| 1710 |
col49=if(abs(-1815717335)%100<20,null,-17504), |
| 1711 |
col51=if(abs(-15263)%100<20,null,'virelay'), |
| 1712 |
col52=if(abs(2227333.3279519030)%100<20,null,-5210), |
| 1713 |
col53='2032', |
| 1714 |
col54=if(abs(791647.5947447127)%100<20,null,32576), |
| 1715 |
col55=if(abs(20293)%100<20,null,'tumblebug'), |
| 1716 |
col56=if(abs(4288698564.2967925000)%100<20,null,17141), |
| 1717 |
col57=if(abs(-2138460927)%100<20,null,14495), |
| 1718 |
col59=abs(7624) % 2, |
| 1719 |
col60=if(abs(-1500892492)%100<20,null,-68), |
| 1720 |
col63=if(abs(4290890487.3789482000)%100<20,null,-32129), |
| 1721 |
col65=if(abs(22)%100<20,null,15722), |
| 1722 |
col79=if(abs(4292420489.2606282000)%100<20,null,-23891), |
| 1723 |
col84=if(abs(-21248)%100<20,null,'0x4f9888d044435050eab83cb3dcad88b01886e434e216'), |
| 1724 |
col85=if(abs(4294260188.6230965000)%100<20,null,16867), |
| 1725 |
col86=if(abs(-11659)%100<20,null,'prewar'), |
| 1726 |
col87=if(abs(-2253)%100<20,null,'Radnorshire'), |
| 1727 |
col90=if(abs(4287254529.4026613000)%100<20,null,23506), |
| 1728 |
col92=if(abs(6472)%100<20,null,'electroplated'), |
| 1729 |
col93=if(abs(-13523)%100<20,null,'sparkiest'), |
| 1730 |
col95=if(abs(23998)%100<20,null,'Crimea'), |
| 1731 |
col96=if(abs(4287719060.2789087000)%100<20,null,20527), |
| 1732 |
col98=if(abs(-14090)%100<20,null,'firebrat'), |
| 1733 |
col99='19161023095430', |
| 1734 |
col100=if(abs(-31178)%100<20,null,'clinical'), |
| 1735 |
col102=if(abs(4407547.5205542166)%100<20,null,18226), |
| 1736 |
col103=if(abs(4286834687.5994444000)%100<20,null,27520), |
| 1737 |
col104=if(abs(-2105663477)%100<20,null,28591), |
| 1738 |
col105=if(abs(1929)%100<20,null,'renascent'), |
| 1739 |
col107=if(abs(5972348.8099917602)%100<20,null,-11408), |
| 1740 |
col108=if(abs(-11262)%100<20,null,'aircraftmen'), |
| 1741 |
col110=if(abs(6530491.4546037167)%100<20,null,-17672), |
| 1742 |
col111=if(abs(4289897795.5000763000)%100<20,null,3742), |
| 1743 |
col112=if(abs(1680557.8560441907)%100<20,null,13944), |
| 1744 |
col113=if(abs(-27195)%100<20,null,'dekameter'), |
| 1745 |
col115=if(abs(-2083419827)%100<20,null,-17272), |
| 1746 |
col117=if(abs(1704826)%100<20,null,17880), |
| 1747 |
col118=if(abs(-2848)%100<20,null,'judicatory'), |
| 1748 |
col119=if(abs(-28087)%100<20,null,'mistitles'), |
| 1749 |
col120=if(abs(-2100119097)%100<20,null,22465), |
| 1750 |
col121=if(abs(-1868777891)%100<20,null,15172), |
| 1751 |
col122=if(abs(7039857.3608508557)%100<20,null,-22154), |
| 1752 |
col125=if(abs(70)%100<20,null,'Hong\'s'), |
| 1753 |
col126=if(abs(3820673.5968199712)%100<20,null,-24185), |
| 1754 |
col127=if(abs(12331)%100<20,null,'0x674e14584e88fca3fed0a0b1488a440008228aa01454a65cf09e3f0fa0511c3ce2f8688450'), |
| 1755 |
col128=if(abs(20335)%100<20,null,'Zoroaster\'s'), |
| 1756 |
col129=if(abs(3916577.6225165562)%100<20,null,-4088), |
| 1757 |
col130=abs(-15003) % 2, |
| 1758 |
col132='2016', |
| 1759 |
col134=if(abs(-26555)%100<20,null,'Caesarea'), |
| 1760 |
col135=if(abs(4288484655.2416148000)%100<20,null,-30073), |
| 1761 |
col136=if(abs(-17577)%100<20,null,'upbraid'), |
| 1762 |
col137=if(abs(-1742797945)%100<20,null,-21651), |
| 1763 |
col138='20751113181230', |
| 1764 |
col139=if(abs(4288997063.9889216000)%100<20,null,1816), |
| 1765 |
col141=if(abs(-31448)%100<20,null,'threnodist'), |
| 1766 |
col142=if(abs(88)%100<20,null,-19748), |
| 1767 |
col143='6930607', |
| 1768 |
col144='5760250', |
| 1769 |
col145=if(abs(3591496.9625843074)%100<20,null,76), |
| 1770 |
col146=if(abs(20875)%100<20,null,'rename'), |
| 1771 |
col147=if(abs(4294789439.6773582000)%100<20,null,32314), |
| 1772 |
col148=if(abs(7072)%100<20,null,'recesses'), |
| 1773 |
col150=if(abs(-26540)%100<20,null,'cuckoo'), |
| 1774 |
col152=if(abs(23553)%100<20,null,'shortened'), |
| 1775 |
col153=if(abs(-30422)%100<20,null,'inhabitant'), |
| 1776 |
col154=if(abs(30457)%100<20,null,'Orwellian'), |
| 1777 |
col155=if(abs(-30263)%100<20,null,'Ptolemies'), |
| 1778 |
col156=if(abs(-41)%100<20,null,6382), |
| 1779 |
col157=if(abs(2557)%100<20,null,'horsewhips'), |
| 1780 |
col158='2764427', |
| 1781 |
col160=if(abs(-15872)%100<20,null,'girlhood'), |
| 1782 |
col161=abs(15378) % 2, |
| 1783 |
col163=if(abs(4286662730.9309368000)%100<20,null,-19516), |
| 1784 |
col167=if(abs(-1881918655)%100<20,null,6927), |
| 1785 |
col169=if(abs(-14442)%100<20,null,-6392), |
| 1786 |
col170=if(abs(29965)%100<20,null,'resynthesis'), |
| 1787 |
col173=if(abs(-451)%100<20,null,'Clute'), |
| 1788 |
col174=if(abs(3262594.6284981840)%100<20,null,17846); |
| 1789 |
|
| 1790 |
update ignore t1 set col165=repeat('a',7000); |
| 1791 |
check table t1; |
| 1792 |
drop table t1; |
| 1793 |
|
| 1794 |
# |
| 1795 |
# Bug#38466 aria: range query returns no data |
| 1796 |
# |
| 1797 |
|
| 1798 |
create table t1 (a char(200) primary key, b int default 12345) engine=aria; |
| 1799 |
insert t1 (a) values (repeat('0', 200)); |
| 1800 |
insert t1 (a) values (repeat('1', 200)), (repeat('2', 200)), (repeat('3', 200)), |
| 1801 |
(repeat('4', 200)), (repeat('5', 200)), (repeat('6', 200)), (repeat('7', 200)), |
| 1802 |
(repeat('8', 200)), (repeat('9', 200)), (repeat('a', 200)), (repeat('b', 200)), |
| 1803 |
(repeat('c', 200)), (repeat('d', 200)), (repeat('e', 200)), (repeat('f', 200)), |
| 1804 |
(repeat('g', 200)), (repeat('h', 200)), (repeat('i', 200)), (repeat('j', 200)), |
| 1805 |
(repeat('k', 200)), (repeat('l', 200)), (repeat('m', 200)), (repeat('n', 200)), |
| 1806 |
(repeat('o', 200)), (repeat('p', 200)), (repeat('q', 200)), (repeat('r', 200)), |
| 1807 |
(repeat('s', 200)), (repeat('t', 200)), (repeat('u', 200)), (repeat('v', 200)), |
| 1808 |
(repeat('w', 200)), (repeat('x', 200)), (repeat('y', 200)), (repeat('z', 200)), |
| 1809 |
(repeat('+', 200)), (repeat('-', 200)), (repeat('=', 200)), (repeat('*', 200)); |
| 1810 |
select b from t1 where a >= repeat('f', 200) and a < 'k'; |
| 1811 |
drop table t1; |
| 1812 |
|
| 1813 |
# |
| 1814 |
# BUG#38606 test suite |
| 1815 |
# |
| 1816 |
create table t1 (a int) engine=aria transactional=1; |
| 1817 |
insert into t1 values (1); |
| 1818 |
lock table t1 write concurrent; |
| 1819 |
# should be fixed with fully implemented versioning |
| 1820 |
--error ER_CHECK_NOT_IMPLEMENTED |
| 1821 |
delete from t1 where a>0; |
| 1822 |
--error ER_CHECK_NOT_IMPLEMENTED |
| 1823 |
delete from t1; |
| 1824 |
--error ER_TABLE_NOT_LOCKED_FOR_WRITE |
| 1825 |
truncate t1; |
| 1826 |
unlock tables; |
| 1827 |
drop table t1; |
| 1828 |
|
| 1829 |
# |
| 1830 |
# Bug#39243 SELECT WHERE does not find row |
| 1831 |
# (Problem with skip_row) |
| 1832 |
# |
| 1833 |
|
| 1834 |
create table t1 (p int primary key, i int, a char(10), key k1(i), key k2(a)) |
| 1835 |
engine aria; |
| 1836 |
insert into t1 values (1, 1, 'qqqq'), (2, 1, 'pppp'), |
| 1837 |
(3, 1, 'yyyy'), (4, 3, 'zzzz'); |
| 1838 |
insert into t1 values (5, 3, 'yyyy'), (6, 3, 'yyyy'), (7, 0, NULL), |
| 1839 |
(8, 0, NULL); |
| 1840 |
select * from t1 where a='zzzz'; |
| 1841 |
select * from t1 where a='yyyy'; |
| 1842 |
select * from t1 where a is NULL; |
| 1843 |
select * from t1; |
| 1844 |
check table t1; |
| 1845 |
drop table t1; |
| 1846 |
|
| 1847 |
# |
| 1848 |
# Bug39248 INSERT ON DUPLICATE KEY UPDATE gives error if using a view |
| 1849 |
# Note that this only crashes when using |
| 1850 |
# --mysqld=--binlog-format=row --ps-protocol |
| 1851 |
# |
| 1852 |
|
| 1853 |
create table t1 (f1 int unique, f2 int) engine=aria; |
| 1854 |
create table t2 (f3 int, f4 int) engine=aria; |
| 1855 |
create view v1 as select * from t1, t2 where f1= f3; |
| 1856 |
insert into t1 values (1,11), (2,22); |
| 1857 |
--error 1393 |
| 1858 |
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; |
| 1859 |
--error 1393 |
| 1860 |
insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; |
| 1861 |
drop table t1,t2; |
| 1862 |
drop view v1; |
| 1863 |
|
| 1864 |
# |
| 1865 |
# BUG#39399 ALTER TABLE renaming column: affected_rows > 0 |
| 1866 |
# |
| 1867 |
|
| 1868 |
CREATE TABLE t1 (id int, c varchar(10)) engine=aria; |
| 1869 |
INSERT INTO t1 VALUES (1,"1"); |
| 1870 |
--enable_info |
| 1871 |
ALTER TABLE t1 CHANGE c d varchar(10); |
| 1872 |
--disable_info |
| 1873 |
drop table t1; |
| 1874 |
|
| 1875 |
# |
| 1876 |
# Bug #39227 Aria: crash with ALTER TABLE PARTITION |
| 1877 |
# |
| 1878 |
|
| 1879 |
create table t1 (s1 int); |
| 1880 |
insert into t1 values (1); |
| 1881 |
--error ER_NO_PARTITION_FOR_GIVEN_VALUE |
| 1882 |
alter table t1 partition by list (s1) (partition p1 values in (2)); |
| 1883 |
drop table t1; |
| 1884 |
|
| 1885 |
# |
| 1886 |
# Bug #40311 |
| 1887 |
# Crash when aborting inserting of row with 2 blobs where first is short |
| 1888 |
# |
| 1889 |
|
| 1890 |
create table t1(a int primary key, b blob, c blob) engine=aria; |
| 1891 |
set sql_mode=''; |
| 1892 |
insert into t1 values(1,repeat('a',100), repeat('b',657860)); |
| 1893 |
--error ER_DUP_ENTRY |
| 1894 |
insert into t1 values(1,repeat('a',100), repeat('b',657860)); |
| 1895 |
set sql_mode=default; |
| 1896 |
check table t1; |
| 1897 |
drop table t1; |
| 1898 |
|
| 1899 |
# |
| 1900 |
# Check we can create a table with the 'Aria' alias |
| 1901 |
# |
| 1902 |
|
| 1903 |
Create table t1 (a int) engine="aria"; |
| 1904 |
show create table t1; |
| 1905 |
drop table t1; |
| 1906 |
|
| 1907 |
# |
| 1908 |
# Test of LP#614265. This happens when we where using quick_range_select in |
| 1909 |
# delete |
| 1910 |
# |
| 1911 |
|
| 1912 |
CREATE TABLE t1 ( f1 DOUBLE , f2 DOUBLE , f3 DOUBLE , f4 DOUBLE , v3 DOUBLE , v4 DOUBLE , KEY ( v3 ) , KEY ( v4 ) ) engine=maria; |
| 1913 |
REPLACE t1 ( f2 , f1 ) VALUES ( f2 , 56 ) ; |
| 1914 |
INSERT t1 ( f1 , f2 , f3 , f4 ) VALUES ( 0 , f2 , 8 , f3 ) ; |
| 1915 |
INSERT t1 ( f4 , f2 ) VALUES ( 4 , 92 ) ; |
| 1916 |
DELETE FROM t1 WHERE v3 = 173 OR v4 = 9 ; |
| 1917 |
drop table t1; |
| 1918 |
|
| 1919 |
# |
| 1920 |
# Test for LP#61465 Memory corruption with (M)aria storage engine and |
| 1921 |
# virtual columns |
| 1922 |
# |
| 1923 |
|
| 1924 |
CREATE TABLE t1 ( |
| 1925 |
f1 CHAR(255) BINARY , |
| 1926 |
f2 CHAR(255) BINARY NOT NULL DEFAULT '0', |
| 1927 |
f3 CHAR(255) BINARY NOT NULL , |
| 1928 |
f4 CHAR(255) BINARY NOT NULL DEFAULT '0' , |
| 1929 |
v3 CHAR(255) BINARY AS ( ( f1 NOT LIKE '%' ) ) PERSISTENT, |
| 1930 |
KEY (v3) |
| 1931 |
) ENGINE=Maria; |
| 1932 |
INSERT INTO t1 ( f1 , f2 , f3 , f4 ) SELECT f1 , f4 , f1 , f4 FROM t1; |
| 1933 |
DELETE FROM t1; |
| 1934 |
drop table t1; |
| 1935 |
|
| 1936 |
# |
| 1937 |
# Test of problem where REPAIR finds old deleted rows. |
| 1938 |
# |
| 1939 |
|
| 1940 |
create table t1 (a int not null primary key, b blob) engine=maria transactional=1; |
| 1941 |
insert into t1 values(1,repeat('a',8000)); |
| 1942 |
insert into t1 values(2,repeat('b',8000)); |
| 1943 |
insert into t1 values(3,repeat('c',8000)); |
| 1944 |
flush tables; |
| 1945 |
delete from t1 where a>1; |
| 1946 |
--error 1062 |
| 1947 |
insert into t1 values(1,repeat('d',8000*3)); |
| 1948 |
flush tables; |
| 1949 |
check table t1; |
| 1950 |
# This failed by finding 2 extra rows. |
| 1951 |
repair table t1 extended; |
| 1952 |
drop table t1; |
| 1953 |
|
| 1954 |
# |
| 1955 |
# Test problem with disable/enable keys |
| 1956 |
# |
| 1957 |
|
| 1958 |
create table t1 (a int, b int, key (a), key(b)); |
| 1959 |
alter table t1 disable keys; |
| 1960 |
insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); |
| 1961 |
alter table t1 enable keys; |
| 1962 |
select count(*) from t1; |
| 1963 |
drop table t1; |
| 1964 |
|
| 1965 |
create table t1 (a int not null, b int, primary key (a), key(b)); |
| 1966 |
alter table t1 disable keys; |
| 1967 |
insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); |
| 1968 |
alter table t1 enable keys; |
| 1969 |
select count(*) from t1; |
| 1970 |
drop table t1; |
| 1971 |
|
| 1972 |
create table t1 (a int not null, b int, primary key (a), key(b)); |
| 1973 |
lock tables t1 write; |
| 1974 |
alter table t1 disable keys; |
| 1975 |
insert into t1 values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1); |
| 1976 |
alter table t1 enable keys; |
| 1977 |
unlock tables; |
| 1978 |
select count(*) from t1; |
| 1979 |
drop table t1; |
| 1980 |
|
| 1981 |
--echo # |
| 1982 |
--echo # MDEV-14690: Assertion `page_link == &fake_link' failed in |
| 1983 |
--echo # pagecache_write_part |
| 1984 |
--echo # |
| 1985 |
|
| 1986 |
CREATE TABLE t1 (a CHAR(8), b CHAR(8), c CHAR(8) NOT NULL DEFAULT '', f FLOAT, KEY(f)) ENGINE=Aria; |
| 1987 |
INSERT INTO t1 (a) VALUES ('foo'); |
| 1988 |
DELETE FROM t1 WHERE c < 'bar'; |
| 1989 |
ALTER TABLE t1 DISABLE KEYS; |
| 1990 |
INSERT INTO t1 (b) VALUES (''); |
| 1991 |
ALTER TABLE t1 ENABLE KEYS; |
| 1992 |
|
| 1993 |
# Cleanup |
| 1994 |
DROP TABLE t1; |
| 1995 |
|
| 1996 |
--echo # |
| 1997 |
--echo # Start of 5.5 tests |
| 1998 |
--echo # |
| 1999 |
|
| 2000 |
--echo # |
| 2001 |
--echo # MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA |
| 2002 |
--echo # |
| 2003 |
|
| 2004 |
CREATE TABLE t1 ( |
| 2005 |
id TINYINT NOT NULL AUTO_INCREMENT, |
| 2006 |
name CHAR(30) NOT NULL, |
| 2007 |
PRIMARY KEY (id) |
| 2008 |
) ENGINE=ARIA; |
| 2009 |
SHOW CREATE TABLE t1; |
| 2010 |
INSERT INTO t1 (name) VALUES ('dog'); |
| 2011 |
UPDATE t1 SET id=-1 WHERE id=1; |
| 2012 |
INSERT INTO t1 (name) VALUES ('cat'); |
| 2013 |
SELECT * FROM t1; |
| 2014 |
DROP TABLE t1; |
| 2015 |
|
| 2016 |
# |
| 2017 |
# MDEV-16682 |
| 2018 |
# Assertion `(buff[7] & 7) == HEAD_PAGE' failed. |
| 2019 |
# |
| 2020 |
|
| 2021 |
CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; |
| 2022 |
INSERT INTO t1 VALUES (1,2), (2,3),(3,4); |
| 2023 |
DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); |
| 2024 |
DROP TABLE t1; |
| 2025 |
|
| 2026 |
--echo # |
| 2027 |
--echo # End of 5.5 tests |
| 2028 |
--echo # |
| 2029 |
|
| 2030 |
# |
| 2031 |
# Check FLUSH FOR EXPORT |
| 2032 |
# |
| 2033 |
|
| 2034 |
CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); |
| 2035 |
INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), |
| 2036 |
(6,'0'),(7,'0'); |
| 2037 |
flush tables test.t1 for export; |
| 2038 |
--error ER_TABLE_NOT_LOCKED_FOR_WRITE |
| 2039 |
insert into t1 values (8,'0'); |
| 2040 |
unlock tables; |
| 2041 |
drop table t1; |
| 2042 |
|
| 2043 |
# |
| 2044 |
# End of test |
| 2045 |
# |
| 2046 |
# Set defaults back |
| 2047 |
--disable_result_log |
| 2048 |
--disable_query_log |
| 2049 |
eval set global default_storage_engine=$default_engine, |
| 2050 |
aria_page_checksum=$default_checksum, |
| 2051 |
aria_log_file_size=$default_log_file_size; |
| 2052 |
--enable_result_log |
| 2053 |
--enable_query_log |
| 2054 |
|
| 2055 |
--echo # |
| 2056 |
--echo # MDEV-22284 Aria table key read crash because wrong index used |
| 2057 |
--echo # |
| 2058 |
create table t1 ( |
| 2059 |
a int auto_increment, |
| 2060 |
b int, c int, |
| 2061 |
key(c, a), unique(b) |
| 2062 |
) engine aria |
| 2063 |
partition by hash (b); |
| 2064 |
replace into t1 values (1, 0, 0), (2, 0, 0), (0, 0, 0); |
| 2065 |
# cleanup |
| 2066 |
drop table t1; |
| 2067 |
|
| 2068 |
--echo # |
| 2069 |
--echo # MDEV-28269 Assertion `save_errno' in maria_write or ER_GET_ERRNO |
| 2070 |
--echo # |
| 2071 |
|
| 2072 |
connect (session1,localhost,root,,); |
| 2073 |
SET big_tables= on; |
| 2074 |
SET NAMES 'sjis'; |
| 2075 |
SELECT 'למטה' AS a UNION SELECT 'Wetter' AS a; |
| 2076 |
connection default; |
| 2077 |
disconnect session1; |
| 2078 |
|
| 2079 |
--source include/test_db_charset_restore.inc |