Subselect in where clause returns incorrect result

[tsileo-wdi]

Hi Mark,

Apologies for the fairly complicated query here, but I'm hoping this will give you enough to troubleshoot the issue.

When I run the query below, I get records where the numblockedbyofthattype as returned in the outermost select is > 0, even though the first criteria in the where clause is the exact same subselect (testing for count = 0).

I've also tried using exists/not exists and count(*) instead of count(1), but get the same results.

Any thoughts? Is this is a bug?

SELECT TOP 5 a.wdi_assignmentid,
             a.wdi_name AS thisassignment,
             a.wdi_assignmenttypename AS thistype,
             blockedbytype.wdi_name AS defblockedbytype,
             parent.wdi_name AS parentname,
             (SELECT count(1)
              FROM   wdi_assignment AS child
              WHERE  child.wdi_parentassignment = parent.wdi_assignmentid
                     AND child.wdi_assignmenttypename = blockedbytype.wdi_name) AS numchildrenofdefblockedbytype,
             (SELECT count(1)
              FROM   wdi_blockingassignments AS blockedbyassignments
                     INNER JOIN
                     wdi_assignment AS blockedbyassignment
                     ON blockedbyassignment.wdi_assignmentid = blockedbyassignments.wdi_assignmentidtwo
              WHERE  blockedbyassignments.wdi_assignmentidone = a.wdi_assignmentid
                     AND blockedbyassignment.wdi_assignmenttypename = blockedbytype.wdi_name) AS numblockedbyofthattype
FROM   wdi_assignment AS a
       INNER JOIN
       wdi_assignmenttype AS ty
       ON a.wdi_assignmenttype = ty.wdi_assignmenttypeid
       INNER JOIN
       wdi_blockingassignmenttypes AS blockedbytypes
       ON ty.wdi_assignmenttypeid = blockedbytypes.wdi_assignmenttypeidone
       INNER JOIN
       wdi_assignmenttype AS blockedbytype
       ON blockedbytype.wdi_assignmenttypeid = blockedbytypes.wdi_assignmenttypeidtwo
       INNER JOIN
       wdi_assignment AS parent
       ON parent.wdi_assignmentid = a.wdi_parentassignment
WHERE  (SELECT count(1)
        FROM   wdi_blockingassignments AS blockedbyassignments
               INNER JOIN
               wdi_assignment AS blockedbyassignment
               ON blockedbyassignment.wdi_assignmentid = blockedbyassignments.wdi_assignmentidtwo
        WHERE  blockedbyassignments.wdi_assignmentidone = a.wdi_assignmentid
               AND blockedbyassignment.wdi_assignmenttypename = blockedbytype.wdi_name) = 0
       AND (SELECT count(1)
            FROM   wdi_assignment AS child
            WHERE  child.wdi_parentassignment = parent.wdi_assignmentid
                   AND child.wdi_assignmenttypename = blockedbytype.wdi_name) > 0;

[tsileo-wdi]

hmmm. I might be onto something. Doing it this way is returning different results.

select top 5 * from (
SELECT a.wdi_assignmentid,
             a.wdi_name AS thisassignment,
             a.wdi_assignmenttypename AS thistype,
             blockedbytype.wdi_name AS defblockedbytype,
             parent.wdi_name AS parentname,
             (SELECT count(1)
              FROM   wdi_assignment AS child
              WHERE  child.wdi_parentassignment = parent.wdi_assignmentid
                     AND child.wdi_assignmenttypename = blockedbytype.wdi_name) AS numchildrenofdefblockedbytype,
             (SELECT count(1)
              FROM   wdi_blockingassignments AS blockedbyassignments
                     INNER JOIN
                     wdi_assignment AS blockedbyassignment
                     ON blockedbyassignment.wdi_assignmentid = blockedbyassignments.wdi_assignmentidtwo
              WHERE  blockedbyassignments.wdi_assignmentidone = a.wdi_assignmentid
                     AND blockedbyassignment.wdi_assignmenttypename = blockedbytype.wdi_name) AS numblockedbyofthattype
FROM   wdi_assignment AS a
       INNER JOIN
       wdi_assignmenttype AS ty
       ON a.wdi_assignmenttype = ty.wdi_assignmenttypeid
       INNER JOIN
       wdi_blockingassignmenttypes AS blockedbytypes
       ON ty.wdi_assignmenttypeid = blockedbytypes.wdi_assignmenttypeidone
       INNER JOIN
       wdi_assignmenttype AS blockedbytype
       ON blockedbytype.wdi_assignmenttypeid = blockedbytypes.wdi_assignmenttypeidtwo
       INNER JOIN
       wdi_assignment AS parent
       ON parent.wdi_assignmentid = a.wdi_parentassignment
       ) t
       where t.numchildrenofdefblockedbytype > 0 
       and t.numblockedbyofthattype = 0

[MarkMpn]

I can't reproduce this at the moment, can you either share a solution with the definitions of these entities and some sample data, or get me access to a test system to diagnose this against? You can send any private details to sql4cds@markcarrington.dev if necessary.

[tsileo-wdi]

Hi Mark,

I was able to re-write my query to get the results I expected. I actually suspect was coming from the way I was joining tables together.

By nesting two of the joined tables into a virtual blockedbytype table like below, I got the expected results:

FROM wdi_assignment AS a INNER JOIN wdi_assignmenttype AS ty ON a.wdi_assignmenttype = ty.wdi_assignmenttypeid INNER JOIN (select wdi_assignmenttypeidone, wdi_assignmenttypeidtwo, at1.wdi_name as blockedbyassignmenttypename from wdi_blockingassignmenttypes INNER JOIN wdi_assignmenttype at1 ON at1.wdi_assignmenttypeid = wdi_blockingassignmenttypes.wdi_assignmenttypeidtwo ) blockedbytype on blockedbytype.wdi_assignmenttypeidone = ty.wdi_assignmenttypeid INNER JOIN wdi_assignment AS parent ON parent.wdi_assignmentid = a.wdi_parentassignment

I think my original construction was creating extra rows, which then caused the where criteria to work differently from the sub-select in the from.

So - I think let's close this issue out unless this leads you to think there was still an underlying bug in the execution plan.

cheers! Tony

[MarkMpn]

I think the original query should work - other ways of writing the query would probably be more efficient, but if you can share anything to help reproduce the issue with the original version that would be much appreciated.