Ng Jiunn Jye created CALCITE-596:
------------------------------------
Summary: JDBCAdaptor: Resultset.get does not handle Null value
Key: CALCITE-596
URL: https://issues.apache.org/jira/browse/CALCITE-596
Project: Calcite
Issue Type: Bug
Affects Versions: 1.0.0-incubating
Environment: Tested on remote database hsql and h2
Reporter: Ng Jiunn Jye
Assignee: Julian Hyde
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 */
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
