DELETE/UPDATE print misleading error message suggesting ALLOW FILTERING would work

[nyh]

If one tries the request

DELETE FROM tbl WHERE partitionKey = ? AND value = ?

Where value is some non-primary-key column, Cassandra refuses to run it saying:

Non PRIMARY KEY columns found in where clause: value

Unfortunately, Scylla gives a very different error message:

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

This is error message is unfortunately wrong: It suggests that adding ALLOW FILTERING to this query would make it work. But in fact, it doesn't work - the ALLOW FILTERING isn't even an allowed part of the DELETE syntax. So this error message is wrong and misleading.

By the way, #10593 explains why allowing and supporting ALLOW FILTERING in DELETE might be problematic. If the partition is huge, the DELETE will have to scan it entirely before it can return, because DELETE does not support paging.

I suspect that the "ALLOW FILTERING" message is printed by some common expression-parsing code which assumes it is running for a SELECT. But in this case, it isn't, it is running for a DELETE.

Discovered by the translated Cassandra unit tests:

• cassandra_tests/validation/operations/compact_storage_test.py::testDeleteWithNoClusteringColumns
• cassandra_tests/validation/operations/delete_test.py::testDeleteWithNoClusteringColumns
• cassandra_tests/validation/operations/delete_test.py::testDeleteWithSecondaryIndices

Those tests expect Cassandra's error message. We usually don't insist on having exactly the same error message in Scylla as in Cassandra - but we do insist on the error message not being wrong or misleading, which I think in this case it is.

We have exactly the same bug with other write operations, which also don't support ALLOW FILTERING so shouldn't print errors suggesting that it would help. The following translated Cassandra unit tests reproduce this bug for UPDATE:

• cassandra_tests/validation/operations/update_test.py::testUpdate
• cassandra_tests/validation/operations/update_test.py::testUpdateWithTwoClusteringColumns
• cassandra_tests/validation/operations/update_test.py::testUpdateWithMultiplePartitionKeyComponents

[nyh]

Here is an example test showing that the DELETE with a filter on a regular column claims that ALLOW FILTERING is required, but when the user adds this, the statement doesn't work (syntax error):

import pytest
from util import new_test_table, unique_key_int
from cassandra.protocol import InvalidRequest

@pytest.fixture(scope="module")
def table1(cql, test_keyspace):
    with new_test_table(cql, test_keyspace, "p int, c int, v int, PRIMARY KEY (p, c)") as table:
        yield table

def test_delete_partition_with_filter(cql, table1):
    p = unique_key_int()
    cql.execute(f"INSERT INTO {table1} (p, c, v) VALUES ({p}, 1, 10)")
    cql.execute(f"INSERT INTO {table1} (p, c, v) VALUES ({p}, 2, 20)")
    cql.execute(f"INSERT INTO {table1} (p, c, v) VALUES ({p}, 3, 30)")
    with pytest.raises(InvalidRequest, match='ALLOW FILTERING'):
        cql.execute(f"DELETE FROM {table1} WHERE p = {p} AND v > 15")
    # The following request FAILS, with a syntax error :-(
    cql.execute(f"DELETE FROM {table1} WHERE p = {p} AND v > 15 ALLOW FILTERING")