[
https://issues.apache.org/jira/browse/CASSANDRA-10556?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sylvain Lebresne updated CASSANDRA-10556:
-----------------------------------------
Fix Version/s: 2.1.x
> Wrong order returned when querying ttl(value) and using SELECT IN + ORDER BY
> ----------------------------------------------------------------------------
>
> Key: CASSANDRA-10556
> URL: https://issues.apache.org/jira/browse/CASSANDRA-10556
> Project: Cassandra
> Issue Type: Bug
> Environment: Tested with 3 node cluster on these versions:
> C* 2.0.16.678
> C* 2.1.8.689
> C* 2.1.9.791
> Reporter: Sucwinder Bassi
> Fix For: 2.1.x
>
>
> 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).
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
