diff options
Diffstat (limited to 'storage/sequence/mysql-test/sequence/simple.test')
-rw-r--r-- | storage/sequence/mysql-test/sequence/simple.test | 93 |
1 files changed, 93 insertions, 0 deletions
diff --git a/storage/sequence/mysql-test/sequence/simple.test b/storage/sequence/mysql-test/sequence/simple.test new file mode 100644 index 00000000000..9559e42fafd --- /dev/null +++ b/storage/sequence/mysql-test/sequence/simple.test @@ -0,0 +1,93 @@ +--source inc.inc +--source include/have_xtradb.inc +--source include/have_binlog_format_statement.inc + +--query_vertical select * from information_schema.engines where engine='sequence' + +set sql_quote_show_create=0; + +show create table seq_1_to_15_step_2; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_15_step; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_15_st; +show create table seq_1_to_15; +show create table seq_1_to_1; +--error ER_NO_SUCH_TABLE +show create table seq_1_to_; +--error ER_NO_SUCH_TABLE +show create table seq_1_t; +--error ER_NO_SUCH_TABLE +show create table seq_1; +--error ER_NO_SUCH_TABLE +show create table seq_; +--error ER_NO_SUCH_TABLE +show create table se; +--error ER_GET_ERRNO +show create table seq_1_to_15_step_0; + +# simple select +select * from seq_1_to_15_step_2; +select * from seq_1_to_15; +select * from seq_1_to_1; +# backwards +select * from seq_15_to_1; +select * from seq_15_to_1_step_2; + +# step > |to - from| +select * from seq_1_to_15_step_12345; +select * from seq_15_to_1_step_12345; +explain select * from seq_15_to_1_step_12345; + +--sorted_result +show open tables from test; +show tables; +# row estimates +explain select * from seq_1_to_15_step_2; +explain select * from seq_1_to_15_step_2 where seq > 4; +explain select * from seq_1_to_15_step_2 where seq between 4 and 9; +explain select * from seq_1_to_15_step_2 where seq between 20 and 30; +explain select * from seq_1_to_15_step_2 where seq between 4 and 6; +explain select * from seq_1_to_15_step_2 where seq between 4 and 5; +explain select * from seq_1_to_15_step_2 where seq between 4 and 4; +explain select * from seq_1_to_15_step_2 where seq between 5 and 5; + +# join +create table t1 (a int, aa int, b varchar(100)); +insert t1 select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_20; +select * from t1; +select aa, b from t1, seq_1_to_20_step_3 as seq where a=seq; +# adding more rows, example +insert t1 + select seq, seq*seq, if (seq % 2, 'odd', 'even') from seq_1_to_30 + where seq > (select max(a) from t1); +select * from t1; +drop table t1; + +# Prime Numbers from 2 to 50 :) +select seq from seq_2_to_50 s1 where 0 not in + (select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); +explain select seq from seq_2_to_50 s1 where 0 not in + (select s1.seq % s2.seq from seq_2_to_50 s2 where s2.seq <= sqrt(s1.seq)); + +# Years of XX-th century where 28th of February was Monday +select year(dt) from + (select '1901-02-28' + interval seq year as dt from seq_0_to_99) as seqdt + where weekday(dt) = 0; + +# transactions and XA +create table t1 (a int) engine=innodb; +reset master; +start transaction; +# No warning about "accesses nontransactional table" +insert t1 select * from seq_1_to_10; +savepoint s1; +insert t1 select * from seq_11_to_20; +rollback to savepoint s1; +commit; +select count(*) from t1; +# must show Xid event +--replace_regex /xid=[0-9]+/xid/ +show binlog events limit 2,10; +drop table t1; + |