diff options
Diffstat (limited to 'mysql-test/t/archive.test')
| -rw-r--r-- | mysql-test/t/archive.test | 275 |
1 files changed, 241 insertions, 34 deletions
diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index 14a124a96bc..b42c8446a32 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1,13 +1,16 @@ # # Simple test for archive example -# Taken from the select test +# Taken FROM the select test # -- source include/have_archive.inc +-- source include/have_binlog_format_mixed_or_statement.inc --disable_warnings -drop table if exists t1,t2; +DROP TABLE if exists t1,t2,t3,t4,t5,t6; --enable_warnings +SET storage_engine=ARCHIVE; + CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL @@ -15,9 +18,9 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (9410,9412); -select period from t1; -select * from t1; -select t1.* from t1; +select period FROM t1; +select * FROM t1; +select t1.* FROM t1; # # Create test table @@ -1243,64 +1246,64 @@ INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly','' # Search with a key # -select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; -select fld3 from t2 where fld3 like "%cultivation" ; +select t2.fld3 FROM t2 where companynr = 58 and fld3 like "%imaginable%"; +select fld3 FROM t2 where fld3 like "%cultivation" ; # # Search with a key using sorting and limit the same time # -select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; -select fld3,companynr from t2 where companynr = 58 order by fld3; +select t2.fld3,companynr FROM t2 where companynr = 57+1 order by fld3; +select fld3,companynr FROM t2 where companynr = 58 order by fld3; -select fld3 from t2 order by fld3 desc limit 10; -select fld3 from t2 order by fld3 desc limit 5; -select fld3 from t2 order by fld3 desc limit 5,5; +select fld3 FROM t2 order by fld3 desc limit 10; +select fld3 FROM t2 order by fld3 desc limit 5; +select fld3 FROM t2 order by fld3 desc limit 5,5; # # Search with a key having a constant with each unique key. # The table is read directly with read-next on fld3 # -select t2.fld3 from t2 where fld3 = 'honeysuckle'; -select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; -select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; -select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; -select t2.fld3 from t2 where fld3 LIKE 'h%le'; +select t2.fld3 FROM t2 where fld3 = 'honeysuckle'; +select t2.fld3 FROM t2 where fld3 LIKE 'honeysuckl_'; +select t2.fld3 FROM t2 where fld3 LIKE 'hon_ysuckl_'; +select t2.fld3 FROM t2 where fld3 LIKE 'honeysuckle%'; +select t2.fld3 FROM t2 where fld3 LIKE 'h%le'; -select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; -select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; +select t2.fld3 FROM t2 where fld3 LIKE 'honeysuckle_'; +select t2.fld3 FROM t2 where fld3 LIKE 'don_t_find_me_please%'; # # Test sorting with a used key (there is no need for sorting) # -select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; -select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; +select t2.fld3 FROM t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; +select fld1,fld3 FROM t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; # # Search with a key with LIKE constant # If the like starts with a certain letter key will be used. # -select fld1,fld3 from t2 where companynr = 37 and fld3 like 'f%'; -select fld3 from t2 where fld3 like "L%" and fld3 = "ok"; -select fld3 from t2 where (fld3 like "C%" and fld3 = "Chantilly"); -select fld1,fld3 from t2 where fld1 like "25050%"; -select fld1,fld3 from t2 where fld1 like "25050_"; +select fld1,fld3 FROM t2 where companynr = 37 and fld3 like 'f%'; +select fld3 FROM t2 where fld3 like "L%" and fld3 = "ok"; +select fld3 FROM t2 where (fld3 like "C%" and fld3 = "Chantilly"); +select fld1,fld3 FROM t2 where fld1 like "25050%"; +select fld1,fld3 FROM t2 where fld1 like "25050_"; # # Test rename of table # -create table t3 engine=archive select * from t2; -select * from t3 where fld3='bonfire'; -select count(*) from t3; +CREATE TABLE t3 engine=archive select * FROM t2; +select * FROM t3 where fld3='bonfire'; +select count(*) FROM t3; # Clean up path in error message --replace_result $MYSQL_TEST_DIR . /var/master-data/ / rename table t3 to t4; -select * from t4 where fld3='bonfire'; -select count(*) from t4; +select * FROM t4 where fld3='bonfire'; +select count(*) FROM t4; # End of 4.1 tests @@ -1345,6 +1348,7 @@ SELECT * FROM t2; CHECK TABLE t2; SELECT * FROM t2; + # Test INSERT DELAYED and wait until the table has one more record SELECT COUNT(auto) FROM t2; INSERT DELAYED INTO t2 VALUES (99999,011403,37,'the','delayed','insert',''); @@ -1362,16 +1366,188 @@ while (`SELECT COUNT(auto)!=1215 FROM t2`) } SELECT COUNT(auto) FROM t2; -# Adding test for alter table +# Adding test for ALTER TABLE ALTER TABLE t2 DROP COLUMN fld6; SHOW CREATE TABLE t2; -SELECT * from t2 WHERE auto != 100000; +SELECT * FROM t2 WHERE auto != 100000; + + +# Adding tests for autoincrement +# First the simple stuff + +CREATE TABLE `t5` ( +`a` int(11) NOT NULL auto_increment, +b char(12), +PRIMARY KEY (`a`) +) DEFAULT CHARSET=latin1; + +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (32, "foo"); +--error 1022 +INSERT INTO t5 VALUES (23, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +--error 1022 +INSERT INTO t5 VALUES (3, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +SELECT * FROM t5; + +SELECT * FROM t5 WHERE a=3; + +DROP TABLE t5; + +CREATE TABLE `t5` ( +`a` int(11) NOT NULL auto_increment, +b char(12), +KEY (`a`) +) DEFAULT CHARSET=latin1 AUTO_INCREMENT=5; + +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (32, "foo"); +INSERT INTO t5 VALUES (23, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (3, "foo"); +INSERT INTO t5 VALUES (NULL, "foo"); +SELECT * FROM t5; +OPTIMIZE TABLE t5; +SELECT * FROM t5; + +SELECT * FROM t5 WHERE a=32; +SELECT * FROM t5 WHERE a=3; + +DROP TABLE t5; + +CREATE TABLE `t5` ( +`a` int(11) NOT NULL auto_increment, +b blob(12), +KEY (`a`) +) DEFAULT CHARSET=latin1; + +INSERT INTO t5 VALUES (NULL, "foo"); +INSERT INTO t5 VALUES (NULL, "We the people"); +INSERT INTO t5 VALUES (NULL, "in order to form a more pefect union"); +INSERT INTO t5 VALUES (NULL, "establish justice"); +INSERT INTO t5 VALUES (NULL, "foo grok "); +INSERT INTO t5 VALUES (32, "ensure domestic tranquility"); +INSERT INTO t5 VALUES (23, "provide for the common defense"); +INSERT INTO t5 VALUES (NULL, "fo fooo"); +INSERT INTO t5 VALUES (NULL, "foo just naother bit of text"); +INSERT INTO t5 VALUES (3, "foo this is mine to think about"); +INSERT INTO t5 VALUES (NULL, "promote the general welfare"); +SELECT * FROM t5; +SELECT b FROM t5; +SELECT b FROM t5 WHERE a =3; +SELECT b FROM t5 WHERE a IN (32, 23, 5); + + +#More blob tests + +DROP TABLE t5; + +CREATE TABLE `t5` ( +`a` int(11) NOT NULL auto_increment, +b blob(12), +c blob(12), +KEY (`a`) +) DEFAULT CHARSET=latin1; + +INSERT INTO t5 VALUES (NULL, "foo", "grok this!"); +INSERT INTO t5 VALUES (NULL, "We the people", NULL); +INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty"); +INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and"); +INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL); +INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity"); +INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain"); +SELECT * FROM t5; +SELECT b FROM t5; +SELECT b FROM t5 WHERE a =3; +SELECT b FROM t5 WHERE a IN (32, 23, 5); +SELECT c FROM t5; +SELECT c FROM t5 WHERE a =3; +SELECT c FROM t5 WHERE a IN (32, 23, 5); + +# Adding this in case someone tries to add fast ALTER TABLE and doesn't tes +# it. +# Some additional tests for new, faster ALTER TABLE. Note that most of the +# whole ALTER TABLE code is being tested all around the test suite already. +# + +DROP TABLE t1; +CREATE TABLE t1 (v varchar(32)) ; +insert into t1 values ('def'),('abc'),('hij'),('3r4f'); +select * from t1; +# Fast alter, no copy performed +ALTER TABLE t1 change v v2 varchar(32); +select * from t1; +# Fast alter, no copy performed +ALTER TABLE t1 change v2 v varchar(64); +select * from t1; +# Regular ALTER TABLE +ALTER TABLE t1 add i int auto_increment not null primary key first; +select * from t1; + +# Testing cleared row key +DROP TABLE t5; + +CREATE TABLE `t5` ( +`a` int(11) NOT NULL auto_increment, +b varchar(250), +c varchar(800), +KEY (`a`) +) DEFAULT CHARSET=latin1; + +INSERT INTO t5 VALUES (NULL, "foo", "grok this!"); +INSERT INTO t5 VALUES (NULL, "We the people", NULL); +INSERT INTO t5 VALUES (NULL, "in order to form a more peefect union", "secure the blessing of liberty"); +INSERT INTO t5 VALUES (NULL, "establish justice", "to ourselves and"); +INSERT INTO t5 VALUES (32, "ensure domestic tranquility", NULL); +INSERT INTO t5 VALUES (23, "provide for the common defense", "posterity"); +INSERT INTO t5 VALUES (NULL, "promote the general welfare", "do ordain"); +INSERT INTO t5 VALUES (NULL, "abcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabcdeghijklmnopqrstuvwxyzabc", "do ordain"); + +SELECT * FROM t5; + +CREATE TABLE `t6` ( +`a` int(11) NOT NULL auto_increment, +b blob(12), +c int, +KEY (`a`) +) DEFAULT CHARSET=latin1; +SELECT * FROM t6; +INSERT INTO t6 VALUES (NULL, "foo", NULL); +INSERT INTO t6 VALUES (NULL, "We the people", 5); +INSERT INTO t6 VALUES (NULL, "in order to form a more pefect union", 9); +INSERT INTO t6 VALUES (NULL, "establish justice", NULL); +INSERT INTO t6 VALUES (NULL, NULL, NULL); +INSERT INTO t6 VALUES (32, "ensure domestic tranquility", NULL); +INSERT INTO t6 VALUES (23, "provide for the common defense", 30); +INSERT INTO t6 VALUES (NULL, "fo fooo", 70); +INSERT INTO t6 VALUES (NULL, NULL, 98); +INSERT INTO t6 VALUES (NULL, "promote the general welfare", 50); +SELECT * FROM t6; +SELECT * FROM t6 ORDER BY a; +SELECT * FROM t6 ORDER BY a DESC; + +SHOW CREATE TABLE t6; + + # # Cleanup, test is over # -drop table t1, t2, t4; +--disable_warnings +DROP TABLE t1, t2, t4, t5, t6; +--enable_warnings # # BUG#26138 - REPAIR TABLE with option USE_FRM erases all records in ARCHIVE @@ -1391,3 +1567,34 @@ insert into t1 set a=''; insert into t1 set a='a'; check table t1 extended; drop table t1; + +# +# BUG#31036 - Using order by with archive table crashes server +# + +CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE; + +let $bug31036=41; +--disable_query_log +while($bug31036) +{ + INSERT INTO t1(a) VALUES (REPEAT('a', 510)); + dec $bug31036; +} +--enable_query_log +INSERT INTO t1(a) VALUES (''); + +--disable_result_log +SELECT * FROM t1 ORDER BY a; +--enable_result_log + +DROP TABLE t1; + +# +# BUG#31833 - ORDER BY leads to wrong result when ARCHIVE, BLOB and table +# cache is full +# +CREATE TABLE t1(a INT NOT NULL AUTO_INCREMENT, b BLOB, KEY(a)) ENGINE=archive; +INSERT INTO t1 VALUES (NULL, NULL),(NULL, NULL); +FLUSH TABLE t1; +SELECT * FROM t1 ORDER BY a; |