CQL Comprehensive Compatibility

A systematic compatibility test for every CQL feature that Ferrosa supports. Run schema.cql, data.cql, and queries.cql in order against a Ferrosa cluster to verify full CQL coverage.

Note	Complete the 3-Node Cluster Setup tutorial first, or have a running Ferrosa cluster accessible on `localhost:9042`.

Purpose

This example exists to exercise every CQL statement and data type in a single, reproducible sequence. It is not a tutorial — it is a compatibility verification suite disguised as an example.

The three CQL files are designed to run in order:

schema.cql — DDL: keyspace, tables, types, indexes, alter statements
data.cql — DML writes: inserts, updates, batches, counters, TTL, LWT
queries.cql — DML reads: selects, aggregations, token scans, cleanup

If all three files execute without error, the cluster under test supports the full CQL surface area covered here.

Data Types

CQL defines a rich set of scalar types. The all_types table creates one column for each.

CREATE TABLE IF NOT EXISTS all_types (
    id           int PRIMARY KEY,
    col_text     text,
    col_ascii    ascii,
    col_bigint   bigint,
    col_int      int,
    col_smallint smallint,
    col_tinyint  tinyint,
    col_boolean  boolean,
    col_float    float,
    col_double   double,
    col_decimal  decimal,
    col_blob     blob,
    col_uuid     uuid,
    col_timeuuid timeuuid,
    col_timestamp timestamp,
    col_date     date,
    col_time     time,
    col_inet     inet,
    col_varint   varint
);

The following types are tested:

Type Notes

Type	Notes
`int`, `bigint`, `smallint`, `tinyint`	Signed integers at 32, 64, 16, and 8 bits
`float`, `double`	IEEE 754 single and double precision
`decimal`	Arbitrary-precision decimal
`varint`	Arbitrary-precision integer
`boolean`	`true` / `false`
`text`, `ascii`	UTF-8 and ASCII-only strings
`blob`	Arbitrary bytes (hex literal `0xdeadbeef`)
`uuid`, `timeuuid`	Version 4 random UUID and version 1 time-based UUID
`timestamp`, `date`, `time`	Millisecond timestamp, calendar date, nanosecond time-of-day
`inet`	IPv4 or IPv6 address

int, bigint, smallint, tinyint

Signed integers at 32, 64, 16, and 8 bits

float, double

IEEE 754 single and double precision

decimal

Arbitrary-precision decimal

varint

Arbitrary-precision integer

boolean

true / false

text, ascii

UTF-8 and ASCII-only strings

blob

Arbitrary bytes (hex literal 0xdeadbeef)

uuid, timeuuid

Version 4 random UUID and version 1 time-based UUID

timestamp, date, time

Millisecond timestamp, calendar date, nanosecond time-of-day

inet

IPv4 or IPv6 address

The data file inserts positive boundary values, negative boundary values, zeros, nulls, and empty strings to exercise edge cases.

INSERT INTO all_types (id, col_text, col_ascii, col_bigint, col_int, col_smallint, col_tinyint,
    col_boolean, col_float, col_double, col_decimal, col_blob, col_uuid, col_timestamp,
    col_date, col_time, col_inet)
VALUES (1, 'hello', 'ascii_val', 9223372036854775807, 42, 32767, 127,
    true, 3.14, 2.718281828459045, 99.99, 0xdeadbeef,
    550e8400-e29b-41d4-a716-446655440000, '2024-06-15T10:30:00Z',
    '2024-06-15', '10:30:00', '192.168.1.1');

Collections

CQL supports three collection types, each of which can be nested inside a frozen<> wrapper.

CREATE TABLE IF NOT EXISTS collection_types (
    id         int PRIMARY KEY,
    tags       list<text>,
    scores     set<int>,
    props      map<text, text>,
    nested_list list<frozen<list<int>>>,
    nested_map  map<text, frozen<map<text, int>>>
);

list<text> — ordered, allows duplicates
set<int> — unordered, unique elements
map<text, text> — key-value pairs
list<frozen<list<int>>> — nested frozen list
map<text, frozen<map<text, int>>> — nested frozen map

