>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)
############
It is a bug of MySQL server, or I am miss-understanding of the "limit" in the update
command?
CREATE TABLE `old_topic` (
`FID` smallint(5) unsigned NOT NULL default '0',
`page` smallint(5) unsigned NOT NULL default '0',
`MGID` mediumint(8) unsigned NOT NULL auto_increment,
`status`
enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20')
NOT NULL default '1',
`clicks` smallint(5) unsigned NOT NULL default '0',
`topic` char(80) NOT NULL default '',
`nickname` char(20) binary NOT NULL default '',
`uid` mediumint(8) unsigned NOT NULL default '0',
`no_of_reply` smallint(5) unsigned NOT NULL default '0',
`last_replyer` char(20) binary NOT NULL default '',
`replytime` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`MGID`),
KEY `uid` (`uid`),
KEY `FID` (`FID`,`page`,`replytime`),
FULLTEXT KEY `topic` (`topic`)
) TYPE=MyISAM
>How-To-Repeat:
>Fix:
>Submitter-Id: [EMAIL PROTECTED]
>Originator: root
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: update bug with "limit" syntax
>Severity:
>Priority:
>Category: mysql
>Class:
>Release: mysql-4.0.11-gamma (Official MySQL RPM)
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux ip-83-99-134-202.rev.dyxnet.com 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47
EDT 2002 i686 i686 i386 GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking
--host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit
Thread model: posix
gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='g++'
CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 14 Jan 23 11:25 /lib/libc.so.6 ->
libc-2.2.93.so
-rwxr-xr-x 1 root root 1235468 Sep 6 2002 /lib/libc-2.2.93.so
-rw-r--r-- 1 root root 2233342 Sep 6 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Sep 6 2002 /usr/lib/libc.so
Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static'
'--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb'
'--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server'
'--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CFLAGS=-O6
-fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium'
---------------------------------------------------------------------
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
