> CREATE KEYSPACE ks > WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 1} AND > tablets = {'enabled': false}; OK > USE ks; OK > > create table lwt (a int, b int, primary key (a,b)); OK > -- basic batch: ok > begin batch > insert into lwt (a, b) values (1, 1) if not exists > apply batch; +-------------+------+------+ | [applied] | a | b | |-------------+------+------| | True | null | null | +-------------+------+------+ > -- begin unlogged batch + lwt: ok; unlogged is ignored > -- (same in c*) > begin unlogged batch > insert into lwt (a, b) values (1, 1) if not exists > apply batch; +-------------+-----+-----+ | [applied] | a | b | |-------------+-----+-----| | False | 1 | 1 | +-------------+-----+-----+ > -- begin counter batch + lwt: error > begin counter batch > insert into lwt (a, b) values (1, 1) if not exists > apply batch; Error from server: code=2200 [Invalid query] message="Cannot include non-counter statement in a counter batch" > -- a batch affecting two partitions: error > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into lwt (a, b) values (2, 1) if not exists > apply batch; Error from server: code=2200 [Invalid query] message="BATCH with conditions cannot span multiple partitions" > -- a batch with an LWT statement and other statement affecting another > -- partition: error > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into lwt (a, b) values (2, 1) > apply batch; Error from server: code=2200 [Invalid query] message="BATCH with conditions cannot span multiple partitions" > -- a batch affecting different clustering keys of the same partition: ok > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into lwt (a, b) values (1, 2) if not exists > apply batch; +-------------+------+------+ | [applied] | a | b | |-------------+------+------| | False | 1 | 1 | | False | null | null | +-------------+------+------+ > -- a batch and non-batch statement, on the same partition: ok > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into lwt (a, b) values (1, 2) > apply batch; +-------------+------+------+ | [applied] | a | b | |-------------+------+------| | False | 1 | 1 | | False | null | null | +-------------+------+------+ > -- a batch affecting two tables: error > create table two (a int primary key, b int); OK > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into two (a, b) values (1, 1) > apply batch; Error from server: code=2200 [Invalid query] message="BATCH with conditions cannot span multiple tables" > drop table two; OK > -- a batch with custom timestamp set: error > begin batch using timestamp 1 > insert into lwt (a, b) values (1, 1) if not exists > apply batch; Error from server: code=2200 [Invalid query] message="Cannot provide custom timestamp for conditional BATCH" > -- a batch with a statement with a custom timestamp: error > begin batch > insert into lwt (a, b) values (1, 1) if not exists using timestamp 1 > apply batch; Error from server: code=2200 [Invalid query] message="Cannot provide custom timestamp for conditional updates" > -- a batch with an LWT statement and another statement with a custom timestamp set: ok > begin batch > insert into lwt (a, b) values (1, 1) if not exists > insert into lwt (a, b) values (1, 1) using timestamp 1 > apply batch; +-------------+-----+-----+ | [applied] | a | b | |-------------+-----+-----| | False | 1 | 1 | | False | 1 | 1 | +-------------+-----+-----+ > -- a batch with if condition and counters: error > -- if a table has a counter, all its non-primary key columns > -- must be counters > create table lcounter (a int primary key, c counter); OK > begin batch > update lcounter set c = c + 1 where a = 1 if c = null > apply batch; Error from server: code=2200 [Invalid query] message="Conditional updates are not supported on counter tables" > drop table lcounter; OK > drop table lwt; OK > create table lwt (a int, b int, c int, primary key (a,b)); OK > -- a batch with IF condition on primary key: error > begin batch > update lwt set c = 2 where a = 1 and b = 1 if a > 0 > apply batch; Error from server: code=2200 [Invalid query] message="PRIMARY KEY column 'a' cannot have IF conditions" > begin batch > update lwt set c = 2 where a = 1 and b = 1 if b > 0 > apply batch; Error from server: code=2200 [Invalid query] message="PRIMARY KEY column 'b' cannot have IF conditions" > begin batch > update lwt set c = 2 where a = 1 if c = null > apply batch; Error from server: code=2200 [Invalid query] message="Missing mandatory PRIMARY KEY part b" > -- a batch with a statement that has IN prediacte and IF condition: error > begin batch > update lwt set c = 2 where a = 1 and b in (1, 2) if c = null > apply batch; Error from server: code=2200 [Invalid query] message="IN on the clustering key columns is not supported with conditional updates" > -- a batch with a statement that has IN prediacte and *another* statement > -- with IF condition: OK. This is an error in C* but I see no reason > -- why it should be an error in Scylla. > delete from lwt where a = 1; OK > begin batch > update lwt set c = 2 where a = 1 and b = 1 if c = null > update lwt set c = 2 where a = 1 and b in (1, 2) > apply batch; +-------------+------+------+------+ | [applied] | a | b | c | |-------------+------+------+------| | True | null | null | null | | True | null | null | null | +-------------+------+------+------+ > select a, b, c from lwt where a = 1 and b in (1, 2); +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 1 | 1 | 2 | | 1 | 2 | 2 | +-----+-----+-----+ > drop table lwt; OK > -- > -- BATCH LWT with multiple statements on LIST data type, > -- append/discard operations on the type: all append/discard > -- operations from all stsatemetns of the batch are applied > -- > create table lwt (a int, b int, c list, d list static, primary key (a, b)); OK > begin batch > insert into lwt (a, b, c, d ) values (1, 1, ['1'], ['1']) if not exists > insert into lwt (a, b, c, d ) values (1, 2, ['2'], ['2']) if not exists > apply batch; +-------------+------+------+------+------+ | [applied] | a | b | d | c | |-------------+------+------+------+------| | True | null | null | null | null | | True | null | null | null | null | +-------------+------+------+------+------+ > select b, c, d from lwt where a = 1; +-----+-------+------------+ | b | c | d | |-----+-------+------------| | 1 | ['1'] | ['1', '2'] | | 2 | ['2'] | ['1', '2'] | +-----+-------+------------+ > begin batch > update lwt set c = c + ['3'], d = d + ['3'] where a = 1 and b = 1 if exists > update lwt set c = c + ['4'], d = d + ['4'] where a = 1 and b = 2 > apply batch; +-------------+-----+------+------------+-------+ | [applied] | a | b | d | c | |-------------+-----+------+------------+-------| | True | 1 | 1 | ['1', '2'] | ['1'] | | True | 1 | null | ['1', '2'] | null | +-------------+-----+------+------------+-------+ > select b, c, d from lwt where a = 1; +-----+------------+----------------------+ | b | c | d | |-----+------------+----------------------| | 1 | ['1', '3'] | ['1', '2', '3', '4'] | | 2 | ['2', '4'] | ['1', '2', '3', '4'] | +-----+------------+----------------------+ > begin batch > update lwt set c = c + ['5'], d = d + ['5'] where a = 1 and b = 1 if c[0] = '1' and c[1] = '3' > update lwt set c = c + ['6'], d = d + ['6'] where a = 1 and b = 2 > apply batch; +-------------+-----+------+------------+ | [applied] | a | b | c | |-------------+-----+------+------------| | True | 1 | 1 | ['1', '3'] | | True | 1 | null | null | +-------------+-----+------+------------+ > select b, c, d from lwt where a = 1; +-----+-----------------+--------------------------------+ | b | c | d | |-----+-----------------+--------------------------------| | 1 | ['1', '3', '5'] | ['1', '2', '3', '4', '5', '6'] | | 2 | ['2', '4', '6'] | ['1', '2', '3', '4', '5', '6'] | +-----+-----------------+--------------------------------+ > -- multiple conditions: > -- two simple conditions, effects of all statements are applied atomically > -- or not applied at all: ok > begin batch > update lwt set c = c + ['7'], d = d + ['7'] where a = 1 and b = 1 if c[0] = '1' and c[1] = '2' > update lwt set c = c + ['8'], d = d + ['8'] where a = 1 and b = 2 if c[3] = '3' and c[4] = '4' > apply batch; +-------------+-----+-----+-----------------+ | [applied] | a | b | c | |-------------+-----+-----+-----------------| | False | 1 | 1 | ['1', '3', '5'] | | False | 1 | 2 | ['2', '4', '6'] | +-------------+-----+-----+-----------------+ > select b, c, d from lwt where a = 1; +-----+-----------------+--------------------------------+ | b | c | d | |-----+-----------------+--------------------------------| | 1 | ['1', '3', '5'] | ['1', '2', '3', '4', '5', '6'] | | 2 | ['2', '4', '6'] | ['1', '2', '3', '4', '5', '6'] | +-----+-----------------+--------------------------------+ > begin batch > update lwt set c = c + ['7'], d = d + ['7'] where a = 1 and b = 1 if c[0] = '1' and c[1] = '3' > update lwt set c = c + ['8'], d = d + ['8'] where a = 1 and b = 2 if c[1] = '4' and c[2] = '6' > apply batch; +-------------+-----+-----+-----------------+ | [applied] | a | b | c | |-------------+-----+-----+-----------------| | True | 1 | 1 | ['1', '3', '5'] | | True | 1 | 2 | ['2', '4', '6'] | +-------------+-----+-----+-----------------+ > select b, c, d from lwt where a = 1; +-----+----------------------+------------------------------------------+ | b | c | d | |-----+----------------------+------------------------------------------| | 1 | ['1', '3', '5', '7'] | ['1', '2', '3', '4', '5', '6', '7', '8'] | | 2 | ['2', '4', '6', '8'] | ['1', '2', '3', '4', '5', '6', '7', '8'] | +-----+----------------------+------------------------------------------+ > drop table lwt; OK > > -- batch + lwt + range UPDATE of multiple rows, clustering key is not fully restricted: ok > -- batch + lwt + range DELETE of multiple rows, clustering key is not fully restricted: ok > -- usually within the same partition key > create table lwt (a int, b int, c int, d int, primary key (a, b, c)); OK > insert into lwt (a, b, c, d) values (1,1,1,1); OK > insert into lwt (a, b, c, d) values (1,1,2,0); OK > insert into lwt (a, b, c, d) values (1,1,3,1); OK > insert into lwt (a, b, c, d) values (1,2,1,1); OK > insert into lwt (a, b, c, d) values (1,2,2,0); OK > insert into lwt (a, b, c, d) values (1,2,3,1); OK > -- update > begin batch > update lwt set d = 7 where a = 1 and b = 1 and c = 1 if exists > update lwt set d = 7 where a = 1 and b in (1,2) and c in (1,2,3) > apply batch; +-------------+-----+-----+-----+-----+ | [applied] | a | b | c | d | |-------------+-----+-----+-----+-----| | True | 1 | 1 | 1 | 1 | | True | 1 | 1 | 1 | 1 | +-------------+-----+-----+-----+-----+ > select a, b, c, d from lwt where a = 1; +-----+-----+-----+-----+ | a | b | c | d | |-----+-----+-----+-----| | 1 | 1 | 1 | 7 | | 1 | 1 | 2 | 7 | | 1 | 1 | 3 | 7 | | 1 | 2 | 1 | 7 | | 1 | 2 | 2 | 7 | | 1 | 2 | 3 | 7 | +-----+-----+-----+-----+ > -- delete > begin batch > delete from lwt where a = 1 and b = 1 if exists > delete from lwt where a = 1 and b = 2 if exists > apply batch; Error from server: code=2200 [Invalid query] message="DELETE statements must restrict all PRIMARY KEY columns with equality relations in order to delete non static columns" > -- select a, b, c, d from lwt where a = 1; > drop table lwt; OK > > -- Rows fetched for statements that require read but has no conditions > -- must not be included into the result set. > create table lwt(p int, c int, i int, l list, primary key(p, c)); OK > insert into lwt(p, c, i, l) values(1, 1, 1, [1, 2]); OK > insert into lwt(p, c, i, l) values(1, 2, 2, [1, 2, 3, 4]); OK > -- doesn't apply > begin batch > update lwt set i = 3 where p = 1 and c = 1 if i = 2 > update lwt set l = l - [1] where p = 1 and c = 2 > apply batch; +-------------+-----+-----+-----+ | [applied] | p | c | i | |-------------+-----+-----+-----| | False | 1 | 1 | 1 | | False | 1 | 2 | 2 | +-------------+-----+-----+-----+ > -- applies > begin batch > update lwt set i = 2 where p = 1 and c = 1 if i = 1 > update lwt set l = l - [2] where p = 1 and c = 2 > apply batch; +-------------+-----+-----+-----+ | [applied] | p | c | i | |-------------+-----+-----+-----| | True | 1 | 1 | 1 | | True | 1 | 2 | 2 | +-------------+-----+-----+-----+ > -- doesn't apply > begin batch > delete l from lwt where p = 1 and c = 3 if exists > update lwt set l = l - [3] where p = 1 and c = 2 > apply batch; +-------------+------+------+------+-----------+ | [applied] | p | c | i | l | |-------------+------+------+------+-----------| | False | null | null | null | null | | False | 1 | 2 | 2 | [1, 3, 4] | +-------------+------+------+------+-----------+ > -- applies > begin batch > delete l from lwt where p = 1 and c = 1 if exists > update lwt set l = l - [4] where p = 1 and c = 2 > apply batch; +-------------+-----+-----+-----+-----------+ | [applied] | p | c | i | l | |-------------+-----+-----+-----+-----------| | True | 1 | 1 | 2 | [1, 2] | | True | 1 | 2 | 2 | [1, 3, 4] | +-------------+-----+-----+-----+-----------+ > select * from lwt; +-----+-----+-----+--------+ | p | c | i | l | |-----+-----+-----+--------| | 1 | 1 | 2 | null | | 1 | 2 | 2 | [1, 3] | +-----+-----+-----+--------+ > drop table lwt; OK > > -- Suppose there's a batch with two statements, one of which has clustering > -- column restrictions that select no row, another has static conditions. > -- In this case we must fetch the static row (provided it exists, of course) > -- to check the static column conditions. > create table lwt (p int, c int, s int static, primary key (p, c)); OK > insert into lwt(p, s) values(1, 1); OK > begin batch > insert into lwt(p, c) values(1, 1) if not exists > update lwt set s = 2 where p = 1 if s = 1 > apply batch; +-------------+------+------+------+ | [applied] | p | c | s | |-------------+------+------+------| | True | null | null | null | | True | 1 | null | 1 | +-------------+------+------+------+ > select * from lwt; +-----+-----+-----+ | p | c | s | |-----+-----+-----| | 1 | 1 | 2 | +-----+-----+-----+ > drop table lwt; OK > -- > -- > -- conditions on different list columns and different rows of the same > -- partition, these columns are retrieved fine and the appropriate row > -- is found when checking the conds > -- > -- correct read command: statement 1 condition is on column a and list update > -- is on column b, while statement b condition is on column c and list > -- update is on column d > -- a composite read command is built and both rows are retreieved, > -- conditions are executed correctly and mutations are applied accordingly > -- > -- > -- Issue: #6273 > -- Delete have priority above Insert. > create table lwt (key bigint, ck int, cv set, PRIMARY KEY((key), ck)); OK > insert into lwt (key, ck, cv) values (1, 0, {'a', 'b'}) if not exists; +-------------+-------+------+------+ | [applied] | key | ck | cv | |-------------+-------+------+------| | True | null | null | null | +-------------+-------+------+------+ > begin batch > delete from lwt where key=1 and ck=0 if exists > insert into lwt (key, ck, cv) values (1, 0, {'b', 'c'}) > apply batch; +-------------+-------+------+------------+ | [applied] | key | ck | cv | |-------------+-------+------+------------| | True | 1 | 0 | ['a', 'b'] | | True | 1 | 0 | ['a', 'b'] | +-------------+-------+------+------------+ > select * from lwt; +-------+------+------+ | key | ck | cv | |-------+------+------| +-------+------+------+ > drop table lwt; OK > -- > -- Workaround for Issue #6273. > -- Delete individual cells instead of entire partition. > -- > create table lwt (key bigint, ck int, cv set, PRIMARY KEY((key), ck)); OK > insert into lwt (key, ck, cv) values (1, 0, {'a', 'b'}) if not exists; +-------------+-------+------+------+ | [applied] | key | ck | cv | |-------------+-------+------+------| | True | null | null | null | +-------------+-------+------+------+ > begin batch > update lwt set cv=null where key=1 and ck=0 if exists > insert into lwt (key, ck, cv) values (1, 0, {'b', 'c'}) > apply batch; +-------------+-------+------+------------+ | [applied] | key | ck | cv | |-------------+-------+------+------------| | True | 1 | 0 | ['a', 'b'] | | True | 1 | 0 | ['a', 'b'] | +-------------+-------+------+------------+ > select * from lwt; +-------+------+------------+ | key | ck | cv | |-------+------+------------| | 1 | 0 | ['b', 'c'] | +-------+------+------------+ > drop table lwt; OK > > -- > -- A test case for Issue #7113 > -- Return one row per each LWT statement > -- in a batch, in statement order. > -- > CREATE TABLE IF NOT EXISTS gh7113 ( > part int, > key int, > lwt_trivial int, > int1 int, > int2 int, > PRIMARY KEY (part, key) > ); OK > > BEGIN BATCH > UPDATE gh7113 SET int1 = 6 WHERE part = 0 AND key = 4 IF lwt_trivial = null > APPLY BATCH; +-------------+--------+-------+---------------+ | [applied] | part | key | lwt_trivial | |-------------+--------+-------+---------------| | True | null | null | null | +-------------+--------+-------+---------------+ > > BEGIN BATCH > UPDATE gh7113 SET int2 = 0, int1 = 0 WHERE part = 0 AND key = 0 IF lwt_trivial = null > UPDATE gh7113 SET int2 = 1, int1 = 6 WHERE part = 0 AND key = 7 IF lwt_trivial = null > APPLY BATCH; +-------------+--------+-------+---------------+ | [applied] | part | key | lwt_trivial | |-------------+--------+-------+---------------| | True | null | null | null | | True | null | null | null | +-------------+--------+-------+---------------+ > > BEGIN BATCH > UPDATE gh7113 SET int2 = 0, int1 = 2 WHERE part = 0 AND key = 9 IF lwt_trivial = null > UPDATE gh7113 SET int1 = 7 WHERE part = 0 AND key = 0 IF lwt_trivial = null > APPLY BATCH; +-------------+--------+-------+---------------+ | [applied] | part | key | lwt_trivial | |-------------+--------+-------+---------------| | True | null | null | null | | True | 0 | 0 | null | +-------------+--------+-------+---------------+ > > > BEGIN BATCH > UPDATE gh7113 SET int1 = 6, int2 = 7 WHERE part = 0 AND key = 1 IF lwt_trivial = null > UPDATE gh7113 SET int2 = 4 WHERE part = 0 AND key = 0 IF lwt_trivial = null > UPDATE gh7113 SET int2 = 2 WHERE part = 0 AND key = 3 IF lwt_trivial = null > APPLY BATCH; +-------------+--------+-------+---------------+ | [applied] | part | key | lwt_trivial | |-------------+--------+-------+---------------| | True | null | null | null | | True | 0 | 0 | null | | True | null | null | null | +-------------+--------+-------+---------------+ > > BEGIN BATCH > UPDATE gh7113 SET int2 = 1 WHERE part = 0 AND key = 4 IF lwt_trivial = null > UPDATE gh7113 SET int2 = 1 WHERE part = 0 AND key = 0 IF lwt_trivial = null > UPDATE gh7113 SET int1 = 4, int2 = 8 WHERE part = 0 AND key = 9 IF lwt_trivial = null > UPDATE gh7113 SET int1 = 0, int2 = 9 WHERE part = 0 AND key = 0 IF lwt_trivial = null > APPLY BATCH; +-------------+--------+-------+---------------+ | [applied] | part | key | lwt_trivial | |-------------+--------+-------+---------------| | True | 0 | 4 | null | | True | 0 | 0 | null | | True | 0 | 9 | null | | True | 0 | 0 | null | +-------------+--------+-------+---------------+ > DROP TABLE gh7113; OK > > -- cleanup > DROP KEYSPACE ks; OK