IF NOT EXISTS on UPDATE statements?

[Kevin Burton]

There’s still a lot of weirdness in CQL.

For example, you can do an INSERT with an UPDATE .. .which I’m generally
fine with. Kind of make sense.

However, with INSERT you can do IF NOT EXISTS.


 but you can’t do the same thing on UPDATE.

So I foolishly wrote all my code assuming that INSERT/UPDATE were
orthogonal, but now they’re not.

you can still do IF on UPDATE though
 but it’s not possible to do IF
mycolumn IS NULL

.. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug?

[DuyHai Doan]

"So I foolishly wrote all my code assuming that INSERT/UPDATE were
orthogonal, but now they’re not"

There are some subtle differences.

INSERT will create marker columns, UPDATE won't touch/modify them. What
are marker columns ? Some insights here:
http://www.slideshare.net/doanduyhai/cassandra-introduction-40711134/87

"you can still do IF on UPDATE though
 but it’s not possible to do IF
mycolumn IS NULL" --> If mycolumn = null should work

Post by Kevin Burton
There’s still a lot of weirdness in CQL.
For example, you can do an INSERT with an UPDATE .. .which I’m generally
fine with. Kind of make sense.
However, with INSERT you can do IF NOT EXISTS.

 but you can’t do the same thing on UPDATE.
So I foolishly wrote all my code assuming that INSERT/UPDATE were
orthogonal, but now they’re not.
you can still do IF on UPDATE though
 but it’s not possible to do IF
mycolumn IS NULL
.. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug?
--
Founder/CEO Spinn3r.com
Location: *San Francisco, CA*
blog: http://burtonator.wordpress.com

 or check out my Google+ profile
<https://plus.google.com/102718274791889610666/posts>
<http://spinn3r.com>

[Kevin Burton]

Post by DuyHai Doan
"you can still do IF on UPDATE though
 but it’s not possible to do IF
mycolumn IS NULL" --> If mycolumn = null should work

Alas.. it doesn’t :-/

[DuyHai Doan]

Just tested with C* 2.1.1

cqlsh:test> CREATE TABLE simple(id int PRIMARY KEY, val text);
cqlsh:test> INSERT INTO simple (id) VALUES (1);
cqlsh:test> SELECT * FROM simple ;

id | val
----+------
1 | null

(1 rows)

cqlsh:test> UPDATE simple SET val = 'new val' WHERE id=1 *IF val = null*;

[applied]
-----------
True

cqlsh:test> SELECT * FROM simple ;

id | val
----+---------
1 | new val

(1 rows)

Post by DuyHai Doan
"you can still do IF on UPDATE though
 but it’s not possible to do IF

Post by DuyHai Doan
mycolumn IS NULL" --> If mycolumn = null should work

Alas.. it doesn’t :-/
--
Founder/CEO Spinn3r.com
Location: *San Francisco, CA*
blog: http://burtonator.wordpress.com

 or check out my Google+ profile
<https://plus.google.com/102718274791889610666/posts>
<http://spinn3r.com>

[Kevin Burton]

Oh yes. That will work because a value is already there. I’m talking if
the value does not exist. Otherwise I’d have to insert a null first.

Post by DuyHai Doan
Just tested with C* 2.1.1
cqlsh:test> CREATE TABLE simple(id int PRIMARY KEY, val text);
cqlsh:test> INSERT INTO simple (id) VALUES (1);
cqlsh:test> SELECT * FROM simple ;
id | val
----+------
1 | null
(1 rows)
cqlsh:test> UPDATE simple SET val = 'new val' WHERE id=1 *IF val = null*;
[applied]
-----------
True
cqlsh:test> SELECT * FROM simple ;
id | val
----+---------
1 | new val
(1 rows)

Post by DuyHai Doan
"you can still do IF on UPDATE though
 but it’s not possible to do IF

Post by DuyHai Doan
mycolumn IS NULL" --> If mycolumn = null should work

Alas.. it doesn’t :-/
--
Founder/CEO Spinn3r.com
Location: *San Francisco, CA*
blog: http://burtonator.wordpress.com

 or check out my Google+ profile
<https://plus.google.com/102718274791889610666/posts>
<http://spinn3r.com>

[Sylvain Lebresne]

Post by Kevin Burton
There’s still a lot of weirdness in CQL.
For example, you can do an INSERT with an UPDATE .. .which I’m generally
fine with. Kind of make sense.
However, with INSERT you can do IF NOT EXISTS.

 but you can’t do the same thing on UPDATE.
So I foolishly wrote all my code assuming that INSERT/UPDATE were
orthogonal, but now they’re not.
you can still do IF on UPDATE though
 but it’s not possible to do IF
