[jira] [Commented] (CASSANDRA-20154) BETWEEN where token(Y) > token(Z) returns wrong answer

["David Capwell (Jira)" <ji...@apache.org>]

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

David Capwell commented on CASSANDRA-20154:
-------------------------------------------

We found a correctness issue, Marcus is going to file a new ticket

{code}
SELECT * FROM tbl WHERE token(pk) BETWEEN ? AND ?
{code}

if the last argument is the MIN_TOKEN then this sorting corrupts the query and produces a incorrect response.

[~xvade] are you still planning to address this issue?

> BETWEEN where token(Y) > token(Z) returns wrong answer
> ------------------------------------------------------
>
>                 Key: CASSANDRA-20154
>                 URL: https://issues.apache.org/jira/browse/CASSANDRA-20154
>             Project: Apache Cassandra
>          Issue Type: Bug
>          Components: CQL/Semantics
>            Reporter: Ariel Weisberg
>            Assignee: Simon Chess
>            Priority: Normal
>             Fix For: 5.x
>
>
> Noticed it’s not compliant with the SQL specification for BETWEEN and so doesn’t match what other databases do (tested on Postgres and MySQL).
> Here is the [SQL 92 spec|https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt] (later versions probably don't change something so fundamental).
> In particular rule 6 doesn't match the observed behavior which is to swap Y and Z and then return what is in between them which is pretty surprising as a result.
> {noformat}
> 6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
> {noformat}
> {noformat}
>          8.3  <between predicate>
>          Function
>          Specify a range comparison.
>          Format
>          <between predicate> ::=
>               <row value constructor> [ NOT ] BETWEEN
>                 <row value constructor> AND <row value constructor>
>          Syntax Rules
>          1) The three <row value constructor>s shall be of the same degree.
>          2) Let respective values be values with the same ordinal position
>             in the two <row value constructor>s.
>          3) The data types of the respective values of the three <row value
>             constructor>s shall be comparable.
>          4) Let X, Y, and Z be the first, second, and third <row value con-
>             structor>s, respectively.
>          5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND
>             Z )".
>          6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
> {noformat}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cassandra.apache.org
For additional commands, e-mail: commits-help@cassandra.apache.org