[jira] [Created] (CALCITE-7062) Row type of UNION may ignore a column's nullability

["Vladimir Steshin (Jira)" <ji...@apache.org>]

Vladimir Steshin created CALCITE-7062:
-----------------------------------------

             Summary: 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.40.0, 1.39.0
            Reporter: Vladimir Steshin


I'm not sure this is a problem. Is that ok? I found it by one of our type casting tests.

Consider:

 
{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 t1 (i smallint not null)");
       s.execute("create table t2 (i bigint)");

       s.executeUpdate("insert into t1 values (1)");
       s.executeUpdate("insert into t2 values (10), (null)");

       try (ResultSet r = s.executeQuery("select i from t1 union all select i from t2")) {
         assertTrue(r.next());

         assertThat(r.getLong("i"), is(1L));

         assertTrue(r.next());
         assertThat(r.getLong("i"), is(10L));

         assertTrue(r.next());
         // The result has a null value. It is ok.
         assertNull(r.getObject("i"));
       }

       // The plan.
       try (ResultSet r = s.executeQuery("explain plan for select i from t1 union all select i from t2")) {
         assertTrue(r.next());

         String plan = r.getString(1);

         // Fails here. It actually casts to a BIGINT NOT NULL whereas the actual resuls contains a NULL.
         assertTrue(plan.contains("[CAST($t0):BIGINT]"));
       }
     }
   }
} {code}
 

The least restrictive type is _nullable BIGINT._ Looks ok. However, _StandardConvertletTable#convertCast(...)_ produces a _CAST_ to a {_}NOT NULLABLE{_}.

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(){_}. 

And also in 

 
{code:java}
class SqlCastFunction

private static RelDataType createTypeWithNullabilityFromExpr(RelDataTypeFactory typeFactory,
    RelDataType expressionType, RelDataType targetType, boolean safe) {
  boolean isNullable = expressionType.isNullable() || safe;
...

} {code}
 

The same: _targetType.isNullable()_ is ignored.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)