Intersect is not supported

create table if not exists c1 (id varchar(50)) engine=Columnstore DEFAULT CHARSET=utf8mb4;

create table if not exists c2 (id varchar(50)) engine=Columnstore DEFAULT CHARSET=utf8mb4;

create table if not exists i1 (id varchar(50)) engine=Innodb DEFAULT CHARSET=utf8mb4;

create table if not exists i2 (id varchar(50)) engine=Innodb DEFAULT CHARSET=utf8mb4;

insert into c1 (id) values(1),(2),(3),(4);

insert into c2 (id) values(3),(4),(5),(6);

insert into i1 (id) values(1),(2),(3),(4);

insert into i2 (id) values(3),(4),(5),(6);

select count(*) from

(

select id from c1 intersect

select id from c2

) a;

select count(*) from

(

select id from i1 intersect

select id from i2

) a;

MariaDB [d1]> select count(*) from

    -> (

    -> select id from c1 intersect

    -> select id from c2

    -> ) a;

+----------+

| count(*) |

+----------+

|        6 |

+----------+

1 row in set (1.074 sec)

MariaDB [d1]> select count(*) from

    -> (

    -> select id from i1 intersect

    -> select id from i2

    -> ) a;

+----------+

| count(*) |

+----------+

|        2 |

+----------+

1 row in set (0.000 sec)