Re: Wrong empty result for MIN/MAX without GROUP BY filtered by an empty subquery
This bug is also present in the latest MySQL server as:
http://bugs.mysql.com/bug.php?id=40037

Re: Wrong empty result for MIN/MAX without GROUP BY filtered by an empty subquery
Test case extracted from subselect_mat.test:

create table t1 (a1 int key);
create table t2 (b1 int);
insert into t1 values (5);
– echo Only the last query returns correct result. Filed as BUG#40037.

1. Query with group by, executed via materialization
   set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
   explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
   select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
2. Query with group by, executed via IN=>EXISTS
   set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
   explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
   select min(a1) from t1 where 7 in (select b1 from t2 group by b1);

1. Executed with materialization
   set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
   explain select min(a1) from t1 where 7 in (select b1 from t2);
   select min(a1) from t1 where 7 in (select b1 from t2);
2. Executed via IN=>EXISTS
   set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off';
   explain select min(a1) from t1 where 7 in (select b1 from t2);
   select min(a1) from t1 where 7 in (select b1 from t2);
3. Executed with semi-join. Notice, this time we get a different result (NULL).
4. This is the only correct result of all five queries. This difference is
5. filed as BUG#40037.
   set @@optimizer_switch='materialization=off,in_to_exists=off,semijoin=on';
   explain select min(a1) from t1 where 7 in (select b1 from t2);
   select min(a1) from t1 where 7 in (select b1 from t2);
   drop table t1,t2;

Re: Wrong empty result for MIN/MAX without GROUP BY filtered by an empty subquery
The following simple test case constructed by Timour demonstrates this problem:

CREATE TABLE t1 (a int, b int, KEY (b));
INSERT INTO t1 VALUES (3,1), (4,2);
CREATE TABLE t2 (a int);
INSERT INTO t2 VALUES (7), (8);

MariaDB [test]> SELECT MAX(t1.b) AS max_res FROM t1 WHERE (9) IN (SELECT a FROM t2)
Empty set (0.00 sec)

Launchpad bug id: 702301