Selection clause¶

The select_clause determines which columns need to be queried and returned in the result-set, as well as any transformation to apply to this result before returning. It consists of a comma-separated list of selectors or, alternatively, of the wildcard character (*) to select all the columns defined in the table.

SELECT 1, 'hello', true FROM t;            -- int, text, boolean
SELECT [1, 2, 3] FROM t;                   -- frozen<list<int>>
SELECT {'a': 1, 'b': 2} FROM t;            -- frozen<map<text, int>>
SELECT [1, 10000000000] FROM t;            -- frozen<list<bigint>>, widened from int + bigint
SELECT CAST(1 AS bigint) FROM t;           -- bigint via explicit cast

// Without alias
SELECT intAsBlob(4) FROM t;

//  intAsBlob(4)
// --------------
//  0x00000004

// With alias
SELECT intAsBlob(4) AS four FROM t;

//  four
// ------------
//  0x00000004

-- Create a table with a map column and a set column
CREATE TABLE t (pk int PRIMARY KEY, m map<int, int>, s set<int>);

-- Insert elements with an explicit timestamp
INSERT INTO t (pk, m, s) VALUES (1, {1: 10, 2: 20}, {1, 2}) USING TIMESTAMP 1000000;

-- Update one element with a newer timestamp
UPDATE t USING TIMESTAMP 2000000 SET m = m + {2: 30}, s = s + {3} WHERE pk = 1;

-- WRITETIME(m[key]) returns the write timestamp of that specific map element
SELECT WRITETIME(m[1]), WRITETIME(m[2]) FROM t WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- WRITETIME(s[element]) returns the write timestamp of that specific set element
SELECT WRITETIME(s[1]), WRITETIME(s[3]) FROM t WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- TTL(m[key]) returns the remaining TTL of that specific map element
INSERT INTO t (pk, m) VALUES (1, {1: 10}) USING TTL 3600;
SELECT TTL(m[1]) FROM t WHERE pk = 1;
-- Returns the remaining TTL in seconds for element m[1]

CREATE TYPE address (street text, city text);
CREATE TABLE person (pk int PRIMARY KEY, addr address);

-- Write two fields at different timestamps
UPDATE person USING TIMESTAMP 1000000 SET addr.street = '123 Main St' WHERE pk = 1;
UPDATE person USING TIMESTAMP 2000000 SET addr.city = 'Springfield' WHERE pk = 1;

-- WRITETIME(udt_column.field) returns the write timestamp of that specific UDT field
SELECT WRITETIME(addr.street), WRITETIME(addr.city) FROM person WHERE pk = 1;
-- Returns: 1000000 | 2000000

-- TTL(udt_column.field) returns the remaining TTL of that specific UDT field
UPDATE person USING TTL 3600 SET addr.street = 'Oak Ave' WHERE pk = 1;
SELECT TTL(addr.street) FROM person WHERE pk = 1;
-- Returns the remaining TTL in seconds for addr.street

The WHERE clause¶

The WHERE clause specifies which rows must be queried. It is composed of relations on the columns that are part of the PRIMARY KEY, and relations can be joined only with AND (OR and other logical operators are not supported).

Not all relations are allowed in a query. For instance, non-equal relations (where IN is considered as an equal relation) on a partition key are not supported (see the use of the TOKEN method below to do non-equal queries on the partition key). Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations on them restricted to the relations that let you select a contiguous (for the ordering) set of rows. For instance, given:

CREATE TABLE posts (
    userid text,
    blog_title text,
    posted_at timestamp,
    entry_title text,
    content text,
    category int,
    PRIMARY KEY (userid, blog_title, posted_at)
)

The following query is allowed:

SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND blog_title='John''s Blog'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

But the following query is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are set):

// Needs a blog_title to be set to select ranges of posted_at
SELECT entry_title, content FROM posts
 WHERE userid = 'john doe'
   AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'

