apache / apache/druid

Like filter gives extra/unexpected results on multi valued dimension

Open
#18,357 0 comments 0 reactions 0 assignees View on GitHub
Area - Querying
Dominant language
Java
Stars
14.1k
Forks
3.8k
Avg merge
2d 58m
Merged PRs (30d)
233

Description

When using `Like` filter on multi valued fields, it returns extra/unexpected results. Example query
```
SELECT ("col_mvd") FROM "table"
WHERE "col_mvd" LIKE ('%abc%')
GROUP BY 1
ORDER BY "col_mvd"
LIMIT 10
```

I think its because the filter returns a bitmap index with matching rows however each row may have multiple values and those values are not match against filter and added to the results. Native search query returns correct results because it does match against actual filter [here](https://github.com/apache/druid/blob/master/processing/src/main/java/org/apache/druid/query/search/SearchQueryRunner.java#L136). Example search query
```
{
"queryType": "search",
"dataSource": "table",
"granularity": "hour",
"searchDimensions": [
"col_mvd"
],
"query": {
"type": "insensitive_contains",
"value": "abc"
},
"sort" : {
"type": "lexicographic"
},
"intervals": [
"2024/2025"
]
}
```

### Affected Version

All

Contributor guide

Open the contributing guide

Research direction

Reproduce the SQL query against a multi-valued dimension and compare its results with the native search query. Start with processing/src/main/java/org/apache/druid/query/search/SearchQueryRunner.java at the referenced line, then trace the Like filter's bitmap-index path. Done means multi-valued LIKE results exclude values that do not match the filter.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.