← Back to branch summary

~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

« back to all changes in this revision

Viewing changes to mysql-test/t/insert_update.test

  • Committer: Bazaar Package Importer
  • Author(s): Norbert Tretkowski
  • Date: 2010-03-17 14:56:02 UTC
  • Revision ID: james.westby@ubuntu.com-20100317145602-x7e30l1b2sb5s6w6
Tags: upstream-5.1.45
Import upstream version 5.1.45

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/t/insert_update.test
 
1
--disable_warnings
 
2
DROP TABLE IF EXISTS t1, t2;
 
3
--enable_warnings
 
4
 
 
5
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
 
6
INSERT t1 VALUES (1,2,10), (3,4,20);
 
7
INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE c=c+100;
 
8
SELECT * FROM t1;
 
9
INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100;
 
10
SELECT * FROM t1;
 
11
INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000;
 
12
SELECT * FROM t1;
 
13
INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000;
 
14
SELECT * FROM t1;
 
15
-- error ER_DUP_ENTRY
 
16
INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4;
 
17
SELECT * FROM t1;
 
18
TRUNCATE TABLE t1;
 
19
INSERT t1 VALUES (1,2,10), (3,4,20);
 
20
INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100;
 
21
SELECT * FROM t1;
 
22
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
 
23
SELECT * FROM t1;
 
24
INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
 
25
SELECT *, VALUES(a) FROM t1;
 
26
explain extended SELECT *, VALUES(a) FROM t1;
 
27
explain extended select * from t1 where values(a);
 
28
DROP TABLE t1;
 
29
 
 
30
#
 
31
# test for Bug #2709 "Affected Rows for ON DUPL.KEY undocumented, 
 
32
#                                                 perhaps illogical"
 
33
#
 
34
create table t1(a int primary key, b int);
 
35
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
 
36
select * from t1;
 
37
 
 
38
--enable_info
 
39
insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18)
 
40
 on duplicate key update b=b+10;
 
41
--disable_info
 
42
 
 
43
select * from t1;
 
44
 
 
45
enable_info;
 
46
replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29);
 
47
disable_info;
 
48
 
 
49
select * from t1;
 
50
drop table t1;
 
51
 
 
52
# WorkLog #2274 - enable INSERT .. SELECT .. UPDATE syntax
 
53
# Same tests as beginning of this test except that insert source
 
54
# is a result from a select statement
 
55
#
 
56
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
 
57
INSERT t1 VALUES (1,2,10), (3,4,20);
 
58
INSERT t1 SELECT 5,6,30 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
 
59
SELECT * FROM t1;
 
60
INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
 
61
SELECT * FROM t1;
 
62
INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
 
63
SELECT * FROM t1;
 
64
INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
 
65
SELECT * FROM t1;
 
66
-- error ER_DUP_ENTRY
 
67
INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
 
68
SELECT * FROM t1;
 
69
TRUNCATE TABLE t1;
 
70
INSERT t1 VALUES (1,2,10), (3,4,20);
 
71
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
 
72
# column names deliberately clash with columns in t1 (Bug#8147)
 
73
INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1);
 
74
INSERT t2 VALUES (2,1,11,2), (7,4,40,2);
 
75
INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=t1.c+100;
 
76
SELECT * FROM t1;
 
77
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
 
78
SELECT * FROM t1;
 
79
--error ER_NON_UNIQ_ERROR
 
80
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
 
81
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
 
82
SELECT *, VALUES(a) FROM t1;
 
83
DROP TABLE t1;
 
84
DROP TABLE t2;
 
85
 
 
86
#
 
87
# Bug#9725 - "disapearing query/hang" and "unknown error" with "on duplicate key update"
 
88
# INSERT INGORE...UPDATE gives bad error or breaks protocol.
 
89
#
 
90
create table t1 (a int not null unique) engine=myisam;
 
91
insert into t1 values (1),(2);
 
92
insert ignore into t1 select 1 on duplicate key update a=2;
 
93
select * from t1;
 
94
insert ignore into t1 select a from t1 as t2 on duplicate key update a=t1.a+1 ;
 
95
select * from t1;
 
