dcapwell commented on PR #4572:
URL: https://github.com/apache/cassandra/pull/4572#issuecomment-3775425277
I used AI to help write these docs, and had it create test cases for each
example to make sure it actually works. While doing this it found a lot of
nice UX or issues; below is the state that it created
# Accord Future Syntax and Semantic Features
This document tracks CQL transaction syntax and semantic features that are
documented or desired but not yet implemented in the current version of
Cassandra. These features may be added in future releases.
## Arithmetic Expressions with Row References
### Issue: Row Reference Arithmetic in UPDATE SET Clauses
**Status:** Not implemented
**Parser Error:** `no viable alternative at input '-'`
**Description:**
Using arithmetic expressions with row references (LET variable fields) in
UPDATE SET clauses is not currently supported by the CQL parser.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
UPDATE users SET balance = user_data.balance - 50 WHERE id = ?;
COMMIT TRANSACTION
```
**Error Message:**
```
line 1:X no viable alternative at input '-' (...SET balance =
[user_data].balance...)
```
**Current Workaround:**
Use column self-reference instead of row reference arithmetic:
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
IF user_data.balance > 50 THEN
UPDATE users SET balance = balance - 50 WHERE id = ?;
END IF
COMMIT TRANSACTION
```
**Impact:**
- Cannot compute new values based on LET variable fields in UPDATE SET
clauses
- Must use column self-reference (e.g., `balance = balance - 50`) instead
- Row references can still be used in IF conditions for validation
---
## Comparing Two Row References
### Issue: Comparison Between Two LET Variables
**Status:** Not implemented
**Parser Error:** `IllegalArgumentException`
**Description:**
Comparing values from two different LET variables in an IF condition is not
currently supported.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET account = (SELECT balance FROM accounts WHERE id = ?);
LET limit = (SELECT max_balance FROM limits WHERE type = 'standard');
IF account.balance < limit.max_balance THEN
UPDATE accounts SET balance = balance + 10 WHERE id = ?;
END IF
COMMIT TRANSACTION
```
**Current Workaround:**
Read the limit in the application layer and pass it as a parameter:
```cql
-- Application code:
-- double maxBalance = 1000.00; // Retrieved from limits table
BEGIN TRANSACTION
LET account = (SELECT balance FROM accounts WHERE id = ?);
IF account.balance < ? THEN -- Pass maxBalance
UPDATE accounts SET balance = balance + 10 WHERE id = ?;
END IF
COMMIT TRANSACTION
```
**Impact:**
- Cannot perform cross-table validations entirely within the database if
they involve comparing values between tables
- Requires application-side logic for such comparisons
---
## Range Updates and Deletes
### Issue: Updates or Deletes without Full Primary Key
**Status:** Not implemented
**Error:** `InvalidRequestException: Some clustering keys are missing`
**Description:**
Performing `UPDATE` or `DELETE` operations that match multiple rows (range
operations) by specifying only the partition key (or a partial primary key) is
not currently supported in transactions. You must specify the full primary key
for every modification.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
-- Attempting to update all sessions for a user
UPDATE user_sessions
SET status = 'invalidated'
WHERE user_id = ?; -- Missing session_id (clustering key)
COMMIT TRANSACTION
```
**Current Workaround:**
Perform a read query to identify the specific rows to modify, then issue
individual modification statements for each row within the transaction (or pass
the IDs as parameters).
```cql
-- Step 1: Query active sessions (outside transaction or in previous step)
-- SELECT session_id FROM user_sessions WHERE user_id = ?;
-- Step 2: Transaction with specific updates
BEGIN TRANSACTION
UPDATE users SET status = 'deactivated' WHERE id = ?;
-- Update known sessions individually
UPDATE user_sessions SET status = 'invalidated' WHERE user_id = ? AND
session_id = ?;
UPDATE user_sessions SET status = 'invalidated' WHERE user_id = ? AND
session_id = ?;
COMMIT TRANSACTION
```
**Impact:**
- Cannot atomically "delete all" or "update all" children records without
knowing their IDs
- Requires application to track or query IDs before modifying
---
## Arithmetic Expressions with Row References in SELECT
### Issue: Row Reference Arithmetic in SELECT Return Values
**Status:** Not implemented
**Parser Error:** `no viable alternative at input '-'`
**Description:**
Computing arithmetic expressions with row references in SELECT statements
within transactions is not supported.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
UPDATE users SET balance = balance - 50 WHERE id = ?;
SELECT user_data.balance, user_data.balance - 50;
COMMIT TRANSACTION
```
**Error Message:**
```
line 1:X no viable alternative at input '-' (...SELECT user_data.balance,
user_data.balance...)
```
**Current Workaround:**
Return only the row reference fields without arithmetic:
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
UPDATE users SET balance = balance - 50 WHERE id = ?;
SELECT user_data.balance;
COMMIT TRANSACTION
```
**Impact:**
- Cannot return computed values based on LET variables
- Application must perform calculations on returned values
- Row reference fields can be returned individually
---
## SELECT with Row Data References Positioning
### Issue: SELECT with Row References Must Precede Modifications
**Status:** Grammar limitation
**Parser Error:** `no viable alternative at input 'SELECT'`
**Description:**
The CQL grammar requires that `SELECT rowDataReferences` (selecting only row
reference fields without FROM clause) must appear before modification
statements in a transaction. After UPDATE/INSERT/DELETE, only full SELECT
statements with FROM clauses are allowed.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
UPDATE users SET balance = balance - 50 WHERE id = ?;
SELECT user_data.balance; -- Error: SELECT with row references after
UPDATE
COMMIT TRANSACTION
```
**Error Message:**
```
line 1:X no viable alternative at input 'SELECT' (... WHERE id = ?;
[SELECT]...)
```
**Current Workaround:**
Place SELECT statements before modifications, or query outside the
transaction:
```cql
-- Option 1: SELECT before UPDATE
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
SELECT user_data.balance; -- SELECT before UPDATE
UPDATE users SET balance = balance - 50 WHERE id = ?;
COMMIT TRANSACTION
-- Option 2: Query after transaction commits
BEGIN TRANSACTION
UPDATE users SET balance = balance - 50 WHERE id = ?;
COMMIT TRANSACTION
-- Then query outside transaction:
SELECT balance FROM users WHERE id = ?;
```
**Grammar Structure:**
According to Parser.g, the transaction structure is:
```
BEGIN TRANSACTION
[LET statements]
[SELECT selectStatement | SELECT rowDataReferences] <- Must be before
modifications
[IF condition THEN]
[modifications]
[END IF]
COMMIT TRANSACTION
```
The grammar does NOT allow SELECT statements after modification statements,
even if they are full SELECT statements with FROM clauses. The SELECT position
is fixed in the grammar structure.
**Impact:**
- ALL SELECT statements must appear before any UPDATE/INSERT/DELETE
- Cannot SELECT updated values within the same transaction
- To see results after modifications, query outside the transaction
- Row reference-only SELECTs must also precede modifications
---
## Computed LET Assignments
### Issue: LET Assignments with Computed Values
**Status:** Not implemented
**Parser Error:** Varies based on expression type
**Description:**
LET statements can only assign the result of a SELECT query. They cannot
assign computed values, arithmetic expressions, or CASE expressions.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
LET new_balance = user_data.balance - 50; -- Error: computed assignment
LET discount_rate = CASE user_data.tier
WHEN 'gold' THEN 0.10
ELSE 0.0 END; -- Error: CASE expression
UPDATE users SET balance = new_balance WHERE id = ?;
COMMIT TRANSACTION
```
**Current Workaround:**
Perform computations directly in UPDATE statements or in the application
layer:
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance, tier FROM users WHERE id = ?);
-- Compute in the UPDATE statement instead
IF user_data.balance >= 50 THEN
UPDATE users SET balance = balance - 50 WHERE id = ?;
END IF
COMMIT TRANSACTION
```
**Impact:**
- Cannot store intermediate computed values in LET variables
- All computations must happen in UPDATE/INSERT SET clauses
- Complex calculations must be done in application code
- No support for CASE expressions in LET assignments
---
## SELECT After Modification Statements
### Issue: SELECT Must Precede All Modifications
**Status:** Grammar restriction
**Parser Error:** `no viable alternative at input 'SELECT'` (when after
modifications)
**Description:**
The CQL transaction grammar strictly orders statements: LET, then SELECT,
then modifications. SELECT statements (including those returning computation
results) cannot appear after any UPDATE, INSERT, or DELETE.
**Example of Unsupported Syntax:**
```cql
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
UPDATE users SET balance = balance - 50 WHERE id = ?;
-- Error: SELECT after UPDATE not allowed
SELECT user_data.balance, 'Update successful' as message;
COMMIT TRANSACTION
```
**Current Workaround:**
Either move SELECT before modifications or query outside the transaction:
```cql
-- Option 1: SELECT before modifications
BEGIN TRANSACTION
LET user_data = (SELECT balance FROM users WHERE id = ?);
SELECT user_data.balance; -- Before UPDATE
UPDATE users SET balance = balance - 50 WHERE id = ?;
COMMIT TRANSACTION
-- Option 2: Query after transaction
BEGIN TRANSACTION
UPDATE users SET balance = balance - 50 WHERE id = ?;
COMMIT TRANSACTION
-- Then query outside:
SELECT balance FROM users WHERE id = ?;
```
**Impact:**
- Cannot return confirmation messages with results from modifications
- Cannot SELECT updated values within the transaction
- Return values must be queried separately or selected before modifications
- Limits ability to return operation status
---
## IN and OR Operators in IF Conditions
### Issue: IN and OR Not Supported in Transaction Conditions
**Status:** Not implemented
**Parser Error:** `no viable alternative at input 'IN'` or `no viable
alternative at input 'OR'`
**Description:**
The `IN` operator and `OR` logical operator are not currently supported in
transaction `IF` conditions. Only the `AND` logical operator is available for
combining conditions.
**Example of Unsupported Syntax (IN):**
```cql
BEGIN TRANSACTION
LET order = (SELECT status FROM orders WHERE order_id = ?);
IF order.status IN ('PENDING', 'PAID') THEN
UPDATE orders SET status = 'CANCELLED' WHERE order_id = ?;
END IF
COMMIT TRANSACTION
```
**Error Message:**
```
line 1:X no viable alternative at input 'IN' (...IF order.status [IN]...)
```
**Example of Unsupported Syntax (OR):**
```cql
BEGIN TRANSACTION
LET order = (SELECT status FROM orders WHERE order_id = ?);
IF order.status = 'PENDING' OR order.status = 'PAID' THEN
UPDATE orders SET status = 'CANCELLED' WHERE order_id = ?;
END IF
COMMIT TRANSACTION
```
**Error Message:**
```
line 1:X no viable alternative at input 'OR' (...= 'PENDING' [OR]...)
```
**Current Workaround:**
Use numeric status codes and range comparisons to check multiple valid
states:
```cql
-- Schema uses integer status codes: 10=PENDING, 20=PAID, 30=SHIPPED, etc.
BEGIN TRANSACTION
LET order = (SELECT status_code FROM orders WHERE order_id = ?);
-- Use range comparison instead of IN/OR
-- status_code <= 20 means PENDING or PAID
IF order.status_code <= 20 THEN
UPDATE orders SET status_code = 99 WHERE order_id = ?; -- 99=CANCELLED
END IF
COMMIT TRANSACTION
```
**Impact:**
- Cannot use `IN (value1, value2, ...)` syntax to check membership in a set
- Cannot use `OR` to combine multiple conditions
- Only `AND` is supported for combining conditions
- Workaround requires careful design of numeric status codes to enable range
comparisons
**Grammar Reference:**
The `txnConditions` rule in Parser.g only supports `AND`:
```antlr
txnConditions returns [List<ConditionStatement.Raw> conditions]
: txnColumnCondition[conditions] ( K_AND txnColumnCondition[conditions]
)*
;
```
**Code Reference:**
`ConditionStatement.java` contains a TODO for future support:
```java
// TODO: Support for IN, CONTAINS, CONTAINS KEY
```
---
## Summary of Limitations
### Row Reference Arithmetic Limitations
**What Works:**
- Row references in IF conditions: `IF user_data.balance > 100 THEN`
- Row references in comparisons: `IF user_data.balance >= order_total THEN`
- Returning row reference fields: `SELECT user_data.balance`
- Column self-reference arithmetic: `SET balance = balance - 50`
**What Doesn't Work:**
- Arithmetic in UPDATE SET: `SET balance = user_data.balance - 50`
- Arithmetic in SELECT: `SELECT user_data.balance - 50`
- Computed LET values: `LET new_balance = user_data.balance - 50`
**Parser Status:**
The CQL parser (Parser.g) does not currently support arithmetic expressions
involving row references outside of conditional expressions.
---
## Future Enhancements
These features may be added in future Cassandra versions:
1. **Computed LET Assignments:**
```cql
LET new_balance = user_data.balance - 50;
UPDATE users SET balance = new_balance WHERE id = ?;
```
2. **Arithmetic Expressions in SET Clauses:**
```cql
UPDATE users SET balance = user_data.balance - 50 WHERE id = ?;
```
3. **Computed SELECT Returns:**
```cql
SELECT user_data.balance, user_data.balance - 50 AS new_balance;
```
---
## Testing Notes
All examples in the Cassandra documentation
(doc/modules/cassandra/pages/developing/cql/transactions*.adoc) should only
show syntax that currently works. Examples using unsupported syntax have been
removed and documented here for future reference.
Test classes validating documentation examples:
- `BasicTransactionExamplesTest` - Basic transaction patterns
- `BankingExamplesTest` - Banking and financial examples
- `EcommerceExamplesTest` - E-commerce examples
- `UserManagementExamplesTest` - User management examples
- `InOperatorTest` - Validates IN and OR operators are not supported in IF
conditions
- Other example tests in
`test/distributed/org/apache/cassandra/distributed/test/accord/example/`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
