>Description:
Under some pretty specific conditions, adding a "LIMIT" clause to a query
will cause incorrect results.
>How-To-Repeat:
This bug is NOT present in 4.0.3.
CREATE TABLE `history` (
`id_pic` int(11) unsigned NOT NULL auto_increment,
`id_cam` smallint(11) unsigned NOT NULL default '0',
`time` int(14) unsigned NOT NULL default '0',
`votes` smallint(3) NOT NULL default '0',
`score` mediumint(3) NOT NULL default '0',
`avgscore` double NOT NULL default '-65534',
`id_show` smallint(11) NOT NULL default '0',
`views` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`id_pic`),
KEY `avgscore` (`avgscore`),
KEY `id_cam` (`id_cam`),
KEY `id_show` (`id_show`),
KEY `votes` (`votes`),
KEY `camshow` (`id_cam`,`id_show`)
) TYPE=MyISAM
CREATE TABLE `cam_update` (
`id_cam` int(11) NOT NULL auto_increment,
`status` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id_cam`),
KEY `status` (`status`)
) TYPE=MyISAM PACK_KEYS=1
mysql> SELECT history.* FROM history LEFT JOIN cam_update ON
history.id_cam=cam_update.id_cam WHERE history.votes > 10 AND (cam_update.status=2)
ORDER BY avgscore desc limit 0,13;
Empty set (0.14 sec)
mysql> SELECT history.* FROM history LEFT JOIN cam_update ON
history.id_cam=cam_update.id_cam WHERE history.votes > 10 AND (cam_update.status=2)
ORDER BY avgscore limit 0,13;
+--------+--------+------------+-------+-------+--------------------+---------+-------+
| id_pic | id_cam | time | votes | score | avgscore | id_show | views |
+--------+--------+------------+-------+-------+--------------------+---------+-------+
| 388402 | 520 | 1017892232 | 31 | -65 | -0.185185185185185 | 0 | 0 |
+--------+--------+------------+-------+-------+--------------------+---------+-------+
1 row in set (0.15 sec)
mysql> SELECT history.* FROM history LEFT JOIN cam_update ON
history.id_cam=cam_update.id_cam WHERE history.votes > 10 AND (cam_update.status=2)
ORDER BY avgscore limit 0,100;
+--------+--------+------------+-------+-------+--------------------+---------+-------+
| id_pic | id_cam | time | votes | score | avgscore | id_show | views |
+--------+--------+------------+-------+-------+--------------------+---------+-------+
| 388402 | 520 | 1017892232 | 31 | -65 | -0.185185185185185 | 0 | 0 |
| 389682 | 520 | 1017909724 | 50 | -45 | 0.05 | 0 | 0 |
| 316393 | 520 | 1015659458 | 12 | -29 | 0.338709677419355 | 0 | 0 |
+--------+--------+------------+-------+-------+--------------------+---------+-------+
3 rows in set (0.21 sec)
mysql> SELECT history.* FROM history LEFT JOIN cam_update ON
history.id_cam=cam_update.id_cam WHERE history.votes > 10 AND (cam_update.status=2)
ORDER BY avgscore;
+--------+--------+------------+-------+-------+--------------------+---------+-------+
| id_pic | id_cam | time | votes | score | avgscore | id_show | views |
+--------+--------+------------+-------+-------+--------------------+---------+-------+
<snip>
847 rows in set (0.72 sec)
>Fix:
>Submitter-Id:
>Originator: Kevin Day
>Organization:
Stile Project
>MySQL support: none
>Synopsis: LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing results
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.4-beta (Source distribution)
>Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.4-beta, for
>unknown-freebsdelf4.7 on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.4-beta-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 10 hours 52 min 32 sec
Threads: 197 Questions: 8633502 Slow queries: 0 Opens: 5374 Flush tables: 1 Open
tables: 73 Queries per second avg: 220.512
>Environment:
System: FreeBSD server12.stileproject.com 4.7-RELEASE FreeBSD 4.7-RELEASE #0: Sun Oct
27 02:20:13 CST 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SERVER
i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.4 20020320 [FreeBSD]
Compilation info: CC='gcc' CFLAGS='-march=pentiumpro -mcpu=pentiumpro -O6
-fomit-frame-pointer -fexpensive-optimizations' CXX='g++'
CXXFLAGS='-march=pentiumpro -mcpu=pentiumpro -O6 -fomit-frame-pointer
-fexpensive-optimizations' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1218496 Oct 9 07:43 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Oct 10 15:54 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 574916 Oct 9 07:43 /usr/lib/libc.so.4
Configure command: ./configure --without-isam --enable-assembler --with-berkeley-db
--with-mysqld-ldflags=--static 'CFLAGS=-march=pentiumpro -mcpu=pentiumpro -O6
-fomit-frame-pointer -fexpensive-optimizations' 'CXXFLAGS=-march=pentiumpro
-mcpu=pentiumpro -O6 -fomit-frame-pointer -fexpensive-optimizations'
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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
