[YCQL] Identify if an upsert operation inserts or updates fails

[beatrausch]

Jira Link: DB-3181

Description

Hi,

we try to identify if an upsert statement inserts or updates the row based on the RETURNS STATUS AS ROW.
When we run the following example with YB Version 2.14.1.0 it does not seem to work as expected:

ycqlsh> create keyspace ks;
ycqlsh> use ks;
ycqlsh:ks> create table test(key text primary key, value text, checkpoint text);

# Inserting a new row
ycqlsh:ks> update test set value='v1', checkpoint='c1' where key='k1' if checkpoint='' or not exists returns status as row;

[applied] | [message] | key  | value | checkpoint
-----------+-----------+------+-------+------------
      True |      null | null |  null |       null

For the insert we would expect applied=false

# Updating an existing row
ycqlsh:ks> update test set value='v1', checkpoint='c1' where key='k1' if checkpoint='c1' or not exists returns status as row;

[applied] | [message] | key  | value | checkpoint
-----------+-----------+------+-------+------------
      True |      null | null |  null |       null

Works as expected

[ddorian]

For the insert we would expect applied=false

But the row is inserted (because of or not exists).

Updates in YCQL are like UPSERTS. Example

ycqlsh:ks> update test set value='v1', checkpoint='c1' where key='k2' returns status as row;

 [applied] | [message] | key  | value | checkpoint
-----------+-----------+------+-------+------------
      True |      null | null |  null |       null

ycqlsh:ks> select * from test;

 key | value | checkpoint
-----+-------+------------
  k2 |    v1 |         c1

(1 rows)

[beatrausch]

@ddorian If this behavior is intended, what is the best way to distinguish/identify if a value is inserted or updated when using an upsert statement?

[ddorian]

what is the best way to distinguish/identify if a value is inserted or updated when using an upsert statement?

It's not currently possible to distinguish because it doesn't do a read to check if the row exists or not.

[beatrausch]

@ddorian What do you think, does it make sense to support such kind of feature?

[ddorian]

@ddorian What do you think, does it make sense to support such kind of feature?

@beatrausch personally, no.

This feature is already supported in YSQL, so you can use that API if you need it.

YCQL is supposed to compete with Cassandra (where this feature has very low chance of landing)

[in-cloud-opensource]

@ddorian If you want to use the TTL feature from YCQL that is not possible with YSQL right?

[ddorian]

If you want to use the TTL feature from YCQL that is not possible with YSQL right?

@in-cloud-opensource no, it's not.

Can you explain your complete use case for this table where you need TTL & UPSERT ?
You can do it on the forums,slack, or here.