[
https://issues.apache.org/jira/browse/CALCITE-5435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17648120#comment-17648120
]
Aleksey Plekhanov commented on CALCITE-5435:
--------------------------------------------
[~libenchao], no, it's not a duplicate. CALCITE-5141 not solves the problem
completely, there is still a problem when least restrictive type can't be
found, however types can be coerced. I've attached the pull request with fix
and test, this test fails on current master without fix (even with
CALCITE-5141).
> Insert for multi row VALUES failed when column has values of different types
> ----------------------------------------------------------------------------
>
> Key: CALCITE-5435
> URL: https://issues.apache.org/jira/browse/CALCITE-5435
> Project: Calcite
> Issue Type: Bug
> Reporter: Aleksey Plekhanov
> Assignee: Aleksey Plekhanov
> Priority: Major
>
> Queries like:
> {noformat}
> insert into empnullables (empno) values (10), ('20'){noformat}
> Or:
> {noformat}
> insert into empnullables (empno, deptno) values (10, '300'), (20,
> null){noformat}
> Fail with an exception:
> {noformat}
> From line 2, column 1 to line 2, column 32: Values passed to VALUES operator
> must have compatible types
> org.apache.calcite.runtime.CalciteContextException: From line 2, column 1 to
> line 2, column 32: Values passed to VALUES operator must have compatible types
> ...
> at
> app//org.apache.calcite.sql.validate.SqlValidatorImpl.validateValues(SqlValidatorImpl.java:5370){noformat}
> In the first query, first row has column type INTEGER and second row has
> column type CHAR(2).
> In the second query data type of {{null}} is infered from target data type
> (INTEGER).
> However types of values can be coerced.
> In case of one-by-one inserts statements work as expected. For one-row
> inserts this check is skipped and types are coerced later in
> {{SqlValidatorImpl#validateInsert}} -> {{{}checkTypeAssignment{}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
