| Bug #100561 | Renaming the table will cause inconsistent results of other queries | ||
|---|---|---|---|
| Submitted: | 18 Aug 2020 12:40 | Modified: | 18 Aug 2020 14:54 |
| Reporter: | zhijun long | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 2020 13:39]
MySQL Verification Team
Hi Mr. Long, This is what I get with the first run of your query, where line 16 is commented out and line 17 remains: --------------------------------------------------------- pk col_int_nokey col_int_key 18 0 1 23 8 1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 NULL system PRIMARY,col_int_key NULL NULL NULL 1 100.00 NULL 1 SIMPLE t1a NULL ref col_int_key col_int_key 4 const 1 100.00 Start temporary 1 SIMPLE t1b NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (hash join) 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (hash join) pk col_int_nokey col_int_key 18 0 1 23 8 1 pk col_int_nokey col_int_key 18 0 1 23 8 1 --------------------------------------------------------- And this is what I get when I uncomment line 16 and comment out line 17: --------------------------------------------------------- pk col_int_nokey col_int_key 18 0 1 23 8 1 id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 NULL system PRIMARY,col_int_key NULL NULL NULL 1 100.00 NULL 1 SIMPLE t1a NULL ref col_int_key col_int_key 4 const 1 100.00 Start temporary 1 SIMPLE t1b NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (hash join) 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (hash join) pk col_int_nokey col_int_key 18 0 1 23 8 1 pk col_int_nokey col_int_key 18 0 1 23 8 1 --------------------------------------------------------- As you can see the results are exactly the same. I can run it as a test case and if I repeat the behaviour, that this report could be verified as the lowest priority, test case bug, of the Severity S7. In that case it would not be a bug in our server, but in our test case software. Can't repeat it as a server bug, which is what you reported it to be.
[18 Aug 2020 14:54]
zhijun long
Use the following script to repeat. ###### case1: drop table ####### mysql-test/t/case1.test ``` 1 SET optimizer_switch='firstmatch=off'; 2 3 CREATE TABLE t1 ( 4 pk int NOT NULL, 5 col_int_key int NOT NULL, 6 col_varchar_nokey varchar(1) NOT NULL, 7 col_varchar_key varchar(1) NOT NULL, 8 PRIMARY KEY(pk), 9 KEY col_int_key(col_int_key), 10 KEY col_varchar_key(col_varchar_key, col_int_key) 11 ) charset utf8mb4 engine=INNODB; 12 13 INSERT INTO t1 VALUES (1,7,'a','a'), (2,0,'v','v'), (3,9,'c','c'), (4,3,'m','m'), (5,2,'a','a'), 14 (6,1,'d','d'), (7,8,'y','y'), (8,6,'t','t'), (11,7,'a','x'), (12,0,'v','v'), (13,9,'c','c'), 15 (14,3,'m','m'), (15,2,'a','x'), (16,1,'d','d'), (17,8,'y','y'); 16 DROP TABLE t1; 17 18 CREATE TABLE t3 ( 19 pk int NOT NULL, 20 col_int_nokey int NOT NULL, 21 col_int_key int NOT NULL, 22 PRIMARY KEY (pk), 23 KEY col_int_key (col_int_key) 24 ) ENGINE=MyIsam; 25 26 INSERT INTO t3 VALUES (10,1,7), (13,7,3), (18,0,1), (23,8,1); 27 CREATE TABLE t4 ( 28 pk int NOT NULL, 29 col_int_key int NOT NULL, 30 PRIMARY KEY (pk), 31 KEY col_int_key (col_int_key) 32 ) ENGINE=MyIsam; 33 INSERT INTO t4 VALUES (1,7); 34 35 let $query= 36 SELECT t1a.* 37 FROM t3 AS t1a 38 JOIN t3 AS t1b USING ( col_int_nokey ) 39 WHERE t1a.col_int_key IN ( 40 SELECT pk 41 FROM t4 42 WHERE col_int_key IN ( 43 SELECT col_int_nokey 44 FROM t3 45 ) 46 ); 47 48 eval EXPLAIN $query; 49 --sorted_result 50 eval $query; 51 ALTER TABLE t3 ENGINE=Innodb; 52 ALTER TABLE t4 ENGINE=Innodb; 53 eval $query; 54 DROP TABLE t3, t4; ``` ###### case2: rename table ####### [lzj@ecs]$ diff ../../mysql-test/t/case1.test ../../mysql-test/t/case2.test 16c16 < DROP TABLE t1; --- > RENAME TABLE t1 TO t2; 54c54 < DROP TABLE t3, t4; --- > DROP TABLE t2, t3, t4; Compare the result files of the above two scenarios. [lzj@ecs]$ diff ../../mysql-test/r/case1.result ../../mysql-test/r/case2.result 14c14 < DROP TABLE t1; --- > RENAME TABLE t1 TO t2; 76d75 < 18 0 1 78c77,78 < DROP TABLE t3, t4; --- > 18 0 1 > DROP TABLE t2, t3, t4;
[19 Aug 2020 12:05]
MySQL Verification Team
Hi Mr. long, Here is a new run for the first variant: id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 NULL system PRIMARY,col_int_key NULL NULL NULL 1 100.00 NULL 1 SIMPLE t1a NULL ref col_int_key col_int_key 4 const 1 100.00 Start temporary 1 SIMPLE t1b NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (hash join) 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (hash join) pk col_int_nokey col_int_key 18 0 1 23 8 1 pk col_int_nokey col_int_key 18 0 1 23 8 1 And here is the second variant output: id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t4 NULL system PRIMARY,col_int_key NULL NULL NULL 1 100.00 NULL 1 SIMPLE t1a NULL ref col_int_key col_int_key 4 const 1 100.00 Start temporary 1 SIMPLE t1b NULL ALL NULL NULL NULL NULL 4 25.00 Using where; Using join buffer (hash join) 1 SIMPLE t3 NULL ALL NULL NULL NULL NULL 4 25.00 Using where; End temporary; Using join buffer (hash join) pk col_int_nokey col_int_key 18 0 1 23 8 1 pk col_int_nokey col_int_key 18 0 1 23 8 1 They are the same. Hence my previous questions still remain unanswered .....
[31 Aug 2020 7:18]
zhijun long
case1
Attachment: case1_drop.test (application/octet-stream, text), 1.31 KiB.
[31 Aug 2020 7:20]
zhijun long
case2
Attachment: case2_rename.test (application/octet-stream, text), 1.32 KiB.
[21 Sep 2020 14:29]
MySQL Verification Team
HI Mr. long, I have tested both of your test cases and they all return the same result. Still can't repeat.
Description: Hi, all Renaming the table will cause inconsistent results of other queries, although there is no relationship between these tables. ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ [ 50%] main.rename_tmp [ fail ] Test ended at 2020-08-18 20:26:44 CURRENT_TEST: main.rename_tmp --- ../mysql-8.0.21/mysql-test/r/rename_tmp.result 2020-08-18 15:26:00.871000000 +0300 +++ ../mysql-8.0.21/cmake_build/mysql-test/var/log/rename_tmp.reject 2020-08-18 15:26:44.543000000 +0300 @@ -11,7 +11,7 @@ INSERT INTO t1 VALUES (1,7,'a','a'), (2,0,'v','v'), (3,9,'c','c'), (4,3,'m','m'), (5,2,'a','a'), (6,1,'d','d'), (7,8,'y','y'), (8,6,'t','t'), (11,7,'a','x'), (12,0,'v','v'), (13,9,'c','c'), (14,3,'m','m'), (15,2,'a','x'), (16,1,'d','d'), (17,8,'y','y'); -DROP TABLE t1; +RENAME TABLE t1 TO t2; CREATE TABLE t3 ( pk int NOT NULL, col_int_nokey int NOT NULL, @@ -73,6 +73,6 @@ ) ); pk col_int_nokey col_int_key -18 0 1 23 8 1 -DROP TABLE t3, t4; +18 0 1 +DROP TABLE t2, t3, t4; mysqltest: Result content mismatch How to repeat: mtr case ``` 1 SET optimizer_switch='firstmatch=off'; 2 3 CREATE TABLE t1 ( 4 pk int NOT NULL, 5 col_int_key int NOT NULL, 6 col_varchar_nokey varchar(1) NOT NULL, 7 col_varchar_key varchar(1) NOT NULL, 8 PRIMARY KEY(pk), 9 KEY col_int_key(col_int_key), 10 KEY col_varchar_key(col_varchar_key, col_int_key) 11 ) charset utf8mb4 engine=INNODB; 12 13 INSERT INTO t1 VALUES (1,7,'a','a'), (2,0,'v','v'), (3,9,'c','c'), (4,3,'m','m'), (5,2,'a','a'), 14 (6,1,'d','d'), (7,8,'y','y'), (8,6,'t','t'), (11,7,'a','x'), (12,0,'v','v'), (13,9,'c','c'), 15 (14,3,'m','m'), (15,2,'a','x'), (16,1,'d','d'), (17,8,'y','y'); 16 #RENAME TABLE t1 TO t2; 17 DROP TABLE t1; 18 19 CREATE TABLE t3 ( 20 pk int NOT NULL, 21 col_int_nokey int NOT NULL, 22 col_int_key int NOT NULL, 23 PRIMARY KEY (pk), 24 KEY col_int_key (col_int_key) 25 ) ENGINE=MyIsam; 26 27 INSERT INTO t3 VALUES (10,1,7), (13,7,3), (18,0,1), (23,8,1); 28 CREATE TABLE t4 ( 29 pk int NOT NULL, 30 col_int_key int NOT NULL, 31 PRIMARY KEY (pk), 32 KEY col_int_key (col_int_key) 33 ) ENGINE=MyIsam; 34 INSERT INTO t4 VALUES (1,7); 35 36 let $query= 37 SELECT t1a.* 38 FROM t3 AS t1a 39 JOIN t3 AS t1b USING ( col_int_nokey ) 40 WHERE t1a.col_int_key IN ( 41 SELECT pk 42 FROM t4 43 WHERE col_int_key IN ( 44 SELECT col_int_nokey 45 FROM t3 46 ) 47 ); 48 49 eval EXPLAIN $query; 50 --sorted_result 51 eval $query; 52 ALTER TABLE t3 ENGINE=Innodb; 53 ALTER TABLE t4 ENGINE=Innodb; 54 eval $query; 55 #DROP TABLE t2, t3, t4; 56 DROP TABLE t3, t4; ``` If the rename table is executed on line 16 instead of droping table on line 17, the result of query on line 54 is different.