planner: index join inner side with non-push-downed selection should also be considered
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
explain select /*+ INL_JOIN(t) */ * from t, t1 where is_ipv4(t.b) and t.a = t1.a; // fail
explain select /*+ INL_JOIN(t1) */ * from t, t1 where is_ipv4(t.b) and t.a = t1.a; // ok
```
when we force the t as the probe side, the warnings show that the hint is ignored.
```
mysql> explain select /*+ INL_JOIN(t) */ * from t, t1 where is_ipv4(t.b) and t.a = t1.a;
+--------------------------------+----------+-----------+----------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+----------+-----------+----------------------+---------------------------------------------+
| HashJoin_21 | 9990.00 | root | | inner join, equal:[eq(test.t.a, test.t1.a)] |
| ├─Selection_29(Build) | 7992.00 | root | | is_ipv4(cast(test.t.b, var_string(20))) |
| │ └─TableReader_32 | 9990.00 | root | | data:Selection_31 |
| │ └─Selection_31 | 9990.00 | cop[tikv] | | not(isnull(test.t.a)) |
| │ └─TableFullScan_30 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
| └─IndexReader_37(Probe) | 9990.00 | root | | index:IndexFullScan_36 |
| └─IndexFullScan_36 | 9990.00 | cop[tikv] | table:t1, index:a(a) | keep order:false, stats:pseudo |
+--------------------------------+----------+-----------+----------------------+---------------------------------------------+
7 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | Scalar function 'is_ipv4'(signature: IsIPv4, return type: bigint(1)) is not supported to push down to storage layer now. |
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t) */ or /*+ TIDB_INLJ(t) */ is inapplicable |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
```
But actually, it can work with some enhancement.
```
mysql> explain select /*+ INL_JOIN(t) */ * from t, t1 where is_ipv4(t.b) and t.a = t1.a;
+------------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
| IndexJoin_25 | 9990.00 | root | | inner join, inner:Selection_24, outer key:test.t1.a, inner key:test.t.a, equal cond:eq(test.t1.a, test.t.a) |
| ├─IndexReader_37(Build) | 9990.00 | root | | index:IndexFullScan_36 |
| │ └─IndexFullScan_36 | 9990.00 | cop[tikv] | table:t1, index:a(a) | keep order:false, stats:pseudo |
| └─Selection_24(Probe) | 0.80 | root | | is_ipv4(cast(test.t.b, var_string(20))) |
| └─IndexLookUp_23 | 1.00 | root | | |
| ├─Selection_22(Build) | 1.00 | cop[tikv] | | not(isnull(test.t.a)) |
| │ └─IndexRangeScan_20 | 1.00 | cop[tikv] | table:t, index:a(a) | range: decided by [eq(test.t.a, test.t1.a)], keep order:false, stats:pseudo |
| └─TableRowIDScan_21(Probe) | 1.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+------------------------------------+---------+-----------+----------------------+-------------------------------------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.