Mailing-List: contact commits-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@cassandra.apache.org
Date: Mon, 3 Aug 2015 22:39:05 +0000 (UTC)
From: "Marc Zbyszynski (JIRA)" <jira@apache.org>
To: commits@cassandra.apache.org
Message-ID: <JIRA.12851235.1438641542000.360932.1438641545200@Atlassian.JIRA>
In-Reply-To: <JIRA.12851235.1438641542000@Atlassian.JIRA>
References: <JIRA.12851235.1438641542000@Atlassian.JIRA>
 <JIRA.12851235.1438641542883@arcas>
Subject: [jira] [Created] (CASSANDRA-9970) CQL 3.3 ordering in where clause
 produces different results when using "IN...ORDER BY"
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Marc Zbyszynski created CASSANDRA-9970:
------------------------------------------

             Summary: CQL 3.3 ordering in where clause produces different results when using "IN...ORDER BY"
                 Key: CASSANDRA-9970
                 URL: https://issues.apache.org/jira/browse/CASSANDRA-9970
             Project: Cassandra
          Issue Type: Bug
          Components: API
            Reporter: Marc Zbyszynski
            Priority: Minor


This is an issue I found with
{code}
[cqlsh 5.0.1 | Cassandra 2.2.0 | CQL spec 3.3.0 | Native protocol v4]
{code}

Given this table and data:

{code:sql}
cqlsh> CREATE TABLE myschema.in_test (
   ...     col_1 int,
   ...     col_2 int,
   ...     col_3 int,
   ...     PRIMARY KEY ((col_1, col_2), col_3)
   ... );
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 1);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 2);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 1, 13);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 2, 10);
cqlsh> INSERT INTO myschema.in_test (col_1, col_2, col_3) VALUES(1, 2, 11);
{code}

This query behaves as expected (as described in [http://docs.datastax.com/en/cql/3.3/cql/cql_using/useQueryIN.html])

{code:sql}
cqlsh> select * from myschema.in_test where col_1=1 and col_2 IN (1,2) order by col_3 desc;
InvalidRequest: code=2200 [Invalid query] message="Cannot page queries with both ORDER BY and a IN restriction on the partition key; you must either remove the ORDER BY or the IN and sort client side, or disable paging for this query"
{code}

But if you swap the order of the statements in the where clause, the query executes without error and doesn't totally honor the _order by_ clause:

{code:sql}
cqlsh> select * from myschema.in_test where col_2 IN (1,2) and col_1=1 order by col_3 desc;

 col_1 | col_2 | col_3
-------+-------+-------
     1 |     2 |    11
     1 |     2 |    10
     1 |     1 |    13
     1 |     1 |     2
     1 |     1 |     1

(5 rows)
{code}

Apologies in advance if this is working as intended, or I'm duplicating an existing issue. Also please let me know if you need any additional info.


--
This message was sent by Atlassian JIRA
(v6.3.4#6332)