optimizer hints: add notes for how to specify the table name in hints #8292

winoros · 2022-02-08T08:55:53Z

First-time contributors' checklist

I've signed Contributor License Agreement that's required for repo owners to accept my contribution.

What is changed, added or deleted? (Required)

It's a little against intuition when we are specifying a table in the optimizer hints. So we add some notes to tell the user how to use it.

Which TiDB version(s) do your changes apply to? (Required)

Tips for choosing the affected version(s):

By default, CHOOSE MASTER ONLY so your changes will be applied to the next TiDB major or minor releases. If your PR involves a product feature behavior change or a compatibility change, CHOOSE THE AFFECTED RELEASE BRANCH(ES) AND MASTER.

For details, see tips for choosing the affected versions (in Chinese).

What is the related PR or file link(s)?

This PR is translated from:
Other reference link(s):

Do your changes match any of the following descriptions?

Delete files
Change aliases
Need modification after applied to another branch
Might cause conflicts after applied to another branch

ti-chi-bot · 2022-02-08T08:55:54Z

[REVIEW NOTIFICATION]

This pull request has not been approved.

To complete the pull request process, please ask the reviewers in the list to review by filling /cc @reviewer in the comment.
After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the list by filling /assign @committer in the comment to help you merge this pull request.

The full list of commands accepted by this bot can be found here.

Reviewer can indicate their review by submitting an approval review.
Reviewer can cancel approval by submitting a request changes review.

fzhedu · 2022-02-09T02:57:54Z

optimizer-hints.md

