[
https://issues.apache.org/jira/browse/CASSANDRA-20154?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17907481#comment-17907481
]
David Capwell commented on CASSANDRA-20154:
-------------------------------------------
I am working on CQL model testing and I think this isn't localized to token fn,
this happened for me with partition key value.
{code}
33: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN [-2878770320244128199,
-203346577545623963] AND [-4192885816482051076, 98075706893185202] ALLOW
FILTERING; -- pk BETWEEN
{code}
In this example "[-2878770320244128199, -203346577545623963]" is greater than
"[-4192885816482051076, 98075706893185202]" so it should never match anything,
yet I got results back!
I print the value order in my test and see the following
{code}
-- Ordered values
...
[-4192885816482051076, 98075706893185202] -- end
...
[-3991897441031442413, -5518318226209889119] -- match
...
[-2878770320244128199, -203346577545623963] -- start
...
{code}
{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';
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
[-5704172325416771783, 3846309519421053135] AND [2929232267250217325,
-1220475562904956058] ALLOW FILTERING; -- pk BETWEEN
6: INSERT INTO ks1.tbl (pk0, ck0, ck1, v1) VALUES
([2929232267250217325, -1220475562904956058],
00000000-0000-1f00-b800-000000000000, -4.5964221E27, 2064175109)
7: UPDATE ks1.tbl SET s0=00000000-0000-4e00-8400-000000000000,
v0=17891 + 3022, v1=1504598085 + 1101501236, v2=true, v3=19308,
v4=00000000-0000-1d00-bb00-000000000000 WHERE pk0 = [-259059460210907102,
-1645227451934361622] AND ck0 = 00000000-0000-1c00-9e00-000000000000 AND ck1
= 1.3458757E32 + -8.565376E16
8: INSERT INTO ks1.tbl (pk0, ck0, ck1, v1) VALUES
([-541857316546990509, 1114589261596723863],
00000000-0000-1100-9300-000000000000, 5.8809172E-27, 296400816 * -1942603997)
9: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([7196344161821254078, -8223336020965922179],
00000000-0000-1c00-8d00-000000000000, -1.18768968E10 + 1.4744335E38,
00000000-0000-4a00-8600-000000000000, 13241, -1878702909 - 703665696, false,
18235, 00000000-0000-1b00-a600-000000000000)
10: DELETE v0, v1, v2, v4 FROM ks1.tbl WHERE pk0 =
[-259059460210907102, -1645227451934361622] AND ck0 =
00000000-0000-1300-9d00-000000000000 AND ck1 = 1.0844903E35
11: UPDATE ks1.tbl SET v0=-30903 * 27573, v2=true, v3=30667,
v4=00000000-0000-1300-bf00-000000000000 WHERE pk0 = [5451071733174098889,
1653948671266695265] AND ck0 = 00000000-0000-1f00-8400-000000000000 AND ck1 =
-2.86097E28 * -9.0008196E-32
12: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([-4199391080181793311, -1906103115569602962],
00000000-0000-1400-9600-000000000000, 1.32273448E18 + 5.0844207E-10,
00000000-0000-4800-bf00-000000000000, -24549 - -827, -145304360, false, -10040,
00000000-0000-1500-9c00-000000000000)
13: UPDATE ks1.tbl SET s0=00000000-0000-4000-9300-000000000000,
v0=-29386 - -7443, v1=-68247772, v2=false, v3=18208 * -3419,
v4=00000000-0000-1900-8600-000000000000 WHERE pk0 = [-9091193049635175338,
-9032697114177287761] AND ck0 = 00000000-0000-1000-8300-000000000000 AND ck1
= -272091.75
14: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN
[-8130167752969267821, -2157226589982377454] AND [141576648926801786,
7383322623601173678] ALLOW FILTERING; -- pk BETWEEN
15: UPDATE ks1.tbl SET s0=00000000-0000-4600-a700-000000000000,
v0=5584 + 27203, v1=615891816 - 628523066, v2=true, v3=11971 - -25726,
v4=00000000-0000-1a00-8600-000000000000 WHERE pk0 = [-1063646753832141608,
546534980926114739] AND ck0 = 00000000-0000-1b00-af00-000000000000 AND ck1 =
6.4535835E18
16: DELETE FROM ks1.tbl WHERE pk0 = [-5047710136686827084,
5521075528270689731]
17: INSERT INTO ks1.tbl (pk0, ck0, ck1, v2) VALUES
([-9091193049635175338, -9032697114177287761],
00000000-0000-1f00-9100-000000000000, -0.011240102 + 5.6527026E26, false)
18: DELETE FROM ks1.tbl WHERE pk0 = [5451071733174098889,
1653948671266695265] AND ck0 = 00000000-0000-1b00-a300-000000000000 AND ck1 =
-2.285084E35
19: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([-6496726100186017566, -3039357267610553350],
00000000-0000-1d00-8700-000000000000, -4.0941727E34,
00000000-0000-4b00-8e00-000000000000, -7409, -460387216, true, 12858,
00000000-0000-1500-8800-000000000000)
20: DELETE v1 FROM ks1.tbl WHERE pk0 = [6212536393132884817,
4114466704212210640] AND ck0 = 00000000-0000-1700-9300-000000000000 AND ck1 =
-3.976186E32
21: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN
[-126548869119638583, 4377483905874898060] AND [7879444429343937203,
5889484806745807212] ALLOW FILTERING; -- pk BETWEEN
22: UPDATE ks1.tbl SET s0=00000000-0000-4f00-bc00-000000000000,
v0=29256 - -4655, v1=1198246832, v2=false, v3=-2460,
v4=00000000-0000-1a00-ae00-000000000000 WHERE pk0 = [6856899131430592997,
-3947282675983297343] AND ck0 = 00000000-0000-1000-b000-000000000000 AND ck1
= 3.0965652E27 - 118.437355
23: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([-6041539979971172789, 2094834853955338278],
00000000-0000-1a00-8c00-000000000000, 1.1355977E19,
00000000-0000-4500-9a00-000000000000, 26058, 2041454759 * 726853401, false,
1747, 00000000-0000-1700-b100-000000000000)
24: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v3, v4) VALUES
([1261111340948926624, 5230994890757686059],
00000000-0000-1200-ac00-000000000000, 1.1181304E-21,
00000000-0000-4000-8d00-000000000000, -29262, -6462 - 16353,
00000000-0000-1100-9000-000000000000)
25: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN
[-5335151488260833470, -3332938684249578510] AND [4808763727145238002,
-2214852440438443509] ALLOW FILTERING; -- pk BETWEEN
26: DELETE s0, s0 FROM ks1.tbl WHERE pk0 =
[7180978469086833253, -7801518584966990451]
27: UPDATE ks1.tbl SET s0=00000000-0000-4600-a500-000000000000,
v0=-23702 * -4859, v1=-274528887 - -1008504317, v2=true, v3=17550 * -6578,
v4=00000000-0000-1100-8d00-000000000000 WHERE pk0 = [-243227898709182579,
-6038617780328330641] AND ck0 = 00000000-0000-1e00-8000-000000000000 AND ck1
= 3.37941103E12
28: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([-3991897441031442413, -5518318226209889119],
00000000-0000-1b00-8c00-000000000000, -1.8759379E-23 + 9.832552E-22,
00000000-0000-4700-9e00-000000000000, -28026 + 7573, 1466407629 - 1631413069,
true, 17768 + 8618, 00000000-0000-1000-a800-000000000000)
29: UPDATE ks1.tbl SET v2=true, v3=6454,
s0=00000000-0000-4700-bc00-000000000000 WHERE pk0 = [6328885713997900091,
-2760621685032347000] AND ck0 = 00000000-0000-1300-8200-000000000000 AND ck1
= -2.3992077E-11
30: INSERT INTO ks1.tbl (pk0, ck0, ck1, s0, v0, v1, v2, v3, v4)
VALUES ([-7723818749604640318, -9054844102399005006],
00000000-0000-1300-ab00-000000000000, -1.9764596E-14 + -7.5176804E-31,
00000000-0000-4300-9500-000000000000, -32541, 1160205184, true, 9567,
00000000-0000-1b00-8700-000000000000)
31: UPDATE ks1.tbl SET v0=-11906 * 24413, v1=-760453429 +
771819033, v2=true, v3=-32050 * -25723,
v4=00000000-0000-1c00-a600-000000000000,
s0=00000000-0000-4700-9200-000000000000 WHERE pk0 = [7885540249043491144,
-9049930744443420629] AND ck0 = 00000000-0000-1600-a900-000000000000 AND ck1
= -2.2378636E22
32: DELETE s0 FROM ks1.tbl WHERE pk0 = [6040778144342943286,
5058344850602385405]
33: SELECT * FROM ks1.tbl WHERE pk0 BETWEEN
[-2878770320244128199, -203346577545623963] AND [-4192885816482051076,
98075706893185202] ALLOW FILTERING; -- pk BETWEEN
{code}
Error was
{code}
Caused by: java.lang.AssertionError: Unexpected rows found:
pk0 | ck0
| ck1 | s0 | v0 | v1
| v2 | v3 | v4
[-3991897441031442413, -5518318226209889119] |
00000000-0000-1b00-8c00-000000000000 | 9.644958E-22 |
00000000-0000-4700-9e00-000000000000 | -20453 | -165005440 | true | 26386 |
00000000-0000-1000-a800-000000000000
Expected:
pk0 | ck0 | ck1 | s0 | v0 | v1 | v2 | v3 | v4
{code}
Ill see if I can get a smaller repo...
> 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]