When specifying relations, the TOKEN function can be used on the PARTITION KEY column to query. In that case, rows will be selected based on the token of their PARTITION_KEY rather than on the value. Note that the token of a key depends on the partitioner in use and that, in particular, the RandomPartitioner won’t yield a meaningful order. Also note that ordering partitioners always order token values by bytes (so even if the partition key is of type int, token(-1) > token(0) in particular). For example:

SELECT * FROM posts
 WHERE token(userid) > token('tom') AND token(userid) < token('bob')

Moreover, the IN relation is only allowed on the last column of the partition key and on the last column of the full primary key.

It is also possible to “group” CLUSTERING COLUMNS together in a relation using the tuple notation. For instance:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')

will request all rows that sort after the one having “John’s Blog” as blog_title and ‘2012-01-01’ for posted_at in the clustering order. In particular, rows having a posted_at <= '2012-01-01' will be returned as long as their blog_title > 'John''s Blog'.

The tuple notation may also be used for IN clauses on clustering columns:

SELECT * FROM posts
 WHERE userid = 'john doe'
   AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01'))

This tuple notation is different from boolean grouping. For example, the following query is not supported:

SELECT * FROM users
 WHERE (country = 'BR' AND state = 'SP')

because parentheses are only allowed around a single relation, so this works: (country = 'BR') AND (state = 'SP'), but this does not: (country = 'BR' AND state = 'SP'). Similarly, an extended query of the form of:

SELECT * FROM users
 WHERE (country = 'BR' AND state = 'SP')
   OR (country = 'BR' AND state = 'RJ')

won’t work due to both: grouping boolean expressions and not supporting OR, so when possible, rewrite such queries with IN on the varying column, for example country = 'BR' AND state IN ('SP', 'RJ'), or run multiple queries and merge the results client-side.

The CONTAINS operator may only be used on collection columns (lists, sets, and maps). In the case of maps, CONTAINS applies to the map values. The CONTAINS KEY operator may only be used on map columns and applies to the map keys.

Grouping results¶

The GROUP BY option lets you condense into a single row all selected rows that share the same values for a set of columns. Using the GROUP BY option, it is only possible to group rows at the partition key level or at a clustering column level. The GROUP BY arguments must form a prefix of the primary key.

For example, if the primary key is (p1, p2, c1, c2), then the following queries are valid:

GROUP BY p1
GROUP BY p1, p2
GROUP BY p1, p2, c1
GROUP BY p1, p2, c1, c2

The following should be considered when using the GROUP BY option:

• If a primary key column is restricted by an equality restriction, it is not required to be present in the GROUP BY clause.

• Aggregate functions will produce a separate value for each group.

• If no GROUP BY clause is specified, aggregate functions will produce a single value for all the rows.

• If a column is selected without an aggregate function, in a statement with a GROUP BY, the first value encounter in each group will be returned.

Ordering results¶

The default order for a SELECT statement depends on the default clustering order of a table, which is defined when the table is created - it is ASC (ascendant) by default, but can be changed using the WITH CLUSTERING ORDER BY option. See CREATE TABLE.

The ORDER BY clause allows you to configure a non-default order of the returned result. It takes a list of column names along with the order for the column as an argument (ASC for ascendant and DESC for descendant, omitting the default order).

Currently, the possible orderings are limited by the clustering order defined on the table:

• If the table has been defined without any specific CLUSTERING ORDER, then allowed orderings are the order induced by the clustering columns and the reverse of that one.

• Otherwise, the orderings allowed are the order of the CLUSTERING ORDER option and the reversed one.

Vector queries ScyllaDB Cloud¶

The ORDER BY clause can also be used with vector columns to perform the approximate nearest neighbor (ANN) search. When using vector columns, the syntax is as follows:

order_by_vector: ORDER BY ( `vector_column_name` ANN OF `vector` | ANN '(' `vector_column_name` ',' `vector` ')' ) LIMIT `integer`

Where vector_column_name is the name of the vector column, vector is the query vector, and LIMIT is a limit on the number of results to return. Vector queries can only be performed on a vector column that has a vector index created.

