Posted on 16/04/2019 19:21:35
Hi Anders,
A couple of comments to your post.
First, I've reproduced the issue and I've implemented a fix. It has bug number 63665 and is currently awaiting QA testing. To elaborate a little on the issue, I can say that it's not caused by any SQL execution. As mentioned previously, we use a CommandBuilder class to create queries that parameterized queries. The Add function has an overload which accepts a formattable text and a param array with the values. The function then turns the placeholders and values into parameters. Because it accepts a param array, just like string.Format, and GetProductSql passes an array object as the first argument, the values in the array are expanded and this causes a mismatch between the number of placeholders and the number of values. This is the cause of the exception.
Second, the debug log doesn't show the query that is actually executed on the database, unfortunately. I've executed the same query on my solution and I've attached the query text as seen by the SQL Profiler. It is executable. Also, please note that this query isn't actually related to the original issue but traces back to GetPagedProductsByGroupIdsWithCount.
I hope this helps.
- Jeppe
exec sp_executesql N'SELECT PagedProducts.ProductSelectionTotalCount, ProductOutputs.* FROM ( SELECT ProductResults.ProductAutoID, ProductResults.ProductSelectionTotalCount, ProductResults.RowNumber FROM ( SELECT EcomProducts.ProductAutoID, COUNT(EcomProducts.ProductID) OVER () AS ProductSelectionTotalCount, ROW_NUMBER() OVER(ORDER BY ProductName COLLATE Latin1_General_CI_AS ASC, ProductID ASC) AS RowNumber FROM ( SELECT DISTINCT AssortedGroupProductSelections.GroupProductRelationProductID FROM ( SELECT DISTINCT GroupProductSelections.GroupProductRelationProductID , AssortmentItems.AssortmentItemLanguageID FROM ( SELECT EcomGroupProductRelation.GroupProductRelationProductID FROM EcomGroupProductRelation WHERE ( EcomGroupProductRelation.GroupProductRelationGroupID IN ( @p0 , @p1 , @p2 , @p3 , @p4 , @p5 ) ) GROUP BY EcomGroupProductRelation.GroupProductRelationProductID ) AS GroupProductSelections LEFT OUTER JOIN EcomAssortmentItems AS AssortmentItems ON ( AssortmentItems.AssortmentItemProductID = GroupProductSelections.GroupProductRelationProductID ) LEFT OUTER JOIN EcomAssortments AS Assortments ON ( Assortments.AssortmentID = AssortmentItems.AssortmentItemAssortmentID ) WHERE ( ( AssortmentItems.AssortmentItemAssortmentID IN ( @p6 ) ) OR ( AssortmentItems.AssortmentItemAssortmentID IS NULL ) OR ( ( COALESCE( AssortmentItems.AssortmentItemProductVariantID, '''' ) <> '''' ) AND ( NOT EXISTS( SELECT MasterAssortmentItems.AssortmentItemAutoID FROM EcomAssortmentItems AS MasterAssortmentItems WHERE ( MasterAssortmentItems.AssortmentItemProductID = AssortmentItems.AssortmentItemProductID ) AND ( MasterAssortmentItems.AssortmentItemLanguageID = AssortmentItems.AssortmentItemLanguageID ) AND ( COALESCE( MasterAssortmentItems.AssortmentItemProductVariantID, '''' ) = '''' ) ) ) AND ( EXISTS( SELECT VariantProducts.ProductAutoID FROM EcomProducts AS VariantProducts WHERE ( VariantProducts.ProductID = AssortmentItems.AssortmentItemProductID ) AND ( VariantProducts.ProductLanguageID = AssortmentItems.AssortmentItemLanguageID ) AND ( COALESCE( VariantProducts.ProductVariantID, '''' ) <> '''' ) AND ( NOT EXISTS ( SELECT VariantAssortmentItems.AssortmentItemProductVariantID FROM EcomAssortmentItems AS VariantAssortmentItems WHERE ( VariantAssortmentItems.AssortmentItemProductID = AssortmentItems.AssortmentItemProductID ) AND ( VariantAssortmentItems.AssortmentItemLanguageID = AssortmentItems.AssortmentItemLanguageID ) AND ( COALESCE( VariantAssortmentItems.AssortmentItemProductVariantID, '''' ) = COALESCE( VariantProducts.ProductVariantID, '''' ) ) ) ) ) ) ) ) ) AS AssortedGroupProductSelections ) AS ProductSelections INNER JOIN EcomProducts ON ( EcomProducts.ProductID = ProductSelections.GroupProductRelationProductID ) AND ( EcomProducts.ProductLanguageID = @p7 ) AND ( EcomProducts.ProductActive = @p8 ) AND ( ( ISNULL( EcomProducts.ProductPeriodId, '''' ) = '''' ) OR ( EcomProducts.ProductPeriodId IN ( SELECT EcomPeriods.PeriodID FROM EcomPeriods WHERE ( ( COALESCE( EcomPeriods.PeriodShowProductsAfterExpiration, 0 ) = @p8 ) OR ( ( COALESCE( EcomPeriods.PeriodActive, 1 ) = @p8 ) AND ( ( COALESCE( EcomPeriods.PeriodAlways, 0 ) = @p8 ) OR ( ( COALESCE( EcomPeriods.PeriodEndDate, @p9 ) >= @p9 ) AND ( COALESCE( EcomPeriods.PeriodStartDate, @p9 ) <= @p9 ) ) ) ) ) AND ( EcomPeriods.PeriodHidden IN ( 1, @p8 ) ) ) ) ) AND ( COALESCE( EcomProducts.ProductVariantID, '''' ) = '''') AND ( ISNULL(EcomProducts.ProductHidden, 0) <> @p8 ) ) AS ProductResults WHERE ( ProductResults.RowNumber BETWEEN @p10 And @p11 ) ) As PagedProducts INNER JOIN EcomProducts As ProductOutputs On ( ProductOutputs.ProductAutoID = PagedProducts.ProductAutoID ) ORDER BY PagedProducts.RowNumber ASC',N'@p0 nvarchar(6),@p1 nvarchar(6),@p2 nvarchar(6),@p3 nvarchar(7),@p4 nvarchar(7),@p5 nvarchar(8),@p6 nvarchar(25),@p7 nvarchar(5),@p8 bit,@p9 datetime,@p10 int,@p11 int',@p0=N'GROUP5',@p1=N'GROUP7',@p2=N'GROUP8',@p3=N'GROUP27',@p4=N'GROUP65',@p5=N'GROUP123',@p6=N'ASSORTMENT1'',''ASSORTMENT2',@p7=N'LANG1',@p8=1,@p9='2019-04-16 16:58:29.180',@p10=1,@p11=10
