Allowing join between two index using MVA as criteria

[ivanghisleni]

Proposal:

Scenario: create a query search link this

select * from rt1 where id in (select sphinx_ids as id from rt2)

where

• rt1 and rt2 are teal-time indexes
• id is an integer
• sphinx_ids is an MVA (list of rt1.id)

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
• OpenAPI YAML updated and issue created to rebuild clients

[sanikolaev]

What might work is:

mysql> drop table if exists t; create table t; drop table if exists t2; create table t2(m multi); insert into t values(1); insert into t2 values(1, (1,2)); select * from t inner join t2 on any(t2.m) = t.id;
--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
drop table if exists t2
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
create table t2(m multi)
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
insert into t values(1)
--------------

Query OK, 1 row affected (0.00 sec)

--------------
insert into t2 values(1, (1,2))
--------------

Query OK, 1 row affected (0.00 sec)

--------------
select * from t inner join t2 on any(t2.m) = t.id
--------------

ERROR 1064 (42000): P01: syntax error, unexpected '(', expecting SUBKEY near '(t2.m) = t.id'

but this is not supported.

[ivanghisleni]

Sorry, what I mean is something like this:

select * from rt1 inner join rt2 on any(rt2.sphinx_ids) in rt1.id