mirrors/scylladb
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
debug_formatting
scylladb/test/cql/lwt_batch_test.result
Konstantin Osipov 92aca17bc5 test: make cql/lwt_* pass with and without tablets 
Lightweight transactions don't support tablets, so let's
explicitly disable tablets in LWT tests.
2024-10-02 06:37:14 -04:00

543 lines
20 KiB
Plaintext
Raw Permalink Blame History

> 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<text>, d list<text> 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<int>, 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<text>, 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<text>, 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
Reference in New Issue View Git Blame Copy Permalink