--disable_abort_on_error

--disable_warnings
DROP TABLE IF EXISTS t0, t_insert_good, t_insert_bad;
--disable_warnings
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;


--echo ############### 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;
--echo 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;
--echo 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;
#
--echo content of t_insert_good
SELECT * FROM t_insert_good;
--echo Please note the correct value combination of the first record 101 -- 1
--echo content of t_insert_bad
SELECT * FROM t_insert_bad;
--echo Please note the wrong   value combination of the first record 102 -- 1
--echo First/Last matching record within INSERT has f1 = 1, following record in table has f1 = 2
#
--echo ############### 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;
--echo 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;
--echo 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;
#
--echo content of t_insert_good
SELECT * FROM t_insert_good;
--echo Please note the correct value combinations 101 -- 1 and 102 -- 2
--echo content of t_insert_bad
SELECT * FROM t_insert_bad;
--echo Please note the wrong   value combination  104 -- 1
--echo First  matching record within INSERT has f1 = 1, following record in table has f1 = 2
--echo Last   matching record within INSERT has f1 = 2, following record in table has f1 = 4
#
--echo ############### 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;
--echo 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;
--echo 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;
#
--echo content of t_insert_good
SELECT * FROM t_insert_good;
--echo Please note the correct value combinations 101 -- 1 , 102 -- 2 and 104 -- 4
--echo content of t_insert_bad
SELECT * FROM t_insert_bad;
--echo Please note the wrong   value combination  110 -- 1
--echo First  matching record has f1 = 1, following record in table has f1 = 2
--echo Second matching record has f1 = 2, following record in table has f1 = 4
--echo Last   matching record has f1 = 4, following record in table has f1 = 10
#
--echo ############### 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;
--echo 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;
--echo 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;
#
--echo content of t_insert_good
SELECT * FROM t_insert_good;
--echo Please note the correct value combinations 101 -- 1 , 102 -- 2 , 104 -- 4 and 110 -- 10
--echo content of t_insert_bad
SELECT * FROM t_insert_bad;
--echo Please note the wrong   value combination  110 -- 1
--echo First  matching record has f1 = 1, following record in table has f1 = 2
--echo Second matching record has f1 = 2, following record in table has f1 = 4
--echo Third  matching record has f1 = 4, following record in table has f1 = 10
--echo Last   matching record has f1 = 10, the is no following record within the table.
#