[
https://issues.apache.org/jira/browse/CALCITE-7062?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17994740#comment-17994740
]
Aleksey Plekhanov commented on CALCITE-7062:
--------------------------------------------
IMO:
1. There are no problems with NUMERIC data types. Cast of not nullable type to
another not nullable type on one input of the Union is absolutely correct as
far as Union output type is nullable. And Union output type is nullable.
2. There is a problem with date to timestamp cast. Reproducer:
{code:sql}
SELECT CAST(NULL AS DATE) UNION ALL SELECT CURRENT_TIMESTAMP
{code}
3. There is a problem with collections cast. Reproducer:
{code:sql}
SELECT ARRAY[1, 2, 3] UNION ALL SELECT NULL
{code}
Both problems caused by type coercion without nullability by
{{AbstractTypeCoercion#getTightestCommonType}}. Collections also affected by
CALCITE-6920.
Fixing {{AbstractTypeCoercion#getTightestCommonType}} is straitforward, but
fixing element types nullability for collections ({{SqlMapTypeNameSpec}},
{{SqlCollectionTypeNameSpec}}) can affect some related code (I will test it).
I think I can provide a fix today if nobody minds about such a fix.
> 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)