The two forms are equivalent: ORDER BY embedding ANN OF [0.1, 0.2] and ORDER BY ANN(embedding, [0.1, 0.2]) produce the same query. The function form is a ScyllaDB extension, and is consistent with how the BM25 scoring function is written; the ANN OF form is compatible with Apache Cassandra.

The ANN ordering clause orders the results by their distance to the provided query vector, using the distance function defined for the vector column when creating the vector index. The query vector must have the same dimension as the vector column.

Supported LIMIT values are integers between 1 and 1000, inclusive. However, based on the index parameters and the dataset size and distribution, the actual number of results returned may be less than the specified limit.

For example:

SELECT image_id FROM ImageEmbeddings
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;

This query returns up to 5 rows with the closest distance of embedding vector to the provided query vector, in this case [0.1, 0.2, 0.3, 0.4]. Written with the function form, it is:

SELECT image_id FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;

ANN() may also be used as a selector. It returns a tuple<float, int>: the similarity the index gave the row, and the row’s rank, its position in the index’s result counted from 1:

SELECT image_id, ANN(embedding, [0.1, 0.2, 0.3, 0.4]) AS score_and_rank
  FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;

ANN_SCORE() and ANN_RANK() return the two values on their own:

SELECT image_id, ANN_SCORE(embedding, [0.1, 0.2, 0.3, 0.4]) AS similarity
  FROM ImageEmbeddings
  ORDER BY ANN(embedding, [0.1, 0.2, 0.3, 0.4]) LIMIT 5;

All three describe the search the rows are ranked by, so each is only accepted in a query whose ORDER BY clause has an ANN search, in either form, on the same column, and every occurrence must use the same query vector as that search. A query using several of them still makes a single request.

An index configured for rescoring recomputes the similarity on the coordinator and reorders the rows by it, so the rank the Vector Store gave them no longer applies. ANN_RANK() and ANN() are rejected in the SELECT clause of such a query; ANN_SCORE() and the ORDER BY itself work as usual.

To score rows against a vector other than the one being searched for, use the similarity functions directly.

For example:

SELECT image_id, similarity_cosine(embedding, [0.9, 0.8, 0.7, 0.6])
  FROM ImageEmbeddings
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;

Vector queries also support filtering with WHERE clauses on columns that are part of the primary key. See WHERE.

For example:

SELECT image_id FROM ImageEmbeddings
  WHERE user_id = 'user123'
  ORDER BY embedding ANN OF [0.1, 0.2, 0.3, 0.4] LIMIT 5;

Full-Text Search queries (BM25) ScyllaDB Cloud¶

The BM25() function enables full-text search queries over text columns that have a full-text index created with USING 'fulltext_index'. BM25() takes a column name and a query string and scores rows using the BM25 ranking algorithm.

A full-text search query must contain both of the following clauses, referencing the same column and the same search term:

• A WHERE filter of the exact form BM25(column, 'term') > 0.

• An ORDER BY BM25(column, 'term') clause that ranks the matching rows.

Neither clause is accepted on its own. A query that has only WHERE BM25() or only ORDER BY BM25() is rejected. The exception is a hybrid query, which ranks the rows by several searches at once and takes no WHERE clause.

Syntax:

fts_query: SELECT ... FROM `table_name`
         :   WHERE BM25 '(' `column_name` ',' `term` ')' '>' 0
         :   ORDER BY BM25 '(' `column_name` ',' `term` ')'
         :   LIMIT `integer`

In the WHERE clause, > is the only supported comparison operator, and the right-hand side must be the literal 0. The column must be of type text, varchar, or ascii and have a fulltext_index. Columns of the primary key and static columns cannot be indexed.

ORDER BY BM25() must be the only ordering in the query - it cannot be combined with another ORDER BY column. To rank the rows by several searches, such as a full-text and a vector search, fuse them into one ordering in a hybrid query.

A LIMIT clause is mandatory; queries without LIMIT are rejected. The specified value must not exceed 1000.

