diff options
Diffstat (limited to 'storage/sequence/mysql-test')
| -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 |
7 files changed, 612 insertions, 0 deletions
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 { }; + |