[jira] [Created] (CALCITE-1572) JdbcSchema throws exception when detect nullable for columns

["Wu Xiang (JIRA)" <ji...@apache.org>]

Wu Xiang created CALCITE-1572:
---------------------------------

             Summary: JdbcSchema throws exception when detect nullable for columns
                 Key: CALCITE-1572
                 URL: https://issues.apache.org/jira/browse/CALCITE-1572
             Project: Calcite
          Issue Type: Bug
          Components: jdbc-adapter
    Affects Versions: 1.11.0
            Reporter: Wu Xiang
            Assignee: Julian Hyde


Currently Calcite detect nullable column in JdbcSchema by checking column #11 in response of DatabaseMetadata.getColumns, 

[JdbcSchema.java|https://github.com/apache/calcite/blob/branch-1.11/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcSchema.java#L286]
{code}
  RelProtoDataType getRelDataType(DatabaseMetaData metaData, String catalogName,
      String schemaName, String tableName) throws SQLException {
  ...
      boolean nullable = resultSet.getBoolean(11);
      fieldInfo.add(columnName, sqlType).nullable(nullable);
    }
    resultSet.close();
    return RelDataTypeImpl.proto(fieldInfo.build());
  }
{code}

However, some jdbc drivers, e.g Presto, would send bigint value for this column. This would make Calcite throws exception when querying,
{code}
Caused by: java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Boolean
        at com.facebook.presto.jdbc.PrestoResultSet.getBoolean(PrestoResultSet.java:191)
        at org.apache.commons.dbcp.DelegatingResultSet.getBoolean(DelegatingResultSet.java:216)
        at org.apache.calcite.adapter.jdbc.JdbcSchema.getRelDataType(JdbcSchema.java:286)
        at org.apache.calcite.adapter.jdbc.JdbcSchema.getRelDataType(JdbcSchema.java:250)
        at org.apache.calcite.adapter.jdbc.JdbcTable.getRowType(JdbcTable.java:108)
        at org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom(CalciteCatalogReader.java:124)
        at org.apache.calcite.prepare.CalciteCatalogReader.getTable(CalciteCatalogReader.java:100)
        at org.apache.calcite.prepare.CalciteCatalogReader.getTable(CalciteCatalogReader.java:73)
        at org.apache.calcite.sql.validate.EmptyScope.getTableNamespace(EmptyScope.java:71)
        at org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace(DelegatingScope.java:189)
        at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:104)
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:910)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:891)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2859)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2844)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3077)
        at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:910)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:891)
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:866)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:577)
        at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:554)
        at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:236)
        at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:200)
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:761)
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:617)
        at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:587)
        at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:215)
        at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:594)
        at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:615)
        at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:148)
{code}

My temp workaround is like this,
{code}
      boolean nullable;
      int nullableColumnType = resultSet.getMetaData().getColumnType(11);
      if (nullableColumnType == Types.BOOLEAN) {
        nullable = resultSet.getBoolean(11);
      } else {
        nullable = ((Number) (resultSet.getObject(11))).intValue() != 0;
      }
{code}

Beside, JDK docs tends to treat "nullable" column as integer column.
[DatabaseMetadata|https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)]

thanks





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)