Catalog Products List widget is not displayed on Storefront

[alena-marchenko]

Preconditions

1. Magento is installed from 2.2-develop branch

Steps to reproduce

1. Go to Stores > Attributes > Product and open "Color" attribute
2. Create 2 options red and black
3. Add this attribute to attribute set
4. Create 2 simple products with red and black attributes
5. Go to Stores> Configuration > CATALOG > Catalog > Storefront
6. Set "Use Flat Catalog Category" = Yes and "Use Flat Catalog Product" = "Yes"
7. Go to Content > Widgets and create new widget with:
   Storefront Properties

• Widget Title = Test widget
• Assign to Store Views = All Store Views
• Layout Updates:
  -- Display on = All Pages
  -- Container = Main Content Area

Widget Options

• Title = test
• Number of Products to Display = 10
• Conditions: Color is red

8. run
   rm -rf var/* pub/static/* generated/*
   php bin/magento indexer:reindex
   php bin/magento setup:static-content:deploy
9. Go to Storefront

Expected result

Widget is visible on Storefront

Actual result

Widget is not visible on Storefront, following error persists in support_report.log:
report.CRITICAL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'at_color.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(at_color.value, 0) = '13') ))

[RostislavS]

#mageconf

[magento-engcom-team]

@RostislavS thank you for joining. Please accept team invitation here and self-assign the issue.

[BarnyShergold]

I was about to report this issue myself. I've done some experimentation and it would seem that if you have Flat Product enabled and then create a Product List widget where one of the conditions is on a product attribute that is NOT on the Flat Product table (ie Used in Product Listing = NO) then Magento fails to add the table join for the attribute - hence why as above at_color fails.

@alena-marchenko - If you set 'color' so that Used in Product Listing = YES and then do a full reindex from CLI, you should find the error will stop occurring.

This problem also occurs on the 2.1.x branch so can any fix be backported please?

[magento-engcom-team]

Hi @alena-marchenko. Thank you for your report.
The issue has been fixed in #12765 by @RostislavS in 2.3-develop branch
Related commit(s):

• d30696e
• 55a3ded

The fix will be available with the upcoming 2.3.0 release.

[webspeaks]

@magento-engcom-team What is the solution to this issue? I am having this issue after upgrading to Magento 2.2.5 CE.

[pathakkrunal]

I was facing same problem after updating 2.2.4 to 2.2.5 I have change scope of attribute to "Global" from "Store View" and it run for me.
I have also tried "Website" view but it also gives same error.
Change Attribute Scope to "Global" and problem will be solved.

[helefa]

I am having this issue on 2.2.4. Above suggestion didn't help. If I apply the fixes intended for 2.3, will it help here too?

[ihor-sviziev]

@helefa if you'll apply patch from #12765 - it should work in 2.2.x, but as this change is not backward compatible - we can't add it to one of next 2.2.x releases.

[helefa]

@ihor-sviziev thank you for your response. As I am still new to this, pardon my ignorance - if I do the fix and later upgrade to 2.3.x where the fix has been included, will I be messing up any of my core files in a way that it won't upgrade properly? If I understood you correctly, the only issue I would have is if I were to upgrade to another version of 2.2.x?

[ihor-sviziev]

Hi @helefa,
All patches listed in composer.json file, so after upgrade to 2.3.x - you have to remove applying of this patch and that's it

[dribblecastle]

@pathakkrunal, thanks! switching the attributes to "Global" worked for me.

[magento-engcom-team]

Hi @TomashKhamlai. Thank you for working on this issue.
Looks like this issue is already verified and confirmed. But if your want to validate it one more time, please, go though the following instruction:

• 1. Add/Edit Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 2. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 3. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• 4. If the issue is not relevant or is not reproducible any more, feel free to close it.

[TomashKhamlai]

The issue was re-tested and we can confirm that it was fixed on the 2.3 release branch. We closing this issue as fixed due to upcoming 2.3 release that will be available soon.