https://pinot.apache.org/ logo
Join Slack
Powered by
Hi Team, any way to apply filter on Array type co...
# troubleshooting
a
Hi Team, any way to apply filter on Array type column? found out VALUEIN function, but it works with equality only it seems. Can someone please help and advice?
m
Does filter clause work?
a
Equals works perfectly with VLAUEIN(). wanted support for like , not in. but still valuein needs to be select clause, thats another restriction.
m
I mean there is a FILTER clause. @Atri Sharma can you point to the doc?
k
what kind of filter are you trying to apply? most filters should work on Multi Value by default
a
Normal ones like = , IN , NOT IN, LIKE, NOT LIKE
k
IN and NOT IN should work
a
We tried = , it was not working
m
What is the exact filtering you want to accomplish? Are you trying to select a row iff all values in MV column in a row match?
k
whats the query?
a
Let's assume, we have [health insurance, insurance] in mv column in a row. Now we have two cases, 1.Mv column in select clause and filter = 'insurance’ Select mv_column , sum(metric1) from table where mv_column = insurance group by mv_column 2.Just filter clause Select dim_column , sum(metric1) from table where mv_column = insurance group by dim_column Similarly IN, NOT IN, LIKE
k
those queries should work already
➕ 1
m
Note for MV column, if at least one entry matches the row is selected for equality/in etc
a
So in first case, we will get two rows instead of one, right? And will “not in” and “like” supported?
a
I don't quite understand. What's the issue?
@Anish Nair for the first query, do you want filtered results for both the projection and the sum, or just the sum?
a
Projection and sum.
m
@Anish Nair can you paste a SQL query, so we can use that for discussion? I think there is some confusion around the semantics of MV column
Assuming it is 
Select mv_column , sum(metric1) from table where mv_column = insurance group by mv_column
Only rows that have 
mv_column = insurance
will be counted once to perform the sum.
a
Yes, that was exactly what I was wondering
Hence my question
m
However, if the rows have multiple values, they will start showing up as separate groups. You use VALUEIN to filter out the additional “groups” that show up. But the result of 
sum
is not double counted.
All predicates work on MV column in the 
where
clause.
a
But this won't work in case, when we are trying to use contains or like. For example, Select mv_column, sum() from table where mv_column like 'health’ group by mv_column In this case, how we can filter ? Let's assume the mv Column have two values here. [ health insurance, insurance ]
m
When the column has 
[ health insurance, insurance ]
what are you expecting
a
Health insurance, should only be present in this case, in projection
m
@Atri Sharma does Filter clause work here with LIKE to remove it from projection and group-by?
a
Yes, it should.
m
Is there a user doc for it that can be given to Anish?
a
Also, not able to apply where LOWER(mv_column) = 'value1' , giving null pointer exception.
Copy code
[
  {
    "message": "QueryExecutionError:\nProcessingException(errorCode:450, message:InternalError:\njava.lang.NullPointerException\n\tat org.apache.pinot.core.operator.combine.GroupByOrderByCombineOperator.mergeResults(GroupByOrderByCombineOperator.java:242)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:119)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:50)",
    "errorCode": 200
  }
]
a
@Anish Nair Can you paste the query you tried which gave this exception?
I will debug this now
a
select cam_id, cam_tag_name camptag, count(1) from max_reporting_aggregations where statsdatehour_epoch between '2022-05-01 000000' and '2022-05-01 235959' and cam_id = 99710 and LOWER(cam_tag_name) in ('accelerated') group by cam_id,camptag order by cam_id limit 1000
a
Did you try FILTER with LIKE?
a
yes. it returns true if any value matches the regex. but its doesn't filter the groups.
a
Interesting, let me try out the LOWER query and see if I can reproduce the problem
a
okay. also wanted to know, if Scalar Function can accept MV Column as input. please let me know
k
Lower scalar function only works on single value not multi value.. can you file an issue..? We can probably add a generic function to apply any scalar function on an MV column
a
Sure.
Open in Slack
PreviousNext
Powered by