gfphoenix78 opened a new issue, #1431:
URL: https://github.com/apache/cloudberry/issues/1431
### Apache Cloudberry version
main
### What happened
When running the CTAS statement, the orca may get wrong type for the
columns. For example
```sql
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
```sql
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);
```
```sql
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
- [x] I agree to follow this project's [Code of
Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