Collection mutation operations are tested in the data file: append, remove, element access for each type.

-- List append and remove
UPDATE collection_types SET tags = tags + ['delta'] WHERE id = 1;
UPDATE collection_types SET tags = tags - ['alpha'] WHERE id = 1;

-- Set add and remove
UPDATE collection_types SET scores = scores + {40, 50} WHERE id = 1;
UPDATE collection_types SET scores = scores - {10} WHERE id = 1;

-- Map put and remove

Primary Keys

Simple primary key

The all_types table uses a single-column primary key (id int PRIMARY KEY).

Composite partition key

The composite_pk table uses a multi-column partition key with clustering columns and explicit ordering.

CREATE TABLE IF NOT EXISTS composite_pk (
    tenant     text,
    region     text,
    event_id   uuid,
    event_time timestamp,
    data       text,
    PRIMARY KEY ((tenant, region), event_time, event_id)
) WITH CLUSTERING ORDER BY (event_time DESC, event_id ASC);

The partition key (tenant, region) means all events for one tenant in one region are co-located. The clustering columns event_time DESC, event_id ASC sort rows within each partition.

Queries exercise full partition reads, ordering, and LIMIT:

SELECT * FROM composite_pk WHERE tenant = 'acme' AND region = 'us-east';

-- ORDER BY with clustering column
SELECT * FROM composite_pk WHERE tenant = 'acme' AND region = 'us-east' ORDER BY event_time DESC;

-- LIMIT
SELECT * FROM composite_pk WHERE tenant = 'acme' AND region = 'us-east' LIMIT 1;

Clustering order

CLUSTERING ORDER BY controls the physical sort order of rows on disk. DESC on event_time means the most recent events are read first, which avoids reversing results at query time.

Static Columns

A STATIC column is shared across all rows in a partition. It is set once and applies to every clustering row.

CREATE TABLE IF NOT EXISTS static_cols (
    group_id   int,
    item_id    int,
    group_name text STATIC,
    item_name  text,
    PRIMARY KEY (group_id, item_id)
);

When group_name is set for group_id = 1, all items in that group see the same name. The DISTINCT keyword retrieves only the partition key and static columns, without scanning clustering rows.

SELECT * FROM static_cols WHERE group_id = 1;

-- DISTINCT on partition key with static column
SELECT DISTINCT group_id, group_name FROM static_cols;

Write Operations

INSERT

Standard inserts with all scalar types, null values, empty strings, and function calls (uuid()).

INSERT INTO all_types (id, col_text, col_ascii, col_bigint, col_int, col_smallint, col_tinyint,
    col_boolean, col_float, col_double, col_decimal, col_blob, col_uuid, col_timestamp,
    col_date, col_time, col_inet)
VALUES (1, 'hello', 'ascii_val', 9223372036854775807, 42, 32767, 127,
    true, 3.14, 2.718281828459045, 99.99, 0xdeadbeef,
    550e8400-e29b-41d4-a716-446655440000, '2024-06-15T10:30:00Z',
    '2024-06-15', '10:30:00', '192.168.1.1');

UPDATE

Column updates, arithmetic increments, and collection mutations.

-- UPDATE — simple column updates
-- ============================================================
UPDATE all_types SET col_text = 'updated', extra_col = 'added' WHERE id = 1;
UPDATE all_types SET col_int = col_int + 1 WHERE id = 1;

-- ============================================================
-- Collection updates — append, remove, element access
-- ============================================================

-- List append and remove
UPDATE collection_types SET tags = tags + ['delta'] WHERE id = 1;
UPDATE collection_types SET tags = tags - ['alpha'] WHERE id = 1;

-- Set add and remove
UPDATE collection_types SET scores = scores + {40, 50} WHERE id = 1;
UPDATE collection_types SET scores = scores - {10} WHERE id = 1;

-- Map put and remove

TTL and TIMESTAMP

USING TTL sets a per-row expiration in seconds. USING TIMESTAMP sets an explicit write timestamp for last-write-wins conflict resolution.

