That query returns zero rows because a single ISO status code column cannot hold two values at once. The WHERE clause demands iso_status_cde equal 'rjct' and also equal 'expi'; no row satisfies both. The ALLOW FILTERING hint does not change this, since it relaxes partition-key restrictions, not contradictory predicates. This is a logic error, not a data error.
What the query is structurally asking for
Read the predicate literally. The engine evaluates iso_status_cde = 'rjct' AND iso_status_cde = 'expi' for every candidate row. For any row, the column holds exactly one value. If it is 'rjct', the second comparison fails. If it is 'expi', the first fails. If it is anything else, both fail. The intersection is the empty set by construction.
This is the same class of mistake as asking for records where a payment is both settled and reversed. The two status codes are mutually exclusive terminal states in most ISO 20022-derived payment schemas.
The allow filtering token deserves separate treatment. In Cassandra and Cassandra-compatible stores, ALLOW FILTERING permits scans that the query planner would otherwise reject for efficiency reasons. It does not weaken boolean logic. A contradictory predicate stays contradictory whether the engine scans one partition or the whole cluster.
Plain language: the database is being asked for a coin that shows heads and tails on the same face, and no amount of scanning permission mints that coin.
How the payment-status mechanism actually works
A credit transfer instruction moves through a state machine. It is accepted, then validated, then processed, then either completed or terminated. The iso_status_cde column records the current state, not the history of states.
Rejection and expiry are two distinct terminal transitions. Rejection typically follows validation failure, insufficient funds, sanctions screening hits, or beneficiary-account problems. Expiry follows a timeout: the instruction sat unprocessed past a deadline the scheme defines. Both end the instruction's life, but they arrive there by different paths.
The debtor_prxy_id value '0954164556984118go4foc2' is a proxy identifier for the debtor, likely a scheme-issued alias resolving to an account. It is long and alphanumeric, which suggests an opaque token rather than a human-readable handle. The record was almost certainly written with a single terminal status. This schema stores the current state, not a status log.
The scientific analogy: you are querying an enzyme for its current conformation and asking for two mutually exclusive conformations simultaneously. The protein is in one state. The query, not the data, is malformed.
Why ALLOW FILTERING misleads here
ALLOW FILTERING exists because Cassandra's primary-key model makes some otherwise reasonable queries expensive. Without the hint, the planner refuses predicates on non-key columns. With the hint, it scans and filters row by row.
Three things the hint does not do deserve explicit listing, because they are common misconceptions:
It does not permit OR-like semantics on a single equality column. Two equality predicates on one column remain an AND. It does not bypass the fact that the column is scalar. If the schema stored a set or list of statuses, membership queries would work; a scalar column cannot. It does not fix a missing partition key. If debtor_prxy_id is not the partition key, the scan may touch every node in the cluster.
The counterintuitive professional caveat: ALLOW FILTERING can return correct results on a badly modeled query while quietly costing orders of magnitude more in read latency and coordinator load. A query that returns zero rows after a full-cluster scan looks identical to a query that returns zero rows after a single-partition lookup. The result set tells you nothing about the cost.
Reconstructing the intended query
The likely intent was "find rejected or expired instructions for this debtor." That requires OR logic, expressed in CQL in one of two ways.
The comparison table below maps the common approaches and their tradeoffs. It assumes a Cassandra-style store; adapt column names to your schema, since this article cannot inspect your actual keyspace.
| Approach | Predicate shape | Works without ALLOW FILTERING? | Cost profile |
|---|---|---|---|
| IN clause | iso_status_cde IN ('rjct','expi') | Only if status is a clustering column | Single partition if keyed well |
| OR clause | (iso_status_cde='rjct' OR iso_status_cde='expi') | Rarely; often needs filtering | Scan unless indexed |
| Two queries | Run each status separately, union client-side | Depends on key design | Two bounded reads |
| Materialized view | Pre-project by status | Yes | Extra write amplification |
| Secondary index | Index on status column | Yes | Index read plus lookup |
| App-side filter | Fetch by debtor, filter in code | Yes | One partition read, cheap |
The two-query approach is usually the safest fix. Run ... WHERE debtor_prxy_id='...' AND iso_status_cde='rjct' and the same with 'expi', then merge results. Each query has a single, satisfiable predicate. If debtor_prxy_id is the partition key, each read touches one partition.
The IN clause is cleaner when the schema supports it. Confirm whether iso_status_cde is a clustering column; if it is, IN fans out across the specified clustering values within the partition, which is efficient.
What the status timeline looks like
Terminal statuses are endpoints, not waypoints. The table below sketches a generic credit-transfer lifecycle. Exact codes and thresholds vary by scheme, so treat the day counts as illustrative, not as a published standard.
| Stage | Typical status code family | What it means | Exit paths |
|---|---|---|---|
| Received | Accepted, received | Instruction acknowledged by the scheme | Proceeds to validation |
| Validated | Pending, in process | Syntax and business rules pass | Proceeds or rejects |
| Screening | Blocked, held | Sanctions or fraud review | Releases or rejects |
| Settled | Completed, settled | Funds moved | Terminal |
| Rejected | 'rjct' family | Validation or business failure | Terminal |
| Expired | 'expi' family | Deadline passed unprocessed | Terminal |
| Returned | Returned, reversed | Post-settlement clawback | Terminal |
A record cannot sit at both 'rjct' and 'expi'. The schema would need a status-history table, keyed by instruction ID and timestamp, to answer "show me everything that happened to this instruction." That is a different query against a different table, and it is the one most operational investigations actually need.
Practical actions and explicit thresholds
Work through these steps in order. Each has a concrete check.
Test the predicate in isolation. Run the query with only iso_status_cde='rjct' and confirm whether any rows exist for that debtor. Repeat with 'expi'. This distinguishes "no rejected records" from "no records at all." Confirm the partition key. Inspect the schema definition. If debtor_prxy_id is not the partition key, the ALLOW FILTERING scan is touching multiple nodes and your latency budget matters. Replace the AND pair with IN or two queries. This is the fix in nearly all cases. Check whether status history exists elsewhere. If the store keeps an audit or event table, query that instead of the current-state table for lifecycle questions. Measure read cost before and after. Compare coordinator-side metrics for the filtered scan against the two single-predicate reads.
A reconciliation note on thresholds: this article deliberately states no latency or row-count thresholds as universal. Acceptable scan cost depends on cluster size, replication factor, and SLA. What is universal is the logic: a single-valued equality column cannot equal two distinct strings in one predicate. Every other threshold in this piece is either structural (column cardinality) or illustrative (lifecycle days), and the illustrative numbers should be replaced with your scheme's documented values.
Red-flag warnings to carry into any production change:
Do not add ALLOW FILTERING to a query on a large table to "make it work." It converts a planner rejection into a slow full scan. Do not assume zero rows means zero matching instructions until you have tested each predicate alone. Do not build status-history reporting on a current-state table; it will silently undercount. Do not treat an IN clause as free. It multiplies the clustering values read within a partition and can still exceed a timeout on wide partitions.
Limitations and what this analysis cannot know
This explainer reasons from CQL semantics and generic ISO payment-messaging behavior. It does not inspect your keyspace, your table definition, or your cluster topology. Column names, partition keys, and available indexes must be verified against your own schema before any query change ships. The row counts and latency figures in any real deployment will differ from anything stated here, and no number in this article should be treated as a benchmark.
The 'rjct' and 'expi' codes are assumed to be terminal, mutually exclusive status values based on common ISO 20022 usage. Some schemes define overlapping or hierarchical status families, and a store might encode multiple statuses per row in a collection column. If your schema does store a status set rather than a scalar, the original query has a different failure mode (set membership syntax), and the fix differs. Verify before rewriting.
Finally, the operational meaning of a rejection versus an expiry differs by scheme, and the remediation path differs too. A rejection may be resubmittable after correction; an expiry usually requires a fresh instruction. Getting that distinction right matters more than the query rewrite, and it comes from your scheme rulebook, not from the database.