`IN` filtering result are not correct.

[t0mpere]

Hey I'm performing queries like

          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:

I'm expecting this to have the same result as the union of the following queries:

         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_ids 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

[Jackie-Jiang]

By default, pinot only returns 10 groups (implicit LIMIT 10), and without order-by the result is indeterministic. Can you try running the query with a higher LIMIT, or add an order-by clause?

[t0mpere]

This happens even if the number of groups to return is less than 10. Can you explain how order by would change the behavior of this query?

[Jackie-Jiang]

To ensure I didn't misunderstand the problem, can you also post the responses for the above queries?

[t0mpere]

first query result:

job_id              | dt            | count
2023_08_14_10_10_01 | 1691712000000 | 1166708

union of the result of the last two queries:

job_id              | dt            | count
2023_08_14_10_10_01 | 1691712000000 | 1166708
union
2023_08_14_10_25_13 | 1691712000000 | 20349

This doesn't seem like a wanted behavior to me. It doesn't always happen, only with some combinations of job_id. I've figured this out while validating the data based on job_id. It gets worse if the number of job_id in the in clause gets bigger (adjusting limit accordingly of course).

[Jackie-Jiang]

I see you are using star-tree index according to the plan. Can you try adding SET useStarTree = false; to your query and see if the results are expected?