-- TTL — permanent and expiring rows
-- ============================================================
INSERT INTO ttl_test (id, val) VALUES (1, 'permanent');
INSERT INTO ttl_test (id, val) VALUES (2, 'expires_soon') USING TTL 1;

-- ============================================================
-- USING TIMESTAMP — explicit write timestamps
-- ============================================================

BATCH

Logged batches guarantee atomicity across multiple statements. Unlogged batches skip the batch log for better performance when atomicity is not required.

-- Logged batch
-- ============================================================
BEGIN BATCH
    INSERT INTO batch_test (id, val) VALUES (1, 'batch_1');
    INSERT INTO batch_test (id, val) VALUES (2, 'batch_2');
    INSERT INTO batch_test (id, val) VALUES (3, 'batch_3');
APPLY BATCH;

-- ============================================================
-- Unlogged batch
-- ============================================================
BEGIN UNLOGGED BATCH
    INSERT INTO batch_test (id, val) VALUES (4, 'unlogged_1');

Counter updates

Counter columns only support increment and decrement operations. They cannot be set to an absolute value or mixed with non-counter columns in the same table.

-- Counter updates — increment operations
-- ============================================================
UPDATE counters SET page_views = page_views + 100, unique_visitors = unique_visitors + 42 WHERE id = 'homepage';

DELETE

Row deletes target a specific clustering row. Partition deletes remove all rows in a partition.

DELETE FROM delete_test WHERE pk = 1 AND ck = 2;
SELECT * FROM delete_test WHERE pk = 1;

-- Partition delete — remove all rows in a partition
DELETE FROM delete_test WHERE pk = 2;
SELECT * FROM delete_test WHERE pk = 2;

Lightweight Transactions

LWT provides linearizable consistency for conditional mutations.

INSERT IF NOT EXISTS

The first insert succeeds and returns [applied] = true. The second insert for the same primary key is a no-op and returns [applied] = false.

-- LWT: INSERT IF NOT EXISTS — first succeeds, second is a no-op
-- ============================================================

UPDATE … IF condition

A conditional update that only applies if the current value matches the condition.

UPDATE lwt_test SET data = 'modified' WHERE id = 1 IF version = 1;
SELECT * FROM lwt_test WHERE id = 1;

DELETE … IF EXISTS

A conditional delete that only applies if the row exists.

DELETE FROM lwt_test WHERE id = 1 IF EXISTS;
SELECT * FROM lwt_test WHERE id = 1;

Read Operations

SELECT

Basic reads with *, column subsets, and WHERE clauses on partition keys.

SELECT * FROM all_types WHERE id = 1;
SELECT col_text, col_int, col_bigint FROM all_types WHERE id = 1;

LIMIT and ORDER BY

LIMIT restricts the number of rows returned. ORDER BY on clustering columns controls the sort order (must match or reverse the CLUSTERING ORDER BY).

SELECT * FROM composite_pk WHERE tenant = 'acme' AND region = 'us-east' ORDER BY event_time DESC;

-- LIMIT
SELECT * FROM composite_pk WHERE tenant = 'acme' AND region = 'us-east' LIMIT 1;

COUNT and aggregation

COUNT(*) returns the number of rows matching the query.

SELECT COUNT(*) FROM all_types;
SELECT COUNT(*) FROM batch_test;

DISTINCT

Returns only unique partition key values, optionally with static columns.

SELECT DISTINCT group_id, group_name FROM static_cols;

Token function

token() exposes the partition token, enabling range scans across the token ring.

SELECT * FROM all_types WHERE token(id) > token(3);

WRITETIME function

WRITETIME() returns the microsecond timestamp of the last write to a column.

SELECT col_text, WRITETIME(col_text) FROM all_types WHERE id = 1;

TTL function

TTL() returns the remaining time-to-live in seconds for a column, or null if no TTL is set.

SELECT val, TTL(val) FROM ttl_test WHERE id = 1;

IN clause

IN on the partition key retrieves rows from multiple partitions in a single query.

SELECT * FROM all_types WHERE id IN (1, 2, 3);

ALLOW FILTERING

