92aca17bc5
Lightweight transactions don't support tablets, so let's explicitly disable tablets in LWT tests.
837 lines
36 KiB
SQL
837 lines
36 KiB
SQL
CREATE KEYSPACE ks
|
|
WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 1} AND
|
|
tablets = {'enabled': false};
|
|
USE ks;
|
|
--
|
|
-- basic: ensure the grammar works
|
|
--
|
|
create table lwt (a int primary key, b int);
|
|
-- insert if not exists
|
|
insert into lwt (a, b) values (1, 1) if not exists;
|
|
select * from lwt allow filtering;
|
|
-- delete if exists
|
|
delete from lwt where a=1 if exists;
|
|
select * from lwt allow filtering;
|
|
-- update with condition
|
|
update lwt set b = 2 where a=1 if b = 1;
|
|
select * from lwt allow filtering;
|
|
-- update if exists
|
|
update lwt set b = 2 where a = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
-- update if exists with a scan: proper error message
|
|
update lwt set b = 2 where b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
-- incorrect syntax: update if *not* exists
|
|
update lwt set b=3 where a=2 if not exists;
|
|
-- incorrect syntax: delete if *not* exists
|
|
delete from lwt where a=2 if not exists;
|
|
--
|
|
-- timestamp: using timestamp clause is not allowed
|
|
--
|
|
-- check that using timestamp without LWT clause works
|
|
insert into lwt (a, b) values (1, 1) using timestamp 1;
|
|
-- with LWT, it's not allowed
|
|
insert into lwt (a, b) values (1, 1) if not exists using timestamp 1;
|
|
--
|
|
--
|
|
-- update if not exists is not allowed (grammar error), it doesn't make sense
|
|
update lwt set b = 2 where a = 1 if not exists;
|
|
-- update with multiple partitions can not have conditions
|
|
-- Cassandra output: code=2200 [Invalid query] message="IN on the partition
|
|
-- key is not supported with conditional updates"
|
|
update lwt set b = 2 where a in (1, 2) if b = 1;
|
|
|
|
drop table lwt;
|
|
--
|
|
-- basic: ends
|
|
--
|
|
--
|
|
-- clustering: tests related to lwt and clustering keys
|
|
--
|
|
-- create a table with a partitioning key and a clustering key
|
|
create table lwt (a int, b int, c int, primary key (a,b));
|
|
-- update with multiple clustering keys can not have conditions
|
|
-- Cassandra output: code=2200 [Invalid query] message="IN on the clustering
|
|
-- key is not supported with conditional updates"
|
|
update lwt set c = 2 where a = 1 and b in (2,3) if c = 1;
|
|
-- update with a clustering key - should work
|
|
update lwt set c = 2 where a = 1 and b = 2 if c = 1;
|
|
-- update with a single clustering key inside IN predicate
|
|
-- correctly identified as a single-key update
|
|
update lwt set c = 2 where a = 1 and b in (2) if c = 1;
|
|
-- unrestricted conditional update,
|
|
-- Casssandra: code=2200 [Invalid query]
|
|
-- message="Some clustering keys are missing: b"
|
|
update lwt set c=3 where a=1 if c=2;
|
|
-- comparing columns
|
|
update lwt set c=3 where a=1 if c=a;
|
|
-- cleanup
|
|
drop table lwt;
|
|
--
|
|
-- clustering: ends
|
|
--
|
|
-- limitations: check grammar/feature limitations of LWT
|
|
--
|
|
create table lwt (a int, b frozen<map<int, int>>, c int, primary key (a, b));
|
|
insert into lwt (a, b, c) values (1, {1:1, 2:2}, 3);
|
|
-- Strictcly speaking this is not an LWT check, but we need to ensure that
|
|
-- LWT restrictions are a superposition of modification statement
|
|
-- restrictions
|
|
update lwt set c=3 where a=1 and b contains 1 if c=1;
|
|
|
|
drop table lwt;
|
|
|
|
-- conditional updates and counters: not supported
|
|
create table lwt (a int primary key, b counter);
|
|
update lwt set b = b+1 where a = 1;
|
|
update lwt set b = b+1 where a = 1 if exists;
|
|
insert into lwt (a, b) values (1,1) if not exists;
|
|
delete from lwt where a=1 if exists;
|
|
delete from lwt where a=1 if b=2;
|
|
drop table lwt;
|
|
--
|
|
-- limitations: ends
|
|
--
|
|
---
|
|
-- if_exists: check if exists/if not exists works
|
|
---
|
|
create table lwt (a int primary key, b int);
|
|
-- insert if not exists
|
|
insert into lwt (a, b) values (1, 1) if not exists;
|
|
select * from lwt allow filtering;
|
|
-- should not update
|
|
insert into lwt (a, b) values (1, 2) if not exists;
|
|
select * from lwt allow filtering;
|
|
-- should update
|
|
insert into lwt (a, b) values (2, 2) if not exists;
|
|
select * from lwt allow filtering;
|
|
-- should delete
|
|
delete from lwt where a=2 if exists;
|
|
select * from lwt allow filtering;
|
|
-- should not update
|
|
update lwt set b=3 where a=2 if exists;
|
|
select * from lwt allow filtering;
|
|
-- should update
|
|
update lwt set b=3 where a=1 if exists;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
--
|
|
-- if_exists: ends end of check for if-exists/if-not-exists
|
|
--
|
|
-- conditional updates and static columns
|
|
-- see also https://issues.apache.org/jira/browse/CASSANDRA-10532
|
|
create table lwt (id bigint, uuid uuid, stc text static, value text, primary key(id, uuid));
|
|
delete stc FROM lwt WHERE id=1 IF stc='key';
|
|
drop table lwt;
|
|
|
|
create table lwt (p int, c int, s int static, v int, primary key(p, c));
|
|
insert into lwt(p, c, v) values(1, 1, 1);
|
|
select * from lwt;
|
|
-- must fail: regular row conditions do not match
|
|
insert into lwt(p, c, v) values(1, 1, 10) if not exists;
|
|
insert into lwt(p, c, s) values(1, 1, 10) if not exists;
|
|
update lwt set v=10 where p=1 and c=1 if v=2;
|
|
delete from lwt where p=1 and c=1 if v=2;
|
|
select * from lwt;
|
|
-- must succeed: regular row conditions match
|
|
insert into lwt(p, c, v) values(1, 2, 2) if not exists;
|
|
update lwt set v=10 where p=1 and c=1 if v=1;
|
|
update lwt set v=20 where p=1 and c=2 if exists;
|
|
select * from lwt;
|
|
-- must fail: there is no static row in the partition
|
|
update lwt set s=2 where p=1 if exists;
|
|
update lwt set s=2 where p=1 if s=1;
|
|
update lwt set v=2 where p=1 and c=1 if s=1;
|
|
delete s from lwt where p=1 if exists;
|
|
delete s from lwt where p=1 if s=1;
|
|
delete v from lwt where p=1 and c=1 if s=1;
|
|
delete s from lwt where p=1 and c=1 if s=1 and v=10;
|
|
delete v from lwt where p=1 and c=1 if s=1 and v=10;
|
|
delete from lwt where p=1 and c=1 if s=1;
|
|
delete from lwt where p=1 and c=1 if s=1 and v=10;
|
|
select * from lwt;
|
|
-- must succeed: there is no static row in the partition (even though there are regular rows)
|
|
insert into lwt(p, s) values(1, 1) if not exists;
|
|
select * from lwt;
|
|
-- must fail: static row conditions do not match
|
|
insert into lwt(p, s) values(1, 2) if not exists;
|
|
update lwt set s=3 where p=1 if s=2;
|
|
update lwt set v=3 where p=1 and c=1 if s=2;
|
|
delete s from lwt where p=1 if s=2;
|
|
delete v from lwt where p=1 and c=1 if s=2;
|
|
delete s from lwt where p=1 and c=1 if s=2 and v=10;
|
|
delete v from lwt where p=1 and c=1 if s=2 and v=10;
|
|
delete from lwt where p=1 and c=1 if s=2;
|
|
delete from lwt where p=1 and c=1 if s=2 and v=10;
|
|
-- must succeed: regular row conditions match
|
|
delete from lwt where p=1 and c=1 if exists;
|
|
delete from lwt where p=1 and c=2 if v=20;
|
|
select * from lwt;
|
|
-- must succeed: there is no regular rows in the partition (even though there is a static row)
|
|
insert into lwt(p, c, v) values(1, 1, 1) if not exists;
|
|
select * from lwt;
|
|
delete from lwt where p=1 and c=1;
|
|
select * from lwt;
|
|
-- must fail: there is no regular rows in the partition
|
|
update lwt set v=10 where p=1 and c=1 if exists;
|
|
update lwt set v=10 where p=1 and c=1 if s=1 and v=1;
|
|
update lwt set s=10 where p=1 and c=1 if s=1 and v=1;
|
|
delete s from lwt where p=1 and c=1 if s=1 and v=1;
|
|
delete v from lwt where p=1 and c=1 if s=1 and v=1;
|
|
delete from lwt where p=1 and c=1 if s=1 and v=1;
|
|
select * from lwt;
|
|
-- must succeed: the condition applies only to the static row
|
|
delete from lwt where p=1 and c=1 if s=1;
|
|
delete v from lwt where p=1 and c=1 if s=1;
|
|
update lwt set v=10 where p=1 and c=1 if s=1;
|
|
update lwt set s=10 where p=1 if s=1;
|
|
select * from lwt;
|
|
insert into lwt(p, c, s, v) values(2, 2, 20, 20);
|
|
insert into lwt(p, s) values(3, 30);
|
|
insert into lwt(p, s) values(4, 40);
|
|
select * from lwt;
|
|
-- must succeed: static row conditions match
|
|
delete s from lwt where p=1 if exists;
|
|
delete s from lwt where p=2 if s=20;
|
|
delete s from lwt where p=3 if exists;
|
|
delete s from lwt where p=4 if s=40;
|
|
select * from lwt;
|
|
insert into lwt(p, c, v) values(1, 1, 1);
|
|
insert into lwt(p, c, v, s) values(1, 2, 2, 1);
|
|
insert into lwt(p, c, v) values(2, 1, 1);
|
|
insert into lwt(p, c, v) values(2, 2, 2);
|
|
insert into lwt(p, c, v, s) values(2, 3, 3, 1);
|
|
-- must succeed: both static and regular row conditions match
|
|
delete v from lwt where p=1 and c=1 if v=1 and s=1;
|
|
delete s from lwt where p=1 and c=2 if v=2 and s=1;
|
|
delete from lwt where p=2 and c=3 if v=3 and s=1;
|
|
update lwt set v=2 where p=2 and c=1 if v=1 and s=1;
|
|
update lwt set s=2 where p=2 and c=2 if v=2 and s=1;
|
|
select * from lwt;
|
|
-- sanity checks
|
|
update lwt set v=1 where p=1 if exists;
|
|
update lwt set v=1 where p=1 if s=1;
|
|
update lwt set v=1 where p=1 if v=1;
|
|
update lwt set v=1 where p=1 if s=1 and v=1;
|
|
update lwt set s=1 where p=1 if v=1;
|
|
update lwt set s=1 where p=1 if v=1 and s=1;
|
|
update lwt set s=1 where p=1 and c=1 if s=1;
|
|
update lwt set s=1 where p=1 and c=1 if exists;
|
|
delete from lwt where p=1 if exists;
|
|
delete from lwt where p=1 if v=10;
|
|
delete from lwt where p=1 and c>0 if exists;
|
|
delete from lwt where p=1 and c>0 if v=10;
|
|
delete v from lwt where p=1 if exists;
|
|
delete v from lwt where p=1 if v=10;
|
|
delete v from lwt where p=1 and c>0 if exists;
|
|
delete v from lwt where p=1 and c>0 if v=10;
|
|
delete s from lwt where p=1 if v=1;
|
|
delete s from lwt where p=1 if v=1 and s=1;
|
|
delete s from lwt where p=1 and c=1 if s=1;
|
|
delete s from lwt where p=1 and c=1 if exists;
|
|
drop table lwt;
|
|
|
|
-- mixing reading operations and static column conditions in one statement
|
|
create table lwt(p int, c int, s int static, v list<int>, primary key(p, c));
|
|
insert into lwt(p, c) values(1, 1);
|
|
insert into lwt(p, c, v) values(1, 2, [1, 2, 3]);
|
|
insert into lwt(p, c, s) values(1, 3, 1);
|
|
update lwt set v=v-[2] where p=1 and c=2 if s=1;
|
|
select * from lwt;
|
|
drop table lwt;
|
|
|
|
-- todo: conditional updates and views
|
|
|
|
--
|
|
-- collections: conditional updates and sets, maps, lists, frozen
|
|
-- sets, maps and lists
|
|
--
|
|
create table lwt (a int, b int, setint set<int>, setsetint set<frozen<set<int>>>, primary key (a, b));
|
|
insert into test (a, b) values (1,1);
|
|
update lwt set setint={1, 2, 3}, setsetint = {{1,2,3}, {2,3,4}, {5,6,7}} where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set setint=setint+{1, 2, 3}, setsetint = setsetint + {{7,8,9}} where a = 1 and b = 1 if setint = {1, 2, 3};
|
|
select * from lwt allow filtering;
|
|
update lwt set setint=setint-{1, 2, 3}, setsetint = setsetint - {{1,2,3}} where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
|
|
create table lwt (a int, b int, mapint map<int, int>, mapsetint map<int, frozen<set<int>>>, primary key (a, b));
|
|
insert into lwt (a, b) values (1,1);
|
|
update lwt set mapint={1: 1, 2: 1, 3: 1}, mapsetint = {1: {1}, 2: {2}, 3: {3}} where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set mapsetint = mapsetint + {1: {3,4}} where a = 1 and b = 1 if mapint[2] = 1;
|
|
select * from lwt allow filtering;
|
|
update lwt set mapint = mapint + {2: 1} where a = 1 and b = 1 if exists;
|
|
update lwt set mapint = mapint - {2} where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
|
|
create table lwt (a int, b int, listint list<int>, staticsettext set<text> static, primary key(a, b));
|
|
insert into lwt (a, b) values (1,1);
|
|
update lwt set staticsettext = {'a', 'b', 'c'} where a = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set staticsettext = {'d', 'e', 'f'} where a = 2 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set staticsettext = staticsettext + {'d'}, listint = [1, 2, 3] where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set staticsettext = staticsettext + {'e'} where a = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set listint = listint + [5] where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set listint = [-1] + listint, staticsettext = staticsettext - {'e'} where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
|
|
create table lwt (a int, b int, flistint frozen<list<int>>, smapint map<text, int> static, primary key(a, b));
|
|
insert into lwt (a, b) values (1,1);
|
|
update lwt set smapint = {'a': 1, 'b': 1, 'c': 3} where a = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set smapint = smapint + {'d': 3}, flistint = [1] where a = 1 if exists;
|
|
update lwt set smapint = smapint + {'d': 4}, flistint = [1] where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set flistint = [1, 2] where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set flistint = flistint + [3] where a = 1 and b = 1 if exists;
|
|
select * from lwt allow filtering;
|
|
update lwt set flistint = [4] where a = 1 and b = 1 if smapint['a'] = 1;
|
|
update lwt set flistint = [4] where a = 1 and b = 1;
|
|
drop table lwt;
|
|
--
|
|
-- collection: ends
|
|
--
|
|
--
|
|
-- if expr grammar
|
|
--
|
|
create table lwt (a int, c int, listint list<int>, mapint map<int, int>, setint set<int>, primary key(a));
|
|
-- term
|
|
update lwt set c=0 where a = 1 if c;
|
|
-- -- equality
|
|
update lwt set c=0 where a = 1 if c = 1;
|
|
-- inequality: there is na inequality
|
|
update lwt set c=0 where a = 1 if c != 1;
|
|
-- greater
|
|
update lwt set c=0 where a = 1 if c > 1;
|
|
-- less
|
|
update lwt set c=0 where a = 1 if c < 1;
|
|
-- greater equal
|
|
update lwt set c=0 where a = 1 if c >= 1;
|
|
-- less equal
|
|
update lwt set c=0 where a = 1 if c <= 1;
|
|
-- AND prediate
|
|
update lwt set c=0 where a = 1 if c = 1 and c = 1;
|
|
-- OR predicate: Cassandra users do not deserve it :(
|
|
update lwt set c=0 where a = 1 if c = 1 or c = 1;
|
|
-- null
|
|
update lwt set c=0 where a = 1 if c = null;
|
|
-- in
|
|
update lwt set c=0 where a = 1 if c in (1);
|
|
-- in
|
|
update lwt set c=0 where a = 1 if c in (1, 2);
|
|
-- in (null)
|
|
update lwt set c=0 where a = 1 if c in (1, null);
|
|
-- list contains
|
|
update lwt set c=0 where a = 1 if listint contains null;
|
|
-- map contains
|
|
update lwt set c=0 where a = 1 if mapint contains key 1;
|
|
-- set contains
|
|
update lwt set c=0 where a = 1 if setint contains key 1;
|
|
-- multi-value set contaias
|
|
update lwt set c=0 where a = 1 if setint contains key 1;
|
|
-- addressing map element
|
|
update lwt set c=0 where a = 1 if mapint[2] = 3;
|
|
-- addressing map element
|
|
update lwt set c=0 where a = 1 if mapint[2];
|
|
-- comparing map element and null
|
|
update lwt set c=0 where a = 1 if mapint[2] = null;
|
|
-- rinse and repeat with actual data
|
|
insert into lwt (a, c, listint, mapint, setint) values (1, 1, [1], {1: 1}, {1});
|
|
update lwt set c=1 where a = 1 if c = 0;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=2 where a = 1 if c != 1;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=3 where a = 1 if c > 0;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=4 where a = 1 if c < 2;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=5 where a = 1 if c >= 1;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=6 where a = 1 if c <= 5;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=7 where a = 1 if c = 1 and c = 1;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=8 where a = 1 if c = null;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=9 where a = 1 if c in (4);
|
|
select * from lwt allow filtering;
|
|
update lwt set c=10 where a = 1 if c in (4, 5);
|
|
select * from lwt allow filtering;
|
|
update lwt set c=11 where a = 1 if c in (5, null);
|
|
select * from lwt allow filtering;
|
|
update lwt set c=12 where a = 1 if mapint[1] = 1;
|
|
select * from lwt allow filtering;
|
|
update lwt set c=13 where a = 1 if mapint[1] = null;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
--
|
|
-- conditions: null handling
|
|
--
|
|
create table lwt (a int, b int, primary key(a));
|
|
insert into lwt (a) values (1);
|
|
-- null >|<|<=|<= val = false
|
|
update lwt set b = 1 where a = 1 if b > 3;
|
|
update lwt set b = 1 where a = 1 if b >= 3;
|
|
update lwt set b = 1 where a = 1 if b <= 3;
|
|
update lwt set b = 1 where a = 1 if b < 3;
|
|
-- null >|<|<=|<= null = error
|
|
update lwt set b = 1 where a = 1 if b > null;
|
|
update lwt set b = 1 where a = 1 if b >= null;
|
|
update lwt set b = 1 where a = 1 if b < null;
|
|
update lwt set b = 1 where a = 1 if b <= null;
|
|
-- cassandra doesn't allow this (ugh), but test nevertheless
|
|
update lwt set b = 1 where a = 1 if 3 < b;
|
|
update lwt set b = 1 where a = 1 if 3 <= b;
|
|
update lwt set b = 1 where a = 1 if 3 >= b;
|
|
update lwt set b = 1 where a = 1 if 3 > b;
|
|
update lwt set b = 1 where a = 1 if null < b;
|
|
update lwt set b = 1 where a = 1 if null <= b;
|
|
update lwt set b = 1 where a = 1 if null > b;
|
|
update lwt set b = 1 where a = 1 if null >= b;
|
|
update lwt set b = 1 where a = 1 if null = b;
|
|
-- null == null = true
|
|
update lwt set b = 1 where a = 1 if b = null;
|
|
select * from lwt allow filtering;
|
|
-- val != null = true
|
|
update lwt set b = null where a = 1 if b != null;
|
|
select * from lwt allow filtering;
|
|
update lwt set b = null where a = 1;
|
|
select * from lwt allow filtering;
|
|
-- null != null = false
|
|
update lwt set b = 1 where a = 1 if b != null;
|
|
select * from lwt allow filtering;
|
|
-- null != val = true
|
|
update lwt set b = 2 where a = 1 if b != 1;
|
|
select * from lwt allow filtering;
|
|
drop table lwt;
|
|
--
|
|
-- conditions: simple expression
|
|
--
|
|
create table lwt (a int, b boolean, d decimal, f double, i int, s text, t timestamp, primary key(a));
|
|
insert into lwt (a, b, d, f, i, s, t) values (1, true, 1, 1, 1, '', dateof(now()));
|
|
-- boolean test
|
|
update lwt set b = false where a = 1 if b = true;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = true where a = 1 if b = true;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = true where a = 1 if b != true;
|
|
-- for whatever reason these operators are allowed for boolean values,
|
|
-- keep it this way
|
|
update lwt set b = true where a = 1 if b > true;
|
|
update lwt set b = true where a = 1 if b < false;
|
|
update lwt set b = true where a = 1 if b <= false;
|
|
update lwt set b = true where a = 1 if b >= true;
|
|
-- integer test
|
|
update lwt set i = 1 where a = 1 if i = 1;
|
|
update lwt set i = 2 where a = 1 if i != 1;
|
|
update lwt set i = 3 where a = 1 if i < 1;
|
|
update lwt set i = 4 where a = 1 if i > 1;
|
|
update lwt set i = 5 where a = 1 if i >= 1;
|
|
update lwt set i = 6 where a = 1 if i <= 1;
|
|
-- Compare with another column: cassandra doesn't allow anything but a
|
|
-- constant on the right hand side :(
|
|
update lwt set i = 7 where a = 1 if i = t;
|
|
update lwt set i = 7 where a = 1 if i = d;
|
|
update lwt set i = 7 where a = 1 if i = f;
|
|
update lwt set i = 7 where a = 1 if s = i;
|
|
-- float test
|
|
update lwt set f = 2 where a = 1 if f = 1;
|
|
update lwt set f = 3 where a = 1 if f != 1;
|
|
update lwt set f = 4 where a = 1 if f < 1;
|
|
update lwt set f = 5 where a = 1 if f > 1;
|
|
update lwt set f = 6 where a = 1 if f >= 1;
|
|
update lwt set f = 7 where a = 1 if f <= 1;
|
|
-- this is broken, obviously
|
|
update lwt set f = 8 where a = 1 if f != 6.0000000000000001;
|
|
-- decimal
|
|
update lwt set d = 2.1 where a = 1 if d = 1;
|
|
update lwt set d = 3.2 where a = 1 if d != 2.09999999999999999999999999999999999;
|
|
update lwt set d = 4.3 where a = 1 if d < 3.2;
|
|
update lwt set d = 5.4 where a = 1 if d > 3.2;
|
|
update lwt set d = 6.5 where a = 1 if d >= 3.2;
|
|
update lwt set d = 7.6 where a = 1 if d <= 6.5;
|
|
-- text
|
|
update lwt set s = 'i' where a = 1 if s = '';
|
|
-- update lwt set s = 'щ' where a = 1 if s != 'j';
|
|
-- wheeled charriot and left-to-right
|
|
-- update lwt set s = ' 𐃌 ' where a = 1 if s < 'я';
|
|
-- update lwt set s = 'שׁלום' where a = 1 if s >= '1';
|
|
select s from lwt where a = 1;
|
|
-- timestamp test
|
|
-- update lwt set t = dateof(now()) where a = 1 if t <= dateof(now());
|
|
drop table lwt;
|
|
-- map keys
|
|
create table lwt (a int, b map<boolean, boolean>, c list<boolean>, s set<boolean>, primary key (a));
|
|
insert into lwt (a, b) values (1, {true: true});
|
|
update lwt set b = {true:false} where a = 1 if b[true] = true;
|
|
update lwt set b = {true:false} where a = 1 if b[true] = null;
|
|
update lwt set b = {false:true} where a = 1 if b[false] = null;
|
|
update lwt set b = {false:false} where a = 1 if b[true] != null;
|
|
update lwt set b[true] = false where a = 1 if b[true] != 1;
|
|
update lwt set b[false] = null where a = 1 if b[true] > null;
|
|
update lwt set b[false] = true where a = 1 if b[1] != true;
|
|
update lwt set b = {true:false, true:true, false:false} where a = 1 if b[null] = null;
|
|
update lwt set b = {false:false} where a = 1 if b[null] = null;
|
|
update lwt set b = b + {false:true} where a = 1 if b[null] = true;
|
|
update lwt set b[null] = false where a = 1 if b[true] != true;
|
|
update lwt set b[null] = false where a = 1 if b[true] = true;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = {false:true} where a = 1 if b = {true:false};
|
|
update lwt set b = {} where a = 1 if exists;
|
|
update lwt set b = {} where a = 1 if b = {};
|
|
select b from lwt where a = 1;
|
|
update lwt set b = {} where a = 1 if b = null;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = null where a = 1 if b = {};
|
|
select b from lwt where a = 1;
|
|
-- lists
|
|
-- null value == empty list. see CASSANDRA-7155
|
|
update lwt set c = [false] where a = 1 if c = [];
|
|
-- c is null, really, so this should work
|
|
update lwt set c = [] where a = 1 if c = null;
|
|
-- check with empty list
|
|
update lwt set c = [false] where a = 1 if c = [];
|
|
update lwt set c = [false] where a = 1 if c = [false];
|
|
update lwt set c = [false] where a = 1 if c = [null];
|
|
update lwt set c = [false] where a = 1 if c = [null, true];
|
|
select c from lwt where a = 1;
|
|
update lwt set c = [true, false] where a = 1 if c[0] = false;
|
|
update lwt set c = c + [true] where a = 1 if c[1] = false;
|
|
update lwt set c = c + [false] where a = 1 if c[2] = null;
|
|
update lwt set c = c + [true] where a = 1 if c[3] = null;
|
|
update lwt set c = c + [true] where a = 1 if c[100000000] = null;
|
|
update lwt set c = c + [false] where a = 1 if c[100000000] = true;
|
|
update lwt set c = c + [true, false] where a = 1 if c[null] = false;
|
|
update lwt set c = [false] where a = 1 if c[null] = null;
|
|
update lwt set c = [true, false, true, true, true] where a = 1 if c[null] = null;
|
|
update lwt set c = c + [false] where a = 1 if c[null] = 1;
|
|
update lwt set c = [true] + c where a = 1 if c = [false];
|
|
select c from lwt where a = 1;
|
|
-- sets
|
|
-- null value == empty set
|
|
update lwt set s = {} where a = 1 if s = {};
|
|
update lwt set s = {} where a = 1 if s = null;
|
|
update lwt set s = {false} where a = 1 if s = null;
|
|
update lwt set s = {true, false} where a = 1 if s = {false};
|
|
update lwt set s = {false} where a = 1 if s = {null};
|
|
update lwt set s = {false} where a = 1 if s = {null, true};
|
|
update lwt set s = {true, false} where a = 1 if s{true} = false;
|
|
update lwt set s = {false} where a = 1 if s = 1;
|
|
update lwt set s = {true} + s where a = 1 if s = {false};
|
|
-- sets
|
|
-- null value == empty set
|
|
update lwt set s = {} where a = 1 if s = {};
|
|
update lwt set s = {} where a = 1 if s = null;
|
|
update lwt set s = {false} where a = 1 if s = null;
|
|
update lwt set s = {true, false} where a = 1 if s = {false};
|
|
update lwt set s = {false} where a = 1 if s = {null};
|
|
update lwt set s = {false} where a = 1 if s = {null, true};
|
|
update lwt set s = {true, false} where a = 1 if s{true} = false;
|
|
update lwt set s = {false} where a = 1 if s = 1;
|
|
update lwt set s = {true} + s where a = 1 if s = {false};
|
|
drop table lwt;
|
|
--
|
|
-- frozen collections
|
|
--
|
|
create table lwt (a int, b frozen<map<boolean, boolean>>, c frozen<list<boolean>>, s frozen<set<boolean>>, primary key (a));
|
|
insert into lwt (a, b) values (1, {true: true});
|
|
update lwt set b = {true:false} where a = 1 if b[true] = true;
|
|
update lwt set b = {true:false} where a = 1 if b[true] = null;
|
|
update lwt set b = {false:true} where a = 1 if b[false] = null;
|
|
update lwt set b = {false:false} where a = 1 if b[true] != null;
|
|
update lwt set b[true] = false where a = 1 if b[true] != true;
|
|
update lwt set b = b + {false:true} where a = 1 if b[true] = true;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = {false:true} where a = 1 if b = {true:false};
|
|
update lwt set b = {} where a = 1 if exists;
|
|
update lwt set b = {} where a = 1 if b = {};
|
|
select b from lwt where a = 1;
|
|
update lwt set b = {} where a = 1 if b = null;
|
|
select b from lwt where a = 1;
|
|
update lwt set b = null where a = 1 if b = {};
|
|
select b from lwt where a = 1;
|
|
-- lists
|
|
-- null value != empty list for a frozen list
|
|
update lwt set c = [false] where a = 1 if c = [];
|
|
-- c is null, really, so this should work
|
|
update lwt set c = [] where a = 1 if c = null;
|
|
-- check with empty list
|
|
update lwt set c = [false] where a = 1 if c = [];
|
|
update lwt set c = [false] where a = 1 if c = [false];
|
|
update lwt set c = [false] where a = 1 if c = [null];
|
|
update lwt set c = [false] where a = 1 if c = [null, true];
|
|
-- lists with NULL are allowed, but cannot be stored
|
|
update lwt set c = [false, null] where a = 1 if c = [false];
|
|
select c from lwt where a = 1;
|
|
-- negative list index
|
|
update lwt set c = [true, false] where a = 1 if c[-1] = false;
|
|
update lwt set c = [true, false] where a = 1 if c[-1.5] = false;
|
|
update lwt set c = [true, false] where a = 1 if c[0] = false;
|
|
update lwt set c = [true] where a = 1 if c[1] = false;
|
|
update lwt set c = [true, false] where a = 1 if c[2] = null;
|
|
update lwt set c = [true] where a = 1 if c[100000000] = null;
|
|
update lwt set c = [false] where a = 1 if c[null] = null;
|
|
update lwt set c = [true] where a = 1 if c[null] = null;
|
|
update lwt set c = [true] + c where a = 1 if c = [false];
|
|
update lwt set c = [true,true,true,false,false,false] where a = 1 if exists;
|
|
update lwt set c = null where a = 1 if c[1] = true and c[5] = false and c[7] = null;
|
|
select c from lwt where a = 1;
|
|
-- sets
|
|
-- null value == empty set
|
|
update lwt set s = {} where a = 1 if s = {};
|
|
update lwt set s = {} where a = 1 if s = null;
|
|
update lwt set s = {false} where a = 1 if s = null;
|
|
update lwt set s = {true, false} where a = 1 if s = {false};
|
|
update lwt set s = {false} where a = 1 if s = {null};
|
|
update lwt set s = {false} where a = 1 if s = {null, true};
|
|
update lwt set s = {true, false} where a = 1 if s{true} = false;
|
|
update lwt set s = s + {true} where a = 1 if s = {true, false};
|
|
update lwt set s = {true, true} where a = 1 if exists;
|
|
-- check non frozen sets are compared correctly to set literals
|
|
update lwt set s = {false, false} where a = 1 if s = {true, true};
|
|
update lwt set s = {false, true} where a = 1 if s = {true, false};
|
|
select s from lwt where a = 1;
|
|
drop table lwt;
|
|
-- gt/le on composite collections
|
|
create table lwt (a int, b map<int, int>, c list<int>, s set<int>, primary key (a));
|
|
insert into lwt (a, b, c, s) values (1, {}, [], {});
|
|
-- maps
|
|
update lwt set b = {1:1} where a = 1 if b > {};
|
|
update lwt set b = {1:1} where a = 1 if b < {};
|
|
update lwt set b = {1:1} where a = 1 if b = {};
|
|
update lwt set b = {2:2} where a = 1 if b > {1:1};
|
|
update lwt set b = {2:2} where a = 1 if b >= {1:1};
|
|
update lwt set b = {3:3} where a = 1 if b > {1:1};
|
|
update lwt set b = {4:4} where a = 1 if b >= {2:3};
|
|
update lwt set b = {4:4} where a = 1 if b >= {3:3};
|
|
update lwt set b = {1:1} where a = 1 if b < {4:4};
|
|
update lwt set b = {1:1} where a = 1 if b <= {4:4};
|
|
select b from lwt where a= 1;
|
|
-- lists
|
|
update lwt set c = [1,1] where a = 1 if c > [];
|
|
update lwt set c = [1,1] where a = 1 if c < [];
|
|
update lwt set c = [1,1] where a = 1 if c = [];
|
|
update lwt set c = [2,2] where a = 1 if c > [1,1];
|
|
update lwt set c = [2,2] where a = 1 if c >= [1,1];
|
|
update lwt set c = [3,3] where a = 1 if c > [1,1];
|
|
update lwt set c = [4,4] where a = 1 if c >= [2,3];
|
|
update lwt set c = [4,4] where a = 1 if c >= [3,3];
|
|
update lwt set c = [1,1] where a = 1 if c < [4,4];
|
|
update lwt set c = [1,1] where a = 1 if c <= [4,4];
|
|
select c from lwt where a= 1;
|
|
-- sets
|
|
update lwt set s = {1} where a = 1 if s > {};
|
|
update lwt set s = {1} where a = 1 if s < {};
|
|
update lwt set s = {1} where a = 1 if s = {};
|
|
update lwt set s = {2} where a = 1 if s > {1};
|
|
update lwt set s = {2} where a = 1 if s >= {1};
|
|
update lwt set s = {3} where a = 1 if s > {1};
|
|
update lwt set s = {4} where a = 1 if s >= {3};
|
|
update lwt set s = {1} where a = 1 if s < {4};
|
|
update lwt set s = {1} where a = 1 if s <= {4};
|
|
select s from lwt where a= 1;
|
|
drop table lwt;
|
|
-- collections of collections
|
|
-- non-frozen nested sets are not supported
|
|
create table lwt (a int, b set<map<int,int>>, c list<set<int>>, primary key (a));
|
|
-- frozen collection elements are however ok
|
|
create table lwt (a int, b set<frozen<list<int>>>, c list<frozen<set<int>>>, primary key (a));
|
|
insert into lwt (a, b, c) values (1, {[1,2], [1,2]}, [{1,2}, {1,2}]);
|
|
-- sets
|
|
update lwt set b={[3,3], [4,4]} where a = 1 if b = {[1,2], [1,2]};
|
|
update lwt set b={[5,5,5], [4,4,4]} where a = 1 if b > {[3,3], [4,4]};
|
|
update lwt set b={[5,5,5], [4,4,4]} where a = 1 if b >= {[3,3], [4,4]};
|
|
select b from lwt where a = 1;
|
|
-- lists
|
|
update lwt set c=[{3,4}, {4,5}] where a = 1 if c = [{1,2}, {1,2}];
|
|
update lwt set c=[{3,4,5}, {4,5,6}] where a = 1 if c > [{3,3}, {4,4}];
|
|
update lwt set c=[{5,6,7}, {7,8,9}] where a = 1 if c >= [{3,3}, {5,4}];
|
|
update lwt set c=[{5,6,7}, {7,8,9}] where a = 1 if c >= [{3,4}, {4,5}];
|
|
select c from lwt where a = 1;
|
|
drop table lwt;
|
|
-- in predicate, static columns
|
|
create table lwt (a int, b int, c list<int>, d set<int> static, primary key (a, b));
|
|
insert into lwt (a, b, c, d) values (1,1,[1],{1});
|
|
update lwt set d = {2,3,4,5,-1} where a = 1 if d in ({1});
|
|
update lwt set d = {2} where a = 1 if d in ({2,3,4,5});
|
|
update lwt set d = {2} where a = 1 if d in ({2,3,4,5,-1});
|
|
update lwt set c = [2,3,4,5,-1] where a = 1 and b = 1 if c in ([1]);
|
|
update lwt set c = [2] where a = 1 and b = 1 if c in ([2,3,4,5]);
|
|
-- check null handling in IN condition
|
|
update lwt set c = [] where a = 1 and b = 1 if c in ([2,3,4,5,-1]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in ([]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in ([]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in (null);
|
|
update lwt set c = [1] where a = 1 and b = 1 if c in ();
|
|
update lwt set c = [2] where a = 1 and b = 1 if c in ([1], null, [2]);
|
|
delete from lwt where a = 1;
|
|
update lwt set d = {2} where a = 1 if d in ();
|
|
update lwt set d = {2} where a = 1 if d in (null);
|
|
update lwt set d = {} where a = 1 if d in ({}, {2});
|
|
update lwt set d = {} where a = 1 if d in (null, {2});
|
|
update lwt set d = null where a = 1 if d in (null, {2});
|
|
update lwt set d = null where a = 1 if d in (null, {2}, {3,4}, {5});
|
|
drop table lwt;
|
|
|
|
-- in predicate, frozen sets/lists columns
|
|
create table lwt (a int, b int, c frozen<list<int>>, d frozen<set<int>> static, primary key (a, b));
|
|
insert into lwt (a, b, c, d) values (1,1,[1],{1});
|
|
update lwt set d = {2,3,4,5,-1} where a = 1 if d in ({1});
|
|
update lwt set d = {2} where a = 1 if d in ({2,3,4,5});
|
|
update lwt set d = {2} where a = 1 if d in ({2,3,4,5,-1});
|
|
update lwt set c = [2,3,4,5,-1] where a = 1 and b = 1 if c in ([1]);
|
|
update lwt set c = [2] where a = 1 and b = 1 if c in ([2,3,4,5]);
|
|
-- check null handling in IN condition
|
|
update lwt set c = [] where a = 1 and b = 1 if c in ([2,3,4,5,-1]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in ([]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in ([]);
|
|
update lwt set c = null where a = 1 and b = 1 if c in (null);
|
|
update lwt set c = [1] where a = 1 and b = 1 if c in ();
|
|
update lwt set c = [2] where a = 1 and b = 1 if c in ([1], null, [2]);
|
|
delete from lwt where a = 1;
|
|
update lwt set d = {2} where a = 1 if d in ();
|
|
update lwt set d = {2} where a = 1 if d in (null);
|
|
update lwt set d = {} where a = 1 if d in ({}, {2});
|
|
update lwt set d = {} where a = 1 if d in (null, {2});
|
|
update lwt set d = null where a = 1 if d in (null, {2});
|
|
update lwt set d = null where a = 1 if d in (null, {2}, {3,4}, {5});
|
|
drop table lwt;
|
|
-- LWT and slices; a slice is a Cassandra term for a clustering key range,
|
|
-- usually within the same partition key
|
|
create table lwt (a int, b int, c int, d int, primary key (a, b, c));
|
|
insert into lwt (a, b, c, d) values (1,1,1,1);
|
|
insert into lwt (a, b, c, d) values (1,1,2,0);
|
|
insert into lwt (a, b, c, d) values (1,1,3,1);
|
|
insert into lwt (a, b, c, d) values (1,2,1,1);
|
|
insert into lwt (a, b, c, d) values (1,2,2,0);
|
|
insert into lwt (a, b, c, d) values (1,2,3,1);
|
|
insert into lwt (a, b, c, d) values (1,3,1,1);
|
|
insert into lwt (a, b, c, d) values (1,3,2,0);
|
|
insert into lwt (a, b, c, d) values (1,3,3,1);
|
|
-- update
|
|
select d from lwt where a = 1 and b = 1;
|
|
update lwt set d = 7 where a = 1 and b = 1 if d = 0;
|
|
select d from lwt where a = 1 and b = 1;
|
|
select d from lwt where a = 1 and b = 2;
|
|
update lwt set d = 7 where a = 1 and b = 2;
|
|
-- delete
|
|
select a, b, c, d from lwt where a = 1 and b = 1;
|
|
delete from lwt where a = 1 and b = 1 if d = 0;
|
|
select a, b, c, d from lwt where a = 1 and b = 1;
|
|
select a, b, c, d from lwt where a = 1 and b = 2;
|
|
delete from lwt where a = 1 and b = 2 if d = 1;
|
|
select a, b, c, d from lwt where a = 1 and b = 2;
|
|
select a, b, c, d from lwt where a = 1 and b = 3;
|
|
delete from lwt where a = 1 and b = 3;
|
|
select a, b, c, d from lwt where a = 1 and b = 3;
|
|
drop table lwt;
|
|
-- ok to delete if d is static
|
|
create table lwt (a int, b int, c int, d int static, primary key (a, b, c));
|
|
insert into lwt (a, b, c, d) values (1,1,1,1);
|
|
insert into lwt (a, b, c, d) values (1,1,2,1);
|
|
insert into lwt (a, b, c, d) values (1,1,3,1);
|
|
select a, b, c, d from lwt where a = 1 and b = 1;
|
|
-- try to delete all - fail
|
|
delete from lwt where a = 1 and b = 1 if d = 0;
|
|
-- ok to delete static row
|
|
delete d from lwt where a = 1 if d = 0;
|
|
select a, b, c, d from lwt where a = 1;
|
|
delete d from lwt where a = 1 if d = 1;
|
|
select a, b, c, d from lwt where a = 1;
|
|
drop table lwt;
|
|
--
|
|
-- lists, sets, maps as partition or primary keys
|
|
--
|
|
-- This is covered with tests since with collections_as_maps
|
|
-- flags, sets/ints in primary key will make it difficult
|
|
-- to find the matching cells
|
|
--
|
|
-- invalid collection type
|
|
create table lwt (a int, b int, c set<int>, primary key (a, b, c));
|
|
-- ok - frozen collections are allowed
|
|
create table lwt (a int, b int, c frozen<list<int>>, primary key (a, b, c));
|
|
drop table lwt;
|
|
-- invalid collection type
|
|
create table lwt (a list<int>, b int, c int, primary key (a, b, c));
|
|
--
|
|
-- test empty primary key range: it is impossible to construct due to
|
|
-- limitations of the query language
|
|
--
|
|
create table lwt (a int, b int, c int, primary key (a, b));
|
|
-- error: can't use token and non-token restrictions
|
|
update lwt set c = 1 where a = 1 and b = 1 and token(a) > 0 and token(a) < 0 if c = 1;
|
|
-- error: can't use eq and non-eq restriction
|
|
update lwt set c = 1 where a = 1 and b = 1 and a < 0 if c = 1;
|
|
update lwt set c = 1 where a = 1 and b = 1 and a < 0 if c = 1;
|
|
-- error: partition key must be fully restricted
|
|
update lwt set c = 1 where a > 0 and a < 0 and b = 1 if c = 1;
|
|
-- error: partition key and IN is not supported
|
|
update lwt set c = 1 where a in () and b = 1 if c = 1;
|
|
update lwt set c = 1 where a = 1 and b IN (1, 2) if c = 1;
|
|
update lwt set c = 1 where a = 1 and (b) IN ((1), (2)) if c = 1;
|
|
drop table lwt;
|
|
|
|
-- gh-10081 Conditional UPDATE fails to apply when IF includes both static
|
|
-- and non-static columns.
|
|
create table t (p int, c int, r int, s int static, primary key(p, c));
|
|
insert into t (p, s) values (1, 1);
|
|
update t set s=2, r=1 where p=1 and c=1 if s=1 and r=null;
|
|
-- in case of LWT batch, each statement in the batch should
|
|
-- use correct regular or static row to evaluate the condition.
|
|
-- Specifically, the following batch must apply because
|
|
-- condition of statement 1 is true for regular row (1,1),
|
|
-- and condition of statements 2 and 3 are true for regular rows (1,2)
|
|
-- (1,3), which do not exist in the table, but are incarnated
|
|
-- to check the condition
|
|
begin batch
|
|
update t set s=3, r=10 where p=1 and c=1 if s=2 and r=1
|
|
update t set s=4, r=2 where p=1 and c=2 if s=2 and r=null
|
|
update t set s=5, r=3 where p=1 and c=3 if s=2 and r=null
|
|
apply batch;
|
|
-- test select behaviour for static rows
|
|
-- the table contains 3 rows, right?
|
|
select count(*) from t;
|
|
-- count(*) returns 3 rows. So let's delete 3 rows
|
|
delete from t where p=1 and c = 1 if exists;
|
|
delete from t where p=1 and c = 2 if exists;
|
|
delete from t where p=1 and c = 3 if exists;
|
|
-- WAT, count(*) returns 1 row even though
|
|
-- there were 3 rows and we deleted 3 rows!
|
|
select count(*) from t;
|
|
-- there is no DELETE statement syntax to
|
|
-- delete a static row; yep, that's right,
|
|
-- the row is there but you can't DELETE it!
|
|
delete from t where p=1 if exists;
|
|
-- let's kill the sneaky static row now
|
|
update t set s = null where p=1 if exists;
|
|
-- no more sneaky static rows.
|
|
select count(*) from t;
|
|
-- let's add a row back!
|
|
insert into t (p, s) values (1,1) if not exists;
|
|
-- that's right, there is a row now; what row though?
|
|
select count(*) from t;
|
|
-- let's add more rows
|
|
insert into t (p, c, s) values (1,1,1) if not exists;
|
|
-- WAT, we did not add more rows?
|
|
select count(*) from t;
|
|
-- WAT, you can't add a static row by inserting a partition
|
|
-- key alone, but you can add a clustering row by inserting
|
|
-- clustering key alone
|
|
insert into t (p, c) values (1,2) if not exists;
|
|
select count(*) from t;
|
|
-- stop this travesty
|
|
drop table t;
|
|
|
|
-- cleanup
|
|
DROP KEYSPACE ks;
|
|
|