SELECT DISTINCT is not supported in queries ordered by BM25(). It is currently accepted, but does not remove duplicate partition keys.

Examples:

Filter and rank by relevance:

SELECT * FROM articles
    WHERE BM25(body, 'distributed database') > 0
    ORDER BY BM25(body, 'distributed database')
    LIMIT 10;

Use bind markers for the query term:

SELECT * FROM articles
    WHERE BM25(body, ?) > 0
    ORDER BY BM25(body, ?)
    LIMIT 10;

Return the score and the rank of each row:

SELECT title, BM25(body, 'distributed database') AS score_and_rank FROM articles
    WHERE BM25(body, 'distributed database') > 0
    ORDER BY BM25(body, 'distributed database')
    LIMIT 10;

BM25() returns a tuple<float, int>: the score the index gave the row, and the row’s rank, its position in the index’s result counted from 1. BM25_SCORE() and BM25_RANK() return the two values on their own.

All three describe the search the rows are ranked by, so each is only accepted in a query whose ORDER BY clause has a BM25 search on the same column, and every occurrence must use the same search term as that search. WHERE BM25(...) > 0 compares the score, and is the same as WHERE BM25_SCORE(...) > 0. BM25_RANK() cannot be used in the WHERE clause.

The BM25() operator is not a reserved word. If a user-defined function named bm25 exists in a keyspace, unqualified BM25() becomes ambiguous; qualify the built-in operator as system.bm25(...) to disambiguate it.

Hybrid search queries ScyllaDB Cloud¶

A hybrid query ranks the rows by several searches at once, typically a vector search and a full-text search over the same table. Its ORDER BY clause fuses the searches into one score:

hybrid_query: SELECT ... FROM `table_name`
            :   ORDER BY `fusion_function` '(' `search` ( ',' `search` )+ ')'
            :   LIMIT `integer`
search: ANN '(' `vector_column_name` ',' `vector` ')'
      : | BM25 '(' `column_name` ',' `term` ')'

For example:

