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

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        The JDBC spec is clear:

        11. NULLABLE int => is NULL allowed.
        columnNoNulls - might not allow NULL values
        columnNullable - definitely allows NULL values
        columnNullableUnknown - nullability unknown

        so we are wrong to treat the column as boolean. I guess some JDBC drivers will do getInt(11) != 0 if you call getBoolean(11), but the Presto driver does not. So, let's change the code to call getInt(11). Don't bother to check whether the column is boolean.

        Can you create a pull-request for this?

        Show
        julianhyde Julian Hyde added a comment - The JDBC spec is clear: 11. NULLABLE int => is NULL allowed. columnNoNulls - might not allow NULL values columnNullable - definitely allows NULL values columnNullableUnknown - nullability unknown so we are wrong to treat the column as boolean. I guess some JDBC drivers will do getInt(11) != 0 if you call getBoolean(11) , but the Presto driver does not. So, let's change the code to call getInt(11) . Don't bother to check whether the column is boolean. Can you create a pull-request for this?
        Hide
        wxiang7 Wu Xiang added a comment -
        Show
        wxiang7 Wu Xiang added a comment - Hi Julian Hyde , PR requested, https://github.com/apache/calcite/pull/357
        Hide
        julianhyde Julian Hyde added a comment -

        A couple of comments:

        • Can you use DatabaseMetaData.columnNoNulls rather than 0
        • Can you make sure JdbcAdapterTest calls DatabaseMetaData.getColumns on a particular known table, say Foodmart.Employee, which has a mixture of null and not null columns
        Show
        julianhyde Julian Hyde added a comment - A couple of comments: Can you use DatabaseMetaData.columnNoNulls rather than 0 Can you make sure JdbcAdapterTest calls DatabaseMetaData.getColumns on a particular known table, say Foodmart.Employee, which has a mixture of null and not null columns
        Hide
        wxiang7 Wu Xiang added a comment -

        Comments resolved.

        Show
        wxiang7 Wu Xiang added a comment - Comments resolved.
        Hide
        julianhyde Julian Hyde added a comment -
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/2d7e1f1a . Thanks for the PR, Wu Xiang !
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.12.0 (2017-03-24).

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.12.0 (2017-03-24).

          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:

              Development