xiangfu0 commented on issue #12230: URL: https://github.com/apache/pinot/issues/12230#issuecomment-4113973473
The new `filterMv` function in #17659 addresses this directly. The core problem is that a WHERE filter matches at the row level, but the MV column still retains all its elements in the GROUP BY keys. `filterMv` lets you apply element-level filtering so only matching values appear in aggregation results: ```sql -- Before: WHERE filters the row but array_col still contains all elements in GROUP BY -- SELECT count(foo) FROM myTable WHERE array_col = 1 GROUP BY array_col -- Returns: [1] -> 1, [2] -> 1 (wrong — [2] shouldn't appear) -- After: filterMv restricts the MV elements before grouping SELECT filterMv(array_col, 'v = 1') AS filtered, COUNT(foo) FROM myTable GROUP BY filterMv(array_col, 'v = 1') -- Returns: [1] -> 1 (correct) ``` This eliminates the need for the `VALUEIN` workaround mentioned in the issue. The predicate uses `v` as a placeholder for each MV element and supports `=`, `!=`, `>`, `>=`, `<`, `<=`, `IN`, `NOT IN`, `BETWEEN`, `REGEXP_LIKE`, and compound predicates with `AND`/`OR`/`NOT`. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
