Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

hint doesn't work after inlining CTE #53454

Open
wjhuang2016 opened this issue May 21, 2024 · 0 comments
Open

hint doesn't work after inlining CTE #53454

wjhuang2016 opened this issue May 21, 2024 · 0 comments

Comments

@wjhuang2016
Copy link
Member

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t1 (a int, b int, key(a));
create table t2 (a int, b int, key(a));
set @@tidb_enable_inl_join_inner_multi_pattern=on;
desc with cte as (select a, count(b) from t2 group by a) select /*+ INL_JOIN(cte) */  * from t1, cte where t1.a=cte.a;

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

Use index join.

3. What did you see instead (Required)

mysql> desc with cte as (select a, count(b) from t2 group by a) select /*+ INL_JOIN(cte) */  * from t1, cte where t1.a=cte.a;
+------------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+
| id                                 | estRows  | task      | access object | operator info                                                                            |
+------------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+
| Projection_13                      | 9990.00  | root      |               | sys.t1.a, sys.t1.b, sys.t2.a, Column#13                                                  |
| └─HashJoin_46                      | 9990.00  | root      |               | inner join, equal:[eq(sys.t1.a, sys.t2.a)]                                               |
|   ├─HashAgg_71(Build)              | 7992.00  | root      |               | group by:sys.t2.a, funcs:count(Column#29)->Column#13, funcs:firstrow(sys.t2.a)->sys.t2.a |
|   │ └─TableReader_72               | 7992.00  | root      |               | data:HashAgg_64                                                                          |
|   │   └─HashAgg_64                 | 7992.00  | cop[tikv] |               | group by:sys.t2.a, funcs:count(sys.t2.b)->Column#29                                      |
|   │     └─Selection_70             | 9990.00  | cop[tikv] |               | not(isnull(sys.t2.a))                                                                    |
|   │       └─TableFullScan_69       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                           |
|   └─TableReader_60(Probe)          | 9990.00  | root      |               | data:Selection_59                                                                        |
|     └─Selection_59                 | 9990.00  | cop[tikv] |               | not(isnull(sys.t1.a))                                                                    |
|       └─TableFullScan_58           | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                           |
+------------------------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (cte) in optimizer hint /*+ INL_JOIN(cte) */ or /*+ TIDB_INLJ(cte) */. Maybe you can use the table alias name |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

b69f81b

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

5 participants