The query returns a row in 5.5 but no rows in 5.6. It is derived from Magento advanced search which works in 5.5 but not 5.6
Fiddle here:
http://sqlfiddle.com/#!9/e5f28/7
* Schema:
create table `catalog_product_index_price`
(
`entity_id` int(10) unsigned not null default '0',
primary key (`entity_id`)
) engine=InnoDB default charset=utf8;
insert into `catalog_product_index_price` values (1), (2);
create table `catalog_product_entity_varchar`
(
`value_id` int(11) not null auto_increment,
`entity_id` int(10) unsigned not null,
`store_id` smallint(5) unsigned not null,
`value` varchar(255) default null,
primary key (`value_id`)
) engine=InnoDB default charset=utf8;
insert into `catalog_product_entity_varchar` values
(1, 1, 0, 'test');
* Query
select price_index.* from catalog_product_index_price as price_index where (`price_index`.`entity_id` IN(SELECT `t1`.`entity_id` FROM `catalog_product_entity_varchar` AS `t1` LEFT JOIN `catalog_product_entity_varchar` AS `t2` ON t1.entity_id = t2.entity_id AND t2.store_id=1 WHERE t1.store_id = 0 AND (t1.entity_id = price_index.entity_id) AND (IF(t2.value_id>0, t2.value, t1.value) = 'test')));