I'm running into an issue on 1.0.6, 1.0.7, and 1.0.8 when performing a cross engine select against a view containing a group by atop innodb tables.

If I use a straight_join optimizer hint I can force the optimizer to not perform an aria temp table on disk; I get the correct result set (albeit with a query execution time of 4 sec). It is only when the optimizer decides to use a temp table on disk (aria) that the performance degrades considerably (27 sec, 10 for the temp table and 17 for sending data) and the group by result set only returns a value for the view's first pivot GROUP_CONCAT(IF()); the remaining pivot columns contain an empty string ('', not null).

The same view atop innodb tables within maraidb 10.1.19, with the same explain plan using a temp table on disk returns the correct result set in under 600 ms.

As I am able to get the cross engine select to run with a group by and return the correct result set; I'd be more inclined that the underlying problem is with the use of aria temp tables and the columnstore optimizer.

Reference MCOL-515 as appearing to be similar behavior with group by in a sub select and an aria temp table:

https://jira.mariadb.org/projects/MCOL/issues/MCOL-515?filter=allopenissues