no_join related hints don't work in sql binding

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
create table a(id int key);
create table b(id int key);
create binding for select * from a, b where a.id=b.id using select /*+ no_merge_join(a,b) */ * from a, b where a.id=b.id;
show bindings;
explain select * from a, b where a.id=b.id;

In addition to no_merge_join, the other no_join hints like no_hash_join, no_index_join, no_index_hash_join, no_index_merge_join also have the same issue.

2. What did you expect to see? (Required)

The hint works in sql binding.

3. What did you see instead (Required)

mysql> show bindings;
+--------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
| Original_sql                                                                   | Bind_sql                                                                                   | Default_db | Status  | Create_time             | Update_time             | Charset | Collation       | Source | Sql_digest                                                       | Plan_digest |
+--------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
| select * from ( `test` . `a` ) join `test` . `b` where `a` . `id` = `b` . `id` | SELECT /*+ no_merge_join()*/ * FROM (`test`.`a`) JOIN `test`.`b` WHERE `a`.`id` = `b`.`id` | test       | enabled | 2024-04-22 20:25:43.628 | 2024-04-22 20:25:43.628 | utf8    | utf8_general_ci | manual | 0fcc6567db6b75418c703cc9f9f17c03125ca90730298b2edb8c9c1b81378e4e |             |
+--------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+------------+---------+-------------------------+-------------------------+---------+-----------------+--------+------------------------------------------------------------------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from a, b where a.id=b.id;
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------+
| id                          | estRows  | task      | access object | operator info                                       |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------+
| MergeJoin_8                 | 12500.00 | root      |               | inner join, left key:test.a.id, right key:test.b.id |
| ├─TableReader_32(Build)     | 10000.00 | root      |               | data:TableFullScan_31                               |
| │ └─TableFullScan_31        | 10000.00 | cop[tikv] | table:b       | keep order:true, stats:pseudo                       |
| └─TableReader_30(Probe)     | 10000.00 | root      |               | data:TableFullScan_29                               |
|   └─TableFullScan_29        | 10000.00 | cop[tikv] | table:a       | keep order:true, stats:pseudo                       |
+-----------------------------+----------+-----------+---------------+-----------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+----------------------------------------------------------------------------------------+
| Warning | 1815 | Hint no_merge_join() is inapplicable. Please specify the table names in the arguments. |
+---------+------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

v6.5.5

[pcqz]

The issue should be fixed in #49587 in v7.6.0

[fixdb]

@qw4990 Can you cherry-pick to 7.5?

[seiya-annie]

/found customer