[
https://issues.apache.org/jira/browse/CALCITE-2042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16244357#comment-16244357
]
Julian Hyde commented on CALCITE-2042:
--------------------------------------
I agree that the SQL should work as written, and the Calcite should add
necessary implicit casts.
{{SetOp}} and its sub-classes ({{Union}} etc.) allow the input relational
expressions to have *somewhat* different types but for each column it has to be
able to infer a "least restrictive type" - basically in the same type family -
otherwise {{SetOp.deriveRowType()}} will throw (see CALCITE-1522).
Which means that there is work to do to make the types identical. I think we
should have a new utility method that {{SetOp.ensureHomogeneous()}} that calls
{{SetOp.isHomogeneous()}} (an existing method, not currently used, but probably
used for a similar purpose in the distant past) and returns either the same
{{SetOp}} or one with the necessary casts added. You don't need to override the
method in sub-classes; just call {{copy}}. After we have a plan we should call
{{ensureHomogeneous}} before generating code.
> Compatible types of columns combined via set operators may need explicit
> type-casting conversion
> ------------------------------------------------------------------------------------------------
>
> Key: CALCITE-2042
> URL: https://issues.apache.org/jira/browse/CALCITE-2042
> Project: Calcite
> Issue Type: Bug
> Reporter: Liao Xintao
> Assignee: Julian Hyde
> Priority: Minor
>
> As knowns that columns combined via set operators (such as UNION, EXCEPT and
> INTERSECT) need not have exactly the same data type. So the SQL query is
> likely as below:
> {code:sql}
> select sum(bonus) from (
> (select sal as bonus from emp where empno < 100)
> UNION ALL
> (select sal * 0.5 as bonus from emp where empno >= 100)
> )
> {code}
> In this case, the data types of two "bonus" column are INTEGER and
> DECIMAL(12, 1) respectively, and the UNION operator derives DECIMAL(12, 1) as
> return type. The plan seems so good till I run it on Flink. The Flink
> UnionOperator validates the data types of two inputs and throws an exception
> because the two input types are not exactly the same.
> The underling systems based on calcite, like Flink, may adapt themselves to
> this kind of plan by modifying some codes. In my opinion, however, it may be
> a better way that the plan ensures the rules of data types of results of
> aggregation, (maybe) via rewriting the plan or adding explicit type-casting
> conversion which has been done when converting <case expression>.
> Any feedback or advice is greatly appreciated.
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
