Multivalue equality filter with literal returns wrong value for mv column in SQL
- Dominant language
- Java
- Stars
- 14.1k
- Forks
- 3.8k
- Avg merge
- 2d 58m
- Merged PRs (30d)
- 233
Description
Multivalue equality filter with literal returns wrong value for mv column in SQL due to assuming transitivity wrongly
### Affected Version
24.0.0
### Description
SQL query rewrite to native query uses transitivity logic on multi value columns, and results in selecting the virtual column instead of the original expression
Example SQL:
```sql
SELECT id
, mv_col
FROM results_b
WHERE mv_col='one_value'
```
in the json native query, a virtual column is created w the literal value and that is used in the columns
```jsonc
// ...
"virtualColumns": [
{
"type": "expression",
"name": "v0",
"expression": "'one_value'",
"outputType": "STRING"
}
],
// ...
"columns": [
"id",
"v0"
],
// ...
```
There is no need for the virtual column and the original column should be in the columns:
```jsonc
// ...
"columns": [
"id",
"mv_col"
],
// ...
```
Contributor guide
Research direction
Reproduce the SQL example against the affected Druid version and inspect the generated native query, especially the transitivity rewrite for multivalue columns. Trace where the selected column is changed to the literal virtual column; done means the native query selects mv_col and does not create or select the unnecessary literal virtual column.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100