[jira] [Comment Edited] (CALCITE-7607) DML type coercion inserts implicit narrowing casts for target-column assignments

["zzwqqq (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-7607?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18089596#comment-18089596 ] 

zzwqqq edited comment on CALCITE-7607 at 6/17/26 9:45 AM:
----------------------------------------------------------

I am trying a smaller validator-side change first, by adding RexUtil.isLosslessCast in TypeCoercionImpl#querySourceCoercion, so validation does not add a narrowing CAST for a DML source assignment,
{code:java}
  @Override public boolean querySourceCoercion(@Nullable SqlValidatorScope scope,
      RelDataType sourceRowType, RelDataType targetRowType, SqlNode query) {
    final List<RelDataTypeField> sourceFields = sourceRowType.getFieldList();
    final List<RelDataTypeField> targetFields = targetRowType.getFieldList();
    final int sourceCount = sourceFields.size();
    for (int i = 0; i < sourceCount; i++) {
      RelDataType sourceType = sourceFields.get(i).getType();
      RelDataType targetType = targetFields.get(i).getType();
      if (!SqlTypeUtil.equalSansNullability(validator.getTypeFactory(), sourceType, targetType)
          && !SqlTypeUtil.canAssignFrom(targetType, sourceType)) {
        // DML source coercion follows assignment compatibility. A source that
        // is only cast-compatible needs an explicit CAST in SQL.
        return false;
      }
    }
    boolean coerced = false;
    for (int i = 0; i < sourceFields.size(); i++) {
      RelDataType sourceType = sourceFields.get(i).getType();
      RelDataType targetType = targetFields.get(i).getType();
      if (!SqlTypeUtil.equalSansNullability(validator.getTypeFactory(), sourceType, targetType)
          && RexUtil.isLosslessCast(sourceType, targetType)) {
        coerced = coerceSourceRowType(scope, query, i, targetType) || coerced;
      }
    }
    return coerced;
  }
{code}
I found at least one remaining planner-side path though. If CoerceInputsRule is applied to LogicalTableModify, it uses TableModify#getExpectedInputRowType. For INSERT, that is the target table row type, so RelOptUtil#createCastRel can add the narrowing CAST back.

I am not sure whether this belongs in CoerceInputsRule, by making it assignment-aware for TableModify, or in TableModify#getExpectedInputRowType, perhaps by passing it the original input row type. There may also be other planner paths I have not found yet.

I’d appreciate any advice on which direction fits best.


was (Author: JIRAUSER311718):
I am trying a smaller validator-side change first, by adding RexUtil.isLosslessCast in TypeCoercionImpl#querySourceCoercion, so validation does not add a narrowing CAST for a DML source assignment,
{code:java}
  @Override public boolean querySourceCoercion(@Nullable SqlValidatorScope scope,
      RelDataType sourceRowType, RelDataType targetRowType, SqlNode query) {
    final List<RelDataTypeField> sourceFields = sourceRowType.getFieldList();
    final List<RelDataTypeField> targetFields = targetRowType.getFieldList();
    final int sourceCount = sourceFields.size();
    for (int i = 0; i < sourceCount; i++) {
      RelDataType sourceType = sourceFields.get(i).getType();
      RelDataType targetType = targetFields.get(i).getType();
      if (!SqlTypeUtil.equalSansNullability(validator.getTypeFactory(), sourceType, targetType)
          && !SqlTypeUtil.canAssignFrom(targetType, sourceType)) {
        // DML source coercion follows assignment compatibility. A source that
        // is only cast-compatible needs an explicit CAST in SQL.
        return false;
      }
    }
    boolean coerced = false;
    for (int i = 0; i < sourceFields.size(); i++) {
      RelDataType sourceType = sourceFields.get(i).getType();
      RelDataType targetType = targetFields.get(i).getType();
      if (!SqlTypeUtil.equalSansNullability(validator.getTypeFactory(), sourceType, targetType)
          && RexUtil.isLosslessCast(sourceType, targetType)) {
        coerced = coerceSourceRowType(scope, query, i, targetType) || coerced;
      }
    }
    return coerced;
  }
{code}
I found at least one remaining planner-side path though. If CoerceInputsRule is applied to LogicalTableModify, it uses TableModify#getExpectedInputRowType. For INSERT, that is the target table row type, so RelOptUtil#createCastRel can add the narrowing CAST back.

I am not sure whether this belongs in CoerceInputsRule, by making it assignment-aware for TableModify, or somewhere around TableModify#getExpectedInputRowType. There may also be other planner paths I have not found yet.

I’d appreciate any advice on which direction fits best.

> DML type coercion inserts implicit narrowing casts for target-column assignments
> --------------------------------------------------------------------------------
>
>                 Key: CALCITE-7607
>                 URL: https://issues.apache.org/jira/browse/CALCITE-7607
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: zzwqqq
>            Assignee: zzwqqq
>            Priority: Major
>
> For {{{}INSERT{}}}, {{{}UPDATE{}}}, and {{{}MERGE{}}}, Calcite may coerce a source expression to the target column type if the two types are cast-compatible. This happens even when the SQL does not contain an explicit {{{}CAST{}}}.
> That can change DML assignment semantics. A target table may need to reject the value, or may need to apply its own checked assignment rules. Calcite should not turn a bare source expression into an ordinary cast unless the assignment is safe to rewrite.
> This includes length, precision, and range narrowing. For example, inserting {{VARCHAR(64)}} into {{{}VARCHAR(16){}}}, {{DECIMAL(18, 4)}} into {{{}DECIMAL(10, 2){}}}, or {{BIGINT}} into {{INTEGER}} should not be hidden by an implicit cast in the DML plan. Several databases reject those assignments when the actual value does not fit the target column.
> Tests in postgresql, mysql and oracle:
> [https://onecompiler.com/postgresql/44sf3dz68]
> [https://onecompiler.com/mysql/44sf3efz4]
> [https://onecompiler.com/oracle/44sf3ewmh]
> h3. Steps to Reproduce
> Add this case in {{{}TypeCoercionConverterTest{}}}:
> {code:java}
> @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();
> }{code}
> The plan is
> {code:java}
> LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT], flattened=[false])
>   LogicalProject(t1_varchar20=['AVeryLongLongStringV':VARCHAR(20)], 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]])
>  {code}
> The source expression is {{{}CAST('AVeryLongLongStringValue' AS VARCHAR(64)){}}}, but the plan has already truncated it to {{{}'AVeryLongLongStringV':VARCHAR(20){}}}. This bypasses target-column assignment checks.
> Desired behavior: the plan keeps the source type:
> {code:java}
> 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]])
>  {code}
> h3. Possible Fix
> One possible approach is to keep store assignment separate from ordinary cast coercion:
>  * In validation, use assignment compatibility for bare DML sources, and leave ordinary cast compatibility to user-written {{CAST}} expressions.
>  * In query-source coercion, rewrite only assignments that are known to be safe to rewrite.
>  * Preserve source/target assignment metadata through {{TableModify}} so later planning and execution stages can still see the original source type.
>  * For Calcite's enumerable execution path, reject kept-source values that do not fit the target column.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)