[jira] [Created] (CASSANDRA-4354) Add default range constraint to prevent non-intuitive results when using composite key

["Leonid Ilyevsky (JIRA)" <ji...@apache.org>]

Leonid Ilyevsky created CASSANDRA-4354:
------------------------------------------

             Summary: Add default range constraint to prevent non-intuitive results when using composite key
                 Key: CASSANDRA-4354
                 URL: https://issues.apache.org/jira/browse/CASSANDRA-4354
             Project: Cassandra
          Issue Type: New Feature
          Components: Core
    Affects Versions: 1.1.1
         Environment: Any
            Reporter: Leonid Ilyevsky


When ByteOrderedPartitioner is used, and the table has a composite primary key, the result of the query may be logically incorrect if only one inequality is specified.
For example, let say x and y are components of the key. The query with the predicate like "x = ?" will give correct answer, as well as "x = ? and y >= ? and y <= ?".
However, the predicate "x = ? and y >= ?" will give the result where we may see different values of x.
This behavior is understandable because we know how the composite key is used internally, but it is very confusing for users with sql experience, and indeed is very inconvenient overall.
This can me easily fixed by automatically adding the complementary inequality constraint, using bit sequence of all zeroes or all ones, depending on the side.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

[jira] [Commented] (CASSANDRA-4354) Add default range constraint to prevent non-intuitive results when using composite key

["Sylvain Lebresne (JIRA)" <ji...@apache.org>]

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

Sylvain Lebresne commented on CASSANDRA-4354:
---------------------------------------------

Which version of Cassandra are you using and do you have an actual example?

I've tried the following example that seems to match your description and it returns the correct result (i.e. it doesn't "l give the result where we may see different values of x"):
{noformat}
cqlsh:foo> CREATE TABLE test (
       ...     x int,
       ...     y int,
       ...     v int,
       ...     PRIMARY KEY (x, y)
       ... );
// Some inserts

cqlsh:foo> SELECT * FROM test;
 x | y | v
---+---+---
 0 | 0 | 0
 0 | 1 | 2
 1 | 0 | 3
 1 | 1 | 4
 1 | 2 | 5
 2 | 0 | 6
 2 | 1 | 7
 2 | 2 | 8

cqlsh:foo> SELECT * FROM test WHERE x = 1 AND y > 0;
 x | y | v
---+---+---
 1 | 1 | 4
 1 | 2 | 5

cqlsh:foo> SELECT * FROM test WHERE x = 1 AND y < 2 ORDER BY y DESC;
 x | y | v
---+---+---
 1 | 1 | 4
 1 | 0 | 3

{noformat}
                
> Add default range constraint to prevent non-intuitive results when using composite key
> --------------------------------------------------------------------------------------
>
>                 Key: CASSANDRA-4354
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-4354
>             Project: Cassandra
>          Issue Type: New Feature
>          Components: Core
>    Affects Versions: 1.1.1
>         Environment: Any
>            Reporter: Leonid Ilyevsky
>
> When ByteOrderedPartitioner is used, and the table has a composite primary key, the result of the query may be logically incorrect if only one inequality is specified.
> For example, let say x and y are components of the key. The query with the predicate like "x = ?" will give correct answer, as well as "x = ? and y >= ? and y <= ?".
> However, the predicate "x = ? and y >= ?" will give the result where we may see different values of x.
> This behavior is understandable because we know how the composite key is used internally, but it is very confusing for users with sql experience, and indeed is very inconvenient overall.
> This can me easily fixed by automatically adding the complementary inequality constraint, using bit sequence of all zeroes or all ones, depending on the side.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira