10.5.4-2 mariadb enterprise server

allen.lee@mariadb.com Allen - can you try to change the query slightly? And then, if it works, run the original on InnoDB and the one below on columnstore and compare the results to make sure they return identical results.

CREATE VIEW `vw_triggerevent_assoc_bcap_cases`
AS
SELECT `cc`.`CASEID` AS `CASEID`
FROM (((`cas_triggerevent` `ct`
JOIN `cas_case` `cc`
ON(`ct`.`ASSOCIATED_BCAP_CASEID` = `cc`.`CASEID` AND `ct`.`SOURCE_DB` = `cc`.`SOURCE_DB`))
JOIN `cas_bcapkyc` `cb`
ON(`cb`.`CASEID` = `cc`.`CASEID` AND `cb`.`SOURCE_DB` = `cc`.`SOURCE_DB` AND `cb`.`gcin1` = `cc`.`GCIN`))
LEFT JOIN (SELECT MAX(`e`.`INDATE`) AS INDATE, `e`.`CASEID`, `e`.`SOURCE_DB`, `e`.`STATUSCODE`, `STATUSCODE` FROM `cas_caseflow` `e`
GROUP BY `e`.`CASEID`, `e`.`SOURCE_DB`, `e`.`STATUSCODE`, `e`.`STATUSCODE`) `cf`
ON(`cc`.`CASEID` = `cf`.`CASEID` AND `cc`.`SOURCE_DB` = `cf`.`SOURCE_DB`
AND `cf`.`STATUSCODE` = 'CompletedScreening' AND `e`.`STATUSCODE` = `cf`.`STATUSCODE`
AND `cc`.`INDATE` = 'cf'.INDATE))
WHERE `cc`.`REQUESTTYPECODE` IN ('CBGBranchesRequest','BCAPApplication')

Background. The simplest reproduction is as follows:

MariaDB [(none)]> create database gjd;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use gjd;
Database changed
MariaDB [gjd]> create table t1 (a int, b int, c int, d int) engine=columnstore;
Query OK, 0 rows affected (0.648 sec)

MariaDB [gjd]> insert into t1 values (1,10,100,1000),(2,20,200,2000),(3,30,300,3000);
Query OK, 3 rows affected (0.395 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [gjd]> select * from t1 q1 left join t1 q2 on q1.a = (select a from t1 limit 1);
ERROR 1815 (HY000): Internal error: IDB-1015: Subquery on OUTER JOIN ON clause is currently not supported.
MariaDB [gjd]> select * from t1 q1 left join (select a from t1 limit 1) q2 on q1.a=q2.a;
----------------------

----------------------

----------------------
3 rows in set, 1 warning (0.195 sec)

A little more complex but closer to the original:

MariaDB [gjd]> drop database gjd;
Query OK, 1 row affected (0.591 sec)

MariaDB [(none)]> create database gjd;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> use gjd;
Database changed
MariaDB [gjd]> create table t1 (a int, b int, c int, d int) engine=columnstore;
Query OK, 0 rows affected (0.843 sec)

MariaDB [gjd]> insert into t1 values (1,10,100,1000),(1,10,100,2000),(1,10,100,3000);
Query OK, 3 rows affected (0.565 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [gjd]> select * from t1 q1 left join t1 q2 on q1.a=q2.a and q1.b=q2.b and q1.c=q2.c and q2.d = (select max(d) from t1 q3 where q1.a=q3.a and q1.b=q3.b and q1.c=q3.c);
ERROR 1815 (HY000): Internal error: IDB-1015: Subquery on OUTER JOIN ON clause is currently not supported.
MariaDB [gjd]> select * from t1 q1 left join (select a,b,c,max(d) as d from t1 group by a,b,c) q2 on q1.a=q2.a and q1.b=q2.b and q1.c=q2.c and q1.d=q2.d;
----------------------------------+

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

----------------------------------+
3 rows in set, 1 warning (0.178 sec)

drrtuy - this is less horrific than what we thought. We can do correlated subqueries and use them in join clauses, just not with outer joins. Probably is "just a bug", in this particular case. Does not make what we discussed untrue. And - if DBS accepts workaround, assuming it is confirmed as producing the right results, it wouldn't be a high priority for now, at least in my opinion.

MariaDB [gjd]> create table t1 (a int, b int, c int, d int) engine=columnstore;
Query OK, 0 rows affected (0.601 sec)

MariaDB [gjd]> insert into t1 values (1,10,100,1000),(2,20,200,2000),(3,30,300,3000);
Query OK, 3 rows affected (0.397 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [gjd]> select * from t1 q1 left join t1 q2 on q1.a = (select a from t1 limit 1) and q1.b=q2.b and q1.c=q2.c and q1.d=q2.d;
ERROR 1815 (HY000): Internal error: IDB-1015: Subquery on OUTER JOIN ON clause is currently not supported.

MariaDB [gjd]> select * from t1 q1 join t1 q2 on q1.a = (select a from t1 limit 1) and q1.b=q2.b and q1.c=q2.c and q1.d=q2.d;
----------------------------------+

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

----------------------------------+
1 row in set, 1 warning (0.121 sec)

I believe this is b/c you try to join BASEID columns from ct(varchar (10))
and cc (INTEGER). I think there might be a mistake in the DDL or the field
in ct and all other tables must become an integer column.

Or varchar(10)

All right, please provide the complete DDL for all tables involved, I will do full research and investigation myself. Best if you e-mail it to me, but you can also post it here. Make sure it is for all tables involved in the query.