https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# troubleshooting
s
This message was deleted.
t
One hack I can think is
Copy code
SELECT
CONCAT(tags, '-', count(*)) as tag_count
FROM inline_data
where tags like 't3'
group by tags
having tag_count like 't3%'
One issue with using this hack is it might break in future because of delimiter we use to add tags to count
v
There are two points here: first 
MV_CONTAINS(tags, 't3')
is probably the true way of representing the filter that you want. 
tags = 't3'
semi-works for strange back compat reasons but it can lead to bad things since the SQL planner can simplify it incorrectly. It plans to the same native construct (selector) but plays nicer with the SQL side of the world.
now I am sure you have discovered that 
GROUP BY tags
+ 
HAVING tags = 't3'
does not work as you want... this is because the SQL planner is trying to be too cool and push the HAVING into a WHERE
this is because it is not aware of the special nature of 
tags
side note: what is the special nature of 
tags
? well you see tags is not an array, it is a multi-value string (thus 
MV_
). I always imagine MV strings as being in super-position with themselves. Like a single tags can be both t1 and t2 and t3. Hence 
tags = 't1'
and 
tags = 't2'
can both be true for the same row. This is a concept of Druid that was inherited from the native system but it does not translate to SQL as there is no SQL equivalent. SQL has `ARRAY`s but they have different semantics so Druid can not just pretend that 
tags
is an Array as its storage format for MV columns is different.
anyhow... 
MV_FILTER_ONLY(tags, ARRAY ['t3'])
will restrict what values tags can be (think of it like 
Array#filter
in JS).
BTW your workaround works because it is complex enough that the planner can not do its fancy push HAVING into WHERE
the query I posted is the 'correct' way to do it. It is what we do in Imply Pivot
Make sure to check out the other 
MV_*
functions https://druid.apache.org/docs/latest/querying/sql-multivalue-string-functions.html the 8th one will blow your mind!
🤯 1
t
Thanks a million@Vadim for the detailed explanation. Also for most of our use cases we want to filter on multi values starting with a certain pattern. Like this one. Looks like MV_ functions doesn't take regex as a parameter
Copy code
SELECT tags, count(*)
FROM inline_data
where tags like 't3%'
group by tags
having tags like 't3%
. In native query this can be done using
Copy code
"dimensions": [
    {
      "type": "prefixFiltered",
      "delegate": {
        "type": "default",
        "dimension": "tags",
        "outputName": "tags"
      },
      "prefix": "t3%"
    }
  ],
g
that would be an excellent new feature
t
@meili
@Bhanu Kovuri
2 Views
Open in Slack
PreviousNext
Powered by