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

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

index_merge=on,index_merge_intersection=on

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

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

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

a	b	c

-----------------

8	v	v

8	m	m

9	d	d

a	b	c

-----------------

8	v	v

8	m	m