[
https://issues.apache.org/jira/browse/CALCITE-7062?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17993166#comment-17993166
]
Julian Hyde commented on CALCITE-7062:
--------------------------------------
I tried to reproduce by replacing the DDL with inline queries:
{code}
SELECT cast(ts AS DATE) as t
FROM (values '1999-12-31 00:00:00') AS t2 (ts)
UNION ALL
select c
from (values '1999-12-31 00:00:00', null) as t1 (c)
{code}
And I cannot reproduce. So maybe DDL is a factor here.
The error also goes away when I make the implicit cast explicit (on line 3):
{code}
SELECT cast(test as date) as test FROM t2
UNION ALL
select cast(test as date) from t1
{code}
> Row type of UNION may ignore a column's nullability
> ---------------------------------------------------
>
> Key: CALCITE-7062
> URL: https://issues.apache.org/jira/browse/CALCITE-7062
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.39.0, 1.40.0
> Reporter: Vladimir Steshin
> Priority: Minor
>
> Reproducer:
> {code:java}
> package org.apache.calcite.test;
> class ServerTest {
> @Test void testNullableCoercionInUnion() throws Exception {
> try (Connection c = connect(); Statement s = c.createStatement()) {
> s.execute("create table t(a DATE)");
> try (ResultSet r = s.executeQuery("SELECT a FROM t UNION ALL select
> CURRENT_TIMESTAMP")) {
> assertTrue(r.next());
> assertFalse(r.next());
> }
> }
> }
> // Or
> @Test void test2() throws Exception {
> try (Connection c = connect(); Statement s = c.createStatement()) {
> s.execute("create table t1(id int not null, test TIMESTAMP NOT NULL)");
> s.execute("create table t2(id int not null, test VARCHAR)");
> s.execute("insert into t1 values (1, SYSTIMESTAMP)");
> s.execute("insert into t2 values (1, CAST(SYSDATE AS VARCHAR))");
> String query = "SELECT cast(test as date) as test FROM t2 " +
> "UNION ALL " +
> "select test from t1";
> s.execute(query);
> }
> }{code}
> Failure:
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(TIMESTAMP(0) NOT NULL A) NOT NULL
> converted type:
> RecordType(TIMESTAMP(0) A) NOT NULL
> rel:
> LogicalUnion(all=[true])
> LogicalProject(A=[CAST($0):TIMESTAMP(0)])
> LogicalTableScan(table=[[T]])
> LogicalProject(CURRENT_TIMESTAMP=[CURRENT_TIMESTAMP])
> LogicalValues(tuples=[[{ 0 }]])
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:524)
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:643)
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:258) at
> org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:221) at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:673)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:524)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:492)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:246)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:654){code}
> Regarding my research, the nullability is lost somewhere around
> {code:java}
> class AbstractTypeCoercion
> RelDataType syncAttributes(
> RelDataType fromType,
> RelDataType toType) {
> RelDataType syncedType = toType;
> if (fromType != null) {
> syncedType = factory.createTypeWithNullability(syncedType,
> fromType.isNullable());
> ...
> }{code}
> It doesn't take in account {_}toType.isNullable(){_}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
