cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alec Collier <>
Subject RE: Order By limitation or bug?
Date Fri, 04 Sep 2015 00:48:52 GMT
You should be able to execute the following

SELECT data FROM import_file WHERE roll = 1 AND type = 'foo' ORDER BY type, id DESC;

Essentially the order by clause has to specify the clustering columns in order in full. It
doesn't by default know that you have already essentially filtered by type.

Alec Collier | Workplace Service Design
Corporate Operations Group - Technology | Macquarie Group Limited *

From: Robert Wille []
Sent: Friday, 4 September 2015 7:17 AM
Subject: Re: Order By limitation or bug?

If you only specify the partition key, and none of the clustering columns, you can order by
in either direction:

SELECT data FROM import_file WHERE roll = 1 order by type;
SELECT data FROM import_file WHERE roll = 1 order by type DESC;

These are both valid. Seems like specifying the prefix of the clustering columns is just a
specialization of an already-supported pattern.


On Sep 3, 2015, at 2:46 PM, DuyHai Doan <<>>

Limitation, not bug. The reason ?

On disk, data are sorted by type first, and FOR EACH type value, the data are sorted by id.

So to do an order by Id, C* will need to perform an in-memory re-ordering, not sure how bad
it is for performance. In any case currently it's not possible, maybe you should create a
JIRA to ask for lifting the limitation.

On Thu, Sep 3, 2015 at 10:27 PM, Robert Wille <<>>

Given this table:

CREATE TABLE import_file (
  roll int,
  type text,
  id timeuuid,
  data text,
  PRIMARY KEY ((roll), type, id)

This should be possible:

SELECT data FROM import_file WHERE roll = 1 AND type = 'foo' ORDER BY id DESC;

but it results in the following error:

Bad Request: Order by currently only support the ordering of columns following their declared
order in the PRIMARY KEY

I am ordering in the declared order in the primary key. I don't see why this shouldn't be
able to be supported. Is this a known limitation or a bug?

In this example, I can get the results I want by omitting the ORDER BY clause and adding WITH
CLUSTERING ORDER BY (id DESC) to the schema. However, now I can only get descending order.
I have to choose either ascending or descending order. I cannot get both.


This email, including any attachments, is confidential. If you are not the intended recipient,
you must not disclose, distribute or use the information in this email in any way. If you
received this email in error, please notify the sender immediately by return email and delete
the message. Unless expressly stated otherwise, the information in this email should not be
regarded as an offer to sell or as a solicitation of an offer to buy any financial product
or service, an official confirmation of any transaction, or as an official statement of the
entity sending this message. Neither Macquarie Group Limited, nor any of its subsidiaries,
guarantee the integrity of any emails or attached files and are not responsible for any changes
made to them by any other person.
View raw message