[BUG] Bi-directional Product Relationship record creation is inconsistent and breaks environment

[jecdx]

Describe the bug
We execute an INSERT script to insert multiple Bi-Directional, Substitute records into the productsubstitute table.
For Bi-Directional records, the productsubstitute table is meant to have an A -> B record and a twin/reverse B -> A record. All of the "A -> B" records are created as expected, but an inconsistent and unpredictable amount of the "B -> A" records are created.

The records that are missing the twin are in a broken state.

• Manually attempting to create the twin results in an error.
• Attempting to delete the 'orphaned' record errors out, whether deleting from SQL 4 CDS or from the Dynamics web UI of a model-driven app.

  • Exception Message: The other row for the product relationship is not available.

  • Full error details: Delete-ErrorDetails.txt

The only way we were able to fix these records was to reach out to Microsoft support who were able to run a script of their own (not runnable in SQL 4 CDS) to delete the problem records.
Our workaround in this case was to manually create the needed Product Relationship records using our existing model-driven app.

To Reproduce
Query to reproduce the behavior:

INSERT INTO productsubstitute (productid, substitutedproductid, salesrelationshiptype, direction)
SELECT
    ptid.productid,
    p.productid,
    3, -- Substitute
    1  -- Bi-Directional
FROM product p
INNER JOIN cdm_company c            ON p.msdyn_companyid     = c.cdm_companyid
INNER JOIN msdyn_productcategory pc ON p.pre_productcategory = pc.msdyn_productcategoryid
INNER JOIN product ptid             ON ptid.productnumber    = 'SomeProductNumber'
WHERE
    c.cdm_companycode = '1234' -- Example company code
    AND pc.msdyn_code = '4444' -- Example code
    AND p.productnumber <> 'SomeProductNumber' -- Avoid associating with itself.

The intention of the script is to set all of the products with the expected company and product category as substitutes for the product with SomeProductNumber.

p.pre_productcategory is a lookup column to the Product Category table.

Expected behavior
All of the records and their twins are created.

Screenshots
If applicable, add screenshots to help explain your problem.

Environment (please complete the following information):

• SQL 4 CDS edition: XrmToolbox
• Results of SELECT @@VERSION:
• First observed with SQL 4 CDS 10.1.0.0 in XrmToolbox version 1.2025.7.71 (when these versions were latest) but also reproduced with later versions

Additional context
We opened a ticket with Microsoft. They were able to delete the problem records from our environment, but declined to look into the root cause since XrmToolbox and SQL 4 CDS are third-party tools.

Sponsorship
If you find this tool useful, please consider sponsoring its development.

[fowl2]

It's ridiculous that Microsoft won't fix their bug - SQL4CDS just uses their published API.

1. Can you provide the query plan of your query?
2. Does the problem reproduce?
3. Does the problem reproduce with the simplest case - inserting a single record? Or is it something to do with particular values generated by your query?

If it reproduces with a single record, it should be easy reproduce with a single web api request - that would likely be harder for Microsoft to deny.

[jecdx]

1. Is that the same as the execution plan in SQL 4 CDS?

2. The script reproduces the issue every time, but it was inconsistent and unpredictable how many of the record pairs would be created without issue.
3. I have not seen a plain insert without additional clauses cause the issue. In one case, I ran the script with an additional WHERE condition that would cause only a single record to be used, and this worked without issue. I did not run the scenario multiple times though -- who knows how many times that would happen out of X number of repeats.

Overall, it is difficult to test this thoroughly or exhaustively. Every reproduction of the issue permanently breaks the relevant records in the environment, and I have to overwrite the whole environment from another environment that is in a good state. It takes ~40 minutes to copy over a good environment state, so it's either slow to iterate or taking up a lot of space if I prepare multiple environments in advance. I have not worked on creating a minimal environment for the issue and testing if that copies faster.

I have found no way to fix up or delete the problem records without being blocked by an error occurring (we had to get Microsoft support to clear the records from their end in our environments that we did not want to entirely overwrite or recreate).

I have the current latest XrmToolbox 1.2025.10.74 and SQL 4 CDS 10.3.0.0.

[MarkMpn]

If the original record is being inserted correctly, I'm not sure there's anything I can do within SQL 4 CDS if the Microsoft plugins are then not creating the other side of that relationship. That isn't functionality that's visible to the tool - it's only issuing a Create request for the original record.

It might be interesting if you can look to see if there are any relevant entries in the plugin trace log around the time you were inserting the records that did not get their reverse records created that might shed any further light on the issue.

[jecdx]

I will poke around in the plugin trace log next time I reproduce the issue.

It sounds like the SQL query is getting transformed into CreateRequest objects and executed through the Microsoft.PowerPlatform.Dataverse.Client nuget package? I see that namespace is referenced in the source.

Do you know if all of those CreateRequests would be getting sent in one ExecuteTransactionRequest, or separately in rapid succession?

If I can put together a small C# program using that nuget that is able to reproduce the issue, I imagine we'd be able to reopen our ticket and get Microsoft support to dig deeper.

[MarkMpn]

They’re not done in a transaction, but may be grouped into one or more ExecuteMultipleRequests and/or may be executed in parallel depending on your settings.

[jecdx]

Even with the number of workers threads set to 1 in SQL 4 CDS, the script is able to reproduce the issue.