mycolumn IS NULL
.. so is there a way to mimic IF NOT EXISTS on UPDATE or is this just a bug?

There is no way to mimic IF NOT EXISTS on UPDATE and it's not a bug. INSERT
and UPDATE are not totally orthogonal
in CQL and you should use INSERT for actual insertion and UPDATE for
updates (granted, the database will not reject
our query if you break this rule but it's nonetheless the way it's intended
to be used).

--
Sylvain

Post by Kevin Burton
--
Founder/CEO Spinn3r.com
Location: *San Francisco, CA*
blog: http://burtonator.wordpress.com

 or check out my Google+ profile
<https://plus.google.com/102718274791889610666/posts>
<http://spinn3r.com>

[Kevin Burton]

Post by Sylvain Lebresne
There is no way to mimic IF NOT EXISTS on UPDATE and it's not a bug.

INSERT and UPDATE are not totally orthogonal
in CQL and you should use INSERT for actual insertion and UPDATE for
updates (granted, the database will not reject
our query if you break this rule but it's nonetheless the way it's intended
to be used).

OK.. (and not trying to be difficult here). We can’t have it both ways.
One of these use cases is a bug


You’re essentially saying “don’t do that, but yeah, you can do it.. “

Either UPDATE should support IF NOT EXISTS or UPDATE should not perform
INSERTs.

At least that’s the way I see it.

Kevin

[Robert Stupp]

There is no way to mimic IF NOT EXISTS on UPDATE and it's not a bug. INSERT and UPDATE are not totally orthogonal

in CQL and you should use INSERT for actual insertion and UPDATE for updates (granted, the database will not reject
our query if you break this rule but it's nonetheless the way it's intended to be used).
OK.. (and not trying to be difficult here). We can’t have it both ways. One of these use cases is a bug

You’re essentially saying “don’t do that, but yeah, you can do it.. “
Either UPDATE should support IF NOT EXISTS or UPDATE should not perform INSERTs.

UPDATE performs like INSERT in the meaning of an UPSERT - means: INSERT allows to write the same primary key again and UPDATE allows to write data to a non-existing primary key (effectively inserting data).
(That’s what NoSQL databases do.)
Take that as an advantage / feature not present on other DBs.

"UPDATE 
 IF EXISTS“ and "INSERT 
 IF NOT EXISTS“ are *expensive* operations (require serial-consistency/LWT which requires some more network roundtrips).
"IF [NOT] EXISTS“ is basically some kind of "convenience“.
And please take into account that UPDATE also has "IF column = value“ condition (using LWT).

[Brian O'Neill]

FWIW ‹ we have the exact same need.
And we have been struggling with the differences in CQL between UPDATE and
INSERT.

Our use case:

We do in-memory dimensional aggregations that we want to write to C* using
LWT.
(so, it¹s a low-volume of writes, because we are doing aggregations across
time windows)

On ³commit², we:
1) Read current value for time window
(which returns null if not exists for time window, or current_value if
exists)
2) Then we need to UPSERT new_value for window
where new_value = current_value + agg_value
but only if no other node has updated the value

For (2), we would love to see:
UPSERT value=new_value where (not exists || value=read_value)

(ignoring some intricacies)

-brian

---
Brian O'Neill
Chief Technology Officer


Health Market Science
The Science of Better Results
2700 Horizon Drive € King of Prussia, PA € 19406
M: 215.588.6024 € @boneill42 <http://www.twitter.com/boneill42> €
healthmarketscience.com


This information transmitted in this email message is for the intended
recipient only and may contain confidential and/or privileged material. If
you received this email in error and are not the intended recipient, or the
person responsible to deliver it to the intended recipient, please contact
the sender at the email above and delete this email and any attachments and
destroy any copies thereof. Any review, retransmission, dissemination,
copying or other use of, or taking any action in reliance upon, this
information by persons or entities other than the intended recipient is
strictly prohibited.



From: Robert Stupp <***@snazy.de>
Reply-To: <***@cassandra.apache.org>
Date: Tuesday, November 18, 2014 at 12:35 PM
To: <***@cassandra.apache.org>
Subject: Re: IF NOT EXISTS on UPDATE statements?

Post by Sylvain Lebresne

Post by Sylvain Lebresne
There is no way to mimic IF NOT EXISTS on UPDATE and it's not a bug. INSERT

and UPDATE are not totally orthogonal

