search also looks in deleted productvariants

example:
product with name = 'ABC' with
producvariants SKU = 'ABC-001'
producvariants SKU = 'ABC-002'
DELETED producvariants SKU = 'XYZ-001'

product with name = 'XYZ' with
producvariants SKU = 'XYZ-001'
producvariants SKU = 'XYZ-002'

Search on 'XYZ' will select both products because of the Deleted SKU which belonged to product 'ABC'

This is in my search stored procedure in 2.3:

    AND 
      (
        @ShowHidden = 1 OR pv.Deleted = 0
      )

That's going to filter out any deleted variants right there.

yep, that's what I thought as well. And that's why it is dangerous. You even think it is ok, but it is not.
It goes wrong before, when filling up the temp table #KeywordProducts; in the code below, I already introduced the fix.

--product name
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId])
SELECT p.Id
FROM Product p with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
  SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
ELSE
  SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


--product variant name
SET @sql = @sql + '
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
  SET @sql = @sql + 'CONTAINS(pv.[Name], @Keywords) '
ELSE
  SET @sql = @sql + 'PATINDEX(@Keywords, pv.[Name]) > 0 '

--FIX: product variant cannot be deleted
SET @sql = @sql + 'AND pv.Deleted = 0 '


--SKU
SET @sql = @sql + '
UNION
SELECT pv.ProductId
FROM ProductVariant pv with (NOLOCK)
WHERE '
IF @UseFullTextSearch = 1
  SET @sql = @sql + 'CONTAINS(pv.[Sku], @Keywords) '
ELSE
  SET @sql = @sql + 'PATINDEX(@Keywords, pv.[Sku]) > 0 '

--FIX: product variant cannot be deleted
SET @sql = @sql + 'AND pv.Deleted = 0 '

AndyMcKenna is right. Deleted product variants are filtered out later. There is no need to apply two "pv.Deleted = 0" where conditions when searching. It could slow down the procedure. Everything works as it should

ok, I rest my case...
I use the fix, as otherwise I always get plenty of wrong results caused by copying products where afterwards some variants were deleted.

I'm using 2.65 version

rinusripsus wrote:

Tested in 2.6 and I could not get the product 'ABC' to show up in search when PV 'XYZ-003' was deleted.

Next time test before posting.

daveb wrote:

I have been IT manager for a couple of years with some programming/architectural skills. I am certainly not experienced, but have overall skills from network design to programming.
And after long defending them, I must agree, IT people are arrogant. They are very prejudiced and lack respect.

Here is a more elaborated test:
I installed new instance of NopCommerce 2.65 with empty database.

