Comparing dimensions to each other in a filter

[erikdubbelboer]

We would like to model something like the following SQL query in Druid:

SELECT *
FROM t
WHERE t.a == t.b

As far as I can see it's not possible to compare dimensions to each other in Druid. Or is it?

Would it be possible to add some new filter like:

"filter": { "type": "dimensions", "dimensions": [<dimension_string>, <dimension_string>, ...], "op": <"=="|"!="> }

Which would allow you to compare 2 or more dimensions to each other.

It's not possible top speed up queries with this filter so I guess it should be applied once a columns is selected to be processed (does that make sense?).

[erikdubbelboer]

I think I can implement this by adding another method to ValueMatcherFactory.java like makeValueMatcher(String[] dimensions, Comparator<String> compare). The implementation in StringValueMatcherColumnSelectorStrategy would be quite easy and just passes different dimensions values to the comparator (would need a custom comparator class if we want to support comparing more than 2 dimensions at once). Implementing the actual Filter after this is quite easy.

Does that seem accurate?

[gianm]

ValueMatcherFactory doesn't exist anymore! It was removed in #3797 to make it possible for filters to be more flexible. The new Filter interface should let you do this. You want a filter that always returns false from "supportsBitmapIndex", throws unsupported operation exception from getBitmapIndex, and in "makeMatcher" it should create two selectors and build a ValueMatcher that compares their results.

That sounds like a useful contribution if you're interested in working on it.

We'll need to figure out what the semantics of "a == b" should be if a and b are both multi-value columns. Only true if the lists of values are identical? Or true if there's any overlap in values?

[erikdubbelboer]

Should I modify ValueMatcherColumnSelectorStrategy and StringValueMatcherColumnSelectorStrategy and add a method to match multiple columns with each other? I think that's probably cleanest.

How about if instead of making a new filter I extend the selector filter by adding an valueDimension parameter. So either value or valueDimension need to be set. Then we can keep the current semantics of selector with the addition that if valueDimension is multi-value all of its values need to be in dimension.

[gianm]

Hmm that could work too, although "selector" is kind of a weird name for a filter that checks if two columns are equal. If you do it that way then how about having a list called "dimensions"? So the syntax would be:

{"type": "selector", "dimensions": ["a", "b"]}

I think "they're equal if there is any overlap" is probably the best semantic for multi-value, since:

• it is symmetric, such that selector(["a", "b"], ["a", "b", "c"]) and selector(["a", "b", "c"], ["a", "b"]) are both true
• it is a proper extension of single-value, such that selector(["a", "b"], "b") and selector(["a", "b"], ["b"]) are both true

[erikdubbelboer]

@gianm with those semantics it will not be possible to check if a is in b but b is not in a. But I guess modifying the in filter to do this would make more sense.

[erikdubbelboer]

Then we have one semantics issue left and that is extractionFn. I guess it should applied to all dimensions.

[gianm]

That sounds reasonable; may also be useful to allow an extractionFns where they positionally apply to each of the dimensions.

[erikdubbelboer]

I'm already working on a pull request: erikdubbelboer@85c4e82#diff-9c0344041e595e113f03e0d783939f88
So far I only wrote the code and got it to pass all current tests. No idea yet if it actually works. Will write tests for it next.