MySQL Forums
Forum List  »  General

Query works in 5.5 but not 5.6
Posted by: Anthony Tso
Date: October 10, 2013 03:59AM

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

Options: ReplyQuote


Subject
Written By
Posted
Query works in 5.5 but not 5.6
October 10, 2013 03:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.