Optimizer hints is not worked for views

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql --connect-timeout 15 -u '3sDg3oSF1k7eu15.root' -h [gateway01.ap-northeast-1.prod.aws.tidbcloud.com](http://gateway01.ap-northeast-1.prod.aws.tidbcloud.com/) -P 4000 -D test -p

You can use your own tidb-cloud dev_tier.

use gharchive_dev;
create view v1 as select * from github_events;
explain select /*+ use_index(github_events, index_github_events_on_actor_id) */ * from v1 where id = 1;

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

The plan should use index index_github_events_on_actor_id.

3. What did you see instead (Required)

mysql> explain select /*+ use_index(github_events, index_github_events_on_actor_id) */ * from v1 where id = 1;
+--------------------------------+---------+-----------+----------------------------------------------------------+-------------------------------+
| id                             | estRows | task      | access object                                            | operator info                 |
+--------------------------------+---------+-----------+----------------------------------------------------------+-------------------------------+
| IndexLookUp_15                 | 0.00    | root      |                                                          |                               |
| ├─IndexRangeScan_13(Build)     | 0.00    | cop[tikv] | table:github_events, index:index_github_events_on_id(id) | range:[1,1], keep order:false |
| └─TableRowIDScan_14(Probe)     | 0.00    | cop[tikv] | table:github_events                                      | keep order:false              |
+--------------------------------+---------+-----------+----------------------------------------------------------+-------------------------------+
3 rows in set, 1 warning (0.18 sec)

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                              |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | use_index(gharchive_dev.github_events, index_github_events_on_actor_id) is inapplicable, check whether the table(gharchive_dev.github_events) exists |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.12 sec)

4. What is your TiDB version? (Required)

796fb1f0a48dc2d5acc9943bd6f42af9ccef2c0d

[winoros]

https://docs.oracle.com/cd/E18283_01/server.112/e16638/hintsref.htm#i27644

We can impl the same grammar mentioned in this chapter. It's a reasonable solution that is not hard to impl. But we need to extend our parser to support the grammar.

The current

// HintTable is table in the hint. It may have query block info.
type HintTable struct {
        DBName        model.CIStr
        TableName     model.CIStr
        QBName        model.CIStr
        PartitionList []model.CIStr
}

should become

// HintTable is table in the hint. It may have query block info.
type HintTable struct {
        DBName        model.CIStr
        TableName     model.CIStr
        QBNameList   []model.CIStr
        PartitionList []model.CIStr
}

[winoros]

Not only the index hint, we will fail if we want to use hint to choose the physical algorithm of join/agg. Thus, modify the title of the issue.