diff options
Diffstat (limited to 'storage/sequence')
| -rw-r--r-- | storage/sequence/CMakeLists.txt | 1 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/group_by.result | 135 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/group_by.test | 70 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/inc.inc | 4 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/inc.opt | 2 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/simple.result | 280 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/simple.test | 114 | ||||
| -rw-r--r-- | storage/sequence/mysql-test/sequence/suite.pm | 7 | ||||
| -rw-r--r-- | storage/sequence/sequence.cc | 543 |
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..bcda2ba5 --- /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 COLLATE=latin1_swedish_ci +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..b8bc0ead --- /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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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 COLLATE=latin1_swedish_ci +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..b2bce932 --- /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() = default; + 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; |