This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 9acb3745c6 [CALCITE-7607] DML type coercion inserts implicit narrowing
casts for target-column assignments
9acb3745c6 is described below
commit 9acb3745c61db9bb01a7427df64cd5d2a0834a96
Author: zzwqqq <[email protected]>
AuthorDate: Tue Jun 23 17:05:50 2026 +0800
[CALCITE-7607] DML type coercion inserts implicit narrowing casts for
target-column assignments
---
.../org/apache/calcite/rel/core/TableModify.java | 30 ++++++++++++++
.../sql/validate/implicit/TypeCoercionImpl.java | 8 +++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 21 +++++-----
.../org/apache/calcite/test/JdbcAdapterTest.java | 8 ++--
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 2 +-
.../calcite/test/TypeCoercionConverterTest.java | 32 +++++++++++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 4 +-
.../apache/calcite/test/SqlToRelConverterTest.xml | 4 +-
.../calcite/test/TypeCoercionConverterTest.xml | 47 ++++++++++++++++++++--
server/src/test/resources/sql/materialized_view.iq | 26 ++++++------
server/src/test/resources/sql/table_as.iq | 28 ++++++-------
12 files changed, 160 insertions(+), 52 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/rel/core/TableModify.java
b/core/src/main/java/org/apache/calcite/rel/core/TableModify.java
index 60b9814dfd..83dd810647 100644
--- a/core/src/main/java/org/apache/calcite/rel/core/TableModify.java
+++ b/core/src/main/java/org/apache/calcite/rel/core/TableModify.java
@@ -32,7 +32,9 @@
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.type.SqlTypeUtil;
@@ -261,9 +263,37 @@ public boolean isMerge() {
null);
}
+ inputRowType = expectedInputRowTypeForAssignment(inputRowType);
return inputRowType;
}
+ private RelDataType expectedInputRowTypeForAssignment(
+ RelDataType expectedRowType) {
+ final RelDataType actualRowType = getInput().getRowType();
+ if (actualRowType.getFieldCount() != expectedRowType.getFieldCount()) {
+ return expectedRowType;
+ }
+ final RelDataTypeFactory typeFactory = getCluster().getTypeFactory();
+ final RelDataTypeFactory.Builder builder = typeFactory.builder();
+ boolean changed = false;
+ final List<RelDataTypeField> expectedFields =
expectedRowType.getFieldList();
+ final List<RelDataTypeField> actualFields = actualRowType.getFieldList();
+ for (int i = 0; i < expectedFields.size(); i++) {
+ final RelDataTypeField expectedField = expectedFields.get(i);
+ final RelDataType actualType = actualFields.get(i).getType();
+ final RelDataType expectedType = expectedField.getType();
+ if (!SqlTypeUtil.equalSansNullability(typeFactory, actualType,
expectedType)
+ && SqlTypeUtil.canAssignFrom(expectedType, actualType)
+ && !RexUtil.isLosslessCast(actualType, expectedType)) {
+ builder.add(expectedField.getName(), actualType);
+ changed = true;
+ } else {
+ builder.add(expectedField);
+ }
+ }
+ return changed ? builder.build() : expectedRowType;
+ }
+
@Override public RelWriter explainTerms(RelWriter pw) {
return super.explainTerms(pw)
.item("table", table.getQualifiedName())
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
index 26812be961..46dc3af5ed 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
@@ -19,6 +19,7 @@
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlFunction;
@@ -830,8 +831,13 @@ && coerceOperandType(binding.getScope(),
binding.getCall(), i, implicitType)
}
boolean coerced = false;
for (int i = 0; i < sourceFields.size(); i++) {
+ RelDataType sourceType = sourceFields.get(i).getType();
RelDataType targetType = targetFields.get(i).getType();
- coerced = coerceSourceRowType(scope, query, i, targetType) || coerced;
+ if (!SqlTypeUtil.equalSansNullability(validator.getTypeFactory(),
sourceType, targetType)
+ && (RexUtil.isLosslessCast(sourceType, targetType)
+ || !SqlTypeUtil.canAssignFrom(targetType, sourceType))) {
+ coerced = coerceSourceRowType(scope, query, i, targetType) || coerced;
+ }
}
return coerced;
}
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index f056ee9d4f..2ac820e838 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -10105,7 +10105,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
- + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "VALUES \"DEPT\".\"DEPTNO\" + 1,\n"
+ "LOWER(\"DEPT\".\"DNAME\"),\n"
+ "UPPER(\"DEPT\".\"LOC\")";
sql(sql1)
@@ -10139,9 +10139,9 @@ private void checkLiteral2(String expression, String
expected) {
+ "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ "WHEN MATCHED THEN UPDATE SET \"DNAME\" = \"DEPT\".\"DNAME\"\n"
+ "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
- + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "VALUES \"DEPT\".\"DEPTNO\" + 1,\n"
+ "'abc',\n"
- + "CAST(LOWER(\"DEPT\".\"DNAME\") AS VARCHAR(13) CHARACTER SET
\"ISO-8859-1\")";
+ + "LOWER(\"DEPT\".\"DNAME\")";
sql(sql3)
.schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
.ok(expected3);
@@ -10171,7 +10171,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "USING \"SCOTT\".\"DEPT\"\n"
+ "ON \"DEPT\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
- + "VALUES CAST(\"DEPT\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "VALUES \"DEPT\".\"DEPTNO\" + 1,\n"
+ "LOWER(\"DEPT\".\"DNAME\"),\n"
+ "UPPER(\"DEPT\".\"LOC\")";
sql(sql5)
@@ -10191,7 +10191,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "WHERE CAST(\"DEPTNO\" AS INTEGER) <> 5) AS \"t0\"\n"
+ "ON \"t0\".\"DEPTNO\" = \"DEPT0\".\"DEPTNO\"\n"
+ "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
- + "VALUES CAST(\"t0\".\"DEPTNO\" + 1 AS TINYINT),\n"
+ + "VALUES \"t0\".\"DEPTNO\" + 1,\n"
+ "LOWER(\"t0\".\"DNAME\"),\n"
+ "UPPER(\"t0\".\"LOC\")";
sql(sql6)
@@ -10213,7 +10213,7 @@ private void checkLiteral2(String expression, String
expected) {
+ "ON \"t0\".\"EXPR$0\" = \"t1\".\"DEPTNO0\"\n"
+ "WHEN MATCHED THEN UPDATE SET \"DNAME\" = 'abc'\n"
+ "WHEN NOT MATCHED THEN INSERT (\"DEPTNO\", \"DNAME\", \"LOC\") "
- + "VALUES CAST(\"t0\".\"EXPR$0\" + 1 AS TINYINT),\n"
+ + "VALUES \"t0\".\"EXPR$0\" + 1,\n"
+ "CAST(LOWER(\"t0\".\"EXPR$1\") AS VARCHAR(14) CHARACTER SET
\"ISO-8859-1\"),\n"
+ "CAST(UPPER(\"t0\".\"EXPR$2\") AS VARCHAR(13) CHARACTER SET
\"ISO-8859-1\")";
sql(sql7)
@@ -10402,9 +10402,8 @@ private void checkLiteral2(String expression, String
expected) {
final String sql0 = "update \"foodmart\".\"product\" "
+ "set \"product_name\" = \"product_name\" || '_'\n"
+ "where \"product_id\" > 10";
- final String expected0 = "UPDATE \"foodmart\".\"product\" SET
\"product_name\" = CAST"
- + "(\"product_name\" || '_' AS VARCHAR(60) CHARACTER SET
\"ISO-8859-1\")\nWHERE "
- + "\"product_id\" > 10";
+ final String expected0 = "UPDATE \"foodmart\".\"product\" SET
\"product_name\" = "
+ + "\"product_name\" || '_'\nWHERE \"product_id\" > 10";
sql(sql0).ok(expected0);
final String sql1 = "update \"foodmart\".\"product\""
@@ -10420,8 +10419,8 @@ private void checkLiteral2(String expression, String
expected) {
+ " \"product_name\" = \"product_name\" || '_' \n"
+ "where \"product_id\" > 10";
final String expected2 = "UPDATE \"foodmart\".\"product\" SET
\"product_id\" = \"product_id\""
- + " + CHAR_LENGTH(\"product_name\"), \"product_name\" =
CAST(\"product_name\" || '_' AS "
- + "VARCHAR(60) CHARACTER SET \"ISO-8859-1\")\nWHERE \"product_id\" >
10";
+ + " + CHAR_LENGTH(\"product_name\"), \"product_name\" =
\"product_name\" || '_'"
+ + "\nWHERE \"product_id\" > 10";
sql(sql2).ok(expected2);
final String sql3 = "update \"foodmart\".\"product\"\n"
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 7bed49bb10..5ecf63c5ba 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -1465,21 +1465,21 @@ private LockWrapper exclusiveCleanDb(Connection c)
throws SQLException {
+ " JdbcTableModify(table=[[foodmart, expense_fact]],
operation=[MERGE],"
+ " updateColumnList=[[amount]], flattened=[false])\n"
+ " JdbcProject(STORE_ID=[$0], $f1=[666], $f2=[1997-01-01
00:00:00], $f3=[666],"
- + " $f4=['666':VARCHAR(30)], $f5=[666], AMOUNT=[CAST($1):DECIMAL(10,
4) NOT NULL],"
+ + " $f4=['666':VARCHAR(30)], $f5=[666], AMOUNT=[$1],"
+ " store_id=[$2],"
+ " account_id=[$3], exp_date=[$4], time_id=[$5], category_id=[$6],
currency_id=[$7],"
- + " amount=[$8], AMOUNT0=[CAST($1):DECIMAL(10, 4) NOT NULL])\n"
+ + " amount=[$8], AMOUNT0=[$1])\n"
+ " JdbcJoin(condition=[=($2, $0)], joinType=[left])\n"
+ " JdbcValues(tuples=[[{ 666, 42 }]])\n"
+ " JdbcTableScan(table=[[foodmart, expense_fact]])\n";
final String jdbcSql = "MERGE INTO \"foodmart\".\"expense_fact\"\n"
+ "USING (VALUES (666, 42)) AS \"t\" (\"STORE_ID\", \"AMOUNT\")\n"
+ "ON \"t\".\"STORE_ID\" = \"expense_fact\".\"store_id\"\n"
- + "WHEN MATCHED THEN UPDATE SET \"amount\" = CAST(\"t\".\"AMOUNT\" AS
DECIMAL(10, 4))\n"
+ + "WHEN MATCHED THEN UPDATE SET \"amount\" = \"t\".\"AMOUNT\"\n"
+ "WHEN NOT MATCHED THEN INSERT (\"store_id\", \"account_id\",
\"exp_date\", \"time_id\", "
+ "\"category_id\", \"currency_id\", \"amount\") VALUES
\"t\".\"STORE_ID\",\n"
+ "666,\nTIMESTAMP '1997-01-01 00:00:00',\n666,\n'666',\n666,\n"
- + "CAST(\"t\".\"AMOUNT\" AS DECIMAL(10, 4))";
+ + "\"t\".\"AMOUNT\"";
final AssertThat that =
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.enable(CalciteAssert.DB == DatabaseInstance.HSQLDB);
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 0c5ab19b2e..83ce5b1d78 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -390,7 +390,7 @@ static void forEachExpand(Runnable r) {
+ "expr#7=[null:JavaType(class java.lang.Integer)], "
+ "empid=[$t3], deptno=[$t4], name=[$t5], salary=[$t6], "
+ "commission=[$t7])\n"
- + " EnumerableValues(tuples=[[{ 'Fred', 56,
123.4000015258789E0 }]])\n";
+ + " EnumerableValues(tuples=[[{ 'Fred', 56, 123.4 }]])\n";
assertThat(resultSet.getString(1), isLinux(expected));
// With named columns
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index d94ae46526..60a5c56cba 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -6053,7 +6053,7 @@ private void checkEmptyJoin(RelOptFixture f) {
@Test void testReduceCastsNullable() {
HepProgram program = new HepProgramBuilder()
- // Simulate the way INSERT will insert casts to the target types
+ // Simulate the way INSERT can insert casts to the target types.
.addRuleInstance(
CoerceInputsRule.Config.DEFAULT
.withCoerceNames(false)
diff --git
a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
index b31e982671..a12a07d5e5 100644
--- a/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/TypeCoercionConverterTest.java
@@ -236,4 +236,36 @@ public static void checkActualAndReferenceFiles() {
String sql = "select CAST(null AS INTEGER) union select '10'";
sql(sql).ok();
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7607";>[CALCITE-7607]
+ * Avoid implicit narrowing casts in DML assignment coercion</a>. */
+ @Test void testInsertVarcharNarrowingKeepsSourceType() {
+ final String sql = "insert into t1 select "
+ + "CAST('AVeryLongLongStringValue' AS VARCHAR(64)), "
+ + "t2_smallint, t2_int, t2_bigint,\n"
+ + "t2_real, t2_double, t2_decimal, t2_timestamp, "
+ + "t2_date, t2_binary, t2_boolean from t2";
+ sql(sql).ok();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7607";>[CALCITE-7607]
+ * Avoid implicit narrowing casts in DML assignment coercion</a>. */
+ @Test void testUpdateVarcharNarrowingKeepsSourceType() {
+ final String sql = "update t1 set t1_varchar20 = "
+ + "CAST('AVeryLongLongStringValue' AS VARCHAR(64))";
+ sql(sql).ok();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7607";>[CALCITE-7607]
+ * Avoid implicit narrowing casts in DML assignment coercion</a>. */
+ @Test void testMergeVarcharNarrowingKeepsSourceType() {
+ final String sql = "merge into t1\n"
+ + "using t2 on t1.t1_smallint = t2.t2_smallint\n"
+ + "when matched then update set t1_varchar20 = "
+ + "CAST('AVeryLongLongStringValue' AS VARCHAR(64))";
+ sql(sql).ok();
+ }
}
diff --git
a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index 24d6a16f3f..d7c03e93f5 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -16542,14 +16542,14 @@ select empno, cast(job as varchar(128)) from
sales.empnullables]]>
<Resource name="planBefore">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, DEPT]], operation=[INSERT],
flattened=[false])
- LogicalProject(DEPTNO=[$0], NAME=[$2])
+ LogicalProject(DEPTNO=[$0], NAME=[CAST($2):VARCHAR(128)])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
<Resource name="planAfter">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, DEPT]], operation=[INSERT],
flattened=[false])
- LogicalCalc(expr#0..8=[{inputs}], expr#9=[CAST($t2):VARCHAR(10) NOT NULL],
DEPTNO=[$t0], NAME=[$t9])
+ LogicalCalc(expr#0..8=[{inputs}], expr#9=[CAST($t2):VARCHAR(128)],
DEPTNO=[$t0], NAME=[$t9])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
</Resource>
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index f2ab8fec37..c36f7ad59b 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -5597,7 +5597,7 @@ values(t.empno, t.ename, 10, t.sal * .15)]]>
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, EMPNULLABLES]], operation=[MERGE],
updateColumnList=[[ENAME, DEPTNO, SAL]], flattened=[true])
- LogicalProject(EMPNO=[$0], ENAME=[$1], $f2=[null:VARCHAR(10)],
$f3=[null:INTEGER], $f4=[null:TIMESTAMP(0)], $f5=[CAST(*($5, 0.15:DECIMAL(2,
2))):INTEGER NOT NULL], $f6=[null:INTEGER], $f7=[10], $f8=[null:BOOLEAN],
EMPNO0=[$9], ENAME0=[$10], JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14],
COMM0=[$15], DEPTNO0=[$16], SLACKER0=[$17], ENAME1=[$1], DEPTNO=[$7],
$f20=[CAST(*($5, 0.1:DECIMAL(1, 1))):INTEGER NOT NULL])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], $f2=[null:VARCHAR(10)],
$f3=[null:INTEGER], $f4=[null:TIMESTAMP(0)], $f5=[*($5, 0.15:DECIMAL(2, 2))],
$f6=[null:INTEGER], $f7=[10], $f8=[null:BOOLEAN], EMPNO0=[$9], ENAME0=[$10],
JOB0=[$11], MGR0=[$12], HIREDATE0=[$13], SAL0=[$14], COMM0=[$15],
DEPTNO0=[$16], SLACKER0=[$17], ENAME1=[$1], DEPTNO=[$7], $f20=[*($5,
0.1:DECIMAL(1, 1))])
LogicalJoin(condition=[=($9, $0)], joinType=[left])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[IS NULL($7)])
@@ -7257,7 +7257,7 @@ LogicalTableModify(table=[[CATALOG, SALES, DEPT]],
operation=[INSERT], flattened
LogicalAggregate(group=[{0, 1}])
LogicalProject(EMPNO=[$0], ENAME=[$1])
LogicalFilter(condition=[$2])
- LogicalProject(EMPNO=[$0], ENAME=[CAST($1):VARCHAR(10) NOT NULL],
QualifyExpression=[=(RANK() OVER (PARTITION BY $1 ORDER BY $8 DESC), 1)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], QualifyExpression=[=(RANK()
OVER (PARTITION BY $1 ORDER BY $8 DESC), 1)])
LogicalFilter(condition=[>($7, 5)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
diff --git
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
index 8e9d0207ca..15512cb615 100644
---
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -140,7 +140,7 @@ t2_double, t2_decimal, t2_int, t2_date, t2_timestamp,
t2_varchar20, t2_int from
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
- LogicalProject(t1_varchar20=[CAST($1):VARCHAR(20) NOT NULL],
t1_smallint=[CAST($2):SMALLINT NOT NULL], t1_int=[CAST($3):INTEGER NOT NULL],
t1_bigint=[CAST($4):BIGINT NOT NULL], t1_real=[CAST($5):REAL NOT NULL],
t1_double=[CAST($6):DOUBLE NOT NULL], t1_decimal=[CAST($2):DECIMAL(19, 0) NOT
NULL], t1_timestamp=[CAST($8):TIMESTAMP(0) NOT NULL], t1_date=[CAST($7):DATE
NOT NULL], t1_binary=[CAST($0):BINARY(1) NOT NULL], t1_boolean=[<>($2, 0)])
+ LogicalProject(t1_varchar20=[CAST($1):VARCHAR(20) NOT NULL],
t1_smallint=[$2], t1_int=[$3], t1_bigint=[$4], t1_real=[$5], t1_double=[$6],
t1_decimal=[CAST($2):DECIMAL(19, 0) NOT NULL],
t1_timestamp=[CAST($8):TIMESTAMP(0) NOT NULL], t1_date=[CAST($7):DATE NOT
NULL], t1_binary=[CAST($0):BINARY(1) NOT NULL], t1_boolean=[<>($2, 0)])
LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
</Resource>
@@ -152,7 +152,7 @@ LogicalTableModify(table=[[CATALOG, SALES, T1]],
operation=[INSERT], flattened=[
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
- LogicalValues(tuples=[[{ 'a', 1, 1, 0, 0.0E0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2, 0, 0.0E0, 0.0E0, 0, 2021-11-28
00:00:00, 2021-11-28, X'0a', false }, { 'c', 3, 3, 0, 0.0E0, 0.0E0, 0,
2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'd', 4, 4, 0, 0.0E0, 0.0E0,
0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'e', 5, 5, 0, 0.0E0,
0.0E0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'a', 1, 1.0, 0, 0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2.0, 0, 0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'c', 3, 3.0, 0, 0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'd', 4, 4.0, 0, 0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'e', 5, 5.0, 0, 0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
]]>
</Resource>
</TestCase>
@@ -164,6 +164,19 @@ LogicalTableModify(table=[[CATALOG, SALES, T1]],
operation=[INSERT], flattened=[
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
LogicalValues(tuples=[[{ 'a', 1, 1, 0, 0.0E0, 0.0E0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2, 0, 0.0E0, 0.0E0, 0, 2021-11-28
00:00:00, 2021-11-28, X'0a', false }, { 'c', 3, 3, 0, 0.0E0, 0.0E0, 0,
2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'd', 4, 4, 0, 0.0E0, 0.0E0,
0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }, { 'e', 5, 5, 0, 0.0E0,
0.0E0, 0, 2021-11-28 00:00:00, 2021-11-28, X'0a', false }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testInsertVarcharNarrowingKeepsSourceType">
+ <Resource name="sql">
+ <![CDATA[insert into t1 select CAST('AVeryLongLongStringValue' AS
VARCHAR(64)), t2_smallint, t2_int, t2_bigint,
+t2_real, t2_double, t2_decimal, t2_timestamp, t2_date, t2_binary, t2_boolean
from t2]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
+ LogicalProject(t1_varchar20=['AVeryLongLongStringValue':VARCHAR(64)],
t1_smallint=[$1], t1_int=[$2], t1_bigint=[$3], t1_real=[$4], t1_double=[$5],
t1_decimal=[$6], t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9],
t1_boolean=[$10])
+ LogicalTableScan(table=[[CATALOG, SALES, T2]])
]]>
</Resource>
</TestCase>
@@ -206,6 +219,22 @@ LogicalProject(X=[$0])
LogicalAggregate(group=[{0}])
LogicalProject(X=[$0])
LogicalValues(tuples=[[{ 3 }, { 4 }]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testMergeVarcharNarrowingKeepsSourceType">
+ <Resource name="sql">
+ <![CDATA[merge into t1
+using t2 on t1.t1_smallint = t2.t2_smallint
+when matched then update set t1_varchar20 = CAST('AVeryLongLongStringValue' AS
VARCHAR(64))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[MERGE],
updateColumnList=[[t1_varchar20]], flattened=[false])
+ LogicalProject(t1_varchar20=[$11], t1_smallint=[$12], t1_int=[$13],
t1_bigint=[$14], t1_real=[$15], t1_double=[$16], t1_decimal=[$17],
t1_timestamp=[$18], t1_date=[$19], t1_binary=[$20], t1_boolean=[$21],
$f11=['AVeryLongLongStringValue':VARCHAR(64)])
+ LogicalJoin(condition=[=($12, $1)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, T2]])
+ LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
</Resource>
</TestCase>
@@ -282,7 +311,19 @@ LogicalUnion(all=[false])
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[UPDATE],
updateColumnList=[[t1_varchar20, t1_date, t1_int]], sourceExpressionList=[[$11,
$12, $13]], flattened=[false])
- LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2],
t1_bigint=[$3], t1_real=[$4], t1_double=[$5], t1_decimal=[$6],
t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10],
EXPR$0=['123':VARCHAR(20)], EXPR$1=[2020-01-03], EXPR$2=[12])
+ LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2],
t1_bigint=[$3], t1_real=[$4], t1_double=[$5], t1_decimal=[$6],
t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10],
EXPR$0=['123':VARCHAR(20)], EXPR$1=[2020-01-03], EXPR$2=[12.3:DECIMAL(3, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, T1]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testUpdateVarcharNarrowingKeepsSourceType">
+ <Resource name="sql">
+ <![CDATA[update t1 set t1_varchar20 = CAST('AVeryLongLongStringValue' AS
VARCHAR(64))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[UPDATE],
updateColumnList=[[t1_varchar20]], sourceExpressionList=[[$11]],
flattened=[false])
+ LogicalProject(t1_varchar20=[$0], t1_smallint=[$1], t1_int=[$2],
t1_bigint=[$3], t1_real=[$4], t1_double=[$5], t1_decimal=[$6],
t1_timestamp=[$7], t1_date=[$8], t1_binary=[$9], t1_boolean=[$10],
EXPR$0=['AVeryLongLongStringValue':VARCHAR(64)])
LogicalTableScan(table=[[CATALOG, SALES, T1]])
]]>
</Resource>
diff --git a/server/src/test/resources/sql/materialized_view.iq
b/server/src/test/resources/sql/materialized_view.iq
index 8a8b12992f..ca18a8ccf6 100644
--- a/server/src/test/resources/sql/materialized_view.iq
+++ b/server/src/test/resources/sql/materialized_view.iq
@@ -19,7 +19,7 @@
!set outputformat mysql
# Create a source table
-create table dept (deptno int not null, name varchar(10));
+create table dept (deptno int not null, name varchar(20));
(0 rows modified)
!update
@@ -39,12 +39,12 @@ select * from dept where deptno > 10;
# Check contents
select * from v;
-+--------+------------+
-| DEPTNO | NAME |
-+--------+------------+
-| 20 | Marketing |
-| 30 | Engineerin |
-+--------+------------+
++--------+-------------+
+| DEPTNO | NAME |
++--------+-------------+
+| 20 | Marketing |
+| 30 | Engineering |
++--------+-------------+
(2 rows)
!ok
@@ -64,12 +64,12 @@ select * from dept where deptno < 30;
# Check contents are unchanged
select * from v;
-+--------+------------+
-| DEPTNO | NAME |
-+--------+------------+
-| 20 | Marketing |
-| 30 | Engineerin |
-+--------+------------+
++--------+-------------+
+| DEPTNO | NAME |
++--------+-------------+
+| 20 | Marketing |
+| 30 | Engineering |
++--------+-------------+
(2 rows)
!ok
diff --git a/server/src/test/resources/sql/table_as.iq
b/server/src/test/resources/sql/table_as.iq
index cf24509914..8e16f4c5a9 100644
--- a/server/src/test/resources/sql/table_as.iq
+++ b/server/src/test/resources/sql/table_as.iq
@@ -19,7 +19,7 @@
!set outputformat mysql
# Create a source table
-create table dept (deptno int not null, name varchar(10));
+create table dept (deptno int not null, name varchar(20));
(0 rows modified)
!update
@@ -37,14 +37,14 @@ select * from dept where deptno > 10;
!update
-# Check contents; "Engineering" is too long for varchar(10)
+# Check contents
select * from d;
-+--------+------------+
-| DEPTNO | NAME |
-+--------+------------+
-| 20 | Marketing |
-| 30 | Engineerin |
-+--------+------------+
++--------+-------------+
+| DEPTNO | NAME |
++--------+-------------+
+| 20 | Marketing |
+| 30 | Engineering |
++--------+-------------+
(2 rows)
!ok
@@ -64,12 +64,12 @@ select * from dept where deptno < 30;
# Check contents are unchanged
select * from d;
-+--------+------------+
-| DEPTNO | NAME |
-+--------+------------+
-| 20 | Marketing |
-| 30 | Engineerin |
-+--------+------------+
++--------+-------------+
+| DEPTNO | NAME |
++--------+-------------+
+| 20 | Marketing |
+| 30 | Engineering |
++--------+-------------+
(2 rows)
!ok
