Activity log for bug #1008773

Date Who What changed Old value New value Message
2012-06-04 22:58:04 Elena Stepanova bug added bug
2012-06-04 22:58:16 Elena Stepanova summary Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING; crash on 5.2 Wrong result (NULL instead of a value) with no matching rows, subquery in FROM and HAVING
2012-06-04 23:04:58 Elena Stepanova description 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 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. t2 can have more rows too, in which case a subquery should use an aggregate function, e.g. SUM. 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. # Either t2 has 1 row, or an aggregate function, e.g. SUM, is used in the subquery. 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
2012-06-05 11:11:21 Timour Katchaounov maria: status New In Progress
2012-06-05 12:42:04 Timour Katchaounov maria: status In Progress Fix Committed
2012-06-14 14:04:54 Timour Katchaounov maria: status Fix Committed Fix Released
2012-06-14 14:05:19 Launchpad Janitor branch linked lp:maria/5.3