aggregate function bug w/ table join
am 04.03.2003 19:16:54 von pd>Description:
When doing a cross join on two tables, one with X rows > 0, and one with 0
rows, with no WHERE clause, and a MAX(something) in the field list, mysql
returns the actual max value rather than NULL. NULL is correct since the
cross join of the two tables is 0 rows.
>How-To-Repeat:
Here is the test case:
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;
and here is the expected results:
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
The majority of the select statements are just leading up to the problem, and the
last one shows that if you add a where clause, mysql doesn't trust the stats, and
will indeed calculate the max value, which turns out to be NULL.
>Fix:
unknown, it would seem to me that this is an optimization using an internal
statistic on the tables, when there isn't a where clause, to filter the
records by, adding the case where the resulting query has 0 rows, and not
using the internal stats, would be an appropriate change
>Submitter-Id: n/a
>Originator: Paul DeMarco
>Organization: n/a
>MySQL support: none
>Synopsis: aggregate function bug w/ table join
>Severity: non-critical
>Priority: low
>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 bugs-thread13896@lists.mysql.com
To unsubscribe, e-mail