Bug #123 Aggregate function bug with cartesian product
Submitted: 5 Mar 2003 6:36 Modified: 5 Mar 2003 6:40
Reporter: Alexander Keremidarski Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23 OS:Any (all)
Assigned to: Alexander Keremidarski CPU Architecture:Any

[5 Mar 2003 6:36] Alexander Keremidarski
Description:
When using aggregation functions with cross join where one of tables is empty for    first column of first table MySQL returns not NULL as it should.

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);

mysql> select max(t1.id), max(t1.val), max(t2.id), max(t2.val) from t1, t2;
+------------+-------------+------------+-------------+
| max(t1.id) | max(t1.val) | max(t2.id) | max(t2.val) |
+------------+-------------+------------+-------------+
|          2 |        NULL |       NULL |        NULL |
+------------+-------------+------------+-------------+

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
[5 Mar 2003 6:40] Alexander Keremidarski
This is fixed in 4.0.12, but will not be fixed in 3.23