You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Robert Wille <rw...@fold3.com> on 2015/09/03 22:27:53 UTC
Order By limitation or bug?
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.
Robert
Re: Order By limitation or bug?
Posted by DuyHai Doan <do...@gmail.com>.
It's normal, type is the FIRST clustering column so on disk, data are
sorted first by "type" naturally. C* does not have to perform any sorting
in memory.
And when you're using "order by type DESC", it's still not sorted in
memory, C* is just doing a backward-scan on disk starting from the
"biggest" value for type. At least that's what I've understood from the
pre-3.0 storage engine.
Re: Order By limitation or bug?
Posted by Robert Wille <rw...@fold3.com>.
Thanks. Based on what I know about the architecture, it seems like it should be pretty easy to support. Thanks for the confirmation and the ticket.
On Sep 4, 2015, at 3:30 PM, Tyler Hobbs <ty...@datastax.com>> wrote:
This query would be reasonable to support, so I've opened https://issues.apache.org/jira/browse/CASSANDRA-10271 to fix that.
On Thu, Sep 3, 2015 at 7:48 PM, Alec Collier <Al...@macquarie.com>> wrote:
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 [mailto:rwille@fold3.com<ma...@fold3.com>]
Sent: Friday, 4 September 2015 7:17 AM
To: user@cassandra.apache.org<ma...@cassandra.apache.org>
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.
Robert
On Sep 3, 2015, at 2:46 PM, DuyHai Doan <do...@gmail.com>> wrote:
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 <rw...@fold3.com>> wrote:
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.
Robert
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.
--
Tyler Hobbs
DataStax<http://datastax.com/>
Re: Order By limitation or bug?
Posted by Tyler Hobbs <ty...@datastax.com>.
This query would be reasonable to support, so I've opened
https://issues.apache.org/jira/browse/CASSANDRA-10271 to fix that.
On Thu, Sep 3, 2015 at 7:48 PM, Alec Collier <Al...@macquarie.com>
wrote:
> 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 [mailto:rwille@fold3.com]
> *Sent:* Friday, 4 September 2015 7:17 AM
> *To:* user@cassandra.apache.org
> *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.
>
>
>
> Robert
>
>
>
> On Sep 3, 2015, at 2:46 PM, DuyHai Doan <do...@gmail.com> wrote:
>
>
>
> 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 <rw...@fold3.com> wrote:
>
> 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.
>
>
>
> Robert
>
>
>
>
>
>
>
> 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.
>
--
Tyler Hobbs
DataStax <http://datastax.com/>
RE: Order By limitation or bug?
Posted by Alec Collier <Al...@macquarie.com>.
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 [mailto:rwille@fold3.com]
Sent: Friday, 4 September 2015 7:17 AM
To: user@cassandra.apache.org
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.
Robert
On Sep 3, 2015, at 2:46 PM, DuyHai Doan <do...@gmail.com>> wrote:
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 <rw...@fold3.com>> wrote:
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.
Robert
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.
Re: Order By limitation or bug?
Posted by Robert Wille <rw...@fold3.com>.
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.
Robert
On Sep 3, 2015, at 2:46 PM, DuyHai Doan <do...@gmail.com>> wrote:
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 <rw...@fold3.com>> wrote:
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.
Robert
Re: Order By limitation or bug?
Posted by DuyHai Doan <do...@gmail.com>.
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 <rw...@fold3.com> wrote:
> 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.
>
> Robert
>
>