[CALCITE-1572] JdbcSchema throws exception when detecting nullable for columns

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.11.0
• Fix Version/s: 1.12.0
• Component/s: jdbc-adapter
• Assignee: Julian Hyde
• Reporter: Wu Xiang
• Created: 11/Jan/17 07:52
• Updated: 24/Mar/17 03:20
• Resolved: 29/Jan/17 18:11

Description

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

JdbcSchema.java

  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());
  }

However, some jdbc drivers, e.g Presto, would send bigint value for this column. This would make Calcite throw exception when querying,

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)

My temp workaround is like this,

      boolean nullable;
      int nullableColumnType = resultSet.getMetaData().getColumnType(11);
      if (nullableColumnType == Types.BOOLEAN) {
        nullable = resultSet.getBoolean(11);
      } else {
        nullable = ((Number) (resultSet.getObject(11))).intValue() != 0;
      }

Beside, JDK docs tends to treat "nullable" column as integer column.
DatabaseMetadata

thanks