Target rules are broken since 2.1 (entity_id to row_id change)

[AntonEvers]

Preconditions

1. Magento version 2.1.2 (EE)

Steps to reproduce

1. Create a target rule (related products) with these 'products to display' conditions:
2. Create an attribute called Model of type text and use it for catalog rules (make sure it is also present in the flat tables)
   2.1. If ALL of these conditions are TRUE :
   2.2. Product Type is Constant Value Configurable Product
   2.3. Product Model is Matched Product Model
3. Enable developer mode
4. Enable flat product
5. flush cache, reindex target rule indexes, visit product view page

Expected result

1. Product detail page loads with related products

Actual result

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table.entity_id' in 'where clause', query was: SELECT `e`.`entity_id` FROM `catalog_product_flat_1` AS `e`
 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
 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' WHERE (( (`e`.`type_id`='configurable') AND  (IFNULL((SELECT COUNT(*) FROM `catalog_product_entity_varchar` AS `table` WHERE (table.entity_id = e.entity_id) AND (table.attribute_id='291') AND (table.store_id=0) AND (`table`.`value`=:targetrule_bind_0)), 0)) > 0)) AND (e.entity_id NOT IN('3836')) LIMIT 20
Exception #1 (PDOException): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table.entity_id' in 'where clause'

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'table.entity_id' in 'where clause', query was: SELECT `e`.`entity_id` FROM `catalog_product_flat_1` AS `e`
 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
 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' WHERE (( (`e`.`type_id`='configurable') AND  (IFNULL((SELECT COUNT(*) FROM `catalog_product_entity_varchar` AS `table` WHERE (table.entity_id = e.entity_id) AND (table.attribute_id='291') AND (table.store_id=0) AND (`table`.`value`=:targetrule_bind_0)), 0)) > 0)) AND (e.entity_id NOT IN('3836')) LIMIT 20

The location of the error is https://github.com/magento/magento2ee/blob/develop/app/code/Magento/TargetRule/Model/Actions/Condition/Product/Attributes.php#L395

The problem is... You try to join catalog_product_flat_1 on catalog_product_entity_varchar
catalog_product_entity_varchar has identifier column 'row_id' instead of 'entity_id'
catalog_product_flat_1 only has identifier column 'entity_id'
You cannot match these without catalog_product_entity in between to make the entity_id to row_id match. Or you should include row_id in catalog_product_flat_1

[AntonEvers]

I think you also should know that bugs like these withhold us from rolling out 2.1 to our clients and keep us on 2.0 for stability. For instance https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogRule/etc/mview.xml also mentions entity_id columns in the eav attribute tables. Those columns don't exist anymore and have become row_id. when I see things like these I wonder what else could be broken and untested.

[veloraven]

@ajpevers thank you for your report.
Please report EE issues via the Support portal of your account or Partner portal if you are a partner reporting on behalf of a merchant.
Github is intended for Community edition reports given no account management for CE users. This will allow for proper tracking of issues at the account level.

[ghost]

Leaving this comment here for anyone who might be waiting on a fix from Magento or simply trying to debug the problem.

Run the following sql
*be advised this is sql so please please please try it locally or on dev before staging or prod.

UPDATE magento_targetrule SET action_select = NULL;

Then hit the PDP page and it should load just fine. The reason this is happening is because SQL is being saved to that column and it needs to be updated after a data migration for version x to version y.

Hope this helps!

@ajpevers