diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2017-09-21 08:12:19 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2017-09-21 08:12:19 +0300 |
| commit | e3d44f5d62672478c40294d6e46d9f066e418cac (patch) | |
| tree | 44b4d1b9d94ced780d4f369f84eddc36791470be /mysql-test/suite | |
| parent | d176be8aeaecdb20a963fbd126929ddcd5bd98f3 (diff) | |
| parent | f70865bc9e540767d8afc3cc2cbe0e1c92a8c0be (diff) | |
| download | mariadb-git-e3d44f5d62672478c40294d6e46d9f066e418cac.tar.gz | |
Merge bb-10.2-ext into 10.3
Diffstat (limited to 'mysql-test/suite')
45 files changed, 1185 insertions, 393 deletions
diff --git a/mysql-test/suite/encryption/r/innodb-spatial-index.result b/mysql-test/suite/encryption/r/innodb-spatial-index.result index 852be0b9a73..c2a41ac4c2e 100644 --- a/mysql-test/suite/encryption/r/innodb-spatial-index.result +++ b/mysql-test/suite/encryption/r/innodb-spatial-index.result @@ -9,10 +9,14 @@ ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong creat DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB; -CREATE SPATIAL INDEX b on t1(coordinate); -ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") -ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY; ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 140 "Wrong create options") +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE; +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' +ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' +CREATE SPATIAL INDEX b on t1(coordinate); +ERROR HY000: Table storage engine 'InnoDB' does not support the create option 'ENCRYPTED' DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=DEFAULT ENGINE=INNODB; diff --git a/mysql-test/suite/encryption/t/innodb-spatial-index.test b/mysql-test/suite/encryption/t/innodb-spatial-index.test index de78461c765..6b6191c69cb 100644 --- a/mysql-test/suite/encryption/t/innodb-spatial-index.test +++ b/mysql-test/suite/encryption/t/innodb-spatial-index.test @@ -31,12 +31,17 @@ DROP TABLE t1; # CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB; +# FIXME: MDEV-13851 Encrypted table refuses some form of ALGORITHM=COPY, +# but allows rebuild by FORCE --replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ --error ER_CANT_CREATE_TABLE +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); +--error ER_ILLEGAL_HA_CREATE_OPTION CREATE SPATIAL INDEX b on t1(coordinate); ---replace_regex /#sql-[0-9a-f_]*`/#sql-temporary`/ ---error ER_CANT_CREATE_TABLE -ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); DROP TABLE t1; CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, @@ -69,4 +74,4 @@ INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)') SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0; SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; -DROP TABLE t1, t2;
\ No newline at end of file +DROP TABLE t1, t2; diff --git a/mysql-test/suite/federated/net_thd_crash-12951.result b/mysql-test/suite/federated/net_thd_crash-12951.result new file mode 100644 index 00000000000..573ac96efff --- /dev/null +++ b/mysql-test/suite/federated/net_thd_crash-12951.result @@ -0,0 +1,11 @@ +set global query_cache_size= 16*1024*1024; +set global query_cache_type= 1; +create table t1 (i int) engine=innodb; +create table t2 (i int) engine=federated +CONNECTION="mysql://root@localhost:MASTER_MYPORT/test/t1"; +select * from t2; +i +drop table t2; +drop table t1; +set global query_cache_type= default; +set global query_cache_size= default; diff --git a/mysql-test/suite/federated/net_thd_crash-12951.test b/mysql-test/suite/federated/net_thd_crash-12951.test new file mode 100644 index 00000000000..81cd826686e --- /dev/null +++ b/mysql-test/suite/federated/net_thd_crash-12951.test @@ -0,0 +1,23 @@ +# +# MDEV-12951 Server crash [mysqld got exception 0xc0000005] +# + +--source include/have_innodb.inc + +set global query_cache_size= 16*1024*1024; +set global query_cache_type= 1; + +create table t1 (i int) engine=innodb; +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create table t2 (i int) engine=federated + CONNECTION="mysql://root@localhost:$MASTER_MYPORT/test/t1"; + +select * from t2; + +source include/restart_mysqld.inc; + +drop table t2; +drop table t1; + +set global query_cache_type= default; +set global query_cache_size= default; diff --git a/mysql-test/suite/funcs_1/r/innodb_views.result b/mysql-test/suite/funcs_1/r/innodb_views.result index 8b861011dd3..5a27cb65b3d 100644 --- a/mysql-test/suite/funcs_1/r/innodb_views.result +++ b/mysql-test/suite/funcs_1/r/innodb_views.result @@ -4314,7 +4314,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7566,7 +7566,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21312,7 +21312,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/memory_views.result b/mysql-test/suite/funcs_1/r/memory_views.result index 082c8aeb5f3..a2af9082c72 100644 --- a/mysql-test/suite/funcs_1/r/memory_views.result +++ b/mysql-test/suite/funcs_1/r/memory_views.result @@ -4315,7 +4315,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -7567,7 +7567,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -21314,7 +21314,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/funcs_1/r/myisam_views-big.result b/mysql-test/suite/funcs_1/r/myisam_views-big.result index 949aaabf864..3290b3dd36a 100644 --- a/mysql-test/suite/funcs_1/r/myisam_views-big.result +++ b/mysql-test/suite/funcs_1/r/myisam_views-big.result @@ -4784,7 +4784,7 @@ CREATE VIEW v2 AS Select * from test.v1; ERROR 42S02: Table 'test.v1' doesn't exist DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' Testcase 3.3.1.25 -------------------------------------------------------------------------------- @@ -8387,7 +8387,7 @@ Call sp1() ; ERROR 42000: PROCEDURE test.sp1 does not exist Drop view if exists test.v1 ; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Drop procedure sp1 ; ERROR 42000: PROCEDURE test.sp1 does not exist @@ -22989,7 +22989,7 @@ CREATE VIEW v1 AS SELECT f1 FROM t1; DROP VIEW IF EXISTS v1; DROP VIEW IF EXISTS v1; Warnings: -Note 4089 Unknown VIEW: 'test.v1' +Note 4090 Unknown VIEW: 'test.v1' Testcase 3.3.1.68 -------------------------------------------------------------------------------- diff --git a/mysql-test/suite/innodb/r/innodb_bug46000.result b/mysql-test/suite/innodb/r/innodb_bug46000.result index 7c5ef13f3dc..86e8766fb8f 100644 --- a/mysql-test/suite/innodb/r/innodb_bug46000.result +++ b/mysql-test/suite/innodb/r/innodb_bug46000.result @@ -6,7 +6,7 @@ show warnings; Level Code Message Warning 1280 Cannot Create Index with name 'GEN_CLUST_INDEX'. The name is reserved for the system default primary index. Error 1280 Incorrect index name 'GEN_CLUST_INDEX' -Warning 1030 Got error 124 "Wrong index given to function" from storage engine InnoDB +Warning 1030 Got error 124 "Wrong index given to a function" from storage engine InnoDB create table bug46000(id int) engine=innodb; create index GEN_CLUST_INDEX on bug46000(id); ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX' diff --git a/mysql-test/suite/innodb/r/row_format_redundant.result b/mysql-test/suite/innodb/r/row_format_redundant.result index 2bc8769092f..a2d5bbef8df 100644 --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@ -67,7 +67,7 @@ SELECT COUNT(*) FROM t3; COUNT(*) 0 RENAME TABLE t1 TO tee_one; -ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in engine") +ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in the storage engine") DROP TABLE t1; Warnings: Warning 1932 Table 'test.t1' doesn't exist in engine diff --git a/mysql-test/suite/innodb/t/log_data_file_size.test b/mysql-test/suite/innodb/t/log_data_file_size.test index 7928fc45520..f01e013ddfa 100644 --- a/mysql-test/suite/innodb/t/log_data_file_size.test +++ b/mysql-test/suite/innodb/t/log_data_file_size.test @@ -64,6 +64,9 @@ truncate(FILE, $page_size * 4); close FILE; open(FILE, "+<", "$ENV{'MYSQLD_DATADIR'}test/ibd4f.ibd") or die; truncate(FILE, $page_size * 4 + 1234); +# Work around MDEV-12699 and ensure that the truncated page is all-zero. +sysseek(FILE, $page_size * 4, 0); +syswrite(FILE, chr(0) x 1234); close FILE; open(FILE, "+<", "$ENV{'MYSQLD_DATADIR'}test/ibd5.ibd") or die; truncate(FILE, $page_size * 5); diff --git a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result index abc3c5b5f05..17f1f7e1b06 100644 --- a/mysql-test/suite/innodb_gis/r/alter_spatial_index.result +++ b/mysql-test/suite/innodb_gis/r/alter_spatial_index.result @@ -47,10 +47,20 @@ VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140, ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'), ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) @@ -140,8 +150,14 @@ c1 ST_Astext(c2) ST_Astext(c4) 1 POINT(1000 1000) POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -166,8 +182,14 @@ tab 1 idx4 1 c44 A # 32 NULL SPATIAL testing spatial index on Polygon tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c44 A # 10 NULL BTREE ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -210,7 +232,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab DROP PRIMARY KEY; +affected rows: 4 +info: Records: 4 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c2) ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1); c1 ST_Astext(c2) ST_Astext(c4) @@ -254,7 +280,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR c1 ST_Astext(c2) ST_Astext(c4) INSERT INTO tab SELECT * FROM tab1; ALTER TABLE tab DROP PRIMARY KEY; +affected rows: 1 +info: Records: 1 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP INDEX idx2; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR CREATE TEMPORARY TABLE temp_tab AS SELECT * FROM tab where c1 = c2; ERROR HY000: Illegal parameter data types int and geometry for operation '=' @@ -309,8 +339,14 @@ tab 1 idx5 1 c5 A # 32 NULL SPATIAL testing spatial index on Geometry tab 1 idx6 1 c4 A # 10 NULL BTREE DELETE FROM tab; ALTER TABLE tab ADD PRIMARY KEY(c2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -342,9 +378,17 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'), ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); DELETE FROM tab; ALTER TABLE tab DROP PRIMARY KEY ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab DROP KEY const_1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD PRIMARY KEY(c5(10)); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -422,13 +466,25 @@ ANALYZE TABLE tab; Table Op Msg_type Msg_text test.tab analyze status OK ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on Polygon'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; -ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Do not support online operation on table with GIS index. Try ALGORITHM=COPY +ERROR 0A000: LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -462,6 +518,8 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; c1 ST_Astext(c2) ST_Astext(c4) ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -510,8 +568,14 @@ test.tab analyze status OK SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'); SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)'); ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; +affected rows: 8 +info: Records: 8 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; +affected rows: 8 +info: Records: 8 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE tab; Table Create Table tab CREATE TABLE `tab` ( @@ -588,7 +652,11 @@ DROP TABLE tab; CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( @@ -616,7 +684,11 @@ DROP table child,parent; CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB; ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 SHOW CREATE TABLE parent; Table Create Table parent CREATE TABLE `parent` ( @@ -644,11 +716,12 @@ create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=inplace; ERROR 42000: All parts of a SPATIAL index must be NOT NULL -alter table t1 add b geometry, algorithm=inplace; -update t1 set b = st_geomfromtext('point(0 0)'); -alter table t1 add spatial index(b), algorithm=inplace; -ERROR 42000: All parts of a SPATIAL index must be NOT NULL -delete from t1; +alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=inplace; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 DROP table t1; create table t1 (c1 int) engine=innodb; insert into t1 values(NULL); @@ -656,11 +729,8 @@ alter table t1 add b geometry, add spatial index(b), algorithm=copy; ERROR 42000: All parts of a SPATIAL index must be NOT NULL alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field -update t1 set b = st_geomfromtext('point(0 0)'); -ERROR 42S22: Unknown column 'b' in 'field list' -alter table t1 add spatial index(b), algorithm=copy; -ERROR 42000: Key column 'b' doesn't exist in table -delete from t1; +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=copy; DROP table t1; # # BUG#20111575 ALTER TABLE...ADD SPATIAL INDEX...LOCK NONE IS REFUSED diff --git a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test index 48052da021f..2b834ac69a6 100644 --- a/mysql-test/suite/innodb_gis/t/alter_spatial_index.test +++ b/mysql-test/suite/innodb_gis/t/alter_spatial_index.test @@ -86,6 +86,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))')); +--enable_info ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); @@ -95,7 +96,7 @@ ALTER TABLE tab ADD SPATIAL INDEX idx4(c4 ASC) COMMENT 'testing spatial index on ALTER TABLE tab ADD SPATIAL KEY idx5(c5 ASC) COMMENT 'testing spatial index on Geometry'; ALTER TABLE tab ADD INDEX idx6(c4(10)) USING BTREE; - +--disable_info # Test the MBRContains SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'); @@ -195,22 +196,26 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1); DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1); +--enable_info ALTER TABLE tab CHANGE COLUMN c2 c22 POINT NOT NULL; ALTER TABLE tab CHANGE COLUMN c3 c33 LINESTRING NOT NULL; ALTER TABLE tab CHANGE COLUMN c4 c44 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; --replace_column 7 # SHOW INDEX FROM tab; +--enable_info ALTER TABLE tab CHANGE COLUMN c22 c2 POINT NOT NULL; ALTER TABLE tab CHANGE COLUMN c33 c3 LINESTRING NOT NULL; ALTER TABLE tab CHANGE COLUMN c44 c4 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -234,9 +239,11 @@ DELETE FROM tab WHERE MBREquals(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1); +--enable_info ALTER TABLE tab DROP PRIMARY KEY; ALTER TABLE tab ADD PRIMARY KEY(c2) ; +--disable_info SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))'); @@ -298,9 +305,11 @@ SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) OR INSERT INTO tab SELECT * FROM tab1; +--enable_info ALTER TABLE tab DROP PRIMARY KEY; ALTER TABLE tab DROP INDEX idx2; +--disable_info # Check spatial index on temp tables --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION @@ -346,11 +355,13 @@ SHOW INDEX FROM tab; DELETE FROM tab; +--enable_info ALTER TABLE tab ADD PRIMARY KEY(c2); CREATE SPATIAL INDEX idx2 ON tab(c2 ASC); ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c2); +--disable_info SHOW CREATE TABLE tab; @@ -366,6 +377,7 @@ ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))')); DELETE FROM tab; +--enable_info ALTER TABLE tab DROP PRIMARY KEY ; ALTER TABLE tab DROP KEY const_1; @@ -373,6 +385,7 @@ ALTER TABLE tab DROP KEY const_1; ALTER TABLE tab ADD PRIMARY KEY(c5(10)); ALTER TABLE tab ADD CONSTRAINT const_1 UNIQUE(c5(10)); +--disable_info SHOW CREATE TABLE tab; @@ -449,6 +462,7 @@ ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 ANALYZE TABLE tab; +--enable_info ALTER TABLE tab ADD SPATIAL INDEX idx2(c2 ASC); ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC); @@ -470,6 +484,7 @@ ALTER TABLE tab MODIFY COLUMN c2 GEOMETRY NOT NULL; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tab add COLUMN c8 POINT NOT NULL, ALGORITHM = INPLACE, LOCK=NONE; +--disable_info SHOW CREATE TABLE tab; @@ -487,10 +502,12 @@ DELETE FROM tab WHERE MBRContains(tab.c4, @g1); SELECT c1,ST_Astext(c2),ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1; +--enable_info # --error ER_CANT_CREATE_GEOMETRY_OBJECT # ALTER TABLE tab MODIFY COLUMN c2 POLYGON NOT NULL; ALTER TABLE tab MODIFY COLUMN c4 GEOMETRY NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -524,11 +541,13 @@ SET @g1 = ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 SET @g2 = ST_GeomFromText('LINESTRING(1 1,2 2,3 3)'); # When Point type data exist in the column allow DDL operation +--enable_info ALTER TABLE tab MODIFY COLUMN c2 POINT NOT NULL; ALTER TABLE tab MODIFY COLUMN c3 LINESTRING NOT NULL; ALTER TABLE tab MODIFY COLUMN c4 POLYGON NOT NULL; +--disable_info SHOW CREATE TABLE tab; @@ -575,6 +594,7 @@ DELETE FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); SELECT c1,ST_Astext(c2),ST_AsText(c3),ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) OR ST_Touches(tab.c3,@g2); +--enable_info # --error ER_SPATIAL_MUST_HAVE_GEOM_COL --error ER_WRONG_ARGUMENTS ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; @@ -582,6 +602,7 @@ ALTER TABLE tab MODIFY COLUMN c4 INT NOT NULL; # --error ER_SPATIAL_MUST_HAVE_GEOM_COL --error ER_WRONG_ARGUMENTS ALTER TABLE tab MODIFY COLUMN c4 BLOB NOT NULL; +--disable_info # Test InnoDB to Myisam to InnoDB ALTER TABLE tab ENGINE Myisam; @@ -619,9 +640,11 @@ CREATE TABLE parent (id POINT, PRIMARY KEY(id)) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id POINT NOT NULL) ENGINE=InnoDB; +--enable_info ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC); ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +--disable_info SHOW CREATE TABLE parent; @@ -650,9 +673,11 @@ CREATE TABLE parent (id GEOMETRY, PRIMARY KEY(id(10))) ENGINE=InnoDB; CREATE TABLE child (id GEOMETRY NOT NULL, parent_id GEOMETRY NOT NULL) ENGINE=InnoDB; +--enable_info ALTER TABLE parent ADD SPATIAL INDEX idx1(id ASC) ; ALTER TABLE child ADD SPATIAL INDEX idx2(parent_id ASC); +--disable_info SHOW CREATE TABLE parent; @@ -682,29 +707,13 @@ alter table t1 add b geometry, add spatial index(b), algorithm=inplace; # Add spatial index fail, since there's invalid geo data. # The case has to be commented because it no longer fails and following cases # don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -# alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; - -# Add a geometry column. -alter table t1 add b geometry, algorithm=inplace; - -# Add spatial index fail, since there's a NULL or invalid geo data. -# The case has to be commented because it no longer fails and following cases -# don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -#alter table t1 add spatial index(b), algorithm=inplace; - -# Update invalide geo data to point(0 0). -update t1 set b = st_geomfromtext('point(0 0)'); - -# Add spatial index success. ---error ER_SPATIAL_CANT_HAVE_NULL -alter table t1 add spatial index(b), algorithm=inplace; - -# Delete rows. -delete from t1; +--error ER_CANT_CREATE_GEOMETRY_OBJECT + alter table t1 add b geometry not null, add spatial index(b), algorithm=inplace; -#cleanup +--enable_info +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=inplace; +--disable_info DROP table t1; # Check add spatial index when table already has rows (copy). @@ -716,32 +725,11 @@ insert into t1 values(NULL); alter table t1 add b geometry, add spatial index(b), algorithm=copy; # Add spatial index fail, since there's a NULL or invalid geo data. -# --error ER_INVALID_USE_OF_NULL --error ER_CANT_CREATE_GEOMETRY_OBJECT alter table t1 add b geometry not null, add spatial index(b), algorithm=copy; -# Add a geometry column. -# --error ER_INVALID_USE_OF_NULL -# alter table t1 add b geometry not null, algorithm=copy; - -# Add spatial index. -# The case has to be commented because it no longer fails and following cases -# don't expect the effect of such a statement. -#--error ER_CANT_CREATE_GEOMETRY_OBJECT -#alter table t1 add spatial index(b), algorithm=copy; - -# Update invalide geo data to point(0 0). ---error ER_BAD_FIELD_ERROR -update t1 set b = st_geomfromtext('point(0 0)'); - -# Add spatial index success. ---error ER_KEY_COLUMN_DOES_NOT_EXITS -alter table t1 add spatial index(b), algorithm=copy; - -# Delete rows. -delete from t1; - -#cleanup +alter table t1 add b geometry not null default st_geomfromtext('POINT(0 0)'), +add spatial index(b), algorithm=copy; DROP table t1; --echo # diff --git a/mysql-test/suite/innodb_zip/r/create_options.result b/mysql-test/suite/innodb_zip/r/create_options.result index 9b9283d5df1..b217388b6dc 100644 --- a/mysql-test/suite/innodb_zip/r/create_options.result +++ b/mysql-test/suite/innodb_zip/r/create_options.result @@ -261,14 +261,14 @@ Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB # can be set to default values during strict mode. CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ALTER TABLE t1 ADD COLUMN f1 INT; +SHOW WARNINGS; +Level Code Message SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `i` int(11) DEFAULT NULL, `f1` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 -SHOW WARNINGS; -Level Code Message ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; Level Code Message diff --git a/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result b/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result index 1a02c1a711a..d766ecceaac 100644 --- a/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result +++ b/mysql-test/suite/innodb_zip/r/wl6501_crash_4.result @@ -461,10 +461,6 @@ i f c drop table t; set global innodb_file_per_table = 1; call mtr.add_suppression("does not exist in the InnoDB internal"); -Warnings: -Error 145 Table './mtr/test_suppressions' is marked as crashed and should be repaired -Error 1194 Table 'test_suppressions' is marked as crashed and should be repaired -Error 1034 1 client is using or hasn't closed the table properly set global innodb_file_per_table = on; "1. Hit crash point on completing drop of all indexes before creation" " of index is commenced." @@ -520,3 +516,4 @@ check table t; Table Op Msg_type Msg_text test.t check Error Table 'test.t' doesn't exist test.t check status Operation failed +set global innodb_file_per_table = 1; diff --git a/mysql-test/suite/innodb_zip/t/create_options.test b/mysql-test/suite/innodb_zip/t/create_options.test index f91cd7d42dd..fce64060df3 100644 --- a/mysql-test/suite/innodb_zip/t/create_options.test +++ b/mysql-test/suite/innodb_zip/t/create_options.test @@ -214,8 +214,8 @@ SHOW WARNINGS; --echo # can be set to default values during strict mode. CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; ALTER TABLE t1 ADD COLUMN f1 INT; -SHOW CREATE TABLE t1; SHOW WARNINGS; +SHOW CREATE TABLE t1; ALTER TABLE t1 ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0; SHOW WARNINGS; ALTER TABLE t1 ADD COLUMN f2 INT; diff --git a/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc b/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc index 5ffc5438a3e..7d6441b15ad 100644 --- a/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc +++ b/mysql-test/suite/parts/inc/part_exch_drop_tabs.inc @@ -1,4 +1,3 @@ ---disable_warnings DROP TABLE IF EXISTS t_10; DROP TABLE IF EXISTS t_100; DROP TABLE IF EXISTS t_1000; @@ -11,5 +10,3 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; ---enable_warnings - diff --git a/mysql-test/suite/parts/inc/part_exch_tabs.inc b/mysql-test/suite/parts/inc/part_exch_tabs.inc index 378e0c2278d..482c9d378e7 100644 --- a/mysql-test/suite/parts/inc/part_exch_tabs.inc +++ b/mysql-test/suite/parts/inc/part_exch_tabs.inc @@ -1,51 +1,27 @@ ---disable_warnings -DROP TABLE IF EXISTS t_10; -DROP TABLE IF EXISTS t_100; -DROP TABLE IF EXISTS t_1000; -DROP TABLE IF EXISTS tp; -DROP TABLE IF EXISTS tsp; -DROP TABLE IF EXISTS t_empty; -DROP TABLE IF EXISTS t_null; ---enable_warnings +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_10 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_100 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_1000 (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_empty (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_table; -eval CREATE TABLE t_null (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_table; - -eval CREATE TABLE tp (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_part +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_part PARTITION BY RANGE (a) (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory, PARTITION p1 VALUES LESS THAN (100) $p_data_directory $p_index_directory, PARTITION p2 VALUES LESS THAN (1000) $p_data_directory $p_index_directory); -eval CREATE TABLE tsp (a INT, - b VARCHAR(55), - PRIMARY KEY (a)) $data_directory $index_directory -ENGINE = $engine_subpart +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) $data_directory $index_directory ENGINE = $engine_subpart PARTITION BY RANGE (a) SUBPARTITION BY HASH(a) (PARTITION p0 VALUES LESS THAN (10) $p_data_directory $p_index_directory @@ -53,8 +29,7 @@ SUBPARTITION BY HASH(a) SUBPARTITION sp01, SUBPARTITION sp02, SUBPARTITION sp03, - SUBPARTITION sp04), - PARTITION p1 VALUES LESS THAN (100) + SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) (SUBPARTITION sp10 $p_data_directory $p_index_directory, SUBPARTITION sp11 $p_data_directory $p_index_directory, SUBPARTITION sp12 $p_data_directory $p_index_directory, @@ -99,41 +74,13 @@ INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four") INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); -eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; -eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; -eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; -eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; -eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) - ENGINE = $engine_table $data_directory $index_directory - AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; - -SHOW CREATE TABLE t_10; -SHOW CREATE TABLE t_100; -SHOW CREATE TABLE t_1000; -SHOW CREATE TABLE tp; -SHOW CREATE TABLE tsp; - ---sorted_result -SELECT * FROM t_10; ---sorted_result -SELECT * FROM t_100; ---sorted_result -SELECT * FROM t_1000; ---sorted_result -SELECT * FROM tp; ---sorted_result -SELECT * FROM tp WHERE a< 10; ---sorted_result -SELECT * FROM tp WHERE a BETWEEN 11 AND 100; ---sorted_result -SELECT * FROM tp WHERE a BETWEEN 101 AND 200; ---sorted_result -SELECT * FROM tsp; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR; +eval CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = $engine_table $data_directory $index_directory AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; diff --git a/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result b/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result index 9ff4afcfe35..a2a58c22c42 100644 --- a/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result +++ b/mysql-test/suite/parts/r/partition_exch_myisam_innodb.result @@ -1,3 +1,58 @@ +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB DROP TABLE IF EXISTS t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_14.result b/mysql-test/suite/parts/r/partition_exch_qa_14.result index f6866727184..1420982436a 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_14.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_14.result @@ -1,4 +1,198 @@ -use test; +# === Data/Index directories are identical +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +DROP TABLE IF EXISTS t_10; +DROP TABLE IF EXISTS t_100; +DROP TABLE IF EXISTS t_1000; +DROP TABLE IF EXISTS tp; +DROP TABLE IF EXISTS tsp; +DROP TABLE IF EXISTS tsp_00; +DROP TABLE IF EXISTS tsp_01; +DROP TABLE IF EXISTS tsp_02; +DROP TABLE IF EXISTS tsp_03; +DROP TABLE IF EXISTS tsp_04; +DROP TABLE IF EXISTS t_empty; +DROP TABLE IF EXISTS t_null; +# === partition has directories, the table does not +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; +ERROR HY000: Tables have different definitions +DROP TABLE IF EXISTS t_10; +DROP TABLE IF EXISTS t_100; +DROP TABLE IF EXISTS t_1000; +DROP TABLE IF EXISTS tp; +DROP TABLE IF EXISTS tsp; +DROP TABLE IF EXISTS tsp_00; +DROP TABLE IF EXISTS tsp_01; +DROP TABLE IF EXISTS tsp_02; +DROP TABLE IF EXISTS tsp_03; +DROP TABLE IF EXISTS tsp_04; +DROP TABLE IF EXISTS t_empty; +DROP TABLE IF EXISTS t_null; +# === the table has directories, partition does not +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; @@ -13,7 +207,62 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; -use test; +# === data directory differs +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' INDEX DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; @@ -28,7 +277,62 @@ DROP TABLE IF EXISTS tsp_03; DROP TABLE IF EXISTS tsp_04; DROP TABLE IF EXISTS t_empty; DROP TABLE IF EXISTS t_null; -use test; +# === index directory differs +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +PARTITION p1 VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp11 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp12 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp13 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir', +SUBPARTITION sp14 DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir'), +PARTITION p2 VALUES LESS THAN (1000) DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-data-dir' DATA DIRECTORY = 'MYSQLTEST_VARDIR/mysql-test-idx-dir' AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: Tables have different definitions DROP TABLE IF EXISTS t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_15.result b/mysql-test/suite/parts/r/partition_exch_qa_15.result index 87671a74253..355cf43d886 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_15.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_15.result @@ -1,10 +1,65 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = InnoDB AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; CREATE TABLE t_11 (a INT, b VARCHAR(55), FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE) ENGINE= InnoDB; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Table to exchange with partition has foreign key references: 't_11' -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; DROP TABLE IF EXISTS t_10; DROP TABLE IF EXISTS t_100; DROP TABLE IF EXISTS t_1000; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_2.result b/mysql-test/suite/parts/r/partition_exch_qa_2.result index ea4983db3dc..956cb0af695 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_2.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_2.result @@ -1,4 +1,59 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; SELECT * FROM t_10; a b 1 One @@ -102,20 +157,20 @@ a b CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Tables have different definitions -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= MYISAM SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Tables have different definitions -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= MYISAM PARTITION BY KEY() AS SELECT * FROM t_10; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ERROR HY000: Table to exchange with partition is partitioned: 't_11' -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp; ERROR HY000: Table to exchange with partition is partitioned: 'tsp' ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_3.result b/mysql-test/suite/parts/r/partition_exch_qa_3.result index 9f4043a055a..791757c95f2 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_3.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_3.result @@ -1,4 +1,159 @@ use test; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = InnoDB +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; +SELECT * FROM t_10; +a b +1 One +3 Three +5 Five +9 Nine +SELECT * FROM t_100; +a b +11 Eleven +13 Thirdteen +15 Fifeteen +19 Nineteen +91 Ninety-one +93 Ninety-three +95 Ninety-five +99 Ninety-nine +SELECT * FROM t_1000; +a b +111 Hundred elven +113 Hundred thirdteen +115 Hundred fiveteen +119 Hundred nineteen +131 Hundred thirty-one +133 Hundred thirty-three +135 Hundred thirty-five +139 Hundred thirty-nine +151 Hundred fifty-one +153 Hundred fifty-three +155 Hundred fity-five +159 Hundred fifty-nine +191 Hundred ninety-one +193 Hundred ninety-three +195 Hundred ninety-five +199 Hundred ninety-nine +SELECT * FROM tp; +a b +112 Hundred twelve +114 Hundred fourteen +116 Hundred sixteen +118 Hundred eightteen +12 twelve +122 Hundred twenty-two +124 Hundred twenty-four +126 Hundred twenty-six +128 Hundred twenty-eight +14 Fourteen +16 Sixteen +162 Hundred sixty-two +164 Hundred sixty-four +166 Hundred sixty-six +168 Hundred sixty-eight +18 Eightteen +182 Hundred eighty-two +184 Hundred eighty-four +186 Hundred eighty-six +188 Hundred eighty-eight +2 Two +4 Four +6 Six +8 Eight +SELECT * FROM tsp; +a b +112 Hundred twelve +114 Hundred fourteen +116 Hundred sixteen +118 Hundred eightteen +12 twelve +122 Hundred twenty-two +124 Hundred twenty-four +126 Hundred twenty-six +128 Hundred twenty-eight +14 Fourteen +16 Sixteen +162 Hundred sixty-two +164 Hundred sixty-four +166 Hundred sixty-six +168 Hundred sixty-eight +18 Eightteen +182 Hundred eight-two +184 Hundred eighty-four +186 Hundred eighty-six +188 Hundred eighty-eight +2 Two +4 Four +6 Six +8 Eight +SELECT * FROM tsp_00; +a b +5 Five +SELECT * FROM tsp_01; +a b +1 One +SELECT * FROM tsp_02; +a b +SELECT * FROM tsp_03; +a b +3 Three +SELECT * FROM tsp_04; +a b +9 Nine ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ERROR HY000: The mix of handlers in the partitions is not allowed in this version of MariaDB ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; diff --git a/mysql-test/suite/parts/r/partition_exch_qa_6.result b/mysql-test/suite/parts/r/partition_exch_qa_6.result index bef277f695a..6de40ee614a 100644 --- a/mysql-test/suite/parts/r/partition_exch_qa_6.result +++ b/mysql-test/suite/parts/r/partition_exch_qa_6.result @@ -1,8 +1,62 @@ CREATE USER test2@localhost; +CREATE TABLE t_10 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_100 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_1000 (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_empty (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE t_null (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM; +CREATE TABLE tp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +(PARTITION p0 VALUES LESS THAN (10) , +PARTITION p1 VALUES LESS THAN (100) , +PARTITION p2 VALUES LESS THAN (1000) ); +CREATE TABLE tsp (a INT, b VARCHAR(55), PRIMARY KEY (a)) ENGINE = MYISAM +PARTITION BY RANGE (a) +SUBPARTITION BY HASH(a) +(PARTITION p0 VALUES LESS THAN (10) +(SUBPARTITION sp00, +SUBPARTITION sp01, +SUBPARTITION sp02, +SUBPARTITION sp03, +SUBPARTITION sp04), PARTITION p1 VALUES LESS THAN (100) +(SUBPARTITION sp10 , +SUBPARTITION sp11 , +SUBPARTITION sp12 , +SUBPARTITION sp13 , +SUBPARTITION sp14 ), +PARTITION p2 VALUES LESS THAN (1000) +(SUBPARTITION sp20, +SUBPARTITION sp21, +SUBPARTITION sp22, +SUBPARTITION sp23, +SUBPARTITION sp24)); +INSERT INTO t_10 VALUES (1, "One"), (3, "Three"), (5, "Five"), (9, "Nine"); +INSERT INTO t_100 VALUES (11, "Eleven"), (13, "Thirdteen"), (15, "Fifeteen"), (19, "Nineteen"); +INSERT INTO t_100 VALUES (91, "Ninety-one"), (93, "Ninety-three"), (95, "Ninety-five"), (99, "Ninety-nine"); +INSERT INTO t_1000 VALUES (111, "Hundred elven"), (113, "Hundred thirdteen"), (115, "Hundred fiveteen"), (119, "Hundred nineteen"); +INSERT INTO t_1000 VALUES (131, "Hundred thirty-one"), (133, "Hundred thirty-three"), (135, "Hundred thirty-five"), (139, "Hundred thirty-nine"); +INSERT INTO t_1000 VALUES (151, "Hundred fifty-one"), (153, "Hundred fifty-three"), (155, "Hundred fity-five"), (159, "Hundred fifty-nine"); +INSERT INTO t_1000 VALUES (191, "Hundred ninety-one"), (193, "Hundred ninety-three"), (195, "Hundred ninety-five"), (199, "Hundred ninety-nine"); +INSERT INTO t_null VALUES (1, "NULL"); +INSERT INTO tp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tp VALUES (182, "Hundred eighty-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +INSERT INTO tsp VALUES (2, "Two"), (4, "Four"), (6, "Six"), (8, "Eight"); +INSERT INTO tsp VALUES (12, "twelve"), (14, "Fourteen"), (16, "Sixteen"), (18, "Eightteen"); +INSERT INTO tsp VALUES (112, "Hundred twelve"), (114, "Hundred fourteen"), (116, "Hundred sixteen"), (118, "Hundred eightteen"); +INSERT INTO tsp VALUES (122, "Hundred twenty-two"), (124, "Hundred twenty-four"), (126, "Hundred twenty-six"), (128, "Hundred twenty-eight"); +INSERT INTO tsp VALUES (162, "Hundred sixty-two"), (164, "Hundred sixty-four"), (166, "Hundred sixty-six"), (168, "Hundred sixty-eight"); +INSERT INTO tsp VALUES (182, "Hundred eight-two"), (184, "Hundred eighty-four"), (186, "Hundred eighty-six"), (188, "Hundred eighty-eight"); +CREATE TABLE tsp_01(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 1; +CREATE TABLE tsp_02(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 2; +CREATE TABLE tsp_03(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 3; +CREATE TABLE tsp_04(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 4; +CREATE TABLE tsp_00(a INT,b VARCHAR(55),PRIMARY KEY (a)) ENGINE = MYISAM AS SELECT a, b FROM t_10 WHERE MOD(a,5)= 0; GRANT USAGE ON *.* TO test2@localhost; GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -USE test; SHOW GRANTS FOR CURRENT_USER; Grants for test2@localhost GRANT USAGE ON *.* TO 'test2'@'localhost' @@ -81,7 +135,6 @@ connection default; GRANT CREATE ON test.* TO test2@localhost; REVOKE DROP ON test.* FROM test2@localhost; connect test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK; -USE test; SHOW GRANTS FOR CURRENT_USER; Grants for test2@localhost GRANT USAGE ON *.* TO 'test2'@'localhost' diff --git a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test index fa956f19aec..c625ad93775 100644 --- a/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test +++ b/mysql-test/suite/parts/t/partition_exch_myisam_innodb.test @@ -1,21 +1,17 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_innodb.inc ---source include/have_partition.inc +source include/have_innodb.inc; +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= InnoDB; let $engine_subpart= InnoDB; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; ---error 1497 +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_14.test b/mysql-test/suite/parts/t/partition_exch_qa_14.test index 7c6699a0a72..8d9f201f1db 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_14.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_14.test @@ -1,94 +1,66 @@ # Author: Horst Hunger # Created: 2010-07-13 ---source include/not_windows.inc ---source include/have_partition.inc ---source include/have_symlink.inc +source include/not_windows.inc; +source include/have_partition.inc; +source include/have_symlink.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; let $engine_subpart= MYISAM; - -# DATA DIRECTORY -# Make directory for partition data -let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; -let $data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index -let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; -let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - -# DATA DIRECTORY -# Make directory for partition data let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - -# DATA DIRECTORY -# Make directory for partition data -let $data_dir_path= $MYSQLTEST_VARDIR/mysql-test-data-dir; ---mkdir $data_dir_path -let $data_directory= DATA DIRECTORY = '$data_dir_path'; - -# INDEX DIRECTORY -# Make directory for partition index -let $idx_dir_path= $MYSQLTEST_VARDIR/mysql-test-idx-dir; ---mkdir $idx_dir_path -let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; - -use test; - ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log - ---error ER_TABLES_DIFFERENT_METADATA -ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; - ---source suite/parts/inc/part_exch_drop_tabs.inc ---rmdir $data_dir_path ---rmdir $idx_dir_path - +mkdir $data_dir_path; +mkdir $idx_dir_path; + +echo # === Data/Index directories are identical; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === partition has directories, the table does not; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= ; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= ; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === the table has directories, partition does not; + let $p_data_directory= ; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= ; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === data directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$idx_dir_path'; + let $p_index_directory= INDEX DIRECTORY = '$idx_dir_path'; + let $index_directory= INDEX DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +echo # === index directory differs; + let $p_data_directory= DATA DIRECTORY = '$data_dir_path'; + let $data_directory= DATA DIRECTORY = '$data_dir_path'; + let $p_index_directory= DATA DIRECTORY = '$data_dir_path'; + let $index_directory= DATA DIRECTORY = '$idx_dir_path'; + source suite/parts/inc/part_exch_tabs.inc; + error ER_TABLES_DIFFERENT_METADATA; + ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; + source suite/parts/inc/part_exch_drop_tabs.inc; + +rmdir $idx_dir_path; +rmdir $data_dir_path; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_15.test b/mysql-test/suite/parts/t/partition_exch_qa_15.test index 51d09be5ed9..8ea641c8178 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_15.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_15.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-15 ---source include/have_innodb.inc ---source include/have_partition.inc +source include/have_innodb.inc; +source include/have_partition.inc; let $engine_table= InnoDB; let $engine_part= InnoDB; @@ -10,11 +10,7 @@ let $engine_subpart= InnoDB; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; # 21) Foreign Key. # Exchange of partition with table differing in structure. @@ -22,10 +18,8 @@ CREATE TABLE t_11 (a INT, b VARCHAR(55), FOREIGN KEY (a) REFERENCES t_10 (a) ON DELETE CASCADE) ENGINE= InnoDB; #--error ER_TABLES_DIFFERENT_METADATA ---error ER_PARTITION_EXCHANGE_FOREIGN_KEY +error ER_PARTITION_EXCHANGE_FOREIGN_KEY; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings ---source suite/parts/inc/part_exch_drop_tabs.inc +DROP TABLE t_11; +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_2.test b/mysql-test/suite/parts/t/partition_exch_qa_2.test index 83dc0a81fca..1858131ce10 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_2.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_2.test @@ -1,7 +1,7 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_partition.inc +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; @@ -9,72 +9,61 @@ let $engine_subpart= MYISAM; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM t_100; ---sorted_result +sorted_result; SELECT * FROM t_1000; ---sorted_result +sorted_result; SELECT * FROM tp; ---sorted_result +sorted_result; SELECT * FROM tsp; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_01; ---sorted_result +sorted_result; SELECT * FROM tsp_02; ---sorted_result +sorted_result; SELECT * FROM tsp_03; ---sorted_result +sorted_result; SELECT * FROM tsp_04; # 3) Invalid exchanges. # Exchange of partition with table differing in structure. CREATE TABLE t_11(a INT,b VARCHAR(55)) SELECT * FROM t_10; ---error ER_TABLES_DIFFERENT_METADATA +error ER_TABLES_DIFFERENT_METADATA; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table SELECT * FROM t_10; ---error ER_TABLES_DIFFERENT_METADATA +error ER_TABLES_DIFFERENT_METADATA; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; +DROP TABLE t_11; CREATE TABLE t_11(a INT,b VARCHAR(55),PRIMARY KEY(a)) ENGINE= MEMORY SELECT * FROM t_10; ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; # Exchange of partition with partitioned table. eval CREATE TABLE t_11(a INT,b CHAR(55),PRIMARY KEY(a)) ENGINE= $engine_table PARTITION BY KEY() AS SELECT * FROM t_10; ---error ER_PARTITION_EXCHANGE_PART_TABLE +error ER_PARTITION_EXCHANGE_PART_TABLE; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_11; ---disable_warnings -DROP TABLE IF EXISTS t_11; ---enable_warnings +DROP TABLE t_11; # Exchange of subpartition with partitioned table. ---error ER_PARTITION_EXCHANGE_PART_TABLE +error ER_PARTITION_EXCHANGE_PART_TABLE; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE tsp; # Exchange of subpartitioned partition with table. ---error ER_PARTITION_INSTEAD_OF_SUBPARTITION +error ER_PARTITION_INSTEAD_OF_SUBPARTITION; ALTER TABLE tsp EXCHANGE PARTITION p0 WITH TABLE t_10; # Exchange of values in partition not fitting the hash. ---error ER_ROW_DOES_NOT_MATCH_PARTITION +error ER_ROW_DOES_NOT_MATCH_PARTITION; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_100; # Exchange of values in subpartition not fitting the hash. ---error ER_ROW_DOES_NOT_MATCH_PARTITION +error ER_ROW_DOES_NOT_MATCH_PARTITION; ALTER TABLE tp EXCHANGE PARTITION p2 WITH TABLE t_10; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_3.test b/mysql-test/suite/parts/t/partition_exch_qa_3.test index aa79e97adb6..fc49eb1da90 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_3.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_3.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-05 ---source include/have_partition.inc ---source include/have_innodb.inc +source include/have_partition.inc; +source include/have_innodb.inc; let $engine_table= MYISAM; let $engine_part= InnoDB; @@ -10,38 +10,34 @@ let $engine_subpart= InnoDB; use test; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc +source suite/parts/inc/part_exch_tabs.inc; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM t_100; ---sorted_result +sorted_result; SELECT * FROM t_1000; ---sorted_result +sorted_result; SELECT * FROM tp; ---sorted_result +sorted_result; SELECT * FROM tsp; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_01; ---sorted_result +sorted_result; SELECT * FROM tsp_02; ---sorted_result +sorted_result; SELECT * FROM tsp_03; ---sorted_result +sorted_result; SELECT * FROM tsp_04; ---enable_result_log ---enable_query_log # 5) Exchanges with different engines. ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---error ER_MIX_HANDLER_ERROR +error ER_MIX_HANDLER_ERROR; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; diff --git a/mysql-test/suite/parts/t/partition_exch_qa_6.test b/mysql-test/suite/parts/t/partition_exch_qa_6.test index 69b49a32813..ace13f26921 100644 --- a/mysql-test/suite/parts/t/partition_exch_qa_6.test +++ b/mysql-test/suite/parts/t/partition_exch_qa_6.test @@ -1,8 +1,8 @@ # Author: Horst Hunger # Created: 2010-07-06 ---source include/not_embedded.inc ---source include/have_partition.inc +source include/not_embedded.inc; +source include/have_partition.inc; let $engine_table= MYISAM; let $engine_part= MYISAM; @@ -10,41 +10,36 @@ let $engine_subpart= MYISAM; CREATE USER test2@localhost; ---disable_result_log ---disable_query_log ---source suite/parts/inc/part_exch_tabs.inc ---enable_result_log ---enable_query_log +source suite/parts/inc/part_exch_tabs.inc; GRANT USAGE ON *.* TO test2@localhost; GRANT CREATE, DROP, ALTER, UPDATE, INSERT, SELECT ON test.* TO test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -USE test; SHOW GRANTS FOR CURRENT_USER; # 9) Exchanges with different owner. # Privilege for ALTER and SELECT ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM tp WHERE a BETWEEN 0 AND 10; # Back to former values. ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; ---sorted_result +sorted_result; SELECT * FROM t_10; ---sorted_result +sorted_result; SELECT * FROM tp WHERE a BETWEEN 0 AND 10; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; # Back to former values. ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp_00; ---sorted_result +sorted_result; SELECT * FROM tsp WHERE a BETWEEN 0 AND 10; disconnect test2; @@ -55,7 +50,7 @@ connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); USE test; SHOW GRANTS FOR CURRENT_USER; # Privilege for ALTER and SELECT ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; disconnect test2; @@ -66,7 +61,7 @@ REVOKE CREATE ON test.* FROM test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); USE test; SHOW GRANTS FOR CURRENT_USER; ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tsp EXCHANGE PARTITION sp00 WITH TABLE tsp_00; disconnect test2; @@ -75,15 +70,14 @@ GRANT CREATE ON test.* TO test2@localhost; REVOKE DROP ON test.* FROM test2@localhost; connect (test2,localhost,test2,,test,$MASTER_MYPORT,$MASTER_MYSOCK); -USE test; SHOW GRANTS FOR CURRENT_USER; # Privilege for ALTER and SELECT ---error ER_TABLEACCESS_DENIED_ERROR +error ER_TABLEACCESS_DENIED_ERROR; ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t_10; disconnect test2; connection default; ---source suite/parts/inc/part_exch_drop_tabs.inc +source suite/parts/inc/part_exch_drop_tabs.inc; DROP USER test2@localhost; diff --git a/mysql-test/suite/perfschema/r/misc.result b/mysql-test/suite/perfschema/r/misc.result index 2adf2cba851..f2d40fe90b5 100644 --- a/mysql-test/suite/perfschema/r/misc.result +++ b/mysql-test/suite/perfschema/r/misc.result @@ -42,12 +42,12 @@ AND EVENT_NAME IN WHERE NAME LIKE "wait/synch/%") LIMIT 1; create table test.t1(a int) engine=performance_schema; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type WHERE object_schema='test'; object_schema object_name create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") SELECT object_schema, object_name FROM performance_schema.objects_summary_global_by_type WHERE object_schema='test'; object_schema object_name diff --git a/mysql-test/suite/perfschema/r/privilege.result b/mysql-test/suite/perfschema/r/privilege.result index 09d32a177fd..7bb7627142e 100644 --- a/mysql-test/suite/perfschema/r/privilege.result +++ b/mysql-test/suite/perfschema/r/privilege.result @@ -155,13 +155,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'setup_instruments' @@ -254,13 +254,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_1'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_1'@'localhost' for table 'setup_instruments' @@ -354,13 +354,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_2'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_2'@'localhost' for table 'setup_instruments' @@ -454,13 +454,13 @@ before insert on performance_schema.file_instances for each row begin end; ERROR 42000: Access denied for user 'pfs_user_3'@'localhost' to database 'performance_schema' create table test.t1(a int) engine=PERFORMANCE_SCHEMA; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.setup_instruments; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.events_waits_current; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") create table test.t1 like performance_schema.file_instances; -ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by database") +ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") insert into performance_schema.setup_instruments set name="foo"; ERROR 42000: INSERT command denied to user 'pfs_user_3'@'localhost' for table 'setup_instruments' diff --git a/mysql-test/suite/rpl/r/rpl_create_drop_view.result b/mysql-test/suite/rpl/r/rpl_create_drop_view.result index 34f27faf98a..ebbe9efc9df 100644 --- a/mysql-test/suite/rpl/r/rpl_create_drop_view.result +++ b/mysql-test/suite/rpl/r/rpl_create_drop_view.result @@ -99,7 +99,7 @@ DROP VIEW v1; ERROR 42S02: Unknown VIEW: 'test.v1' DROP VIEW IF EXISTS v2; Warnings: -Note 4089 Unknown VIEW: 'test.v2' +Note 4090 Unknown VIEW: 'test.v2' # Syncing slave with master connection slave; SELECT * FROM v1; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result index 62a5b9c3531..54156685806 100644 --- a/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result +++ b/mysql-test/suite/rpl/r/rpl_gtid_errorhandling.result @@ -113,7 +113,7 @@ SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,dummy_disable_default_dbug_output"; SET GLOBAL debug_dbug="+d,gtid_fail_after_record_gtid"; SET sql_log_bin=0; -CALL mtr.add_suppression('Got error 131 "Command not supported by database" during COMMIT'); +CALL mtr.add_suppression('Got error 131 "Command not supported by the engine" during COMMIT'); SET sql_log_bin=1; START SLAVE; include/wait_for_slave_sql_error.inc [errno=1180] diff --git a/mysql-test/suite/rpl/r/rpl_sp.result b/mysql-test/suite/rpl/r/rpl_sp.result index 25287b70cf6..2849e5b7ae3 100644 --- a/mysql-test/suite/rpl/r/rpl_sp.result +++ b/mysql-test/suite/rpl/r/rpl_sp.result @@ -128,7 +128,7 @@ show warnings; Level Code Message Error 1062 Duplicate entry '20' for key 'a' Warning 1196 Some non-transactional changed tables couldn't be rolled back -Note 4091 At line 4 in mysqltest1.foo4 +Note 4092 At line 4 in mysqltest1.foo4 select * from t2; a 20 @@ -291,7 +291,7 @@ end| do fn1(100); Warnings: Error 1062 Duplicate entry '100' for key 'a' -Note 4091 At line 3 in mysqltest1.fn1 +Note 4092 At line 3 in mysqltest1.fn1 Warning 1196 Some non-transactional changed tables couldn't be rolled back select fn1(20); ERROR 23000: Duplicate entry '20' for key 'a' diff --git a/mysql-test/suite/rpl/r/rpl_sp_variables.result b/mysql-test/suite/rpl/r/rpl_sp_variables.result new file mode 100644 index 00000000000..7e2ba72845c --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_sp_variables.result @@ -0,0 +1,28 @@ +include/master-slave.inc +[connection master] +# +# MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +# +connection master; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +CREATE PROCEDURE p1() +BEGIN +DECLARE v_id INT DEFAULT 2017; +INSERT INTO test.t1 SELECT CONCAT(v_id, '오'); +END; +$$ +CALL p1; +SELECT * FROM t1; +a +2017오 +connection slave; +SET NAMES utf8; +SELECT * FROM t1; +a +2017오 +connection master; +DROP PROCEDURE p1; +DROP TABLE t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test index 796f6894f19..c02e2670c92 100644 --- a/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test +++ b/mysql-test/suite/rpl/t/rpl_gtid_errorhandling.test @@ -132,7 +132,7 @@ SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,dummy_disable_default_dbug_output"; SET GLOBAL debug_dbug="+d,gtid_fail_after_record_gtid"; SET sql_log_bin=0; -CALL mtr.add_suppression('Got error 131 "Command not supported by database" during COMMIT'); +CALL mtr.add_suppression('Got error 131 "Command not supported by the engine" during COMMIT'); SET sql_log_bin=1; START SLAVE; --let $slave_sql_errno= 1180 diff --git a/mysql-test/suite/rpl/t/rpl_sp_variables.test b/mysql-test/suite/rpl/t/rpl_sp_variables.test new file mode 100644 index 00000000000..87e9fe194ea --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_sp_variables.test @@ -0,0 +1,28 @@ +source include/master-slave.inc; + +--echo # +--echo # MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +--echo # + +connection master; +SET NAMES utf8; +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +DELIMITER $$; +CREATE PROCEDURE p1() +BEGIN + DECLARE v_id INT DEFAULT 2017; + INSERT INTO test.t1 SELECT CONCAT(v_id, '오'); +END; +$$ +DELIMITER ;$$ +CALL p1; +SELECT * FROM t1; +sync_slave_with_master; +SET NAMES utf8; +SELECT * FROM t1; +connection master; +DROP PROCEDURE p1; +DROP TABLE t1; +sync_slave_with_master; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 283253b585a..0fca8b7c02b 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -212,7 +212,7 @@ ERROR 42S02: 'test.t1' is not a SEQUENCE drop table t1; alter sequence if exists t1 minvalue=100; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' alter sequence t1 minvalue=100; ERROR 42S02: Table 'test.t1' doesn't exist create sequence t1; diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 2697da1d8ff..a5eb64802db 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -165,7 +165,7 @@ drop sequence t1; ERROR 42S02: 'test.t1' is not a SEQUENCE drop sequence if exists t1; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' create sequence t1 start with 10 maxvalue=9; ERROR HY000: Sequence 'test.t1' values are conflicting create sequence t1 minvalue= 100 maxvalue=10; @@ -377,7 +377,7 @@ key key1 (next_not_cached_value) ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys) drop sequence if exists t1; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t1' create sequence t1; create sequence t2; create table t3 (a int) engine=myisam; @@ -387,8 +387,8 @@ CREATE SEQUENCE s1; drop sequence s1; drop sequence if exists t1,t2,t3,t4; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t3' -Note 4088 Unknown SEQUENCE: 'test.t4' +Note 4089 Unknown SEQUENCE: 'test.t3' +Note 4089 Unknown SEQUENCE: 'test.t4' drop table if exists t1,t2,t3; Warnings: Note 1051 Unknown table 'test.t1' @@ -414,9 +414,9 @@ CREATE TABLE t2 (a int); CREATE SEQUENCE s1; drop sequence if exists t1,t2,s1,s2; Warnings: -Note 4088 Unknown SEQUENCE: 'test.t1' -Note 4088 Unknown SEQUENCE: 'test.t2' -Note 4088 Unknown SEQUENCE: 'test.s2' +Note 4089 Unknown SEQUENCE: 'test.t1' +Note 4089 Unknown SEQUENCE: 'test.t2' +Note 4089 Unknown SEQUENCE: 'test.s2' drop table if exists t1,t2; CREATE TEMPORARY SEQUENCE s1; DROP SEQUENCE s1; diff --git a/mysql-test/suite/vcol/r/innodb_virtual_fk.result b/mysql-test/suite/vcol/r/innodb_virtual_fk.result new file mode 100644 index 00000000000..58db12583e2 --- /dev/null +++ b/mysql-test/suite/vcol/r/innodb_virtual_fk.result @@ -0,0 +1,12 @@ +set default_storage_engine=innodb; +create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2)); +create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade); +insert into t1 (id) values (1), (2); +insert into t2 (id) values (1), (2); +delete from t1; +select * from t1; +id id2 +select * from t2; +id +drop table t2; +drop table t1; diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result index 95b0093ed71..5c7905cf547 100644 --- a/mysql-test/suite/vcol/r/update.result +++ b/mysql-test/suite/vcol/r/update.result @@ -155,3 +155,13 @@ select * from t; a b c d e 11 11 11 11 11 drop table t, t1, t2; +create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2)); +insert into t (f1,f2) values (1,1),(2,2); +create view v as +select a2.f1, a2.f2, a1.f3 +from t a1, t a2 +where a2.f3 <> 0 +with local check option; +update v set f3 = 52; +drop view v; +drop table t; diff --git a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result index 86030a304d4..3fa4f6e1431 100644 --- a/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result +++ b/mysql-test/suite/vcol/r/vcol_supported_sql_funcs.result @@ -2911,16 +2911,31 @@ drop table t1; set sql_warnings = 0; # TIME_FORMAT() set sql_warnings = 1; -create table t1 (a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y"))); +create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S"))); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime DEFAULT NULL, - `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%d.%m.%Y')) VIRTUAL + `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -insert into t1 values ('2001-01-01 02:02:02',default); +insert into t1 values ('2001-01-01 02:03:04',default); select * from t1; a b -2001-01-01 02:02:02 01.01.2001 +2001-01-01 02:03:04 02.03.04 +drop table t1; +set sql_warnings = 0; +# TIME_FORMAT() STORED +set sql_warnings = 1; +create table t1 (a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` datetime DEFAULT NULL, + `b` varchar(10) GENERATED ALWAYS AS (time_format(`a`,'%H.%i.%S')) STORED +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('2001-01-01 02:03:04',default); +select * from t1; +a b +2001-01-01 02:03:04 02.03.04 drop table t1; set sql_warnings = 0; diff --git a/mysql-test/suite/vcol/t/innodb_virtual_fk.test b/mysql-test/suite/vcol/t/innodb_virtual_fk.test new file mode 100644 index 00000000000..c364adaa613 --- /dev/null +++ b/mysql-test/suite/vcol/t/innodb_virtual_fk.test @@ -0,0 +1,16 @@ +source include/have_innodb.inc; +set default_storage_engine=innodb; + +# +# MDEV-13708 Crash with indexed virtual columns and FK cascading deletes +# + +create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2)); +create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade); +insert into t1 (id) values (1), (2); +insert into t2 (id) values (1), (2); +delete from t1; +select * from t1; +select * from t2; +drop table t2; +drop table t1; diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test index 062d9736ed8..1797bdd501e 100644 --- a/mysql-test/suite/vcol/t/update.test +++ b/mysql-test/suite/vcol/t/update.test @@ -111,3 +111,17 @@ check table t; select * from t; update t, t tt set t.b=11, tt.d=11 where t.a=tt.a; check table t; select * from t; drop table t, t1, t2; + +# +# MDEV-13623 Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in virtual longlong Field_long::val_int +# +create table t (f1 int, f2 int, f3 int as (f1*2) virtual, key(f3,f2)); +insert into t (f1,f2) values (1,1),(2,2); +create view v as + select a2.f1, a2.f2, a1.f3 + from t a1, t a2 + where a2.f3 <> 0 + with local check option; +update v set f3 = 52; +drop view v; +drop table t; diff --git a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc index 4a95ea75534..dafc42098dd 100644 --- a/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc +++ b/mysql-test/suite/vcol/t/vcol_supported_sql_funcs_main.inc @@ -1204,8 +1204,14 @@ let $rows = 1; --source suite/vcol/inc/vcol_supported_sql_funcs.inc --echo # TIME_FORMAT() -let $cols = a datetime, b varchar(10) as (time_format(a,"%d.%m.%Y")); -let $values1 = '2001-01-01 02:02:02',default; +let $cols = a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")); +let $values1 = '2001-01-01 02:03:04',default; +let $rows = 1; +--source suite/vcol/inc/vcol_supported_sql_funcs.inc + +--echo # TIME_FORMAT() STORED +let $cols = a datetime, b varchar(10) as (time_format(a,"%H.%i.%S")) STORED; +let $values1 = '2001-01-01 02:03:04',default; let $rows = 1; --source suite/vcol/inc/vcol_supported_sql_funcs.inc |