| Bug #98635 | Explain neglects db part of identifier after create view | ||
|---|---|---|---|
| Submitted: | 17 Feb 2020 11:38 | Modified: | 9 Jul 2021 22:49 |
| Reporter: | Kaiwang CHen (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.18, 8.0.19, 5.7.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[17 Feb 2020 12:18]
MySQL Verification Team
Hello Kaiwang, Thank you for the report and contribution! Please ensure to re-send the patch via "contribution" tab. Otherwise we would not be able to accept it. regards, Umesh
[20 Feb 2020 18:47]
Kaiwang CHen
See enclosed for a fix. Note that it will causes many regressions. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bugfix_98635.patch (application/octet-stream, text), 4.73 KiB.
[20 Feb 2020 18:48]
Kaiwang CHen
These tests will be affected by the patch. ./mtr main.subquery_sj_all main.derived main.subquery_sj_all_bka main.subquery_sj_all_bka_nixbnl main.group_by main.subquery_sj_all_bkaunique main.subquery_sj_dupsweed main.myisam_explain_json_non_select_all main.subquery_sj_dupsweed_bka main.myisam_explain_json_non_select_none main.subquery_sj_dupsweed_bka_nixbnl main.myisam_explain_non_select_all main.myisam_explain_non_select_none main.subquery_sj_dupsweed_bkaunique main.subquery_sj_firstmatch main.subquery_sj_firstmatch_bka main.subquery_all main.subquery_all_bka_nixbnl main.subquery_nomat_nosj main.subquery_nomat_nosj_bka main.subquery_nomat_nosj_bka_nixbnl main.subquery_none main.subquery_none_bka main.subquery_none_bka_nixbnl main.subquery_sj_firstmatch_bka_nixbnl main.subquery_sj_firstmatch_bkaunique main.subquery_sj_loosescan main.subquery_sj_loosescan_bka main.subquery_sj_loosescan_bka_nixbnl main.subquery_sj_loosescan_bkaunique main.subquery_sj_mat main.subquery_sj_mat_bka main.subquery_sj_mat_bka_nixbnl main.subquery_sj_mat_bkaunique main.subquery_sj_mat_nosj main.view main.subquery_sj_none main.subquery_sj_none_bka main.subquery_sj_none_bka_nixbnl main.subquery_sj_none_bkaunique --record Logging: /Users/kaiwang.ckw/Projects/mysql-server/mysql-test/mysql-test-run.pl main.subquery_sj_all main.derived main.subquery_sj_all_bka main.subquery_sj_all_bka_nixbnl main.group_by main.subquery_sj_all_bkaunique main.subquery_sj_dupsweed main.myisam_explain_json_non_select_all main.subquery_sj_dupsweed_bka main.myisam_explain_json_non_select_none main.subquery_sj_dupsweed_bka_nixbnl main.myisam_explain_non_select_all main.myisam_explain_non_select_none main.subquery_sj_dupsweed_bkaunique main.subquery_sj_firstmatch main.subquery_sj_firstmatch_bka main.subquery_all main.subquery_all_bka_nixbnl main.subquery_nomat_nosj main.subquery_nomat_nosj_bka main.subquery_nomat_nosj_bka_nixbnl main.subquery_none main.subquery_none_bka main.subquery_none_bka_nixbnl main.subquery_sj_firstmatch_bka_nixbnl main.subquery_sj_firstmatch_bkaunique main.subquery_sj_loosescan main.subquery_sj_loosescan_bka main.subquery_sj_loosescan_bka_nixbnl main.subquery_sj_loosescan_bkaunique main.subquery_sj_mat main.subquery_sj_mat_bka main.subquery_sj_mat_bka_nixbnl main.subquery_sj_mat_bkaunique main.subquery_sj_mat_nosj main.view main.subquery_sj_none main.subquery_sj_none_bka main.subquery_sj_none_bka_nixbnl main.subquery_sj_none_bkaunique --record
[9 Jul 2021 22:49]
Jon Stephens
Documented fix as follows in the MySQL 8.0.27 changelog:
The EXPLAIN output for a DML statement contains the table
identifier, which normally includes the database name, in the
output of SHOW WARNINGS. For some statements such as CREATE
VIEW, the database name should be omitted, which is enforced by
setting the alias_name_used flag to true in the cached table
object, but when the cached table was reused following CREATE
VIEW, the flag was not reset, which caused the database name to
be omitted from the warnings following EXPLAIN for statements
run after a CREATE VIEW which access the same cached table as
the view.
We fix this by ensuring that the alias_name_used flag is always
set to an appropriate value during table initialization.
Our thanks to Kaiwang Chen for the contribution.
Closed.
Description: Some command expects identifiers to be printed correctly: bool LEX::need_correct_ident() { switch (sql_command) { case SQLCOM_SHOW_CREATE: case SQLCOM_SHOW_TABLES: case SQLCOM_CREATE_VIEW: return true; default: return false; } } However, when these command are issued, they change the cached TABLE object, that change is not reset for other commands, thus applies all l subsequent commands, which is not expected. How to repeat: CREATE TABLE t1 ( pk INT, col_int_key INT, col_int_nokey INT, col_varchar_key VARCHAR(10), col_varchar_nokey VARCHAR(10), KEY col_int_key (col_int_key), KEY col_varchar_key (col_varchar_key) ); INSERT INTO t1 VALUES (), (); CREATE VIEW v1 AS SELECT alias1.col_int_nokey AS field1, (SELECT alias2.col_int_key FROM t1 AS alias2 WHERE alias1.col_varchar_key <= alias1.col_varchar_nokey ) AS field2 FROM t1 AS alias1; DROP VIEW v1; EXPLAIN SELECT pk AS foo, col_int_key AS bar FROM t1; SHOW WARNINGS; DROP TABLE t1; The expected output of SHOW WARNINGS: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `foo`,`test`.`t1`.`col_int_key` AS `bar` from `test`.`t1` The actual output of SHOW WARNINGS: Note 1003 /* select#1 */ select `t1`.`pk` AS `foo`,`t1`.`col_int_key` AS `bar` from `test`.`t1` Suggested fix: diff --git a/sql/table.cc b/sql/table.cc index a2057a0..0ebb1f8 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -4073,6 +4073,9 @@ void TABLE::init(THD *thd, TABLE_LIST *tl) { if (thd->lex->need_correct_ident()) alias_name_used = my_strcasecmp(table_alias_charset, s->table_name.str, tl->alias); + else + alias_name_used = false; + /* Fix alias if table name changes. */ if (strcmp(alias, tl->alias)) { size_t length = strlen(tl->alias) + 1;