We are trying to use a simple user-defined function (UDF) in the where clause of a query in Azure Cosmos DB, but it's not working correctly. The end goal is to query all results where the timestamp _ts is greater than or equal to yesterday, but our first step is to get a UDF working.
The abbreviated data looks like this:
[
{
"_ts": 1500000007
}
{
"_ts": 1500000005
}
]
Using the Azure Portal's Cosmos DB Data Explorer, a simple query like the following will correctly return one result:
SELECT * FROM c WHERE c._ts >= 1500000006
A simple query using our UDF incorrectly returns zero results. This query is below:
SELECT * FROM c WHERE c._ts >= udf.getHardcodedTime()
The definition of the function is below:
function getHardcodedTime(){
return 1500000006;
}
And here is a screenshot of the UDF for verification:
As you can see, the only difference between the two queries is that one query uses a hard-coded value while the other query uses a UDF to get a hard-coded value. The problem is that the query using the UDF returns zero results instead of returning one result.
Are we using the UDF correctly?
Update 1
When the UDF is updated to return the number 1, then we get a different count of results each time.
New function:
function getHardcodedTime(){
return 1;
}
New query: SELECT count(1) FROM c WHERE c._ts >= udf.getHardcodedTime()
Results vary with 7240, 7236, 7233, 7264, etc. (This set is the actual order of responses from Cosmos DB.)
SELECT udf.getHardcodedTime() FROM c? – Chun Liu