cassandra-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Benjamin Lerer (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (CASSANDRA-14172) Select with multiple clustering columns and inequality doesn't work
Date Fri, 19 Jan 2018 08:57:01 GMT

    [ https://issues.apache.org/jira/browse/CASSANDRA-14172?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16331936#comment-16331936
] 

Benjamin Lerer edited comment on CASSANDRA-14172 at 1/19/18 8:56 AM:
---------------------------------------------------------------------

{quote}Once I removed the birthday and discoverytime out of the key, the command you suggested
worked fine.
{quote}
Filtering is supported on clustering columns since version 3.6 (CASSANDRA-11310). So it will
depends on which version you are.
{quote}Just want to confirm with you. In the following query, the last "AND" means "plus",
not the logical AND, right?
{quote}
No, it is really a logical {{AND}}. A lower limit and an upper one on the full clustering
key.

Under the hood for each partition C* keep the rows ordered by clustering key (eg. ({{1,1)}},
{{(1,2)}}, ({{1,3)}}, ({{1,4)}}, ({{2,1)}}, ({{2,2)}}, ({{2,3)}}, ...).
 For C*
{code:java}
(c1, c2) >= (1,3) AND (c1, c2) <= (2, 2){code}
means that you want to retrieve, for the selected partitions, all the rows that have a clustering
key in the range [(1,3), (2,2)]. In our example, the rows with the clustering keys: ({{1,3)}},
({{1,4)}}, ({{2,1)}}, ({{2,2)}}


was (Author: blerer):
{quote}Once I removed the birthday and discoverytime out of the key, the command you suggested
worked fine.
{quote}
Filtering is supported on clustering columns since version 3.6 (CASSANDRA-11310). So it will
depends on which version you are.
{quote}Just want to confirm with you. In the following query, the last "AND" means "plus",
not the logical AND, right?
{quote}
No, it is really a logical {{AND}}. A lower limit and an upper one on the full clustering
key.

Under the hood for each partition C* keep the rows ordered by clustering key (eg. ({{1,1)}},
{{(1,2)}}, ({{1,3)}}, ({{1,4)}}, ({{2,1)}}, ({{2,2)}}, ({{2,3)}}, ...).
 For C*
{code:java}
(c1, c2) >= (1,3) AND (c1, c2) <= (2, 2){code}
means that you want to retrieve, for the selected partitions, all the rows that have a clustering
key in the range [({{1,3)}}, ({{2,2)}}]. In our example, the rows with the clustering keys:
({{1,3)}}, ({{1,4)}}, ({{2,1)}}, ({{2,2)}}

> Select with multiple clustering columns and inequality doesn't work
> -------------------------------------------------------------------
>
>                 Key: CASSANDRA-14172
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-14172
>             Project: Cassandra
>          Issue Type: Bug
>          Components: CQL
>         Environment: com.datastax.cassandra:cassandra-driver-core:3.3.0 on linux
>            Reporter: Samuel Wu
>            Priority: Major
>         Attachments: graycol.gif
>
>
> * I have a table created as the following command.
>  * CREATE TABLE IF NOT EXISTS test.discrepancies(
>  modality text,
>  disease text,
>  location text,
>  type text,
>  status text,
>  studyDate timestamp,
>  birthDay timestamp,
>  discoveryTime timestamp,
>  reportId uuid,
>  mrn text,
>  firstName text,
>  PRIMARY KEY ((location, modality, disease), type, status, studyDate, birthDay, discoveryTime)
> );
>  * The following data are populated in the table
>  *  location | modality | disease  | type | status | studydate              
        | birthday                        | discoverytime            
      | firstname | mrn  | reportid
> ----------+----------+----------+------+--------+---------------------------------+---------------------------------+---------------------------------+-----------+------+--------------------------------------
>      West |       CX | disease1 |  PLD |   OPEN | 2018-01-17 15:06:44.138000+0000
| 1970-01-02 10:17:36.789000+0000 | 2018-01-17 15:06:44.138000+0000 |     name1 | mrn1 |
9a545096-6191-4275-86b3-cc89e181f494
>  
>  * When the following command was run, I would expect no row found since the birthday
and discoveoytime is out of range. 
>  * select * from test.discrepancies where disease='disease1' and location='West' and
modality='CX' and type='PLD' and status='OPEN' and (studydate, birthday, discoverytime) <
(2516201604308,0,0);
>  * But the above row was returned instead.
>  * This feature was implemented in https://issues.apache.org/jira/browse/CASSANDRA-4851
>  * see https://docs.datastax.com/en/cql/3.3/cql/cql_using/useQueryIN.html



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cassandra.apache.org
For additional commands, e-mail: commits-help@cassandra.apache.org


Mime
View raw message