Filter category/product collection by new custom attribute returns 0 results

[pniel-cohen]

Summary of the issue

I created a bool (int) attribute with a default value of 0 for both category & product entities.
I reindexed, cleared cache, etc...
Now, no matter what I do, I can't filter the collection using this attribute. It always returns 0 results, no matter if the value is 0/1/null.
Also, when I tried to debug the issue I found out that my custom attributes are missing from catalog_category_entity_int & therefore - there is no default value for this attribute & the query returns 0 because it uses an inner join with this table (is that normal?).

This is how I created those attributes (I also tried the same with different values for 'global', 'nullable', 'used_in_product_listing', ...):

`
$eavSetup->addAttribute(
\Magento\Catalog\Model\Category::ENTITY,
'attr_code',
[
'type' => 'int',
'label' => 'Attr Label',
'input' => 'boolean',
'required' => false,
'default' => '0',
'sort_order' => 100,
'visible' => false,
'source' => \Magento\Eav\Model\Entity\Attribute\Source\Boolean::class,
'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_STORE,
'nullable' => false,
'filterable' => true,
]
);

$eavSetup->addAttribute(
\Magento\Catalog\Model\Product::ENTITY,
'attr_code',
[
'type' => 'int',
'sort_order' => 100,
'label' => 'Attr Label',
'input' => 'boolean',
'source' => \Magento\Eav\Model\Entity\Attribute\Source\Boolean::class,
'global' => \Magento\Eav\Model\Entity\Attribute\ScopedAttributeInterface::SCOPE_STORE,
'visible' => false,
'required' => false,
'user_defined' => false,
'default' => '0',
'searchable' => false,
'filterable' => true,
'comparable' => false,
'visible_on_front' => false,
'used_in_product_listing' => false,
'unique' => false,
'nullable' => false,
]
);
`

Preconditions

1. Magento 2.2.4
2. PHP 7.0 (Apache)

Steps to reproduce

1. Add new boolean (int) attribute with a defaut of '0' to category or product entities using your module's install script.
2. Reindex, clear cache, etc...
3. Try to filter a category/product collection using this new attribute (before saving any value for this attribute on any item)

Expected result

1. If you set this filter: $collection->addAttributeToFilter('attr_code', 1); it should return 0 items.
2. If you set this filter: $collection->addAttributeToFilter('attr_code', 0); it should return all items.items.

Actual result

1. The 2 examples above returns 0 results.

[magento-engcom-team]

Hi @pini-girit. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me {$VERSION} instance

where {$VERSION} is version tags (starting from 2.2.0+) or develop branches (2.2-develop +).
For more details, please, review the Magento Contributor Assistant documentation.

@pini-girit do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

[pniel-cohen]

@magento-engcom-team give me 2.2.4 instance

[magento-engcom-team]

Hi @pini-girit. Thank you for your request. I'm working on Magento 2.2.4 instance for you

[magento-engcom-team]

Hi @pini-girit, here is your Magento instance.
Admin access: https://i-16367-2-2-4.engcom.dev.magento.com/admin
Login: admin Password: 123123q
Instance will be terminated in up to 3 hours.

[engcom-backlog-nickolas]

Hello @pini-girit, thank you for your report.
We've acknowledged the issue and added to our backlog.

[datwiki]

Hi. We are having exact same issue with Magento CE 2.2.4 version. Can you please let me know the status of this issue. Appreciate

[magento-engcom-team]

Hi @irajneeshgupta. 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.

[magento-engcom-team]

Hi @shikhamis11. 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.

[magento-engcom-team]

Hi @milindsingh. 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.

[senders]

Any updates?
The Problem still exists in 2.2.10.

[shoaibgits]

With the same situation it is now working fine for me with Magento 2.3.6

[github-jira-sync-bot]

✅ Jira issue https://jira.corp.magento.com/browse/AC-1032 is successfully created for this GitHub issue.

[m2-assistant]

✅ Confirmed by @engcom-Alfa. Thank you for verifying the issue.
Issue Available: @engcom-Alfa, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[magelogger]

Hi, @pniel-cohen! You could try to use the left join type to get the expected results:
$collection->addAttributeToFilter( 'attr_code', [ ['eq' => 0], ['null' => true] ], 'left' );