opt: cast UNION inputs to identical types

[RaduBerinde]

Info from #58285: the following case causes an internal error:

CREATE TABLE t (_int4 INT4);
INSERT INTO t VALUES (2);
WITH
  cte (col)
    AS (
      SELECT * FROM (VALUES (1:::INT8))
      UNION
        SELECT
          *
        FROM
          (VALUES ((SELECT _int4 FROM t LIMIT 1)))
    )
SELECT
  col
FROM
  cte;

EXPLAIN (TYPES):

  • root
  │ columns: (col int)
  │
  ├── • render
  │   │ columns: (col int)
  │   │ estimated row count: 2
  │   │ render 0: (column1)[int]
  │   │
  │   └── • union
  │       │ columns: (column1 int)
  │       │ estimated row count: 2
  │       │
  │       ├── • values
  │       │     columns: (column1 int)
  │       │     size: 1 column, 1 row
  │       │     row 0, expr 0: (1)[int]
  │       │
  │       └── • values
  │             columns: (column1 int4)
  │             size: 1 column, 1 row
  │             row 0, expr 0: (@S1)[int4]
  │
  └── • subquery
      │ id: @S1
      │ original sql: (SELECT _int4 FROM t LIMIT 1)
      │ exec mode: one row
      │
      └── • scan
            columns: (_int4 int4)
            estimated row count: 1 (missing stats)
            table: t@primary
            spans: LIMITED SCAN
            limit: 1

We're UNIONing INT (treated as INT8) with INT4 which trips up the vectorized engine.

[RaduBerinde]

A simpler repro:

create table ab (a int4, b int8);
insert into ab values (1,1), (1,2), (2,1), (2,2);
set vectorize = experimental_always;
select a from ab union select b from ab;
ERROR: mismatched types at index 0: expected [int4]	actual [int] 

[yuzefovich]

Have another repro here.