question about where clause of CQL update statement

[John Sanda <jo...@gmail.com>]

I am using CQL 3 and trying to execute the following,

UPDATE CHANGELOGLOCK SET LOCKED = 'true', LOCKEDBY = '10.11.8.242
(10.11.8.242)', LOCKGRANTED = '2012-10-05 16:58:01' WHERE ID = 1 AND LOCKED
= 'false';


It gives me the error, Bad Request: PRIMARY KEY part locked found in SET
part. The primary key consists only of the ID column, but I do have a
secondary index on the locked column. Is it not possible to include a
column in both the set clause and in the where clause? And if it is not
possible, how come?

Thanks

- John

Re: question about where clause of CQL update statement

[Sylvain Lebresne <sy...@datastax.com>]

> Is it not possible to include a column in both the set clause and in the where clause?

No it's not. Or rather in that case what is not supported is the use
of secondary indexes in update where clauses. But if 'locked' is
indeed not part of your primary key, then at least the error message
suck and will need to be improved.

Now, why isn't this supported? Honestly I don't remember that we have
discussed this much, but I see at least 2 potential reasons why we
might hesitate doing it:
1) we won't really be able to implement this in a much better way that
you would do client (i.e. by first querying the secondary index and
then doing the insertion). In particular, we won't be able to
guarantee the atomicity of the read/write involved by such operation.
2) currently writes don't involve read (that isn't true for 2ndary
index updates currently but that will be fixed in Cassandra 1.2). That
would be a case where a write involves a read, and a fairly costly
one.

This is not saying this is a bad idea, just that there is more to it
than "we've been too lazy to implement it". But if you are interested
I encourage you to open a ticket on JIRA to start a discussion.

--
Sylvain

Re: question about where clause of CQL update statement

[aaron morton <aa...@thelastpickle.com>]

What is the CF schema ?

>  Is it not possible to include a column in both the set clause and in the where clause? And if it is not possible, how come?

Not sure. 

Looks like you are looking for a conditional update here. You know the row is at ID 1 and you only want to update if locked = 'false' ?

Not sure that's supported. But I'm not sure this is the right sort of error. 

Cheers


-----------------
Aaron Morton
Freelance Developer
@aaronmorton
http://www.thelastpickle.com

On 6/10/2012, at 11:30 AM, John Sanda <jo...@gmail.com> wrote:

> I am using CQL 3 and trying to execute the following,
> 
> UPDATE CHANGELOGLOCK SET LOCKED = 'true', LOCKEDBY = '10.11.8.242 (10.11.8.242)', LOCKGRANTED = '2012-10-05 16:58:01' WHERE ID = 1 AND LOCKED = 'false';
> 
> 
> It gives me the error, Bad Request: PRIMARY KEY part locked found in SET part. The primary key consists only of the ID column, but I do have a secondary index on the locked column. Is it not possible to include a column in both the set clause and in the where clause? And if it is not possible, how come?
> 
> Thanks
> 
> - John