Create Product and ProductVariants
/****** Object:  Table [dbo].[Product]    Script Date: 09/28/2012 10:22:59 ******/
SET IDENTITY_INSERT [dbo].[Product] ON
INSERT [dbo].[Product] ([Id], [Name], [ShortDescription], [FullDescription], [AdminComment], [ProductTemplateId], [ShowOnHomePage], [MetaKeywords], [MetaDescription], [MetaTitle], [SeName], [AllowCustomerReviews], [ApprovedRatingSum], [NotApprovedRatingSum], [ApprovedTotalReviews], [NotApprovedTotalReviews], [Published], [Deleted], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (1, N'ABC', NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, NULL, 1, 0, 0, 0, 0, 1, 0, CAST(0x0000A0DA0085D5AF AS DateTime), CAST(0x0000A0DA0085D5AF AS DateTime))
INSERT [dbo].[Product] ([Id], [Name], [ShortDescription], [FullDescription], [AdminComment], [ProductTemplateId], [ShowOnHomePage], [MetaKeywords], [MetaDescription], [MetaTitle], [SeName], [AllowCustomerReviews], [ApprovedRatingSum], [NotApprovedRatingSum], [ApprovedTotalReviews], [NotApprovedTotalReviews], [Published], [Deleted], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (2, N'XYZ', NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, NULL, 1, 0, 0, 0, 0, 1, 0, CAST(0x0000A0DA00878BE4 AS DateTime), CAST(0x0000A0DA00878BE4 AS DateTime))
SET IDENTITY_INSERT [dbo].[Product] OFF
/****** Object:  Table [dbo].[ProductVariant]    Script Date: 09/28/2012 10:22:59 ******/
SET IDENTITY_INSERT [dbo].[ProductVariant] ON
INSERT [dbo].[ProductVariant] ([Id], [ProductId], [Name], [Sku], [Description], [AdminComment], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId], [RequireOtherProducts], [RequiredProductVariantIds], [AutomaticallyAddRequiredProductVariants], [IsDownload], [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId], [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring], [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsShipEnabled], [IsFreeShipping], [AdditionalShippingCharge], [IsTaxExempt], [TaxCategoryId], [ManageInventoryMethodId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity], [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions], [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [CallForPrice], [Price], [OldPrice], [ProductCost], [SpecialPrice], [SpecialPriceStartDateTimeUtc], [SpecialPriceEndDateTimeUtc], [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [HasTierPrices], [HasDiscountsApplied], [Weight], [Length], [Width], [Height], [PictureId], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc], [Published], [Deleted], [DisplayOrder], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (1, 1, N'001', N'ABC-001', NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 1, 0, CAST(0.0000 AS Decimal(18, 4)), 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL, NULL, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(1000.0000 AS Decimal(18, 4)), 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), 0, NULL, NULL, 1, 0, 1, CAST(0x0000A0DA0085D5BD AS DateTime), CAST(0x0000A0DA008603A9 AS DateTime))
INSERT [dbo].[ProductVariant] ([Id], [ProductId], [Name], [Sku], [Description], [AdminComment], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId], [RequireOtherProducts], [RequiredProductVariantIds], [AutomaticallyAddRequiredProductVariants], [IsDownload], [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId], [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring], [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsShipEnabled], [IsFreeShipping], [AdditionalShippingCharge], [IsTaxExempt], [TaxCategoryId], [ManageInventoryMethodId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity], [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions], [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [CallForPrice], [Price], [OldPrice], [ProductCost], [SpecialPrice], [SpecialPriceStartDateTimeUtc], [SpecialPriceEndDateTimeUtc], [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [HasTierPrices], [HasDiscountsApplied], [Weight], [Length], [Width], [Height], [PictureId], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc], [Published], [Deleted], [DisplayOrder], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (2, 1, N'002', N'ABC-002', NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 1, 0, CAST(0.0000 AS Decimal(18, 4)), 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL, NULL, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(1000.0000 AS Decimal(18, 4)), 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), 0, NULL, NULL, 1, 0, 1, CAST(0x0000A0DA0086121B AS DateTime), CAST(0x0000A0DA0086121B AS DateTime))
INSERT [dbo].[ProductVariant] ([Id], [ProductId], [Name], [Sku], [Description], [AdminComment], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId], [RequireOtherProducts], [RequiredProductVariantIds], [AutomaticallyAddRequiredProductVariants], [IsDownload], [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId], [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring], [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsShipEnabled], [IsFreeShipping], [AdditionalShippingCharge], [IsTaxExempt], [TaxCategoryId], [ManageInventoryMethodId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity], [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions], [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [CallForPrice], [Price], [OldPrice], [ProductCost], [SpecialPrice], [SpecialPriceStartDateTimeUtc], [SpecialPriceEndDateTimeUtc], [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [HasTierPrices], [HasDiscountsApplied], [Weight], [Length], [Width], [Height], [PictureId], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc], [Published], [Deleted], [DisplayOrder], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (3, 1, N'001', N'XYZ-001', NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 1, 0, CAST(0.0000 AS Decimal(18, 4)), 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL, NULL, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(1000.0000 AS Decimal(18, 4)), 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), 0, NULL, NULL, 1, 0, 1, CAST(0x0000A0DA008759A8 AS DateTime), CAST(0x0000A0DA008759A8 AS DateTime))
INSERT [dbo].[ProductVariant] ([Id], [ProductId], [Name], [Sku], [Description], [AdminComment], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId], [RequireOtherProducts], [RequiredProductVariantIds], [AutomaticallyAddRequiredProductVariants], [IsDownload], [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId], [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring], [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsShipEnabled], [IsFreeShipping], [AdditionalShippingCharge], [IsTaxExempt], [TaxCategoryId], [ManageInventoryMethodId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity], [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions], [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [CallForPrice], [Price], [OldPrice], [ProductCost], [SpecialPrice], [SpecialPriceStartDateTimeUtc], [SpecialPriceEndDateTimeUtc], [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [HasTierPrices], [HasDiscountsApplied], [Weight], [Length], [Width], [Height], [PictureId], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc], [Published], [Deleted], [DisplayOrder], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (4, 2, N'001', N'XYZ-001', NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 1, 0, CAST(0.0000 AS Decimal(18, 4)), 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL, NULL, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(1000.0000 AS Decimal(18, 4)), 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), 0, NULL, NULL, 1, 0, 1, CAST(0x0000A0DA00878C70 AS DateTime), CAST(0x0000A0DA0087ABB4 AS DateTime))
INSERT [dbo].[ProductVariant] ([Id], [ProductId], [Name], [Sku], [Description], [AdminComment], [ManufacturerPartNumber], [Gtin], [IsGiftCard], [GiftCardTypeId], [RequireOtherProducts], [RequiredProductVariantIds], [AutomaticallyAddRequiredProductVariants], [IsDownload], [DownloadId], [UnlimitedDownloads], [MaxNumberOfDownloads], [DownloadExpirationDays], [DownloadActivationTypeId], [HasSampleDownload], [SampleDownloadId], [HasUserAgreement], [UserAgreementText], [IsRecurring], [RecurringCycleLength], [RecurringCyclePeriodId], [RecurringTotalCycles], [IsShipEnabled], [IsFreeShipping], [AdditionalShippingCharge], [IsTaxExempt], [TaxCategoryId], [ManageInventoryMethodId], [StockQuantity], [DisplayStockAvailability], [DisplayStockQuantity], [MinStockQuantity], [LowStockActivityId], [NotifyAdminForQuantityBelow], [BackorderModeId], [AllowBackInStockSubscriptions], [OrderMinimumQuantity], [OrderMaximumQuantity], [AllowedQuantities], [DisableBuyButton], [DisableWishlistButton], [AvailableForPreOrder], [CallForPrice], [Price], [OldPrice], [ProductCost], [SpecialPrice], [SpecialPriceStartDateTimeUtc], [SpecialPriceEndDateTimeUtc], [CustomerEntersPrice], [MinimumCustomerEnteredPrice], [MaximumCustomerEnteredPrice], [HasTierPrices], [HasDiscountsApplied], [Weight], [Length], [Width], [Height], [PictureId], [AvailableStartDateTimeUtc], [AvailableEndDateTimeUtc], [Published], [Deleted], [DisplayOrder], [CreatedOnUtc], [UpdatedOnUtc]) VALUES (5, 2, N'002', N'XYZ-002', NULL, NULL, NULL, NULL, 0, 0, 0, NULL, 0, 0, 0, 1, 10, NULL, 1, 0, 0, 0, NULL, 0, 100, 0, 10, 1, 0, CAST(0.0000 AS Decimal(18, 4)), 0, 0, 0, 10000, 0, 0, 0, 0, 1, 0, 0, 1, 10000, NULL, 0, 0, 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), NULL, NULL, NULL, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(1000.0000 AS Decimal(18, 4)), 0, 0, CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), CAST(0.0000 AS Decimal(18, 4)), 0, NULL, NULL, 1, 0, 1, CAST(0x0000A0DA0087BBF1 AS DateTime), CAST(0x0000A0DA0087BBF1 AS DateTime))
SET IDENTITY_INSERT [dbo].[ProductVariant] OFF

Search
Search for 'ABC' returns one result: ABC.
Search for 'XYZ' returns two results: ABC and XYZ.
This make sense, as sku XYZ-001 is a variant of Product ABC and so ABC is also shown up.

Delete
Go in administration and select product ABC.
Go to Product variants tab and click edit on variant with sku = 'XYZ-001'
Click Delete button to delete this variant.
If I look in the ProductVariants table, there still is the variant with sku = 'XYZ-001' for Product 'ABC', but with 'Deleted' = 1.

Search again
Afterwards, I log out, clean cache, reboot sql server and go back to website.
If I search for XYZ, it still shows two results: ABC and XYZ.


I didn't intend to make a big deal of it, just wanted to warn. I can handle the problem myself.
See https://www.nopcommerce.com/boards/t/8682/why-are-records-not-really-deleted-in-the-database.aspx.
Just wanted to say that it is not the best choice.

But, like Einstein said: you can't solve a problem with the mind that created it.
Best regards,
Reinout