Limit on Update and Delete about crate

[dobe]

hi @herenow

yes, we currently have do not support limit on update and delete
see https://crate.io/docs/current/sql/reference/delete.html and https://crate.io/docs/current/sql/reference/update.html

can you tell me about the use-case you have to update delete with limit. we put this on our backlog - in order to prioritize this feature, it would be nice to hear such use-cases

thx, bernd

[herenow]

Hi @dobe,

Just let me get something straight first, how does Crate know when to stop deleting or updating? If its searching with a 'primary key' it will by default stop matching once it has found one match? If its a normal search based on a normal index 'key' it will keep iterating the table until it has found all matches?

Sorry if I got the process wrong, i'm not sure how DB's generally work and match data. But as a developer, I generally put LIMIT's on my UPDATE's and DELETE's for performance reasons, considering if I don't the DB will keep trying to find more then one match.

[dobe]

In crate, it is actually more work to apply a limit than without. This is due to the distributed nature of crate. A table is distributed across multiply shards from which all matching docs are updated or deleted. Implementing a limit clause would require some effort in order to not update/delete too many rows, since we cannot limit just by the value of the specified limit, because in the worst case it would delete num_shards*limit number of documents instead of just the specified limit.

However since this is only for performance - you don't need to specify the limit. I also think that for most databases this makes no difference in speed.

Actually, if crate sees that a full primary key is specified in the query part, the planner internally does a direct update of the row instead of a query automatically.

[herenow]

Got it, thanks.