Wrong empty result for MIN/MAX without GROUP BY filtered by an empty subquery

[Timour Katchaounov]

The following test case subselect_mat.test:
"BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT"
produces an incorrect empty result for MIN/MAX, while it should produce a NULL
according to the SQL standard.

[Timour Katchaounov (timour)]

This bug is also present in the latest MySQL server as:
http://bugs.mysql.com/bug.php?id=40037

[Timour Katchaounov (timour)]

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.
# 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);
# 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);

# 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);
# 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);
# Executed with semi-join. Notice, this time we get a different result (NULL).
# This is the only correct result of all five queries. This difference is
# 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;

[Igor Babaev (igorb-seattle)]

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)