List:General Discussion« Previous MessageNext Message »
From:lknecht Date:January 30 2004 1:05pm
Subject:ORDER BY may remove result rows
View as plain text  
>Description:
Instead of ordering rows, ORDER BY may remove rows
>How-To-Repeat:
CREATE TABLE T (
  Id INTEGER NOT NULL,
  Id2 INTEGER NOT NULL,
  Val FLOAT NOT NULL,
  Id3 INTEGER NOT NULL,
  KEY Key2 (Id2, Val),
  KEY Key3 (Id3)
);

INSERT INTO T VALUES(1,3,-12,4);
INSERT INTO T VALUES(2,2,-27.5,4);
INSERT INTO T VALUES(3,2,18.4,3);

mysql> SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20;
+-----+
| Id3 |
+-----+
|   4 |
|   3 |
+-----+
2 rows in set (0.00 sec)

mysql> SELECT Id3 FROM T WHERE Id2=2 AND Val BETWEEN -30 AND 20 ORDER BY Id3;
+-----+
| Id3 |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

Note: both indices Key2 and Key3 must be present for the bug to occur.

>Fix:
	

>Submitter-Id:	<submitter ID>
>Originator:	Lukas Knecht
>Organization:
 EraGen Biosciences Inc.
>MySQL support: licence
>Synopsis:	ORDER BY may remove result rows
>Severity:	critical
>Priority:	high
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-4.1.1-alpha-max (Official MySQL-max binary)

>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
	
System: Linux barks 2.4.18-4GB #1 Wed Dec 17 18:08:38 UTC 2003 i686 unknown
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/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc'  CFLAGS='-O2 -mpentiumpro -DBIG_TABLES'  CXX='gcc' 
CXXFLAGS='-O2 -mpentiumpro -felide-constructors -DBIG_TABLES'  LDFLAGS=''  ASFLAGS=''
LIBC: 
-rwxr-xr-x    1 root     root      1394302 Mar 27  2003 /lib/libc.so.6
-rw-r--r--    1 root     root     25362104 Mar 27  2003 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Mar 23  2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin'
'--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex'
'--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile'
'--enable-assembler' '--disable-shared' '--with-berkeley-db' '--with-raid'
'--with-readline' '--with-embedded-server' '--with-innodb' 'CFLAGS=-O2 -mpentiumpro
-DBIG_TABLES' 'CXXFLAGS=-O2 -mpentiumpro -felide-constructors -DBIG_TABLES' 'CXX=gcc'

Thread
ORDER BY may remove result rowslknecht30 Jan
  • Re: ORDER BY may remove result rowsDan Nelson31 Jan
  • Re: ORDER BY may remove result rowsVictoria Reznichenko2 Feb