WHERE Restrictions, ALLOW FILTERING, Query Constraints, and Why CQL Is Not SQL

Learning outcomes

AtlasMart developers now ask for “all active electronics products under 50 dollars.” In SQL, that request sounds like a WHERE clause. In Cassandra, the first question is which partition(s) the query can route to and whether the table was modeled for that access path. This lesson makes accepted, rejected and explicitly filtered queries concrete.

1. CQL query rules are a data-placement contract

Cassandra distributes partitions by hashing the complete partition key. The coordinator can route a point or bounded partition query because the request identifies the partition key. Clustering restrictions then describe slices inside that partition. When predicates do not identify an efficient access path, Cassandra often rejects the query rather than silently scanning an unbounded data set.

This is the opposite of the “write normalized tables now, let an optimizer discover access paths later” reflex. Cassandra data modeling is query-first. Multiple denormalized tables are normal when the application has multiple stable access patterns.

2. Build a table whose primary key makes one query cheap

# Disposable Chapter 05 cluster: three nodes, three racks, one DC, 16 vnodes/node# If these objects already exist from Chapters 01–04, reuse them instead of recreating them.docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait for node 1 to accept CQL, then start the two peers.docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 java -version

CREATE KEYSPACE IF NOT EXISTS atlasmart_cqlWITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3}AND durable_writes = true;CONSISTENCY QUORUM;CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_id (    product_id text PRIMARY KEY,    name text,    category text,    price_cents bigint,    status text,    description text,    updated_at timestamp);CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_category (    category text,    bucket tinyint,    product_id text,    name text,    status text,    price_cents bigint,    PRIMARY KEY ((category, bucket), product_id));CREATE TABLE IF NOT EXISTS atlasmart_cql.inventory_by_product (    product_id text,    location_id text,    quantity int,    status text,    promo_note text,    PRIMARY KEY ((product_id), location_id));

INSERT INTO atlasmart_cql.products_by_category (category,bucket,product_id,name,status,price_cents) VALUES ('electronics',0,'p-100','Mouse','active',2500);INSERT INTO atlasmart_cql.products_by_category (category,bucket,product_id,name,status,price_cents) VALUES ('electronics',0,'p-101','Keyboard','active',4800);INSERT INTO atlasmart_cql.products_by_category (category,bucket,product_id,name,status,price_cents) VALUES ('electronics',0,'p-102','Camera','inactive',29900);INSERT INTO atlasmart_cql.products_by_category (category,bucket,product_id,name,status,price_cents) VALUES ('books',0,'p-200','Cassandra Field Guide','active',3900);-- Designed query: exact compound partition key.SELECT product_id,name,status,price_centsFROM atlasmart_cql.products_by_categoryWHERE category='electronics' AND bucket=0;

This table supports “products in one category bucket.” It does not automatically support every predicate on status and price. Bucketing also creates an application obligation: if a category spans several buckets, the application must know which buckets to query and merge.

3. Observe rejected query shapes before reaching for ALLOW FILTERING

-- Missing one component of the compound partition key: expect rejection.SELECT * FROM atlasmart_cql.products_by_categoryWHERE category='electronics';-- Non-key predicate without an index/access path: expect rejection.SELECT * FROM atlasmart_cql.products_by_categoryWHERE status='active';-- Complete partition key plus non-key filtering predicate: may still require ALLOW FILTERING.SELECT * FROM atlasmart_cql.products_by_categoryWHERE category='electronics' AND bucket=0 AND status='active';

Capture the exact Cassandra error text on your version. The error is a design signal, not an inconvenience to suppress. Ask whether the access pattern deserves its own table, whether Storage-Attached Indexing (SAI) is appropriate in the later indexing chapter, or whether this is genuinely a tiny administrative fixture where filtering is bounded and acceptable.

4. ALLOW FILTERING changes permission, not the physical schema

The clause says, in effect, “execute this request even though Cassandra cannot guarantee work proportional to the returned rows.” It does not create an index, repartition data, or teach the coordinator a cheaper route. A LIMIT 10 limits returned rows; it does not guarantee the system examines only ten rows.

SELECT product_id,name,statusFROM atlasmart_cql.products_by_categoryWHERE status='active'ALLOW FILTERING;SELECT product_id,name,status,price_centsFROM atlasmart_cql.products_by_categoryWHERE category='electronics' AND bucket=0  AND status='active'ALLOW FILTERING;

5. Use tracing to see a request path, not to publish benchmarks

cqlsh TRACING ON asks Cassandra to record trace events for subsequent queries. Trace output can reveal coordinator and replica steps and is excellent for mechanism learning. It adds overhead and its individual timings are not a load-test methodology.

TRACING ON;CONSISTENCY QUORUM;SELECT product_id,name,status,price_centsFROM atlasmart_cql.products_by_categoryWHERE category='electronics' AND bucket=0;TRACING OFF;

Record the coordinator address, replica-related events and elapsed times shown on your cluster. Then repeat the filtered query on the tiny fixture and compare the qualitative event pattern. Do not interpret one trace as p95/p99 performance; Chapter 24 develops representative benchmarking and observability.

6. CQL is not SQL with missing features

No implicit joins, no arbitrary cross-partition predicates, and no cost-based optimizer choosing among many relational access paths are not accidental omissions. Cassandra trades those freedoms for predictable routing around known partition keys and denormalized query tables. The design question is therefore “what query must this table serve?” before “what columns describe this entity?”

For AtlasMart, a dedicated active_products_by_category_bucket table could make active-category reads direct at the cost of write fan-out and reconciliation responsibility. SAI may be a better fit when indexed predicates and selectivity justify it. Search/vector chapters cover different workloads again. Do not force one table to be every access path.

Verification checklist

• The complete compound partition-key query succeeds.
• A query missing bucket is rejected on the unindexed table.
• The status-only query is rejected unless filtering is explicitly allowed.
• ALLOW FILTERING is tested only on the bounded fixture and its risk is documented.
• You captured one trace and can distinguish trace evidence from benchmark evidence.

Summary and next bridge

You can now treat CQL restrictions as placement/routing feedback rather than syntactic hostility. Lesson 4 separates interactive cqlsh workflows from application execution and proves how prepared/bound statements carry reusable query structure and routing metadata.

# Chapter-only reset or deliberate preservation# Keep atlasmart_cql if you are continuing directly to the next lesson.# Full reset (deletes only the dedicated course containers/volumes/network)docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-datadocker network rm atlasmart-cassandra

Authoritative references

• Apache Cassandra downloads — Current Cassandra and ASF Java-driver release baseline.
• CQL data definition — Official keyspace/table DDL and schema options.
• CQL data manipulation — Official INSERT/UPDATE/DELETE/SELECT, timestamps, TTL, WRITETIME and filtering semantics.
• cqlsh documentation — Official shell commands, tracing, paging, SOURCE/CAPTURE/COPY behavior and compatibility boundary.
• Native protocol — PREPARE/EXECUTE protocol semantics and version framing.
• Apache Cassandra Java Driver prepared statements — Prepared/bound statement behavior, caching and routing metadata.