CH-multi-join

drop table fact;
drop table animals;
drop table colors;

create table fact(id Int64, animal_key Int64, color_key Int64) Engine = MergeTree order by tuple();
insert into fact values (1,1,1),(2,2,2);

create table animals(animal_key UInt64, animal_name String) Engine = MergeTree order by tuple();
insert into animals values (0, 'unknown');

create table colors(color_key UInt64, color_name String) Engine = MergeTree order by tuple();
insert into colors values (0, 'unknown');


select  id, animal_name, a.animal_key, color_name, color_key
from fact a
        left join (select toInt64(animal_key) animal_key, animal_name from animals) b on (a.animal_key = b.animal_key)
        left join (select toInt64(color_key) color_key, color_name from colors) c on (a.color_key = c.color_key)

color_key -- возвращается из правой таблицы???

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

drop table f;
drop table k;
drop table v;

create table f(key Int64, val String) Engine = Memory;
insert into f values (1,'1'),(2,'2');

create table k(key Int64) Engine = Memory;
insert into k values (0);

create table v(val String) Engine = Memory;
insert into v values ('0');


select key,val from f a
        left join k b on (a.key = b.key)
        left join v c on (a.val = c.val)

проблема с однобуквенными таблицами?

--------------------------------------------------------
CREATE TABLE a1 ( ANIMAL Nullable(String) ) engine = MergeTree order by tuple();
insert into a1 values('CROCO');

select * from a1 

select count() from a1 a join a1 b on (a.ANIMAL = b.ANIMAL) 
prewhere ANIMAL = 'CROCO'


select count() from a1 a join a1 b on (a.ANIMAL = b.ANIMAL) join a1 c on (c.ANIMAL = b.ANIMAL) 
where ANIMAL = 'CROCO'

prewhere не работает???

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