Hive How to disable Semantic check 'Schema of both sides of union should match'

Question

Two same Hive 2.1

I have two hadoop cluster:

1. HDP 2.x with Hive 2.1.0 r6177e19d5af719688732bbffc2a7953295e62b0a (select version();)
2. CDH 6.x with Hive 2.1.1-cdh6.3.2 rb3393cf499504df1d2a12d34b4285e5d0c02be11 (select version();)

The same SQL

I run below SQL

set hive.execution.engine=spark;

with t1 as (
    select 1 as id, 11 as code
)
, t2 as (
    select 2 as id, '22' as code
)
select * from t1
union all
select * from t2;

HDP works

In the HDP cluster, this SQL executable.

But CDH failed

But in the CDH cluster, this SQL throws:

[42000][40000] Error while compiling statement: FAILED: SemanticException 9:14 Schema of both sides of union should match: Column code is of type int on first table and type string on second table. Error encountered near token 't2'

What's the difference? How to fix?

So what's the difference between these two Hive?

We have a lot of SQL like this and we cannot afford the cost to change them all using cast(xxx as string). How to make the CDH Hive work like HDP?

Thanks a lot for helping!

Answer 1

According to the Hive issue: https://issues.apache.org/jira/browse/HIVE-14251, this is a BUG, so there's no setting for it.

There are two solutions:

1. Adjust your SQLs. (Recommended)
2. Reverse this patch for your cluster. (A lot of effort)