# Cassandra Query Language (CQL) v3.4.3 This document is a Markdown specification of CQL v3.4.3, derived from cql_singlefile.adoc. ## Syntax and lexical rules ### Conventions - Grammar rules are shown in BNF-like notation. - Non-terminals are written as ``. - `?` means optional, `*` means zero or more, `+` means one or more. - Parentheses group symbols. - `[abc]` denotes any one character from the set. - The grammar is descriptive and omits some minor details. ### Identifiers and keywords - Unquoted identifiers match `[A-Za-z][A-Za-z0-9_]*` and are case-insensitive. - Quoted identifiers are enclosed in double quotes, are never keywords, and are case-sensitive. - A double quote inside a quoted identifier is escaped by doubling it: `""`. - A lowercase quoted identifier that matches the unquoted form is equivalent to the unquoted identifier. - Reserved keywords cannot be used as identifiers unless quoted. - Avoid quoted identifiers that look like `"[applied]"` or `"f(x)"` to prevent conflicts. ### Constants - String: `'...'` with `''` to escape single quotes. - String (alternate): `$$...$$` to allow single quotes inside. - Integer: `-?[0-9]+`. - Float: `-?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?`, plus `NaN` and `Infinity`. - Boolean: `true` or `false` (case-insensitive). - UUID: `hex{8}-hex{4}-hex{4}-hex{4}-hex{12}`. - Blob: `0[xX](hex)+`. ### Terms and literals A term can be a constant, collection/vector/tuple/UDT literal, function call, arithmetic expression, type hint, or a bind marker. ```bnf ::= unquoted or quoted identifier (excluding reserved keywords) ::= ( ".")? ::= a string constant ::= an integer constant ::= a float constant ::= | ::= a uuid constant ::= a boolean constant ::= a blob constant ::= | | | | ::= "?" | ":" ::= | | ::= "{" ( ":" ( "," ":" )* )? "}" ::= "{" ( ( "," )* )? "}" ::= "[" ( ( "," )* )? "]" ::= "[" ( ( "," )* )? "]" ::= ::= | | | | | "(" ( ( "," )* )? ")" | | | ::= ( "AND" )* ::= "=" ( | | ) ``` Notes: - Not all productions are valid in practice. In particular, `` and nested `` are not allowed inside collection literals. - Bind markers can be anonymous (`?`) or named (`:name`). - Tuple and UDT literal syntax follows their type definitions and is not expanded here. `` is used by keyspace and table statements; properties can be simple values or maps of sub-options. ### Comments - Single-line comments start with `--` or `//`. - Multi-line comments are enclosed with `/*` and `*/`. ### Statements CQL statements fall into three categories: data definition (DDL), data manipulation (DML), and queries. Statements end with `;`, but the semicolon is optional for a single statement. ### Prepared statements Prepared statements allow a query to be parsed once and executed multiple times with different values. - Bind markers can be used anywhere a term is accepted. - Bind markers can also be used for `LIMIT`, `TIMESTAMP`, and `TTL` clauses. Anonymous markers use names `[limit]`, `[timestamp]`, and `[ttl]`. #### Prepared statement caching Prepared statements are cached using a Caffeine-managed cache, configured by `prepared_statements_cache_size`. The cache is persisted in `system.prepared_statements` so it can be preloaded on startup. Use bind markers for all non-constant values to avoid cache churn and eviction. ## Data definition (DDL) ### Keyspaces #### CREATE KEYSPACE Syntax: ```sql CREATE KEYSPACE [IF NOT EXISTS] WITH [AND ]* ``` Keyspace names are alphanumeric, up to 32 characters, and case-insensitive unless quoted. Supported properties: | name | kind | required | default | description | | --- | --- | --- | --- | --- | | replication | map | yes | - | Replication strategy and options. | | durable_writes | simple | no | true | Whether to use the commit log for updates. | `replication` must include sub-option `'class'`: - `SimpleStrategy`: requires `'replication_factor'`. - `NetworkTopologyStrategy`: use per-datacenter replication factor options. #### USE Syntax: ```sql USE ``` Sets the current keyspace for the session. #### ALTER KEYSPACE Syntax: ```sql ALTER KEYSPACE WITH [AND ]* ``` Uses the same properties as `CREATE KEYSPACE`. #### DROP KEYSPACE Syntax: ```sql DROP KEYSPACE [IF EXISTS] ``` Drops a keyspace and all its tables and data. #### GRANT ON KEYSPACE Syntax: ```sql GRANT ON KEYSPACE TO GRANT ALL PERMISSIONS ON KEYSPACE TO ``` Supported keyspace permissions are `SELECT`, `MODIFY`, `CREATE`, `ALTER`, `DROP`, and `ALL [PERMISSIONS]`. The proxy maps keyspace grants to TiDB database grants because a CQL keyspace maps to a TiDB database: | CQL permission | TiDB privileges | | --- | --- | | `SELECT` | `SELECT` | | `MODIFY` | `INSERT`, `UPDATE`, `DELETE` | | `CREATE` | `CREATE` | | `ALTER` | `ALTER`, `INDEX` | | `DROP` | `DROP` | | `ALL [PERMISSIONS]` | `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, `INDEX` | Only `ON KEYSPACE` resources are supported. `AUTHORIZE` is not supported. ### Tables #### CREATE TABLE Syntax: ```sql CREATE TABLE [IF NOT EXISTS] ( [, ...], PRIMARY KEY ( [, ...] ) ) [WITH [AND ]*] ``` Notes: - `CREATE COLUMNFAMILY` is an alias for `CREATE TABLE`. - `PRIMARY KEY` is required and may be specified inline for a single-column key. - Table names may be keyspace-qualified; otherwise the current keyspace is used. - The partition key is the first component of the primary key. - A composite partition key uses an extra set of parentheses. - Clustering columns define on-disk ordering within a partition. - Operations within the same partition key are atomic and isolated. ##### Partition key and clustering columns - The first primary-key column is the partition key. - Remaining primary-key columns are clustering columns. - Clustering order determines how rows are stored and what `ORDER BY` is allowed. ##### STATIC columns A `STATIC` column is shared by all rows in the same partition. Restrictions: - Not allowed with `COMPACT STORAGE`. - Not allowed when a table has no clustering columns. - Only non-primary key columns can be static. ##### Table options `COMPACT STORAGE` is a legacy option: - No collections or static columns. - If clustering columns exist, exactly one non-primary key column is allowed. - Columns cannot be added or removed after creation. `CLUSTERING ORDER` sets on-disk ordering for clustering columns and affects `ORDER BY`. Other supported options: | option | kind | default | description | | --- | --- | --- | --- | | comment | simple | none | Human-readable comment. | | gc_grace_seconds | simple | 864000 | Time to wait before GC of tombstones. | | bloom_filter_fp_chance | simple | 0.00075 | Target bloom filter false positive rate. | | default_time_to_live | simple | 0 | Default TTL in seconds for new values. | | compaction | map | see below | Compaction options. | | compression | map | see below | Compression options. | | caching | map | see below | Caching options. | | crc_check_chance | simple | 1.0 | Probability of checksum verification on reads (compressed files only). | Note: `crc_check_chance` applies only to compressed files; if compression is disabled, checksums are not verified. ###### Compaction options `compaction` requires sub-option `'class'`. Default classes: `SizeTieredCompactionStrategy`, `LeveledCompactionStrategy`, `TimeWindowCompactionStrategy`. | option | strategy | default | description | | --- | --- | --- | --- | | enabled | all | true | Enable or disable compaction. | | tombstone_threshold | all | 0.2 | Tombstone ratio to trigger single-sstable compaction. | | tombstone_compaction_interval | all | 1 day | Minimum age before tombstone compaction. | | unchecked_tombstone_compaction | all | false | Run tombstone compactions without success checks. | | min_sstable_size | SizeTiered | 50MB | Size threshold for SSTable bucket grouping. | | min_threshold | SizeTiered | 4 | Minimum SSTables for minor compaction. | | max_threshold | SizeTiered | 32 | Maximum SSTables per minor compaction. | | bucket_low | SizeTiered | 0.5 | Lower bound for size bucket. | | bucket_high | SizeTiered | 1.5 | Upper bound for size bucket. | | sstable_size_in_mb | Leveled | 5MB | Target SSTable size. | | timestamp_resolution | TimeWindow | MICROSECONDS | Timestamp resolution (Java TimeUnit). | | compaction_window_unit | TimeWindow | DAYS | Window unit (DAYS, HOURS, MINUTES). | | compaction_window_size | TimeWindow | 1 | Window size in units. | | unsafe_aggressive_sstable_expiration | TimeWindow | false | Drop expired SSTables without shadow checks (requires JVM flag). | ###### Compression options | option | default | description | | --- | --- | --- | | class | LZ4Compressor | Compression class (LZ4Compressor, SnappyCompressor, DeflateCompressor, or custom). | | enabled | true | Disable compression by setting to false. | | chunk_length_in_kb | 64KB | Block size for SSTable compression. | ###### Caching options | option | default | description | | --- | --- | --- | | keys | ALL | Cache keys: ALL or NONE. | | rows_per_partition | NONE | Row cache size: NONE, ALL, or integer count. | ##### Other considerations - Missing columns are not stored on disk. - Adding columns is constant time. #### CREATE TABLE LIKE Syntax: ```sql CREATE TABLE [IF NOT EXISTS] LIKE [WITH [AND ]*] [AND INDEXES] ``` Creates a new table based on the schema of an existing table. Notes: - `IF NOT EXISTS` makes the statement a no-op when the table already exists. - Options can be overridden with `WITH`. - `INDEXES` can copy SAI and legacy secondary indexes (no triggers or materialized views). #### ALTER TABLE Syntax: ```sql ALTER TABLE [IF EXISTS] ADD [IF NOT EXISTS] | DROP [IF EXISTS] | RENAME [IF EXISTS] TO [AND TO ]* | WITH [AND ]* ``` Notes: - `ALTER COLUMNFAMILY` is an alias for `ALTER TABLE`. - `ADD` and `DROP` are not supported with `COMPACT STORAGE`. - `RENAME` is only allowed for primary key columns. - `WITH` does not support `COMPACT STORAGE`. Setting compaction or compression overwrites existing sub-options. ##### CQL type compatibility for ALTER TABLE A column with a type in the left column may be altered to the type in the right column. | source type(s) | target type | | --- | --- | | timestamp | bigint | | ascii, bigint, boolean, date, decimal, double, float, inet, int, smallint, text, time, timestamp, timeuuid, tinyint, uuid, varchar, varint | blob | | int | date | | ascii, varchar | text | | bigint | time | | bigint | timestamp | | timeuuid | uuid | | ascii, text | varchar | | bigint, int, timestamp | varint | Clustering columns have stricter conversions: | source type(s) | target type | | --- | --- | | ascii, text, varchar | blob | | ascii, varchar | text | | ascii, text | varchar | #### DROP TABLE Syntax: ```sql DROP TABLE [IF EXISTS] ``` Drops a table and all its data. `DROP COLUMNFAMILY` is an alias. #### TRUNCATE Syntax: ```sql TRUNCATE ``` Removes all data from a table. ### Secondary indexes #### CREATE INDEX Creates a secondary index on a column. Notes: - Index creation is asynchronous for existing data. - Optional index name can be provided before `ON`. - `IF NOT EXISTS` makes the statement a no-op when the index already exists. - `USING` specifies index type: `legacy_local_table` (default), `sai`, or a custom class. - Use `keys()` to index map keys; otherwise map values are indexed. #### DROP INDEX Syntax: ```sql DROP INDEX [IF EXISTS] [.] ``` ### Materialized views #### CREATE MATERIALIZED VIEW Creates a view based on a `SELECT` from a base table. Notes: - Materialized views cannot be updated directly. - `IF NOT EXISTS` makes the statement a no-op when the view already exists. - Updates to the base table update the view. - The `WHERE` clause must prevent `NULL` values in the view primary key (use `IS NOT NULL`). - Only base primary-key columns may be restricted with operators other than `IS NOT NULL`. #### ALTER MATERIALIZED VIEW Updates view options using the same options as `CREATE TABLE`. #### DROP MATERIALIZED VIEW Syntax: ```sql DROP MATERIALIZED VIEW [IF EXISTS] ``` ### User-defined types #### CREATE TYPE Syntax: ```sql CREATE TYPE [IF NOT EXISTS] ( [, ...] ) ``` Notes: - Type names are identifiers. - Existing CQL types and reserved type names cannot be used. - Types belong to a keyspace (current if not specified). #### ALTER TYPE Syntax: ```sql ALTER TYPE [IF EXISTS] ADD [IF NOT EXISTS] | RENAME [IF EXISTS] TO [AND TO ]* ``` ALTER TYPE can add fields, rename fields, and (where supported) change field types. #### DROP TYPE Syntax: ```sql DROP TYPE [IF EXISTS] ``` A type cannot be dropped while referenced by another type or table. ### Triggers #### CREATE TRIGGER Creates a trigger on a table using external JVM code stored in `lib/triggers`. Triggers fire before DML statements. #### DROP TRIGGER Removes a trigger created with `CREATE TRIGGER`. ### Functions and aggregates (DDL) #### CREATE FUNCTION Creates or replaces a user-defined function. Syntax (simplified): ```sql CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] ( [, ...] ) RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT RETURNS LANGUAGE AS $$ ... $$ ``` Notes: - Signatures are defined by fully qualified name and argument types. - `OR REPLACE` and `IF NOT EXISTS` cannot be used together. - `RETURNS NULL ON NULL INPUT` returns null if any argument is null; `CALLED ON NULL INPUT` always executes. - Functions belong to a keyspace; system keyspaces are not allowed. #### DROP FUNCTION Syntax: ```sql DROP FUNCTION [IF EXISTS] ( [, ...] ) ``` Argument types are required to disambiguate overloaded functions. #### CREATE AGGREGATE Creates or replaces a user-defined aggregate. Syntax (simplified): ```sql CREATE [OR REPLACE] AGGREGATE [IF NOT EXISTS] ( [, ...] ) SFUNC STYPE [FINALFUNC ] [INITCOND ] ``` Notes: - `OR REPLACE` and `IF NOT EXISTS` cannot be used together. - `STYPE` is required. - `INITCOND` defaults to `null`. - `SFUNC` and `FINALFUNC` are UDFs; signatures must match. - `SFUNC` first argument type must match `STYPE`; remaining arguments match aggregate arguments. - If `FINALFUNC` is omitted, the aggregate return type is `STYPE`. #### DROP AGGREGATE Syntax: ```sql DROP AGGREGATE [IF EXISTS] ( [, ...] ) ``` ### User DDL This proxy supports a TiDB-native subset of Cassandra user DDL: ```sql CREATE USER [IF NOT EXISTS] WITH PASSWORD '' [NOSUPERUSER] ALTER USER [IF EXISTS] WITH PASSWORD '' DROP USER [IF EXISTS] LIST USERS ``` These statements manage TiDB accounts using host `%`. Cassandra `SUPERUSER` semantics are not supported. See [Cassandra User DDL on TiDB](user-ddl.md) for the compatibility contract. ## Data manipulation (DML) ### INSERT Syntax (simplified): ```sql INSERT INTO ( [, ...] ) VALUES ( [, ...] ) [USING TIMESTAMP AND TTL ] [IF NOT EXISTS] ``` Notes: - Primary key columns must be provided. - Rows are inserted or updated; prior existence is not checked. - `IF NOT EXISTS` uses Paxos and has a performance cost. - All updates in an `INSERT` are applied atomically and in isolation within a partition. - `INSERT` does not support counters. - `JSON` form is supported (see JSON section). - When using `JSON`, the column list is optional and omitted columns default to `NULL`. ### UPDATE Syntax (simplified): ```sql UPDATE [USING TIMESTAMP AND TTL ] SET [, ...] WHERE [IF ] ``` Notes: - The `WHERE` clause must include all primary key columns. - `IF` conditions use Paxos. - Rows are created if they do not already exist. - Updates within the same partition key are atomic and isolated. - Counter updates use `c = c + ` or `c = c - `. - Collection updates use `col = col + ` and `col = col - `. - Map and list updates support `col[] = `. - UDT field updates use `col.field = ` for non-frozen UDTs. ### TIMESTAMP and TTL options - `TIMESTAMP` sets the write timestamp in microseconds; if omitted, the coordinator uses the current time at statement start. - `TTL` sets a time-to-live in seconds for inserted or updated values. - TTL applies to the values written by the statement, not the column definition. - Updating a value resets its TTL; `TTL 0` removes the TTL (including a table default TTL). ### DELETE Syntax (simplified): ```sql DELETE [ [, ...]] FROM [USING TIMESTAMP ] WHERE [IF ] ``` Notes: - Omitting column names deletes entire rows. - Collection element deletion uses `col[]`. - UDT field deletion uses `col.field` for non-frozen UDTs. - `IN` and range relations are supported in the `WHERE` clause. - `IF` conditions use Paxos. - Deletions within the same partition key are atomic and isolated. ### BATCH Syntax (simplified): ```sql BEGIN [UNLOGGED | COUNTER] BATCH ; ... APPLY BATCH; ``` Notes: - Batches may include only `INSERT`, `UPDATE`, and `DELETE`. - Batches are not full SQL-style transactions. - Updates for the same partition key are applied in isolation. - `LOGGED` (default) ensures eventual completion of all mutations. - `UNLOGGED` skips the batch log and can leave partial application. - When `executor.unlogged_batch_parallel=true`, `UNLOGGED BATCH` executes in best-effort parallel mode and is explicitly non-atomic. - `COUNTER` is required for counter batches. - A batch `TIMESTAMP` applies to all statements and cannot be used inside the batch. - If per-operation timestamps are omitted, all operations share the same timestamp and may be applied out of order. ## Queries ### SELECT Syntax (simplified): ```sql SELECT [JSON] FROM [WHERE ] [GROUP BY ] [ORDER BY ] [LIMIT ] [PER PARTITION LIMIT ] [ALLOW FILTERING] ``` #### Select clause - `` is `*` or a list of selectors. - Column order for `SELECT *` is unspecified and may change between versions. - A selector is a column, a function call, `WRITETIME`, `MAXWRITETIME`, or `TTL`. - `WRITETIME` and `TTL` are not allowed on multi-cell columns (non-frozen collections or UDTs). - Map/set access is supported using `col[]` or slices `col[..]`. - Aliases are supported with `AS` but cannot be used in `WHERE` or `ORDER BY`. - `COUNT(*)` and `COUNT(1)` return the number of matching rows. #### WHERE clause - Relations are allowed on primary key columns and on indexed columns. - Non-equality relations on partition keys are not allowed (except via `TOKEN`). - For clustering columns, relations must select a contiguous range of rows. - `TOKEN()` can be used for token-range queries. - `IN` is allowed only on the last column of the partition key and the last column of the full primary key. - Tuple notation is supported for clustering columns: `(c1, c2) > (v1, v2)`. - `CONTAINS` applies to collection values; `CONTAINS KEY` applies to map keys. #### ORDER BY - Only clustering columns can be ordered. - Allowed orderings are the table clustering order and its reverse. #### GROUP BY - Grouping is limited to partition key and clustering column prefixes. - Only primary key columns in order may appear. - If a primary key column is restricted by equality, it need not appear. - Non-aggregate columns return the first value encountered per group. #### LIMIT and PER PARTITION LIMIT - `LIMIT` caps total returned rows. - `PER PARTITION LIMIT` caps rows per partition. #### ALLOW FILTERING Allows queries that require server-side filtering with potentially unpredictable performance. ## Data types ### Type grammar (simplified) ```bnf ::= | | | | ::= ascii | bigint | blob | boolean | counter | date | decimal | double | float | inet | int | smallint | text | time | timestamp | timeuuid | tinyint | uuid | varchar | varint ::= list "<" ">" | set "<" ">" | map "<" "," ">" ::= tuple "<" ( "," )* ">" ``` Custom types are specified using the fully qualified Java class name. Native type keywords are case-insensitive and not reserved. ### Native types | type | constants supported | description | | --- | --- | --- | | ascii | strings | ASCII character string. | | bigint | integers | 64-bit signed long. | | blob | blobs | Arbitrary bytes (no validation). | | boolean | booleans | true or false. | | counter | integers | Counter column (64-bit signed). | | date | integers, strings | Date without time. | | decimal | integers, floats | Variable-precision decimal. | | double | integers | 64-bit IEEE-754 floating point. | | float | integers, floats | 32-bit IEEE-754 floating point. | | inet | strings | IPv4 or IPv6 address. | | int | integers | 32-bit signed int. | | smallint | integers | 16-bit signed int. | | text | strings | UTF8 encoded string. | | time | integers, strings | Time with nanosecond precision. | | timestamp | integers, strings | Timestamp with millisecond precision. | | timeuuid | uuids | Type 1 UUID. | | tinyint | integers | 8-bit signed int. | | uuid | uuids | Type 1 or type 4 UUID. | | varchar | strings | UTF8 encoded string. | | varint | integers | Arbitrary-precision integer. | ### Working with timestamps - Stored as milliseconds since the Unix epoch (1970-01-01 00:00:00 GMT). - Accepts integer values or ISO 8601 strings. - Accepted string formats include: - `2011-02-03 04:05+0000` - `2011-02-03 04:05:00+0000` - `2011-02-03 04:05:00.000+0000` - `2011-02-03T04:05+0000` - `2011-02-03T04:05:00+0000` - `2011-02-03T04:05:00.000+0000` - `2011-02-03 04:05` - `2011-02-03 04:05:00` - `2011-02-03 04:05:00.000` - `2011-02-03T04:05` - `2011-02-03T04:05:00` - `2011-02-03T04:05:00.000` - `2011-02-03` - `2011-02-03+0000` - Time zone may be omitted (then the coordinator time zone is used), but specifying it is recommended. - Date-only strings are accepted and default to 00:00:00. ### Working with dates - Stored as an unsigned 32-bit integer representing days since an epoch centered at 2^31. - Accepts integer values or strings in `YYYY-MM-DD`. ### Working with time - Stored as nanoseconds since midnight. - Accepts integer values or strings in `HH:MM:SS[.fffffffff]`. ### Counters - Can only be incremented or decremented; values cannot be set. - Cannot be part of a primary key. - If a table has any counter columns, all non-primary key columns must be counters. ### Collections Collections are intended for small, bounded datasets. General limitations: - Collections are read in full (no internal paging). - Reading more than 65535 elements is not supported. - Some list operations require read-before-write; prefer sets when possible. #### Map - Syntax: `map`. - Keys are unique and maps are ordered by key. - `INSERT` replaces the entire map. - Update a key: `SET m[key] = value` or `SET m = m + {k:v}`. - Delete a key: `DELETE m[key] ...`. - TTL applies only to newly updated values. #### Set - Syntax: `set`. - Values are unique and ordered. - `INSERT` replaces the entire set. - Add/remove values: `SET s = s + {...}` or `SET s = s - {...}`. - TTL applies only to newly updated values. #### List - Syntax: `list`. - Values are ordered by position and may be non-unique. - `INSERT` replaces the entire list. - Append/prepend: `SET l = l + [...]` or `SET l = [...] + l` (not idempotent). - Index operations: `SET l[i] = v`, `DELETE l[i]`, `SET l = l - [...]`. - Index operations require read-before-write and can be slower. #### Vectors Vectors are fixed-size sequences of non-null values of a single type. - Literals use list syntax. - Individual element update and selection are not supported. ## Functions ### CAST `CAST` converts between native types where supported. | from | to | | --- | --- | | ascii | text, varchar | | bigint | tinyint, smallint, int, float, double, decimal, varint, text, varchar | | boolean | text, varchar | | counter | tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar | | date | timestamp | | decimal | tinyint, smallint, int, bigint, float, double, varint, text, varchar | | double | tinyint, smallint, int, bigint, float, decimal, varint, text, varchar | | float | tinyint, smallint, int, bigint, double, decimal, varint, text, varchar | | inet | text, varchar | | int | tinyint, smallint, bigint, float, double, decimal, varint, text, varchar | | smallint | tinyint, int, bigint, float, double, decimal, varint, text, varchar | | time | text, varchar | | timestamp | date, text, varchar | | timeuuid | timestamp, date, text, varchar | | tinyint | tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar | | uuid | text, varchar | | varint | tinyint, smallint, int, bigint, float, double, decimal, text, varchar | Casting uses Java semantics; casting to the same type is a no-op. ### TOKEN `TOKEN` computes the partitioner token for the partition key. Return type depends on the partitioner: - Murmur3Partitioner: `bigint` - RandomPartitioner: `varint` - ByteOrderedPartitioner: `blob` ### UUID `uuid()` generates a random (type 4) UUID. ### Timeuuid functions - `now()` generates a new timeuuid at execution time. - `min_timeuuid(timestamp)` and `max_timeuuid(timestamp)` return a fake timeuuid for range queries (not safe for inserts). - Values from `min_timeuuid` and `max_timeuuid` are not RFC 4122 compliant and are not unique. ### Time conversion functions | function | input type | description | | --- | --- | --- | | to_date | timeuuid | Convert timeuuid to date. | | to_date | timestamp | Convert timestamp to date. | | to_timestamp | timeuuid | Convert timeuuid to timestamp. | | to_timestamp | date | Convert date to timestamp. | | to_unix_timestamp | timeuuid | Convert timeuuid to bigint. | | to_unix_timestamp | timestamp | Convert timestamp to bigint. | | to_unix_timestamp | date | Convert date to bigint. | ### Blob conversion functions For each native type `` (except `blob`): - `_as_blob()` returns a `blob` representation. - `blob_as_(blob)` converts a 64-bit blob to ``. Example: `bigint_as_blob(3)` yields `0x0000000000000003` and `blob_as_bigint(0x0000000000000003)` yields `3`. ## Aggregates Aggregate functions compute values across multiple rows. When selected alongside non-aggregates, non-aggregate values come from the first matching row. ### COUNT - `COUNT(*)` returns the number of rows. - `COUNT(column)` returns the number of non-null values. ### MAX and MIN Compute maximum or minimum values for a column. ### SUM Sums numeric values for a column. ### AVG Computes the average of numeric values for a column. ## User-defined functions - Languages: Java and JavaScript (other JSR 223 languages removed). - UDFs are part of schema and replicated across the cluster. - UDFs can be overloaded by argument types. - Exceptions during execution fail the statement. - Complex types (collections, tuples, UDTs) are supported. - Function bodies can be enclosed in `$$...$$` to avoid escaping single quotes. - `udfContext` provides helpers to create UDT and tuple values. ## User-defined aggregates - Built from UDFs using `SFUNC`, optional `FINALFUNC`, and `STYPE`. - `INITCOND` defines the initial state (default `null`). - `STYPE` must be specified to distinguish overloads. ## JSON support ### SELECT JSON `SELECT JSON` returns each row as a JSON-encoded map with a single column named `json`. Case-sensitive column names are returned with double-quoted keys. ### INSERT JSON `INSERT JSON` inserts a JSON map as a single row. Missing columns default to `NULL` (creating tombstones). ### JSON encoding of CQL types | type | formats accepted | return format | notes | | --- | --- | --- | --- | | ascii | string | string | Uses JSON `\u` escapes. | | bigint | integer, string | integer | String must be 64-bit integer. | | blob | string | string | 0x followed by even number of hex digits. | | boolean | boolean, string | boolean | String must be true or false. | | date | string | string | `YYYY-MM-DD`, UTC. | | decimal | integer, float, string | float | May exceed float precision in client decoders. | | double | integer, float, string | float | String must be valid integer or float. | | float | integer, float, string | float | String must be valid integer or float. | | inet | string | string | IPv4 or IPv6 address. | | int | integer, string | integer | String must be 32-bit integer. | | list | list, string | list | JSON list representation. | | map | map, string | map | JSON map representation. | | smallint | integer, string | integer | String must be 16-bit integer. | | set | list, string | list | JSON list representation. | | text | string | string | Uses JSON `\u` escapes. | | time | string | string | `HH:MM:SS[.fffffffff]`. | | timestamp | integer, string | string | Returned as `YYYY-MM-DD HH:MM:SS.SSS`. | | timeuuid | string | string | UUID format. | | tinyint | integer, string | integer | String must be 8-bit integer. | | tuple | list, string | list | JSON list representation. | | UDT | map, string | map | JSON map with field names as keys. | | uuid | string | string | UUID format. | | varchar | string | string | Uses JSON `\u` escapes. | | varint | integer, string | integer | May exceed 32/64-bit precision in decoders. | ### from_json() `from_json()` can be used where a column value literal is expected (INSERT VALUES, UPDATE assignments, and DELETE/SELECT restrictions). It cannot be used in the selection clause of a `SELECT`. ### to_json() `to_json()` can be used in the selection clause of a `SELECT` to render a single column value as JSON. ## Appendix A: CQL keywords Reserved keywords cannot be used as identifiers unless quoted. | keyword | reserved | | --- | --- | | ADD | yes | | AGGREGATE | no | | ALL | no | | ALLOW | yes | | ALTER | yes | | AND | yes | | APPLY | yes | | AS | no | | ASC | yes | | ASCII | no | | AUTHORIZE | yes | | BATCH | yes | | BEGIN | yes | | BIGINT | no | | BLOB | no | | BOOLEAN | no | | BY | yes | | CALLED | no | | CAST | no | | CLUSTERING | no | | COLUMNFAMILY | yes | | COMPACT | no | | CONTAINS | no | | COUNT | no | | COUNTER | no | | CREATE | yes | | CUSTOM | no | | DATE | no | | DECIMAL | no | | DEFAULT | yes | | DELETE | yes | | DESC | yes | | DESCRIBE | yes | | DISTINCT | no | | DOUBLE | no | | DROP | yes | | DURATION | no | | ENTRIES | yes | | EXECUTE | yes | | EXISTS | no | | FILTERING | no | | FINALFUNC | no | | FLOAT | no | | FROM | yes | | FROZEN | no | | FULL | yes | | FUNCTION | no | | FUNCTIONS | no | | GRANT | yes | | GROUP | no | | IF | yes | | IN | yes | | INDEX | yes | | INET | no | | INFINITY | yes | | INITCOND | no | | INPUT | no | | INSERT | yes | | INT | no | | INTO | yes | | IS | yes | | JSON | no | | KEY | no | | KEYS | no | | KEYSPACE | yes | | KEYSPACES | no | | LANGUAGE | no | | LIKE | no | | LIMIT | yes | | LIST | no | | LOGIN | no | | MAP | no | | MASKED | no | | MATERIALIZED | yes | | MBEAN | yes | | MBEANS | yes | | MODIFY | yes | | NAN | yes | | NOLOGIN | no | | NORECURSIVE | yes | | NOSUPERUSER | no | | NOT | yes | | NULL | yes | | OF | yes | | ON | yes | | OPTIONS | no | | OR | yes | | ORDER | yes | | PARTITION | no | | PASSWORD | no | | PER | no | | PERMISSION | no | | PERMISSIONS | no | | PRIMARY | yes | | RENAME | yes | | REPLACE | yes | | RETURNS | no | | REVOKE | yes | | ROLE | no | | ROLES | no | | SCHEMA | yes | | SELECT | yes | | SELECT_MASKED | no | | SET | yes | | SFUNC | no | | SMALLINT | no | | STATIC | no | | STORAGE | no | | STYPE | no | | SUPERUSER | no | | TABLE | yes | | TEXT | no | | TIME | no | | TIMESTAMP | no | | TIMEUUID | no | | TINYINT | no | | TO | yes | | TOKEN | yes | | TRIGGER | no | | TRUNCATE | yes | | TTL | no | | TUPLE | no | | TYPE | no | | UNLOGGED | yes | | UNMASK | no | | UNSET | yes | | UPDATE | yes | | USE | yes | | USER | no | | USERS | no | | USING | yes | | UUID | no | | VALUES | no | | VARCHAR | no | | VARINT | no | | VIEW | yes | | WHERE | yes | | WITH | yes | | WRITETIME | no | ## Appendix B: CQL reserved types The following type names are reserved and cannot be used for UDT names: - bitstring - byte - complex - date - enum - interval - macaddr