MySQL Bugs: #91780: Inconsistent printing of EXPLAIN queries.

Bug #91780	Inconsistent printing of EXPLAIN queries.
Submitted:	24 Jul 2018 20:10	Modified:	25 Jul 2018 14:45
Reporter:	Manuel Ung	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.11, 5.7.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Certain explain queries with views return consistent output depending on whether table cache was flushed or not after the view was created.

How to repeat:
CREATE TABLE t1 ( person_id int NOT NULL PRIMARY KEY, username varchar(40) default NULL, status_flg char(1) NOT NULL default 'A');
CREATE TABLE t2 ( person_role_id int NOT NULL auto_increment PRIMARY KEY, role_id int NOT NULL, person_id int NOT NULL, INDEX idx_person_id (person_id), INDEX idx_role_id (role_id));

CREATE VIEW v1 as SELECT profile.person_id AS person_id FROM t1 profile, t2 userrole WHERE userrole.person_id = profile.person_id;

INSERT INTO  t1 VALUES (6,'Sw','A'), (-1136332546,'ols','e'), (0,'    *\n','0'), (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10);

EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;

flush tables;

EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;

drop view v1;

drop table t1, t2;

Result:

CREATE TABLE t1 ( person_id int NOT NULL PRIMARY KEY, username varchar(40) default NULL, status_flg char(1) NOT NULL default 'A');
CREATE TABLE t2 ( person_role_id int NOT NULL auto_increment PRIMARY KEY, role_id int NOT NULL, person_id int NOT NULL, INDEX idx_person_id (person_id), INDEX idx_role_id (role_id));
CREATE VIEW v1 as SELECT profile.person_id AS person_id FROM t1 profile, t2 userrole WHERE userrole.person_id = profile.person_id;
INSERT INTO  t1 VALUES (6,'Sw','A'), (-1136332546,'ols','e'), (0,'    *\n','0'), (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10);
EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  profile NULL    const   PRIMARY PRIMARY 4       const   1       100.00  Using index
1       SIMPLE  userrole        NULL    ref     idx_person_id   idx_person_id   4       const   2       100.00  Using index
Warnings:
Note    1003    /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`userrole`.`person_id` = 6)
flush tables;
EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  profile NULL    const   PRIMARY PRIMARY 4       const   1       100.00  Using index
1       SIMPLE  userrole        NULL    ref     idx_person_id   idx_person_id   4       const   2       100.00  Using index
Warnings:
Note    1003    /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`test`.`userrole`.`person_id` = 6)
drop view v1;
drop table t1, t2;

Note that the two explain queries change output after flush tables is run.

Suggested fix:
LEX::need_correct_ident needs to return true of LEX::is_explain() is true.

Hello Manuel,

Thank you for the report.
I assume you are complaining about the inconsistency in the 'Message' before and after 'flush tables'.

Thanks,
Umesh

- 8.0.11 and even 5.7.22 has same thing
--
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

[...]

mysql> EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | profile  | NULL       | const | PRIMARY       | PRIMARY       | 4       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | userrole | NULL       | ref   | idx_person_id | idx_person_id | 4       | const |    2 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                               |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`userrole`.`person_id` = 6) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | profile  | NULL       | const | PRIMARY       | PRIMARY       | 4       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | userrole | NULL       | ref   | idx_person_id | idx_person_id | 4       | const |    2 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                      |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`test`.`userrole`.`person_id` = 6) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

- 5.7.22

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)

[...]

mysql> EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | profile  | NULL       | const | PRIMARY       | PRIMARY       | 4       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | userrole | NULL       | ref   | idx_person_id | idx_person_id | 4       | const |    2 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                               |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`userrole`.`person_id` = 6) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | profile  | NULL       | const | PRIMARY       | PRIMARY       | 4       | const |    1 |   100.00 | Using index |
|  1 | SIMPLE      | userrole | NULL       | ref   | idx_person_id | idx_person_id | 4       | const |    2 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                      |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`test`.`userrole`.`person_id` = 6) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Yes, I'm complaining about the inconsistent message:

Note    1003    /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`userrole`.`person_id` = 6)

and

Note    1003    /* select#1 */ select '6' AS `a`,'6' AS `b` from `test`.`t1` `profile` join `test`.`t2` `userrole` where (`test`.`userrole`.`person_id` = 6)