summaryrefslogtreecommitdiffstats
path: root/storage/sequence
diff options
context:
space:
mode:
Diffstat (limited to 'storage/sequence')
-rw-r--r--storage/sequence/CMakeLists.txt1
-rw-r--r--storage/sequence/mysql-test/sequence/group_by.result135
-rw-r--r--storage/sequence/mysql-test/sequence/group_by.test70
-rw-r--r--storage/sequence/mysql-test/sequence/inc.inc4
-rw-r--r--storage/sequence/mysql-test/sequence/inc.opt2
-rw-r--r--storage/sequence/mysql-test/sequence/simple.result280
-rw-r--r--storage/sequence/mysql-test/sequence/simple.test114
-rw-r--r--storage/sequence/mysql-test/sequence/suite.pm7
-rw-r--r--storage/sequence/sequence.cc543
9 files changed, 1156 insertions, 0 deletions
diff --git a/storage/sequence/CMakeLists.txt b/storage/sequence/CMakeLists.txt
new file mode 100644
index 00000000..e0b4688c
--- /dev/null
+++ b/storage/sequence/CMakeLists.txt
@@ -0,0 +1 @@
+MYSQL_ADD_PLUGIN(sequence sequence.cc STORAGE_ENGINE DEFAULT)
diff --git a/storage/sequence/mysql-test/sequence/group_by.result b/storage/sequence/mysql-test/sequence/group_by.result
new file mode 100644
index 00000000..795c32c7
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/group_by.result
@@ -0,0 +1,135 @@
+show create table seq_1_to_15_step_2;
+Table Create Table
+seq_1_to_15_step_2 CREATE TABLE `seq_1_to_15_step_2` (
+ `seq` bigint(20) unsigned NOT NULL,
+ PRIMARY KEY (`seq`)
+) ENGINE=SEQUENCE DEFAULT CHARSET=latin1
+select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+count(seq) sum(seq) 1
+8 64 1
+#
+# The engine should be able to optimize the following requests
+#
+select count(*) from seq_1_to_15_step_2;
+count(*)
+8
+explain select count(*) from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select count(seq) from seq_1_to_15_step_2;
+count(seq)
+8
+explain select count(seq) from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select sum(seq) from seq_1_to_15_step_2;
+sum(seq)
+64
+explain select sum(seq) from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select count(seq),sum(seq) from seq_1_to_15_step_2;
+count(seq) sum(seq)
+8 64
+explain select count(seq),sum(seq) from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select count(seq) as c from seq_1_to_15_step_2 having c > 5;
+c
+8
+explain select count(seq) as c from seq_1_to_15_step_2 having c > 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
+c
+explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select distinct count(*) from seq_1_to_15_step_2;
+count(*)
+8
+explain select distinct count(*) from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Storage engine handles GROUP BY
+select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
+seq count(*)
+1 8
+3 8
+5 8
+7 8
+9 8
+11 8
+13 8
+15 8
+explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY seq_1_to_15_step_2 index NULL PRIMARY 8 NULL # Using index
+1 PRIMARY <derived2> ALL NULL NULL NULL NULL # Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL # Storage engine handles GROUP BY
+create view v1 as select count(*) from seq_1_to_15_step_2;
+select * from v1;
+count(*)
+8
+drop view v1;
+#
+# The engine can't optimize the following queries
+#
+select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+count(seq) sum(seq) 1
+8 64 1
+explain select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
+explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
+1 SIMPLE t2 index NULL PRIMARY 8 NULL 8 Using index; Using join buffer (flat, BNL join)
+explain select count(*) from seq_1_to_15_step_2 where seq > 0;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 index PRIMARY PRIMARY 8 NULL 8 Using where; Using index
+explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index; Using temporary; Using filesort
+create temporary table t1 select * from seq_1_to_3;
+select count(NULL) from t1;
+count(NULL)
+0
+select count(NULL) from seq_1_to_3;
+count(NULL)
+0
+#
+# MDEV-20753: Sequence with limit 0 crashes server
+#
+select count(NULL) from seq_1_to_3 limit 0;
+count(NULL)
+# End of 10.3 tests
+#
+# MDEV-16327: Server doesn't account for engines that supports
+# OFFSET on their own.
+#
+select count(NULL) from seq_1_to_3 limit 1;
+count(NULL)
+0
+explain format=json select count(NULL) from seq_1_to_3 limit 1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "Storage engine handles GROUP BY"
+ }
+ }
+}
+select count(NULL) from seq_1_to_3 limit 1 offset 1;
+count(NULL)
+explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "table": {
+ "message": "Storage engine handles GROUP BY"
+ }
+ }
+}
+# End of 10.5 tests
diff --git a/storage/sequence/mysql-test/sequence/group_by.test b/storage/sequence/mysql-test/sequence/group_by.test
new file mode 100644
index 00000000..ca43ba1a
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/group_by.test
@@ -0,0 +1,70 @@
+--source inc.inc
+
+# Check that group by handler forks for the sequence engine.
+# The sequence engine can only optimize queries with COUNT(primary_key) or
+# SUM(primary_key) when there is no GROUP BY.
+
+show create table seq_1_to_15_step_2;
+
+# Get the correct results
+select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+
+--echo #
+--echo # The engine should be able to optimize the following requests
+--echo #
+select count(*) from seq_1_to_15_step_2;
+explain select count(*) from seq_1_to_15_step_2;
+select count(seq) from seq_1_to_15_step_2;
+explain select count(seq) from seq_1_to_15_step_2;
+select sum(seq) from seq_1_to_15_step_2;
+explain select sum(seq) from seq_1_to_15_step_2;
+select count(seq),sum(seq) from seq_1_to_15_step_2;
+explain select count(seq),sum(seq) from seq_1_to_15_step_2;
+select count(seq) as c from seq_1_to_15_step_2 having c > 5;
+explain select count(seq) as c from seq_1_to_15_step_2 having c > 5;
+select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
+explain select count(seq) as c from seq_1_to_15_step_2 having c > 1000;
+select distinct count(*) from seq_1_to_15_step_2;
+explain select distinct count(*) from seq_1_to_15_step_2;
+select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
+--replace_column 9 #
+explain select * from seq_1_to_15_step_2, (select count(*) from seq_1_to_15_step_2) as t1;
+
+create view v1 as select count(*) from seq_1_to_15_step_2;
+select * from v1;
+drop view v1;
+
+--echo #
+--echo # The engine can't optimize the following queries
+--echo #
+select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+explain select count(seq),sum(seq),1 from seq_1_to_15_step_2;
+explain select count(*) from seq_1_to_15_step_2, seq_1_to_15_step_2 as t2;
+explain select count(*) from seq_1_to_15_step_2 where seq > 0;
+explain select count(*) from seq_1_to_15_step_2 group by mod(seq,2);
+
+#
+# MDEV-9550 COUNT(NULL) returns incorrect result with sequence storage engine
+#
+create temporary table t1 select * from seq_1_to_3;
+select count(NULL) from t1;
+select count(NULL) from seq_1_to_3;
+
+--echo #
+--echo # MDEV-20753: Sequence with limit 0 crashes server
+--echo #
+select count(NULL) from seq_1_to_3 limit 0;
+
+--echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-16327: Server doesn't account for engines that supports
+--echo # OFFSET on their own.
+--echo #
+
+select count(NULL) from seq_1_to_3 limit 1;
+explain format=json select count(NULL) from seq_1_to_3 limit 1;
+select count(NULL) from seq_1_to_3 limit 1 offset 1;
+explain format=json select count(NULL) from seq_1_to_3 limit 1 offset 1;
+
+--echo # End of 10.5 tests
diff --git a/storage/sequence/mysql-test/sequence/inc.inc b/storage/sequence/mysql-test/sequence/inc.inc
new file mode 100644
index 00000000..702e90b2
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/inc.inc
@@ -0,0 +1,4 @@
+if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'sequence' AND support='YES'`)
+{
+ --skip Test requires sequence engine
+}
diff --git a/storage/sequence/mysql-test/sequence/inc.opt b/storage/sequence/mysql-test/sequence/inc.opt
new file mode 100644
index 00000000..a6db8dd1
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/inc.opt
@@ -0,0 +1,2 @@
+--plugin-load-add=$HA_SEQUENCE_SO
+--loose-sequence
diff --git a/storage/sequence/mysql-test/sequence/simple.result b/storage/sequence/mysql-test/sequence/simple.result
new file mode 100644
index 00000000..d921b80b
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/simple.result
@@ -0,0 +1,280 @@
+select * from information_schema.engines where engine='sequence';
+ENGINE SEQUENCE
+SUPPORT YES
+COMMENT Generated tables filled with sequential values
+TRANSACTIONS YES
+XA NO
+SAVEPOINTS YES
+set sql_quote_show_create=0;
+show create table seq_1_to_15_step_2;
+Table Create Table
+seq_1_to_15_step_2 CREATE TABLE seq_1_to_15_step_2 (
+ seq bigint(20) unsigned NOT NULL,
+ PRIMARY KEY (seq)
+) ENGINE=SEQUENCE DEFAULT CHARSET=latin1
+show create table seq_1_to_15_step;
+ERROR 42S02: Table 'test.seq_1_to_15_step' doesn't exist
+show create table seq_1_to_15_st;
+ERROR 42S02: Table 'test.seq_1_to_15_st' doesn't exist
+show create table seq_1_to_15;
+Table Create Table
+seq_1_to_15 CREATE TABLE seq_1_to_15 (
+ seq bigint(20) unsigned NOT NULL,
+ PRIMARY KEY (seq)
+) ENGINE=SEQUENCE DEFAULT CHARSET=latin1
+show create table seq_1_to_1;
+Table Create Table
+seq_1_to_1 CREATE TABLE seq_1_to_1 (
+ seq bigint(20) unsigned NOT NULL,
+ PRIMARY KEY (seq)
+) ENGINE=SEQUENCE DEFAULT CHARSET=latin1
+show create table seq_1_to_;
+ERROR 42S02: Table 'test.seq_1_to_' doesn't exist
+show create table seq_1_t;
+ERROR 42S02: Table 'test.seq_1_t' doesn't exist
+show create table seq_1;
+ERROR 42S02: Table 'test.seq_1' doesn't exist
+show create table seq_;
+ERROR 42S02: Table 'test.seq_' doesn't exist
+show create table se;
+ERROR 42S02: Table 'test.se' doesn't exist
+show create table seq_1_to_15_step_0;
+ERROR HY000: Got error 140 "Wrong create options" from storage engine SEQUENCE
+show create table `seq_-1_to_15`;
+ERROR 42S02: Table 'test.seq_-1_to_15' doesn't exist
+show create table `seq_1_to_+2`;
+ERROR 42S02: Table 'test.seq_1_to_+2' doesn't exist
+select * from seq_1_to_15_step_2;
+seq
+1
+3
+5
+7
+9
+11
+13
+15
+select * from seq_1_to_15;
+seq
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+select * from seq_1_to_1;
+seq
+1
+select * from seq_15_to_1;
+seq
+15
+14
+13
+12
+11
+10
+9
+8
+7
+6
+5
+4
+3
+2
+1
+select * from seq_15_to_1_step_2;
+seq
+15
+13
+11
+9
+7
+5
+3
+1
+select * from seq_1_to_15_step_12345;
+seq
+1
+select * from seq_15_to_1_step_12345;
+seq
+15
+explain select * from seq_15_to_1_step_12345;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_15_to_1_step_12345 ALL NULL NULL NULL NULL 1
+show open tables from test;
+Database Table In_use Name_locked
+test seq_15_to_1 0 0
+test seq_15_to_1_step_12345 0 0
+test seq_15_to_1_step_2 0 0
+test seq_1_to_1 0 0
+test seq_1_to_15 0 0
+test seq_1_to_15_step_12345 0 0
+test seq_1_to_15_step_2 0 0
+show tables;
+Tables_in_test
+explain select * from seq_1_to_15_step_2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 index NULL PRIMARY 8 NULL 8 Using index
+explain select * from seq_1_to_15_step_2 where seq > 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 3 Using where; Using index
+explain select * from seq_1_to_15_step_2 where seq between 4 and 9;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 3 Using where; Using index
+explain select * from seq_1_to_15_step_2 where seq between 20 and 30;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+explain select * from seq_1_to_15_step_2 where seq between 4 and 6;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index
+explain select * from seq_1_to_15_step_2 where seq between 4 and 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 range PRIMARY PRIMARY 8 NULL 1 Using where; Using index
+explain select * from seq_1_to_15_step_2 where seq between 4 and 4;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+explain select * from seq_1_to_15_step_2 where seq between 5 and 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE seq_1_to_15_step_2 const PRIMARY PRIMARY 8 const 1 Using index
+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;
+a aa b
+1 1 odd
+2 4 even
+3 9 odd
+4 16 even
+5 25 odd
+6 36 even
+7 49 odd
+8 64 even
+9 81 odd
+10 100 even
+11 121 odd
+12 144 even
+13 169 odd
+14 196 even
+15 225 odd
+16 256 even
+17 289 odd
+18 324 even
+19 361 odd
+20 400 even
+select aa, b from t1, seq_1_to_20_step_3 as seq where a=seq;
+aa b
+1 odd
+16 even
+49 odd
+100 even
+169 odd
+256 even
+361 odd
+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;
+a aa b
+1 1 odd
+2 4 even
+3 9 odd
+4 16 even
+5 25 odd
+6 36 even
+7 49 odd
+8 64 even
+9 81 odd
+10 100 even
+11 121 odd
+12 144 even
+13 169 odd
+14 196 even
+15 225 odd
+16 256 even
+17 289 odd
+18 324 even
+19 361 odd
+20 400 even
+21 441 odd
+22 484 even
+23 529 odd
+24 576 even
+25 625 odd
+26 676 even
+27 729 odd
+28 784 even
+29 841 odd
+30 900 even
+drop table t1;
+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));
+seq
+2
+3
+5
+7
+11
+13
+17
+19
+23
+29
+31
+37
+41
+43
+47
+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));
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY s1 index NULL PRIMARY 8 NULL 49 Using where; Using index
+2 DEPENDENT SUBQUERY s2 index PRIMARY PRIMARY 8 NULL 49 Using where; Using index
+select year(dt) from
+(select '1901-02-28' + interval seq year as dt from seq_0_to_99) as seqdt
+where weekday(dt) = 0;
+year(dt)
+1910
+1916
+1921
+1927
+1938
+1944
+1949
+1955
+1966
+1972
+1977
+1983
+1994
+2000
+create table t1 (a int) engine=innodb;
+reset master;
+start transaction;
+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;
+count(*)
+10
+include/show_binlog_events.inc
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Gtid # # BEGIN GTID #-#-#
+master-bin.000001 # Query # # use `test`; insert t1 select * from seq_1_to_10
+master-bin.000001 # Query # # SAVEPOINT s1
+master-bin.000001 # Xid # # COMMIT /* XID */
+drop table t1;
+drop table seq_1_to_1;
+set binlog_format=statement;
+lock table seq_1_to_2 write;
+set binlog_format=row;
+lock table seq_1_to_2 write;
diff --git a/storage/sequence/mysql-test/sequence/simple.test b/storage/sequence/mysql-test/sequence/simple.test
new file mode 100644
index 00000000..00d2464c
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/simple.test
@@ -0,0 +1,114 @@
+--source inc.inc
+--source include/have_innodb.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;
+
+#
+# MDEV-5735 Selecting from SEQUENCE table with negative number hangs server
+#
+--error ER_NO_SUCH_TABLE
+show create table `seq_-1_to_15`;
+--error ER_NO_SUCH_TABLE
+show create table `seq_1_to_+2`;
+
+# 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 > 10;
+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;
+
diff --git a/storage/sequence/mysql-test/sequence/suite.pm b/storage/sequence/mysql-test/sequence/suite.pm
new file mode 100644
index 00000000..3964415d
--- /dev/null
+++ b/storage/sequence/mysql-test/sequence/suite.pm
@@ -0,0 +1,7 @@
+package My::Suite::Sequence;
+@ISA = qw(My::Suite);
+
+sub is_default { 1 }
+
+bless { };
+
diff --git a/storage/sequence/sequence.cc b/storage/sequence/sequence.cc
new file mode 100644
index 00000000..f5a18094
--- /dev/null
+++ b/storage/sequence/sequence.cc
@@ -0,0 +1,543 @@
+/*
+ Copyright (c) 2013 Monty Program Ab
+
+ This program is free software; you can redistribute it and/or
+ modify it under the terms of the GNU General Public License
+ as published by the Free Software Foundation; version 2 of
+ the License.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA
+*/
+
+/*
+ a engine that auto-creates tables with rows filled with sequential values
+*/
+
+#include <my_config.h>
+#include <ctype.h>
+#include <mysql_version.h>
+#include <item.h>
+#include <item_sum.h>
+#include <handler.h>
+#include <table.h>
+#include <field.h>
+#include <sql_limit.h>
+
+static handlerton *sequence_hton;
+
+class Sequence_share : public Handler_share {
+public:
+ const char *name;
+ THR_LOCK lock;
+
+ ulonglong from, to, step;
+ bool reverse;
+
+ Sequence_share(const char *name_arg, ulonglong from_arg, ulonglong to_arg,
+ ulonglong step_arg, bool reverse_arg):
+ name(name_arg), from(from_arg), to(to_arg), step(step_arg),
+ reverse(reverse_arg)
+ {
+ thr_lock_init(&lock);
+ }
+ ~Sequence_share()
+ {
+ thr_lock_delete(&lock);
+ }
+};
+
+class ha_seq final : public handler
+{
+private:
+ THR_LOCK_DATA lock;
+ Sequence_share *get_share();
+ ulonglong cur;
+
+public:
+ Sequence_share *seqs;
+ ha_seq(handlerton *hton, TABLE_SHARE *table_arg)
+ : handler(hton, table_arg), seqs(0) { }
+ ulonglong table_flags() const
+ { return HA_BINLOG_ROW_CAPABLE | HA_BINLOG_STMT_CAPABLE; }
+
+ /* open/close/locking */
+ int create(const char *name, TABLE *table_arg,
+ HA_CREATE_INFO *create_info)
+ { return HA_ERR_WRONG_COMMAND; }
+
+ int open(const char *name, int mode, uint test_if_locked);
+ int close(void);
+ int delete_table(const char *name)
+ {
+ return 0;
+ }
+ THR_LOCK_DATA **store_lock(THD *, THR_LOCK_DATA **, enum thr_lock_type);
+
+ /* table scan */
+ int rnd_init(bool scan);
+ int rnd_next(unsigned char *buf);
+ void position(const uchar *record);
+ int rnd_pos(uchar *buf, uchar *pos);
+ int info(uint flag);
+
+ /* indexes */
+ ulong index_flags(uint inx, uint part, bool all_parts) const
+ { return HA_READ_NEXT | HA_READ_PREV | HA_READ_ORDER |
+ HA_READ_RANGE | HA_KEYREAD_ONLY; }
+ uint max_supported_keys() const { return 1; }
+ int index_read_map(uchar *buf, const uchar *key, key_part_map keypart_map,
+ enum ha_rkey_function find_flag);
+ int index_next(uchar *buf);
+ int index_prev(uchar *buf);
+ int index_first(uchar *buf);
+ int index_last(uchar *buf);
+ ha_rows records_in_range(uint inx, const key_range *start_key,
+ const key_range *end_key, page_range *pages);
+ double scan_time() { return (double)nvalues(); }
+ double read_time(uint index, uint ranges, ha_rows rows) { return (double)rows; }
+ double keyread_time(uint index, uint ranges, ha_rows rows) { return (double)rows; }
+
+private:
+ void set(uchar *buf);
+ ulonglong nvalues() { return (seqs->to - seqs->from)/seqs->step; }
+};
+
+THR_LOCK_DATA **ha_seq::store_lock(THD *thd, THR_LOCK_DATA **to,
+ enum thr_lock_type lock_type)
+{
+ if (lock_type != TL_IGNORE && lock.type == TL_UNLOCK)
+ lock.type= TL_WRITE_ALLOW_WRITE;
+ *to ++= &lock;
+ return to;
+}
+
+void ha_seq::set(unsigned char *buf)
+{
+ MY_BITMAP *old_map = dbug_tmp_use_all_columns(table, &table->write_set);
+ my_ptrdiff_t offset = (my_ptrdiff_t) (buf - table->record[0]);
+ Field *field = table->field[0];
+ field->move_field_offset(offset);
+ field->store(cur, true);
+ field->move_field_offset(-offset);
+ dbug_tmp_restore_column_map(&table->write_set, old_map);
+}
+
+int ha_seq::rnd_init(bool scan)
+{
+ cur= seqs->reverse ? seqs->to : seqs->from;
+ return 0;
+}
+
+int ha_seq::rnd_next(unsigned char *buf)
+{
+ if (seqs->reverse)
+ return index_prev(buf);
+ else
+ return index_next(buf);
+}
+
+void ha_seq::position(const uchar *record)
+{
+ *(ulonglong*)ref= cur;
+}
+
+int ha_seq::rnd_pos(uchar *buf, uchar *pos)
+{
+ cur= *(ulonglong*)pos;
+ return rnd_next(buf);
+}
+
+int ha_seq::info(uint flag)
+{
+ if (flag & HA_STATUS_VARIABLE)
+ stats.records = nvalues();
+ return 0;
+}
+
+int ha_seq::index_read_map(uchar *buf, const uchar *key_arg,
+ key_part_map keypart_map,
+ enum ha_rkey_function find_flag)
+{
+ ulonglong key= uint8korr(key_arg);
+ switch (find_flag) {
+ case HA_READ_AFTER_KEY:
+ key++;
+ // fall through
+ case HA_READ_KEY_OR_NEXT:
+ if (key <= seqs->from)
+ cur= seqs->from;
+ else
+ {
+ cur= (key - seqs->from + seqs->step - 1) / seqs->step * seqs->step + seqs->from;
+ if (cur >= seqs->to)
+ return HA_ERR_KEY_NOT_FOUND;
+ }
+ return index_next(buf);
+
+ case HA_READ_KEY_EXACT:
+ if ((key - seqs->from) % seqs->step != 0 || key < seqs->from || key >= seqs->to)
+ return HA_ERR_KEY_NOT_FOUND;
+ cur= key;
+ return index_next(buf);
+
+ case HA_READ_BEFORE_KEY:
+ key--;
+ // fall through
+ case HA_READ_PREFIX_LAST_OR_PREV:
+ if (key >= seqs->to)
+ cur= seqs->to;
+ else
+ {
+ if (key < seqs->from)
+ return HA_ERR_KEY_NOT_FOUND;
+ cur= (key - seqs->from) / seqs->step * seqs->step + seqs->from;
+ }
+ return index_prev(buf);
+ default: return HA_ERR_WRONG_COMMAND;
+ }
+}
+
+
+int ha_seq::index_next(uchar *buf)
+{
+ if (cur == seqs->to)
+ return HA_ERR_END_OF_FILE;
+ set(buf);
+ cur+= seqs->step;
+ return 0;
+}
+
+
+int ha_seq::index_prev(uchar *buf)
+{
+ if (cur == seqs->from)
+ return HA_ERR_END_OF_FILE;
+ cur-= seqs->step;
+ set(buf);
+ return 0;
+}
+
+
+int ha_seq::index_first(uchar *buf)
+{
+ cur= seqs->from;
+ return index_next(buf);
+}
+
+
+int ha_seq::index_last(uchar *buf)
+{
+ cur= seqs->to;
+ return index_prev(buf);
+}
+
+ha_rows ha_seq::records_in_range(uint inx, const key_range *min_key,
+ const key_range *max_key,
+ page_range *pages)
+{
+ ulonglong kmin= min_key ? uint8korr(min_key->key) : seqs->from;
+ ulonglong kmax= max_key ? uint8korr(max_key->key) : seqs->to - 1;
+ if (kmin >= seqs->to || kmax < seqs->from || kmin > kmax)
+ return 0;
+ return (kmax - seqs->from) / seqs->step -
+ (kmin - seqs->from + seqs->step - 1) / seqs->step + 1;
+}
+
+
+int ha_seq::open(const char *name, int mode, uint test_if_locked)
+{
+ if (!(seqs= get_share()))
+ return HA_ERR_OUT_OF_MEM;
+ DBUG_ASSERT(my_strcasecmp(table_alias_charset, name, seqs->name) == 0);
+
+ ref_length= sizeof(cur);
+ thr_lock_data_init(&seqs->lock,&lock,NULL);
+ return 0;
+}
+
+int ha_seq::close(void)
+{
+ return 0;
+}
+
+static handler *create_handler(handlerton *hton, TABLE_SHARE *table,
+ MEM_ROOT *mem_root)
+{
+ return new (mem_root) ha_seq(hton, table);
+}
+
+
+static bool parse_table_name(const char *name, size_t name_length,
+ ulonglong *from, ulonglong *to, ulonglong *step)
+{
+ uint n0=0, n1= 0, n2= 0;
+ *step= 1;
+
+ // the table is discovered if its name matches the pattern of seq_1_to_10 or
+ // seq_1_to_10_step_3
+ sscanf(name, "seq_%llu_to_%n%llu%n_step_%llu%n",
+ from, &n0, to, &n1, step, &n2);
+ // I consider this a bug in sscanf() - when an unsigned number
+ // is requested, -5 should *not* be accepted. But is is :(
+ // hence the additional check below:
+ return
+ n0 == 0 || !isdigit(name[4]) || !isdigit(name[n0]) || // reject negative numbers
+ (n1 != name_length && n2 != name_length);
+}
+
+
+Sequence_share *ha_seq::get_share()
+{
+ Sequence_share *tmp_share;
+ lock_shared_ha_data();
+ if (!(tmp_share= static_cast<Sequence_share*>(get_ha_share_ptr())))
+ {
+ bool reverse;
+ ulonglong from, to, step;
+
+ parse_table_name(table_share->table_name.str,
+ table_share->table_name.length, &from, &to, &step);
+
+ if ((reverse = from > to))
+ {
+ if (step > from - to)
+ to = from;
+ else
+ swap_variables(ulonglong, from, to);
+ /*
+ when keyread is allowed, optimizer will always prefer an index to a
+ table scan for our tables, and we'll never see the range reversed.
+ */
+ table_share->keys_for_keyread.clear_all();
+ }
+
+ to= (to - from) / step * step + step + from;
+
+ tmp_share= new Sequence_share(table_share->normalized_path.str, from, to, step, reverse);
+
+ if (!tmp_share)
+ goto err;
+ set_ha_share_ptr(static_cast<Handler_share*>(tmp_share));
+ }
+err:
+ unlock_shared_ha_data();
+ return tmp_share;
+}
+
+
+static int discover_table(handlerton *hton, THD *thd, TABLE_SHARE *share)
+{
+ ulonglong from, to, step;
+ if (parse_table_name(share->table_name.str, share->table_name.length,
+ &from, &to, &step))
+ return HA_ERR_NO_SUCH_TABLE;
+
+ if (step == 0)
+ return HA_WRONG_CREATE_OPTION;
+
+ const char *sql="create table seq (seq bigint unsigned primary key)";
+ return share->init_from_sql_statement_string(thd, 0, sql, strlen(sql));
+}
+
+
+static int discover_table_existence(handlerton *hton, const char *db,
+ const char *table_name)
+{
+ ulonglong from, to, step;
+ return !parse_table_name(table_name, strlen(table_name), &from, &to, &step);
+}
+
+static int dummy_commit_rollback(handlerton *, THD *, bool) { return 0; }
+
+static int dummy_savepoint(handlerton *, THD *, void *) { return 0; }
+
+/*****************************************************************************
+ Example of a simple group by handler for queries like:
+ SELECT SUM(seq) from sequence_table;
+
+ This implementation supports SUM() and COUNT() on primary key.
+*****************************************************************************/
+
+class ha_seq_group_by_handler: public group_by_handler
+{
+ Select_limit_counters limit;
+ List<Item> *fields;
+ TABLE_LIST *table_list;
+ bool first_row;
+
+public:
+ ha_seq_group_by_handler(THD *thd_arg, List<Item> *fields_arg,
+ TABLE_LIST *table_list_arg,
+ Select_limit_counters *orig_lim)
+ : group_by_handler(thd_arg, sequence_hton), limit(orig_lim[0]),
+ fields(fields_arg), table_list(table_list_arg)
+ {
+ // Reset limit because we are handling it now
+ orig_lim->set_unlimited();
+ }
+ ~ha_seq_group_by_handler() {}
+ int init_scan() { first_row= 1 ; return 0; }
+ int next_row();
+ int end_scan() { return 0; }
+};
+
+static group_by_handler *
+create_group_by_handler(THD *thd, Query *query)
+{
+ ha_seq_group_by_handler *handler;
+ Item *item;
+ List_iterator_fast<Item> it(*query->select);
+
+ /* check that only one table is used in FROM clause and no sub queries */
+ if (query->from->next_local != 0)
+ return 0;
+ /* check that there is no where clause and no group_by */
+ if (query->where != 0 || query->group_by != 0)
+ return 0;
+
+ /*
+ Check that all fields are sum(primary_key) or count(primary_key)
+ For more ways to work with the field list and sum functions, see
+ opt_sum.cc::opt_sum_query().
+ */
+ while ((item= it++))
+ {
+ Item *arg0;
+ Field *field;
+ if (item->type() != Item::SUM_FUNC_ITEM ||
+ (((Item_sum*) item)->sum_func() != Item_sum::SUM_FUNC &&
+ ((Item_sum*) item)->sum_func() != Item_sum::COUNT_FUNC))
+
+ return 0; // Not a SUM() function
+ arg0= ((Item_sum*) item)->get_arg(0);
+ if (arg0->type() != Item::FIELD_ITEM)
+ {
+ if ((((Item_sum*) item)->sum_func() == Item_sum::COUNT_FUNC) &&
+ arg0->basic_const_item())
+ continue; // Allow count(1)
+ return 0;
+ }
+ field= ((Item_field*) arg0)->field;
+ /*
+ Check that we are using the sequence table (the only table in the FROM
+ clause) and not an outer table.
+ */
+ if (field->table != query->from->table)
+ return 0;
+ /* Check that we are using a SUM() on the primary key */
+ if (strcmp(field->field_name.str, "seq"))
+ return 0;
+ }
+
+ /* Create handler and return it */
+ handler= new ha_seq_group_by_handler(thd, query->select, query->from,
+ query->limit);
+ return handler;
+}
+
+int ha_seq_group_by_handler::next_row()
+{
+ List_iterator_fast<Item> it(*fields);
+ Item_sum *item_sum;
+ Sequence_share *seqs= ((ha_seq*) table_list->table->file)->seqs;
+ DBUG_ENTER("ha_seq_group_by_handler::next_row");
+
+ /*
+ Check if this is the first call to the function. If not, we have already
+ returned all data.
+ */
+ if (!first_row ||
+ limit.get_offset_limit() > 0 ||
+ limit.get_select_limit() == 0)
+ DBUG_RETURN(HA_ERR_END_OF_FILE);
+ first_row= 0;
+
+ /* Pointer to first field in temporary table where we should store summary*/
+ Field **field_ptr= table->field;
+ ulonglong elements= (seqs->to - seqs->from + seqs->step - 1) / seqs->step;
+
+ while ((item_sum= (Item_sum*) it++))
+ {
+ Field *field= *(field_ptr++);
+ switch (item_sum->sum_func()) {
+ case Item_sum::COUNT_FUNC:
+ {
+ Item *arg0= ((Item_sum*) item_sum)->get_arg(0);
+ if (arg0->basic_const_item() && arg0->is_null())
+ field->store(0LL, 1);
+ else
+ field->store((longlong) elements, 1);
+ break;
+ }
+ case Item_sum::SUM_FUNC:
+ {
+ /* Calculate SUM(f, f+step, f+step*2 ... to) */
+ ulonglong sum;
+ sum= seqs->from * elements + seqs->step * (elements*elements-elements)/2;
+ field->store((longlong) sum, 1);
+ break;
+ }
+ default:
+ DBUG_ASSERT(0);
+ }
+ field->set_notnull();
+ }
+ DBUG_RETURN(0);
+}
+
+
+/*****************************************************************************
+ Initialize the interface between the sequence engine and MariaDB
+*****************************************************************************/
+
+static int drop_table(handlerton *hton, const char *path)
+{
+ const char *name= strrchr(path, FN_LIBCHAR)+1;
+ ulonglong from, to, step;
+ if (parse_table_name(name, strlen(name), &from, &to, &step))
+ return ENOENT;
+ return 0;
+}
+
+static int init(void *p)
+{
+ handlerton *hton= (handlerton *)p;
+ sequence_hton= hton;
+ hton->create= create_handler;
+ hton->drop_table= drop_table;
+ hton->discover_table= discover_table;
+ hton->discover_table_existence= discover_table_existence;
+ hton->commit= hton->rollback= dummy_commit_rollback;
+ hton->savepoint_set= hton->savepoint_rollback= hton->savepoint_release=
+ dummy_savepoint;
+ hton->create_group_by= create_group_by_handler;
+ return 0;
+}
+
+static struct st_mysql_storage_engine descriptor =
+{ MYSQL_HANDLERTON_INTERFACE_VERSION };
+
+maria_declare_plugin(sequence)
+{
+ MYSQL_STORAGE_ENGINE_PLUGIN,
+ &descriptor,
+ "SEQUENCE",
+ "Sergei Golubchik",
+ "Generated tables filled with sequential values",
+ PLUGIN_LICENSE_GPL,
+ init,
+ NULL,
+ 0x0100,
+ NULL,
+ NULL,
+ "0.1",
+ MariaDB_PLUGIN_MATURITY_STABLE
+}
+maria_declare_plugin_end;