[
https://issues.apache.org/jira/browse/CALCITE-596?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14315616#comment-14315616
]
Julian Hyde commented on CALCITE-596:
-------------------------------------
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.
> 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)
