Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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 );
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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))))
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
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.
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
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;
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
It is important that tables are optimized off...
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
To be executed correctly execution should avoid following if branch in JOIN::exec
if (!tables_list && (tables || !select_lex->with_sum_func))
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
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.
Re: Wrong empty result instead of NULL for MAX/MIN with derived_merge=ON or with a view
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.
Launchpad bug id: 904345
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.