in CQL and you should use INSERT for actual insertion and UPDATE for updates
(granted, the database will not reject
our query if you break this rule but it's nonetheless the way it's intended to be used).
OK.. (and not trying to be difficult here). We can¹t have it both ways. One
of these use cases is a bugŠ
You¹re essentially saying ³don¹t do that, but yeah, you can do it.. ³
Either UPDATE should support IF NOT EXISTS or UPDATE should not perform INSERTs.

UPDATE performs like INSERT in the meaning of an UPSERT - means: INSERT
allows to write the same primary key again and UPDATE allows to write data
to a non-existing primary key (effectively inserting data).
(That¹s what NoSQL databases do.)
Take that as an advantage / feature not present on other DBs.

"UPDATE Š IF EXISTS³ and "INSERT Š IF NOT EXISTS³ are *expensive* operations
(require serial-consistency/LWT which requires some more network
roundtrips).
"IF [NOT] EXISTS³ is basically some kind of "convenience³.
And please take into account that UPDATE also has "IF column = value
³ condition (using LWT).

[Robert Stupp]

Post by Brian O'Neill
UPSERT value=new_value where (not exists || value=read_value)

That would be something like "UPDATE … IF column=value OR NOT EXISTS“.

Took at the C* source and that feels like a LHF (for 3.0) so I opened https://issues.apache.org/jira/browse/CASSANDRA-8335 for that.
Fell free to comment on that :)

[Brian O'Neill]

Exactly. Perfect. Will do.
Thanks Robert.

-brian

---
Brian O'Neill
Chief Technology Officer


Health Market Science
The Science of Better Results
2700 Horizon Drive € King of Prussia, PA € 19406
M: 215.588.6024 € @boneill42 <http://www.twitter.com/boneill42> €
healthmarketscience.com


This information transmitted in this email message is for the intended
recipient only and may contain confidential and/or privileged material. If
you received this email in error and are not the intended recipient, or the
person responsible to deliver it to the intended recipient, please contact
the sender at the email above and delete this email and any attachments and
destroy any copies thereof. Any review, retransmission, dissemination,
copying or other use of, or taking any action in reliance upon, this
information by persons or entities other than the intended recipient is
strictly prohibited.



From: Robert Stupp <***@snazy.de>
Reply-To: <***@cassandra.apache.org>
Date: Tuesday, November 18, 2014 at 2:26 PM
To: <***@cassandra.apache.org>
Subject: Re: IF NOT EXISTS on UPDATE statements?

Post by Brian O'Neill
UPSERT value=new_value where (not exists || value=read_value)

That would be something like "UPDATE Š IF column=value OR NOT EXISTS³.

Took at the C* source and that feels like a LHF (for 3.0) so I opened
https://issues.apache.org/jira/browse/CASSANDRA-8335 for that.
Fell free to comment on that :)

[DuyHai Doan]

Hi there

I was facing a similar requirement recently, e.g. UPDATE .... IF EXISTS and
I found a work-around.

CREATE TABLE my_table(
partition_key int,
duplicate_partition_key int,
....
value text,
PRIMARY KEY(partition_key));

At the beginning, I tried to query with : UPDATE my_table SET value = ...
WHERE partition_key=xxx IF partition_key=xxx but I ran into "Bad Request:
PRIMARY KEY column 'id' cannot have IF conditions"

So the idea is to create a duplicate column of the partition key. This
column is only set at the creation of the partition and never modified.
With this I can simulate an IF EXISTS using UPDATE:

UPDATE my_table SET value = ... WHERE partition_key=xxx IF
duplicate_partition_key=xxx;

Post by Brian O'Neill
Exactly. Perfect. Will do.
Thanks Robert.
-brian
---
Brian O'Neill
Chief Technology Officer
*Health Market Science*
*The Science of Better Results*
2700 Horizon Drive • King of Prussia, PA • 19406
healthmarketscience.com
This information transmitted in this email message is for the intended
recipient only and may contain confidential and/or privileged material. If
you received this email in error and are not the intended recipient, or the
person responsible to deliver it to the intended recipient, please contact
the sender at the email above and delete this email and any attachments and
destroy any copies thereof. Any review, retransmission, dissemination,
copying or other use of, or taking any action in reliance upon, this
information by persons or entities other than the intended recipient is
strictly prohibited.
Date: Tuesday, November 18, 2014 at 2:26 PM
Subject: Re: IF NOT EXISTS on UPDATE statements?
UPSERT value=new_value where (not exists || value=read_value)
That would be something like "UPDATE 
 IF column=value OR NOT EXISTS“.
Took at the C* source and that feels like a LHF (for 3.0) so I opened
https://issues.apache.org/jira/browse/CASSANDRA-8335 for that.
Fell free to comment on that :)