What is wrong with CQL? The only gotcha I am aware of is UPDATE/INSERT really just meaning UPSERT.
* LWT (Lightweight Transactions) are nothing like what you expect when you head "transactions". LWT are single row only.
* error handling is asinine. E.g: A quorum insert fails. is the data there or not? Maybe!
* material views. Added, removed, readded, deprecated.
* big limits on secondary indexes which only work inside each single partition
* different format of timestamp for insert and select
* BATCH is atomic, but not isolated.
...In general CQL feels like a hack. In classic SQL you understand early that you can compose queries, subqueries and such. CQL leads to you to believe that this is possible, only for you to find out that it isn't. The docs are full of stuff like "You can do this query, except in this case, unless this other case in which you can again, but not in this other". It's just exception on top of exception. Even the documentation does not list all exceptions (I managed to find a couple in my last work).
How is this point fundamentally different from SQL? In SQL, after you send COMMIT to a remote database and get back a network error, you don't know if your INSERTed data is committed or not. You'll have to reconnect and check for it.
In this case it means that it could not write to 3 nodes, but it is not telling you if it did not write anything or at least once. Writing just once means that the data will be replicated.
So if only 1 of the 3 nodes is up, it will still write there and then return error, even if the coordinator knows that the other nodes are not up.
Queries have a maximum running time (only configurable at the database level if I remember correctly), and if your write exceeds this, it returns error. The replication still goes on and the data is still there.
Network errors are different category, since you might not even know if your command was sent or not. Cassandra kind of sidesteps the network errors though since the client connects to multiple coordinators in the cluster.
Fundamentally every distributed protocol has a moment where it may have to tell the client it has abandoned the operation, but already has in flight messages to remote replicas that would result in a decision to complete the operation.
Before that operation is answered by the replicas, the operation is in an unknown state. It is fundamental. Some slower approaches to reaching decisions may mask this problem more often, but it is there whether you realise it or not.
Cassandra does today inform you on timeout how many replicas have been successfully written to, if it was insufficient to reach the requested level of durability, which many years ago it did not. But this is no guarantee the write is durable, as this will represent a minority of nodes - and they may fail, or be partitioned from the remainder of the cluster. So the write remains in an unknown state until successfully read at QUORUM, much like a timeout during COMMIT for any database.
Single partition only. Poorly named, sure. Feature-wise though pretty comparable to peer offerings. Fortunately general purpose transactions are coming next year.
> material views. Added, removed, readded, deprecated.
No; added, then marked experimental. Never removed or deprecated, though they may be superseded before long.
> error handling is asinine
Fundamental distributed systems problem that is just more apparent with eventual consistency. Failure does not have a certain outcome.