[CASSANDRA-10556] Wrong order returned when querying ttl(value) and using SELECT IN + ORDER BY

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

Environment

Tested with 3 node cluster on these versions:

C* 2.0.16.678
C* 2.1.8.689
C* 2.1.9.791

• Severity: Normal
• Assignee: Benjamin Lerer
• Reporter: Sucwinder Bassi

Authors

Benjamin Lerer

• Created: 20/Oct/15 15:56
• Updated: 16/Apr/19 09:30
• Resolved: 21/Oct/15 10:40

Description

Setup a 3 node cluster, use these steps to create a keyspace, table and load data:

CREATE KEYSPACE testksp
WITH replication =

{'class': 'SimpleStrategy', 'replication_factor':3}

AND durable_writes = true;

use testksp;

CREATE TABLE test (
useruid varchar,
direction varchar,
last_modified timestamp,
value varchar,
PRIMARY KEY ((useruid, direction), last_modified)
);

INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.010+00:00', 'a value1') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.020+00:00', 'a value2') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.030+00:00', 'a value3') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.040+00:00', 'a value4') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.050+00:00', 'a value5') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.060+00:00', 'a value6') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.070+00:00', 'a value7') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'in', '2015-09-18T14:11:15.080+00:00', 'a value8') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.090+00:00', 'a value9') USING TTL 86400;
INSERT INTO test (useruid,direction,last_modified,value) VALUES ('userid2', 'out', '2015-09-18T14:11:15.100+00:00', 'a value10') USING TTL 86400;

Running this query with ttl(value) shows this message:

cqlsh:testksp> SELECT useruid, value,ttl(value),last_modified,blobAsBigint(timestampAsBlob(last_modified)) FROM test WHERE useruid= 'userid2' AND direction IN ('out', 'in') ORDER BY last_modified;

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"

Turning off paging returns the result:

cqlsh:testksp> paging off;
Disabled Query paging.

cqlsh:testksp> SELECT useruid, value,ttl(value),last_modified,blobAsBigint(timestampAsBlob(last_modified)) FROM test WHERE useruid= 'userid2' AND direction IN ('out', 'in') ORDER BY last_modified;

useruid | value | ttl(value) | last_modified | blobAsBigint(timestampAsBlob(last_modified))
------------------------------------------------------------------------------------------------
userid2 | a value1 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475010
userid2 | a value2 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475020
userid2 | a value5 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475050
userid2 | a value6 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475060
userid2 | a value9 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475090
userid2 | a value3 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475030
userid2 | a value4 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475040
userid2 | a value7 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475070
userid2 | a value8 | 86275 | 2015-09-18 14:11:15+0000 | 1442585475080
userid2 | a value10 | 86277 | 2015-09-18 14:11:15+0000 | 1442585475100

(10 rows)

Notice value1 and value10 start and finish, but the other rows are in the wrong order. Without ttl(value) the results are returned value1 to value10 in the correct order.

Running the same test using C* 2.0.16.678 there is no need to turn off paging. The results are returned with the wrong order when querying with ttl(value).

Issue Links

• duplicates: CASSANDRA-10363 NullPointerException returned with select ttl(value), IN, ORDER BY and paging off