magento/magento2 — pull request #565 by jason-neumann (2014-05-01 18:40:03+00:00)
Title: Magento\CatalogSearch\Model\Query::getResultCollection() not working

When searching for products using the CatalogSearch module, if the site is setup in a single store configuration, calling getResultCollection() was returning no results for me. This is the query it was using:
```
SELECT
`e` . *,
`price_index`.`price`,
`price_index`.`tax_class_id`,
`price_index`.`final_price`,
IF(price_index.tier_price IS NOT NULL,
LEAST(price_index.min_price,
price_index.tier_price),
price_index.min_price) AS `minimal_price`,
`price_index`.`min_price`,
`price_index`.`max_price`,
`price_index`.`tier_price`
FROM
`catalog_product_entity` AS `e`
INNER JOIN
`catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id
AND product_website.website_id = '1'
INNER JOIN
`catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
AND price_index.website_id = '1'
AND price_index.customer_group_id = 0
WHERE
(`e`.`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 t1.attribute_id = t2.attribute_id
AND t2.store_id = 1
WHERE
(t1.attribute_id IN ('71'))
AND (t1.store_id = 0)
AND (IF(t2.value_id > 0, t2.value, t1.value) LIKE '%balloon%')));
```
The last line of the query was failing. Since t2 is joined with the condition "AND store_id = 1" and no rows in catalog_product_entity_varchar have store_id=1, the value of t2.value_id is NULL. The results of NULL > 0 is UNKNOWN. This caused the query to return no results. Changing the code to IFNULL(t2.value, t1.value) made it work for me.