[MCOL-428] Implement OR correlated subquery Created: 2016-12-02  Updated: 2023-07-03  Resolved: 2023-07-02

Status: Closed
Project: MariaDB ColumnStore
Component/s: N/A
Affects Version/s: None
Fix Version/s: Icebox

Type: New Feature Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Gagan Goel (Inactive)
Resolution: Won't Do Votes: 5
Labels: community

Issue Links:
Blocks
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2020-4, 2020-5

 Description   

From the Google Groups, this query fails:

SELECT DISTINCT member_id
FROM member_store_transaction
WHERE member_id IN (
    SELECT member_id
    FROM member_store_transaction
)
OR member_id IN (
    SELECT member_id
    FROM member_store_transaction
)

With the error "Correlated subquery within OR operator is currently not supported".



 Comments   
Comment by Kleyson Rios [ 2017-06-11 ]

Facing the same issue:

2017/06/11 06:04:41 - Delete Registros.0 - Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Internal error: IDB-3033: Correlated subquery within OR operator is currently not supported.
2017/06/11 06:04:41 - Delete Registros.0 - Query is : delete from dbtest.h_closure_af 
2017/06/11 06:04:41 - Delete Registros.0 - where conta     in (select distinct conta_id from dbtest.d_h where emp = 1 ) or 
2017/06/11 06:04:41 - Delete Registros.0 -       conta_pai in (select distinct conta_id from dbtest.d_h where emp = 1 )

Comment by GUIDI [ 2018-08-29 ]

How to reproduce it in 1.1.5 version of Columnstore
```sql
CREATE TABLE `client_profil` (
`id_unique` bigint(12) NOT NULL DEFAULT 999999999999,
`nom` varchar(38) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8 ;

CREATE TABLE `sollicitations` (
`num_auto` int(11) NOT NULL,
`id_unique` bigint(12) NOT NULL DEFAULT 999999999999,
`recu` int(11) NOT NULL DEFAULT 0
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

SELECT cp.id_unique
FROM client_profil cp
WHERE cp.id_unique IN (
SELECT s.id_unique
FROM sollicitations s
WHERE s.num_auto IN ( 1, 2,3,4)
)
or cp.nom IN ( 'toto', 'tata');

ERROR 1815 (HY000): Internal error: IDB-3033: Correlated subquery within OR operator is currently not supported.
```

Comment by Matias Sánchez [ 2019-12-26 ]

Hi Guys? Any update on this compatibility issue supporting correlated subquery within or operator?

Comment by Roman [ 2020-04-09 ]

Greetings,
This feature is not scheduled in our roadmap yet.

Comment by Todd Stoffel (Inactive) [ 2020-08-27 ]

Since a subquery "OR" operator is not currently supported, would a union satisfy the request here?

SELECT cp.id_unique
FROM client_profil cp
WHERE cp.id_unique IN
    (SELECT s.id_unique
     FROM sollicitations s
     WHERE s.num_auto IN (1,2,3,4) )
UNION
SELECT cp.id_unique
FROM client_profil cp
WHERE cp.nom IN ('toto','tata');

You could even use GROUP BY to dedup

SELECT id_unique FROM (
SELECT cp.id_unique
FROM client_profil cp
WHERE cp.id_unique IN
    (SELECT s.id_unique
     FROM sollicitations s
     WHERE s.num_auto IN (1,2,3,4) )
UNION
SELECT cp.id_unique
FROM client_profil cp
WHERE cp.nom IN ('toto',
                 'tata')) AS t1
GROUP BY id_unique;

Comment by Todd Stoffel (Inactive) [ 2023-07-02 ]

The "create date" on this ticket is pre-convergence with MariaDB server. If the issue still exists in a modern version of the engine/plugin please submit a new ticket.

Generated at Thu Feb 08 02:21:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.