lightweight transaction and rowCount return value

[binhnv]

The rowCount method of PDOStatement class as documented here http://us2.php.net/manual/en/pdostatement.rowcount.php should return number of rows affected by the last SQL statement. But as I tested with this driver, the value that this method return does not conform with that document. Here an example, assume we have a table named t with two columns a and b and a is primary key and table is empty.

$stm = $pdo->prepare( "UPDATE t SET b='foo' WHERE a='bar' IF b='foobar' " );
$stm->execute();
print_r( $stm->rowCount() );

The above code always prints 1 out even it does not update any row.

[LexLythius]

Apparently, this is a side effect of the fact that, for conditional updates/inserts (lightweight transactions), CQL will return a resultset with 1 row having a first column of type boolean named "[applied]" and, if the condition fails, all the existing columns for given key. This is meant to avoid read-before-writes mixed with the check-then-act antipattern.

For example, having table

CREATE TABLE string_test (
  id uuid,
  str1 ascii,
  str2 text,
  PRIMARY KEY ((id))
);

See what happens:

cqlsh:test> INSERT INTO string_test (id, str1) 
  VALUES (ead257d9-92f7-4444-8518-4dd9a095f1eb, '001') IF NOT EXISTS;

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

cqlsh:test> INSERT INTO string_test (id, str1)
  VALUES (ead257d9-92f7-4444-8518-4dd9a095f1eb, '001') IF NOT EXISTS;

 [applied] | id                                   | str1 | str2
-----------+--------------------------------------+------+------
     False | ead257d9-92f7-4444-8518-4dd9a095f1eb |  001 | null

So the problem is how to solve this, keeping in mind that prepared statements will not necessarily be inserts/updates/deletes, but they can also be selects.

Also, conditional updates do not support WHERE...IN clauses, so you will either get 0 or 1 rows updated:

cqlsh:test> UPDATE string_test SET str1='blah'
  WHERE id IN (ead257d9-92f7-4444-8518-4dd9a095f1eb, 789257d9-92f7-4444-8518-4dd9a095f1eb)
  IF str1='BLAH';
Bad Request: IN on the partition key is not supported with conditional updates