[
https://issues.apache.org/jira/browse/CALCITE-6350?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17888106#comment-17888106
]
Mihai Budiu commented on CALCITE-6350:
--------------------------------------
This is related [CALCITE-6620], although that issue deals only with VALUES, and
this one involves a UNION as well.
It looks like the UNION operator infers a type of CHAR(4) for column i, because
the default type system has the property shouldConvertRaggedUnionTypesToVarying
set to false. (I bet that using a type system where the property is true does
not cause this problem.)
The bug is caused by the absence of a CAST: the literal 'w' should be CAST to
CHAR(4) if that is the type that has been inferred for it.
This is a bug in the Validator, I suspect in TypeCoercion; the plan coming out
of the SqlToRelConverter is the following:
{code:java}
LogicalProject(I=[$0])
LogicalFilter(condition=[=($0, CAST('w'):CHAR(4) NOT NULL)])
LogicalUnion(all=[true])
LogicalValues(tuples=[[{ 'word' }]])
LogicalValues(tuples=[[{ 'w' }]])
{code}
notice that the filter has a CAST on 'w' to match the type of the union's
column, but the LogicalValues do not.
> 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)
