[
https://issues.apache.org/jira/browse/CALCITE-7062?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Steshin updated CALCITE-7062:
--------------------------------------
Description:
I'm not sure this is a problem. Is that ok? I found it by one of 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 contain a NULL.
assertTrue(plan.contains("[CAST($t0):BIGINT]"));
}
}
}
// 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";
// Fails here. Similarly in v1.37 / v.38
s.execute(query);
}
}{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.
Te *second test* produces:
{code:java}
ava.lang.AssertionError: Conversion to relational algebra failed to preserve
datatypes:
validated type:
RecordType(TIMESTAMP(0) NOT NULL TEST) NOT NULL
converted type:
RecordType(TIMESTAMP(0) TEST) NOT NULL
rel:
LogicalUnion(all=[true])
LogicalProject(TEST=[CAST(CAST($1):DATE):TIMESTAMP(0)])
LogicalTableScan(table=[[T2]])
LogicalProject(TEST=[$1])
LogicalTableScan(table=[[T1]]){code}
{code:java}
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}
was:
I'm not sure this is a problem. Is that ok? I found it by one of 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 contain a NULL.
assertTrue(plan.contains("[CAST($t0):BIGINT]"));
}
}
}
// 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";
// Fails here
s.execute(query);
}
}{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.
Te *second test* produces:
{code:java}
ava.lang.AssertionError: Conversion to relational algebra failed to preserve
datatypes:
validated type:
RecordType(TIMESTAMP(0) NOT NULL TEST) NOT NULL
converted type:
RecordType(TIMESTAMP(0) TEST) NOT NULL
rel:
LogicalUnion(all=[true])
LogicalProject(TEST=[CAST(CAST($1):DATE):TIMESTAMP(0)])
LogicalTableScan(table=[[T2]])
LogicalProject(TEST=[$1])
LogicalTableScan(table=[[T1]]){code}
{code:java}
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}
> 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
>
> I'm not sure this is a problem. Is that ok? I found it by one of 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 contain a NULL.
> assertTrue(plan.contains("[CAST($t0):BIGINT]"));
> }
> }
> }
> // 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";
> // Fails here. Similarly in v1.37 / v.38
> s.execute(query);
> }
> }{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.
>
> Te *second test* produces:
> {code:java}
> ava.lang.AssertionError: Conversion to relational algebra failed to preserve
> datatypes:
> validated type:
> RecordType(TIMESTAMP(0) NOT NULL TEST) NOT NULL
> converted type:
> RecordType(TIMESTAMP(0) TEST) NOT NULL
> rel:
> LogicalUnion(all=[true])
> LogicalProject(TEST=[CAST(CAST($1):DATE):TIMESTAMP(0)])
> LogicalTableScan(table=[[T2]])
> LogicalProject(TEST=[$1])
> LogicalTableScan(table=[[T1]]){code}
> {code:java}
> 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}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
