London Dev Meetup Rescheduled! Due to unavoidable reasons, the event has been moved to 21st May. Speakers remain the same—any changes will be communicated. Seats are limited—register here to secure your spot!

Bug in ecf_CatalogEntrySearch_Init when @DatabaseClockOffsetMS=0?

Vote:
 

Hi Episerver

We are currently using the Episerver.Commerce.Core version 9.23.

I get some strange errors in my log when I try to incrementally update the Commerce Search index. I think I have located the problem to a bug in the ecf_CatalogEntrySearch_Init stored procedure.

I have tried to call the stored procedure manually from SQL Server Management Studio, and then tried to insert PRINT statements in the stored procedure to see what happends.

If the @DatabaseClockOffsetMS = 0, then the SQL statement below is generated. Notice the parts highlighted with bold.

I guess this part of the stored procedure is not executed:

if (isnull(@DatabaseClockOffsetMS, 0)> 0)

begin

set @EarliestModifiedDate =DATEADD(MS,-@DatabaseClockOffsetMS, @EarliestModifiedDate)

set @EarliestModifiedFilter =' Modified >= cast('''+CONVERT(nvarchar(100), @EarliestModifiedDate, 127)+''' as datetime)'

set @LatestModifiedFilter =' and Modified <= castconvertnvarchar100 latestmodifieddate 127 as datetime>

end

-----

Generated SQL statement:

select distinct '69BECD2C-7688-4838-B832-F50AC6DB70D2'

,ROW_NUMBER()over (orderby e.CatalogEntryId)

, e.CatalogEntryId

, e.ApplicationId

from CatalogEntry e

where e.ApplicationId ='A5390361-7309-45C4-8F6F-FD9DE6C9E5B4'and

e.CatalogId = 2 and

e.CatalogEntryId in

(select ObjectId

from CatalogContentEx

where ObjectTypeId = 0 and

Modified >=cast('2016-12-01T00:00:00'asdatetime)and

Modified <=cast('2016-12-17t00:00:00'asdatetime)>

unionall

select CatalogEntryId

from NodeEntryRelation

where and

Modified >=cast('2016-12-01T00:00:00'asdatetime)and

Modified <=cast('2016-12-17t00:00:00'asdatetime)>

unionall

select cast(ObjectKey asint)as CatalogEntryId

from ApplicationLog

where [Source] ='catalog'and

[Operation] ='Modified'and

[ObjectType] ='relation' and

and

Created >=cast('2016-12-01T00:00:00'asdatetime)and

Created <= cast2016-12-17t00:00:00 as datetime>

#173240
Dec 16, 2016 14:12
Vote:
 

Hi,

Sorry for the late reply - it's not until today when I can spend some time to read and understand the problem.

It sounds like a bug to me - I will file one so we can look at it. Thank you for bringing this to our attention.

Regards,

/Q

#173897
Jan 12, 2017 10:31
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.