Ok, tried the test again, w/o the TIMESTAMP, and got the expected behavior.
Apparently, the INSERT does replace the entire list if no timestamp is
specified (as expected).
However, if the TIMESTAMP is specified, then it does (what appears to be) an
append.
But found even more weird issue - see later below!
===============================================
cqlsh> CREATE TABLE test.test (k int PRIMARY KEY , v list<int>);
cqlsh> INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) ;
cqlsh> INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) ;
cqlsh> INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) ;
cqlsh> SELECT * FROM test.test ;
k | v
---+-----
1 | [1]
(1 rows)
cqlsh>
===============================================
DROP TABLE IF EXISTS test.test ;
CREATE TABLE test.test (k int PRIMARY KEY , v list<int>);
INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) ;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[2]) ;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[3]) ;
SELECT * FROM test.test ;
cqlsh> SELECT * FROM test.test ;
k | v
---+-----
1 | [3]
// ===== EXPECTED RESULT =====
INSERT INTO test.test (k , v ) VALUES ( 1 ,[10]) USING TIMESTAMP 1000;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[20]) USING TIMESTAMP 1000;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[30]) USING TIMESTAMP 1000;
SELECT * FROM test.test ;
cqlsh> SELECT * FROM test.test ;
k | v
---+-----
1 | [3]
// ===== WHY ?? =====
DROP TABLE IF EXISTS test.test ;
CREATE TABLE test.test (k int PRIMARY KEY , v list<int>);
INSERT INTO test.test (k , v ) VALUES ( 1 ,[10]) USING TIMESTAMP 1000;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[20]) USING TIMESTAMP 1000;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[30]) USING TIMESTAMP 1000;
SELECT * FROM test.test ;
cqlsh> SELECT * FROM test.test ;
k | v
---+--------------
1 | [10, 20, 30]
// ===== WHY ?? Probably the server-timestamp-uuid playing a role?! =====
INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) ;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[2]) ;
INSERT INTO test.test (k , v ) VALUES ( 1 ,[3]) ;
SELECT * FROM test.test ;
cqlsh> SELECT * FROM test.test ;
k | v
---+-----
1 | [3]
// ===== EXPECTED RESULT =====
From: Subroto Barua <sbarua...@yahoo.com>
Date: Monday, June 19, 2017 at 11:09 PM
To: "Thakrar, Jayesh" <jthak...@conversantmedia.com>, Subroto Barua
<sbarua...@yahoo.com.INVALID>, Zhongxiang Zheng <zzh...@yahoo-corp.jp>
Cc: "user@cassandra.apache.org" <user@cassandra.apache.org>
Subject: Re: Question: Behavior of inserting a list multiple times with same
timestamp
here is the response from Datastax support/dev:
In a list each item is its own cell. Append adds a new cell sorted at basically
"current server time uuid" prepend adds at "-current server time uuid". User
supplied time stamps are used for the cell timestamp when specified.
Inserting the entire list deletes and then inserts
Reading reads out the entire list
Positional access reads the entire list and gets/puts at the spot specified
Basically, lists are not idempotent
On Monday, June 19, 2017, 6:55:40 AM PDT, Thakrar, Jayesh
<jthak...@conversantmedia.com> wrote:
Subroto,
Cassandra docs say otherwise.
Writing list data is accomplished with a JSON-style syntax. To write a record
using INSERT, specify the entire list as a JSON array. Note: An INSERT will
always replace the entire list.
Maybe you can elaborate/shed some more light?
Thanks,
Jayesh
Lists
A list is a typed collection of non-unique values where elements are ordered by
there position in the list. To create a column of type list, use the list
keyword suffixed with the value type enclosed in angle brackets. For example:
CREATE TABLE plays (
id text PRIMARY KEY,
game text,
players int,
scores list<int>
)
Do note that as explained below, lists have some limitations and performance
considerations to take into account, and it is advised to prefer sets over
lists when this is possible.
Writing list data is accomplished with a JSON-style syntax. To write a record
using INSERT, specify the entire list as a JSON array. Note: An INSERT will
always replace the entire list.
INSERT INTO plays (id, game, players, scores)
VALUES ('123-afde', 'quake', 3, [17, 4, 2]);
Adding (appending or prepending) values to a list can be accomplished by adding
a new JSON-style array to an existing list column.
UPDATE plays SET players = 5, scores = scores + [ 14, 21 ] WHERE id =
'123-afde';
UPDATE plays SET players = 5, scores = [ 12 ] + scores WHERE id = '123-afde';
It should be noted that append and prepend are not idempotent operations. This
means that if during an append or a prepend the operation timeout, it is not
always safe to retry the operation (as this could result in the record appended
or prepended twice).
Lists also provides the following operation: setting an element by its position
in the list, removing an element by its position in the list and remove all the
occurrence of a given value in the list. However, and contrarily to all the
other collection operations, these three operations induce an internal read
before the update, and will thus typically have slower performance
characteristics. Those operations have the following syntax:
UPDATE plays SET scores[1] = 7 WHERE id = '123-afde'; // sets
the 2nd element of scores to 7 (raises an error is scores has less than 2
elements)
DELETE scores[1] FROM plays WHERE id = '123-afde'; // deletes
the 2nd element of scores (raises an error is scores has less than 2 elements)
UPDATE plays SET scores = scores - [ 12, 21 ] WHERE id = '123-afde'; // removes
all occurrences of 12 and 21 from scores
As with maps, TTLs if used only apply to the newly inserted/updated values.
On 6/19/17, 1:12 AM, "Subroto Barua"
<sbarua...@yahoo.com.INVALID<mailto:sbarua...@yahoo.com.INVALID>> wrote:
This is an expected behavior.
We learned this issue/feature at the current site (we use Dse 5.08)
Subroto
> On Jun 18, 2017, at 10:29 PM, Zhongxiang Zheng
<zzh...@yahoo-corp.jp<mailto:zzh...@yahoo-corp.jp>> wrote:
>
> Hi all,
>
> I have a question about a behavior when insert a list with specifying
timestamp.
>
> It is documented that "An INSERT will always replace the entire list."
> https://github.com/apache/cassandra/blob/trunk/doc/cql3/CQL.textile#lists
>
> However, When a list is inserted multiple times using same timestamp,
> it will not be replaced, but will be added as follows.
>
> cqlsh> CREATE TABLE test.test (k int PRIMARY KEY , v list<int>);
> cqlsh> INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) USING TIMESTAMP
1000 ;
> cqlsh> INSERT INTO test.test (k , v ) VALUES ( 1 ,[1]) USING TIMESTAMP
1000 ;
> cqlsh> SELECT * FROM test.test ;
>
> k | v
> ---+--------
> 1 | [1, 1]
>
> I confirmed this behavior is reproduced in 3.0.13 and 3.10.
> I'd like to ask whether this behavior is a expected behavior or a bug?
>
> In our use case, CQL statements with same values and timestamp will be
issued multiple times
> to retry inserting under the assumption that insert is idempotent.
> So, I expect that the entire list will be replace even if insert a list
multiple times with same timestamp.
>
> Thanks,
>
> Zhongxiang
>
>
>
ТÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÒÐÐ¥Fò
Vç7V'67&–&RÂRÖ֖â
W6W"×Vç7V'67&–&T676æG&æ6†Ræ÷&pФf÷"FF—F–öæÂ6öÖÖæG2ÂRÖ֖â
W6W"Ö†VÇ676æG&æ6†Ræ÷&pÐ
B�KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKCB��[��X��ܚX�KK[XZ[�\�\�][��X��ܚX�P�\��[��K�\X�K�ܙ�B��܈Y][ۘ[��[X[��K[XZ[�\�\�Z[�\��[��K�\X�K�ܙ�B
