SQL union between NULL and VARCHAR error

Question

I have two views with identical columns. One of the columns on the first view is generated 'on the fly' and set as NULL and the same column on the other view has values stored as varchar. I have a stored proc that looks like this:

ALTER PROCEDURE [dbo].[mi_GetLearners]  
(@centrename nvarchar(200))  
AS  

SELECT [centrename]  
      ,[Name]  
      ,[Username] --generated on the fly as NULL
FROM  [DB1].[dbo].[vw_Learners]  
WHERE [centrename] = @centrename  

UNION  

SELECT [centrename]  
      ,[Name]   
      ,[Username] --values stored as varchar   
FROM  [Linked_Server].[DB2].[dbo].[vw_Learners]  
WHERE [centrename] = @centrename 

DB1 is on SQL Server 2008 R2
DB2 is on SQL server 2005

When I run the stored proc I get the following error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'someusername' to data type int.

Why is it trying to convert the value to int datatype as the other column is set as NULL? If I instead change the second column from NULL to ' ' the stored proc works fine... I'm really baffled as to why a union between a varchar column and NULL column generated in a select statement would throw such an error... any ideas?

EDIT: I'm looking for an explanation rather than a solution...

EDIT 2: Running the following code:

CREATE VIEW vw_myview
AS
SELECT NULL AS MyColumn

EXECUTE sp_help vw_myview

Returns:

    Type    Column_name
     int       MyColumn

Answer 1

The problem is that NULL is (to within some wiggle room) a member of every data type.

When any SELECT query is being run, the contents of each column must be of one type, and only one type. When there are a mixture of values in a column (including NULLs), the type can obviously be determined by examining the types of the non-NULL values, and appropriate conversions are performed, as necessary.

But, when all rows contain NULL for a particular column, and the NULL hasn't been cast to a particular type, then there's no type information to use. So, SQL Server, somewhat arbitrarily, decides that the type of this column is int.

create view V1
as
    select 'abc' as Col1,null as Col2
go
create view V2
as
    select 'abc' as Col1,CAST(null as varchar(100)) as Col2

V1 has columns of type varchar(3) and int.

V2 has columns of type varchar(3) and varchar(100).

Answer 2

I'd expect the type of the field to be determined by the first SELECT in the union. You may try changing the order of your two selects or change to CAST(NULL AS VARCHAR(...)).