List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 8 2003 4:04pm
Subject:Re: update bug with "limit" syntax - MySQL Ver 4.011
View as plain text  
At 9:45 -0600 3/8/03, Mark Matthews wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>miniwar@stripped wrote:
>>>Description:
>>
>>	It is unbelievable that the MySQL ver 4.0 have so many bug, I 
>>have been reported 2 bugs just a few days ago.
>>Now, I have found a bug again.
>>
>>The bug is :
>>When I execute "select * from old_topic where FID=4 and (page=0 or 
>>page=167) order by replytime asc limit 40;" returns 40 rows[40 rows 
>>in set (0.01 sec)]
>>23 rows value of the column "page=167", and and 17 are "page=0".
>>
>>Then I execute "update old_topic set page=167 where FID=4 and 
>>(page=0 or page=167) order by replytime asc limit 40;"
>>##########
>>mysql> update old_topic set page=167 where FID=4 and (page=0 or 
>>page=167) order by replytime asc limit
> 40;             
>>Query OK, 40 rows affected (7.75 sec)
>>Rows matched: 61  Changed: 40  Warnings: 0
>>#########
>>
>>It tells that 40 rows are updated, but it is incorrect, as there 
>>should only 17 rows are affected because the "limit 40"
>>
>>############
>>mysql> select count(*) from old_topic where FID=4 and page=167;
>>+----------+
>>| count(*) |
>>+----------+
>>|       61 |
>>+----------+
>>1 row in set (0.01 sec)
>>############
>[snip]
>
>Hi!
>
>I think this is a misunderstanding of how/why LIMIT applies to 
>updates. It's not exactly the same as when used for SELECTs. The 
>limit applies to the number of rows _changed_, not examined. Here's 
>the relevant section from the manual:
>
>http://www.mysql.com/doc/en/UPDATE.html
>
>Paul DuBois will probably correct me if I'm wrong, but if you want 
>to have the behavior you expect, you will either have to do it from 
>your program based on the SELECT you have issued, or use MySQL-4.1 
>which has subqueries.

I'm with Mark.  A LIMIT clause in an UPDATE statement limits the number
of records updated, just as a LIMIT clause in a DELETE statement limits
the number of records deleted.  This is consistent with a LIMIT in a SELECT
statement, which does not limit the number of records selected by the
WHERE clause, but the number of those records that actually are returned
to the client.

There is one subtlety here: If you set a value to the value it currently
has, that is not considered an update, and thus does not count against
the limit.

Note that all aspects of the behavior of LIMIT with UPDATE may easily be
discovered with a little experimentation.

>
>	-Mark
>- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
>
>For technical support contracts, visit https://order.mysql.com/?ref=mmma
>
>     __  ___     ___ ____  __
>    /  |/  /_ __/ __/ __ \/ /  Mark Matthews <mark@stripped>
>   / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
>  /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
>         <___/ www.mysql.com

Thread
update bug with "limit" syntax - MySQL Ver 4.011miniwar8 Mar
  • Re: update bug with "limit" syntax - MySQL Ver 4.011Mark Matthews8 Mar
    • Re: update bug with "limit" syntax - MySQL Ver 4.011Paul DuBois8 Mar