expression: support pushing down JSON_OVERLAPS to coprocessor
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Feature Request
### Is your feature request related to a problem? Please describe
`JSON_OVERLAPS` is supported as a TiDB builtin and can be used by MVIndex/IndexMerge for positive JSON membership predicates, but it is not currently pushed down as a normal coprocessor predicate.
For example:
```sql
SELECT *
FROM t
WHERE JSON_OVERLAPS(json_values->'$', JSON_ARRAY('v1', 'v2', 'v3'));
```
The filter is evaluated at the TiDB root side, so rows must be returned from storage before the predicate can be applied. This can add root-side JSON evaluation cost and extra network traffic when the query shape cannot use MVIndex as the access path, or when the predicate remains as a filter after joins.
This is separate from JSON/MVIndex selectivity estimation in https://github.com/pingcap/tidb/issues/69727. Better estimation helps the optimizer choose a better plan; `JSON_OVERLAPS` pushdown would reduce execution cost when the predicate still needs to be evaluated as a filter.
### Describe the feature you'd like
Support pushing down `JSON_OVERLAPS` as a coprocessor scalar function, at least to TiKV, and to TiFlash if the storage engine supports compatible JSON semantics.
In TiDB this likely requires:
- adding a TiPB scalar function signature for `JSON_OVERLAPS`;
- setting the pb code in `jsonOverlapsFunctionClass.getFunction`;
- adding it to pushdown support checks where appropriate;
- adding DistSQL builtin decoding;
- adding tests that `JSON_OVERLAPS` can appear in `cop[tikv]` / supported storage engines when pushdown is enabled.
### Describe alternatives you've considered
MVIndex can help positive JSON membership predicates when it is applicable as an access path. However, not all query shapes can use MVIndex directly, and `JSON_OVERLAPS` can still remain as a normal filter. HashJoin hints or SQL bindings can mitigate some bad join plans, but they do not reduce root-side JSON evaluation cost.
### Teachability, Documentation, Adoption, Migration Strategy
Documentation should clarify which JSON functions can be pushed down and under which storage engines. Once implemented, `JSON_OVERLAPS` should be documented consistently with existing JSON function pushdown support.
Contributor guide
Research direction
Start by tracing jsonOverlapsFunctionClass.getFunction and existing JSON function pushdown implementations. Then inspect the TiPB scalar signature, pushdown support checks, DistSQL builtin decoding, and cop[tikv]/TiFlash tests named in the issue. Done means JSON_OVERLAPS is recognized as a coprocessor predicate for supported engines and its storage-engine documentation is consistent.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases, distributed-systems
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100