Got error when use merge_join hint in CTE

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1, t2;
create table t1(c1 int);
insert into t1 values(1), (2), (1), (2);
create table t2(c1 int primary key);
insert into t2 values(1), (2), (3);
with recursive cte1(c1) as (select c1 from t1 union select c1 + 1 c1 from t1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from cte1 dt1 left join t1 dt2 on dt1.c1 = dt2.c1 order by dt1.c1, dt2.c1;

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

mysql>     with recursive cte1(c1) as (select c1 from t1 union select c1 + 1 c1 from t1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from cte1 dt1 left join t1 dt2 on dt1.c1 = dt2.c1 order by dt1.c1, dt2.c1;
+------+------+
| c1   | c1   |
+------+------+
|    1 |    1 |
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
|    3 | NULL |
+------+------+
5 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql>     with recursive cte1(c1) as (select c1 from t1 union select c1 + 1 c1 from t1 where c1 < 3) select /*+ MERGE_JOIN(dt1, dt2) */ * from cte1 dt1 left join t1 dt2 on dt1.c1 = dt2.c1 order by dt1.c1, dt2.c1;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query

4. What is your TiDB version? (Required)

master and 5.1

[guo-shaoge]

MergeJoin requires CTE to be sorted. But (p *LogicalCTE) findBestTask just return nil.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions