diff options
Diffstat (limited to 'mysql-test/r/merge.result')
| -rw-r--r-- | mysql-test/r/merge.result | 169 |
1 files changed, 129 insertions, 40 deletions
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 66ba6cea70e..14308959557 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2167,7 +2167,7 @@ col1 int(10) NOT NULL ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1); insert into m1 (col1) values (1); insert into m1 (col1) values (1); -ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' +ERROR 23000: Can't write; duplicate key in table 'm1' drop table m1, t1; # # Bug#45800 crash when replacing into a merge table and there is a duplicate @@ -2204,7 +2204,7 @@ CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LA INSERT INTO m1 VALUES (1,2); # insert the duplicate value into the merge table INSERT INTO m1 VALUES (3,2); -ERROR 23000: Duplicate entry '' for key '*UNKNOWN*' +ERROR 23000: Can't write; duplicate key in table 'm1' DROP TABLE m1,t1; # Try to define MERGE and MyISAM with keys on different columns CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1)); @@ -2257,7 +2257,7 @@ CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7); SELECT 1 FROM m1; 1 HANDLER m1 OPEN; -ERROR HY000: Table storage engine for 'm1' doesn't have this option +ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`m1` doesn't have this option DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7; SELECT 1 FROM m1; ERROR 42S02: Table 'test.m1' doesn't exist @@ -3559,7 +3559,7 @@ CREATE TABLE t2 (c1 int); CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2); START TRANSACTION; HANDLER t3 OPEN; -ERROR HY000: Table storage engine for 't3' doesn't have this option +ERROR HY000: Storage engine MRG_MyISAM of the table `test`.`t3` doesn't have this option DROP TABLE t1, t2, t3; # Connection default. # Disconnecting con1, all mdl_tickets must have been released. @@ -3657,85 +3657,85 @@ insert into tmp (b) values (1); insert into t1 (a) values (1); insert into t3 (b) values (1); insert into m1 (a) values ((select max(a) from m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from t3, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from tmp, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'INSERT' and as a separate source for data insert into m1 (a) values ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. insert into m1 (a) values ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation INSERT on table 'm1'. update m1 set a = ((select max(a) from m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from t3, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, m1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, m2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, t1)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from tmp, t2)); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'UPDATE' and as a separate source for data update m1 set a = ((select max(a) from v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. update m1 set a = ((select max(a) from tmp, v1)); ERROR HY000: The definition of table 'v1' prevents operation UPDATE on table 'm1'. delete from m1 where a = (select max(a) from m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from t3, t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, m1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, m2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, t1); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from tmp, t2); -ERROR HY000: You can't specify target table 'm1' for update in FROM clause +ERROR HY000: Table 'm1' is specified twice, both as a target for 'DELETE' and as a separate source for data delete from m1 where a = (select max(a) from v1); ERROR HY000: The definition of table 'v1' prevents operation DELETE on table 'm1'. delete from m1 where a = (select max(a) from tmp, v1); @@ -3850,3 +3850,92 @@ test.t1 analyze status Table is already up to date DEALLOCATE PREPARE stmt; DROP TABLE t1, tmerge; End of 5.5 tests +# +# Additional coverage for refactoring which is made as part +# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +# to allow temp table operations". +# +# Check that prelocking works correctly for various variants of +# merge tables. +drop table if exists t1, t2, m1; +drop function if exists f1; +create table t1 (j int); +insert into t1 values (1); +create function f1() returns int return (select count(*) from m1); +create temporary table t2 (a int) engine=myisam; +insert into t2 values (1); +create temporary table m1 (a int) engine=merge union=(t2); +select f1() from t1; +f1() +1 +drop tables t2, m1; +create table t2 (a int) engine=myisam; +insert into t2 values (1); +create table m1 (a int) engine=merge union=(t2); +select f1() from t1; +f1() +1 +drop table m1; +create temporary table m1 (a int) engine=merge union=(t2); +select f1() from t1; +f1() +1 +drop tables t1, t2, m1; +drop function f1; +# +# Check that REPAIR/CHECK and CHECKSUM statements work correctly +# for various variants of merge tables. +create table t1 (a int) engine=myisam; +insert into t1 values (1); +create table m1 (a int) engine=merge union=(t1); +check table m1; +Table Op Msg_type Msg_text +test.m1 check status OK +repair table m1; +Table Op Msg_type Msg_text +test.m1 repair note The storage engine for the table doesn't support repair +checksum table m1; +Table Checksum +test.m1 3459908756 +drop tables t1, m1; +create temporary table t1 (a int) engine=myisam; +insert into t1 values (1); +create temporary table m1 (a int) engine=merge union=(t1); +check table m1; +Table Op Msg_type Msg_text +test.m1 check status OK +repair table m1; +Table Op Msg_type Msg_text +test.m1 repair note The storage engine for the table doesn't support repair +checksum table m1; +Table Checksum +test.m1 3459908756 +drop tables t1, m1; +create table t1 (a int) engine=myisam; +insert into t1 values (1); +create temporary table m1 (a int) engine=merge union=(t1); +check table m1; +Table Op Msg_type Msg_text +test.m1 check status OK +repair table m1; +Table Op Msg_type Msg_text +test.m1 repair note The storage engine for the table doesn't support repair +checksum table m1; +Table Checksum +test.m1 3459908756 +drop tables t1, m1; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS m1; +DROP TRIGGER IF EXISTS trg1; +DROP TABLE IF EXISTS q1; +DROP TABLE IF EXISTS q2; +CREATE TABLE t1(a INT); +CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2); +CREATE TRIGGER trg1 BEFORE DELETE ON t1 +FOR EACH ROW +INSERT INTO m1 VALUES (1); +DELETE FROM t1; +ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist +DROP TRIGGER trg1; +DROP TABLE t1; +DROP TABLE m1; |