Posted by mattconnolly on July 4, 2008 at 1:03pm
Jump to:
| Project: | Drupal core |
| Version: | 7.x-dev |
| Component: | database system |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (won't fix) |
Issue Summary
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.
Comments
#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.
#5
This code results in "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1'"
<?php$result = db_query_range("DELETE FROM {simpleblogroll} WHERE feed_url = :feed_url ORDER BY timestamp ASC",0,1, array(':feed_url' => $feedurl,));
?>
This code results in "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery':"
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
<?php$result = db_query("DELETE FROM {simpleblogroll} WHERE feed_url IN(SELECT feed_url FROM {simpleblogroll} WHERE feed_url = :feed_url ORDER BY timestamp ASC LIMIT 1)", array(':feed_url' => $feedurl,));
?>
Same problem here:
<?php
$subquery = db_select('simpleblogroll','sbr');
$subquery->fields('sbr',Array('feed_url'))
->orderBy('timestamp', 'ASC')
->range(0,1)
->condition('feed_url', $feedurl);
db_delete('simpleblogroll')
->condition('feed_url', $subquery, 'IN')
->execute();
?>
This code results in "PDOException: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'simpleblogroll' for update" It looks like you can't update the same table which you use in the SELECT part in MYSQL
http://bugs.mysql.com/bug.php?id=6980
<?php
$subquery = db_select('simpleblogroll','sbr');
$subquery->fields('sbr',Array('feed_url'))
->orderBy('timestamp', 'ASC')
->condition('feed_url', $feedurl);
db_delete('simpleblogroll')
->condition('feed_url', $subquery, 'IN')
->execute();
?>
#6
I tried to do this with a subselect and a subquery on db_delete() and ran into problems with both. See above.
My original goal was to update some D6 code to D7:
<?phpdb_query('DELETE FROM {simpleblogroll} '
."WHERE feed_url='%s' "
.'ORDER BY timestamp ASC '
.'LIMIT 1',
$feedurl
);
?>
#7
Unfortunately you cannot use
->condition()with a subselect. See<?php/**
* Helper function: builds the most common conditional clauses.
*
* This method can take a variable number of parameters. If called with two
* parameters, they are taken as $field and $value with $operator having a
* value of IN if $value is an array and = otherwise.
*
* @param $field
* The name of the field to check. If you would like to add a more complex
* condition involving operators or functions, use where().
* @param $value
* The value to test the field against. In most cases, this is a scalar.
* For more complex options, it is an array. The meaning of each element in
* the array is dependent on the $operator.
* @param $operator
* The comparison operator, such as =, <, or >=. It also accepts more
* complex options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is
* an array, and = otherwise.
*
* @return QueryConditionInterface
* The called object.
*/
public function condition($field, $value = NULL, $operator = NULL);
?>
You'll probably need to use the where() function.
#8
Also, it appears that your original support request has been addressed in #630054-8: About port to Drupal 7, so I'm going to reset the issue status on this one back to it's prior values.