96
insert into t1 select 1 on duplicate key update a=2;
 
97
select * from t1;
 
98
--error ER_NON_UNIQ_ERROR
 
99
insert into t1 select a from t1 on duplicate key update a=a+1 ;
 
100
--error ER_NON_UNIQ_ERROR
 
101
insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ;
 
102
drop table t1;
 
103
 
 
104
#
 
105
# Bug#10109 - INSERT .. SELECT ... ON DUPLICATE KEY UPDATE fails
 
106
# Bogus "Duplicate columns" error message
 
107
#
 
108
 
 
109
CREATE TABLE t1 (
 
110
  a BIGINT(20) NOT NULL DEFAULT 0,
 
111
  PRIMARY KEY  (a)
 
112
) ENGINE=MyISAM;
 
113
 
 
114
INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ;
 
115
 
 
116
DROP TABLE t1;
 
117
 
 
118
#
 
119
# Bug#21555: incorrect behavior with INSERT ... ON DUPL KEY UPDATE and VALUES
 
120
#
 
121
 
 
122
 
 
123
# End of 4.1 tests
 
124
CREATE TABLE t1
 
125
(
 
126
  a   BIGINT UNSIGNED,
 
127
  b   BIGINT UNSIGNED,
 
128
  PRIMARY KEY (a)
 
129
);
 
130
 
 
131
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b =
 
132
  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
 
133
SELECT * FROM t1;
 
134
INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b =
 
135
  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
 
136
SELECT * FROM t1;
 
137
INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = 
 
138
  IF(VALUES(b) > t1.b, VALUES(b), t1.b);
 
139
SELECT * FROM t1;
 
140
 
 
141
DROP TABLE t1;
 
142
 
 
143
#
 
144
# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving.
 
145
#
 
146
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
 
147
--error ER_BAD_FIELD_ERROR
 
148
INSERT INTO t1 SELECT 1, j;
 
149
DROP TABLE t1;
 
150
 
 
151
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
 
152
CREATE TABLE t2 (a INT, b INT);
 
153
CREATE TABLE t3 (a INT, c INT);
 
154
INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 
 
155
  ON DUPLICATE KEY UPDATE j= a;
 
156
DROP TABLE t1,t2,t3;
 
157
 
 
158
CREATE TABLE t1 (i INT PRIMARY KEY, j INT);
 
159
CREATE TABLE t2 (a INT);
 
160
INSERT INTO t1 VALUES (1, 1);
 
161
INSERT INTO t2 VALUES (1), (3);
 
162
--error ER_BAD_FIELD_ERROR
 
163
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
 
164
DROP TABLE t1,t2;
 
165
 
 
166
#
 
167
# Bug #26261: Missing default value isn't noticed in 
 
168
#   insert ... on duplicate key update
 
169
#
 
170
SET SQL_MODE = 'TRADITIONAL';
 
171
 
 
172
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
 
173
 
 
174
--error ER_NO_DEFAULT_FOR_FIELD
 
175
INSERT INTO t1 (a) VALUES (1);
 
176
 
 
177
--error ER_NO_DEFAULT_FOR_FIELD
 
178
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
 
179
 
 
180
--error ER_NO_DEFAULT_FOR_FIELD
 
181
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
 
182
 
 
183
SELECT * FROM t1;
 
184
 
 
185
DROP TABLE t1;
 
186
 
 
187
#
 
188
# Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
 
189
#            touched but not actually changed.
 
190
#
 
191
CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
 
192
                 f2 VARCHAR(5) NOT NULL UNIQUE);
 
193
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
 
194
SELECT LAST_INSERT_ID();
 
195
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
 
196
SELECT LAST_INSERT_ID();
 
197
DROP TABLE t1;
 
198
 
 
199
#
 
200
# Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
 
201
#            NO_AUTO_VALUE_ON_ZERO mode.
 
202
#
 
203
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
 
204
CREATE TABLE `t1` (
 
205
  `id` int(11) PRIMARY KEY auto_increment,
 
206
  `f1` varchar(10) NOT NULL UNIQUE
 
207
);
 
208
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
 
209
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
210
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
 
