In playing a little with ClickHouse, issues with joins were one of the biggest things that jumped out at me.
It wouldn't change the join order to try to ensure the smaller side was in RAM. It wouldn't propagate WHERE conditions to the other side of a join, so "WHERE a.customer_id=3 AND b.customer_id=a.customer_id" wouldn't automatically filter the scan of b to blocks where b.customer_id=3. Projections could theoretically be used to speed joins on the projection's ORDER BY columns, but it didn't appear to happen. You'd sometimes need to manually set the join algo for best performance, when I'd rather the optimizer choose it, maybe adaptively based on whether it seemed to make sense to do a join in-RAM.
ClickHouse was also great at certain things, like scanning a ton of rows very efficiently and interfacing with lots of systems (its table functions could pull from lots of sources). It was easy to pick up and start playing with, and seems to have found use in a lot of organizations and at substantial scale. And there are a lot of features that look potentially quite powerful (incremental materialized views, aggregating/deduping merge tree variants, etc.). The best way I could process what I saw experimenting with it was that they'd focused on a providing really efficient low-level execution engine, but sometimes you needed to act as the query optimizer.