[Bug] Orca chooses the wrong column type for CTAS

[gfphoenix78]

Apache Cloudberry version

main

What happened

When running the CTAS statement, the orca may get wrong type for the columns. For example

create table t1 (id int, name varchar(1));
create table t2 (id int, name varchar(2));
insert into t1 values (1,'a');
insert into t2 values (1,'aa');
drop table if exists tmp_1;
create temp table tmp_1
as (select t1.id,t1.name from t1)
union all (select id,name from t2);

The output types of the two subqueries are not totally identical. postgres optimizer gets the type character varying for name, but orca optimizer gets the type character varying(1) for name, which is the types of the first sub-query.

What you think should happen instead

ORCA optimizer should behaves the same as postgres optimizer.

How to reproduce

create table t1 (id int, name varchar(1));
create table t2 (id int, name varchar(2));
insert into t1 values (1,'a');
insert into t2 values (1,'aa');

set optimizer = on;
create temp table tmp_1
as (select t1.id,t1.name from t1)
union all (select id,name from t2);

set optimizer = off;
create temp table tmp_2
as (select t1.id,t1.name from t1)
union all (select id,name from t2);

gpadmin=# \d+ tmp_1
                                               Table "pg_temp_22.tmp_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer              |           |          |         | plain    |             |              |
 name   | character varying(1) |           |          |         | extended |             |              |
Distributed randomly
Access method: heap

gpadmin=# \d+ tmp_2
                                             Table "pg_temp_22.tmp_2"
 Column |       Type        | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer           |           |          |         | plain    |             |              |
 name   | character varying |           |          |         | extended |             |              |
Distributed by: (id)
Access method: heap

Operating System

ubuntu/centos

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[gfphoenix78]

The append output the wrong target list by ORCA.