Projects ⇒ Bugs ⇒ #390: Invalid LIMITs in UPDATE SQL statements. ⇒ CPG Dragonfly™ CMS
Bug #390 Invalid LIMITs in UPDATE SQL statements.
Project: Dragonfly 9.0 -> 9.0.2 Category: SQL
Submitted: Monday, April 04, 2005 (02:39:52) Modified: Monday, April 04, 2005 (21:51:21)
Status: Closed Assigned to:
PHP Version: Irrelevant HTTPD Server: Apache 2.0

View/Vote Add Comment

by: Brennor
According to the SQL99 standard, LIMIT isn't a valid option for an UPDATE command. MySQL allows it, but other DB servers do not (PostgreSQL, Oracle).

The statements that are wrong are generally of the form:
UPDATE abc SET def WHERE ghi='jkl' LIMIT 1

The fix is to delete the " LIMIT 1" bit of the command. In most cases (at least all that I've found so far) there will only be 1 row that matches the WHERE clause anyways.

/html/admin/modules/index.php (rev 9.15): lines 29, 34, 39
/html/admin/modules/messages.php (rev 9.4): line 29
/html/blocks/block-Newsletter.php (rev 9.6): lines 30, 38
/html/includes/coppermine/ (rev 9.1): line 67
/html/index.php (rev 9.12): line 107
/html/modules/Your_Account/index.php (rev 9.10): line 103
/html/modules/coppermine/catmgr.php (rev 9.0): lines 71, 258, 259, 266, 283, 305, 306, 313, 330
/html/modules/coppermine/db_input.php (rev 9.10): lines 79, 81, 263, 267
/html/modules/coppermine/delete.php (rev 9.1): lines 218, 247
/html/modules/coppermine/editpics.php (rev 9.1): line 157
/html/modules/coppermine/groupmgr.php (rev 9.1): line 116
/html/modules/coppermine/ratepic.php (rev 9.0): line 70
by: DJMaze
Correct it's not according SQL99 ISO
But did you read the SQL-2003 ISO to check if this is allowed ?
next update is scheduled for 2007
by: Brennor
Thanks for the link to the draft version of the upcoming standard.

According to page 849 (873 in the 5WD-02-Foundation-2003-09.pdf file), UPDATE won't have a LIMIT clause available. However, I can't find LIMIT called out in ANY page in that document. From what I can tell, it looks like things will be done with a sliding window that you can size as needed.

Then again, the syntax for the featureset is always determined by the individual database creators and not necessarily by the standard. So, even though LIMIT does not appear in the standard, doesn't mean it won't be used at all.

But, right now, LIMIT doesn't appear usable with UPDATE in the upcoming SQL standard.
by: DJMaze
Does Postgres produce a error when LIMIT is used ?
by: Brennor
Yes it does:

Warning line 74: pg_query(): Query failed: ERROR: syntax error at or near "LIMIT" at character 108

Which means that the updates don't happen. Just removing the "LIMIT 1" fixes those problems, and should work for MySQL as well.

The only worry comes with the possibility that more than one row could be returned with the WHERE clause. So far, all of the ones that I have seen have only been able to return one row (ie: where includes user id).
by: DJMaze
This bug has been fixed in the CVS.

Snapshots of the sources are packaged every 6 hours; this change
will be in the next snapshot. You can grab the snapshot at the
Downloads section.

Thank you for the report, and for helping us to make CPG-Nuke CVS for 9.0 better.
User Info

Welcome Anonymous


Support for DragonflyCMS in a other languages: