Intermittent Error with Materialized View in Kusto: "Index was outside the bounds of the array"

Question

Hello,

I'm encountering an issue with a materialized view in Kusto where I sometimes get an error when querying the view. Below is my materialized view definition:

// materialized-views
.create async materialized-view with (backfill=True, docString="Daily basis event", dimensionTables='DatabaseTable') AdtPropertyEventsDayAgg on table AdtPropertyEvents
{
    let DatabaseId = toscalar(DatabaseTable | project DatabaseId | take 1);
    AdtPropertyEvents
    | where Key == "lastKnownValue.value"
    | where ModelId !has "prediction"
    | extend key = strcat(DatabaseId, '-', Id)
    | extend DatabaseId = DatabaseId
    | extend Uur = bin(TimeStamp = TimeStamp, 0.25h)
    | summarize Value = sum(tolong(Value)), MaxValue = max(tolong(Value)), AvgValue = avg(tolong(Value)) 
    by DatabaseId, key, Id, Uur
}

When I query the materialized view like this:

AdtPropertyEventsDayAgg | take 1000

I receive the following error:

Error: Index was outside the bounds of the array.  
Timestamp=2024-09-25T14:38:16.2202188Z  
ServiceAlias=.....
MachineName=KSENGINE000001  
ProcessName=Kusto.WinSvc.Svc  
ProcessId=8096  
ThreadId=9332  
ActivityStack=(Empty activity stack)  
ClientRequestId: Kusto.Web.KWE.Query; b02cc7c8-bd4c-40bb-89dd-19376af28f8e; 36a15827-e5e8-48ac-b9c5-41e8938fd975

The issue is intermittent: sometimes the query works perfectly, and other times it consistently fails. It appears to be time-bound or resource-bound, as the query will succeed for several minutes (e.g., 12 minutes) and then repeatedly fail for a different period (e.g., 6 minutes).

Has anyone encountered this issue or have suggestions for how to troubleshoot or resolve this?

Thank you!

Answer 1

Hi @Wes Verhagen
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Ask: I'm encountering an issue with a materialized view in Kusto where I sometimes get an error when querying the view. Below is my materialized view definition:

KustoAI ConvertCopy

// materialized-views

When I query the materialized view like this:

AdtPropertyEventsDayAgg | take 1000

I receive the following error:

Error: Index was outside the bounds of the array.  
Timestamp=2024-09-25T14:38:16.2202188Z  
ServiceAlias=.....
MachineName=KSENGINE000001  
ProcessName=Kusto.WinSvc.Svc  
ProcessId=8096  
ThreadId=9332  
ActivityStack=(Empty activity stack)  
ClientRequestId: Kusto.Web.KWE.Query; b02cc7c8-bd4c-40bb-89dd-19376af28f8e; 36a15827-e5e8-48ac-b9c5-41e8938fd975

The issue is intermittent: sometimes the query works perfectly, and other times it consistently fails. It appears to be time-bound or resource-bound, as the query will succeed for several minutes (e.g., 12 minutes) and then repeatedly fail for a different period (e.g., 6 minutes).

Has anyone encountered this issue or have suggestions for how to troubleshoot or resolve this?
Solution: I’ve received a response from Microsoft, and they’ve identified the root cause of the issue. It’s related to one of the group-by keys in the materialized view, which is calculated by a toscalar() from another dimension table, causing a bug in certain edge cases.

As a workaround, you can use the materialized_view() function to query the view, which works until the fix is fully deployed. I’ve tested this solution, and it’s effective. Microsoft has confirmed that the bug will be fixed in the next deployment.

I wanted to share this so that others can also find the solution.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members

Answer 2

Hi @Sander van de Velde | MVP ,

Thanks for your reply! I’ve received a response from Microsoft, and they’ve identified the root cause of the issue. It’s related to one of the group-by keys in the materialized view, which is calculated by a toscalar() from another dimension table, causing a bug in certain edge cases.

As a workaround, you can use the materialized_view() function to query the view, which works until the fix is fully deployed. I’ve tested this solution, and it’s effective. Microsoft has confirmed that the bug will be fixed in the next deployment.

I wanted to share this so that others can also find the solution.

Best, Wes