Bug #98697 Table name alone is insufficient to identify a table within a query block
Submitted: 20 Feb 2020 18:14 Modified: 25 Feb 2020 16:28
Reporter: Kaiwang CHen (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[20 Feb 2020 18:14] Kaiwang CHen
Description:
Hints in the table level identify themselves by table alias without db qualification.

However, this query is valid but table alias does not identify a table:

select  1 from db1.t join db2.t using (a);

As a result, hints intended for a particular table apply to all tables with the same alias, and join order hints simply do not work.

How to repeat:
create database db1;
create database db2;

create table db1.t (a int primary key, b int, c int, key (b), key(c));
create table db2.t (a int primary key, b int, c int, key (b), key(c));

insert db1.t values (1,1,1), (2,2,2), (3,3,3), (4,4,4);
insert db2.t values  (1,1,1), (2,2,2), (3,3,3), (4,4,4);

explain select 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);
explain select /*+ NO_INDEX_MERGE(t b,c)*/ 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);

explain select /*+ join_order(t,t2) */ 1 from db1.t join db2.t t2 using (a);
explain select /*+ join_order(t,t) */ 1 from db1.t join db2.t using (a);

Expected output:

NO_INDEX_MERGE in the second query should complain, because it is intended to be apply to only one table.
JOIN_ORDER in the forth query should complain, because because t alone is ambiguous.

Actual output:

mysql [localhost] {msandbox} (db2) > explain select 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index_merge | b,c           | b,c  | 5,5     | NULL |    2 |   100.00 | Using union(b,c); Using where                                        |
|  1 | SIMPLE      | t     | NULL       | index_merge | b,c           | b,c  | 5,5     | NULL |    2 |   100.00 | Using union(b,c); Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------------+---------------+------+---------+------+------+----------+----------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (db2) > explain select /*+ NO_INDEX_MERGE(t b,c)*/ 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    4 |    43.75 | Using where                                        |
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    5 |    36.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select /*+ join_order(t,t) */ 1 from db1.t join db2.t using (a);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select 1 AS `1` from `db1`.`t` join `db2`.`t` where (`db2`.`t`.`a` = `db1`.`t`.`a`) |
+-------+------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select /*+ join_order(t,t2) */ 1 from db1.t join db2.t2 using (a);
ERROR 1146 (42S02): Table 'db2.t2' doesn't exist
mysql [localhost] {msandbox} (test) > explain select /*+ join_order(t,t2) */ 1 from db1.t join db2.t t2 using (a);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t`,`t2`) */ 1 AS `1` from `db1`.`t` join `db2`.`t` `t2` where (`db2`.`t2`.`a` = `db1`.`t`.`a`) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Add dot (.) a special token in the hint lexer, and use "db.t@qb" notation instead of "t@qb"

Add db to Hint_param_table, and if it is missing, use default db.

Opt_hints_table uses "db.t" as its name instead of only the t part.
[20 Feb 2020 18:44] Kaiwang CHen
Update synopsis.
[21 Feb 2020 12:56] MySQL Verification Team
Hello Mr. CHen,

Thank you for your bug report.

We do have certain questions regarding your report.

First of all, table name is sufficient to identify a table and an alias is not mandatory.

Regarding index merge, you specified explicitly that it should not be used, with optimiser hint.

Hence, I do not see why would the optimiser complain about anything. Also , I do not notice that you have asked for the warnings.
[21 Feb 2020 16:28] Kaiwang CHen
Hi Sinisa,

I am sorry that the report is not clear.

Table name (or alias) is sufficient to identify a table in the example query. NO_INDEX_MEGE(t ...) intends to apply to table t, however, it has two tables of the same name t in different databases, db1.t and db2.t. Which t is it referring to? As far as I know, the hint applying to both tables is not a defined behavior. In fact, it is applied to both tables, so in the query with the hint, the extra column no longer has union in it. Note that in setup_tables(), adjust_table_hints() looks up hints by TABLE_LIST::alias alone, both TABLE_LIST has t as alias and get the same hint.

There could be two ways to improve this case, first is to warn with the fact there are two tables of same name and revert the hint, the other is to use a db qualified table name to uniquely identify the table to apply the hint.
[24 Feb 2020 13:38] MySQL Verification Team
Hi Mr. Chen,

You have cleared out the misunderstanding.

I have two additional questions for you.

Are you sure that you. are not getting a warning (with SHOW WARNINGS) either from SELECT or EXPLAIN SELECT about insufficient identity info ????

Please, try that again ......

Second, if there is no warnings, then I can try to reproduce it and verify it as a "Feature request".
[25 Feb 2020 11:29] Kaiwang CHen
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t b,c)*/ 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    5 |    36.00 | Using where                                        |
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    5 |    36.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ NO_INDEX_MERGE(`t`@`select#1` `b`, `c`) */ 1 AS `1` from `db1`.`t` join `db2`.`t` where (((`db1`.`t`.`b` = 1) or (`db1`.`t`.`c` = 1)) and ((`db2`.`t`.`b` = 1) or (`db2`.`t`.`c` = 1))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select /*+ join_order(t,t) */ 1 from db1.t join db2.t using (a);
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index  | PRIMARY       | b       | 5       | NULL    |    5 |   100.00 | Using index |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db1.t.a |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select 1 AS `1` from `db1`.`t` join `db2`.`t` where (`db2`.`t`.`a` = `db1`.`t`.`a`) |
+-------+------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[25 Feb 2020 11:29] Kaiwang CHen
mysql [localhost] {msandbox} (test) > explain select /*+ NO_INDEX_MERGE(t b,c)*/ 1 from db1.t join db2.t where (db1.t.b = 1 or db1.t.c = 1) and (db2.t.b = 1 or db2.t.c = 1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    5 |    36.00 | Using where                                        |
|  1 | SIMPLE      | t     | NULL       | ALL  | b,c           | NULL | NULL    | NULL |    5 |    36.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ NO_INDEX_MERGE(`t`@`select#1` `b`, `c`) */ 1 AS `1` from `db1`.`t` join `db2`.`t` where (((`db1`.`t`.`b` = 1) or (`db1`.`t`.`c` = 1)) and ((`db2`.`t`.`b` = 1) or (`db2`.`t`.`c` = 1))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select /*+ join_order(t,t) */ 1 from db1.t join db2.t using (a);
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index  | PRIMARY       | b       | 5       | NULL    |    5 |   100.00 | Using index |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db1.t.a |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > show warnings;
+-------+------+----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                            |
+-------+------+----------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select 1 AS `1` from `db1`.`t` join `db2`.`t` where (`db2`.`t`.`a` = `db1`.`t`.`a`) |
+-------+------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[25 Feb 2020 13:42] MySQL Verification Team
Hi Mr. CHen,

Actually, I do not think that this is a bug.

A table is fully identified by schema.table_name identifier.

Regarding optimiser hints, they are not part of the standard but our own implementation.

Even there you get a correct warning (1003), so this is not a bug.
[25 Feb 2020 16:28] Kaiwang CHen
But I guess the hints do not work as expected? The join hint is discarded without any message. The index merge hint is effected in a wider range. Of course, it is not supposed to be compared to the SQL standard; it is supposed to be compared to the feature itself.
[26 Feb 2020 12:51] MySQL Verification Team
Thank you for your last comment.

Hints are not covered by SQL standards, but joins are covered.

Hence, if you want your hints to be precise, you should use aliases. Otherwise, you leave it up to the Optimiser to deduce what is it that you want. It is not a purpose of any SQL server to make anyone a better schema or query designer or better DBA.