ALLOW FILTERING permits queries that would otherwise require a full table scan. It signals that the query is intentionally unindexed.

SELECT * FROM all_types WHERE col_boolean = true ALLOW FILTERING;

Secondary Indexes

Secondary indexes allow queries on non-primary-key columns without ALLOW FILTERING.

CREATE INDEX IF NOT EXISTS ON all_types (col_text);
CREATE INDEX IF NOT EXISTS ON all_types (col_int);
CREATE INDEX IF NOT EXISTS ON composite_pk (data);

Queries using secondary indexes:

SELECT * FROM all_types WHERE col_text = 'updated';
SELECT * FROM all_types WHERE col_int = 43;

User-Defined Types

UDTs let you define named structs that can be used as column types. They must be wrapped in frozen<> when used in a table.

CREATE TYPE IF NOT EXISTS address (
    street  text,
    city    text,
    state   text,
    zip     text,
    country text
);

CREATE TYPE IF NOT EXISTS phone_number (
    country_code int,
    number       text,
    phone_type   text
);

CREATE TABLE IF NOT EXISTS udt_test (
    id           int PRIMARY KEY,
    home_address frozen<address>,
    phones       list<frozen<phone_number>>,
    work_address frozen<address>
);

Inserting UDT values uses brace-literal syntax:

-- UDT insert — nested user-defined type values
-- ============================================================
INSERT INTO udt_test (id, home_address, phones, work_address)
VALUES (1,
    {street: '123 Main St', city: 'Springfield', state: 'IL', zip: '62704', country: 'US'},
    [{country_code: 1, number: '555-0100', phone_type: 'mobile'},
     {country_code: 1, number: '555-0200', phone_type: 'work'}],

Tuples

Tuples are anonymous fixed-length structures. They must be frozen<> and are written with parenthesized syntax.

CREATE TABLE IF NOT EXISTS tuple_test (
    id      int PRIMARY KEY,
    coords  frozen<tuple<double, double>>,
    labeled frozen<tuple<text, int, boolean>>
);

-- Tuple insert
-- ============================================================
INSERT INTO tuple_test (id, coords, labeled)
VALUES (1, (37.7749, -122.4194), ('active', 42, true));

Counters

Counter tables have a special structure: the primary key columns plus one or more counter columns. Non-counter columns (other than the primary key) are not allowed.

CREATE TABLE IF NOT EXISTS counters (
    id              text PRIMARY KEY,
    page_views      counter,
    unique_visitors counter
);

Counters are updated with increment/decrement syntax, not absolute assignment:

-- Counter updates — increment operations
-- ============================================================
UPDATE counters SET page_views = page_views + 100, unique_visitors = unique_visitors + 42 WHERE id = 'homepage';

What This Covers

This example exercises the following CQL features:

DDL:

CREATE KEYSPACE with SimpleStrategy
CREATE TABLE with simple and composite primary keys
CLUSTERING ORDER BY (ascending and descending)
STATIC columns
CREATE TYPE (user-defined types)
CREATE INDEX (secondary indexes)
ALTER TABLE ADD
DROP INDEX, DROP TABLE, DROP TYPE, DROP KEYSPACE
IF NOT EXISTS / IF EXISTS guards on DDL

Data types:

All 18 CQL scalar types
list, set, map collections
Nested frozen<> collections
frozen<> UDTs and tuples
counter type

DML writes:

INSERT with literal values, null, empty string, function calls (uuid())
INSERT … USING TTL and USING TIMESTAMP
INSERT … IF NOT EXISTS (LWT)
UPDATE with column assignment, arithmetic, collection mutation
UPDATE … IF condition (LWT)
DELETE (row, partition)
DELETE … IF EXISTS (LWT)
BEGIN BATCH / APPLY BATCH (logged and unlogged)
Counter UPDATE with increment

DML reads:

SELECT * and column subsets
WHERE on partition key, composite partition key, clustering columns
ORDER BY on clustering columns
LIMIT
DISTINCT
COUNT(*)
IN clause on partition key
ALLOW FILTERING
token() function
WRITETIME() and TTL() functions
Secondary index lookups