[YCQL] Deleting rows does not work if rows have been deleted by the TTL previously

[in-cloud-opensource]

Jira Link: DB-3328

Description

Hi,

We noticed some unexpected behavior when using the YCQL-API and Yugabyte.

Version 2.14.1.0 build 36 revision aa9d62a build_type RELEASE built at 08 Aug 2022 08:45:12 UTC.

Deleting a range of rows does not work if rows with the same partition key have been deleted by the TTL-Feature previously.
To reproduce the bug, it is necessary to create a table with a partition key, cluster key and the default_time_to_live property.
The timing of the delete queries also matters (detailed description in the comments below).

Preparation:

// Start the yugabyte container
docker run --rm -d --name yugabyte yugabytedb/yugabyte:2.14.1.0-b36 bin/yugabyted start --daemon=false

// Connect into the container
docker exec -it yugabyte /bin/bash

// Open the ycqlsh
ycqlsh

The following steps describe how to reproduce the bug:

// Create a test keyspace
create keyspace ks;
use ks;

// Create a table with partition key, cluster key and a default_time_to_live
create table test (key text, sort text, value text, primary key((key), sort)) with default_time_to_live = 30;


// Upsert some rows with the same partition key and different cluster key.
update test set value = 'v1' where key = 'A' and sort = 'A';
update test set value = 'v1' where key = 'A' and sort = 'B';

// Delete all rows where key = 'A'  *before*  they expire.
delete from test where key = 'A';


/////////////////////////////////////////////////////////////////////////////////////
// Wait until the rows would have been expired, if they had not been deleted.      //
// So at least default_time_to_live seconds. In this example 30 seconds.           //
/////////////////////////////////////////////////////////////////////////////////////


// Upsert the same rows again.
update test set value = 'v1' where key = 'A' and sort = 'A';
update test set value = 'v1' where key = 'A' and sort = 'B';


// Try to delete all rows where key = 'A'.
// This delete query does not work as expected.
// It runs as if the rows were deleted. But a select query shows that they still exist.
delete from test where key = 'A';

// The rows with partition key = 'A' still exist.
// Which is is not expected
select key, sort, value, ttl(value) from test;

Seems to be a bug?

Regards

[yusong-yan]

This issue can be constantly reproduced on 2.14 cluster

However, the issue seems to be fixed in the master version as I am no longer able to reproduce it anymore

[yusong-yan]

Here is what I saw from the sst file.
First two upsert

SubDocKey(DocKey(0xabed, ["A"], ["A"]), [ColumnId(2); HT{ physical: 1707413501356944 }]) -> "v1"
SubDocKey(DocKey(0xabed, ["A"], ["B"]), [ColumnId(2); HT{ physical: 1707413503528423 }]) -> "v1"

The delete command before they expire

SubDocKey(DocKey(0xabed, ["A"], ["A"]), [HT{ physical: 1707413516031093 }]) -> DEL
SubDocKey(DocKey(0xabed, ["A"], ["B"]), [HT{ physical: 1707413516031093 w: 1 }]) -> DEL

Second two upsert

SubDocKey(DocKey(0xabed, ["A"], ["A"]), [ColumnId(2); HT{ physical: 1707413931774837 }]) -> "v1"
SubDocKey(DocKey(0xabed, ["A"], ["B"]), [ColumnId(2); HT{ physical: 1707413932164249 }]) -> "v1"

Then the second delete command get executed, but it didn't show up in sst file as expected.

[yusong-yan]

After running the second delete from test where key = 'A'; command, the while (VERIFY_RESULT(iterator.HasNext()))
loop was never entered, which means no deletion happened.

Status QLWriteOperation::Apply(const DocOperationApplyData& data) {
        ...
        // Create iterator.
        DocRowwiseIterator iterator(
            projection, *doc_read_context_, txn_op_context_,
            data.doc_write_batch->doc_db(), data.deadline, data.read_time);
        RETURN_NOT_OK(iterator.Init(spec));

        // Iterate through rows and delete those that match the condition.
        // TODO(mihnea): We do not lock here, so other write transactions coming in might appear
        // partially applied if they happen in the middle of a ranged delete.
        while (VERIFY_RESULT(iterator.HasNext())) {
          existing_row.Clear();
          RETURN_NOT_OK(iterator.NextRow(&existing_row));

          // Match the row with the where condition before deleting it.
          bool match = false;
          RETURN_NOT_OK(spec.Match(existing_row, &match));
          if (match) {
            const DocPath row_path(iterator.row_key());
            RETURN_NOT_OK(DeleteRow(row_path, data.doc_write_batch, data.read_time, data.deadline));
        ...

[rthallamko3]

Fixed by the refactoring in #12241 , so its a problem for 2.14.x customers only.

[rthallamko3]

Lowering the severity as the impact is only on 2.14.x customers.