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

JdbcSchema throws exception when detecting nullable for columns

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.11.0
    • Fix Version/s: 1.12.0
    • Component/s: jdbc-adapter
    • Labels:
      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

            People

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

              Dates

              • Created:
                Updated:
                Resolved: