Re: update bug with "limit" syntax - MySQL Ver 4.011

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[EMAIL PROTECTED] 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.

	-Mark
- -- 
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/

For technical support contracts, visit https://order.mysql.com/?ref=mmma

    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Mark Matthews <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
 /_/  /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
        <___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+ahAstvXNTca6JD8RAlJ8AJ4xOpcLH2PO6ImTcZ/CuMYu/+vnPgCfas+z
3zwNaD/bQFe/7yIU6at1Nfw=
=2tMz
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php