Very interesting question, but 1) version-specific; 2) with wrong explanation.
I could reproduce the error only in SQL Server 2005. In SQL Server 2008 R2, the query works fine.
This situation must be a bug in SQL Server 2005. For some reason, SQL Server 2005 raises an error with severity level = 20 (which means a fatal severity level, the client connection is terminated after receiving that message), and writes the following message to Event Log:
A user request from the session with SPID 60 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
These queries work fine in SQL Server 2005, although they are very similar to the QotD query:
select fileid, name from sysfiles
intersect
select cast(file_id as int), name from sys.master_files
where file_id in('1')
order by 2;
select file_id, name from sys.master_files
where file_id in('1')
intersect
select fileid, name from sysfiles
order by 2;
select fileid, name from sysfiles
intersect
select file_id + 0, name from sys.master_files
where file_id in('1')
order by 2;