UNION ALL two SELECTs with different column types - expected behaviour?

Question

What is the expected behaviour due to SQL Standard when we perform UNION on two tables with different data types:

create table "tab1" ("c1" varchar(max));
create table "tab2" ("c3" integer);
insert into tab1 values(N'asd'), (N'qweqwe');
insert into tab2 values(123), (345);
select
c_newname as myname
from
(
select "c1" as c_newname from "tab1"
union all
select "c3" from "tab2"
) as T_UNI;

MS SQL Server gives

Conversion failed when converting the varchar value 'asd' to data type int.

but what is defined in the standard?

Answer 1

From T-SQL UNION page:

The following are basic rules for combining the result sets of two queries by using UNION:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

When one datatype is VARCHAR and other is INTEGER then SQL Server will implicitly attempt to convert VARCHAR to INTEGER (the rules are described in the precedence table). If conversion fails for any row, the query fails. So this works:

INSERT INTO #tab1 VALUES(N'123'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2

But this does not:

INSERT INTO #tab1 VALUES(N'ABC'), (N'345');
INSERT INTO #tab2 VALUES(123), (345);
SELECT C1 FROM #tab1 UNION ALL SELECT C2 FROM #tab2
-- Conversion failed when converting the varchar value 'ABC' to data type int.

The rules for conversion are described here:

T-SQL Data Type Precedence

---

Having said that, you can explicitly convert your integer data to varchar in order to make the query work (the datatype of result would be varchar).