[
https://issues.apache.org/jira/browse/CALCITE-6350?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17892617#comment-17892617
]
Mihai Budiu commented on CALCITE-6350:
--------------------------------------
The bug is really in the Union type inference (SetSetOperator).
Union infers a type of CHAR(4) for the result. It uses the leastRestrictive
rule to achieve this result.
The logical filter after the union coerces the literal 'w' to have type CHAR(4).
However, one of the SELECTS that is an argument to the union does not return
values of type CHAR(4), but of type CHAR(1).
Solution: Set operators should coerce their inputs to have types matching the
inferred result type. This is not as trivial as inserting a CAST, since the
operands are relations.
So the validator should rewrite this query as follows:
{code:sql}
select * from
(select 'word' i
union all
(SELECT CAST(i as CHAR(4)) as I FROM
(select 'w' i))) t1
where i='w'
{code}
The code in uppercase is a SELECT query which coerces the input to the union.
Such a coercion should be added for every operand of the set operation whose
type does not match the result type of the set operation.
I have to poke through the validator code to see whether there are other places
where such a transformation is performed.
> Unexpected result from UNION with literals expression
> -----------------------------------------------------
>
> Key: CALCITE-6350
> URL: https://issues.apache.org/jira/browse/CALCITE-6350
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.36.0
> Reporter: Evgeny Stanilovsky
> Priority: Major
>
> Near expression need to return 'w' but empty result found.
> {noformat}
> select * from (select 'word' i union all select 'w' i) t1 where i='w'
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
