feat: UNION ALL should cast input columns to least restrictive types

[algosday]

Describe the bug

A simple SQL query produces an error in RW but works fine in PostgreSQL. This SQL statement is generated by dbt as an example.

This bug is not blocking #7577 but will prevent our users from completing the dbt getting started guide.

To Reproduce

Execute the following SQL on RW.

select 1 as id
    union all
    select null as id;

Got an error,

Query 1 ERROR: ERROR:  QueryError: Invalid input syntax: UNION types INT32 of column id is different from types VARCHAR of column id

Expected behavior

It should execute successfully and produce a result.

id
----
1
NULL

( PostgreSQL 14.6 )

Additional context

No response

[xiangjinwu]

PostgreSQL's rule is inconsistent between select and values:

test=# select 1 union all select null;
 ?column? 
----------
        1
         
(2 rows)

test=# select 1 union all values (null);
ERROR:  UNION types integer and text cannot be matched

We will need to handle each case differently.

[xiangjinwu]

Oops, we have not supported align_types even when union int and smallint:

dev=> select 1 union all select 2::smallint;
ERROR:  QueryError: Invalid input syntax: UNION types INT32 of column ?column? is different from types INT16 of column int2

[xiangjinwu]

select union select has been supported by #11452 but there are more cases, so keeping this issue open

• values union values: values (1) union values (1.2);
• union union select: select 1 union all select 2 union all select 1.5;