Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.25
-
None
-
None
Description
The following query
SELECT ta.* FROM t1 AS ta, t1 AS tb
|
WHERE ( tb.b != ta.b OR tb.a = ta.a )
|
AND ( tb.b = ta.c OR tb.b = ta.b );
|
on test data returns 2 rows with index_merge=ON and index_merge_intersection=ON, and 3 rows otherwise. 3 rows is the correct result.
bzr version-info
revision-id: monty@askmonty.org-20120627141312-z65pj80390f0f5pp
|
date: 2012-06-27 17:13:12 +0300
|
build-date: 2012-07-02 05:43:00 +0400
|
revno: 3460
|
Could not reproduce on MariaDB 5.3 or MySQL trunk.
Minimal optimizer_switch:
index_merge=on,index_merge_intersection=on
|
Full optimizer_switch (default):
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
|
EXPLAIN (with the minimal optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE ta ALL a,b NULL NULL NULL 3 100.00
|
1 SIMPLE tb ALL a,b NULL NULL NULL 3 100.00 Range checked for each record (index map: 0x3)
|
Warnings:
|
Note 1003 select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b`,`test`.`ta`.`c` AS `c` from `test`.`t1` `ta` join `test`.`t1` `tb` where (((`test`.`tb`.`b` <> `test`.`ta`.`b`) or (`test`.`tb`.`a` = `test`.`ta`.`a`)) and ((`test`.`tb`.`b` = `test`.`ta`.`c`) or (`test`.`tb`.`b` = `test`.`ta`.`b`)))
|
Test case:
|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (
|
a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b)
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d');
|
|
SET optimizer_switch = 'index_merge=on,index_merge_intersection=on';
|
|
SELECT ta.* FROM t1 AS ta, t1 AS tb
|
WHERE ( tb.b != ta.b OR tb.a = ta.a )
|
AND ( tb.b = ta.c OR tb.b = ta.b );
|
|
Expected result:
a b c
|
-----------------
|
8 v v
|
8 m m
|
9 d d
|
Actual result:
a b c
|
-----------------
|
8 v v
|
8 m m
|