MySQL Bugs: #70608: This query returns a row in 5.5 but not 5.6 or current 5.7

Bug #70608	This query returns a row in 5.5 but not 5.6 or current 5.7
Submitted:	12 Oct 2013 1:59	Modified:	8 Jan 2014 18:07
Reporter:	Anthony Tso	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.6, 5.7

Description:
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

Also happening on current version of 5.7

How to repeat:
The query returns a row in 5.5 but no rows in 5.6 or 5.7. 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')));

Related stackoverflow question here:

http://stackoverflow.com/questions/19287023/why-does-this-work-in-mysql-5-5-but-not-mysql-...

Hello Anthony,
 
Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

This is confirmed to be a problem with an IF predicate in the WHERE clause combined with OUTER JOIN in a subquery that is transformed to a semi-join.
A workaround is to disable semi-join for this query:
set optimizer_switch='semijoin=off';

Noted in 5.6.16, 5.7.4 changelogs.

In incorrect result could be returned for a query with an IF()
predicate in the WHERE clause combined with OUTER JOIN in a subquery
that is transformed to a semi-join. (A workaround is to disable
semi-join using SET optimizer_switch='semijoin=off';)

5.6$ bzr log -r 5681
------------------------------------------------------------
revno: 5681
committer: Roy Lyseng <roy.lyseng@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2013-12-11 09:59:30 +0100
message:
  Bug#17600176: This query returns a row in 5.5 but not 5.6 or current 5.7
  
  The problem query contains an IN subquery that is transformed to
  a semi-join. The subquery contains an outer join operation.
  When run standalone, the outer join in the subquery is preserved as an
  outer join and returns one row. However, when the full query is run,
  the outer join is converted into an inner join, and the subquery no
  longer returns any row. This causes the outer query to return no rows.
  
  The problem is with the IF clause in the WHERE clause of the subquery,
  When transforming into semi-join, we rely on Item::fix_after_pullout()
  to adjust used_tables and not_null_tables information within the
  condition objects, in order to determine e.g. the outer join to inner
  join transform. However, there is no specific implementation of
  ::fix_after_pullout() for the IF clause, so it returns generic
  information generated by Item_func::fix_after_pullout(). The fix is to
  add this function.
  
  By analysis, this appears to be a problem for BETWEEN predicates and
  IN predicates too. A specific implementation of :;fix_after_pullout()
  is added for both classes.
  
  In addition, it was detected that not_null_tables information was not
  updated correctly for class Item_row. However, I was not able to think
  out any failing test for this problem, so no test case was added.