summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/binlog_row_mix_innodb_myisam.result158
-rw-r--r--mysql-test/r/binlog_stm_mix_innodb_myisam.result103
-rw-r--r--mysql-test/r/create.result46
-rw-r--r--mysql-test/r/federated.result1
-rw-r--r--mysql-test/r/func_gconcat.result6
-rw-r--r--mysql-test/r/func_time.result1
-rw-r--r--mysql-test/r/innodb_mysql.result60
-rw-r--r--mysql-test/r/insert.result26
-rw-r--r--mysql-test/r/loaddata.result9
-rw-r--r--mysql-test/r/multi_update.result80
-rw-r--r--mysql-test/r/ndb_index_unique.result2
-rw-r--r--mysql-test/r/ndb_replace.result2
-rw-r--r--mysql-test/r/rpl_ddl.result4
-rw-r--r--mysql-test/r/view_grant.result2
14 files changed, 486 insertions, 14 deletions
diff --git a/mysql-test/r/binlog_row_mix_innodb_myisam.result b/mysql-test/r/binlog_row_mix_innodb_myisam.result
index 078a95d5abd..84959684c42 100644
--- a/mysql-test/r/binlog_row_mix_innodb_myisam.result
+++ b/mysql-test/r/binlog_row_mix_innodb_myisam.result
@@ -234,8 +234,6 @@ commit;
begin;
create temporary table ti (a int) engine=innodb;
rollback;
-Warnings:
-Warning 1196 Some non-transactional changed tables couldn't be rolled back
insert into ti values(1);
set autocommit=0;
create temporary table t1 (a int) engine=myisam;
@@ -285,6 +283,162 @@ master-bin.000001 1260 Write_rows 1 # table_id: # flags: STMT_END_F
master-bin.000001 1294 Query 1 # use `test`; create table t2 (n int) engine=innodb
do release_lock("lock1");
drop table t0,t2;
+set autocommit=0;
+CREATE TABLE t1 (a int, b int) engine=myisam;
+reset master;
+INSERT INTO t1 values (1,1),(1,2);
+CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+DROP TABLE if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+INSERT INTO t1 values (3,3);
+CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
+INSERT INTO t1 VALUES (4,4);
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+TRUNCATE table t2;
+INSERT INTO t1 VALUES (5,5);
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * FROM t2;
+a b
+DROP TABLE t2;
+INSERT INTO t1 values (6,6);
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
+INSERT INTO t1 values (7,7);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO t1 values (8,8);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+COMMIT;
+INSERT INTO t1 values (9,9);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SELECT * from t2;
+a b
+TRUNCATE table t2;
+INSERT INTO t1 values (10,10);
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t1;
+a b
+1 1
+1 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+INSERT INTO t2 values (100,100);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+COMMIT;
+INSERT INTO t2 values (101,101);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+DROP TABLE t1,t2;
+show binlog events from 102;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 102 Table_map 1 142 table_id: # (test.t1)
+master-bin.000001 142 Write_rows 1 189 table_id: # flags: STMT_END_F
+master-bin.000001 189 Query 1 257 use `test`; BEGIN
+master-bin.000001 257 Query 1 182 use `test`; CREATE TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 439 Table_map 1 222 table_id: # (test.t2)
+master-bin.000001 479 Write_rows 1 260 table_id: # flags: STMT_END_F
+master-bin.000001 517 Xid 1 544 COMMIT /* xid= */
+master-bin.000001 544 Query 1 630 use `test`; DROP TABLE if exists t2
+master-bin.000001 630 Table_map 1 670 table_id: # (test.t1)
+master-bin.000001 670 Write_rows 1 708 table_id: # flags: STMT_END_F
+master-bin.000001 708 Query 1 776 use `test`; BEGIN
+master-bin.000001 776 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 968 Query 1 1039 use `test`; ROLLBACK
+master-bin.000001 1039 Query 1 1125 use `test`; DROP TABLE IF EXISTS t2
+master-bin.000001 1125 Query 1 1249 use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb
+master-bin.000001 1249 Table_map 1 1289 table_id: # (test.t1)
+master-bin.000001 1289 Write_rows 1 1327 table_id: # flags: STMT_END_F
+master-bin.000001 1327 Query 1 1395 use `test`; BEGIN
+master-bin.000001 1395 Query 1 182 use `test`; CREATE TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 1577 Table_map 1 222 table_id: # (test.t2)
+master-bin.000001 1617 Write_rows 1 260 table_id: # flags: STMT_END_F
+master-bin.000001 1655 Xid 1 1682 COMMIT /* xid= */
+master-bin.000001 1682 Query 1 80 use `test`; TRUNCATE table t2
+master-bin.000001 1762 Xid 1 1789 COMMIT /* xid= */
+master-bin.000001 1789 Table_map 1 1829 table_id: # (test.t1)
+master-bin.000001 1829 Write_rows 1 1867 table_id: # flags: STMT_END_F
+master-bin.000001 1867 Query 1 1935 use `test`; BEGIN
+master-bin.000001 1935 Table_map 1 40 table_id: # (test.t2)
+master-bin.000001 1975 Write_rows 1 78 table_id: # flags: STMT_END_F
+master-bin.000001 2013 Xid 1 2040 COMMIT /* xid= */
+master-bin.000001 2040 Query 1 2116 use `test`; DROP TABLE t2
+master-bin.000001 2116 Table_map 1 2156 table_id: # (test.t1)
+master-bin.000001 2156 Write_rows 1 2194 table_id: # flags: STMT_END_F
+master-bin.000001 2194 Table_map 1 2234 table_id: # (test.t1)
+master-bin.000001 2234 Write_rows 1 2272 table_id: # flags: STMT_END_F
+master-bin.000001 2272 Table_map 1 2312 table_id: # (test.t1)
+master-bin.000001 2312 Write_rows 1 2350 table_id: # flags: STMT_END_F
+master-bin.000001 2350 Query 1 2418 use `test`; BEGIN
+master-bin.000001 2418 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 2610 Xid 1 2637 COMMIT /* xid= */
+master-bin.000001 2637 Table_map 1 2677 table_id: # (test.t1)
+master-bin.000001 2677 Write_rows 1 2715 table_id: # flags: STMT_END_F
+master-bin.000001 2715 Query 1 2783 use `test`; BEGIN
+master-bin.000001 2783 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 2975 Query 1 3046 use `test`; ROLLBACK
+master-bin.000001 3046 Query 1 80 use `test`; TRUNCATE table t2
+master-bin.000001 3126 Xid 1 3153 COMMIT /* xid= */
+master-bin.000001 3153 Table_map 1 3193 table_id: # (test.t1)
+master-bin.000001 3193 Write_rows 1 3231 table_id: # flags: STMT_END_F
+master-bin.000001 3231 Query 1 3299 use `test`; BEGIN
+master-bin.000001 3299 Query 1 192 use `test`; CREATE TEMPORARY TABLE `t2` (
+ `a` int(11) NOT NULL DEFAULT '0',
+ `b` int(11) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB
+master-bin.000001 3491 Xid 1 3518 COMMIT /* xid= */
+master-bin.000001 3518 Query 1 3622 use `test`; DROP TABLE `t1` /* generated by server */
reset master;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
diff --git a/mysql-test/r/binlog_stm_mix_innodb_myisam.result b/mysql-test/r/binlog_stm_mix_innodb_myisam.result
index c5abcff4246..e836cae0b15 100644
--- a/mysql-test/r/binlog_stm_mix_innodb_myisam.result
+++ b/mysql-test/r/binlog_stm_mix_innodb_myisam.result
@@ -209,8 +209,6 @@ commit;
begin;
create temporary table ti (a int) engine=innodb;
rollback;
-Warnings:
-Warning 1196 Some non-transactional changed tables couldn't be rolled back
insert into ti values(1);
set autocommit=0;
create temporary table t1 (a int) engine=myisam;
@@ -256,6 +254,107 @@ master-bin.000001 1654 Query 1 # use `test`; create table t2 (n int) engine=inno
master-bin.000001 1754 Query 1 # use `test`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `test`.`t1`,`test`.`ti`
do release_lock("lock1");
drop table t0,t2;
+set autocommit=0;
+CREATE TABLE t1 (a int, b int) engine=myisam;
+reset master;
+INSERT INTO t1 values (1,1),(1,2);
+CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+DROP TABLE if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+INSERT INTO t1 values (3,3);
+CREATE TEMPORARY TABLE t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb;
+INSERT INTO t1 VALUES (4,4);
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+TRUNCATE table t2;
+INSERT INTO t1 VALUES (5,5);
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * FROM t2;
+a b
+DROP TABLE t2;
+INSERT INTO t1 values (6,6);
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb ;
+INSERT INTO t1 values (7,7);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO t1 values (8,8);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+COMMIT;
+INSERT INTO t1 values (9,9);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SELECT * from t2;
+a b
+TRUNCATE table t2;
+INSERT INTO t1 values (10,10);
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t1;
+a b
+1 1
+1 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+INSERT INTO t2 values (100,100);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+COMMIT;
+INSERT INTO t2 values (101,101);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) engine=innodb select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+DROP TABLE t1,t2;
+show binlog events from 102;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 102 Query 1 198 use `test`; INSERT INTO t1 values (1,1),(1,2)
+master-bin.000001 198 Query 1 284 use `test`; DROP TABLE if exists t2
+master-bin.000001 284 Query 1 374 use `test`; INSERT INTO t1 values (3,3)
+master-bin.000001 374 Query 1 460 use `test`; DROP TABLE IF EXISTS t2
+master-bin.000001 460 Query 1 584 use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb
+master-bin.000001 584 Query 1 674 use `test`; INSERT INTO t1 VALUES (4,4)
+master-bin.000001 674 Query 1 80 use `test`; TRUNCATE table t2
+master-bin.000001 754 Xid 1 781 COMMIT /* xid= */
+master-bin.000001 781 Query 1 871 use `test`; INSERT INTO t1 VALUES (5,5)
+master-bin.000001 871 Query 1 947 use `test`; DROP TABLE t2
+master-bin.000001 947 Query 1 1037 use `test`; INSERT INTO t1 values (6,6)
+master-bin.000001 1037 Query 1 1171 use `test`; CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)) engine=innodb
+master-bin.000001 1171 Query 1 1261 use `test`; INSERT INTO t1 values (7,7)
+master-bin.000001 1261 Query 1 1351 use `test`; INSERT INTO t1 values (8,8)
+master-bin.000001 1351 Query 1 1441 use `test`; INSERT INTO t1 values (9,9)
+master-bin.000001 1441 Query 1 80 use `test`; TRUNCATE table t2
+master-bin.000001 1521 Xid 1 1548 COMMIT /* xid= */
+master-bin.000001 1548 Query 1 1640 use `test`; INSERT INTO t1 values (10,10)
+master-bin.000001 1640 Query 1 1708 use `test`; BEGIN
+master-bin.000001 1708 Query 1 94 use `test`; INSERT INTO t2 values (100,100)
+master-bin.000001 1802 Xid 1 1829 COMMIT /* xid= */
+master-bin.000001 1829 Query 1 1908 use `test`; DROP TABLE t1,t2
reset master;
create table t1 (a int) engine=innodb;
create table t2 (a int) engine=myisam;
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index d45960cf787..6f8c319eb6f 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -266,6 +266,7 @@ select * from t1;
0 1 2
0 0 1
drop table t1;
+flush status;
create table t1 (a int not null, b int, primary key (a));
insert into t1 values (1,1);
create table if not exists t1 select 2;
@@ -281,6 +282,13 @@ Warnings:
Note 1050 Table 't1' already exists
create table if not exists t1 select 3 as 'a',3 as 'b';
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+show warnings;
+Level Code Message
+Note 1050 Table 't1' already exists
+Error 1062 Duplicate entry '3' for key 'PRIMARY'
+show status like "Opened_tables";
+Variable_name Value
+Opened_tables 2
select * from t1;
a b
1 1
@@ -778,3 +786,41 @@ Warnings:
Warning 1071 Specified key was too long; max key length is 765 bytes
insert into t1 values('aaa');
drop table t1;
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+CREATE TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t2 (a int, b int, primary key (a));
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+1 1
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+1 1
+drop table t2;
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+1 1
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+1 1
+drop table t1,t2;
diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result
index 5f735ebe926..b3d6e10448b 100644
--- a/mysql-test/r/federated.result
+++ b/mysql-test/r/federated.result
@@ -1601,6 +1601,7 @@ fld_cid fld_name fld_parentid fld_delt
5 Torkel 0 0
DROP TABLE federated.t1;
DROP TABLE federated.bug_17377_table;
+DROP TABLE federated.t1;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;
DROP TABLE IF EXISTS federated.t1;
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 62b4ddf868a..773efe50749 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -309,6 +309,12 @@ a grp
1 2
2 4,3
3 5
+select group_concat(c order by (select concat(5-t1.c,group_concat(c order by a)) from t2 where t2.a=t1.a)) as grp from t1;
+grp
+5,4,3,2
+select group_concat(c order by (select concat(t1.c,group_concat(c)) from t2 where a=t1.a)) as grp from t1;
+grp
+2,3,4,5
select a,c,(select group_concat(c order by a) from t2 where a=t1.a) as grp from t1 order by grp;
a c grp
3 5 3,3
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 516809b8906..283dd11ca1e 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -763,6 +763,7 @@ time_format('100:00:00', '%H %k %h %I %l')
100 100 04 04 4
create table t1 (a timestamp default '2005-05-05 01:01:01',
b timestamp default '2005-05-05 01:01:01');
+drop function if exists t_slow_sysdate;
create function t_slow_sysdate() returns timestamp
begin
do sleep(2);
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 8dbfa906fe1..918f1c2c7c5 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2;
+drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4;
create table t1 (
c_id int(11) not null default '0',
org_id int(11) default null,
@@ -268,3 +268,61 @@ explain select distinct f1, f2 from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary
drop table t1;
+set storage_engine=innodb;
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+CREATE TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+100 100
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+drop table t2;
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+100 100
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+100 100
+101 101
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+drop table t1,t2;
diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result
index 5e9ab480558..18bd6d7e796 100644
--- a/mysql-test/r/insert.result
+++ b/mysql-test/r/insert.result
@@ -2,8 +2,8 @@ drop table if exists t1,t2,t3;
create table t1 (a int not null);
insert into t1 values (1);
insert into t1 values (a+2);
-insert into t1 values (a+3);
-insert into t1 values (4),(a+5);
+insert into t1 values (a+3),(a+4);
+insert into t1 values (5),(a+6);
select * from t1;
a
1
@@ -11,6 +11,7 @@ a
3
4
5
+6
drop table t1;
create table t1 (id int not null auto_increment primary key, username varchar(32) not null, unique (username));
insert into t1 values (0,"mysql");
@@ -299,3 +300,24 @@ select count(*) from t2;
count(*)
25500
drop table t1,t2,t3;
+create table t1 (a int, b int);
+insert into t1 (a,b) values (a,b);
+insert into t1 SET a=1, b=a+1;
+insert into t1 (a,b) select 1,2;
+INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
+prepare stmt1 from ' replace into t1 (a,a) select 100, ''hundred'' ';
+execute stmt1;
+ERROR 42000: Column 'a' specified twice
+insert into t1 (a,b,b) values (1,1,1);
+ERROR 42000: Column 'b' specified twice
+insert into t1 (a,a) values (1,1,1);
+ERROR 21S01: Column count doesn't match value count at row 1
+insert into t1 (a,a) values (1,1);
+ERROR 42000: Column 'a' specified twice
+insert into t1 SET a=1,b=2,a=1;
+ERROR 42000: Column 'a' specified twice
+insert into t1 (b,b) select 1,2;
+ERROR 42000: Column 'b' specified twice
+INSERT INTO t1 (b,b) SELECT 0,0 ON DUPLICATE KEY UPDATE a = a + VALUES (a);
+ERROR 42000: Column 'b' specified twice
+drop table t1;
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result
index 0b314defece..72beee4b2e3 100644
--- a/mysql-test/r/loaddata.result
+++ b/mysql-test/r/loaddata.result
@@ -115,6 +115,15 @@ select @a, @b;
@a @b
NULL 15
truncate table t1;
+load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b;
+Warnings:
+Warning 1261 Row 1 doesn't contain data for all columns
+Warning 1261 Row 2 doesn't contain data for all columns
+select * from t1;
+a b c
+NULL 10 10
+NULL 15 15
+truncate table t1;
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
select * from t1;
a b c
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index ea02a703c65..6fdd105fd6c 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -519,3 +519,83 @@ a
30
drop view v1;
drop table t1, t2;
+create table t1 (i1 int, i2 int, i3 int);
+create table t2 (id int, c1 varchar(20), c2 varchar(20));
+insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from t1 order by i1;
+i1 i2 i3
+1 5 10
+2 2 2
+3 7 12
+4 5 2
+9 10 15
+select * from t2;
+id c1 c2
+9 abc def
+5 opq lmn
+2 test t t test
+update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from t1 order by i1;
+i1 i2 i3
+1 5 10
+2 15 2
+3 7 12
+4 5 2
+9 15 15
+select * from t2 order by id;
+id c1 c2
+2 test t ppc
+5 opq lmn
+9 abc ppc
+delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
+select * from t1 order by i1;
+i1 i2 i3
+2 15 2
+3 7 12
+9 15 15
+select * from t2 order by id;
+id c1 c2
+2 test t ppc
+9 abc ppc
+drop table t1, t2;
+create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
+create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
+insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
+insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
+select * from t1 order by i1;
+i1 i2 i3
+1 5 10
+2 2 2
+3 7 12
+4 5 2
+9 10 15
+select * from t2 order by id;
+id c1 c2
+2 test t t test
+5 opq lmn
+9 abc def
+update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
+select * from t1 order by i1;
+i1 i2 i3
+1 5 10
+2 15 2
+3 7 12
+4 5 2
+9 15 15
+select * from t2 order by id;
+id c1 c2
+2 test t ppc
+5 opq lmn
+9 abc ppc
+delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
+select * from t1 order by i1;
+i1 i2 i3
+2 15 2
+3 7 12
+9 15 15
+select * from t2 order by id;
+id c1 c2
+2 test t ppc
+9 abc ppc
+drop table t1, t2;
diff --git a/mysql-test/r/ndb_index_unique.result b/mysql-test/r/ndb_index_unique.result
index bd42595f060..88c6e00e215 100644
--- a/mysql-test/r/ndb_index_unique.result
+++ b/mysql-test/r/ndb_index_unique.result
@@ -144,7 +144,7 @@ b int unsigned not null,
c int unsigned,
UNIQUE (b, c) USING HASH
) engine=ndbcluster;
-ERROR 42000: Column 'c' is used with UNIQUE or INDEX but is not defined as NOT NULL
+ERROR 42000: Table handler doesn't support NULL in given index. Please change column 'c' to be NOT NULL or use another handler
CREATE TABLE t3 (
a int unsigned NOT NULL,
b int unsigned not null,
diff --git a/mysql-test/r/ndb_replace.result b/mysql-test/r/ndb_replace.result
index 5e49968ca64..cdcd935bfcc 100644
--- a/mysql-test/r/ndb_replace.result
+++ b/mysql-test/r/ndb_replace.result
@@ -30,4 +30,4 @@ REPLACE INTO t1 (i,j) VALUES (17,2);
SELECT * from t1 ORDER BY i;
i j k
3 1 42
-17 2 24
+17 2 NULL
diff --git a/mysql-test/r/rpl_ddl.result b/mysql-test/r/rpl_ddl.result
index e828f2c1e31..cfa2b4dc76d 100644
--- a/mysql-test/r/rpl_ddl.result
+++ b/mysql-test/r/rpl_ddl.result
@@ -359,8 +359,6 @@ MAX(f1)
-------- switch to master -------
ROLLBACK;
-Warnings:
-Warning 1196 Some non-transactional changed tables couldn't be rolled back
SELECT MAX(f1) FROM t1;
MAX(f1)
5
@@ -579,8 +577,6 @@ MAX(f1)
-------- switch to master -------
ROLLBACK;
-Warnings:
-Warning 1196 Some non-transactional changed tables couldn't be rolled back
SELECT MAX(f1) FROM t1;
MAX(f1)
8
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 4dfdd65339a..db82016f398 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -1,5 +1,5 @@
drop database if exists mysqltest;
-drop view if exists v1;
+drop view if exists v1,v2,v3;
grant create view on test.* to test@localhost;
show grants for test@localhost;
Grants for test@localhost
View Lorry Controller Status