Why CQL is suggesting me to use ALLOW FILTERING on update?

Question

My simple update query is throwing following error.

InvalidRequest: Error from server: code=220 [Invalid query] 
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"

As far as I know ALLOW FILTERING adds indexing and it is usually used to SELECT query. I tried to understand the reason behind this error. But could not find anything. Also, I have just started to learn CQL few concepts are new to me.

Here is the schema of my table:

CREATE TABLE blogplatform.users (
    user_id int PRIMARY KEY,
    active_status int,
    password text,
    user_name text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'}
    AND comment = ''
    AND compaction = {'class': 'SizeTieredCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99.0PERCENTILE';

Query I am trying to execute:

UPDATE users SET active_status = 0 WHERE user_name='Heath';

Output:

InvalidRequest: Error from server: code=220 [Invalid query] 
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"

I even tried to add ALLOW FILTERING in the query:

UPDATE users SET active_status = 0 WHERE user_name='Heath' ALLOW FILTERING;

Output:

SyntaxException: line 1:59  : syntax error...

Could someone please explain me why I am getting this error on simple update. Also, can anyone guide me to resolve this error.

Thank you.

Answer 1

You cannot use a value column in the where clause (without allow filtering but again allow filtering is not a magic word and it just puts a lots of load on the database) - this is applicable for all type of queries and not just Select queries.

Better option for you would be to move the user_name as the PRIMARY KEY (PARTITION KEY).

With that table you can execute a query like.

UPDATE users SET active_status = 0 WHERE user_name='Heath';