Tracking issue for delete

[evenyag]

What problem does the new feature solve?

This is a tracking issue for implementing delete for greptiimedb, also solves #614

What does the feature do?

The final goal is to support the DELETE SQL statement, but we may provide a minimal usable version first, which only allows deleting by primary key, or even only exposes the delete() method in the table interface.

Implementation challenges

The steps to implement a minimal version that supports deletion are

• Support deleting by key in the storage engine Storage engine should support delete #614 feat: Implement delete for the storage engine #777
• Support deleting by primary key in the mito table feat: Implement delete for table #801
• Support the DELETE SQL statement but require specifying the full primary key (and the timestamp)
• Support deletion in distributed mode
• Support DELETE with general "where" condition feat: execute "delete" in query engine (in the form of "LogicalPlan") #1222

Related Issues

• Delete rows in table with non-null value column Unable to delete rows in table that has non-nullable value column #848

[evenyag]

I have created the PR #775 to do the WriteBatch refactoring. I plan to experiment with the delete API in another PR.

[jun0315]

@evenyag Hi, I see on greptimeDB WeChat official account that we do not support delete SQL at present. If possible, I want to try this part:D

[evenyag]

Hi, I see on greptimeDB WeChat official account that we do not support delete SQL at present. If possible, I want to try this part:D

@jun0315 Sure! You could try this task.

Support the DELETE SQL statement but require specifying the full primary key (and the timestamp)

The sqlparser-rs crate supports delete statement so we could just use it.

But the table trait requires providing all key columns in order to form the key and delete that key in the underlying storage engine. So we might only implement the simplest delete statement that: only uses =, and in the where clause and provides all columns needed by the key.

delete from my_table where k1 = xxx and k2 = xxx and timestamp = xxx;

A better approach might be invoking the query engine to scan all the keys and then delete them, so we could support more complex where clauses.

[jun0315]

@evenyag Thank you very much for your reply.

So we might only implement the simplest delete statement that: only uses =, and in the where clause and provides all columns needed by the key.

This means that we must provide all columns in the where statement. If k1 is not specified, do we throw an exception or automatically ignore the filter condition of k1

delete from my_table where k2 = xxx and timestamp = xxx;

[waynexia]

If k1 is not specified, do we throw an exception or automatically ignore the filter condition of k1

Ignoring unspecified columns is fine. Other common DBMSs also act like this.

[jun0315]

Ignoring unspecified columns is fine. Other common DBMSs also act like this.

Ok, thank you. I have identified the requirements:D

[evenyag]

If k1 is not specified, do we throw an exception or automatically ignore the filter condition of k1

You could ignore them in the SQL layer. The underlying table implementation checks whether all key columns are provided and returns error if there are missing columns.