Repository: SFDO-Tooling/CumulusCI
Pull request #3761 (2024-03-05 10:23:42)

Fix Id Not Specified on Update Call
[W-15182566](https://gus.lightning.force.com/lightning/r/ADM_Work__c/a07EE00001lVihqYAC/view)

These modifications address the error reported in [Slack Thread](https://salesforce-internal.slack.com/archives/C024R3K39R6/p1709223158710319)


**Error Reason:**
The old format of sql files use the `id` field to be of type `INTEGER`:
```
CREATE TABLE "accounts" (
	id INTEGER NOT NULL, 
	"Name" VARCHAR(255), 
	"parent_id" VARCHAR(255), 
	PRIMARY KEY (id)
);
INSERT INTO "accounts" VALUES(1,'Bluth','');
INSERT INTO "accounts" VALUES(2,'Funke-Bluth',1);
```

During handling of adding the outerjoins for lookups, when we specified the condition, we were concatenating a string with an integer:
```
def join_for_lookup(lookup):
            key_field = lookup.get_lookup_key_field(self.model)
            value_column = getattr(self.model, key_field)
            if self._old_format:
                return (
                    lookup.aliased_table,
                    lookup.aliased_table.columns.id
                    == str(lookup.table) + "-" + value_column,
                    # Here value_column can be an integer in old format
                )
            else:
                return (
                    lookup.aliased_table,
                    lookup.aliased_table.columns.id == value_column,
                )
```

This resulted in the query not returning anything during the update call of the record. Hence the error, `Id not specified in an update call`


**Solution:**

We have updated the condition to cast the id column to a string so that successful concatenation happens.
```
def join_for_lookup(lookup):
            key_field = lookup.get_lookup_key_field(self.model)
            value_column = getattr(self.model, key_field)
            if self._old_format:
                return (
                    lookup.aliased_table,
                    lookup.aliased_table.columns.id
                    == str(lookup.table) + "-" + func.cast(value_column, String),
                )
            else:
                return (
                    lookup.aliased_table,
                    lookup.aliased_table.columns.id == value_column,
                )
```


Patch:
diff --git a/cumulusci/tasks/bulkdata/query_transformers.py b/cumulusci/tasks/bulkdata/query_transformers.py
index 1618d813e..aef23f5dc 100644
--- a/cumulusci/tasks/bulkdata/query_transformers.py
+++ b/cumulusci/tasks/bulkdata/query_transformers.py
@@ -1,7 +1,7 @@
 import typing as T
 from functools import cached_property
 
-from sqlalchemy import and_, func, text
+from sqlalchemy import String, and_, func, text
 from sqlalchemy.orm import Query, aliased
 
 from cumulusci.core.exceptions import BulkDataException
@@ -75,7 +75,7 @@ class AddLookupsToQuery(LoadQueryExtender):
                 return (
                     lookup.aliased_table,
                     lookup.aliased_table.columns.id
-                    == str(lookup.table) + "-" + value_column,
+                    == str(lookup.table) + "-" + func.cast(value_column, String),
                 )
             else:
                 return (


Test patch:
diff --git a/cumulusci/tasks/bulkdata/tests/test_load.py b/cumulusci/tasks/bulkdata/tests/test_load.py
index 3277ea323..3d6f64503 100644
--- a/cumulusci/tasks/bulkdata/tests/test_load.py
+++ b/cumulusci/tasks/bulkdata/tests/test_load.py
@@ -871,20 +871,22 @@ class TestLoadData:
         assert "RecordType" in str(e.value)
 
     def test_query_db__joins_self_lookups(self):
+        """SQL file in Old Format"""
         _validate_query_for_mapping_step(
             sql_path=Path(__file__).parent / "test_query_db__joins_self_lookups.sql",
             mapping=Path(__file__).parent / "test_query_db__joins_self_lookups.yml",
             mapping_step_name="Update Accounts",
-            expected="""SELECT accounts.id AS accounts_id, accounts."Name" AS "accounts_Name", cumulusci_id_table_1.sf_id AS cumulusci_id_table_1_sf_id FROM accounts LEFT OUTER JOIN cumulusci_id_table AS cumulusci_id_table_1 ON cumulusci_id_table_1.id = ? || accounts.parent_id ORDER BY accounts.parent_id""",
+            expected="""SELECT accounts.id AS accounts_id, accounts."Name" AS "accounts_Name", cumulusci_id_table_1.sf_id AS cumulusci_id_table_1_sf_id FROM accounts LEFT OUTER JOIN cumulusci_id_table AS cumulusci_id_table_1 ON cumulusci_id_table_1.id = ? || cast(accounts.parent_id as varchar) ORDER BY accounts.parent_id""",
             old_format=True,
         )
 
     def test_query_db__joins_polymorphic_lookups(self):
+        """SQL File in New Format (Polymorphic)"""
         _validate_query_for_mapping_step(
             sql_path=Path(__file__).parent / "test_query_db_joins_lookups.sql",
             mapping=Path(__file__).parent / "test_query_db_joins_lookups.yml",
             mapping_step_name="Update Event",
-            expected="""SELECT events.id AS events_id, events."Subject" AS "events_Subject", cumulusci_id_table_1.sf_id AS cumulusci_id_table_1_sf_id FROM events LEFT OUTER JOIN cumulusci_id_table AS cumulusci_id_table_1 ON cumulusci_id_table_1.id = ? || events."WhoId" ORDER BY events."WhoId" """,
+            expected="""SELECT events.id AS events_id, events."Subject" AS "events_Subject", cumulusci_id_table_1.sf_id AS cumulusci_id_table_1_sf_id FROM events LEFT OUTER JOIN cumulusci_id_table AS cumulusci_id_table_1 ON cumulusci_id_table_1.id = ? || cast(events."WhoId" as varchar) ORDER BY events."WhoId" """,
         )
 
     @responses.activate
diff --git a/cumulusci/tasks/bulkdata/tests/test_query_db__joins_self_lookups.sql b/cumulusci/tasks/bulkdata/tests/test_query_db__joins_self_lookups.sql
index 818bece66..39b988a9a 100644
--- a/cumulusci/tasks/bulkdata/tests/test_query_db__joins_self_lookups.sql
+++ b/cumulusci/tasks/bulkdata/tests/test_query_db__joins_self_lookups.sql
@@ -1,6 +1,6 @@
 BEGIN TRANSACTION;
 CREATE TABLE "accounts" (
-	id VARCHAR(255) NOT NULL, 
+	id INTEGER NOT NULL, 
 	"Name" VARCHAR(255), 
 	"parent_id" VARCHAR(255), 
 	PRIMARY KEY (id)