| Bug #100764 | Rename Table causes other SQL execution plans to change. | ||
|---|---|---|---|
| Submitted: | 8 Sep 2020 8:50 | Modified: | 15 Oct 2020 13:08 |
| Reporter: | kfpanda kf | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[8 Sep 2020 9:13]
kfpanda kf
Version: 8.0.18
[15 Sep 2020 13:08]
MySQL Verification Team
Hello kfpanda kf,
Thank you for the report and mtr test case.
I'm not seeing the reported issue with provided instruction i.e ran provided mtr test case with --record option with below line commented out:
#alter table t1 rename to db1.t17;
#alter table t2 rename to db1.t18;
In the second run, uncommented above statements and commented:
#drop table t1,t2;
But seeing no issues:
./mtr distinct1
Logging: ./mtr distinct1
MySQL Version 8.0.18
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
- WARNING: Using the 'mysql-test/var' symlink
Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-8.0.18/mysql-test/var'
Installing system database
Using parallel: 1
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
[100%] main.distinct1 [ fail ]
Test ended at 2020-09-15 14:54:35
CURRENT_TEST: main.distinct1
--- /export/umesh/server/binaries/GABuilds/mysql-8.0.18/mysql-test/r/distinct1.result 2020-09-15 15:54:01.549452461 +0300
+++ /export/umesh/server/binaries/GABuilds/mysql-8.0.18/mysql-test/var/log/distinct1.reject 2020-09-15 15:54:35.402235177 +0300
@@ -27,7 +27,8 @@
test2@testdomain.com Z001
test2@testdomain.com R002
test3@testdomain.com Z001
-drop table t1,t2;
+alter table t1 rename to db1.t17;
+alter table t2 rename to db1.t18;
SET sql_mode = default;
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES (1,1), (2,1), (3,1);
mysqltest: Result content mismatch
safe_process[23251]: Child process: 23252, exit: 1
In both the cases I'm seeing:
EXPLAIN SELECT a FROM t1 GROUP BY a;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL index PRIMARY PRIMARY 4 NULL 3 100.00 Using index
Could you please confirm if anything I'm missing here? Thank you!
regards,
Umesh
[16 Oct 2020 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: - main.distinct1 ``` # The include statement below is a temp one for tests that are yet to #be ported to run with InnoDB, #but needs to be kept for tests that would need MyISAM in future. --source include/force_myisam_default.inc --source include/have_myisam.inc create database db1; # # Test when using a not previously used column in ORDER BY # CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered DATETIME); CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); INSERT INTO t1 (email, infoID, dateentered) VALUES ('test1@testdomain.com', 1, '2002-07-30 22:56:38'), ('test1@testdomain.com', 1, '2002-07-27 22:58:16'), ('test2@testdomain.com', 1, '2002-06-19 15:22:19'), ('test2@testdomain.com', 2, '2002-06-18 14:23:47'), ('test3@testdomain.com', 1, '2002-05-19 22:17:32'); INSERT INTO t2(infoID, shipcode) VALUES (1, 'Z001'), (2, 'R002'); --sorted_result SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; --source include/turn_off_only_full_group_by.inc SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc #drop table t1,t2; alter table t1 rename to db1.t17; alter table t2 rename to db1.t18; SET sql_mode = default; CREATE TABLE t1(a INT PRIMARY KEY, b INT); INSERT INTO t1 VALUES (1,1), (2,1), (3,1); EXPLAIN SELECT DISTINCT a FROM t1; EXPLAIN SELECT DISTINCT a,b FROM t1; EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2; EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2 WHERE t1_1.a = t1_2.a; EXPLAIN SELECT a FROM t1 GROUP BY a; alter table t1 rename to db1.t29; drop database db1; ``` Replace statement `drop table t1,t2` with `alter table t1 rename to db1.t17` and `alter table t2 rename to db1.t18`. Then you will find that the result of statement `EXPLAIN SELECT a FROM t1 GROUP BY a` has changed. ``` [100%] main.distinct1 [ fail ] Test ended at 2020-09-08 08:54:02 CURRENT_TEST: main.distinct1 --- /home/taurus/code/build/install/sql/mysql-test/r/distinct1.result 2020-09-08 11:52:08.946656306 +0300 +++ /home/taurus/code/build/install/sql/mysql-test/var/log/distinct1.reject 2020-09-08 11:54:02.315649549 +0300 @@ -27,7 +27,8 @@ test2@testdomain.com Z001 test2@testdomain.com R002 test3@testdomain.com Z001 -drop table t1,t2; +alter table t1 rename to db1.t17; +alter table t2 rename to db1.t18; SET sql_mode = default; CREATE TABLE t1(a INT PRIMARY KEY, b INT); INSERT INTO t1 VALUES (1,1), (2,1), (3,1); @@ -56,7 +57,7 @@ Note 1003 /* select#1 */ select distinct `test`.`t1_1`.`a` AS `a`,`test`.`t1_1`.`b` AS `b` from `test`.`t1` `t1_1` join `test`.`t1` `t1_2` where (`test`.`t1_2`.`a` = `test`.`t1_1`.`a`) EXPLAIN SELECT a FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL index PRIMARY PRIMARY 4 NULL 3 100.00 Using index +1 SIMPLE t1 NULL range PRIMARY PRIMARY 4 NULL 2 100.00 Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` alter table t1 rename to db1.t29; mysqltest: Result content mismatch safe_process[4172]: Child process: 4173, exit: 1 ``` How to repeat: ./mtr main.distinct1