apache / apache/pinot

`IN` filtering result are not correct.

Open
#11,344 5 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

Hey I'm performing queries like
```SQL
SELECT
job_id,
DATETRUNC('day', ts) as dt,
sum(c)
FROM TABLE
WHERE
job_id in ( '2023_08_14_10_10_01', '2023_08_14_10_25_13')
GROUP BY job_id, dt
```
Query plan:
image

I'm expecting this to have the same result as the union of the following queries:
```SQL
SELECT
job_id,
DATETRUNC('day', ts) as dt,
sum(c)
FROM TABLE
WHERE
job_id = '2023_08_14_10_10_01'
GROUP BY job_id, dt
---------------------------------------------------------------
SELECT
job_id,
DATETRUNC('day', ts) as dt,
sum(c)
FROM TABLE
WHERE
job_id = '2023_08_14_10_25_13'
GROUP BY job_id, dt
```

But there's some occasions where this is not the case and some `job_id`s are left out.

Table is configured like this:
```
"tableIndexConfig": {
"rangeIndexVersion": 2,
"autoGeneratedInvertedIndex": false,
"createInvertedIndexDuringSegmentGeneration": false,
"sortedColumn": [],
"bloomFilterColumns": [],
"noDictionaryColumns": [
"d"
],
"invertedIndexColumns": [],
"onHeapDictionaryColumns": [
"c"
],
"varLengthDictionaryColumns": [
"b",
"a"
],
"enableDefaultStarTree": false,
"starTreeIndexConfigs": [
{
"dimensionsSplitOrder": [
"ts",
"job_id",
[...]
],
"skipStarNodeCreationForDimensions": [],
"functionColumnPairs": [
"SUM__c",
],
"maxLeafRecords": 10000
}
],
"enableDynamicStarTreeCreation": true,
"aggregateMetrics": false,
"nullHandlingEnabled": false,
"optimizeDictionary": true,
"optimizeDictionaryForMetrics": true,
"noDictionarySizeRatioThreshold": 0,
"rangeIndexColumns": []
},
```

Am I doing something wrong here or is this a bug?

Current configuration:
GKE
version 0.12.1
GCS for deep storage
3 ZK - 8 CPU and 18GB ram
6 Servers - 16CPU and 32 64GB ram 1.45TB SSD
2 Controllers - 16 CPU and 32GB ram
2 Brokers - 5 CPU 16.25GB ram
32 Minions - 2 CPU and 2GB of ram

1M Segments 4TB of data

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the reported SQL query and comparing its IN results with the two equality queries on a table using the shown star-tree and indexing configuration. Inspect the query plan and filtering behavior, then confirm that every job_id returned by either equality query also appears in the IN result.

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.