How to write a hint for a subquery forming a table?

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

Original topic: 请问子查询形成表的hint怎么写?

| username: Raymond

TiDB version: v6.5.3
Phenomenon:
The approximate statement is as follows

select q.a,q,b from (
select  a,b from table1 partition(p0520) where c >100 group by a,b) as n join table1 partition(p0520)  q on  n.a=q.a group by a,b;

The default execution plan is that the tables n and q use an index hash join. I want n and q to use a hash join. I added the hint as follows

select /*+hash_join_build(@qb1) */  q.a,q,b from (
select /*+ qb_name(qb1) */ a,b from table1 partition(p0520) where c >100 group by a,b) as n join table1 partition(p0520)  q on  n.a=q.a group by a,b;

I found that this hint cannot make the statement use a hash join. Please help, how can the above statement be made to use a hash join?

| username: 友利奈绪 | Original post link

Does the position of the symbols in “select /*+hash_join_build(@qb1) */ q.a,-------->q,b <-----from (” affect the query?

| username: Raymond | Original post link

q.b Here is just my mistake, it should be q.b

| username: 有猫万事足 | Original post link

Try this.
But generally speaking, index hash join is more efficient than hash join.
I feel that the optimizer did not make a wrong choice. Using hash join might be slower.

| username: Raymond | Original post link

Thank you, this might just be a workaround.

| username: Raymond | Original post link

It should be a bug, and it should be fixed in version 6.5.6 and above.

You can also work around it using the following method:

select /*+hash_join_probe(q) */  q.a,q.b from (
select  a,b from table1 partition(p0520) where c >100 group by a,b) as n join table1 partition(p0520)  q on  n.a=q.a group by a,b;
| username: yytest | Original post link

In this example, /*+ TIDB_HINT('hint_name') */ is the added hint, and hint_name is the name of the hint you want to use. However, the specific hint name and usage depend on the TiDB version and the types of hints it supports.

| username: FutureDB | Original post link

Did you forget to add a space after the plus sign? /*+ hash_join_build(@qb1) */