search

magento / magento2

Prior to making any Submission(s), you must sign an Adobe Contributor License Agreement, available here at: https://opensource.adobe.com/cla.html. All Submissions you make to Adobe Inc. and its affiliates, assigns and subsidiaries (collectively “Adobe”) are subject to the terms of the Adobe Contributor License Agreement.
http://www.magento.com
Open Software License 3.0
11.56k stars 9.32k forks source link

Update to 2.1.11 from 2.1.10 causes MYSQL error when using a custom product attribute as a condition on the Catalog Products List widget #13215

Closed bexdoggett closed 6 years ago

bexdoggett commented 6 years ago

Preconditions

  1. Magento 2.1.11
  2. PHP Version 7.0.19
  3. Percona for MYSQL 5.6.36-82

Steps to reproduce

  1. Upgraded 2.1.10 to 2.1.11
  2. Login to Magento 2 Admin
  3. Use Flat Catalog Product and Use Flat Catalog Category are set to yes
  4. Content > Blocks > Select 'Homepage Featured Products' > Edit
  5. Under Content > Insert Widget
  6. Widget Type: Catalog Products List, Enter Title, Number of Products, etc.
  7. Under Conditions, select category in which products are displayed and custom product attribute ‘Homepage Featured’ which is set to Yes
  8. Click 'Insert Widget’ & ‘Save Block’

Expected result

  1. Products matching the category and with product attribute ‘Homepage Featured’ condition set to yes should display on the homepage. (Working correctly in 2.1.10)

Actual result

  1. Error on homepage of site: ‘We’re sorry, an error has occurred while generating this content.’
  2. Enable developer mode and get following error: Error filtering template: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_homepage_featured_default.value' in 'where clause', query was: SELECT COUNT(DISTINCT e.entity_id) FROM catalog_product_flat_1 AS e INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(2, 4) AND cat_index.category_id='2' 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 (((IFNULL(e.entity_id, 0) IN (SELECT catalog_category_product.product_id FROM catalog_category_product WHERE (category_id IN ('15')))) AND(IFNULL(e.homepage_featured, at_homepage_featured_default.value) = '1') ))

Additional Information

I have already performed the following tasks:

  1. Full Reindex
  2. Full Cache Clean and Flush
  3. Static Content Deploy
  4. Use Flat Catalog Product and Use Flat Catalog Category to no
  5. Reindexed/cleared cache (after switching above to no)
  6. Deleted and added the widget back in
  7. Double checked that custom ‘Homepage Featured’ attribute is set to Yes for 'Used in Product Listing'

Also:

  1. When setting a single condition such as category by itself no error is shown and products are displayed correctly.
  2. When setting a single condition of ‘Homepage Featured’ attribute set to Yes by itself error still displays.
  3. The widget works without error, if I comment out line 209 in magento/vendor/magento/module-rule/Model/Condition/Sql/Builder.php attachConditionToCollection function //$this->eavCollection = $collection;
  4. I am aware of the following ticket https://github.com/magento/magento2/issues/3971 but it is closed as issue was in 2.0 and fixed for 2.1
magento-engcom-team commented 6 years ago

@bexdoggett , thank you for your report. We were not able to reproduce this issue by following the steps you provided. Please provide more detailed steps to reproduce or try to reproduce this issue on a clean installation or latest release.

magento-engcom-team commented 6 years ago

@bexdoggett, we are closing this issue due to inactivity. If you'd like to update it, please reopen the issue.

bexdoggett commented 6 years ago

@magento-engcom-team I still have the issue when updating to 2.1.12 on multiple sites. I will try to provide more steps to help reproduce the issue:

Preconditions

  1. Magento 2.1.10 (Previously I said 2.1.11, but you need to be on 2.1.10 to start)
  2. PHP Version 7.0.19
  3. Percona for MYSQL 5.6.36-82

Steps to reproduce

  1. Login to Magento 2 Admin
  2. Go to Stores > [Attributes] Product and 'Add New Attribute'
  3. Setup a new attribute with 'Catalog Input Type for Store Owner' set to 'Yes/No' and save
  4. Assign a few products to have this new attribute set to yes
  5. Go to Content > [Elements] Widgets and create a new widget
  6. Set widget 'Type' to Catalog Product List
  7. In the 'Layout Updates' section 'Add Layout Update' and select: a. Display on: Specified Page b. Page: CMS Home Default c. Container: Main Content Bottom
  8. In the 'Widget Options' tab under 'Conditions' select the Product Attribute you created in step 3 and select 'Yes' form the dropdown
  9. Save the widget and go to the homepage - the widget should be working
  10. Upgrade from 2.1.10 to 2.1.11
  11. Go to the homepage and you should see the error.

Hopefully that gives enough detail to recreate now