SQL Error: ambiguous column 'customer_group_id' in 'All customers' page in admin when extension attribute table is joined

[maksymhopei]

Preconditions

1. Magento CE 2.2.4 without sample data is installed.

Steps to reproduce

1. In a custom module add the following mysql table for storing customer group preferences with one-to-one relation to customer group:

CREATE TABLE `my_customer_group_preference` (
  `customer_group_id` int(10) unsigned NOT NULL,
  `my_attribute` int(10) unsigned NOT NULL,
  PRIMARY KEY (`customer_group_id`),
  CONSTRAINT `FK_CUSTOMER_GROUP_ID` FOREIGN KEY (`customer_group_id`)
        REFERENCES `customer_group` (`customer_group_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer Group Preferences';

2. In a custom module add the following extension attribute to customer group:

<extension_attributes for="Magento\Customer\Api\Data\GroupInterface">
    <attribute code="my_attribute" type="My\Module\Api\Data\GroupPreferenceDataInterface">
        <join reference_table="my_customer_group_preference"
              reference_field="customer_group_id"
              join_on_field="customer_group_id">
            <field column="my_attribute">my_attribute</field>
        </join>
    </attribute>
</extension_attributes>

3. Refresh caches
4. Go to Admin -> Customers -> All Customers page.

Expected result

1. A page is opened normally

Actual result

1. SQL Error happens:

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'customer_group_id' in where clause is ambiguous, query was: SELECT `main_table`.*, `extension_attribute_my_attribute`.`my_attribute` AS `extension_attribute_my_attribute_my_attribute`, `tax_class_table`.* FROM `customer_group` AS `main_table`
 LEFT JOIN `my_customer_group_preference` AS `extension_attribute_my_attribute` ON main_table.customer_group_id = extension_attribute_my_attribute.customer_group_id
 LEFT JOIN `tax_class` AS `tax_class_table` ON main_table.tax_class_id = tax_class_table.class_id WHERE ((`customer_group_id` != 0)) AND ((`customer_group_id` != 32000)) ORDER BY customer_group_id ASC

This happens because conditions added by \Magento\Customer\Model\GroupManagement::getLoggedInGroups refer to field customer_group_id without the table alias prefix.

[VladimirZaets]

Hi @radio. Thank you for your report.
The issue has been fixed in #15826 by @radio in 2.2-develop branch
Related commit(s):

• 83a8deaa0beb22617be20611540912e6f49be34a

The fix will be available with the upcoming 2.2.6 release.

[magento-engcom-team]

Hi @radio. Thank you for your report.
The issue has been fixed in #15842 by @hitesh-wagento in 2.3-develop branch
Related commit(s):

• 0f23ed75e6aeac411ff51d70bbd421d47fefd919

The fix will be available with the upcoming 2.3.0 release.