Cannot use LIMIT on DELETE query
mattconnolly - July 4, 2008 - 13:03
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | database system |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | won't fix |
Jump to:
Description
The function db_query_range steals the last two arguments to the function as LIMIT offset,count values. This is fine for SELECT queries, but DELETE queries use only LIMIT count.
Additionally, specifying a single parameter is valid in MySQL (ie: LIMIT count) but no provision is made for that by this function.
Effectively that means that the db_query_range function cannot be used to LIMIT DELETE queries or apply a LIMIT without an offset.
I suggest that the LIMIT count value be taken and the offset only applied if the argument is provided. This would make the command compatible with DELETE queries.
#1
Our other supported database PostgreSQL does not support any LIMIT at all on DELETE: http://www.postgresql.org/docs/8.1/static/sql-delete.html
Since this is about a missing feature of one of the functions which was not designed to be used for DELETE queries and only applies to one of our supported databases, it is way far from critical. It is basically a missing feature and therefore should be part of Drupal 7 and possibly backported to Drupal 6 if simple enough. It might not apply to Drupal 7 given the new database layer, in which case feel free to move back to Drupal 6 as a task. Not a bug.
#2
We could certainly add a range() method to delete or update queries as far as the PHP is concerned, but if Postgres doesn't support that in the first place then I don't know if it's a good idea for us to do so. Damien?
#3
This can be emulated using a subselect (DELETE FROM foo WHERE foo_id IN (SELECT foo_id FROM foo ... LIMIT 5)) on all databases, but isn't natively supported except on MySQL, so this is a won't fix.
#4
You can easily do that on D7 with a subquery in a db_delete() query. Won't fix.