>Description:
It appears that when an aggregate like MAX is used in a query, mysql will put
any
values in that it already has from internal statistics, however, this is wrong
if
the query being executed results in zero records, in this example, the MAX of
zero
values is NULL, however mysql continues to report the real MAX.
>How-To-Repeat:
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
val int (11) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE t2 (
id int(11) NOT NULL auto_increment,
val int(11) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO t1 (val) VALUES (1);
INSERT INTO t1 (val) VALUES (2);
SELECT MAX(id), MAX(val) from t1;
SELECT MAX(id), MAX(val) from t2;
SELECT MAX(t1.id), MAX(t2.id) from t1, t2;
SELECT MAX(t1.val), MAX(t2.val) from t1, t2;
SELECT MAX(t1.id), MAX(t2.id) from t1, t2 WHERE t1.id < 2;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
Some of the selects are for completeness, the key one is the third, since t2
contains no rows, the cross between t1 and t2 will be empty, but the query
returns the real MAX value. Furthermore, when introducing the WHERE clause
in the fifth query, it causes mysql to return the correct MAX value. This
might indicate an optimization that was done:
* if no where clause exists, then internal statistics on MAX value
would be accurate, therefore, substitute this data for query results,
however, this does not hold when there are no rows in the query
In the format necessary for mysql test cases, the expected test output is below:
MAX(id) MAX(val)
2 2
MAX(id) MAX(val)
NULL NULL
MAX(t1.id) MAX(t2.id)
NULL NULL
MAX(t1.val) MAX(t2.val)
NULL NULL
MAX(t1.id) MAX(t2.id)
NULL NULL
>Fix:
unknown
>Submitter-Id: [EMAIL PROTECTED]
>Originator: Paul DeMarco
>Organization:
>MySQL support: none
>Synopsis: invalid aggregate function value for 0 row results (from a full join)
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.55-max (Official MySQL-max binary)
>Environment:
System: Linux stevenite.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 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/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-98)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2
-mcpu=pentiumpro -felide-constructors' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 May 20 2002 /lib/libc.so.6 -> libc-2.2.4.so
-rwxr-xr-x 1 root root 1285788 Apr 2 2002 /lib/libc-2.2.4.so
-rw-r--r-- 1 root root 27332668 Apr 2 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 2 2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql' '--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-client-ldflags=-all-static'
'--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro'
'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'
---------------------------------------------------------------------
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
