Repository: matrixorigin/matrixone
Pull request #12615 (2023-11-09 04:03:26)

[fix] the issue of view changing the original data format of char
## What type of PR is this?

- [ ] API-change
- [x] BUG
- [ ] Improvement
- [ ] Documentation
- [ ] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue https://github.com/matrixorigin/MO-Cloud/issues/400

## What this PR does / why we need it:
Changing the union rules for char type.

CLAassistant: [![CLA assistant check](https://cla-assistant.io/pull/badge/not_signed)](https://cla-assistant.io/matrixorigin/matrixone?pullRequest=12615) <br/>Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our [Contributor License Agreement](https://cla-assistant.io/matrixorigin/matrixone?pullRequest=12615) before we can accept your contribution.<br/><sub>You have signed the CLA already but the status is still pending? Let us [recheck](https://cla-assistant.io/check/matrixorigin/matrixone?pullRequest=12615) it.</sub>

Patch:
diff --git a/pkg/sql/plan/query_builder.go b/pkg/sql/plan/query_builder.go
index 1deeb6430..3bdce4e1d 100644
--- a/pkg/sql/plan/query_builder.go
+++ b/pkg/sql/plan/query_builder.go
@@ -1556,13 +1556,17 @@ func (builder *QueryBuilder) buildUnion(stmt *tree.UnionClause, astOrderBy tree.
 			var targetArgType types.Type
 			if len(argsCastType) == 0 {
 				targetArgType = tmpArgsType[0]
+				// if string union string, different length may cause error.
+				if targetArgType.Oid == types.T_varchar || targetArgType.Oid == types.T_char {
+					for _, typ := range argsType {
+						if targetArgType.Width < typ.Width {
+							targetArgType.Width = typ.Width
+						}
+					}
+				}
 			} else {
 				targetArgType = argsCastType[0]
 			}
-			// if string union string, different length may cause error. use text type as the output
-			if targetArgType.Oid == types.T_varchar || targetArgType.Oid == types.T_char {
-				targetArgType = types.T_text.ToType()
-			}
 
 			if targetArgType.Oid == types.T_binary || targetArgType.Oid == types.T_varbinary {
 				targetArgType = types.T_blob.ToType()


Test patch:
diff --git a/test/distributed/cases/view/view-from-subquery.result b/test/distributed/cases/view/view-from-subquery.result
index bcd1899ae..2f0375be2 100644
--- a/test/distributed/cases/view/view-from-subquery.result
+++ b/test/distributed/cases/view/view-from-subquery.result
@@ -353,3 +353,29 @@ SanFran    SanFransiscoPublic    1
 Berkeley    BerkeleyPublic1    1
 Berkeley    BerkeleyPublic2    1
 drop view v1;
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+drop table if exists t4;
+drop view if exists v1;
+drop view if exists v2;
+create table t1 (col1 varchar(20), col2 char(20));
+create table t2 (col1 varchar(64), col2 char(64));
+create table t3 (col1 varchar(20), col2 char(20));
+create table t4 (col1 varchar(20), col2 char(20));
+create view v1 as select t1.col1 as col1, t1.col2 as col2 from t1 union select t2.col1 as col1, t2.col2 as col2 from t2;
+desc v1;
+Field    Type    Null    Key    Default    Extra    Comment
+col1    VARCHAR(64)    YES        null
+col2    CHAR(64)    YES        null
+create view v2 as select t3.col1 as col1, t3.col2 as col2 from t3 union select t4.col1 as col1, t4.col2 as col2 from t4;
+desc v2;
+Field    Type    Null    Key    Default    Extra    Comment
+col1    VARCHAR(20)    YES        null
+col2    CHAR(20)    YES        null
+drop view v1;
+drop view v2;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;
diff --git a/test/distributed/cases/view/view-from-subquery.sql b/test/distributed/cases/view/view-from-subquery.sql
index 1accd8b8a..5d03fbd1f 100755
--- a/test/distributed/cases/view/view-from-subquery.sql
+++ b/test/distributed/cases/view/view-from-subquery.sql
@@ -165,3 +165,24 @@ select * from (select city,libname1,count(libname1) as a from t3 join t1 on libn
 create view v1 as select * from (select city,libname1,count(libname1) as a from t3 join t1 on libname1=libname3 join t2 on isbn3=isbn2 group by city,libname1) sub ;
 select * from v1;
 drop view v1;
+
+drop table if exists t1;
+drop table if exists t2;
+drop table if exists t3;
+drop table if exists t4;
+drop view if exists v1;
+drop view if exists v2;
+create table t1 (col1 varchar(20), col2 char(20));
+create table t2 (col1 varchar(64), col2 char(64));
+create table t3 (col1 varchar(20), col2 char(20));
+create table t4 (col1 varchar(20), col2 char(20));
+create view v1 as select t1.col1 as col1, t1.col2 as col2 from t1 union select t2.col1 as col1, t2.col2 as col2 from t2;
+desc v1;
+create view v2 as select t3.col1 as col1, t3.col2 as col2 from t3 union select t4.col1 as col1, t4.col2 as col2 from t4;
+desc v2;
+drop view v1;
+drop view v2;
+drop table t1;
+drop table t2;
+drop table t3;
+drop table t4;