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