In the example table below an SAI index is used for the item_category
field. The item_name
column is the clustering key.
CREATE TABLE shopping_cart(
id bigint,
item_name text,
item_category text,
PRIMARY KEY (id, item_name)
) WITH CLUSTERING ORDER BY (item_name ASC);
CREATE INDEX shopping_cart_item_category_idx
ON shopping_cart (item_category)
USING 'sai';
An execution of a query such as
SELECT id, item_name, item_category
FROM shopping_cart
WHERE id = 12345 -- partition key
AND item_category = 'softdrinks' -- 2ndary index
is successful. This query selects all softdrink items of the partition. The items in the result set are sorted according to the clustering key. See Query Result
However, the use of an ORDER BY
clause in the query such as
SELECT id, item_name, item_category
FROM shopping_cart
WHERE id = 12345 -- partition key
AND item_category = 'softdrinks' -- 2ndary index
ORDER BY item_name ASC -- clustering key
fails with the error message ORDER BY with 2ndary indexes is not supported, except for ANN queries
. Since this query reads only a single partition, the query with ORDER BY
should be allowed in my opinion.
The fact that a select with ORDER BY
fails on multiple partitions, as shown below, is understandable to me as Cassandra does not support global SAI sorting, currently.
SELECT id, item_name, item_category
FROM shopping_cart
WHERE item_category = 'softdrinks' -- 2ndary index
ORDER BY item_name ASC -- clustering key
Is the observed behavior merely an issue of the query parser?