-- Create Columnstore tables:
drop table if exists test_a_cs;
create table if not exists test_a_cs (
	a_ID int,
    someName varchar(20)
) ENGINE=Columnstore;
insert into test_a_cs values (1, 'bla'),(2, 'xyz'),(3,'foo');

drop table if exists test_b_cs;
create table if not exists test_b_cs (
	b_ID int,
    FK_ID int,
    detailName varchar(20)
) ENGINE=Columnstore;
insert into test_b_cs values (1, 1, 'detail A for 1'), (2, 2, 'detail B for 2'), (3, 3, 'detail C for 3');

-- Join Columnstore and Columnstore:
SELECT
 a.a_ID as ID,
NVL(a.someName,'NICHT VORHANDEN') AS someName
-- (select d.detailName from test_b_cs d where ID = d.FK_ID) as detailNameWorks
-- ==> works
-- NVL((select d.detailName from test_b_cs d where ID = d.FK_ID), 'NA') AS detailNameError
-- ==> ERROR!!! Yields: Error Code: 1178. The storage engine for the table doesnt support IDB-3016: Function or Operator with sub query on te SELECT...
FROM test_a_cs a;

-- ====================================================
-- Create InnoDB tables::
drop table if exists test_a_innodb;
create table if not exists test_a_innodb (
	a_ID int,
    someName varchar(20)
) ENGINE=InnoDB;
insert into test_a_innodb values (1, 'bla'),(2, 'xyz'),(3,'foo');

drop table if exists test_b_innodb;
create table if not exists test_b_innodb (
	b_ID int,
    FK_ID int,
    detailName varchar(20)
) ENGINE=InnoDB;
insert into test_b_innodb values (1, 1, 'detail A for 1'), (2, 2, 'detail B for 2'), (3, 3, 'detail C for 3');

-- Join InnoDB with InnoDB:
SELECT
 a.a_ID as ID,
NVL(a.someName,'NICHT VORHANDEN') AS someName,
-- (select d.detailName from test_b_innodb d where ID = d.FK_ID) as detailNameWorks
-- ==> works
NVL((select d.detailName from test_b_innodb d where ID = d.FK_ID), 'NA') AS detailNameAlsoWorks
-- ==> Works here!!!
FROM test_a_innodb a;

-- Join InnoDB with Columnstore:
SELECT
 a.a_ID as ID,
NVL(a.someName,'NICHT VORHANDEN') AS someName,
(select d.detailName from test_b_innodb d where ID = d.FK_ID) as detailNameWorks
-- ==> works
-- NVL((select d.detailName from test_b_cs d where ID = d.FK_ID), 'NA') AS detailNameError
-- ==> ERROR!!! Yields: Error Code: 1178. The storage engine for the table doesnt support IDB-3016: Function or Operator with sub query on te SELECT...
FROM test_a_innodb a;

-- Join Columnstore with InnoDB:
SELECT
 a.a_ID as ID,
NVL(a.someName,'NICHT VORHANDEN') AS someName,
(select d.detailName from test_b_innodb d where ID = d.FK_ID) as detailNameWorks
-- ==> works
-- NVL((select d.detailName from test_b_innodb d where ID = d.FK_ID), 'NA') AS detailNameError
-- ==> ERROR!!! Yields: Error Code: 1178. The storage engine for the table doesnt support IDB-3016: Function or Operator with sub query on te SELECT...
FROM test_a_cs a;