Bug #123 Aggregate function bug with cartesian product

• Submitted: 5 Mar 2003 6:36
• Modified: 5 Mar 2003 6:40
• Reporter: Alexander Keremidarski
• Status: Won't fix
• 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