Bug #98635 Explain neglects db part of identifier after create view
Submitted: 17 Feb 2020 11:38 Modified: 20 Feb 2020 18:48
Reporter: Kaiwang CHen (OCA) Email Updates:
Status: Verified Impact on me:
None 
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 11:38] Kaiwang CHen
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;
[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