1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
--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
let $binlog_limit= 10;
--source include/show_binlog_events.inc
drop table t1;
#
# MDEV-4449 SEQUENCE depends on TEST_SQL_DISCOVERY for discovering tables upon DDL
#
drop table seq_1_to_1;
#
# MDEV-4451 Attempt to write-lock a SEQUENCE table with log-bin enabled causes ER_BINLOG_ROW_ENGINE_AND_STMT_ENGINE
#
set binlog_format=statement;
lock table seq_1_to_2 write;
set binlog_format=row;
lock table seq_1_to_2 write;
|
