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) ?
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)
Then to be honest I dont understand what you trying to do
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.
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.
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.