Why does sys.fn_xe_file_target_read_file require an explicit cast on datetime2 column

extended-eventssql serversql-server-2022

According to the documentation the returned column timestamp_utc should be of type datetime2(7)

But when I query like this

SELECT 
    *
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE timestamp_utc > dateadd(hour, -1, GETUTCDATE())

It returns no rows. It returns the rows only when I add an explicit cast to datetime2

SELECT 
    *
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE cast(timestamp_utc as datetime2(7)) > dateadd(hour, -1, GETUTCDATE())

Which matches the last example in the documentation (even though no attention is drawn to it)

Why is that?

Best Answer

Erland Sommarskog asked me about this in May 2022.

It's a bug exposed when a predicate is pushed down into the internal streaming table-valued function. The return type is indeed datetime2(7) but the source is a Windows FILETIME.

The predicate is pushed down below the internal conversion, so the server ends up trying to compare a datetime2 with a FILETIME, which does not work.

_{Pushed down predicate}

When you add the explicit conversion, the predicate can no longer be pushed into the TVF so it appears as a separate Filter:

_{Predicate evaluated in a separate filter}

I have no idea why this hasn't been fixed.

Erland subsequently created a feedback item: Filtering output from fn_xe_file_target_read_file on timestamp_utc returns no rows

Related Solutions

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

Sql-server – Why does SUM() return 0 when no rows in table

The result is correct, i.e. according to the standards, as far as I can understand it.

The related question: What is the correct result for this query? contains the paragraph that explains it:

The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY clause)

1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

2) Case:

a) If there are no grouping columns, then the result of the <group by clause> is the grouped table consisting of T as its only group.

So, when there is no GROUP BY - or when we have GROUP BY () - the result should be a single group. It doesn't matter if the table is empty or not. You'll get a single row^* in the result.
If the table is empty (and the SUM() function is used), you get a single row with NULL as value - which you convert to 0 using ISNULL().

When you have GROUP BY t.id, you'll have one row for every group. But there are 0 groups in your case, so 0 rows in the result.

*_{: SQL-Server will however return 0 rows for the query with GROUP BY (), which is contradicting the standard.}

Best Answer

Related Solutions

Sql-server – Why does OBJECT_ID return NULL in a computed column

Sql-server – Why does SUM() return 0 when no rows in table

Related Question