List:General Discussion« Previous MessageNext Message »
From:toasty Date:October 27 2002 9:17pm
Subject:LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing results
View as plain text  
>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     toasty@stripped:/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
Thread
LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing resultstoasty28 Oct
  • re: LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing resultsVictoria Reznichenko28 Oct
RE: LEFT JOIN selects with ORDER BY and WHERE and LIMITmissing resultsKevin Day5 Nov
  • re: RE: LEFT JOIN selects with ORDER BY and WHERE and LIMIT missing resultsEgor Egorov8 Nov