filtering based on MVA

Hello,

I need some help with filtering based on MVA column. I have a single MVA column that keeps attributes from differents sets. And I need to filter the records but I know only some of the attributes.

Let me show the example - config_id is MVA:

id: 1084596
config_id: 15,16,29

Now I need to filter out all the records that DO NOT contain value “1” in MVA column but they DO contain value “29”.
My SphinxQL examples:

select * from idx where id=1084596 and any(Config_id) ALL (1,29);
1 row in set (0.003 sec)

→ this returns the result, because one of the values (29) is in MVA

select * from idx where id=1084596 and all(Config_id) ALL (1,29);
Empty set (0.001 sec)

→ this does not return the result, almost good…

select * from idx where id=1084596 and all(Config_id) ALL (15,29);
Empty set (0.001 sec)

→ this does not return the result, wrong! Both values are in MVA, but there are other values too.

How to “join” results from #2 and #3 so I get empty result if value does not exist in MVA but get nonempty results if all supplied values are in MVA (but there are other values (which I do not know) too) ?

Regards
PP

PS. Manticore 3.6.0

Well in concept can do

 select * from idx where id=1084596 and config_id=29 and config_id!=1;

That should work as such, but can produce warnings. (just included for demonstration purposes)

So write it out in full as …

 select * from idx where id=1084596 and ANY(config_id) = 29 and ALL(config_id) != 1;

So at least one (ie any) of the mva values is 29.

And not 1 in the whole list. ie ‘all’ of them can be any value except 1.

Also just for completeness might find it clearer to write

 select * from idx where id=1084596 
     and ANY(config_id) IN(29) and ALL(config_id) NOT IN(1);

the concept of IN(…) might be easier to understand in relation to working with lists.

Thanks for your replies.

But this is not quite what I’ve meant. Let’s focus on my example #3.
I do not know which values I do not want. I just know values which I want. And if they are absent in MVA, I do not want the record to be returned. But I do not know every value in MVA field.

This behaviour has changed between version 3.5.6 and 3.6.0 and I missed information or the change is not documented.

Version 3.5.6:
select * from idx where id=1084596 and ANY(Config_id) IN (1) and ANY(Config_id) IN (29);
EMPTY (expected result, value 1 is absent in MVA)

Version 3.6.0:
select * from idx where id=1084596 and ANY(Config_id) IN (1) and ANY(Config_id) IN (29);
1 row in set (non-expected result, value 1 is absent in MVA)

Regards
PP

Then to be honest I dont understand what you trying to do :frowning:

But Wonder if the LENGTH() function could useful? Can use that to make sure there arent ‘other’ values in the set, Ie check there are arent more values than filtering by.

@premax, looks like you are looking for ALL() with a little bit different behaviour.
Currently ALL(mva) IN (array) is true when ALL values from the mva are found in the array.
What you are looking for is true when all values from the array are found in the mva.

Do I understand it right?

Yes Sergey! You understand this very well. Could you please confirm that this behaviour has changed in last version?

The behaviour of this

and ANY(Config_id) IN (1) and ANY(Config_id) IN (29);

indeed changed in 3.6.0. It was a bug. The bug was fixed. The general behaviour of ALL() didn’t change. I think it never changed since it was implemented.

@Sergey,
Thanks for clarification.
Now, when we know that this is a bug, do you have any idea how can I implement the old behaviour?
My idea is to query engine multiple times:

select * from idx where id=1084596 and ANY(Config_id) IN (1);
select * from idx where id=1084596 and ANY(Config_id) IN (29);

and then get common values from each result (compute intersection in app).
But this will take additional time.

Any other ideas?

Regards
PP

Since the bug was fixed you can just try a dev package. Please find here info about that:

Thank you. I have misunderstood that my expected behaviour was a bug and you are not fixing it.

Feel free to contact us via contact@manticoresearch.com if the inverted ALL() ... IN () behaviour is mission critical for you and you are ready to sponsor the development.

Otherwise we’ll appreciate if you at least file a feature request in github and if it turns out to be popular we’ll be able to prioritize it.