/*PREPARATION*/
drop table if exists test_ax.t1;
drop table if exists test_ax.t2;
drop table if exists test_ax.t3;
drop table if exists test_ax.t4;

create table test_ax.t1
(id int
)ENGINE=COLUMNSTORE DEFAULT CHARSET=UTF8MB4;

create table test_ax.t2
(id2 int,
snapshot_date date
)ENGINE=COLUMNSTORE DEFAULT CHARSET=UTF8MB4;

create table test_ax.t3
(id3 int,
from_date date,
to_date date
)ENGINE=COLUMNSTORE DEFAULT CHARSET=UTF8MB4;

create table test_ax.t4
(id4 int,
category varchar(64)
)ENGINE=COLUMNSTORE DEFAULT CHARSET=UTF8MB4;

insert into test_ax.t1(id) values(1);
insert into test_ax.t2(id2, snapshot_date) values(1,date(now()));
insert into test_ax.t3(id3, from_date, to_date) values(1,date(now()-interval 1 day), date(now()+interval 2 day));
insert into test_ax.t4(id4, category) values(1, 'cat-1');
/*PREPARATION END*/

/*THE PROBLEM: The SELECT itself below runs properly, but calling it from view the 1815 error is thrown*/
select * from test_ax.t1 t1
	LEFT JOIN test_ax.t4 t4 ON (t1.id = t4.id4)	
	LEFT JOIN test_ax.t2 t2 ON (t1.id = t2.id2)	
	LEFT JOIN test_ax.t3 t3 ON (t2.id2 = t3.id3
				AND t2.snapshot_date BETWEEN t3.from_date AND t3.to_date);

/*Creating VIEW based on the SELECT above*/
create or replace view test_ax.view_test as
select * from test_ax.t1 t1
	LEFT JOIN test_ax.t4 t4 ON (t1.id = t4.id4)
	LEFT JOIN test_ax.t2 t2 ON (t1.id = t2.id2)	
	LEFT JOIN test_ax.t3 t3 ON (t2.id2 = t3.id3
				AND t2.snapshot_date BETWEEN t3.from_date AND t3.to_date);

/*SELECT from the VIEW throws the error: Error Code: 1815. Internal error: On clause filter involving a table not directly involved in the outer join is currently not supported.*/
select * from test_ax.view_test;

/*Notes*/
/*If I remove either the 1st or the 3rd LEFT JOINs from the VIEW definition, the SELECT * from VIEW will work. (I cannot remove the 2nd LEFT JOIN since the 3rd is based on that one)*/
/*Replacing COLUMNSTORE engine to INNODB the view the SELECT * from VIEW will work, but if one of the tables is COLUMNSTORE the error will thrown*/