Re: update bug with limit syntax - MySQL Ver 4.011
-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
Re: update bug with limit syntax - MySQL Ver 4.011
At 9:45 -0600 3/8/03, Mark Matthews wrote: -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. 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 [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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
Re: UPDATE bug
On Wed, Jul 04, 2001 at 07:16:50PM -0500, [EMAIL PROTECTED] wrote: Ok, I know I submitted an earlier bug report about this, but I've actually had it happen from the mysql monitor. Essentially, UPDATE queries are executing, but not actually updating, unless I SELECT data from the table first. How-To-Repeat: Simple as that. I have yet to notice any particular pattern. It happens for some queries, on some tables, some of the time. Can you try MySQL 3.23.41? What table type are you using? MyISAM? System: FreeBSD xtabi.carspot.com 4.2-RELEASE FreeBSD 4.2-RELEASE #4: Sat Jun 2 22:42:12 CDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/XTABI i386 Ugh. You really need to run FreeBSD 4.3 if you want to avoid nasty threading problems. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 1 days, processed 41,358,529 queries (261/sec. avg) - 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
Re: Update bug?
On 2001 Apr 13, Maciek Dobrzanski [EMAIL PROTECTED] wrote: | fd_10 | varchar(20) | | MUL | | | | fd_11 | varchar(20) | | | | | Now when I do this update: UPDATE test SET fd_11='value' WHERE fd_10='some_value' it usually executes in 0.00 sec But when the query looks like this: UPDATE test SET fd_11='value' WHERE fd_10=some_value It takes about 2 seconds to execute. This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. In the second query it has to check for rows where fd_10 is: '10' '010' '0010' ... If you're storing numbers, use a numerical column. If you're testing a varchar field, use a string. Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ www.mysql.com - 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
Re: Update bug?
This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. I thought that maybe MySQL should check the field type and do the conversion to string. - 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
Re: Update bug?
On 2001 Apr 13, Maciek Dobrzanski [EMAIL PROTECTED] wrote: This is because with the first query it can use the index. With the second query, it has to check the whole table. Why? Because obviously you're using numbers. And let's make some_value == 10. I thought that maybe MySQL should check the field type and do the conversion to string. To which string? '10' or ' 10' or '010', ...? One thing that might work in this case is to only scan the ranges ['0' - '1'], [' ' - '!'] and an exact match. Something like that. But it's messy, and best to fix the application. It would be nice if MySQL could optimize even the worst queries, though. Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Development Team /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ www.mysql.com - 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