211
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
212
SELECT LAST_INSERT_ID();
 
213
SELECT * FROM t1;
 
214
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
 
215
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
216
SELECT * FROM t1;
 
217
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
 
218
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
219
SELECT LAST_INSERT_ID();
 
220
SELECT * FROM t1;
 
221
INSERT IGNORE INTO t1 (f1) VALUES ("test3")
 
222
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
223
SELECT LAST_INSERT_ID();
 
224
SELECT * FROM t1;
 
225
DROP TABLE t1;
 
226
CREATE TABLE `t1` (
 
227
  `id` int(11) PRIMARY KEY auto_increment,
 
228
  `f1` varchar(10) NOT NULL UNIQUE
 
229
);
 
230
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
 
231
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
232
SELECT LAST_INSERT_ID();
 
233
SELECT * FROM t1;
 
234
INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
 
235
        ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
 
236
SELECT LAST_INSERT_ID();
 
237
SELECT * FROM t1;
 
238
DROP TABLE t1;
 
239
CREATE TABLE `t1` (
 
240
  `id` int(11) PRIMARY KEY auto_increment,
 
241
  `f1` varchar(10) NOT NULL UNIQUE,
 
242
  tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
 
243
);
 
244
INSERT INTO t1 (f1) VALUES ("test1");
 
245
SELECT id, f1 FROM t1;
 
246
REPLACE INTO t1 VALUES (0,"test1",null);
 
247
SELECT id, f1 FROM t1;
 
248
DROP TABLE t1;
 
249
SET SQL_MODE='';
 
250
 
 
251
#
 
252
# Bug#27954: multi-row INSERT ... ON DUPLICATE with duplicated
 
253
# row at the first place into table with AUTO_INCREMENT and
 
254
# additional UNIQUE key.
 
255
#
 
256
CREATE TABLE t1 (
 
257
  id INT AUTO_INCREMENT PRIMARY KEY,
 
258
  c1 CHAR(1) UNIQUE KEY,
 
259
  cnt INT DEFAULT 1
 
260
);
 
261
INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C');
 
262
SELECT * FROM t1;
 
263
INSERT  INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z')
 
264
  ON DUPLICATE KEY UPDATE cnt=cnt+1;
 
265
SELECT * FROM t1;
 
266
DROP TABLE t1;
 
267
 
 
268
#
 
269
# Bug#28000: INSERT IGNORE ... SELECT ... ON DUPLICATE
 
270
# with erroneous UPDATE: NOT NULL field with NULL value.
 
271
#
 
272
CREATE TABLE t1 (
 
273
  id INT AUTO_INCREMENT PRIMARY KEY,
 
274
  c1 INT NOT NULL,
 
275
  cnt INT DEFAULT 1
 
276
);
 
277
INSERT INTO t1 (id,c1) VALUES (1,10);
 
278
SELECT * FROM t1;
 
279
CREATE TABLE t2 (id INT, c1 INT);
 
280
INSERT INTO t2 VALUES (1,NULL), (2,2);
 
281
--error ER_BAD_NULL_ERROR
 
282
INSERT INTO t1 (id,c1) SELECT 1,NULL
 
283
  ON DUPLICATE KEY UPDATE c1=NULL;
 
284
SELECT * FROM t1;
 
285
INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL
 
286
  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
 
287
SELECT * FROM t1;
 
288
INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2
 
289
  ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1;
 
290
SELECT * FROM t1;
 
291
 
 
292
DROP TABLE t1;
 
293
DROP TABLE t2;
 
294
 
 
295
#
 
296
# Bug#28904: INSERT .. ON DUPLICATE was silently updating rows when it
 
297
#            shouldn't.
 
298
#
 
299
create table t1(f1 int primary key,
 
300
 f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP);
 
301
insert into t1(f1) values(1);
 
302
--replace_column 1 #
 
303
select @stamp1:=f2 from t1;
 
304
--sleep 2
 
305
insert into t1(f1) values(1) on duplicate key update f1=1;
 
306
--replace_column 1 #
 
307
select @stamp2:=f2 from t1;
 
308
select if( @stamp1 = @stamp2, "correct", "wrong");
 
309
drop table t1;