162
162
--error ER_BAD_FIELD_ERROR
163
163
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
164
164
DROP TABLE t1,t2;
167
# Bug #26261: Missing default value isn't noticed in
168
# insert ... on duplicate key update
170
SET SQL_MODE = 'TRADITIONAL';
172
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
175
INSERT INTO t1 (a) VALUES (1);
178
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
181
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
188
# Bug#27033: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE if rows were
189
# touched but not actually changed.
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();
200
# Bug#23233: 0 as LAST_INSERT_ID() after INSERT .. ON DUPLICATE in the
201
# NO_AUTO_VALUE_ON_ZERO mode.
203
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
205
`id` int(11) PRIMARY KEY auto_increment,
206
`f1` varchar(10) NOT NULL UNIQUE
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();
214
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
215
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
217
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
218
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
219
SELECT LAST_INSERT_ID();
221
INSERT IGNORE INTO t1 (f1) VALUES ("test3")
222
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
223
SELECT LAST_INSERT_ID();
227
`id` int(11) PRIMARY KEY auto_increment,
228
`f1` varchar(10) NOT NULL UNIQUE
230
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
231
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
232
SELECT LAST_INSERT_ID();
234
INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
235
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
236
SELECT LAST_INSERT_ID();
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
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;