Hey <@UGRJA9TEH> I am facing a weird issue when fi...
# troubleshooting
b
Hey @Xiang Fu I am facing a weird issue when filtering on a multivalued field. where clause if not working correctly.
Screenshot 2025-08-25 at 3.49.14 PM.png
Pinot version: 1.2.0
it is giving result for other manager_ids also even though I have added one manager_id in filter
x
the filter will match any row contains the manager_id. And the group by will still use all the manager_ids
so you will see other manager ids, and you can try to use the post filter like
HAVING $0='GUxxx'
b
Copy code
SELECT
  arrayToMv(manager_ids),
  COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
  COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
  COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
FROM ace_watchtower_agents_agent_kpi_events
WHERE
  event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1')
GROUP BY arrayToMv(manager_ids) having $0='GU2406261237361289228350'
this is giving exception. Can you please help in modifying this query
@Xiang Fu Please help ^
x
sorry, need to do this:
Copy code
WITH agg AS (
  SELECT
    arrayToMv(manager_ids) AS manager_id,
    COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
    COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
    COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
  FROM ace_watchtower_agents_agent_kpi_events
  WHERE event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1')
  GROUP BY arrayToMv(manager_ids)
)
SELECT *
FROM agg
WHERE manager_id = 'GU2406261237361289228350';
b
following also seems to be working
Copy code
SELECT
  arrayToMv(manager_ids) as m,
  COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
  COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
  COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
FROM ace_watchtower_agents_agent_kpi_events
WHERE
  event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1')
GROUP BY m  having m ='GU2406261237361289228350'
@Xiang Fu ^
please confirm if this is also okay or this has some hidden issue
x
Yes, this works
b
okay, thanks for the help @Xiang Fu
One more doubt @Xiang Fu
Copy code
SELECT
    arrayToMv(manager_ids) AS manager_id,
    COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
    COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
    COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
  FROM ace_watchtower_agents_agent_kpi_events
  WHERE event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1') 
  and arrayToMv(manager_ids) in ('GU2406261237361289228350')
  GROUP BY arrayToMv(manager_ids)
this gives result ^
b
Copy code
WITH agg AS (
  SELECT
    arrayToMv(manager_ids) AS manager_id,
    COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
    COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
    COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
  FROM ace_watchtower_agents_agent_kpi_events
  WHERE event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1') 
  and arrayToMv(manager_ids) in ('GU2406261237361289228350')
  GROUP BY arrayToMv(manager_ids)
)
SELECT *
FROM agg
WHERE manager_id = 'GU2406261237361289228350';
but this gives 0 rows in result
I want to put manager_ids filter in where so that result set is limited.
@Xiang Fu ^ Is there anything wrong with the query or there is some limitation for using same condition in where and having
x
I have a feeling that this WITH agg after the query plan got pushed down to one single predicate, so I think having is the better solution here
use that
HAVING m= 'GU2406261237361289228350'
🌟 1
b
Screenshot 2025-08-26 at 11.48.39 AM.png
@Xiang Fu ^ having works but it is still giving aggs for other managers
Please help in concluding it if this works or not. Only other option I am left with is ingest one row per manager_id
Copy code
SELECT
  arrayToMv(manager_ids) as m,
  COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
  COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
  COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
FROM ace_watchtower_agents_agent_kpi_events
WHERE
  event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1')
GROUP BY m  having m in ('GU2406261237361289228350')
I think multivalued fields works only for 'filtering in where' not for 'group by having'
x
let me check this
b
thanks
@Xiang Fu please guide here ^. I need to decide the data model based on this feature
@Xiang Fu I am still waiting please help in closing. I am chasing a deadline regarding data modeling
x
Copy code
WITH agg AS (
  SELECT
    arrayToMv(manager_ids) AS manager_id,
    COUNT(CASE WHEN event_name = 'ss_deployed_v1' THEN 1 END) AS ss_deployed,
    COUNT(CASE WHEN event_name = 'qr_deployed_v1' THEN 1 END) AS qr_deployed,
    COUNT(CASE WHEN event_name = 'merchant_onboarded_v1' THEN 1 END) AS merchant_onboarded
  FROM ace_watchtower_agents_agent_kpi_events
  WHERE event_name IN ('ss_deployed_v1', 'qr_deployed_v1', 'merchant_onboarded_v1')
  and arrayToMv(manager_ids) in ('GU2406261237361289228350')
  GROUP BY arrayToMv(manager_ids)
  LIMIT 10000000
)
SELECT *
FROM agg
WHERE manager_id = 'GU2406261237361289228350';
somehow only adding the
LIMIT
clause will break the query into two stages so the filter will work in the outer query
b
Will it make the query run slow? @Xiang Fu
Or it is just a hint that will force the query run into 2 stages
x
it's forcing 2 stages, the most computation will happen at leaf stage, so the query perf impact is minimal
b
Sure. I will check this one and come back if any more issues. @Xiang Fu Thanks a lot for helping