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:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[8 Sep 2020 8:50] kfpanda kf
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
[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".