inconsistent interaction where the "where" clause does not filter out a null value.

[tbethe]

I have an example where an event is not filtered out, when I argue it should be.
Any explanation of why this is normal behaviour would also be amazing. I am confused.

Use-case: Check if there was a new row in the table in the last 2 hours.

To demonstrate, I create an empty table, summarize it based on max TimeGenerated.
However, if the table is empty, summarize still generates an event/row, but it is filled with only NULLs.

However, when I then filter for only events from max 2 hours ago, the event with all columns filled with NULLs is still returned!

The documentation of the where clause is very clear in that when the column is NULL, these are not returned.
I quote from the docs:

Notes Null values: all filtering functions return false when compared with null values. You can use special null-aware functions to write queries that handle null values.

In this example, we get an event, with empty (NULL) columns. This is strange to me.

let EmptyTable = datatable(TimeGenerated: datetime, SomeField: guid)[];
EmptyTable
| summarize arg_max(TimeGenerated, *)
| where TimeGenerated >= ago(2h)

To double check, I did the following: If I first extend the boolean condition and then put it in the where clause, it is filtered. This feels arbitrary now.

let EmptyTable = datatable(TimeGenerated: datetime, SomeField: guid)[];
EmptyTable
| summarize arg_max(TimeGenerated, *)
| extend time_gen_ago2h = TimeGenerated >= ago(2h)
| where time_gen_ago2h

[mattwar]

Moving this issue to discussion as it is not about this library but the behavior of the query engine.

[mattwar]

If you were using the count() aggregate instead of arg_max() on an empty table, you would expect to get a single row with count of zero. The summarize operator has to behave the same regardless of the aggregates used.

[tbethe]

I understand that the summarize operator always returns a single row. This is not what my confusion is about.

It is about an inconsistency in the where clause. It is about the fact that this row is not filtered by the where clause when it should be filtered. Moreover, if you first calculate the same boolean and then filter on it using the where clause, it is filtered. This is inconsistent.

Also, you mentioned that this is not the right place to address this issue. Does anyone know what would be the correct place?

[yifatsor]

@tbethe - thank you for letting us know about this issue. We have fixed it, the fix will be rolled out as part of next deployments. FWIW, the root cause is a bug in query optimizer which translated this query:

let EmptyTable = datatable(TimeGenerated: datetime, SomeField: guid)[]; EmptyTable | summarize arg_max(TimeGenerated, *) | where TimeGenerated >= ago(2h)

to this:

let EmptyTable = datatable(TimeGenerated: datetime, SomeField: guid)[]; EmptyTable | where TimeGenerated >= ago(2h) | summarize arg_max(TimeGenerated, *)

The latter returns a single record (with null values) since that is the behavior of the summarize operator on empty input, when there are no group by keys (if you add a group by key it does work as expected). We fixed the optimization to apply the filter after the aggregation as well, so that the aggregation result is still filtered. Thank you for reporting this!

[tbethe]

Thank you @yifatsor ! You're welcome.