Restriction on clustering-key only should require ALLOW FILTERING

[nyh]

Our documentation, https://docs.scylladb.com/getting-started/dml/, states that that queries that do not need ALLOW FILTERING "have predictable performance in the sense that they will execute in a time that is proportional to the amount of data returned by the query (which can be controlled through LIMIT)."

Under this definition, the restriction WHERE c=1, where c is a clustering key, should only work with ALLOW FILTERING: Such a query may return just a few or even no matches, but still needs to go over all the partitions - and for each partition it needs to inspect the promoted index and quite likely also needs to read a chunk of rows just to check if any of them has c=1. The time to do that may be huge in a table with a huge number of partitions - and not proportional to the amount of data returned by the query.

Cassandra indeed requires ALLOW FILTERING in this case, but currently Scylla does not. This is a regression from commit 756b14f by @dekimir - Scylla used to behave like Cassandra in this case, but that commit changed both implementation details (good) and the need for "ALLOW FILTERING" (less good).

In cql-pytest, I created three tests for this in test_allow_filtering.py: test_allow_filtering_clustering_key, test_allow_filtering_clustering_key_token_range and test_allow_filtering_clustering_key_token_specific. These three tests pass on Cassandra, but xfail on Scylla.

[dekimir]

Here is how we initially decided to change the behaviour. Our thinking has since evolved; we now want to match Cassandra and require ALLOW FILTERING for both queries quoted in the link.

[dekimir]

And here is where we decided to switch back.

[nyh]

Another example of an inconsistency of the current behavior:
In a table with partition key p, clustering key c, and regular column r, the current situation is that:

1. The restriction WHERE c=1 works without ALLOW FILTERING
2. The restriction WHERE c=1 AND r=2 only works with ALLOW FILTERING

This doesn't make sense. If we come up with some justification why we should allow the first query without ALLOW FILTERING, then the second one should be allowed as well, with the same justification. For reasons I explained above, I think we should refuse both.

[nyh]

This bug was independently reported by @tzach in #8991. I think we should indeed consider it a bug, not a feature.

[dekimir]

GitHub closed this automatically due to overzealous interpretation of a dtest commit message. In fact, it needs to get fixed in the Scylla repo. Reopening.

[nyh]

Already in 4.6, and I don't want to backport this to earlier branches. I don't think it's a great idea that CQL behavior will change in bugfix releases - even if we consider the new behavior more correct.

[nyh]

I may have prematurely decided that we shouldn't backport this fix - @tzach even opened an issue #9536 specifically about 4.5.
@tzach, @eliransin please state here if you want this patch backported, and why. My thinking about was that it shouldn't be backported because we don't want such CQL changes in point releases (e.g., between 4.5.2 and 4.5.3) but maybe my thinking is wrong. Until we decide, I'm re-adding the Backport candidate tag.

@fee-mendes asked in #9536 to backport this to 4.5.

[nyh]

Backporting of this to 4.5 is more difficult than I hoped. It assumes a bunch of other prior changes to the configuration (a3d6f50) and to handling of restrictions. Unless there is a strong need to do this backport, I propose that we don't.

[avikivity]

Fix present on all active branches, not backporting.