+
+在使用诸如 [MERGE_JOIN](#MERGE_JOIN(t1_name-[,-tl_name-...])) 的 Hint 时，我们会需要在 Hint 中提供表名。为了让语法解析时对表名的处理逻辑与 MySQL 的处理逻辑相同。在使用 Hint 中使用表名时，需要注意如下的限制：
+
+如果这个表有别名，那么只有的使用它的别名可以生效。如果这个表不在当前通过 `USE DATABASE` 命令所指定的数据库中，那么我们需要使用 `数据库名.别名` 的方式指定这个表。


如果这个表有别名，那么只有的使用它的别名可以生效...

的多余？

fzhedu · 2022-02-09T03:11:53Z

optimizer-hints.md

+```sql
+USE test;
+DROP TABLE IF EXISTS test_hint;
+CREATE TABLE test_hint(a int);
+SELECT /*+ HASH_JOIN(t1) */ * FROM test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test.t1) */ * FROM test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test_hint) */ * FROM test_hint t1, test_hint t2;
+DROP DATABASE IF EXISTS test_hint_db;
+CREATE DATABASE test_hint_db;
+CREATE TABLE test_hint_db.test_hint(a int);
+SELECT /*+ HASH_JOIN(t1) */ * FROM test_hint_db.test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test_hint_db.t1) */ * FROM test_hint_db.test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test_hint_db.test_hint) */ * FROM test_hint_db.test_hint t1, test_hint t2;
+```
+
+在上述的例子中只有如下几个 SQL 是可以被使用 Hint 的，其他 Hint 都会提示找不到 Hint 所指定的表名。
+
+```sql
+USE test;
+DROP TABLE IF EXISTS test_hint;
+CREATE TABLE test_hint(a int);
+SELECT /*+ HASH_JOIN(t1) */ * FROM test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test.t1) */ * FROM test_hint t1, test_hint t2;
+SELECT /*+ HASH_JOIN(test_hint_db.t1) */ * FROM test_hint_db.test_hint t1, test_hint t2;
+```


先放错误的示例可能会误导读者，建议先举例 use db + 直接使用别名，无 db + db.别名的2种情况，然后再说不正确的样例。

fzhedu

what about English version?

TomShawn · 2022-02-09T03:14:56Z

what about English version?

I'll translate the approved Chinese version into English. First, let's have this PR approved.

Add comments

yiwen92 · 2022-03-14T09:45:38Z

Let's do a review and merge into branch, thx. @TomShawn

TomShawn · 2022-04-14T03:30:44Z

@winoros Please take a look at the comments above and address them. Thanks~

qiancai · 2023-02-24T11:45:20Z

Removed the needs-cherry-pick-release-6.3 label because the v6.3 docs have been archived at https://docs-archive.pingcap.com/zh/tidb/v6.3 and will no longer receive new updates.

qiancai · 2023-04-06T07:03:14Z

Removed the needs-cherry-pick-release-6.4 label because the v6.4 docs have been archived at https://docs-archive.pingcap.com/zh/tidb/v6.4 and will no longer receive new updates.

optimizer-hints.md

Co-authored-by: xixirangrang <[email protected]>

optimizer-hints.md

Co-authored-by: xixirangrang <[email protected]>

ran-huang · 2023-05-09T05:44:29Z

optimizer-hints.md

@@ -61,6 +61,37 @@ SELECT /*+ HASH_JOIN(@sel_1 t1@sel_1, t3) */ * FROM (SELECT t1.a, t1.b FROM t t1
 >
 > Hint 声明的位置必须在指定生效的查询块之中或之前，不能是在之后的查询块中，否则无法生效。

+## Hint 中的表名
+
+在使用诸如 [MERGE_JOIN](#MERGE_JOIN(t1_name-[,-tl_name-...])) 的 Hint 时，需要在 Hint 中提供表名。为了确保语法解析时对表名的处理逻辑与 MySQL 的处理逻辑相同，在使用 Hint 中使用表名时，需要注意，如果表有别名，必须使用它的别名才可以生效。如果这个表不在当前通过 `USE DATABASE` 命令所指定的数据库中，那么需要使用 `table_name.alias` 的方式指定这个表。参考以下示例。


Suggested change

在使用诸如 [MERGE_JOIN](#MERGE_JOIN(t1_name-[,-tl_name-...])) 的 Hint 时，需要在 Hint 中提供表名。为了确保语法解析时对表名的处理逻辑与 MySQL 的处理逻辑相同，在使用 Hint 中使用表名时，需要注意，如果表有别名，必须使用它的别名才可以生效。如果这个表不在当前通过 `USE DATABASE` 命令所指定的数据库中，那么需要使用 `table_name.alias` 的方式指定这个表。参考以下示例。

在使用诸如 [MERGE_JOIN](#merge_joint1_name--tl_name-) 的 Hint 时，需要在 Hint 中提供表名。为了确保语法解析时对表名的处理逻辑与 MySQL 的处理逻辑相同，在使用 Hint 中使用表名时，需要注意，如果表有别名，必须使用它的别名才可以生效。如果这个表不在当前通过 `USE DATABASE` 命令所指定的数据库中，那么需要使用 `table_name.alias` 的方式指定这个表。参考以下示例。

那么需要使用 table_name.alias 的方式指定这个表

这里是 db_name.alias 吧？下面示例用的是 db_name（L80）

ran-huang · 2023-05-09T05:47:48Z

optimizer-hints.md

+SELECT /*+ HASH_JOIN(test_hint_db.test_hint) */ * FROM test_hint_db.test_hint t1, test_hint t2;  /* hint 不生效，因为未使用别名 */
+```
+
+上述例子中，只有下面几个 SQL 是可以被使用 Hint 的，其他 Hint 都会提示找不到 Hint 所指定的表名。


Suggested change

上述例子中，只有下面几个 SQL 是可以被使用 Hint 的，其他 Hint 都会提示找不到 Hint 所指定的表名。

上述例子中，只有下面几个 SQL 的 Hint 生效，其他语句的 Hint 都会提示找不到 Hint 所指定的表名。

qiancai · 2023-07-07T06:15:47Z

Removed the needs-cherry-pick-release-6.6 label because the v6.6 docs have been archived at https://docs-archive.pingcap.com/zh/tidb/v6.6 and will no longer receive new updates.

ti-chi-bot · 2024-01-26T11:06:17Z

@winoros: The following test failed, say /retest to rerun all failed tests or /retest-required to rerun all mandatory failed tests:

Test name	Commit	Details	Required	Rerun command
pull-verify	`413f62c`	link	true	`/test pull-verify`

Full PR test history. Your PR dashboard.

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository. I understand the commands that are listed here.

qiancai · 2024-12-30T09:57:36Z

Removed the needs-cherry-pick-release-5.3 label because the v5.3 docs have been archived at https://docs-archive.pingcap.com/zh/tidb/v5.3 and will no longer receive new updates.

hfxsd · 2025-01-06T06:16:52Z

closed after confirming with owner

optimizer hints: add notes for how to specify the table name in hints

60fc794

ti-chi-bot requested a review from TomShawn February 8, 2022 08:56

ti-chi-bot added missing-translation-status This PR does not have translation status info. size/M Denotes a PR that changes 30-99 lines, ignoring generated files. labels Feb 8, 2022

TomShawn added the ONCALL Relates to documentation oncall. label Feb 8, 2022

TomShawn self-assigned this Feb 9, 2022

fzhedu reviewed Feb 9, 2022

View reviewed changes

TomShawn added the needs-cherry-pick-release-5.4 Should cherry pick this PR to release-5.4 branch. label Feb 11, 2022

Update optimizer-hints.md

3c93044

Add comments

TomShawn added the needs-cherry-pick-release-6.0 label Apr 14, 2022

shichun-0415 added needs-cherry-pick-release-6.1 Should cherry pick this PR to release-6.1 branch. needs-cherry-pick-release-6.2 and removed needs-cherry-pick-release-6.0 labels Aug 13, 2022

TomShawn added the needs-cherry-pick-release-6.3 label Sep 29, 2022

TomShawn added the needs-cherry-pick-release-6.4 label Nov 16, 2022

TomShawn added the needs-cherry-pick-release-6.5 Should cherry pick this PR to release-6.5 branch. label Dec 28, 2022

TomShawn removed the needs-cherry-pick-release-6.2 label Jan 31, 2023

TomShawn added the needs-cherry-pick-release-6.6 label Feb 17, 2023

qiancai removed the needs-cherry-pick-release-6.3 label Feb 24, 2023

lilin90 requested review from hfxsd and removed request for TomShawn April 3, 2023 08:22

lilin90 assigned hfxsd and unassigned TomShawn Apr 3, 2023

qiancai removed the needs-cherry-pick-release-6.4 label Apr 6, 2023

hfxsd reviewed May 8, 2023

View reviewed changes

optimizer-hints.md Outdated Show resolved Hide resolved

optimizer-hints.md Outdated Show resolved Hide resolved

optimizer-hints.md Outdated Show resolved Hide resolved

optimizer-hints.md Outdated Show resolved Hide resolved

Apply suggestions from code review

4be9754

Co-authored-by: xixirangrang <[email protected]>

hfxsd reviewed May 8, 2023

View reviewed changes

optimizer-hints.md Outdated Show resolved Hide resolved

hfxsd requested a review from ran-huang May 8, 2023 11:27

Update optimizer-hints.md

413f62c

Co-authored-by: xixirangrang <[email protected]>

ran-huang reviewed May 9, 2023

View reviewed changes

qiancai removed the needs-cherry-pick-release-6.6 label Jul 7, 2023

qiancai removed the needs-cherry-pick-release-5.3 label Dec 30, 2024

hfxsd closed this Jan 6, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

optimizer hints: add notes for how to specify the table name in hints #8292

optimizer hints: add notes for how to specify the table name in hints #8292

winoros commented Feb 8, 2022

ti-chi-bot commented Feb 8, 2022

fzhedu Feb 9, 2022 •

edited

Loading

fzhedu Feb 9, 2022

fzhedu left a comment

TomShawn commented Feb 9, 2022

yiwen92 commented Mar 14, 2022

TomShawn commented Apr 14, 2022

qiancai commented Feb 24, 2023

qiancai commented Apr 6, 2023

ran-huang May 9, 2023

ran-huang May 9, 2023

ran-huang May 9, 2023

qiancai commented Jul 7, 2023

ti-chi-bot bot commented Jan 26, 2024

qiancai commented Dec 30, 2024

hfxsd commented Jan 6, 2025


		在使用诸如 [MERGE_JOIN](#MERGE_JOIN(t1_name-[,-tl_name-...])) 的 Hint 时，我们会需要在 Hint 中提供表名。为了让语法解析时对表名的处理逻辑与 MySQL 的处理逻辑相同。在使用 Hint 中使用表名时，需要注意如下的限制：

		如果这个表有别名，那么只有的使用它的别名可以生效。如果这个表不在当前通过 `USE DATABASE` 命令所指定的数据库中，那么我们需要使用 `数据库名.别名` 的方式指定这个表。

	上述例子中，只有下面几个 SQL 是可以被使用 Hint 的，其他 Hint 都会提示找不到 Hint 所指定的表名。
	上述例子中，只有下面几个 SQL 的 Hint 生效，其他语句的 Hint 都会提示找不到 Hint 所指定的表名。

optimizer hints: add notes for how to specify the table name in hints #8292

optimizer hints: add notes for how to specify the table name in hints #8292

Conversation

winoros commented Feb 8, 2022

First-time contributors' checklist

What is changed, added or deleted? (Required)

Which TiDB version(s) do your changes apply to? (Required)

What is the related PR or file link(s)?

Do your changes match any of the following descriptions?

ti-chi-bot commented Feb 8, 2022

fzhedu Feb 9, 2022 • edited Loading

Choose a reason for hiding this comment

fzhedu Feb 9, 2022

Choose a reason for hiding this comment

fzhedu left a comment

Choose a reason for hiding this comment

TomShawn commented Feb 9, 2022

yiwen92 commented Mar 14, 2022

TomShawn commented Apr 14, 2022

qiancai commented Feb 24, 2023

qiancai commented Apr 6, 2023

ran-huang May 9, 2023

Choose a reason for hiding this comment

ran-huang May 9, 2023

Choose a reason for hiding this comment

ran-huang May 9, 2023

Choose a reason for hiding this comment

qiancai commented Jul 7, 2023

ti-chi-bot bot commented Jan 26, 2024

qiancai commented Dec 30, 2024

hfxsd commented Jan 6, 2025

fzhedu Feb 9, 2022 •

edited

Loading