UNION schema depends on branch order in case of array values

[findepi]

Describe the bug

> SELECT DISTINCT arrow_typeof(x[0]) FROM (SELECT make_array(2) x UNION ALL SELECT make_array(now()) x);
+---------------------------+
| arrow_typeof(x[Int64(0)]) |
+---------------------------+
| Int64                     |
+---------------------------+

but same query with union branches swapped gives different return type

> SELECT DISTINCT arrow_typeof(x[0]) FROM (SELECT make_array(now()) x UNION ALL SELECT make_array(2) x);
+---------------------------------------+
| arrow_typeof(x[Int64(0)])             |
+---------------------------------------+
| Timestamp(Nanosecond, Some("+00:00")) |
+---------------------------------------+

To Reproduce

SELECT DISTINCT arrow_typeof(x[0]) FROM (SELECT make_array(2) x UNION ALL SELECT make_array(now()) x);
SELECT DISTINCT arrow_typeof(x[0]) FROM (SELECT make_array(now()) x UNION ALL SELECT make_array(2) x);

Expected behavior

The schema (output type) of UNION ALL should not depend on syntactic order of union's branches.

Additional context

No response

[findepi]

So far i did not reproduce the problem for queries without arrays, so it might be array specific and thus related to #12291.

[jayzhan211]

We should return error since integer is not able to cast to timestamp and vice versa

D SELECT DISTINCT x[0] FROM (SELECT [now()] x UNION ALL SELECT [2] x);
Conversion Error: Unimplemented type for cast (INTEGER -> TIMESTAMP WITH TIME ZONE)
LINE 1: ...OM (SELECT [now()] x UNION ALL SELECT [2] x);

[findepi]

@jayzhan211 agree with you. I created #12291 for this separately.
Once this is solved, do you think there won't be any latent case where UNION schema depends on branch syntactical order?

[jayzhan211]

@jayzhan211 agree with you. I created #12291 for this separately. Once this is solved, do you think there won't be any latent case where UNION schema depends on branch syntactical order?

I guess we would need type_union_resolution for Union somewhere 🤔