DROP TABLE IF EXISTS t0, t_insert_good, t_insert_bad;
CREATE TABLE t0            ( f1 BIGINT, f2 BIGINT                 );
CREATE TABLE t_insert_good ( f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1));
CREATE TABLE t_insert_bad  ( f1 BIGINT, f2 BIGINT, PRIMARY KEY(f1));
INSERT INTO t0 VALUES(1,1),(2,2),(4,4),(10,10);
SELECT f1, f2 FROM t0;
f1	f2
1	1
2	2
4	4
10	10
############### Testcase with WHERE f1 < 2 ###########################
DELETE FROM t_insert_good;
DELETE FROM t_insert_bad;
INSERT INTO t_insert_good (f1, f2) SELECT f1, f2 FROM t0;
INSERT INTO t_insert_bad  (f1, f2) SELECT f1, f2 FROM t0;
Good working Insert, data source(t0) and insert target(t_insert_good) are different tables.
INSERT INTO t_insert_good (f1, f2)
SELECT f1, f1 FROM t0 source_tab
WHERE f1 < 2
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
Bad working Insert, data source and insert target is equal (t_insert_bad)
INSERT INTO t_insert_bad (f1, f2)
SELECT f1, f1 FROM t_insert_bad source_tab
WHERE f1 < 2
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
content of t_insert_good
SELECT * FROM t_insert_good;
f1	f2
101	1
2	2
4	4
10	10
Please note the correct value combination of the first record 101 -- 1
content of t_insert_bad
SELECT * FROM t_insert_bad;
f1	f2
102	1
2	2
4	4
10	10
Please note the wrong   value combination of the first record 102 -- 1
First/Last matching record within INSERT has f1 = 1, following record in table has f1 = 2
############### Testcase with WHERE f1 < 3 ###########################
DELETE FROM t_insert_good;
DELETE FROM t_insert_bad;
INSERT INTO t_insert_good (f1, f2) SELECT f1, f2 FROM t0;
INSERT INTO t_insert_bad  (f1, f2) SELECT f1, f2 FROM t0;
Good working Insert, data source(t0) and insert target(t_insert_good) are different tables.
INSERT INTO t_insert_good (f1, f2)
SELECT f1, f1 FROM t0 source_tab
WHERE f1 < 3
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
Bad working Insert, data source and insert target is equal (t_insert_bad)
INSERT INTO t_insert_bad (f1, f2)
SELECT f1, f1 FROM t_insert_bad source_tab
WHERE f1 < 3
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
ERROR 23000: Duplicate entry '104' for key 1
content of t_insert_good
SELECT * FROM t_insert_good;
f1	f2
101	1
102	2
4	4
10	10
Please note the correct value combinations 101 -- 1 and 102 -- 2
content of t_insert_bad
SELECT * FROM t_insert_bad;
f1	f2
104	1
2	2
4	4
10	10
Please note the wrong   value combination  104 -- 1
First  matching record within INSERT has f1 = 1, following record in table has f1 = 2
Last   matching record within INSERT has f1 = 2, following record in table has f1 = 4
############### Testcase with WHERE f1 < 5 ###########################
DELETE FROM t_insert_good;
DELETE FROM t_insert_bad;
INSERT INTO t_insert_good (f1, f2) SELECT f1, f2 FROM t0;
INSERT INTO t_insert_bad  (f1, f2) SELECT f1, f2 FROM t0;
Good working Insert, data source(t0) and insert target(t_insert_good) are different tables.
INSERT INTO t_insert_good (f1, f2)
SELECT f1, f1 FROM t0 source_tab
WHERE f1 < 5
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
Bad working Insert, data source and insert target is equal (t_insert_bad)
INSERT INTO t_insert_bad (f1, f2)
SELECT f1, f1 FROM t_insert_bad source_tab
WHERE f1 < 5
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
ERROR 23000: Duplicate entry '110' for key 1
content of t_insert_good
SELECT * FROM t_insert_good;
f1	f2
101	1
102	2
104	4
10	10
Please note the correct value combinations 101 -- 1 , 102 -- 2 and 104 -- 4
content of t_insert_bad
SELECT * FROM t_insert_bad;
f1	f2
110	1
2	2
4	4
10	10
Please note the wrong   value combination  110 -- 1
First  matching record has f1 = 1, following record in table has f1 = 2
Second matching record has f1 = 2, following record in table has f1 = 4
Last   matching record has f1 = 4, following record in table has f1 = 10
############### Testcase with WHERE f1 < 11 ###########################
DELETE FROM t_insert_good;
DELETE FROM t_insert_bad;
INSERT INTO t_insert_good (f1, f2) SELECT f1, f2 FROM t0;
INSERT INTO t_insert_bad  (f1, f2) SELECT f1, f2 FROM t0;
Good working Insert, data source(t0) and insert target(t_insert_good) are different tables.
INSERT INTO t_insert_good (f1, f2)
SELECT f1, f1 FROM t0 source_tab
WHERE f1 < 11
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
Bad working Insert, data source and insert target is equal (t_insert_bad)
INSERT INTO t_insert_bad (f1, f2)
SELECT f1, f1 FROM t_insert_bad source_tab
WHERE f1 < 11
ON DUPLICATE KEY UPDATE f1 = 100 + source_tab.f1;
ERROR 23000: Duplicate entry '110' for key 1
content of t_insert_good
SELECT * FROM t_insert_good;
f1	f2
101	1
102	2
104	4
110	10
Please note the correct value combinations 101 -- 1 , 102 -- 2 , 104 -- 4 and 110 -- 10
content of t_insert_bad
SELECT * FROM t_insert_bad;
f1	f2
110	1
2	2
4	4
10	10
Please note the wrong   value combination  110 -- 1
First  matching record has f1 = 1, following record in table has f1 = 2
Second matching record has f1 = 2, following record in table has f1 = 4
Third  matching record has f1 = 4, following record in table has f1 = 10
Last   matching record has f1 = 10, the is no following record within the table.