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: | |
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
[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.