[CASSANDRA-7853] select . . . in . . . order by regression

• Type: Bug
• Status: Resolved
• Priority: Normal
• Resolution: Duplicate

Environment

MAC OSX cqlsh 3.1.8 (DSE 3.2.7) vs cqlsh 5.0.1 (Cassandra 2.1rc6)

• Severity: Normal
• Assignee: Unassigned
• Reporter: K. B. Hahn
• Created: 01/Sep/14 01:32
• Updated: 16/Apr/19 09:31
• Resolved: 02/Sep/14 20:45

Description

Select statement that sorted results in earlier version gives an error in Cassandra 2.1rc6. To reproduce problem:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 2.1.0-rc6-SNAPSHOT | CQL spec 3.2.0 | Native protocol v3]
. . .
cqlsh:test> CREATE TABLE emp (
        ... empID int,
        ... deptID int,
        ... first_name varchar,
        ... last_name varchar,
        ... PRIMARY KEY (empID, deptID));
cqlsh:test> INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (104, 15, 'jane', 'smith');
cqlsh:test> INSERT INTO emp (empID, deptID, first_name, last_name) VALUES (130, 15, 'sughit', 'singh');
SELECT * FROM emp WHERE empID IN (130,104) ORDER BY deptID DESC;
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"

Using an earlier version:

Kris-Hahns-MacBook-Pro:bin krishahn$ ./cqlsh
Connected to Test Cluster at localhost:9160.
[cqlsh 3.1.8 | Cassandra 1.2.17.2 | CQL spec 3.0.0 | Thrift protocol 19.36.2]

. . .
cqlsh:excelsior> CREATE TABLE emp4 (
             ... emp int,
             ... dept int,
             ... first_name varchar,
             ... last_name varchar,
             ... PRIMARY KEY (emp, dept));
cqlsh:excelsior> INSERT INTO emp4 (emp, dept, first_name, last_name) VALUES (104, 15, 'jane', 'smith');
cqlsh:excelsior> INSERT INTO emp4 (emp, dept, first_name, last_name) VALUES (130, 15, 'sughit', 'singh');
cqlsh:excelsior> SELECT * FROM emp4 WHERE emp IN (130,104) ORDER BY dept DESC;

 emp | dept | first_name | last_name
-----+------+------------+-----------
 104 |   15 |       jane |     smith
 130 |   15 |     sughit |     singh

Issue Links

• is superceded by: CASSANDRA-7514 Support paging in cqlsh