You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2015/02/11 06:56:11 UTC
[jira] [Commented] (CALCITE-596) JDBCAdaptor: Resultset.get does
not handle Null value
[ 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)