SELECT id, title FROM articles
    ORDER BY RRF(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;

The searches are sent to their indexes in parallel. A row is a candidate if any of them returned it. The candidates are read, scored by the fusion function, sorted by that score, highest first, and only then cut to the LIMIT.

Each search passes the fusion function a tuple<float, int>: the score the index gave the row and the row’s rank, its position in the index’s result counted from 1. Where a search did not return the row, both are null. Scores of different searches are on different scales, so a BM25 relevance and a vector similarity cannot be meaningfully added; ranks can be compared, so fusion usually goes by rank.

RRF() is reciprocal rank fusion. A row scores 1 / (60 + rank) for each search that returned it, summed over the searches. It takes any number of searches, at least two.

A user-defined function can fuse the searches instead. It takes one tuple<float, int> argument per search and returns a float. For example, to weigh the vector search three times as much as the full-text one:

CREATE FUNCTION ks.weighted(a tuple<float, int>, b tuple<float, int>)
    CALLED ON NULL INPUT RETURNS float LANGUAGE lua AS
    'local function w(hit) if hit[2] == nil then return 0 end return 1 / hit[2] end
     return 3 * w(a) + w(b)';

SELECT id, title FROM articles
    ORDER BY ks.weighted(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;

A search is identified by its kind and its column. Two calls on the same column are the same search and must use the same query vector or search term; several columns of one kind are several searches, so RRF(BM25(title, 'database'), BM25(body, 'database')) fuses two full-text searches.

ANN(), ANN_SCORE(), ANN_RANK(), BM25(), BM25_SCORE() and BM25_RANK() may be selected to report what each search said about a row, provided the ORDER BY clause searches their column with the same query value. They return null for a row that search did not return:

SELECT id, ANN_RANK(embedding, [0.1, 0.2, 0.3, 0.4]) AS vector_rank,
           BM25_RANK(body, 'distributed database') AS text_rank
    FROM articles
    ORDER BY RRF(ANN(embedding, [0.1, 0.2, 0.3, 0.4]), BM25(body, 'distributed database'))
    LIMIT 10;

BM25_HIGHLIGHT() may be selected too, and is null in the same way: only the text of the rows the full-text search returned is sent to the index for an excerpt.

Hybrid queries have the following limitations:

• A hybrid query takes no WHERE clause.

• A LIMIT clause is mandatory, and must not exceed 1000. Only the first LIMIT rows of each search’s answer are fused: a vector index with oversampling is asked for the LIMIT times the oversampling factor, but its answer is cut to the LIMIT before the fusion.

• A vector index configured for rescoring cannot take part in a hybrid query: the rescored order has no rank to fuse by. On such an index, ORDER BY accepts only ANN() or ANN_SCORE() called directly.

• The rank is the position in the index’s result, not in the result set. If an index returns a row that is no longer in the base table, the ranks after it are not renumbered.

• Paging is not supported: the whole result is returned at once, with a warning if a page size smaller than the LIMIT was requested.

Indexed LIKE queries (pattern index) ScyllaDB Cloud¶

This section covers LIKE on a column that has a pattern index. LIKE as a filter, available in every deployment, is described in LIKE Operator. The two use the same pattern syntax but otherwise behave differently, for example in result order, paging, consistency and case sensitivity; see Indexed vs. Filtered LIKE for a comparison.

On a column that has a pattern index, every LIKE restriction is served by the index instead of by filtering. Such a query does not need ALLOW FILTERING; if it is given, it is ignored and the index is still used:

pattern_query: SELECT ... FROM `table_name`
               :   WHERE `column_name` LIKE `pattern`
               :   LIMIT `integer`

Example:

SELECT id, code FROM parts
    WHERE code LIKE '%024%'
    LIMIT 10;

The rules are:

• The pattern uses the LIKE syntax. Which patterns are supported depends on the index node, and a pattern it does not support fails the query with an InvalidRequest error.

• A bind marker may stand for the pattern (LIKE ?). The pattern must not be null.

• LIMIT is mandatory and must not exceed 1000. ORDER BY, PER PARTITION LIMIT, GROUP BY, aggregation and any further WHERE restriction, including one on the partition key, are not supported.

• The query is served by the index even with ALLOW FILTERING. When the index node is unavailable or does not support the pattern, the query fails.

Limiting results¶

The LIMIT option to a SELECT statement limits the number of rows returned by a query, while the PER PARTITION LIMIT option limits the number of rows returned for a given partition by the query. Note that both types of limit can be used in the same statement.

Examples:

The Partition Key in the following table is client_id, and the clustering key is when. The table has seven rows, split between four clients (partition keys)

cqlsh:ks1> SELECT client_id, when FROM test;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       2 | 2020-02-12 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(7 rows)

You can ask the query to limit the number of rows returned from all partition with LIMIT, for example:

cqlsh:ks1> SELECT client_id, when FROM ks1.test LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000

(3 rows)

You can ask the query to limit the number of rows returned for each client_id. For example, with limit of 1 :

cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(4 rows)

Increasing limit to 2, would yield:

cqlsh:ks1> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 2;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       1 | 2020-01-01 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       2 | 2020-02-11 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000
       3 | 2020-02-10 22:00:00.000000+0000

(6 rows)

You can also mix the two limits types:

cqlsh> SELECT client_id, when FROM ks1.test PER PARTITION LIMIT 1 LIMIT 3;

client_id | when
-----------+---------------------------------
       1 | 2019-12-31 22:00:00.000000+0000
       2 | 2020-02-10 22:00:00.000000+0000
       4 | 2020-02-10 22:00:00.000000+0000

(3 rows)

Allowing filtering¶

By default, CQL only allows select queries that don’t involve “filtering” server-side, i.e. queries where we know that all (live) record read will be returned (maybe partly) in the result set. The reasoning is that those “non filtering” queries have predictable performance in the sense that they will execute in a time that is proportional to the amount of data returned by the query (which can be controlled through LIMIT).

The ALLOW FILTERING option lets you explicitly allow (some) queries that require filtering. Please note that a query using ALLOW FILTERING may thus have unpredictable performance (for the definition above), i.e. even a query that selects a handful of records may exhibit performance that depends on the total amount of data stored in the cluster.

For instance, consider the following table holding user profiles with their year of birth (with a secondary index on it) and country of residence:

CREATE TABLE users (
    username text PRIMARY KEY,
    firstname text,
    lastname text,
    birth_year int,
    country text
)

CREATE INDEX ON users(birth_year);

Then the following queries are valid:

SELECT * FROM users;
SELECT * FROM users WHERE birth_year = 1981;

because in both cases, ScyllaDB guarantees that these queries’ performance will be proportional to the amount of data returned. In particular, if no users were born in 1981, then the second query performance will not depend on the number of user profiles stored in the database (not directly at least: due to secondary index implementation consideration, this query may still depend on the number of nodes in the cluster, which indirectly depends on the amount of data stored. Nevertheless, the number of nodes will always be multiple orders of magnitude lower than the number of user profiles stored). Of course, both queries may return very large result sets in practice, but the amount of data returned can always be controlled by adding a LIMIT.

However, the following query will be rejected:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR';

because ScyllaDB cannot guarantee that it won’t have to scan a large amount of data even if the result of those queries is small. Typically, it will scan all the index entries for users born in 1981 even if only a handful are actually from France. However, if you “know what you are doing”, you can force the execution of this query by using ALLOW FILTERING and so the following query is valid:

SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;

Evaluation order of SELECT statement clauses¶

This section explains the relative priority among the various clauses of the SELECT statement.

• All rows of the table named in the FROM clause are considered as candidates.

• Rows are ordered in token order first, then partition key order, then clustering key order.

• If ORDER BY is specified, then the clustering key order can be reversed.

• The WHERE clause predicate is applied.

• GROUP BY is then applied to create groups.

• Aggregate functions in the SELECT clause are applied to groups, or to the entire query if GROUP BY was not specified.

• If there are selectors that are not aggregate functions, then the first value in the group is selected.

• If specified, PER PARTITION LIMIT is applied to each partition result.

• If specified, LIMIT is applied to the entire query result.

SELECT without FROM ScyllaDB Extension¶

The FROM clause is optional. When omitted, the SELECT statement evaluates the given expressions and returns them as a single row, which is useful for evaluating functions or computing constant expressions without needing a table. Internally, the statement runs as an ordinary select over system.one_row, a built-in table with exactly one row.

* and DISTINCT are not allowed without FROM, and there are no columns in scope to reference. Aggregate functions operate on the single row, so, for example, count(*) returns 1.

Examples:

SELECT 1 AS one, 'hello' AS greeting;
SELECT toTimestamp(now()) AS ts;
SELECT JSON 1, now();

Bypass Cache¶

The BYPASS CACHE clause on SELECT statements informs the database that the data being read is unlikely to be read again in the near future, and also was unlikely to have been read in the near past; therefore, no attempt should be made to read it from the cache or to populate the cache with the data. This is mostly useful for range scans; these typically process large amounts of data with no temporal locality and do not benefit from the cache. The clause is placed immediately after the optional ALLOW FILTERING clause.

BYPASS CACHE is a ScyllaDB CQL extension and not part of Apache Cassandra CQL.

For example:

SELECT * FROM users BYPASS CACHE;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE;
SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING BYPASS CACHE;

Using Timeout¶

The USING TIMEOUT clause allows specifying a timeout for a specific request.

For example:

SELECT * FROM users USING TIMEOUT 5s;
SELECT name, occupation FROM users WHERE userid IN (199, 200, 207) BYPASS CACHE USING TIMEOUT 200ms;

USING TIMEOUT is a ScyllaDB CQL extension and not part of Apache Cassandra CQL.

LIKE Operator¶

The LIKE operation on SELECT statements informs ScyllaDB that you are looking for a pattern match. The expression ‘column LIKE pattern’ yields true only if the entire column value matches the pattern.

Without an index, LIKE is a filter: ScyllaDB reads the rows and checks each value against the pattern, so the query requires ALLOW FILTERING. This section describes that filtered LIKE. On a column with a pattern index, LIKE is served by the index instead, even with ALLOW FILTERING, and follows different rules; see Indexed LIKE queries and Indexed vs. Filtered LIKE.

The search pattern is a string of characters with two wildcards, as shown:

• _ matches any single character

• % matches any substring (including an empty string)

• \ escapes the next pattern character, so it matches verbatim

• any other pattern character matches itself

• an empty pattern matches empty text fields

A filtered LIKE is always case sensitive. The entire column value must match the pattern. For example, consider the search pattern ‘M%n’ - this will match Martin, but will not match Moonbeam because the m at the end isn’t matched. In addition, moon is not matched because M is not the same as m. Both the pattern and the column value are assumed to be UTF-8 encoded.

To match regardless of letter case, use a pattern index created with 'case_sensitive': 'false'.

A query can find all values containing some text fragment by matching to an appropriate LIKE pattern.

Differences Between ScyllaDB and Cassandra LIKE Operators

• In Apache Cassandra, you must create a SASI index to use LIKE. ScyllaDB supports LIKE as a regular filter, and can also serve it from a pattern index.

• Consequently, a filtered ScyllaDB LIKE will be less performant than Apache Cassandra LIKE for some workloads.

• ScyllaDB treats underscore (_) as a wildcard; Cassandra doesn’t.

• ScyllaDB treats percent (%) as a wildcard anywhere in the pattern; Cassandra only at the beginning/end

• ScyllaDB interprets backslash (\) as an escape character; Cassandra doesn’t.

• Cassandra allows case-insensitive LIKE through an option of the SASI index. A filtered ScyllaDB LIKE is always case-sensitive (see #4911); case-insensitive matching needs a pattern index created with 'case_sensitive': 'false'.

• ScyllaDB allows empty LIKE pattern; Cassandra doesn’t.

Example A

In this example, LIKE specifies that the match is looking for a word that starts with the letter S. The % after the letter S matches any text to the end of the field.

SELECT * FROM pet_owners WHERE firstname LIKE ‘S%’ ALLOW FILTERING;
╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│1         │Adams                │Steven          │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│25        │Harris               │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│98        │Smith                │Sara            │
├──────────┼─────────────────────┼────────────────┤
│115       │Williams             │Susan           │
├──────────┼─────────────────────┼────────────────┤
│130       │Young                │Stuart          │
╰──────────┴─────────────────────┴────────────────╯

Example B

In this example, you are searching for all pet owners whose last name contains the characters ‘erg’.

SELECT * FROM pet_owners WHERE lastname LIKE ‘%erg%’ ALLOW FILTERING;

╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│11        │Berger               │David           │
├──────────┼─────────────────────┼────────────────┤
│18        │Gerg                 │Lawrence        │
├──────────┼─────────────────────┼────────────────┤
│20        │Goldberg             │Stephanie       │
├──────────┼─────────────────────┼────────────────┤
│88        │Rosenberg            │Samuel          │
├──────────┼─────────────────────┼────────────────┤
│91        │Schulberg            │Barry           │
├──────────┼─────────────────────┼────────────────┤
│110       │Weinberg             │Stuart          │
╰──────────┴─────────────────────┴────────────────╯

Note that this query does not return:

╭──────────┬─────────────────────┬────────────────╮
│ID        │LastName             │FirstName       │
├──────────┼─────────────────────┼────────────────┤
│15        │Erg                  │Sylvia          │
╰──────────┴─────────────────────┴────────────────╯

As it is case sensitive.

Example C

This table contains some commonly used LIKE filters and the matches you can expect the filter to return.

IS NULL and IS NOT NULL Operators¶

The IS NULL and IS NOT NULL operators allow you to filter rows based on whether a column value is present or absent.

• IS NULL returns rows where the column has no value (is null).

• IS NOT NULL returns rows where the column has a value.

Whether these operators require ALLOW FILTERING depends on the column:

• On a partition key column, IS NOT NULL never requires ALLOW FILTERING. Such a column can never be null, so the restriction matches every row and is dropped entirely. Being dropped, it also doesn’t count as restricting the partition key for the rules below. IS NULL on a partition key column matches no row, but it is still a restriction, so it requires ALLOW FILTERING.

• On a clustering key column, both operators are ordinary restrictions on that column and neither is dropped, because a clustering key column can read as null - see below. They therefore need the partition key fully restricted by = or IN (a token() range is not enough) and every preceding clustering key column restricted; otherwise they require ALLOW FILTERING.

• On any other column - regular or static - both operators require ALLOW FILTERING, because every row’s value has to be inspected. This holds even when the column is indexed: the index is not consulted for a null check.

A clustering key column reads as null in one case: a partition that holds a static row but no clustering rows is returned as a single row whose clustering key columns are all null. So on a clustering key column IS NULL selects exactly those static-only rows, and IS NOT NULL selects exactly the clustering rows. In a table with no static columns there are no such rows, which makes IS NULL match nothing and IS NOT NULL match everything - but the two are still ordinary restrictions there, subject to the same rules.

CREATE TABLE events (p int, c int, v int, PRIMARY KEY (p, c));

-- no ALLOW FILTERING needed: p is a partition key column, so IS NOT NULL is
-- dropped and this is a full scan
SELECT * FROM events WHERE p IS NOT NULL;

-- no ALLOW FILTERING needed: the partition key is fixed, so IS [NOT] NULL on
-- the clustering key is an ordinary clustering key restriction
SELECT * FROM events WHERE p = 1 AND c IS NOT NULL;
SELECT * FROM events WHERE p = 1 AND c IS NULL;

-- ALLOW FILTERING needed: no partition key restriction to anchor the
-- clustering key restriction
SELECT * FROM events WHERE c IS NOT NULL ALLOW FILTERING;
SELECT * FROM events WHERE c IS NULL ALLOW FILTERING;

-- ALLOW FILTERING needed: v is a regular column
SELECT * FROM events WHERE p = 1 AND v IS NULL ALLOW FILTERING;

Like other Scylla filtering predicates, they can also be combined with additional restrictions on the same column. The result follows ordinary conjunction semantics: contradictory predicates such as x IS NULL AND x = 3 match no rows, while compatible predicates such as x IS NOT NULL AND x = 3 work normally.

Unlike the other filtering operators, IS NULL and IS NOT NULL may also be used on non-frozen collection columns, because they only test whether the column has a value rather than compare it against one. Note that a non-frozen collection with no elements is indistinguishable from a missing one, so an empty collection is considered null.

Example

CREATE TABLE users (id int PRIMARY KEY, name text, email text);
INSERT INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO users (id, name) VALUES (2, 'Bob');

SELECT * FROM users WHERE email IS NULL ALLOW FILTERING;

 id | email | name
----+-------+------
  2 |  null |  Bob

SELECT * FROM users WHERE email IS NOT NULL ALLOW FILTERING;

 id | email             | name
----+-------------------+-------
  1 | alice@example.com | Alice

Usage in Materialized Views

The IS NOT NULL restriction is required in materialized view definitions for all columns that are part of the view’s primary key, as primary key columns cannot be null. See Materialized Views for more details. This usage does not require ALLOW FILTERING. On a base table primary key column the restriction is a no-op, because a view row is only ever built from a base clustering row, and such a row has a value for every one of the base table’s primary key columns. On a regular base table column promoted into the view’s primary key it is not a no-op: it is what excludes the base rows where that column is null from the view. IS NULL is not supported in materialized view definitions, and neither operator can be used in a view definition on a column that is not part of the view’s primary key - views do not support filtering on non-key columns.

Apache Cassandra Query Language (CQL) Reference

Copyright

© 2016, The Apache Software Foundation.

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.