1 2# 3# Test of update statement that uses many tables, 4# --max_heap_table_size=1 5# 6 7--disable_warnings 8drop table if exists t1, t2; 9--enable_warnings 10 11--echo # 12--echo # Bug #36676: multiupdate using LEFT JOIN updates only 13--echo # first row or fails with an error: 14--echo # ERROR 1022 (23000): Can't write; duplicate key in table '' 15--echo # 16 17--echo 18 19--echo # 20--echo # Multiupdate creates MyISAM temporary table without MEMORY table 21--echo # 22 23CREATE TABLE t1 (ID INT); 24CREATE TABLE t2 (ID INT, 25 s1 TEXT, s2 TEXT, s3 VARCHAR(10), s4 TEXT, s5 VARCHAR(10)); 26 27INSERT INTO t1 VALUES (1),(2); 28INSERT INTO t2 VALUES (1,'test', 'test', 'test', 'test', 'test'), 29 (2,'test', 'test', 'test', 'test', 'test'); 30 31SELECT * FROM t1 LEFT JOIN t2 USING(ID); 32UPDATE t1 LEFT JOIN t2 USING(ID) SET s1 = 'changed'; 33UPDATE t1 JOIN t2 USING(ID) SET s2 = 'changed'; 34UPDATE t1 LEFT JOIN t2 USING(ID) SET s3 = 'changed'; 35UPDATE t1 LEFT JOIN t2 USING(ID) SET s4 = 'changed', s5 = 'changed'; 36SELECT * FROM t1 LEFT JOIN t2 USING(ID); 37 38DROP TABLE t1, t2; 39 40--echo # 41--echo # Multiupdate creates temporary MyISAM table from MEMORY table 42--echo # 43 44CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY); 45CREATE TABLE t2 (id INT, s1 CHAR(255)); 46 47# insert [1..64] into table `t1` 48INSERT INTO t1 VALUES (0), (0), (0), (0), (0), (0), (0), (0); 49INSERT INTO t1 (SELECT 0 FROM t1); 50INSERT INTO t1 (SELECT 0 FROM t1); 51INSERT INTO t1 (SELECT 0 FROM t1); 52 53INSERT INTO t2 (SELECT ID, 'a' FROM t1); 54 55UPDATE t1 LEFT JOIN t2 USING(id) SET s1 = 'b'; 56 57SELECT DISTINCT s1 FROM t1 LEFT JOIN t2 USING(id); 58 59DROP TABLE t1, t2; 60 61 62--echo # End of 5.0 tests 63