[CALCITE-596] JDBC adapter incorrectly reads null values as 0

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

Environment

Tested on remote database hsql and h2

• Assignee: Julian Hyde
• Reporter: Ng Jiunn Jye
• Created: 11/Feb/15 03:35
• Updated: 16/Apr/15 08:35
• Resolved: 23/Mar/15 22:38

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