Using Hints in TiDB Does Not Take Effect

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB中使用hint并不能生效

| username: TiDBer_MDlnj0DO

[TiDB Usage Environment] Production environment TiDB version 4.0.10
When using hints to intervene in the execution plan while joining two tables, the help documentation indicates that this version supports these hints. However, in practice, it always prompts an error, and the execution plan’s hint does not take effect. Is this a bug?

SQL statement:

explain
  SELECT /*+HASH_JOIN(A,C)*/ 
         A.C_CODE, C.TYPES 
    FROM ODS.HIS_CUSTS A 
         INNER JOIN ODS.USERS C
  WHERE A.C_CODE = C.U_CODE 
    AND A.TDATE = 20230525

Prompt: There are no matching table names for (A, C) in optimizer hint /*+ HASH_JOIN(a, c) / or /+ TIDB_HJ(a, c) */. Maybe you can use the table alias name
And the execution plan is still in index join mode. Actually, both table A and table C have millions of records, so hash join should be better.

[Additionally] The same hint issues also occur with /+ INL_JOIN(A) / and /+ MERGE_JOIN(A,C)/, which prompt:
There are no matching table names for (A, C) in optimizer hint /*+ MERGE_JOIN(a, c) / or /+ TIDB_SMJ(a, c) */. Maybe you can use the table alias name

There are no matching table names for (A) in optimizer hint /*+ INL_JOIN(a) / or /+ TIDB_INLJ(a) */. Maybe you can use the table alias name

These hints are directly taken from the official help documentation, so there should be no syntax errors. But why do they always prompt failure, and the hints do not take effect?

| username: zhanggame1 | Original post link

So should we write the full table name?

| username: tidb菜鸟一只 | Original post link

Try removing all the aliases. The 4.0 version is too old, and it’s hard to find an environment to reproduce the issue.

explain
SELECT /*+HASH_JOIN(ODS.HIS_CUSTS, ODS.USERS)*/
ODS.HIS_CUSTS.C_CODE, ODS.USERS.TYPES
FROM ODS.HIS_CUSTS
INNER JOIN ODS.USERS
ON ODS.HIS_CUSTS.C_CODE = ODS.USERS.U_CODE
WHERE ODS.HIS_CUSTS.TDATE = 20230525;
| username: db_user | Original post link

Have A and C crossed databases? Try adding the database name.

| username: xingzhenxiang | Original post link

Try upgrading the version :shushing_face:

| username: WalterWj | Original post link

When executing with the MySQL client, remember to add -c. If there are warnings, use show warnings; to check the results.

| username: 胡杨树旁 | Original post link

Previously, I encountered a situation where the hint did not take effect: it was unsuccessful when executed on the server, but it worked when executed on the client. I wonder if you are facing the same issue.

| username: cy6301567 | Original post link

Upgrade to 6.

| username: redgame | Original post link

Did this later use a hint?