Stored Procedure DeleteGuests - CROSS JOIN !!

Posted: September 14, 2019 at 9:54 PM Quote #246377
(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)
This post/answer is useful
1
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
www.noptools.com
Posted: September 16, 2019 at 1:38 AM Quote #246406
Thanks a lot! We'll check it. Here is a s work item
This post/answer is useful
0
This post/answer is not useful

Please login or register
to vote for this post.

(click on this box to dismiss)
Interested in the dedicated Premium support services provided by core developers? Please visit http://www.nopcommerce.com/supportservices.aspx

Regards,
Andrei Mazulnitsyn
Premium support services
  • Dedicated premium support services provided by core developers are intended for persons who run mission critical websites, work on projects with tight deadlines, or want to get dedicated support.
Professional services
  • Want to open a new store? Want to take your store to the next level? Need a custom extension? We can customize nopCommerce to fit your store perfectly. Request a quote to get started.