Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1572

JdbcSchema throws exception when detecting nullable for columns

VotersWatch issueWatchersLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.11.0
    • 1.12.0
    • jdbc-adapter
    • None

    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

      Attachments

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            julianhyde Julian Hyde
            wxiang7 Wu Xiang
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment