opened
eurekaka
username_0
## Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
```sql
create table t(c1 int, index k(c1));
create table t1(c1 int);
select /*+ TIDB_SMJ(a, b) */ a.c1 from t a , (select * from t1 limit 3) b where a.c1 = b.c1 and b.c1 is not null;
```

2. What did you expect to see?

A plan with sort-merge join generated.

3. What did you see instead?

```
mysql> select /*+ TIDB_SMJ(a, b) */ a.c1 from t a , (select * from t1 limit 3) b where a.c1 = b.c1 and b.c1 is not null;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query
```

4. What version of TiDB are you using (`tidb-server -V` or run `select tidb_version();` on TiDB)?

```
mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.0-rc.3-295-g353861f
Git Commit Hash: 353861fe8f4e5760b105a255a5540f612e9c953c
Git Branch: master
UTC Build Time: 2018-12-06 02:07:15
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
Optimizer fails to generate any plan when `TIDB_SMJ` is specified
issue
created
eurekaka
username_0
RCA: since subquery `b` contains `limit 3`, condition `b.c1 is not null` cannot be pushed down beyond `LogicalLimit`, so the inner child of `LogicalJoin` would be like `LogicalSelection -> LogicalLimit -> DataSource`. Because table `t1` has no index defined, inner plan cannot provide order property for sort-merge join, normally we would remove the order property requirement for inner plan first, then find physical implementation for inner plan, and finally add a `PhysicalSort` on top of inner plan. This logic is reflected in `findBestTask`:
```go
104     if prop.Enforced {
105         // First, get the bestTask without enforced prop
106         prop.Enforced = false
107         bestTask, err = p.findBestTask(prop)
108         if err != nil {
109             return nil, errors.Trace(err)
110         }
111         prop.Enforced = true
112         // Next, get the bestTask with enforced prop
113         prop.Cols = []*expression.Column{}
114     }
115     physicalPlans := p.self.exhaustPhysicalPlans(prop)
116     prop.Cols = oldPropCols
117
118     for _, pp := range physicalPlans {
119         // find best child tasks firstly.
120         childTasks = childTasks[:0]
121         for i, child := range p.children {
122             childTask, err := child.findBestTask(pp.GetChildReqProps(i))
```

However, for `LogicalSelection`, we don't build a new physical property for its child, instead, we just pass pointer of the required physical property for `LogicalSelection` down to its children, so in line 116 of the above pasted code snippet, the order requirement is added back for child of `LogicalSelection`, then it cannot find valid implementation.
comment
created
winoros
username_1
But, in the line 107 of the first code snippet. There should be a valid plan?
comment
created
eurekaka
username_0
No, in line 107, `prop.Cols` is not empty yet.
comment
created
eurekaka
username_0
@zz-jason I have done the check, only `LogicalSelection`, `LogicalLock` and `LogicalUnionScan` have risk. https://github.com/pingcap/tidb/pull/8635 has covered them.
comment
closed
eurekaka
username_0
issue