Category and product row_id is always null when the entity is created

[mpasquin]

Environment and configuration

1. Magento 2.4.5-p1
2. Akeneo connector module version 103.3.1

Steps to reproduce

1. Open vendor/akeneo/module-magento2-connector-community/Job/Product.php file
2. Add brekpoint into createEntities at line 1746 ($connection->query($query);)
3. Run product import with xdebug.
4. Inspect $query variable.
5. See that row_id in select is null when product is created, but we shall have IFNULL expression instead.

There is the same issue while categories are imported.

Expected result

INSERT INTO `catalog_product_entity` (`entity_id`, `attribute_set_id`, `type_id`, `sku`, `updated_at`, `row_id`)
SELECT `tmp_akeneo_connector_entities_product`.`_entity_id`        AS `entity_id`,
       `tmp_akeneo_connector_entities_product`.`_attribute_set_id` AS `attribute_set_id`,
       `tmp_akeneo_connector_entities_product`.`_type_id`          AS `type_id`,
       `tmp_akeneo_connector_entities_product`.`identifier`        AS `sku`,
       NOW()                                                       AS `updated_at`,
       IFNULL(p.row_id, _entity_id)                                AS `row_id`
FROM `tmp_akeneo_connector_entities_product`
LEFT JOIN `catalog_product_entity` AS `p`
    ON _entity_id = p.entity_id
LEFT JOIN `staging_update` AS `s`
    ON p.created_in = s.id
WHERE (s.is_rollback = 1 OR s.id IS NULL)
ON DUPLICATE KEY UPDATE `entity_id`        = VALUES(`entity_id`),
                        `attribute_set_id` = VALUES(`attribute_set_id`),
                        `type_id`          = VALUES(`type_id`),
                        `sku`              = VALUES(`sku`),
                        `updated_at`       = VALUES(`updated_at`),
                        `row_id`           = VALUES(`row_id`);

Actual result

INSERT INTO `catalog_product_entity` (`entity_id`, `attribute_set_id`, `type_id`, `sku`, `updated_at`, `row_id`)
SELECT `tmp_akeneo_connector_entities_product`.`_entity_id`        AS `entity_id`,
       `tmp_akeneo_connector_entities_product`.`_attribute_set_id` AS `attribute_set_id`,
       `tmp_akeneo_connector_entities_product`.`_type_id`          AS `type_id`,
       `tmp_akeneo_connector_entities_product`.`identifier`        AS `sku`,
       NOW()                                                       AS `updated_at`,
       `p`.`row_id`
FROM `tmp_akeneo_connector_entities_product`
LEFT JOIN `catalog_product_entity` AS `p`
    ON _entity_id = p.entity_id
LEFT JOIN `staging_update` AS `s`
    ON p.created_in = s.id
WHERE (s.is_rollback = 1 OR s.id IS NULL)
ON DUPLICATE KEY UPDATE `entity_id`        = VALUES(`entity_id`),
                        `attribute_set_id` = VALUES(`attribute_set_id`),
                        `type_id`          = VALUES(`type_id`),
                        `sku`              = VALUES(`sku`),
                        `updated_at`       = VALUES(`updated_at`),
                        `row_id`           = VALUES(`row_id`);

Patches

--- a/vendor/akeneo/module-magento2-connector-community/Job/Category.php    2022-12-14 10:55:49.000000000 +0100
+++ b/vendor/akeneo/module-magento2-connector-community/Job/Category.php    2023-03-03 16:01:41.456071945 +0100
@@ -605,7 +605,8 @@
         $rowIdExists = $this->entitiesHelper->rowIdColumnExists($table);
         if ($rowIdExists) {
             $this->entities->addJoinForContentStagingCategory($parents, ['p.row_id']);
-            $values['row_id'] = 'IFNULL (p.row_id, _entity_id)'; // on category creation, row_id is null
+            $values['row_id'] = new Expr('IFNULL (p.row_id, _entity_id)'); // on category creation, row_id is null
+            $parents->reset(\Zend_Db_Select::COLUMNS)->columns($values); // update select columns
         }

         $connection->query(

--- a/vendor/akeneo/module-magento2-connector-community/Job/Product.php 2023-03-03 15:52:40.547352729 +0100
+++ b/vendor/akeneo/module-magento2-connector-community/Job/Product.php 2023-03-03 15:53:37.379011854 +0100
@@ -1734,7 +1734,8 @@
         $rowIdExists = $this->entitiesHelper->rowIdColumnExists($table);
         if ($rowIdExists) {
             $this->entities->addJoinForContentStaging($parents, ['p.row_id']);
-            $values['row_id'] = 'IFNULL (p.row_id, _entity_id)'; // on product creation, row_id is null
+            $values['row_id'] = new Expr('IFNULL (p.row_id, _entity_id)'); // on product creation, row_id is null
+            $parents->reset(\Zend_Db_Select::COLUMNS)->columns($values); // update select columns
         }

         /** @var string $query */

[mpasquin]

See https://github.com/akeneo/magento2-connector-community/pull/647