walterddr commented on issue #12230:
URL: https://github.com/apache/pinot/issues/12230#issuecomment-1879779959
IMO this seems like a bug. b/c the behavior of
```
SELECT mvCol FROM tbl WHERE mvCol = 1
```
will return all the mvCol which CONTAINS the value `1`.
thus the behavior of exploding the mvCol during group by should happen after
the filter.
use Postgres as example, the logic can be express (without filter)
```
-- direct unnest
SELECT unnest(arr) AS arr_item, SUM(id)
FROM test
GROUP BY 1
-- nested subquery unnest
SELECT arr_item, SUM(id) FROM (
SELECT unnest(arr) AS arr_item, id FROM test) t
GROUP BY 1
```
however it really depending on how MV column is being considered here when
filter is added:
```
-- add to the outer query
SELECT arr_item, SUM(id) FROM (
SELECT unnest(arr) AS arr_item, id FROM test) t
WHERE arr_item = 1
GROUP BY 1
-- add to the inner query
SELECT arr_item, SUM(id) FROM (
SELECT unnest(arr) AS arr_item, id FROM test WHERE 1=ANY(arr) ) t
GROUP BY 1
```
i felt like the **inner query** version is more acceptable in the MV context
b/c it is not possible to add the `arr_item = 1` filter against the direct
unnest version of the query b/c `unnest(arr)` itself is not allowed to appear
in filter.
--
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]
