Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view

[Elena Stepanova]

The following query

SELECT MAX(a)
  FROM ( SELECT * FROM t1 ) AS alias
  WHERE (1,2) IN ( SELECT 3,4 );

returns an empty set if it is executed with derived_merge=ON (current default), and NULL otherwise.
NULL is correct.

bzr version-info
revision-id: <email address hidden>
date: 2011-12-14 04:56:54 +0400
build-date: 2011-12-14 20:55:23 +0400
revno: 3349
branch-nick: maria-5.3

Also reproducible on revno 3250.

EXPLAIN with derived_merge=ON (wrong result):

1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where <in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))

EXPLAIN with derived_merge=OFF (correct result):

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 DERIVED t1 index NULL a 4 NULL 2 100.00 Using index

select max(`alias`.`a`) AS `MAX(a)` from (select `test`.`t1`.`a` AS `a` from `test`.`t1`) `alias` where <expr_cache><1,2>(<in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4)))))

Minimal optimizer_switch: derived_merge=ON (by default)

Full optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

Test case:

SET optimizer_switch='derived_merge=on';

CREATE TABLE t1 ( a int(11) NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (11);

SELECT MAX(a)
  FROM ( SELECT * FROM t1 ) AS alias
  WHERE (1,2) IN ( SELECT 3,4 );

[Elena Stepanova (elenst)]

As Igor pointed out, the problem is reproducible without a derived table, with a view:

CREATE TABLE t1 ( a int(11) NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (10),(11);
CREATE VIEW v AS SELECT * FROM t1;

SELECT MAX(a) FROM v WHERE (1,2) IN ( SELECT 3,4 );

Reproducible on 5.2.10 and 5.1.60, and on MySQL 5.1.60.

[Elena Stepanova (elenst)]

EXPLAIN for the variant with the view (on 5.3):

EXPLAIN EXTENDED SELECT MAX(a) FROM v WHERE (1,2) IN ( SELECT 3,4 );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL No tables used

select max(`test`.`t1`.`a`) AS `MAX(a)` from `test`.`t1` where <in_optimizer>((1,2),<exists>(select 3,4 having (((1 = 3) or isnull(3)) and ((2 = 4) or isnull(4)) and <is_not_null_test>(3) and <is_not_null_test>(4))))

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It is possible repeat the problem with one table and just non-constant where:

CREATE TABLE t1 ( a int(11) NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (10),(11);
CREATE VIEW v AS SELECT * FROM t1;

SELECT MAX(a) FROM v WHERE rand()*0;

drop view v;
drop table t1;

It returns empty line instead of NULL also.

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It looks like view is not important:

CREATE TABLE t1 ( a int(11) NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (10),(11);

SELECT MAX(a) FROM t1 WHERE rand()*0;

drop table t1;

Produce following:
CREATE TABLE t1 ( a int(11) NOT NULL, KEY(a) );
INSERT INTO t1 VALUES (10),(11);
SELECT MAX(a) FROM t1 WHERE rand()*0;
MAX(a)
drop table t1;

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

It is important that tables are optimized off...

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

To be executed correctly execution should avoid following if branch in JOIN::exec

if (!tables_list && (tables || !select_lex->with_sum_func))

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

opt_sum_query finds max() by index, then above IF branch returns EOF because where condition is FALSE if skip the branch then result is correct.

[Igor Babaev (igorb-seattle)]

Since it was shown that this problem does not depend on using derived tables or views the bug #879864 can be considered as a duplicate of this bug.