[MCOL-2147] Semi-join with correllated subquery case .. when ... end in the join key. Created: 2019-02-08  Updated: 2023-10-25  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.1.6, 1.2.2
Fix Version/s: 23.10

Type: Bug Priority: Major
Reporter: Richard Stracke Assignee: Roman
Resolution: Won't Fix Votes: 1
Labels: None

Issue Links:
Relates
relates to MCOL-2165 Autoswitch broken in some cases in 1.2.2 Closed
relates to MCOL-1531 ColumnStore fails to make inner join ... Closed
relates to MCOL-1963 GROUP BY doesn't process IN + correla... Closed
relates to MCOL-1964 GROUP BY doesn't process NOT IN + cor... Closed
Sprint: 2020-3, 2020-4, 2020-5, 2020-6, 2020-7

 Description   

Error happened with set infinidb_vtable_mode 1 and 2.

Prepare:

set infinidb_vtable_mode= 2;
 
create table cs1 (t varchar(2) , i int) engine = columnstore;
 
create table cs2(t varchar(2) , i int) engine = columnstore;


select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
 
 
#ERROR 1815 (HY000): Internal error: IDB-1000: 'cs1' and 'cs3' are not joined.

Error message is different with a condition before the case condition in the where clause.

select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = cs2.t and cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
 
 
#ERROR 1815 (HY000): Internal error: IDB-3034: Query is not supported.  Unknown column '' in subquery.



 Comments   
Comment by Richard Stracke [ 2019-02-11 ]

Bug already exist in 1.1.6,
but only with set infinidb_vtable_mode= 1;

So automatic mode decision is different between 1.1.6 and 1.2.2

Comment by Roman [ 2019-02-11 ]

GTK. I didn't look into this but GROUP BY handler could also cause this.

Comment by Roman [ 2020-05-11 ]

MCS can't support natively a simplified version of this type:

MariaDB [test]>   select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = cs1.t );
 
ERROR 1815 (HY000): Internal error: IDB-1000: 'cs1' and 'cs2' are not joined.

This causes the failure of the original issue not the CASE function itself.

When run with columnstore_select_handler=off it works though:

MariaDB [test]> set columnstore_select_handler=off;
 
Query OK, 0 rows affected (0.001 sec)
 
 
 
MariaDB [test]> select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = (case cs2.t when 0 then cs1.t else cs2.t end));
 
+----------+
 
| count(1) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set, 1 warning (0.012 sec)

It also works with a simplified version of the original query:

MariaDB [test]> set columnstore_select_handler=on;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select count(1) from cs1 where exists (select 1 from cs2 where cs1.t = (case cs2.t when 0 then 0 else cs2.t end));
 
+----------+
 
| count(1) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set, 1 warning (0.031 sec)

The suggested solution is to check for this corner case: semi-join + correlated subquery with case in join key if case contains both top-level table column and a column(or a function) from the subquery.

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