Magento2: Products list widget error when condition is attribute with scope website

Created on 28 Feb 2018 · 14Comments · Source: magento/magento2

Preconditions

Magento Version 2.1.11

Steps to reproduce

Install clean magento 2.1.11 and create a products list widget with condition on tax class, or add an attribute with scope website and use it as condition. Go to frontend at the page containing the widget

Expected result

See the list of matching products

Actual result

Column not found: 1054 Unknown column 'at_tax_class_id_default.value' in 'where clause', query was: SELECT COUNT(DISTINCT e.entity_id) FROM catalog_product_entity 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, at_tax_class_id_default.value) IN ('1')) ))

In MagentoRuleModelConditionSqlBuilder _getMappedSqlCondition adds this condition to query:
if ($this->canAttributeHaveDefaultValue($condition->getAttribute())) {
$defaultField = 'at_' . $condition->getAttribute() . '_default.value';
but at_attribute_default is never used when building the query so the query fails

CatalogRule CatalogWidget Fixed in 2.3.x Clear Description Confirmed Format is valid Ready for Work Reproduced on 2.1.x

Source

mihaifaget

👍2

Most helpful comment

@hostep Your solution works. Thanks :)

webspeaks on 3 Jul 2018

👍3

All 14 comments

Confirmed on a clean Magento 2.1.12 and bug seems to be introduced in 2.1.11 by MAGETWO-75526

Addition to steps to reproduce of above: make sure you have at least one product with that specific tax class.

The bug happens because in scope of MAGETWO-75526 something was added to try to fetch the default value of an attribute in the sql query, this seems to be the most relevant commit: https://github.com/magento/magento2/commit/4aac0222a7de813d6dbf5a5c21a67861ca2d9bbf#diff-41e2de02c84a94334f70de775066b012R148, but there are multiple commits from MAGETWO-75526 involved.
So a at_{some_attribute_code}_default.value gets added to the sql query, but that expression isn't part of the SELECT clause, which makes this fail...

The strange thing is that the changes from MAGETWO-75526 were only applied to Magento 2.1 and not to 2.2, so the obvious question becomes: why was this done only to 2.1? And will those changes also come to 2.2 one day? If so: then this issue needs to get fixed first.

Temporary quick fix is to replace the line: https://github.com/magento/magento2/blob/2.1.12/app/code/Magento/Rule/Model/Condition/Sql/Builder.php#L159 from

$this->_connection->getIfNullSql($this->_connection->quoteIdentifier($argument), $defaultValue),

$this->_connection->getIfNullSql($this->_connection->quoteIdentifier($argument), 0),

Just like what is being used in Magento 2.2.3 at this point: https://github.com/magento/magento2/blob/2.2.3/app/code/Magento/Rule/Model/Condition/Sql/Builder.php#L135, but that's a bad solution, since it now removes this functionality which was supposed to be added by MAGETWO-75526 again.

@magento-engcom-team: could you be so kind to figure out the status of MAGETWO-75526 in your internal systems? I also see it's not directly mentioned in the release notes of 2.1.11, but you can find a reference by looking through the html source code of the release notes:

<!--- NOT NEEDED   MAGETWO-83263 MAGETWO-71185  MAGETWO-84545     MAGETWO-69344  MAGETWO-69107   MAGETWO-67681   MAGETWO-65466    MAGETWO-64459   MAGETWO-63226 MAGETWO-62621  MAGETWO-61164  MAGETWO-58503 MAGETWO-77969     MAGETWO-71576  MAGETWO-82242 MAGETWO-82554 MAGETWO-71522 MAGETWO-75526 -->

I don't know what this NOT NEEDED means?

Thanks! :)

hostep on 28 Feb 2018

👍3

@hostep According to internal records MAGETWO-75526 was only targeting 2.1.11 and it's Closed/Fixed at the moment. Will see if we are able to get more information on the subject and let you know.

magento-engcom-team on 2 Mar 2018

@mihaifaget, thank you for your report.
We've acknowledged the issue and added to our backlog.

magento-engcom-team on 2 Mar 2018

I can confirm this issue has now cropped up on 2.2.5

cjj25 on 3 Jul 2018

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

webspeaks on 3 Jul 2018

@hostep Your solution works. Thanks :)

webspeaks on 3 Jul 2018

👍3

Indeed, this was added in 2.2.5 in commit https://github.com/magento/magento2/commit/f792ba75046e9f76eb7c27d5c3c4da88511b2feb#diff-41e2de02c84a94334f70de775066b012L135 (in scope of: MAGETWO-77754).

At least we now have some information about this, from the release notes of 2.2.5:

The Related Products rule for up-sell products with customer segments set to Specified now works as expected.

@magento-engcom-team: it would be great if the people involved with that commit could take a look at this issue, thanks! :)

hostep on 3 Jul 2018

M 2.2.5

First, thank you very much @hostep for sharing this quick fix! It works great in M 2.2.5.

I'm going to share my insights that may be interesting for everyone who wants to apply this quick fix through a custom module:

I have overridden the entire function (_getMappedSqlCondition) that includes the line that is mentioned by @hostep in a custom module. If you do so, you can remove the block that sets the variable defaultValue (starts at https://github.com/magento/magento2/blob/2.2/app/code/Magento/Rule/Model/Condition/Sql/Builder.php#L149 and ends at https://github.com/magento/magento2/blob/2.2/app/code/Magento/Rule/Model/Condition/Sql/Builder.php#L154). Otherwise, you have to override some more functions and variables because these are declared as private. You can remove this block because it does nothing else but setting the value of the variable defaultValue and @hostep's quick fix removes the only usage of this variable.

densen45 on 4 Sep 2018

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:

[x] 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.