Stored Procedure DeleteGuests - CROSS JOIN !!

(I'm surprised no one has noticed this before; this sp has been around since 3.x?!)

Note the "inner join [Customer]" in all the sub queries.  There is no ON condition for inner Customer table; the the inner [Customer] table has no alias).  This results in a cross join (Cartesian product) with full customer table:

PROCEDURE [dbo].[DeleteGuests]
...
  I^NSERT #tmp_guests (CustomerId)
  S^ELECT [Id] FROM [Customer] c with (NOLOCK)
  WHERE
...
  --shopping cart items
  ((@OnlyWithoutShoppingCart=0) OR (NOT EXISTS(S^ELECT 1 FROM [ShoppingCartItem] sci with (NOLOCK) inner join [Customer] with (NOLOCK) on sci.[CustomerId]=c.[Id])))
  AND
  --guests only
  (EXISTS(S^ELECT 1 FROM [Customer_CustomerRole_Mapping] ccrm with (NOLOCK) inner join [Customer] with (NOLOCK) on ccrm.[Customer_Id]=c.[Id] inner join [CustomerRole] cr with (NOLOCK) on cr.[Id]=ccrm.[CustomerRole_Id] WHERE cr.[SystemName] = N'Guests'))
  AND
  --no orders
  (NOT EXISTS(S^ELECT 1 FROM [Order] o with (NOLOCK) inner join [Customer] with (NOLOCK) on o.[CustomerId]=c.[Id]))
  AND
        ...

It seems that SQL Server allows join's on condition to be optional.  Probably a bad idea, considering they have CROSS JOIN ;)

You can see the result with this sample query for one customer Id (=1 ;typically the admin account) where I put an alias (x) on the inner Customer table:
(but be forewarned, if you've not been deleting guest accounts, this can return a lot of records!)

S^ELECT sci.Id, sci.CustomerId, sci.ProductId, sci.ShoppingCartTypeId, x.Id as SubqueryCustomerId
  FROM [ShoppingCartItem] sci with (NOLOCK) 
 inner join [Customer] x with (NOLOCK) on sci.[CustomerId]=1 --=c.[Id]

In any case, the fix is to change the sub-queries to use  ... WHERE ...
E.g.

  AND
  --no orders
  (NOT EXISTS(S^ELECT 1 FROM [Order] o with (NOLOCK) where o.[CustomerId]=c.[Id]))

(P.S. In SQL above, Remove the "^" chars in  S^ELECT, and I^NSERT.  I had to insert them because I was getting blocked by CloudFlare)

Thanks a lot! We'll check it. Here is a s work item

Done. You can see changes here.
Thanks for the suggestion.

I don't think LEFT JOIN is correct:

    LEFT JOIN (
      --guests only

New York wrote:

Yes, i'll fix it. Thank you very much.

Agreed the left join contributes to an incorrect result
Try this... replace code snip below

SELECT distinct c.*
  FROM [Customer] c with (NOLOCK)
    LEFT JOIN [ShoppingCartItem] sci with (NOLOCK) ON sci.[CustomerId] = c.[Id]
    JOIN (
      --guests only
      SELECT ccrm.[Customer_Id]
      FROM [Customer_CustomerRole_Mapping] ccrm with (NOLOCK)
        INNER JOIN [CustomerRole] cr with (NOLOCK) ON cr.[Id] = ccrm.[CustomerRole_Id]
      WHERE cr.[SystemName] = N'Guests'
    ) g ON g.[Customer_Id] = c.[Id]

GoRoos wrote:
It's already fixed. See changes here.