Through the use of KQL materialize() one can cache a tabular expression during query execution. This can be good for performance if the tabular expression is used many times in the query and is referenced multiple times in the query. However, materialize does not work with scalar values.
In my case I have a stored function that takes in tabular and scalar values and return one scalar value. The function is quite heavy, and the scalar result is referenced multiple times. Is there a way to cache the scalar value during query execution directly? (A workaround would be to store the value in a 1×1 table)
Minimum working example
This works:
let A = toscalar(1); union (print A), (print(A))
This does not work: let A = materialize(toscalar(print 1)); union (print(A)), (print(A))
Unable to cast object of type ‘Kusto.DataNode.RelationalQuery.ToScalarOperator’ to type ‘Kusto.DataNode.RelationalQuery.RelationalOperator’.
toscalar() returns already a constant value. You can try it out with the random function:
let r = toscalar(rand());
range i from 1 to 3 step 1
| project r