'Does materialize function not work with tabular function argument in Kusto?

I created a simple function MaterializeRemoteInputTable below which accepts the output of a table as an input. To test if the input is materialized, I am using it twice to calculate two scalar values - x and y:

.create-or-alter function MaterializeRemoteInputTable(inputTable: (State:string)) {
let materializedInput = materialize(inputTable);
let x = toscalar(materializedInput | count );
let y = toscalar(materializedInput | where State contains "S" | count);
print x, y;
}

I am using the sample kusto database's function output as an input to the above function:

cluster('https://help.kusto.windows.net').database('Samples').
GetStatesWithPopulationSmallerThan(1000000)
| invoke MaterializeRemoteInputTable()

On querying the help.kusto.windows.net cluster with my ClientActivityId, I see two queries are executed:

.show commands-and-queries 
| where ClientActivityId contains "KE.RunQuery;<guid...>"

Above query outputs two rows:

GetStatesWithPopulationSmallerThan(long(1000000))|__executeAndCache|count as Count|limit long(1)|project ["b2fb..."]=["Count"]
GetStatesWithPopulationSmallerThan(long(1000000))|__executeAndCache|where (["State"] contains ("S"))|count as Count|limit long(1)|project ["5d0e2..."]=["Count"]

Since I have materialized the input to my MaterializeRemoteInputTable, why are two queries executed on the remote cluster, once each for x and y?



Solution 1:[1]

There is no issue with materialize & tabular argument to a function. The issue is a known limitation in materialize with cross cluster query that in some cases the materialization is not performed. Please note that this is only a performance issue and not a logical issue (query results are guaranteed to be correct).

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ziad Hamod