hint doesn't work after inlining CTE

[wjhuang2016]

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

[hawkingrei]

before

        	            	-[Projection_13 9990.00 root  test.t1.a, test.t1.b, test.t2.a, Column#13]
        	            	-[└─HashJoin_46 9990.00 root  inner join, equal:[eq(test.t1.a, test.t2.a)]]
        	            	-[  ├─HashAgg_71(Build) 7992.00 root  group by:test.t2.a, funcs:count(Column#29)->Column#13, funcs:firstrow(test.t2.a)->test.t2.a]
        	            	-[  │ └─TableReader_72 7992.00 root  data:HashAgg_64]
        	            	-[  │   └─HashAgg_64 7992.00 cop[tikv]  group by:test.t2.a, funcs:count(test.t2.b)->Column#29]
        	            	-[  │     └─Selection_70 9990.00 cop[tikv]  not(isnull(test.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(test.t1.a))]
        	            	-[      └─TableFullScan_58 10000.00 cop[tikv] table:t1 keep order:false, stats:pseudo]

after

        	            	+[Projection_9 9990.00 root  test.t1.a, test.t1.b, test.t2.a, Column#7]
        	            	+[└─IndexJoin_17 9990.00 root  inner join, inner:HashAgg_15, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a)]
        	            	+[  ├─TableReader_44(Build) 9990.00 root  data:Selection_43]
        	            	+[  │ └─Selection_43 9990.00 cop[tikv]  not(isnull(test.t1.a))]
        	            	+[  │   └─TableFullScan_42 10000.00 cop[tikv] table:t1 keep order:false, stats:pseudo]
        	            	+[  └─HashAgg_15(Probe) 79840080.00 root  group by:test.t2.a, funcs:count(Column#9)->Column#7, funcs:firstrow(test.t2.a)->test.t2.a]
        	            	+[    └─IndexLookUp_16 79840080.00 root  ]
        	            	+[      ├─Selection_13(Build) 9990.00 cop[tikv]  not(isnull(test.t2.a))]
        	            	+[      │ └─IndexRangeScan_11 10000.00 cop[tikv] table:t2, index:a(a) range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo]
        	            	+[      └─HashAgg_14(Probe) 79840080.00 cop[tikv]  group by:test.t2.a, funcs:count(test.t2.b)->Column#9]
        	            	+[        └─TableRowIDScan_12 9990.00 cop[tikv] table:t2 keep order:false, stats:pseudo]

[hawkingrei]

But I find that it cannot work in MySQL. but MySQL have no INJ_JOIN. so I replace it with JOIN_INDEX and it also consider of the query block offset

 desc with cte as (select a, count(b) from t2 group by a) select /*+ JOIN_IND
                        -> EX(cte) */ * from t1, cte where t1.a=cte.a;


+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                             |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | with `cte` as (/* select#2 */ select `test`.`t2`.`a` AS `a`,count(`test`.`t2`.`b`) AS `count(b)` from `test`.`t2` group by `test`.`t2`.`a`) /* select#1 */ select /*+ JOIN_INDEX(`cte`@`select#1`) */ `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`cte`.`a` AS `a`,`cte`.`count(b)` AS `count(b)` from `test`.`t1` join `cte` where (`cte`.`a` = `test`.`t1`.`a`) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[hawkingrei]

I can only say that the warning message for this issue is inadequate; it is not as straightforward as MySQL.