Illustration Image

ORDER BY on SAI field not supported for single-partition reads

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?

Become part of our
growing community!
Welcome to Planet Cassandra, a community for Apache Cassandra®! We're a passionate and dedicated group of users, developers, and enthusiasts who are working together to make Cassandra the best it can be. Whether you're just getting started with Cassandra or you're an experienced user, there's a place for you in our community.
A dinosaur
Planet Cassandra is a service for the Apache Cassandra® user community to share with each other. From tutorials and guides, to discussions and updates, we're here to help you get the most out of Cassandra. Connect with us and become part of our growing community today.
© 2009-2023 The Apache Software Foundation under the terms of the Apache License 2.0. Apache, the Apache feather logo, Apache Cassandra, Cassandra, and the Cassandra logo, are either registered trademarks or trademarks of The Apache Software Foundation. Sponsored by Anant Corporation and Datastax, and Developed by Anant Corporation.

Get Involved with Planet Cassandra!

We believe that the power of the Planet Cassandra community lies in the contributions of its members. Do you have content, articles, videos, or use cases you want to share with the world?