with 0 rows in t1 and 1 row in t2 produces NULL, NULL on maria/5.3 and maria/5.5. Same happens if t1 is not empty, but the outer SELECT has an impossible WHERE condition.
The same query without t2 produces NULL, 1;
SELECT MAX(a), ( SELECT 1 ) AS bb FROM t1
maria-5.1, maria-5.2, mysql-5.1, mysql-5.5, mysql-trunk, postgres-8.4.7 return NULL, 1 for both queries.
Reproducible with MyISAM and Aria, but not InnoDB.
Reproducible with the default optimizer_switch as well as with all OFF values (except for in_to_exists required to execute the query).
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select max(NULL) AS `MAX(a)`,(select 1 from dual) AS `bb` from `test`.`t1`
# Test case:
SET optimizer_switch='in_to_exists=on';
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1);
# Either t1 is empty, or SELECT has an impossible condition
SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
The following query
SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1
with 0 rows in t1 and 1 row in t2 produces NULL, NULL on maria/5.3 and maria/5.5. Same happens if t1 is not empty, but the outer SELECT has an impossible WHERE condition.
The same query without t2 produces NULL, 1;
SELECT MAX(a), ( SELECT 1 ) AS bb FROM t1
maria-5.1, maria-5.2, mysql-5.1, mysql-5.5, mysql-trunk, postgres-8.4.7 return NULL, 1 for both queries.
Reproducible with MyISAM and Aria, but not InnoDB.
Reproducible with the default optimizer_switch as well as with all OFF values (except for in_to_exists required to execute the query).
EXPLAIN (with in_to_exists=on, everything else OFF):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 const row not found
2 SUBQUERY t2 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select max(NULL) AS `MAX(a)`,(select 1 from dual) AS `bb` from `test`.`t1`
# Test case:
SET optimizer_ switch= 'in_to_ exists= on';
CREATE TABLE t1 (a INT) ENGINE=MyISAM;
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1);
# Either t1 is empty, or SELECT has an impossible condition
SELECT MAX(a), ( SELECT 1 FROM t2 ) AS bb FROM t1;
# End of test case
# Expected result:
#
# MAX(a) bb
# NULL 1
# Actual result:
#
# MAX(a) bb
# NULL NULL