SQL Server

Execution Plan Failure Due to Circular Dependencies

See more CURL Examples

This example demonstrates what happens when an execution plan cannot be created due to a cycle in the dependency graph.

The target curl command requires {{report_id}}. A helper function is defined to produce report_id, but it depends on user_id. In turn, user_id depends on account_id, and account_id depends back on report_id.

This creates a circular dependency:

• report_id → user_id
• user_id → account_id
• account_id → report_id

Because each required value ultimately depends on itself, there is no starting point with a known or independently resolvable input. As a result, it is impossible to construct a valid execution plan.

When ExaminePlan is called, it detects this cycle and reports that the execution plan cannot be created.

This example highlights that dependency resolution requires an acyclic graph. Every chain of dependencies must eventually terminate in a known value. If a cycle exists, the system correctly identifies it and prevents execution.

SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @httpCurl int
    EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @httpCurl OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- This example uses fake URLs and fake variables to demonstrate a dependency cycle.
    -- No requests are sent because we only call ExaminePlan.

    -- The target curl command requires {{report_id}}.
    DECLARE @targetCurl nvarchar(4000)
    SELECT @targetCurl = 'curl -X GET https://api.example.com/reports/{{report_id}}'

    -- getReportId can produce report_id, but it requires user_id.
    DECLARE @fnName nvarchar(4000)
    SELECT @fnName = 'getReportId'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://api.example.com/report-id?user={{user_id}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'report.id', 'report_id'

    -- getUserId can produce user_id, but it requires account_id.
    SELECT @fnName = 'getUserId'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://api.example.com/user-id?account={{account_id}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'user.id', 'user_id'

    -- getAccountId can produce account_id, but it requires report_id.
    -- This creates a cycle:
    -- 
    --   report_id -> user_id -> account_id -> report_id
    -- 
    -- In other words:
    --   - targetCurl needs report_id
    --   - report_id requires user_id
    --   - user_id requires account_id
    --   - account_id requires report_id
    -- 
    -- There is no starting point where the dependency chain can be resolved.
    SELECT @fnName = 'getAccountId'
    EXEC sp_OAMethod @httpCurl, 'AddFunction', @success OUT, @fnName, 'curl -X GET https://api.example.com/account-id?report={{report_id}}'
    EXEC sp_OAMethod @httpCurl, 'AddOutput', @success OUT, @fnName, 'account.id', 'account_id'

    -- Examine the execution plan without sending any requests.
    -- The plan cannot be created because the dependencies contain a cycle.
    DECLARE @planJson int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @planJson OUT

    EXEC sp_OASetProperty @planJson, 'EmitCompact', 0

    EXEC sp_OAMethod @httpCurl, 'ExaminePlan', @success OUT, @targetCurl, @planJson

    -- Success is expected to be false.

    PRINT 'success = ' + @success

    EXEC sp_OAMethod @planJson, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Expected result:
    -- 
    -- {
    --   "errors": [{
    --     "variable": "report_id",
    --     "msg": "Cycle detected"
    --   },{
    --     "variable": "account_id",
    --     "msg": "Unable to resolve"
    --   },{
    --     "variable": "user_id",
    --     "msg": "Unable to resolve"
    --   },{
    --     "variable": "report_id",
    --     "msg": "Unable to resolve"
    --   }]
    -- 

    EXEC @hr = sp_OADestroy @httpCurl
    EXEC @hr = sp_OADestroy @planJson


END
GO