That makes sense, they really are just relational databases optimized for certain tasks, with corresponding limitations e.g. they don't support arbitrary joins.
There's nothing intrinsic about not supporting joins, in a columnar store; it's just that you lose a huge amount of the linear scanning performance if you have to do joins for each value. Most columnar stores I've used (primarily Impala, SparkSQL and Clickhouse) all support joins, but they materialize one side of the join as an in-memory hash table, which limits the allowable size of the join, and is a cost multiplier for a distributed query. I believe per the docs that MemSQL can mix and match row-based with columnar more easily, but joins are always going to be really slow compared to the speed you can get from scanning the minimum number of columns to answer your question.
The ClickHouse team is working on merge joins which will supplement the currently supported in-memory hash join mechanism. It's not a panacea as you point out, especially on distributed tables. That said it will help with a number of important use cases such as those that require joining a fact table against very large dimension tables.
Right, that’s sort of what I was getting at with the word “arbitrary”. Compared to a general purpose db the limitations on what can be joined are pretty severe, if only because of performance considerations.
