[#MDEV-2650] LP:904345 - Wrong empty result instead of NULL for MAX/MIN with derived

[MDEV-2650] LP:904345 - Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view Created: 2011-12-14 Updated: 2015-02-02 Resolved: 2012-10-04
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

Type:

Bug

Priority:

Major

Reporter:

Elena Stepanova

Assignee:

Igor Babaev

Resolution:

Fixed

Votes:

Labels:

Launchpad

Attachments:

LPexportBug904345.xml

Description

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

Comments

Comment by Elena Stepanova [ 2011-12-14 ]

Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON
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.

Comment by Elena Stepanova [ 2011-12-14 ]

Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
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