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

JDBC adapter incorrectly reads null values as 0

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0-incubating
    • Fix Version/s: 1.2.0-incubating
    • Component/s: None
    • Labels:
      None
    • Environment:

      Tested on remote database hsql and h2

      Description

      Description:
      When reading null data(ie integer field) using getObject from remote database using JDBC Adaptor, Calcite return value as 0.

      Cause:
      JDBCAdaptor use getInt (and other getPrimitive method) to retrieve data from remote DB (see generated code line 11,12). According to JDBC Spec, getInt will return 0 for null data. Thus the original null value got lost in the transition.

      On Client side it fail to get null value using getObject.

      Sample Test Code:
      package org.apache.calcite.jdbc;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.Statement;
      import java.util.Properties;

      import org.hsqldb.jdbcDriver;

      public class TestJdbcAdaptorNullValue {

      public static void main(String[] args) {

      try {
      String hsqldbMemUrl = "jdbc:hsqldb:mem:.";
      Connection baseConnection = DriverManager.getConnection(hsqldbMemUrl);
      Statement baseStmt = baseConnection.createStatement();
      baseStmt.execute("CREATE TABLE T1 (\n"
      + "ID INTEGER,\n"
      + "VALS INTEGER)");
      baseStmt.execute("INSERT INTO T1 VALUES (1, 1)");
      baseStmt.execute("INSERT INTO T1 VALUES (2, null)");
      baseStmt.close();
      baseConnection.commit();
      Properties info = new Properties();
      info.put("model",
      "inline:"
      + "{\n"
      + " version: '1.0',\n"
      + " defaultSchema: 'BASEJDBC',\n"
      + " schemas: [\n"
      + "

      {\n" + " type: 'jdbc',\n" + " name: 'BASEJDBC',\n" + " jdbcDriver: '" + jdbcDriver.class.getName() + "',\n" + " jdbcUrl: '" + hsqldbMemUrl + "',\n" + " jdbcCatalog: null,\n" + " jdbcSchema: null\n" + " }

      \n"
      + " ]\n"
      + "}");

      Connection calciteConnection = DriverManager.getConnection(
      "jdbc:calcite:", info);
      ResultSet baseRs = baseConnection.prepareStatement("select * from t1").executeQuery();
      while (baseRs.next())

      { System.out.println ("ID:" + (Integer) baseRs.getObject("ID")); System.out.println ("VALS:" + (Integer) baseRs.getObject("VALS")); }

      baseRs.close();
      ResultSet rs = calciteConnection.prepareStatement("select * from t1").executeQuery();
      while (rs.next())

      { System.out.println ("ID:" + (Integer) rs.getObject("ID")); System.out.println ("VALS:" + (Integer) rs.getObject("VALS")); }

      rs.close();
      calciteConnection.close();
      }catch (Exception e)

      { e.printStackTrace(); }

      }
      }

      Generated Code:
      /* 1 */ org.apache.calcite.DataContext root;
      /* 2 */
      /* 3 */ public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) {
      /* 4 */ root = root0;
      /* 5 */ final org.apache.calcite.linq4j.function.Function1 rowBuilderFactory = new org.apache.calcite.linq4j.function.Function1() {
      /* 6 */ public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) {
      /* 7 */ return new org.apache.calcite.linq4j.function.Function0() {
      /* 8 */ public Object apply() {
      /* 9 */ try

      { /* 10 */ final Object[] values = new Object[2]; /* 11 */ values[0] = resultSet.getInt(1); /* 12 */ values[1] = resultSet.getInt(2); /* 13 */ return values; /* 14 */ }

      catch (java.sql.SQLException e)

      { /* 15 */ throw new RuntimeException( /* 16 */ e); /* 17 */ }

      /* 18 */ }
      /* 19 */ }
      /* 20 */ ;
      /* 21 */ }
      /* 22 */ public Object apply(final Object resultSet)

      { /* 23 */ return apply( /* 24 */ (java.sql.ResultSet) resultSet); /* 25 */ }

      /* 26 */ }
      /* 27 */ ;
      /* 28 */ final org.apache.calcite.linq4j.Enumerable enumerable = org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema) root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(), "SELECT *\nFROM \"T1\"", rowBuilderFactory);
      /* 29 */ return enumerable;
      /* 30 */ }
      /* 31 */
      /* 32 */
      /* 33 */ public java.lang.reflect.Type getElementType()

      { /* 34 */ return java.lang.Object[].class; /* 35 */ }

      /* 36 */
      /* 37 */

        Activity

        Hide
        julianhyde Julian Hyde added a comment -

        Yeah, it should follow the call to getInt with wasNull if the column is nullable. Might be able to reproduce this on the foodmart schema (I think the store table has some nullable int columns). Bug is probably in JdbcToEnumerableConverter.generateGet.

        Show
        julianhyde Julian Hyde added a comment - Yeah, it should follow the call to getInt with wasNull if the column is nullable. Might be able to reproduce this on the foodmart schema (I think the store table has some nullable int columns). Bug is probably in JdbcToEnumerableConverter.generateGet.
        Hide
        julianhyde Julian Hyde added a comment -

        Ng Jiunn Jye, do you want to try to fix this?

        Show
        julianhyde Julian Hyde added a comment - Ng Jiunn Jye , do you want to try to fix this?
        Hide
        YAY Yuri Au Yong added a comment -

        Patch to return null objects when primitive type column value is null

        Show
        YAY Yuri Au Yong added a comment - Patch to return null objects when primitive type column value is null
        Show
        julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/2b07a9e9 .
        Hide
        julianhyde Julian Hyde added a comment -

        Resolved in release 1.2.0-incubating (2015-04-16)

        Show
        julianhyde Julian Hyde added a comment - Resolved in release 1.2.0-incubating (2015-04-16)

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            jiunnjye Ng Jiunn Jye
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development