Preparing a test table

Manticore has a number of functions to handle the multi-value(MVA) data type. More details on the datatype can be found here.

Let's see at the examples of how the ANY(), ALL() and IN() functions can be used with MVA-attributes.

First, connect to Manticore using the MySQL client:

mysql -P9306 -h0

Create a test table with a multi-valued attribute:

DROP TABLE IF EXISTS t;

CREATE TABLE t(m multi);

And populate it with a few values:

INSERT INTO t VALUES(1, (1)),(2, (2)),(3, (3)),(12,(1,2)), (123, (1,2,3)), (23, (2,3)), (13, (1, 3));

SELECT * FROM t;

ANY()

Let's run a simple conditional search on the table we've just created which finds documents with 1 in m:

SELECT * FROM t WHERE m = 1;

In fact, such query performs the ANY(m) call behind the scenes and, additionally, triggers a warning. We can see this warning by running the SHOW WARNINGS statement:

SHOW WARNINGS;

To avoid warnings, we can call the ANY(m) function in the query instead:

SELECT * FROM t WHERE ANY(m) = 1;

SHOW WARNINGS;

As we see, it produces the same result, but it doesn't result in a warning since ANY(m) has been specified explicitly.

The following query finds documents with at least one value in m equaling 1 or 3:

SELECT * FROM t WHERE ANY(m) = 1 OR ANY(m) = 3;

The following query finds documents with at least one value in m equaling 1 and another value equaling 2:

SELECT * FROM t WHERE ANY(m) = 1 AND ANY(m) = 2;

And the following one finds documents with at least one value in m not equaling 1:

SELECT * FROM t WHERE ANY(m) != 1;

In this case, all documents except for document #1, which has just a single value 1 in the MVA, are included.

ALL()

Now let's look at similar examples using the ALL() function.

The following query finds documents with all values in m equaling 1. There's only one such document in our table

SELECT * FROM t WHERE ALL(m) = 1;

The following query tries to find documents with all values in m equaling 1 or 2:

SELECT * FROM t WHERE ALL(m) = 1 OR ALL(m) = 2;

The following query finds documents with all values in m not equaling 1, meaning that if a document has at least one 1 in the MVA, it will not be a match.

SELECT * FROM t WHERE ALL(m) != 1;

ANY() and ALL() with the IN() function

The ANY and ALL() functions can be used together with the IN() function allowing to apply multi-valued conditions as well.

The following query finds documents with all values in m equaling 1 or 2:

SELECT * FROM t where ALL(m) IN (1,2);

And the following one finds documents with at least one value in m equaling 1 or 2:

SELECT * FROM t where ANY(m) IN (1,2);

The following query, on the contrary, finds documents with at least one value in m not equaling neither 1 nor 2. I.e., it excludes documents with (1), (2), and (1,2) in the MVA.

SELECT * from t WHERE ANY(m) NOT IN (1,2);

And the following one finds documents where all values do not equal neither 1 nor 2. Otherwise, a document will not be a match.

SELECT * from t WHERE ALL(m) NOT IN (1,2);