[
https://issues.apache.org/jira/browse/CASSANDRA-20154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17907491#comment-17907491
]
David Capwell commented on CASSANDRA-20154:
-------------------------------------------
now that this looks localized to start > end, I forced the test to hit that
case and this failed right away
{code}
History:
1: UPDATE ks1.tbl SET s0=00000000-0000-4800-b000-000000000000, v0=18403,
v1=-495062346 * -1161569641, v2=true, v3=27629,
v4=00000000-0000-1000-a200-000000000000 WHERE pk0 = [1355835672369775,
-6785520085304465340] AND ck0 = 00000000-0000-1300-9600-000000000000 AND ck1
= -1.79068896E18 * -1.6588907E-27
2: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4) VALUES
([-6158516266708817735, -7861547808663209627],
00000000-0000-1c00-aa00-000000000000, -9.5379321E17 + -1.9257686E-28,
00000000-0000-4000-b100-000000000000, -18012 - 31085, -1031649182 *
-1672976530, false, 25072 * 17136, 00000000-0000-1d00-8c00-000000000000)
3: UPDATE ks1.tbl SET s0=00000000-0000-4b00-8100-000000000000, v0=-5815 +
-23973, v1=-125320268, v2=false, v3=11441,
v4=00000000-0000-1000-bd00-000000000000 WHERE pk0 = [1179220823949845790,
712587607459586578] AND ck0 = 00000000-0000-1700-8500-000000000000 AND ck1 =
-4.011889E34 * -22.25273
4: UPDATE ks1.tbl SET s0=00000000-0000-4a00-8500-000000000000, v0=-11016,
v1=418450511, v2=true, v3=-19901 * -10759,
v4=00000000-0000-1f00-a300-000000000000 WHERE pk0 = [5390641214590446922,
7778030187422349590] AND ck0 = 00000000-0000-1d00-ac00-000000000000 AND ck1 =
-7.408167E21
5: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN [2929232267250217325,
-1220475562904956058] AND [-5704172325416771783, 3846309519421053135] ALLOW
FILTERING; -- pk BETWEEN, rc=-120
{code}
Here are the steps to setup the table
{code}
CREATE KEYSPACE ks1 WITH replication = \{'class': 'SimpleStrategy',
'replication_factor': 1};
CREATE TABLE ks1.tbl (
pk0 vector<bigint, 2>,
ck0 timeuuid,
ck1 float,
s0 uuid static,
v0 smallint,
v1 int,
v2 boolean,
v3 smallint,
v4 timeuuid,
PRIMARY KEY (pk0, ck0, ck1)
) WITH CLUSTERING ORDER BY (ck0 ASC, ck1 ASC)
AND additional_write_policy = '99p'
AND allow_auto_snapshot = true
AND bloom_filter_fp_chance = 0.01
AND caching = \{'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND cdc = false
AND comment = ''
AND compaction = \{'class':
'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
'max_threshold': '32', 'min_threshold': '4'}
AND compression = \{'chunk_length_in_kb': '16', 'class':
'org.apache.cassandra.io.compress.LZ4Compressor'}
AND memtable = 'default'
AND crc_check_chance = 1.0
AND fast_path = 'keyspace'
AND default_time_to_live = 0
AND extensions = {}
AND gc_grace_seconds = 864000
AND incremental_backups = true
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair = 'BLOCKING'
AND transactional_mode = 'off'
AND transactional_migration_from = 'none'
AND speculative_retry = '99p';
{code}
> 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: [email protected]
For additional commands, e-mail: [email protected]
