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 */

        Attachments

          Activity

            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: