MarkMpn / Sql4Cds

SQL 4 CDS core engine and XrmToolbox tool
MIT License
74 stars 22 forks source link

[Bug] OUTER APPLY execution result is wrong #548

Closed hiroyuki0415 closed 5 hours ago

hiroyuki0415 commented 4 days ago
SELECT sc1.solutioncomponentid AS [sc1.solutioncomponentid],
    sc2.solutioncomponentid AS [sc2.solutioncomponentid],
    tmp.only_in_s1,
    tmp.only_in_s2
FROM (
    SELECT sc.solutioncomponentid,
        sc.objectid
    FROM solution AS s
    INNER JOIN solutioncomponent AS sc
        ON s.solutionid = sc.solutionid
    WHERE s.uniquename IN ('Default')
    ) AS sc1
FULL OUTER JOIN (
    SELECT sc.solutioncomponentid,
        sc.objectid
    FROM solution AS s
    INNER JOIN solutioncomponent AS sc
        ON s.solutionid = sc.solutionid
    WHERE s.uniquename IN ('Default')
    ) AS sc2
    ON sc1.objectid = sc2.objectid
OUTER APPLY (
    SELECT IIF(sc2.solutioncomponentid IS NULL, 1, 0) AS [only_in_s1],
        IIF(sc1.solutioncomponentid IS NULL, 1, 0) AS [only_in_s2]
    ) AS tmp
WHERE tmp.only_in_s1 = 1
    OR tmp.only_in_s2 = 1;

The execution result of this SQL is below: