2
# Problem with INSERT ... SELECT
6
drop table if exists t1,t2,t3;
9
create table t1 (bandID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, payoutID SMALLINT UNSIGNED NOT NULL);
10
insert into t1 (bandID,payoutID) VALUES (1,6),(2,6),(3,4),(4,9),(5,10),(6,1),(7,12),(8,12);
11
create table t2 (payoutID SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
12
insert into t2 (payoutID) SELECT DISTINCT payoutID FROM t1;
14
insert into t2 (payoutID) SELECT payoutID+10 FROM t1;
15
insert ignore into t2 (payoutID) SELECT payoutID+10 FROM t1;
20
# bug in bulk insert optimization
21
# test case by Fournier Jocelyn <joc@presence-pc.com>
25
`numeropost` bigint(20) unsigned NOT NULL default '0',
26
`icone` tinyint(4) unsigned NOT NULL default '0',
27
`numreponse` bigint(20) unsigned NOT NULL auto_increment,
28
`contenu` text NOT NULL,
29
`pseudo` varchar(50) NOT NULL default '',
30
`date` datetime NOT NULL default '0000-00-00 00:00:00',
31
`ip` bigint(11) NOT NULL default '0',
32
`signature` tinyint(1) unsigned NOT NULL default '0',
33
PRIMARY KEY (`numeropost`,`numreponse`)
36
KEY `pseudo` (`pseudo`),
37
KEY `numreponse` (`numreponse`)
41
`numeropost` bigint(20) unsigned NOT NULL default '0',
42
`icone` tinyint(4) unsigned NOT NULL default '0',
43
`numreponse` bigint(20) unsigned NOT NULL auto_increment,
44
`contenu` text NOT NULL,
45
`pseudo` varchar(50) NOT NULL default '',
46
`date` datetime NOT NULL default '0000-00-00 00:00:00',
47
`ip` bigint(11) NOT NULL default '0',
48
`signature` tinyint(1) unsigned NOT NULL default '0',
49
PRIMARY KEY (`numeropost`,`numreponse`),
52
KEY `pseudo` (`pseudo`),
53
KEY `numreponse` (`numreponse`)
57
(numeropost,icone,numreponse,contenu,pseudo,date,ip,signature) VALUES
58
(9,1,56,'test','joce','2001-07-25 13:50:53'
62
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
63
SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM t2
64
WHERE numeropost=9 ORDER BY numreponse ASC;
66
show variables like '%bulk%';
68
INSERT INTO t1 (numeropost,icone,contenu,pseudo,date,signature,ip)
69
SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM t2
70
WHERE numeropost=9 ORDER BY numreponse ASC;
75
# Test of insert ... select from same table
78
create table t1 (a int not null);
79
create table t2 (a int not null);
80
insert into t1 values (1);
81
insert into t1 values (a+2);
82
insert into t1 values (a+3);
83
insert into t1 values (4),(a+5);
84
insert into t1 select * from t1;
86
insert into t1 select * from t1 as t2;
88
insert into t2 select * from t1 as t2;
90
insert into t1 select t2.a from t1,t2;
93
insert into t1 select * from t1,t1;
97
# test replace ... select
100
create table t1 (a int not null primary key, b char(10));
101
create table t2 (a int not null, b char(10));
102
insert into t1 values (1,"t1:1"),(3,"t1:3");
103
insert into t2 values (2,"t2:2"), (3,"t2:3");
105
insert into t1 select * from t2;
107
# REPLACE .. SELECT is not yet supported by PS
108
replace into t1 select * from t2;
113
# Test that caused uninitialized memory access in auto_increment_key update
116
CREATE TABLE t1 ( USID INTEGER UNSIGNED, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User CHAR(32) NOT NULL DEFAULT '<UNKNOWN>', NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL);
117
CREATE TABLE t2 ( USID INTEGER UNSIGNED AUTO_INCREMENT, ServerID TINYINT UNSIGNED, State ENUM ('unknown', 'Access-Granted', 'Session-Active', 'Session-Closed' ) NOT NULL DEFAULT 'unknown', SessionID CHAR(32), User TEXT NOT NULL, NASAddr INTEGER UNSIGNED, NASPort INTEGER UNSIGNED, NASPortType INTEGER UNSIGNED, ConnectSpeed INTEGER UNSIGNED, CarrierType CHAR(32), CallingStationID CHAR(32), CalledStationID CHAR(32), AssignedAddr INTEGER UNSIGNED, SessionTime INTEGER UNSIGNED, PacketsIn INTEGER UNSIGNED, OctetsIn INTEGER UNSIGNED, PacketsOut INTEGER UNSIGNED, OctetsOut INTEGER UNSIGNED, TerminateCause INTEGER UNSIGNED, UnauthTime TINYINT UNSIGNED, AccessRequestTime DATETIME, AcctStartTime DATETIME, AcctLastTime DATETIME, LastModification TIMESTAMP NOT NULL, INDEX(USID,ServerID,NASAddr,SessionID), INDEX(AssignedAddr));
118
INSERT INTO t1 VALUES (39,42,'Access-Granted','46','491721000045',2130706433,17690,NULL,NULL,'Localnet','491721000045','49172200000',754974766,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2003-07-18 00:11:21',NULL,NULL,20030718001121);
119
INSERT INTO t2 SELECT USID, ServerID, State, SessionID, User, NASAddr, NASPort, NASPortType, ConnectSpeed, CarrierType, CallingStationID, CalledStationID, AssignedAddr, SessionTime, PacketsIn, OctetsIn, PacketsOut, OctetsOut, TerminateCause, UnauthTime, AccessRequestTime, AcctStartTime, AcctLastTime, LastModification from t1 LIMIT 1;
123
# Another problem from Bug #2012
128
Type tinyint(3) unsigned NOT NULL auto_increment,
129
Field int(10) unsigned NOT NULL,
130
Count int(10) unsigned NOT NULL,
131
UNIQUE KEY Month (Month,Type,Field)
134
insert into t1 Values
135
(20030901, 1, 1, 100),
136
(20030901, 1, 2, 100),
137
(20030901, 2, 1, 100),
138
(20030901, 2, 2, 100),
139
(20030901, 3, 1, 100);
143
Select null, Field, Count From t1 Where Month=20030901 and Type=2;
145
create table t2(No int not null, Field int not null, Count int not null);
147
insert into t2 Select 0, Field, Count From t1 Where Month=20030901 and Type=2;
154
# BUG#6034 - Error code 124: Wrong medium type
157
ID int(11) NOT NULL auto_increment,
158
NO int(11) NOT NULL default '0',
159
SEQ int(11) NOT NULL default '0',
163
INSERT INTO t1 (SEQ, NO) SELECT "1" AS SEQ, IF(MAX(NO) IS NULL, 0, MAX(NO)) + 1 AS NO FROM t1 WHERE (SEQ = 1);
164
select SQL_BUFFER_RESULT * from t1 WHERE (SEQ = 1);
168
# Bug#10886 - Have to restore default values after update ON DUPLICATE KEY
170
create table t1 (f1 int);
171
create table t2 (ff1 int unique, ff2 int default 1);
172
insert into t1 values (1),(1),(2);
173
insert into t2(ff1) select f1 from t1 on duplicate key update ff2=ff2+1;
177
# BUGS #9728 - 'Decreased functionality in "on duplicate key update"'
178
# #8147 - 'a column proclaimed ambigous in INSERT ... SELECT .. ON
181
create table t1 (a int unique);
182
create table t2 (a int, b int);
183
create table t3 (c int, d int);
184
insert into t1 values (1),(2);
185
insert into t2 values (1,2);
186
insert into t3 values (1,6),(3,7);
188
insert into t1 select a from t2 on duplicate key update a= t1.a + t2.b;
190
insert into t1 select a+1 from t2 on duplicate key update t1.a= t1.a + t2.b+1;
192
insert into t1 select t3.c from t3 on duplicate key update a= a + t3.d;
194
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= a + 10;
198
insert into t1 select t2.a from t2 on duplicate key update a= a + t2.b;
200
insert into t1 select t2.a from t2 on duplicate key update t2.a= a + t2.b;
202
insert into t1 select t2.a from t2 group by t2.a on duplicate key update a= t1.a + t2.b;
206
# Bug #12695 Item_func_isnull::update_used_tables() did not update
208
create table t1(f1 varchar(5) key);
209
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
210
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
211
insert into t1(f1) select if(max(f1) is null, '2000',max(f1)+1) from t1;
216
# Bug #13392 values() fails with 'ambiguous' or returns NULL
217
# with ON DUPLICATE and SELECT
218
create table t1(x int, y int);
219
create table t2(x int, z int);
220
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(x);
222
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(z);
224
insert into t1(x,y) select x,z from t2 on duplicate key update x=values(t2.x);
228
# Bug #9676: INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big
232
#Note: not an exsaustive test : just a check of the code path.
233
CREATE TABLE t1 (a int PRIMARY KEY);
234
INSERT INTO t1 values (1), (2);
237
INSERT INTO t1 SELECT a + 2 FROM t1 LIMIT 1;
238
show status like 'Handler_read%';
243
# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty
247
f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
248
f2 varchar(100) NOT NULL default ''
251
f1 varchar(10) NOT NULL default '',
252
f2 char(3) NOT NULL default '',
254
KEY `k1` (`f2`, `f1`)
257
INSERT INTO t1 values(NULL, '');
258
INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
259
SELECT COUNT(*) FROM t1;
261
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
264
SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
266
SELECT COUNT(*) FROM t1;
273
# Bug #18080: INSERT ... SELECT ... JOIN results in ambiguous field list error
275
CREATE TABLE t1 (x int, y int);
276
CREATE TABLE t2 (z int, y int);
277
CREATE TABLE t3 (a int, b int);
278
INSERT INTO t3 (SELECT x, y FROM t1 JOIN t2 USING (y) WHERE z = 1);
279
DROP TABLE IF EXISTS t1,t2,t3;
282
# Bug #21774: Column count doesn't match value count at row x
284
CREATE DATABASE bug21774_1;
285
CREATE DATABASE bug21774_2;
287
CREATE TABLE bug21774_1.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
288
CREATE TABLE bug21774_2.t1(id VARCHAR(10) NOT NULL,label VARCHAR(255));
289
CREATE TABLE bug21774_1.t2(id VARCHAR(10) NOT NULL,label VARCHAR(255));
291
INSERT INTO bug21774_2.t1 SELECT t1.* FROM bug21774_1.t1;
294
INSERT INTO bug21774_2.t1 SELECT t1.* FROM t1;
296
DROP DATABASE bug21774_1;
297
DROP DATABASE bug21774_2;
301
# Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
304
create table t1(f1 int primary key, f2 int);
306
insert into t1 values (1,1);
307
insert into t1 values (1,1) on duplicate key update f2=1;
308
insert into t1 values (1,1) on duplicate key update f2=2;
314
# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE
316
CREATE TABLE t1 (f1 INT, f2 INT );
317
CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT);
318
INSERT INTO t1 VALUES (1,1),(2,2),(10,10);
319
INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1;
320
INSERT INTO t2 (f1, f2)
321
SELECT f1, f1 FROM t2 src WHERE f1 < 2
322
ON DUPLICATE KEY UPDATE f1 = 100 + src.f1;
327
# Bug #29095: incorrect pushing of LIMIT into the temporary
328
# table ignoring ORDER BY clause
332
id INT AUTO_INCREMENT PRIMARY KEY,
334
join_id INT DEFAULT 0);
336
INSERT INTO t1 (prev_id) VALUES (NULL), (1), (2);
339
CREATE TABLE t2 (join_id INT);
340
INSERT INTO t2 (join_id) VALUES (0);
342
INSERT INTO t1 (prev_id) SELECT id
343
FROM t2 LEFT JOIN t1 ON t1.join_id = t2.join_id
344
ORDER BY id DESC LIMIT 1;
350
--echo # Bug#30384: Having SQL_BUFFER_RESULT option in the
351
--echo # CREATE .. KEY(..) .. SELECT led to creating corrupted index.
353
create table t1(f1 int);
354
insert into t1 values(1),(2),(3);
355
create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1;
356
check table t2 extended;
358
--echo ##################################################################
added
removed
mysql-test/t/insert_select.test
