diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/suite/maria/maria.result | 2844 |
1 files changed, 2844 insertions, 0 deletions
diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result new file mode 100644 index 00000000..ab1065f0 --- /dev/null +++ b/mysql-test/suite/maria/maria.result @@ -0,0 +1,2844 @@ +call mtr.add_suppression("Can't find record in '.*'"); +select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA"; +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +Aria YES Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables NO NO NO +set global default_storage_engine=aria; +set session default_storage_engine=aria; +set global aria_page_checksum=0; +set global aria_log_file_size=4294959104; +drop table if exists t1,t2; +drop view if exists v1; +SET SQL_WARNINGS=1; +CREATE TABLE t1 ( +STRING_DATA char(255) default NULL, +KEY string_data (STRING_DATA) +); +INSERT INTO t1 VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'); +INSERT INTO t1 VALUES ('DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD'); +INSERT INTO t1 VALUES ('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'); +INSERT INTO t1 VALUES ('FGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG'); +INSERT INTO t1 VALUES ('HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'); +INSERT INTO t1 VALUES ('WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'); +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (a int primary key auto_increment) engine=aria; +insert into t1 values (1); +update t1 set a=0 where a=1; +check table t1; +Table Op Msg_type Msg_text +test.t1 check warning Found row where the auto_increment column has the value 0 +test.t1 check status OK +select * from t1; +a +0 +drop table t1; +create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +delete from t1 where (a & 1); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +flush table t1; +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +drop table t1; +create table t1 (a int not null auto_increment, b int not null, primary key (a), index(b)); +insert into t1 (b) values (1),(2),(2),(2),(2); +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE NO +t1 1 b 1 b A 1 NULL NULL BTREE NO +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status Table is already up to date +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 0 PRIMARY 1 a A 5 NULL NULL BTREE NO +t1 1 b 1 b A 1 NULL NULL BTREE NO +drop table t1; +create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)); +insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4); +explain select * from t1 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort +explain select * from t1 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort +explain select * from t1 order by c; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort +explain select a from t1 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index +explain select b from t1 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL b 4 NULL 4 Using index +explain select a,b from t1 order by b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using filesort +explain select a,b from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select a,b,c from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +drop table t1; +set autocommit=0; +begin; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES (1), (2), (3); +commit; +set autocommit=1; +UNLOCK TABLES; +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +create table t1 ( t1 char(255), key(t1(250))); +insert t1 values ('137513751375137513751375137513751375137569516951695169516951695169516951695169'); +insert t1 values ('178417841784178417841784178417841784178403420342034203420342034203420342034203'); +insert t1 values ('213872387238723872387238723872387238723867376737673767376737673767376737673767'); +insert t1 values ('242624262426242624262426242624262426242607890789078907890789078907890789078907'); +insert t1 values ('256025602560256025602560256025602560256011701170117011701170117011701170117011'); +insert t1 values ('276027602760276027602760276027602760276001610161016101610161016101610161016101'); +insert t1 values ('281528152815281528152815281528152815281564956495649564956495649564956495649564'); +insert t1 values ('292129212921292129212921292129212921292102100210021002100210021002100210021002'); +insert t1 values ('380638063806380638063806380638063806380634483448344834483448344834483448344834'); +insert t1 values ('411641164116411641164116411641164116411616301630163016301630163016301630163016'); +insert t1 values ('420842084208420842084208420842084208420899889988998899889988998899889988998899'); +insert t1 values ('438443844384438443844384438443844384438482448244824482448244824482448244824482'); +insert t1 values ('443244324432443244324432443244324432443239613961396139613961396139613961396139'); +insert t1 values ('485448544854485448544854485448544854485477847784778477847784778477847784778477'); +insert t1 values ('494549454945494549454945494549454945494555275527552755275527552755275527552755'); +insert t1 values ('538647864786478647864786478647864786478688918891889188918891889188918891889188'); +insert t1 values ('565556555655565556555655565556555655565554845484548454845484548454845484548454'); +insert t1 values ('607860786078607860786078607860786078607856665666566656665666566656665666566656'); +insert t1 values ('640164016401640164016401640164016401640141274127412741274127412741274127412741'); +insert t1 values ('719471947194719471947194719471947194719478717871787178717871787178717871787178'); +insert t1 values ('742574257425742574257425742574257425742549604960496049604960496049604960496049'); +insert t1 values ('887088708870887088708870887088708870887035963596359635963596359635963596359635'); +insert t1 values ('917791779177917791779177917791779177917773857385738573857385738573857385738573'); +insert t1 values ('933293329332933293329332933293329332933278987898789878987898789878987898789878'); +insert t1 values ('963896389638963896389638963896389638963877807780778077807780778077807780778077'); +delete from t1 where t1>'2'; +insert t1 values ('70'), ('84'), ('60'), ('20'), ('76'), ('89'), ('49'), ('50'), +('88'), ('61'), ('42'), ('98'), ('39'), ('30'), ('25'), ('66'), ('61'), ('48'), +('80'), ('84'), ('98'), ('19'), ('91'), ('42'), ('47'); +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 +int, i9 int, i10 int, i11 int, i12 int, i13 int, i14 int, i15 int, i16 int, i17 +int, i18 int, i19 int, i20 int, i21 int, i22 int, i23 int, i24 int, i25 int, +i26 int, i27 int, i28 int, i29 int, i30 int, i31 int, i32 int, i33 int, i34 +int, i35 int, i36 int, i37 int, i38 int, i39 int, i40 int, i41 int, i42 int, +i43 int, i44 int, i45 int, i46 int, i47 int, i48 int, i49 int, i50 int, i51 +int, i52 int, i53 int, i54 int, i55 int, i56 int, i57 int, i58 int, i59 int, +i60 int, i61 int, i62 int, i63 int, i64 int, i65 int, i66 int, i67 int, i68 +int, i69 int, i70 int, i71 int, i72 int, i73 int, i74 int, i75 int, i76 int, +i77 int, i78 int, i79 int, i80 int, i81 int, i82 int, i83 int, i84 int, i85 +int, i86 int, i87 int, i88 int, i89 int, i90 int, i91 int, i92 int, i93 int, +i94 int, i95 int, i96 int, i97 int, i98 int, i99 int, i100 int, i101 int, i102 +int, i103 int, i104 int, i105 int, i106 int, i107 int, i108 int, i109 int, i110 +int, i111 int, i112 int, i113 int, i114 int, i115 int, i116 int, i117 int, i118 +int, i119 int, i120 int, i121 int, i122 int, i123 int, i124 int, i125 int, i126 +int, i127 int, i128 int, i129 int, i130 int, i131 int, i132 int, i133 int, i134 +int, i135 int, i136 int, i137 int, i138 int, i139 int, i140 int, i141 int, i142 +int, i143 int, i144 int, i145 int, i146 int, i147 int, i148 int, i149 int, i150 +int, i151 int, i152 int, i153 int, i154 int, i155 int, i156 int, i157 int, i158 +int, i159 int, i160 int, i161 int, i162 int, i163 int, i164 int, i165 int, i166 +int, i167 int, i168 int, i169 int, i170 int, i171 int, i172 int, i173 int, i174 +int, i175 int, i176 int, i177 int, i178 int, i179 int, i180 int, i181 int, i182 +int, i183 int, i184 int, i185 int, i186 int, i187 int, i188 int, i189 int, i190 +int, i191 int, i192 int, i193 int, i194 int, i195 int, i196 int, i197 int, i198 +int, i199 int, i200 int, i201 int, i202 int, i203 int, i204 int, i205 int, i206 +int, i207 int, i208 int, i209 int, i210 int, i211 int, i212 int, i213 int, i214 +int, i215 int, i216 int, i217 int, i218 int, i219 int, i220 int, i221 int, i222 +int, i223 int, i224 int, i225 int, i226 int, i227 int, i228 int, i229 int, i230 +int, i231 int, i232 int, i233 int, i234 int, i235 int, i236 int, i237 int, i238 +int, i239 int, i240 int, i241 int, i242 int, i243 int, i244 int, i245 int, i246 +int, i247 int, i248 int, i249 int, i250 int, i251 int, i252 int, i253 int, i254 +int, i255 int, i256 int, i257 int, i258 int, i259 int, i260 int, i261 int, i262 +int, i263 int, i264 int, i265 int, i266 int, i267 int, i268 int, i269 int, i270 +int, i271 int, i272 int, i273 int, i274 int, i275 int, i276 int, i277 int, i278 +int, i279 int, i280 int, i281 int, i282 int, i283 int, i284 int, i285 int, i286 +int, i287 int, i288 int, i289 int, i290 int, i291 int, i292 int, i293 int, i294 +int, i295 int, i296 int, i297 int, i298 int, i299 int, i300 int, i301 int, i302 +int, i303 int, i304 int, i305 int, i306 int, i307 int, i308 int, i309 int, i310 +int, i311 int, i312 int, i313 int, i314 int, i315 int, i316 int, i317 int, i318 +int, i319 int, i320 int, i321 int, i322 int, i323 int, i324 int, i325 int, i326 +int, i327 int, i328 int, i329 int, i330 int, i331 int, i332 int, i333 int, i334 +int, i335 int, i336 int, i337 int, i338 int, i339 int, i340 int, i341 int, i342 +int, i343 int, i344 int, i345 int, i346 int, i347 int, i348 int, i349 int, i350 +int, i351 int, i352 int, i353 int, i354 int, i355 int, i356 int, i357 int, i358 +int, i359 int, i360 int, i361 int, i362 int, i363 int, i364 int, i365 int, i366 +int, i367 int, i368 int, i369 int, i370 int, i371 int, i372 int, i373 int, i374 +int, i375 int, i376 int, i377 int, i378 int, i379 int, i380 int, i381 int, i382 +int, i383 int, i384 int, i385 int, i386 int, i387 int, i388 int, i389 int, i390 +int, i391 int, i392 int, i393 int, i394 int, i395 int, i396 int, i397 int, i398 +int, i399 int, i400 int, i401 int, i402 int, i403 int, i404 int, i405 int, i406 +int, i407 int, i408 int, i409 int, i410 int, i411 int, i412 int, i413 int, i414 +int, i415 int, i416 int, i417 int, i418 int, i419 int, i420 int, i421 int, i422 +int, i423 int, i424 int, i425 int, i426 int, i427 int, i428 int, i429 int, i430 +int, i431 int, i432 int, i433 int, i434 int, i435 int, i436 int, i437 int, i438 +int, i439 int, i440 int, i441 int, i442 int, i443 int, i444 int, i445 int, i446 +int, i447 int, i448 int, i449 int, i450 int, i451 int, i452 int, i453 int, i454 +int, i455 int, i456 int, i457 int, i458 int, i459 int, i460 int, i461 int, i462 +int, i463 int, i464 int, i465 int, i466 int, i467 int, i468 int, i469 int, i470 +int, i471 int, i472 int, i473 int, i474 int, i475 int, i476 int, i477 int, i478 +int, i479 int, i480 int, i481 int, i482 int, i483 int, i484 int, i485 int, i486 +int, i487 int, i488 int, i489 int, i490 int, i491 int, i492 int, i493 int, i494 +int, i495 int, i496 int, i497 int, i498 int, i499 int, i500 int, i501 int, i502 +int, i503 int, i504 int, i505 int, i506 int, i507 int, i508 int, i509 int, i510 +int, i511 int, i512 int, i513 int, i514 int, i515 int, i516 int, i517 int, i518 +int, i519 int, i520 int, i521 int, i522 int, i523 int, i524 int, i525 int, i526 +int, i527 int, i528 int, i529 int, i530 int, i531 int, i532 int, i533 int, i534 +int, i535 int, i536 int, i537 int, i538 int, i539 int, i540 int, i541 int, i542 +int, i543 int, i544 int, i545 int, i546 int, i547 int, i548 int, i549 int, i550 +int, i551 int, i552 int, i553 int, i554 int, i555 int, i556 int, i557 int, i558 +int, i559 int, i560 int, i561 int, i562 int, i563 int, i564 int, i565 int, i566 +int, i567 int, i568 int, i569 int, i570 int, i571 int, i572 int, i573 int, i574 +int, i575 int, i576 int, i577 int, i578 int, i579 int, i580 int, i581 int, i582 +int, i583 int, i584 int, i585 int, i586 int, i587 int, i588 int, i589 int, i590 +int, i591 int, i592 int, i593 int, i594 int, i595 int, i596 int, i597 int, i598 +int, i599 int, i600 int, i601 int, i602 int, i603 int, i604 int, i605 int, i606 +int, i607 int, i608 int, i609 int, i610 int, i611 int, i612 int, i613 int, i614 +int, i615 int, i616 int, i617 int, i618 int, i619 int, i620 int, i621 int, i622 +int, i623 int, i624 int, i625 int, i626 int, i627 int, i628 int, i629 int, i630 +int, i631 int, i632 int, i633 int, i634 int, i635 int, i636 int, i637 int, i638 +int, i639 int, i640 int, i641 int, i642 int, i643 int, i644 int, i645 int, i646 +int, i647 int, i648 int, i649 int, i650 int, i651 int, i652 int, i653 int, i654 +int, i655 int, i656 int, i657 int, i658 int, i659 int, i660 int, i661 int, i662 +int, i663 int, i664 int, i665 int, i666 int, i667 int, i668 int, i669 int, i670 +int, i671 int, i672 int, i673 int, i674 int, i675 int, i676 int, i677 int, i678 +int, i679 int, i680 int, i681 int, i682 int, i683 int, i684 int, i685 int, i686 +int, i687 int, i688 int, i689 int, i690 int, i691 int, i692 int, i693 int, i694 +int, i695 int, i696 int, i697 int, i698 int, i699 int, i700 int, i701 int, i702 +int, i703 int, i704 int, i705 int, i706 int, i707 int, i708 int, i709 int, i710 +int, i711 int, i712 int, i713 int, i714 int, i715 int, i716 int, i717 int, i718 +int, i719 int, i720 int, i721 int, i722 int, i723 int, i724 int, i725 int, i726 +int, i727 int, i728 int, i729 int, i730 int, i731 int, i732 int, i733 int, i734 +int, i735 int, i736 int, i737 int, i738 int, i739 int, i740 int, i741 int, i742 +int, i743 int, i744 int, i745 int, i746 int, i747 int, i748 int, i749 int, i750 +int, i751 int, i752 int, i753 int, i754 int, i755 int, i756 int, i757 int, i758 +int, i759 int, i760 int, i761 int, i762 int, i763 int, i764 int, i765 int, i766 +int, i767 int, i768 int, i769 int, i770 int, i771 int, i772 int, i773 int, i774 +int, i775 int, i776 int, i777 int, i778 int, i779 int, i780 int, i781 int, i782 +int, i783 int, i784 int, i785 int, i786 int, i787 int, i788 int, i789 int, i790 +int, i791 int, i792 int, i793 int, i794 int, i795 int, i796 int, i797 int, i798 +int, i799 int, i800 int, i801 int, i802 int, i803 int, i804 int, i805 int, i806 +int, i807 int, i808 int, i809 int, i810 int, i811 int, i812 int, i813 int, i814 +int, i815 int, i816 int, i817 int, i818 int, i819 int, i820 int, i821 int, i822 +int, i823 int, i824 int, i825 int, i826 int, i827 int, i828 int, i829 int, i830 +int, i831 int, i832 int, i833 int, i834 int, i835 int, i836 int, i837 int, i838 +int, i839 int, i840 int, i841 int, i842 int, i843 int, i844 int, i845 int, i846 +int, i847 int, i848 int, i849 int, i850 int, i851 int, i852 int, i853 int, i854 +int, i855 int, i856 int, i857 int, i858 int, i859 int, i860 int, i861 int, i862 +int, i863 int, i864 int, i865 int, i866 int, i867 int, i868 int, i869 int, i870 +int, i871 int, i872 int, i873 int, i874 int, i875 int, i876 int, i877 int, i878 +int, i879 int, i880 int, i881 int, i882 int, i883 int, i884 int, i885 int, i886 +int, i887 int, i888 int, i889 int, i890 int, i891 int, i892 int, i893 int, i894 +int, i895 int, i896 int, i897 int, i898 int, i899 int, i900 int, i901 int, i902 +int, i903 int, i904 int, i905 int, i906 int, i907 int, i908 int, i909 int, i910 +int, i911 int, i912 int, i913 int, i914 int, i915 int, i916 int, i917 int, i918 +int, i919 int, i920 int, i921 int, i922 int, i923 int, i924 int, i925 int, i926 +int, i927 int, i928 int, i929 int, i930 int, i931 int, i932 int, i933 int, i934 +int, i935 int, i936 int, i937 int, i938 int, i939 int, i940 int, i941 int, i942 +int, i943 int, i944 int, i945 int, i946 int, i947 int, i948 int, i949 int, i950 +int, i951 int, i952 int, i953 int, i954 int, i955 int, i956 int, i957 int, i958 +int, i959 int, i960 int, i961 int, i962 int, i963 int, i964 int, i965 int, i966 +int, i967 int, i968 int, i969 int, i970 int, i971 int, i972 int, i973 int, i974 +int, i975 int, i976 int, i977 int, i978 int, i979 int, i980 int, i981 int, i982 +int, i983 int, i984 int, i985 int, i986 int, i987 int, i988 int, i989 int, i990 +int, i991 int, i992 int, i993 int, i994 int, i995 int, i996 int, i997 int, i998 +int, i999 int, i1000 int, b blob) row_format=dynamic; +insert into t1 values (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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 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, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei"); +update t1 set b=repeat('a',256); +update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +delete from t1 where i8=1; +select i1,i2 from t1; +i1 i2 +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +CREATE TABLE `t1` ( +`post_id` mediumint(8) unsigned NOT NULL auto_increment, +`topic_id` mediumint(8) unsigned NOT NULL default '0', +`post_time` datetime NOT NULL default '0000-00-00 00:00:00', +`post_text` text NOT NULL, +`icon_url` varchar(10) NOT NULL default '', +`sign` tinyint(1) unsigned NOT NULL default '0', +`post_edit` varchar(150) NOT NULL default '', +`poster_login` varchar(35) NOT NULL default '', +`ip` varchar(15) NOT NULL default '', +PRIMARY KEY (`post_id`), +KEY `post_time` (`post_time`), +KEY `ip` (`ip`), +KEY `poster_login` (`poster_login`), +KEY `topic_id` (`topic_id`), +FULLTEXT KEY `post_text` (`post_text`) +) TRANSACTIONAL=0; +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'); +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +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)); +ERROR 42000: Specified key was too long; max key length is 2300 bytes +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)); +ERROR 42000: Too many key parts specified; max 32 parts allowed +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)); +ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e, f, g, h, i, j); +ERROR 42000: Specified key was too long; max key length is 2300 bytes +DROP TABLE t1; +CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a)); +INSERT into t1 values (0,null,0), (0,null,1), (0,null,2), (0,null,3), (1,1,4); +INSERT into t1 values (2,4,5), (7,8,4), (8,3,1), (9,7,2), (5,5,9); +create table t2 (a int not null, b int, c int, key(b), key(c), key(a)); +INSERT into t2 values (1,1,1), (2,2,2); +optimize table t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 b 1 b A 10 NULL NULL YES BTREE NO +t1 1 c 1 c A 10 NULL NULL YES BTREE NO +t1 1 a 1 a A 10 NULL NULL BTREE NO +t1 1 a 2 b A 10 NULL NULL YES BTREE NO +t1 1 c_2 1 c A 10 NULL NULL YES BTREE NO +t1 1 c_2 2 a A 10 NULL NULL BTREE NO +explain select * from t1,t2 where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t1 ref a a 4 test.t2.a 1 +explain select * from t1,t2 force index(a) where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t1 ref a a 4 test.t2.a 1 +explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL a NULL NULL NULL 2 +1 SIMPLE t1 ref a a 4 test.t2.a 1 +explain select * from t1,t2 where t1.b=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL b NULL NULL NULL 2 Using where +1 SIMPLE t1 ref b b 5 test.t2.b 1 +explain select * from t1,t2 force index(c) where t1.a=t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t1 ref a a 4 test.t2.a 1 +explain select * from t1 where a=0 or a=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 5 Using index condition +explain select * from t1 force index (a) where a=0 or a=2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 4 NULL 5 Using index condition +explain select * from t1 where c=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c,c_2 c 5 const 2 +explain select * from t1 use index() where c=1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where +drop table t1,t2; +create table t1 (a int not null auto_increment primary key, b varchar(255)); +insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100)); +update t1 set b=repeat(left(b,1),200) where a=1; +delete from t1 where (a & 1)= 0; +update t1 set b=repeat('e',200) where a=1; +flush tables; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +update t1 set b=repeat(left(b,1),255) where a between 1 and 5; +update t1 set b=repeat(left(b,1),10) where a between 32 and 43; +update t1 set b=repeat(left(b,1),2) where a between 64 and 66; +update t1 set b=repeat(left(b,1),65) where a between 67 and 70; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +insert into t1 (b) values (repeat('z',100)); +update t1 set b="test" where left(b,1) > 'n'; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 ( a text not null, key a (a(20))); +insert into t1 values ('aaa '),('aaa'),('aa'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select concat(a,'.') from t1 where a='aaa'; +concat(a,'.') +aaa . +aaa. +select concat(a,'.') from t1 where binary a='aaa'; +concat(a,'.') +aaa. +update t1 set a='bbb' where a='aaa'; +select concat(a,'.') from t1; +concat(a,'.') +bbb. +bbb. +aa. +drop table t1; +create table t1 ( a text not null, key a (a(20))) row_format=dynamic; +insert into t1 values ('aaa '),('aaa'),('aa'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +select concat(a,'.') from t1 where a='aaa'; +concat(a,'.') +aaa . +aaa. +select concat(a,'.') from t1 where binary a='aaa'; +concat(a,'.') +aaa. +update t1 set a='bbb' where a='aaa'; +select concat(a,'.') from t1; +concat(a,'.') +bbb. +bbb. +aa. +drop table t1; +create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10))); +insert into t1 values('807780', '477', '165'); +insert into t1 values('807780', '477', '162'); +insert into t1 values('807780', '472', '162'); +select * from t1 where a='807780' and b='477' and c='165'; +a b c +807780 477 165 +drop table t1; +CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); +INSERT t1 VALUES ("can \tcan"); +INSERT t1 VALUES ("can can"); +INSERT t1 VALUES ("can"); +SELECT * FROM t1; +a +can can +can +can can +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +create table t1 (a blob); +insert into t1 values('a '),('a'); +select concat(a,'.') from t1 where a='a'; +concat(a,'.') +a. +select concat(a,'.') from t1 where a='a '; +concat(a,'.') +a . +alter table t1 add key(a(2)); +select concat(a,'.') from t1 where a='a'; +concat(a,'.') +a. +select concat(a,'.') from t1 where a='a '; +concat(a,'.') +a . +drop table t1; +create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20))); +insert into t1 (b) values ('a'),('b'),('c'); +select concat(b,'.') from t1; +concat(b,'.') +a. +b. +c. +update t1 set b='b ' where a=2; +update t1 set b='b ' where a > 1; +ERROR 23000: Duplicate entry 'b ' for key 'b' +insert into t1 (b) values ('b'); +ERROR 23000: Duplicate entry 'b' for key 'b' +select * from t1; +a b +1 a +2 b +3 c +delete from t1 where b='b'; +select a,concat(b,'.') from t1; +a concat(b,'.') +1 a. +3 c. +drop table t1; +create table t1 (a int not null); +create table t2 (a int not null, primary key (a)); +insert into t1 values (1); +insert into t2 values (1),(2); +select sql_big_result distinct t1.a from t1,t2 order by t2.a; +a +1 +select distinct t1.a from t1,t2 order by t2.a; +a +1 +select sql_big_result distinct t1.a from t1,t2; +a +1 +explain select sql_big_result distinct t1.a from t1,t2 order by t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary +1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index +explain select distinct t1.a from t1,t2 order by t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary +1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index +drop table t1,t2; +create table t1 ( +c1 varchar(32), +key (c1) +); +alter table t1 disable keys; +insert into t1 values ('a'), ('b'); +select c1 from t1 order by c1 limit 1; +c1 +a +drop table t1; +create table t1 (a int not null, primary key(a)) ROW_FORMAT=FIXED; +create table t2 (a int not null, b int not null, primary key(a,b)) ROW_FORMAT=FIXED; +insert into t1 values (1),(2),(3),(4),(5),(6); +insert into t2 values (1,1),(2,1); +set autocommit=0; +begin; +lock tables t1 read local, t2 read local; +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +a a b +1 1 1 +2 2 1 +connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK; +insert into t2 values(2,0); +commit; +disconnect root; +connection default; +select straight_join * from t1,t2 force index (primary) where t1.a=t2.a; +a a b +1 1 1 +2 2 1 +unlock tables; +drop table t1,t2; +set autocommit=1; +CREATE TABLE t1 (c1 varchar(250) NOT NULL) ROW_FORMAT=DYNAMIC; +CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1)) ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003'); +INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004'); +LOCK TABLES t1 READ LOCAL, t2 READ LOCAL; +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 +WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +t1c1 t2c1 +connect con1,localhost,root,,; +connection con1; +INSERT INTO t2 VALUES ('test000001'), ('test000005'); +disconnect con1; +connection default; +SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2 +WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1; +t1c1 t2c1 +unlock tables; +DROP TABLE t1,t2; +CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)); +Got one of the listed errors +create table t1 (a int, b varchar(200), c text not null) checksum=1; +create table t2 (a int, b varchar(200), c text not null) checksum=0; +insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, ""); +insert t2 select * from t1; +checksum table t1, t2, t3 quick; +Table Checksum +test.t1 3442722830 +test.t2 NULL +test.t3 NULL +Warnings: +Error 1146 Table 'test.t3' doesn't exist +checksum table t1, t2, t3; +Table Checksum +test.t1 3442722830 +test.t2 3442722830 +test.t3 NULL +Warnings: +Error 1146 Table 'test.t3' doesn't exist +checksum table t1, t2, t3 extended; +Table Checksum +test.t1 3442722830 +test.t2 3442722830 +test.t3 NULL +Warnings: +Error 1146 Table 'test.t3' doesn't exist +drop table t1,t2; +create table t1 (a int, key (a)); +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE NO +alter table t1 disable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO +create table t2 (a int); +set @@rand_seed1=31415926,@@rand_seed2=2718281828; +insert t1 select * from t2; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A NULL NULL NULL YES BTREE disabled NO +alter table t1 enable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 1000 NULL NULL YES BTREE NO +alter table t1 engine=heap; +alter table t1 disable keys; +Warnings: +Note 1031 Storage engine MEMORY of the table `test`.`t1` doesn't have this option +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a NULL 500 NULL NULL YES HASH NO +drop table t1,t2; +create table t1 ( a tinytext, b char(1), index idx (a(1),b) ); +insert into t1 values (null,''), (null,''); +explain select count(*) from t1 where a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx idx 4 const 2 Using where +insert into t1 values(1,'a'); +explain select count(*) from t1 where a is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx idx 4 const 2 Using where +explain select count(*) from t1 where a = 'a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref idx idx 4 const 1 Using where +select count(*) from t1 where a is null; +count(*) +2 +drop table t1; +create table t1 (c1 int, c2 varchar(4) not null default '', +key(c2(3))) default charset=utf8; +insert into t1 values (1,'A'), (2, 'B'), (3, 'A'); +update t1 set c2='A B' where c1=2; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (c1 int); +insert into t1 values (1),(2),(3),(4); +checksum table t1; +Table Checksum +test.t1 149057747 +delete from t1 where c1 = 1; +create table t2 as select * from t1; +checksum table t1; +Table Checksum +test.t1 984116287 +checksum table t2; +Table Checksum +test.t2 984116287 +drop table t1, t2; +CREATE TABLE t1 ( +twenty int(4), +hundred int(4) NOT NULL +) CHECKSUM=1; +INSERT INTO t1 VALUES (11,91); +check table t1 extended; +Table Op Msg_type Msg_text +test.t1 check status OK +checksum table t1; +Table Checksum +test.t1 3235292310 +checksum table t1 extended; +Table Checksum +test.t1 3235292310 +alter table t1 row_format=fixed; +checksum table t1; +Table Checksum +test.t1 3235292310 +alter table t1 row_format=dynamic; +checksum table t1; +Table Checksum +test.t1 4183529555 +alter table t1 engine=myisam; +checksum table t1; +Table Checksum +test.t1 4183529555 +drop table t1; +show variables like 'aria_stats_method'; +Variable_name Value +aria_stats_method nulls_unequal +create table t1 (a int, key(a)); +insert into t1 values (0),(1),(2),(3),(4); +insert into t1 select NULL from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO +insert into t1 values (11); +delete from t1 where a=11; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO +set aria_stats_method=nulls_equal; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables= COMPLEMENTARY; +show variables like 'aria_stats_method'; +Variable_name Value +aria_stats_method nulls_equal +insert into t1 values (11); +delete from t1 where a=11; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO +insert into t1 values (11); +delete from t1 where a=11; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 5 NULL NULL YES BTREE NO +set @@use_stat_tables= @save_use_stat_tables; +set aria_stats_method=DEFAULT; +show variables like 'aria_stats_method'; +Variable_name Value +aria_stats_method nulls_unequal +insert into t1 values (11); +delete from t1 where a=11; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO +insert into t1 values (11); +delete from t1 where a=11; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 10 NULL NULL YES BTREE NO +drop table t1; +set aria_stats_method=nulls_ignored; +show variables like 'aria_stats_method'; +Variable_name Value +aria_stats_method nulls_ignored +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables= COMPLEMENTARY; +create table t1 ( +a char(3), b char(4), c char(5), d char(6), +key(a,b,c,d) +); +insert into t1 values ('bcd','def1', NULL, 'zz'); +insert into t1 values ('bcd','def2', NULL, 'zz'); +insert into t1 values ('bce','def1', 'yuu', NULL); +insert into t1 values ('bce','def2', NULL, 'quux'); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 2 NULL NULL YES BTREE NO +t1 1 a 2 b A 4 NULL NULL YES BTREE NO +t1 1 a 3 c A 4 NULL NULL YES BTREE NO +t1 1 a 4 d A 4 NULL NULL YES BTREE NO +delete from t1; +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +show index from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 0 NULL NULL YES BTREE NO +t1 1 a 2 b A 0 NULL NULL YES BTREE NO +t1 1 a 3 c A 0 NULL NULL YES BTREE NO +t1 1 a 4 d A 0 NULL NULL YES BTREE NO +set @@use_stat_tables= @save_use_stat_tables; +set aria_stats_method=DEFAULT; +drop table t1; +create table t1( +cip INT NOT NULL, +time TIME NOT NULL, +score INT NOT NULL DEFAULT 0, +bob TINYBLOB +); +insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03'); +insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), +(6, 'c', '00:06'); +select * from t1 where bob is null and cip=1; +cip time score bob +1 00:01:00 0 NULL +create index bug on t1 (bob(22), cip, time); +select * from t1 where bob is null and cip=1; +cip time score bob +1 00:01:00 0 NULL +drop table t1; +create table t1 ( +id1 int not null auto_increment, +id2 int not null default '0', +t text not null, +primary key (id1), +key x (id2, t(32)) +) engine=aria; +insert into t1 (id2, t) values +(10, 'abc'), (10, 'abc'), (10, 'abc'), +(20, 'abc'), (20, 'abc'), (20, 'def'), +(10, 'abc'), (10, 'abc'); +select count(*) from t1 where id2 = 10; +count(*) +5 +select count(id1) from t1 where id2 = 10; +count(id1) +5 +drop table t1; +CREATE TABLE t1(a TINYINT, KEY(a)); +INSERT INTO t1 VALUES(1); +SELECT MAX(a) FROM t1 IGNORE INDEX(a); +MAX(a) +1 +ALTER TABLE t1 DISABLE KEYS; +SELECT MAX(a) FROM t1; +MAX(a) +1 +SELECT MAX(a) FROM t1 IGNORE INDEX(a); +MAX(a) +1 +DROP TABLE t1; +CREATE TABLE t1(a CHAR(9), b VARCHAR(7)); +INSERT INTO t1(a) VALUES('xxxxxxxxx'),('xxxxxxxxx'); +UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; +SELECT * FROM t1; +a b +xxxxxxxxx bbbbbb +xxxxxxxxx bbbbbb +DROP TABLE t1; +SET @@aria_repair_threads=2; +SHOW VARIABLES LIKE 'aria_repair%'; +Variable_name Value +aria_repair_threads 2 +CREATE TABLE t1 ( +`_id` int(11) NOT NULL default '0', +`url` text, +`email` text, +`description` text, +`loverlap` int(11) default NULL, +`roverlap` int(11) default NULL, +`lneighbor_id` int(11) default NULL, +`rneighbor_id` int(11) default NULL, +`length_` int(11) default NULL, +`sequence` mediumtext, +`name` text, +`_obj_class` text NOT NULL, +PRIMARY KEY (`_id`), +UNIQUE KEY `sequence_name_index` (`name`(50)), +KEY (`length_`) +) DEFAULT CHARSET=latin1 row_format=dynamic; +INSERT INTO t1 VALUES +(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), +(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), +(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), +(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), +(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), +(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), +(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), +(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), +(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +SELECT _id FROM t1; +_id +1 +2 +3 +4 +5 +6 +7 +8 +9 +DELETE FROM t1 WHERE _id < 8; +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'; +table_name engine version row_format Table_rows Data_free create_options table_comment +t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC transactional=0 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +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'; +table_name engine version row_format Table_rows Data_free create_options table_comment +t1 Aria 10 Dynamic 2 0 row_format=DYNAMIC transactional=0 +SELECT _id FROM t1; +_id +8 +9 +DROP TABLE t1; +CREATE TABLE t1 ( +`_id` int(11) NOT NULL default '0', +`url` text, +`email` text, +`description` text, +`loverlap` int(11) default NULL, +`roverlap` int(11) default NULL, +`lneighbor_id` int(11) default NULL, +`rneighbor_id` int(11) default NULL, +`length_` int(11) default NULL, +`sequence` mediumtext, +`name` text, +`_obj_class` text NOT NULL, +PRIMARY KEY (`_id`), +UNIQUE KEY `sequence_name_index` (`name`(50)), +KEY (`length_`) +) DEFAULT CHARSET=latin1 row_format=dynamic; +INSERT INTO t1 VALUES +(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample1',''), +(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample2',''), +(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample3',''), +(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample4',''), +(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample5',''), +(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample6',''), +(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample7',''), +(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample8',''), +(9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'sample9',''); +SELECT _id FROM t1; +_id +1 +2 +3 +4 +5 +6 +7 +8 +9 +DELETE FROM t1 WHERE _id < 8; +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'; +table_name engine version row_format Table_rows Data_free create_options table_comment +t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC transactional=0 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 QUICK; +Table Op Msg_type Msg_text +test.t1 repair status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +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'; +table_name engine version row_format Table_rows Data_free create_options table_comment +t1 Aria 10 Dynamic 2 140 row_format=DYNAMIC transactional=0 +SELECT _id FROM t1; +_id +8 +9 +DROP TABLE t1; +SET @@aria_repair_threads=1; +SHOW VARIABLES LIKE 'aria_repair%'; +Variable_name Value +aria_repair_threads 1 +drop table if exists t1,t2,t3; +--- Testing varchar --- +--- Testing varchar --- +create table t1 (v varchar(10), c char(10), t text); +insert into t1 values('+ ', '+ ', '+ '); +set @a=repeat(' ',20); +insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a)); +Warnings: +Note 1265 Data truncated for column 'v' at row 1 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+ *+*+ * +*+ *+*+ * +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +create table t2 like t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +create table t3 select * from t1; +show create table t3; +Table Create Table +t3 CREATE TABLE `t3` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +alter table t1 modify c varchar(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) DEFAULT NULL, + `c` varchar(10) DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +alter table t1 modify v char(10); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) DEFAULT NULL, + `c` varchar(10) DEFAULT NULL, + `t` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +alter table t1 modify t varchar(10); +Warnings: +Note 1265 Data truncated for column 't' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) DEFAULT NULL, + `c` varchar(10) DEFAULT NULL, + `t` varchar(10) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +select concat('*',v,'*',c,'*',t,'*') from t1; +concat('*',v,'*',c,'*',t,'*') +*+*+*+ * +*+*+*+ * +drop table t1,t2,t3; +create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL, + KEY `v` (`v`), + KEY `c` (`c`), + KEY `t` (`t`(10)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +select count(*) from t1; +count(*) +270 +insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1))); +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where c='a'; +count(*) +10 +select count(*) from t1 where t='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where c='a '; +count(*) +10 +select count(*) from t1 where t='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where c like 'a%'; +count(*) +11 +select count(*) from t1 where t like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const # Using where; Using index +explain select count(*) from t1 where c='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref c c 11 const # Using where; Using index +explain select count(*) from t1 where t='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref t t 13 const # Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 13 NULL # Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const # Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 13 const # Using where; Using index +alter table t1 add unique(v); +ERROR 23000: Duplicate entry '{ ' for key 'v_2' +show warnings; +Level Code Message +Error 1062 Duplicate entry 'a\0001' for key 'v_2' +alter table t1 add key(v); +Warnings: +Note 1831 Duplicate index `v_2`. This is deprecated and will be disallowed in a future release +select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a'; +qq +*a*a*a* +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +*a *a*a * +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v,v_2 # 13 const # # +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(c) from t1 group by v limit 10; +v count(c) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(*) from t1 group by c limit 10; +c count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result c,count(t) from t1 group by c limit 10; +c count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(*) from t1 group by t limit 10; +t count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result t,count(t) from t1 group by t limit 10; +t count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(300) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL, + KEY `c` (`c`), + KEY `t` (`t`(10)), + KEY `v` (`v`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 303 const # Using where; Using index +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 303 NULL # Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 303 const # Using where; Using index +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 303 const # Using where; Using index +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 303 const # # +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +alter table t1 drop key v, add key v (v(30)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(300) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL, + KEY `c` (`c`), + KEY `t` (`t`(10)), + KEY `v` (`v`(30)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +select count(*) from t1 where v='a'; +count(*) +10 +select count(*) from t1 where v='a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a '; +count(*) +10 +select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +count(*) +10 +select count(*) from t1 where v like 'a%'; +count(*) +11 +select count(*) from t1 where v like 'a %'; +count(*) +9 +explain select count(*) from t1 where v='a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 33 const # Using where +explain select count(*) from t1 where v like 'a%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range v v 33 NULL # Using where +explain select count(*) from t1 where v between 'a' and 'a '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 33 const # Using where +explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 33 const # Using where +explain select * from t1 where v='a'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref v v 33 const # # +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +alter table t1 modify v varchar(600), drop key v, add key v (v); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(600) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL, + KEY `c` (`c`), + KEY `t` (`t`(10)), + KEY `v` (`v`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +select v,count(*) from t1 group by v limit 10; +v count(*) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +select sql_big_result v,count(t) from t1 group by v limit 10; +v count(t) +a 1 +a 10 +b 10 +c 10 +d 10 +e 10 +f 10 +g 10 +h 10 +i 10 +drop table t1; +create table t1 (a char(10), unique (a)); +insert into t1 values ('a '); +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a' for key 'a' +alter table t1 modify a varchar(10); +insert into t1 values ('a '),('a '),('a '),('a '); +ERROR 23000: Duplicate entry 'a ' for key 'a' +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 'a' +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 'a' +insert into t1 values ('a '); +ERROR 23000: Duplicate entry 'a ' for key 'a' +update t1 set a='a ' where a like 'a%'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='abc ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a %'; +select concat(a,'.') from t1; +concat(a,'.') +a . +update t1 set a='a ' where a like 'a '; +select concat(a,'.') from t1; +concat(a,'.') +a . +drop table t1; +create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5))); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL, + `t` text DEFAULT NULL, + KEY `v` (`v`(5)), + KEY `c` (`c`(5)), + KEY `t` (`t`(5)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (v char(10) character set utf8); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (v varchar(10), c char(10)) row_format=fixed; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` varchar(10) DEFAULT NULL, + `c` char(10) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=FIXED +insert into t1 values('a','a'),('a ','a '); +select concat('*',v,'*',c,'*') from t1; +concat('*',v,'*',c,'*') +*a*a* +*a *a* +drop table t1; +create table t1 (v varchar(65530), key(v(10))); +insert into t1 values(repeat('a',65530)); +select length(v) from t1 where v=repeat('a',65530); +length(v) +65530 +drop table t1; +create table t1(a int, b varchar(12), key ba(b, a)); +insert into t1 values (1, 'A'), (20, NULL); +explain select * from t1 where a=20 and b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index +select * from t1 where a=20 and b is null; +a b +20 NULL +drop table t1; +create table t1 (v varchar(65530), key(v)); +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +drop table if exists t1; +set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for +create table t1 (v varchar(65536)); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for +create table t1 (v varchar(65530) character set utf8); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +set statement sql_mode='' for +create table t1 (v varchar(65535)); +Warnings: +Note 1246 Converting column 'v' from VARCHAR to TEXT +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `v` text DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +set @save_concurrent_insert=@@concurrent_insert; +set global concurrent_insert=1; +create table t1 (a int) ROW_FORMAT=FIXED; +insert into t1 values (1),(2),(3),(4),(5); +lock table t1 read local; +connect con1,localhost,root,,; +connection con1; +insert into t1 values(6),(7); +connection default; +unlock tables; +delete from t1 where a>=3 and a<=4; +lock table t1 read local; +connection con1; +set global concurrent_insert=2; +insert into t1 values (8),(9); +connection default; +unlock tables; +insert into t1 values (10),(11),(12); +select * from t1; +a +1 +2 +11 +10 +5 +6 +7 +8 +9 +12 +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +disconnect con1; +create table t1 (a int, b varchar(30) default "hello") ROW_FORMAT=DYNAMIC; +insert into t1 (a) values (1),(2),(3),(4),(5); +lock table t1 read local; +connect con1,localhost,root,,; +connection con1; +insert into t1 (a) values(6),(7); +connection default; +unlock tables; +delete from t1 where a>=3 and a<=4; +lock table t1 read local; +connection con1; +set global concurrent_insert=2; +insert into t1 (a) values (8),(9); +connection default; +unlock tables; +insert into t1 (a) values (10),(11),(12); +select a from t1; +a +1 +2 +11 +10 +5 +6 +7 +8 +9 +12 +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +disconnect con1; +set global concurrent_insert=@save_concurrent_insert; +create table t1 (a int, key(a)); +insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 8 NULL NULL YES BTREE NO +alter table t1 disable keys; +alter table t1 enable keys; +show keys from t1; +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored +t1 1 a 1 a A 8 NULL NULL YES BTREE NO +drop table t1; +connect session1,localhost,root,,; +connect session2,localhost,root,,; +connection session1; +show create table t1; +connection session2; +show create table t1; +connection default; +create table t1 (a int) select 42 a; +connection session1; +select * from t1; +a +9 +disconnect session1; +connection session2; +select * from t1; +a +99 +disconnect session2; +connection default; +select * from t1; +a +42 +drop table t1; +End of 4.1 tests +create table t1 (c1 int) pack_keys=0; +create table t2 (c1 int) pack_keys=1; +create table t3 (c1 int) pack_keys=default; +create table t4 (c1 int) pack_keys=2; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2' at line 1 +drop table t1, t2, t3; +CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)); +INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5); +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +ALTER TABLE t1 DISABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +SELECT a FROM t1 USE INDEX (inx) WHERE a=1; +a +1 +SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1; +b +1 +SELECT b FROM t1 USE INDEX (uinx) WHERE b=1; +b +1 +SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1; +a +1 +ALTER TABLE t1 ENABLE KEYS; +SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1; +a +1 +DROP TABLE t1; +CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE INDEX (c1), INDEX (c2)); +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 0 # # # 8192 # # # # # # # transactional=1 # N +INSERT INTO t1 VALUES (1,1); +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 1 # # # 24576 # # # # # # # transactional=1 # N +ALTER TABLE t1 DISABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 1 # # # 24576 # # # # # # # transactional=1 # N +ALTER TABLE t1 ENABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 1 # # # 24576 # # # # # # # transactional=1 # N +ALTER TABLE t1 DISABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 1 # # # 24576 # # # # # # # transactional=1 # N +ALTER TABLE t1 ENABLE KEYS; +SHOW TABLE STATUS LIKE 't1'; +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary +t1 Aria 10 Page 1 # # # 24576 # # # # # # # transactional=1 # N +# Enable keys with parallel repair +SET @@aria_repair_threads=2; +ALTER TABLE t1 DISABLE KEYS; +ALTER TABLE t1 ENABLE KEYS; +SET @@aria_repair_threads=1; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 (id int NOT NULL, ref int NOT NULL, INDEX (id)); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); +INSERT INTO t1 SELECT * FROM t2; +SELECT * FROM t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +id ref ref +4 4 5 +SELECT * FROM t1; +id ref +1 3 +2 1 +3 2 +4 5 +4 4 +DELETE FROM a USING t1 AS a INNER JOIN t1 AS b USING (id) WHERE a.ref < b.ref; +SELECT * FROM t1; +id ref +1 3 +2 1 +3 2 +4 5 +DROP TABLE t1, t2; +CREATE TABLE t1 (a INT) ENGINE=ARIA CHECKSUM=1 ROW_FORMAT=DYNAMIC; +INSERT INTO t1 VALUES (0); +UPDATE t1 SET a=1; +SELECT a FROM t1; +a +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES (0), (5), (4), (2); +UPDATE t1 SET a=2; +SELECT a FROM t1; +a +2 +2 +2 +2 +2 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +End of 5.0 tests +create table t1 (a int not null, key `a` (a) key_block_size=1024); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a int not null, key `a` (a) key_block_size=2048); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a varchar(4096), key `a` (a)); +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(4096) DEFAULT NULL, + KEY `a` (`a`(2300)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a varchar(4096), key `a` (a) key_block_size=1024); +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(4096) DEFAULT NULL, + KEY `a` (`a`(2300)) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=1024; +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=1024 +alter table t1 key_block_size=2048; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=2048 +alter table t1 add c int, add key (c); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192, + KEY `c` (`c`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=2048 +alter table t1 key_block_size=4096; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192, + KEY `c` (`c`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=4096 +alter table t1 key_block_size=0; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192, + KEY `c` (`c`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +alter table t1 add d int, add key (d); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + `c` int(11) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`(2300)) KEY_BLOCK_SIZE=8192, + KEY `c` (`c`) KEY_BLOCK_SIZE=8192, + KEY `d` (`d`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a int not null, b varchar(4096), key (a), key(b)) key_block_size=8192; +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`(2300)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=8192 +drop table t1; +create table t1 (a int not null, b varchar(4096), key (a) key_block_size=1024, key(b)) key_block_size=8192; +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(4096) DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`(2300)) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=8192 +drop table t1; +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; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192, + KEY `b` (`b`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 KEY_BLOCK_SIZE=16384 +drop table t1; +create table t1 (a int not null, key `a` (a) key_block_size=512); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a varchar(4096), key `a` (a) key_block_size=1000000000000000000); +Warnings: +Note 1071 Specified key was too long; max key length is 2300 bytes +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(4096) DEFAULT NULL, + KEY `a` (`a`(2300)) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a int not null, key `a` (a) key_block_size=1025); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + KEY `a` (`a`) KEY_BLOCK_SIZE=8192 +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +create table t1 (a int not null, key key_block_size=1024 (a)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1024 (a))' at line 1 +create table t1 (a int not null, key `a` key_block_size=1024 (a)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_block_size=1024 (a))' at line 1 +CREATE TABLE t1 ( +c1 INT, +c2 VARCHAR(300), +KEY (c1) KEY_BLOCK_SIZE 1024, +KEY (c2) KEY_BLOCK_SIZE 8192 +); +INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))), +(11, REPEAT('b', CEIL(RAND() * 300))), +(12, REPEAT('c', CEIL(RAND() * 300))), +(13, REPEAT('d', CEIL(RAND() * 300))), +(14, REPEAT('e', CEIL(RAND() * 300))), +(15, REPEAT('f', CEIL(RAND() * 300))), +(16, REPEAT('g', CEIL(RAND() * 300))), +(17, REPEAT('h', CEIL(RAND() * 300))), +(18, REPEAT('i', CEIL(RAND() * 300))), +(19, REPEAT('j', CEIL(RAND() * 300))), +(20, REPEAT('k', CEIL(RAND() * 300))), +(21, REPEAT('l', CEIL(RAND() * 300))), +(22, REPEAT('m', CEIL(RAND() * 300))), +(23, REPEAT('n', CEIL(RAND() * 300))), +(24, REPEAT('o', CEIL(RAND() * 300))), +(25, REPEAT('p', CEIL(RAND() * 300))), +(26, REPEAT('q', CEIL(RAND() * 300))), +(27, REPEAT('r', CEIL(RAND() * 300))), +(28, REPEAT('s', CEIL(RAND() * 300))), +(29, REPEAT('t', CEIL(RAND() * 300))), +(30, REPEAT('u', CEIL(RAND() * 300))), +(31, REPEAT('v', CEIL(RAND() * 300))), +(32, REPEAT('w', CEIL(RAND() * 300))), +(33, REPEAT('x', CEIL(RAND() * 300))), +(34, REPEAT('y', CEIL(RAND() * 300))), +(35, REPEAT('z', CEIL(RAND() * 300))); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +DELETE FROM t1 WHERE c1 >= 10; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=aria; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=aria; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1) +) ENGINE=aria; +INSERT INTO t1 VALUES(REPEAT("a",128), 'b'); +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(130), +c2 VARCHAR(1), +KEY (c1) +) ENGINE=aria; +# Insert 100 rows. Query log disabled. +UPDATE t1 SET c1=REPEAT("a",128) LIMIT 90; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +ALTER TABLE t1 ENGINE=aria; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=aria DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=aria DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +REPAIR TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 repair status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +1 +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1) +) ENGINE=aria DEFAULT CHARSET UTF8; +INSERT INTO t1 VALUES(REPEAT(_utf8 x'e0ae85',43), 'b'); +INSERT INTO t1 VALUES('b', 'b'); +INSERT INTO t1 VALUES('c', 'b'); +DELETE FROM t1 WHERE c1='b'; +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; +CREATE TABLE t1 ( +c1 CHAR(50), +c2 VARCHAR(1), +KEY (c1) +) ENGINE=aria DEFAULT CHARSET UTF8; +# Insert 100 rows. Query log disabled. +UPDATE t1 SET c1=REPEAT(_utf8 x'e0ae85',43) LIMIT 90; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +ALTER TABLE t1 ENGINE=aria; +SELECT COUNT(*) FROM t1; +COUNT(*) +100 +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; +CREATE TABLE t1 ( +c1 VARCHAR(10) NOT NULL, +c2 CHAR(10) DEFAULT NULL, +c3 VARCHAR(10) NOT NULL, +KEY (c1), +KEY (c2) +) ENGINE=aria DEFAULT CHARSET=utf8 PACK_KEYS=0; +Aria file: MYSQLD_DATADIR/test/t1 +Record format: Block +Crashsafe: yes +Character set: utf8mb3_general_ci (33) +Data records: 0 Deleted blocks: 0 +Block_size: 8192 +Recordlength: 99 + +Table description: +Key Start Len Index Type +1 2 30 multip. varchar +2 33 30 multip. char NULL +DROP TABLE t1; +create table t1 (n int not null, c char(1)) transactional=1; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `n` int(11) NOT NULL, + `c` char(1) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 TRANSACTIONAL=1 +drop table t1; +CREATE TABLE t1 (line LINESTRING NOT NULL) engine=aria; +INSERT INTO t1 VALUES (GeomFromText("LINESTRING(0 0)")); +checksum table t1; +Table Checksum +test.t1 310616673 +CREATE TABLE t2 (line LINESTRING NOT NULL) engine=aria; +INSERT INTO t2 VALUES (GeomFromText("LINESTRING(0 0)")); +checksum table t2; +Table Checksum +test.t2 310616673 +CREATE TABLE t3 select * from t1; +checksum table t3; +Table Checksum +test.t3 310616673 +drop table t1,t2,t3; +End of 5.1 tests +create table t2(a varchar(255),key(a))engine=aria row_format=dynamic transactional=0; +insert into t2 values (repeat('o',124)), (repeat('h',226)), (repeat('i',236)), +(repeat('l',234)), (repeat('b',13)), (repeat('g',236)), (repeat('y',205)), +(repeat('c',99)), (repeat('g',145)), (repeat('o',131)), (repeat('e',63)), +(repeat('q',155)), (repeat('k',87)), (repeat('i',54)), (repeat('p',84)), +(repeat('m',119)), (repeat('c',2)), (repeat('a',174)), (repeat('g',160)), +(repeat('t',147)), (repeat('n',107)); +insert into t2 values ('nupdjlafwfvuuvruxkyjxpmupihzgspkaybijztkeukgzzkrxmd'); +insert into t2 values (repeat('g',40)), (repeat('i',173)), (repeat('q',126)), +(repeat('i',217)), (repeat('f',161)), (repeat('i',28)), (repeat('a',35)), +(repeat('y',27)), (repeat('o',100)), (repeat('o',175)), (repeat('f',69)), +(repeat('k',156)), (repeat('n',220)), (repeat('q',247)), (repeat('y',180)), +(repeat('v',209)), (repeat('m',169)), (repeat('y',170)), (repeat('r',151)), +(repeat('d',38)), (repeat('g',64)), (repeat('k',77)), (repeat('l',150)), +(repeat('s',150)), (repeat('u',127)), (repeat('l',15)), (repeat('m',33)), +(repeat('r',177)), (repeat('v',197)), (repeat('k',62)), (repeat('h',219)), +(repeat('u',161)), (repeat('y',118)), (repeat('i',184)), (repeat('z',202)), +(repeat('j',113)), (repeat('q',95)), (repeat('q',164)), (repeat('e',54)), +(repeat('e',60)), (repeat('l',203)), (repeat('g',77)), (repeat('y',44)), +(repeat('j',196)), (repeat('t',45)), (repeat('l',190)), (repeat('l',89)), +(repeat('q',45)), (repeat('e',191)), (repeat('t',38)), (repeat('f',148)), +(repeat('c',25)), (repeat('v',97)), (repeat('i',83)), (repeat('s',166)), +(repeat('d',96)), (repeat('v',82)), (repeat('n',127)), (repeat('i',201)), +(repeat('x',184)), (repeat('d',76)), (repeat('u',17)), (repeat('a',178)); +insert into t2 values ('hwvfiavnmufgbulapzrolonwxufheqymvjncnczlzcjokzqlsvmomcjzgzwzquyxpunxdmotdczocwliaprpubwaeccsulvittgizcutxxb'); +insert into t2 values (repeat('x',28)), (repeat('p',21)), (repeat('k',241)), +(repeat('i',243)), (repeat('b',172)), (repeat('z',94)), (repeat('i',218)), +(repeat('a',177)), (repeat('g',251)), (repeat('q',161)), (repeat('x',231)), +(repeat('p',51)), (repeat('f',141)), (repeat('m',28)), (repeat('r',77)), +(repeat('h',56)), (repeat('k',23)), (repeat('f',198)), (repeat('o',243)), +(repeat('d',160)), (repeat('h',82)); +check table t2 extended; +Table Op Msg_type Msg_text +test.t2 check status OK +drop table t2; +CREATE TABLE t1 ( +col0 float DEFAULT NULL, +col1 date DEFAULT NULL, +col2 double DEFAULT NULL, +col3 decimal(10,0) DEFAULT NULL, +col4 char(218) DEFAULT NULL, +col5 year(4) DEFAULT NULL, +col6 datetime DEFAULT NULL, +col7 varchar(39) DEFAULT NULL, +col8 double DEFAULT NULL, +col9 decimal(10,0) DEFAULT NULL, +col10 enum('test1','test2','test3') DEFAULT NULL, +col11 year(4) DEFAULT NULL, +col12 tinytext, +col13 tinyblob, +col14 date DEFAULT NULL, +col15 smallint(6) DEFAULT NULL, +col16 varchar(81) DEFAULT NULL, +col17 tinytext, +col18 blob, +col19 double DEFAULT NULL, +col20 double DEFAULT NULL, +col21 varchar(216) DEFAULT NULL, +col22 enum('test1','test2','test3') DEFAULT NULL, +col23 decimal(10,0) DEFAULT NULL, +col24 text, +col25 varchar(118) DEFAULT NULL, +col26 tinytext, +col27 tinyblob, +col28 double DEFAULT NULL, +col29 tinyint(4) DEFAULT NULL, +col30 longtext, +col31 tinyint(1) DEFAULT NULL, +col32 char(212) DEFAULT NULL, +col33 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, +col34 year(4) DEFAULT NULL, +col35 tinyint(1) DEFAULT NULL, +col36 enum('test1','test2','test3') DEFAULT NULL, +col37 decimal(10,0) DEFAULT NULL, +col38 tinyint(4) DEFAULT NULL, +col39 double DEFAULT NULL, +col40 decimal(10,0) DEFAULT NULL, +col41 enum('test1','test2','test3') DEFAULT NULL, +col42 longblob, +col43 text, +col44 blob, +col45 year(4) DEFAULT NULL, +col46 longtext, +col47 int(11) DEFAULT NULL, +col48 set('test1','test2','test3') DEFAULT NULL, +col49 bigint(20) DEFAULT NULL, +col50 date DEFAULT NULL, +col51 tinyblob, +col52 float DEFAULT NULL, +col53 year(4) DEFAULT NULL, +col54 decimal(10,0) DEFAULT NULL, +col55 tinyblob, +col56 float DEFAULT NULL, +col57 bigint(20) DEFAULT NULL, +col58 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', +col59 enum('test1','test2','test3') DEFAULT NULL, +col60 bigint(20) DEFAULT NULL, +col61 year(4) DEFAULT NULL, +col62 year(4) DEFAULT NULL, +col63 double DEFAULT NULL, +col64 tinytext, +col65 tinyint(4) DEFAULT NULL, +col66 longtext, +col67 time DEFAULT NULL, +col68 bigint(20) DEFAULT NULL, +col69 char(142) DEFAULT NULL, +col70 longtext, +col71 time DEFAULT NULL, +col72 year(4) DEFAULT NULL, +col73 longblob, +col74 enum('test1','test2','test3') DEFAULT NULL, +col75 decimal(10,0) DEFAULT NULL, +col76 smallint(6) DEFAULT NULL, +col77 tinytext, +col78 date DEFAULT NULL, +col79 double DEFAULT NULL, +col80 tinyint(4) DEFAULT NULL, +col81 float DEFAULT NULL, +col82 bigint(20) DEFAULT NULL, +col83 double DEFAULT NULL, +col84 varchar(124) DEFAULT NULL, +col85 double DEFAULT NULL, +col86 tinyblob, +col87 tinyblob, +col88 double DEFAULT NULL, +col89 date DEFAULT NULL, +col90 decimal(10,0) DEFAULT NULL, +col91 set('test1','test2','test3') DEFAULT NULL, +col92 blob, +col93 char(174) DEFAULT NULL, +col94 double DEFAULT NULL, +col95 tinytext, +col96 decimal(10,0) DEFAULT NULL, +col97 year(4) DEFAULT NULL, +col98 tinyblob, +col99 datetime DEFAULT NULL, +col100 longblob, +col101 date DEFAULT NULL, +col102 float DEFAULT NULL, +col103 float DEFAULT NULL, +col104 int(11) DEFAULT NULL, +col105 longblob, +col106 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', +col107 float DEFAULT NULL, +col108 text, +col109 float DEFAULT NULL, +col110 decimal(10,0) DEFAULT NULL, +col111 double DEFAULT NULL, +col112 double DEFAULT NULL, +col113 blob, +col114 varchar(152) DEFAULT NULL, +col115 bigint(20) DEFAULT NULL, +col116 decimal(10,0) DEFAULT NULL, +col117 mediumint(9) DEFAULT NULL, +col118 tinytext, +col119 tinyblob, +col120 int(11) DEFAULT NULL, +col121 bigint(20) DEFAULT NULL, +col122 double DEFAULT NULL, +col123 date DEFAULT NULL, +col124 longtext, +col125 longtext, +col126 double DEFAULT NULL, +col127 varchar(84) DEFAULT NULL, +col128 text, +col129 double DEFAULT NULL, +col130 enum('test1','test2','test3') DEFAULT NULL, +col131 time DEFAULT NULL, +col132 year(4) DEFAULT NULL, +col133 blob, +col134 tinytext, +col135 double DEFAULT NULL, +col136 tinytext, +col137 bigint(20) DEFAULT NULL, +col138 datetime DEFAULT NULL, +col139 double DEFAULT NULL, +col140 decimal(10,0) DEFAULT NULL, +col141 longtext, +col142 tinyint(1) DEFAULT NULL, +col143 time DEFAULT NULL, +col144 time DEFAULT NULL, +col145 float DEFAULT NULL, +col146 longblob, +col147 float DEFAULT NULL, +col148 text, +col149 mediumint(9) DEFAULT NULL, +col150 tinyblob, +col151 tinyblob, +col152 tinytext, +col153 tinyblob, +col154 tinyblob, +col155 tinytext, +col156 tinyint(1) DEFAULT NULL, +col157 tinytext, +col158 time DEFAULT NULL, +col159 date DEFAULT NULL, +col160 longtext, +col161 enum('test1','test2','test3') DEFAULT NULL, +col162 text, +col163 decimal(10,0) DEFAULT NULL, +col164 time DEFAULT NULL, +col165 longblob, +col166 tinyint(4) DEFAULT NULL, +col167 bigint(20) DEFAULT NULL, +col168 decimal(10,0) DEFAULT NULL, +col169 smallint(6) DEFAULT NULL, +col170 tinytext, +col171 tinyint(4) DEFAULT NULL, +col172 tinyint(1) DEFAULT NULL, +col173 tinytext, +col174 decimal(10,0) DEFAULT NULL, +col175 double DEFAULT NULL +) engine=aria; +insert ignore into t1 set +col10=abs(28449) % 2, +col11='1973', +col12=if(abs(-30039)%100<20,null,'forgery\'s'), +col13=if(abs(24672)%100<20,null,'adductor\'s'), +col16=if(abs(26872)%100<20,null,'0xf810e016ee0b78e1ce8b1c6cf8d2e82bf8507453768a3908dc20cecfc9a0ac0ac00079d0645a4c'), +col18=if(abs(-15854)%100<20,null,'unattractiveness'), +col19=if(abs(4287439673.9896235000)%100<20,null,25288), +col20=if(abs(4290800136.7527390000)%100<20,null,8887), +col21=if(abs(-26086)%100<20,null,'0x2334181c6068aab18b348ecc1e2600b81e1c5f821eee3e204824'), +col22=abs(-21921) % 2, +col23=if(abs(1503277.6900540178)%100<20,null,-23298), +col25=if(abs(29674)%100<20,null,'0xc8d094f888ee20c83baef8d9380a168d40f4906e742a4dc0daeacb809e64095c71d510c7c0f83a6a0a04b8d6a0d9bea2dc3d4bd44d9c5002e440707c40ead8b3eb20a100a8524b1616a338a440ea02a25a08041810a08cac087cd47b4a79f08730946c5800600ae45e1c08f637'), +col26=if(abs(28642)%100<20,null,'insubstantiality\'s'), +col27=if(abs(-3188)%100<20,null,'exine\'s'), +col29=if(abs(-47)%100<20,null,-24131), +col32=if(abs(-3658)%100<20,null,'shortener\'s'), +col34='1917', +col36=abs(27782) % 2, +col37=if(abs(4864972.0244758446)%100<20,null,32302), +col38=if(abs(97)%100<20,null,-14079), +col39=if(abs(3362872.0521256141)%100<20,null,27191), +col40=if(abs(3348292.2110660113)%100<20,null,-1163), +col41=abs(-18533) % 2, +col42=if(abs(2094)%100<20,null,'Montparnasse'), +col43=if(abs(-15983)%100<20,null,'Massasoit\'s'), +col44=if(abs(2497)%100<20,null,'lags'), +col45='2057', +col46=if(abs(-31691)%100<20,null,'miscegenation\'s'), +col47=if(abs(-1269564297)%100<20,null,1089), +col49=if(abs(-1815717335)%100<20,null,-17504), +col51=if(abs(-15263)%100<20,null,'virelay'), +col52=if(abs(2227333.3279519030)%100<20,null,-5210), +col53='2032', +col54=if(abs(791647.5947447127)%100<20,null,32576), +col55=if(abs(20293)%100<20,null,'tumblebug'), +col56=if(abs(4288698564.2967925000)%100<20,null,17141), +col57=if(abs(-2138460927)%100<20,null,14495), +col59=abs(7624) % 2, +col60=if(abs(-1500892492)%100<20,null,-68), +col63=if(abs(4290890487.3789482000)%100<20,null,-32129), +col65=if(abs(22)%100<20,null,15722), +col79=if(abs(4292420489.2606282000)%100<20,null,-23891), +col84=if(abs(-21248)%100<20,null,'0x4f9888d044435050eab83cb3dcad88b01886e434e216'), +col85=if(abs(4294260188.6230965000)%100<20,null,16867), +col86=if(abs(-11659)%100<20,null,'prewar'), +col87=if(abs(-2253)%100<20,null,'Radnorshire'), +col90=if(abs(4287254529.4026613000)%100<20,null,23506), +col92=if(abs(6472)%100<20,null,'electroplated'), +col93=if(abs(-13523)%100<20,null,'sparkiest'), +col95=if(abs(23998)%100<20,null,'Crimea'), +col96=if(abs(4287719060.2789087000)%100<20,null,20527), +col98=if(abs(-14090)%100<20,null,'firebrat'), +col99='19161023095430', +col100=if(abs(-31178)%100<20,null,'clinical'), +col102=if(abs(4407547.5205542166)%100<20,null,18226), +col103=if(abs(4286834687.5994444000)%100<20,null,27520), +col104=if(abs(-2105663477)%100<20,null,28591), +col105=if(abs(1929)%100<20,null,'renascent'), +col107=if(abs(5972348.8099917602)%100<20,null,-11408), +col108=if(abs(-11262)%100<20,null,'aircraftmen'), +col110=if(abs(6530491.4546037167)%100<20,null,-17672), +col111=if(abs(4289897795.5000763000)%100<20,null,3742), +col112=if(abs(1680557.8560441907)%100<20,null,13944), +col113=if(abs(-27195)%100<20,null,'dekameter'), +col115=if(abs(-2083419827)%100<20,null,-17272), +col117=if(abs(1704826)%100<20,null,17880), +col118=if(abs(-2848)%100<20,null,'judicatory'), +col119=if(abs(-28087)%100<20,null,'mistitles'), +col120=if(abs(-2100119097)%100<20,null,22465), +col121=if(abs(-1868777891)%100<20,null,15172), +col122=if(abs(7039857.3608508557)%100<20,null,-22154), +col125=if(abs(70)%100<20,null,'Hong\'s'), +col126=if(abs(3820673.5968199712)%100<20,null,-24185), +col127=if(abs(12331)%100<20,null,'0x674e14584e88fca3fed0a0b1488a440008228aa01454a65cf09e3f0fa0511c3ce2f8688450'), +col128=if(abs(20335)%100<20,null,'Zoroaster\'s'), +col129=if(abs(3916577.6225165562)%100<20,null,-4088), +col130=abs(-15003) % 2, +col132='2016', +col134=if(abs(-26555)%100<20,null,'Caesarea'), +col135=if(abs(4288484655.2416148000)%100<20,null,-30073), +col136=if(abs(-17577)%100<20,null,'upbraid'), +col137=if(abs(-1742797945)%100<20,null,-21651), +col138='20751113181230', +col139=if(abs(4288997063.9889216000)%100<20,null,1816), +col141=if(abs(-31448)%100<20,null,'threnodist'), +col142=if(abs(88)%100<20,null,-19748), +col143='6930607', +col144='5760250', +col145=if(abs(3591496.9625843074)%100<20,null,76), +col146=if(abs(20875)%100<20,null,'rename'), +col147=if(abs(4294789439.6773582000)%100<20,null,32314), +col148=if(abs(7072)%100<20,null,'recesses'), +col150=if(abs(-26540)%100<20,null,'cuckoo'), +col152=if(abs(23553)%100<20,null,'shortened'), +col153=if(abs(-30422)%100<20,null,'inhabitant'), +col154=if(abs(30457)%100<20,null,'Orwellian'), +col155=if(abs(-30263)%100<20,null,'Ptolemies'), +col156=if(abs(-41)%100<20,null,6382), +col157=if(abs(2557)%100<20,null,'horsewhips'), +col158='2764427', +col160=if(abs(-15872)%100<20,null,'girlhood'), +col161=abs(15378) % 2, +col163=if(abs(4286662730.9309368000)%100<20,null,-19516), +col167=if(abs(-1881918655)%100<20,null,6927), +col169=if(abs(-14442)%100<20,null,-6392), +col170=if(abs(29965)%100<20,null,'resynthesis'), +col173=if(abs(-451)%100<20,null,'Clute'), +col174=if(abs(3262594.6284981840)%100<20,null,17846); +Warnings: +Warning 1265 Data truncated for column 'col25' at row 1 +Warning 1264 Out of range value for column 'col29' at row 1 +Warning 1265 Data truncated for column 'col36' at row 1 +Warning 1264 Out of range value for column 'col38' at row 1 +Warning 1265 Data truncated for column 'col59' at row 1 +Warning 1264 Out of range value for column 'col65' at row 1 +Warning 1264 Out of range value for column 'col142' at row 1 +Warning 1264 Out of range value for column 'col156' at row 1 +Warning 1265 Data truncated for column 'col161' at row 1 +update ignore t1 set col165=repeat('a',7000); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (a char(200) primary key, b int default 12345) engine=aria; +insert t1 (a) values (repeat('0', 200)); +insert t1 (a) values (repeat('1', 200)), (repeat('2', 200)), (repeat('3', 200)), +(repeat('4', 200)), (repeat('5', 200)), (repeat('6', 200)), (repeat('7', 200)), +(repeat('8', 200)), (repeat('9', 200)), (repeat('a', 200)), (repeat('b', 200)), +(repeat('c', 200)), (repeat('d', 200)), (repeat('e', 200)), (repeat('f', 200)), +(repeat('g', 200)), (repeat('h', 200)), (repeat('i', 200)), (repeat('j', 200)), +(repeat('k', 200)), (repeat('l', 200)), (repeat('m', 200)), (repeat('n', 200)), +(repeat('o', 200)), (repeat('p', 200)), (repeat('q', 200)), (repeat('r', 200)), +(repeat('s', 200)), (repeat('t', 200)), (repeat('u', 200)), (repeat('v', 200)), +(repeat('w', 200)), (repeat('x', 200)), (repeat('y', 200)), (repeat('z', 200)), +(repeat('+', 200)), (repeat('-', 200)), (repeat('=', 200)), (repeat('*', 200)); +select b from t1 where a >= repeat('f', 200) and a < 'k'; +b +12345 +12345 +12345 +12345 +12345 +drop table t1; +create table t1 (a int) engine=aria transactional=1; +insert into t1 values (1); +lock table t1 write concurrent; +delete from t1 where a>0; +ERROR 42000: The storage engine for the table doesn't support DELETE in WRITE CONCURRENT +delete from t1; +ERROR 42000: The storage engine for the table doesn't support TRUNCATE in WRITE CONCURRENT +truncate t1; +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +unlock tables; +drop table t1; +create table t1 (p int primary key, i int, a char(10), key k1(i), key k2(a)) +engine aria; +insert into t1 values (1, 1, 'qqqq'), (2, 1, 'pppp'), +(3, 1, 'yyyy'), (4, 3, 'zzzz'); +insert into t1 values (5, 3, 'yyyy'), (6, 3, 'yyyy'), (7, 0, NULL), +(8, 0, NULL); +select * from t1 where a='zzzz'; +p i a +4 3 zzzz +select * from t1 where a='yyyy'; +p i a +3 1 yyyy +5 3 yyyy +6 3 yyyy +select * from t1 where a is NULL; +p i a +7 0 NULL +8 0 NULL +select * from t1; +p i a +1 1 qqqq +2 1 pppp +3 1 yyyy +4 3 zzzz +5 3 yyyy +6 3 yyyy +7 0 NULL +8 0 NULL +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +create table t1 (f1 int unique, f2 int) engine=aria; +create table t2 (f3 int, f4 int) engine=aria; +create view v1 as select * from t1, t2 where f1= f3; +insert into t1 values (1,11), (2,22); +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +insert into v1 (f1) values (3) on duplicate key update f1= f3 + 10; +ERROR HY000: Can not modify more than one base table through a join view 'test.v1' +drop table t1,t2; +drop view v1; +CREATE TABLE t1 (id int, c varchar(10)) engine=aria; +INSERT INTO t1 VALUES (1,"1"); +ALTER TABLE t1 CHANGE c d varchar(10); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +drop table t1; +create table t1 (s1 int); +insert into t1 values (1); +alter table t1 partition by list (s1) (partition p1 values in (2)); +ERROR HY000: Table has no partition for value 1 +drop table t1; +create table t1(a int primary key, b blob, c blob) engine=aria; +set sql_mode=''; +insert into t1 values(1,repeat('a',100), repeat('b',657860)); +Warnings: +Warning 1265 Data truncated for column 'c' at row 1 +insert into t1 values(1,repeat('a',100), repeat('b',657860)); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +set sql_mode=default; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +Create table t1 (a int) engine="aria"; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +drop table t1; +CREATE TABLE t1 ( f1 DOUBLE , f2 DOUBLE , f3 DOUBLE , f4 DOUBLE , v3 DOUBLE , v4 DOUBLE , KEY ( v3 ) , KEY ( v4 ) ) engine=maria; +REPLACE t1 ( f2 , f1 ) VALUES ( f2 , 56 ) ; +INSERT t1 ( f1 , f2 , f3 , f4 ) VALUES ( 0 , f2 , 8 , f3 ) ; +INSERT t1 ( f4 , f2 ) VALUES ( 4 , 92 ) ; +DELETE FROM t1 WHERE v3 = 173 OR v4 = 9 ; +drop table t1; +CREATE TABLE t1 ( +f1 CHAR(255) BINARY , +f2 CHAR(255) BINARY NOT NULL DEFAULT '0', +f3 CHAR(255) BINARY NOT NULL , +f4 CHAR(255) BINARY NOT NULL DEFAULT '0' , +v3 CHAR(255) BINARY AS ( ( f1 NOT LIKE '%' ) ) PERSISTENT, +KEY (v3) +) ENGINE=Maria; +INSERT INTO t1 ( f1 , f2 , f3 , f4 ) SELECT f1 , f4 , f1 , f4 FROM t1; +DELETE FROM t1; +drop table t1; +create table t1 (a int not null primary key, b blob) engine=maria transactional=1; +insert into t1 values(1,repeat('a',8000)); +insert into t1 values(2,repeat('b',8000)); +insert into t1 values(3,repeat('c',8000)); +flush tables; +delete from t1 where a>1; +insert into t1 values(1,repeat('d',8000*3)); +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +flush tables; +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +repair table t1 extended; +Table Op Msg_type Msg_text +test.t1 repair status OK +drop table t1; +create table t1 (a int, b int, key (a), key(b)); +alter table t1 disable keys; +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); +alter table t1 enable keys; +select count(*) from t1; +count(*) +13 +drop table t1; +create table t1 (a int not null, b int, primary key (a), key(b)); +alter table t1 disable keys; +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); +alter table t1 enable keys; +select count(*) from t1; +count(*) +13 +drop table t1; +create table t1 (a int not null, b int, primary key (a), key(b)); +lock tables t1 write; +alter table t1 disable keys; +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); +alter table t1 enable keys; +unlock tables; +select count(*) from t1; +count(*) +13 +drop table t1; +# +# MDEV-14690: Assertion `page_link == &fake_link' failed in +# pagecache_write_part +# +CREATE TABLE t1 (a CHAR(8), b CHAR(8), c CHAR(8) NOT NULL DEFAULT '', f FLOAT, KEY(f)) ENGINE=Aria; +INSERT INTO t1 (a) VALUES ('foo'); +DELETE FROM t1 WHERE c < 'bar'; +ALTER TABLE t1 DISABLE KEYS; +INSERT INTO t1 (b) VALUES (''); +ALTER TABLE t1 ENABLE KEYS; +DROP TABLE t1; +# +# Start of 5.5 tests +# +# +# MDEV-16534 PPC64: Unexpected error with a negative values into auto-increment columns in HEAP, MyISAM, ARIA +# +CREATE TABLE t1 ( +id TINYINT NOT NULL AUTO_INCREMENT, +name CHAR(30) NOT NULL, +PRIMARY KEY (id) +) ENGINE=ARIA; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` tinyint(4) NOT NULL AUTO_INCREMENT, + `name` char(30) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 +INSERT INTO t1 (name) VALUES ('dog'); +UPDATE t1 SET id=-1 WHERE id=1; +INSERT INTO t1 (name) VALUES ('cat'); +SELECT * FROM t1; +id name +-1 dog +2 cat +DROP TABLE t1; +CREATE TABLE t1 (pk int, i2 int) ENGINE=Aria; +INSERT INTO t1 VALUES (1,2), (2,3),(3,4); +DELETE FROM tt.*, t1.* USING t1 AS tt LEFT JOIN t1 ON (tt.i2 = t1.pk); +DROP TABLE t1; +# +# End of 5.5 tests +# +CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); +INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), +(6,'0'),(7,'0'); +flush tables test.t1 for export; +insert into t1 values (8,'0'); +ERROR HY000: Table 't1' was locked with a READ lock and can't be updated +unlock tables; +drop table t1; +# +# MDEV-22284 Aria table key read crash because wrong index used +# +create table t1 ( +a int auto_increment, +b int, c int, +key(c, a), unique(b) +) engine aria +partition by hash (b); +replace into t1 values (1, 0, 0), (2, 0, 0), (0, 0, 0); +drop table t1; +# +# MDEV-28269 Assertion `save_errno' in maria_write or ER_GET_ERRNO +# +connect session1,localhost,root,,; +SET big_tables= on; +Warnings: +Warning 1287 '@@big_tables' is deprecated and will be removed in a future release +SET NAMES 'sjis'; +SELECT 'למטה' AS a UNION SELECT 'Wetter' AS a; +a +למט×? +Wetter +connection default; +disconnect session1; |