Data type precedence while using UNION statement differs in behavior between SQL Server 2000 and SQL Server 2005

It originally seemed to be a quite complex problem, involving an update trigger that was failing in SQL Server 2005, but had never failed in SQL Server 2000, giving a message like

"Syntax error converting the varchar value 'whatever' to a column of data type INT "

when the calling code was doing something simple like "UPDATE TBL SET SOMECOL='NEWVALUE' WHERE PK = 123"

<Trigger Code Snippet>

INSERT INTO DBO.AUDIT_TABLE ( ... COLNAME, OLDVALUE, NEWVALUE )

SELECT ... 'SOMECOL', D.SOMECOL,I.SOMECOL FROM INSERTED I JOIN DELETED D ON I._PK = D._PK WHERE ...

UNION ALL

SELECT ... 'OTHERCOL', D.OTHERCOL, I.OTHERCOL FROM INSERTED I JOIN DELETED D ON I._PK = D._PK WHERE ...

UNION ALL

SELECT ... 'THIRDCOL', D.THIRDCOL, I.THIRDCOL FROM INSERTED I JOIN DELETED D ON I._PK = D._PK WHERE ...

[ETC]

</Trigger Code Snippet>

where each of the selects was producing 1 row for each updated row where the old and new values of the particular column had changed, or one was null but the other wasn't.

To explain what the trigger was actually doing I found that there is a difference in behavior between SQL Server 2000 and SQL Server 2005 when we execute the following code:

<Code Snippet>

IF OBJECT_ID('DBO.AUDIT') IS NOT NULL DROP TABLE DBO.AUDIT

GO

CREATE TABLE DBO.AUDIT (OLD_VALUE VARCHAR(255) , NEW_VALUE VARCHAR(255) );

INSERT DBO.AUDIT(OLD_VALUE, NEW_VALUE)

SELECT '10 YRS', '15 YRS'

UNION ALL

SELECT 5, 10;

</Code Snippet>

If you run the above code in SQL Server 2000, it succeeds the values returned from the UNION ALL are apparently converted to VARCHAR(255) and successfully inserted into the table.

However, if you comment out the INSERT line, so you're just doing a simple UNION ALL select, you get the error

"Syntax error converting the varchar value '10 yrs' to a column of data type int."  

In SQL Server 2000 UNION ALL understands that the result column types should be ones that will match the table into which the UNION ALL result will be inserted.

<Query plan in SQL Server 2000>

insert dbo.Audit(old_value, new_value) select '10 yrs', '15 yrs' UNION ALL select 5, 10

  |--Table Insert(OBJECT:([Northwind].[dbo].[Audit]), SET:([Audit].[old_value]=[Union1004], [Audit].[new_value]=[Union1005]))

       |--Top(ROWCOUNT est 0)

            |--Concatenation

|--Constant Scan

|--Constant Scan

</Query plan in SQL Server 2000>

However, in SQL Server 2005, you get that error even when the insert is active. SQL Server 2005 is apparently evaluating the UNION without knowing what the target column types are supposed to be, and it fails when it decides that the type should be int and encounters a varchar value that won't convert.

<Query plan in SQL Server 2005>

  |--Table Insert(OBJECT:([AdventureWorks].[dbo].[Audit]), SET:([AdventureWorks].[dbo].[Audit].[old_value] = [Expr1010],[AdventureWorks].[dbo].[Audit].[new_value] = [Expr1011]))

       |--Compute Scalar(DEFINE:([Expr1010]=CONVERT_IMPLICIT(varchar(255),[Union1008],0), [Expr1011]=CONVERT_IMPLICIT(varchar(255),[Union1009],0)))

            |--Top(ROWCOUNT est 0)

|--Constant Scan(VALUES:((CONVERT_IMPLICIT(int,'10 yrs',0),CONVERT_IMPLICIT(int,'15 yrs',0)),((5),(10))))

</Query plan in SQL Server 2005>

We normally are under the impression that the data types of the columns in a UNION were determined by the data types of the columns in the first SELECT. However, it's only the column names, not the types, that are determined by the first SELECT. The type precedence rules say that when you've got both int and varchar values that the resulting type should be int, hence the failure.

Please refer to the following article for the complete list of Data Precedence

Data Type Precedence (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

Upon comparing the query plans it is clear that in SQL Server 2005 Constant Scan operator does a CONVERT_IMPLICIT which causes the statement to fail.

In my case it was pretty clear that the customer had to change the code in SQL Server 2005 to generate the triggers.

Rajesh Setlem,
SE, Microsoft SQL Server.

Reviewed by

Amit Banerjee
Technical Lead, Microsoft SQL Server