Support reverse MVA ALL search

[markomilivojevic]

Proposal:

Given that the languages column is MVA, the following:

• works:

  select * from test where all(languages) in (1, 2);

• doesn't work:

  select * from test where all(1, 2) in languages;

I want to be able to return all results from the test index where each row contains both languages (1, 2) among the others.

Should have JSON/PHP support as well.

Checklist:

^{To be completed by the assignee. Check off tasks that have been completed or are not applicable.}

- [ ] Implementation completed - [ ] Tests developed - [ ] Documentation updated - [ ] Documentation reviewed - [ ] [Changelog](https://docs.google.com/spreadsheets/d/1mz_3dRWKs86FjRF7EIZUziUDK_2Hvhd97G0pLpxo05s/edit?pli=1&gid=1102439133#gid=1102439133) updated - [x] OpenAPI YAML updated and issue created to rebuild clients

[sanikolaev]

This can be used as a workaround:

mysql> drop table if exists test; create table test(languages multi); insert into test values(1, (1)), (2,(2)), (3,(1,2)), (4,(2,1)), (5,(1,2,3)); select * from test where any(languages) in (1) and any(languages) in (2);
--------------
drop table if exists test
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table test(languages multi)
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
insert into test values(1, (1)), (2,(2)), (3,(1,2)), (4,(2,1)), (5,(1,2,3))
--------------

Query OK, 5 rows affected (0.00 sec)

--------------
select * from test where any(languages) in (1) and any(languages) in (2)
--------------

+------+-----------+
| id   | languages |
+------+-----------+
|    3 | 1,2       |
|    4 | 1,2       |
|    5 | 1,2,3     |
+------+-----------+
3 rows in set (0.00 sec)
--- 3 out of 3 results in 0ms ---

[markomilivojevic]

Ok, thanks, that works. Can be closed.