Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.9.0
    • Component/s: None
    • Labels:
      None

      Description

      Currently JDBC doesn't handle NULL value, only wasnull() is provided. JDBC handles NULL data like the following rule.

      • Text type: return java null.
      • Int, Float type: return 0
      • Date/Time type: return null.
      • Boolean: return false
      • getObject(): return java null

        Activity

        Hide
        prafulla Prafulla T added a comment -

        Are these rules defined by database implementation OR is it decided by specification?
        If so, Can you update the issue with the link?

        Show
        prafulla Prafulla T added a comment - Are these rules defined by database implementation OR is it decided by specification? If so, Can you update the issue with the link?
        Hide
        hjkim Hyoungjun Kim added a comment - - edited

        I didn't check JSR about JDBC. After Prafulla comment, I found a JDBC specification in the following URL.
        http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf

        I'll change JDBC null handing according to the following specifications.

        The method wasNull can be called to determine if the last value retrieved was a 
        SQL NULL in the database. 
        When the column value in the database is SQL NULL, it may be returned to the Java 
        application as null, 0, or false, depending on the type of the column value. 
        Column values that map to Java Object types are returned as a Java null; those 
        that map to numeric types are returned as 0; those that map to a Java boolean are 
        returned as false. Therefore, it may be necessary to call the wasNull method to 
        determine whether the last value retrieved was a SQL NULL.
        

        There is one issue. It is difficult to identify NULL value in the Tajo Shell.
        So I'll add configuration named "tajo.cli.nullchar".

        id,name,id2,name2,c1,c2
        -------------------------------
        1,ooo,1,,9991231,ooo
        2,ppp,2,,9991231,ppp
        3,qqq,,,9991231,9991231
        4,rrr,,,9991231,9991231
        5,xxx,,,9991231,9991231
        

        If "tajo.cli.nullchar" is set with "null", Shell prints the following.

        id,name,id2,name2,c1,c2
        -------------------------------
        1,ooo,1,null,9991231,ooo
        2,ppp,2,null,9991231,ppp
        3,qqq,null,null,9991231,9991231
        4,rrr,null,null,9991231,9991231
        5,xxx,null,null,9991231,9991231
        
        Show
        hjkim Hyoungjun Kim added a comment - - edited I didn't check JSR about JDBC. After Prafulla comment, I found a JDBC specification in the following URL. http://download.oracle.com/otn-pub/jcp/jdbc-4_1-mrel-spec/jdbc4.1-fr-spec.pdf I'll change JDBC null handing according to the following specifications. The method wasNull can be called to determine if the last value retrieved was a SQL NULL in the database. When the column value in the database is SQL NULL, it may be returned to the Java application as null, 0, or false, depending on the type of the column value. Column values that map to Java Object types are returned as a Java null; those that map to numeric types are returned as 0; those that map to a Java boolean are returned as false. Therefore, it may be necessary to call the wasNull method to determine whether the last value retrieved was a SQL NULL. There is one issue. It is difficult to identify NULL value in the Tajo Shell. So I'll add configuration named "tajo.cli.nullchar". id,name,id2,name2,c1,c2 ------------------------------- 1,ooo,1,,9991231,ooo 2,ppp,2,,9991231,ppp 3,qqq,,,9991231,9991231 4,rrr,,,9991231,9991231 5,xxx,,,9991231,9991231 If "tajo.cli.nullchar" is set with "null", Shell prints the following. id,name,id2,name2,c1,c2 ------------------------------- 1,ooo,1,null,9991231,ooo 2,ppp,2,null,9991231,ppp 3,qqq,null,null,9991231,9991231 4,rrr,null,null,9991231,9991231 5,xxx,null,null,9991231,9991231
        Hide
        githubbot ASF GitHub Bot added a comment -

        GitHub user babokim opened a pull request:

        https://github.com/apache/tajo/pull/18

        TAJO-842: NULL handling in JDBC.

        You can merge this pull request into a Git repository by running:

        $ git pull https://github.com/babokim/tajo TAJO-842

        Alternatively you can review and apply these changes as the patch at:

        https://github.com/apache/tajo/pull/18.patch

        To close this pull request, make a commit to your master/trunk branch
        with (at least) the following in the commit message:

        This closes #18


        commit 8731ebc2f2a7dd57b41b3dc9a7c296718f49cc47
        Author: 김형준 <babokim@babokim-macbook-pro.local>
        Date: 2014-05-24T14:02:43Z

        TAJO-842: NULL handling in JDBC.


        Show
        githubbot ASF GitHub Bot added a comment - GitHub user babokim opened a pull request: https://github.com/apache/tajo/pull/18 TAJO-842 : NULL handling in JDBC. You can merge this pull request into a Git repository by running: $ git pull https://github.com/babokim/tajo TAJO-842 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/tajo/pull/18.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #18 commit 8731ebc2f2a7dd57b41b3dc9a7c296718f49cc47 Author: 김형준 <babokim@babokim-macbook-pro.local> Date: 2014-05-24T14:02:43Z TAJO-842 : NULL handling in JDBC.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user prafullat commented on the pull request:

        https://github.com/apache/tajo/pull/18#issuecomment-44365397

        wasNull related changes look good to me.

        Show
        githubbot ASF GitHub Bot added a comment - Github user prafullat commented on the pull request: https://github.com/apache/tajo/pull/18#issuecomment-44365397 wasNull related changes look good to me.
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user jinossy commented on the pull request:

        https://github.com/apache/tajo/pull/18#issuecomment-44383034

        +1 for the patch.
        Thank you for your contribution!
        Thanks @prafullat for the review!

        Show
        githubbot ASF GitHub Bot added a comment - Github user jinossy commented on the pull request: https://github.com/apache/tajo/pull/18#issuecomment-44383034 +1 for the patch. Thank you for your contribution! Thanks @prafullat for the review!
        Hide
        githubbot ASF GitHub Bot added a comment -

        Github user asfgit closed the pull request at:

        https://github.com/apache/tajo/pull/18

        Show
        githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/tajo/pull/18
        Hide
        jhkim Jinho Kim added a comment -

        Committed the patch to master branch

        Show
        jhkim Jinho Kim added a comment - Committed the patch to master branch
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Tajo-master-build #229 (See https://builds.apache.org/job/Tajo-master-build/229/)
        TAJO-842: NULL handling in JDBC. (Hyoungjun Kim via jinho) (jinossy: rev 300fde0a6f26bab558d5bfe50498b1c4c5cccbfd)

        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestBSTIndexExec.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestNLJoinExec.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestPhysicalPlanner.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestRightOuterMergeJoinExec.java
        • tajo-core/src/main/java/org/apache/tajo/master/querymaster/Query.java
        • tajo-core/src/test/resources/results/TestTablePartitions/case1.result
        • tajo-core/src/test/resources/results/TestJoinQuery/testRightOuterJoin1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestRightOuterHashJoinExec.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestMergeJoinExec.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testRightOuterJoinWithEmptyTable1.result
        • tajo-core/src/main/java/org/apache/tajo/worker/Task.java
        • tajo-core/src/test/resources/results/TestSelectQuery/testCaseWhenWithoutElse.result
        • tajo-core/src/test/resources/results/TestTajoCli/testSelectResultWithNullFalse.result
        • tajo-core/src/test/resources/results/TestNetTypes/testSort2.result
        • tajo-core/src/test/java/org/apache/tajo/TajoTestingCluster.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestFullOuterHashJoinExec.java
        • tajo-core/src/test/resources/results/TestCaseByCases/testTAJO415Case.result
        • tajo-core/src/test/java/org/apache/tajo/QueryTestCaseBase.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoin1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestExternalSortExec.java
        • tajo-core/src/test/resources/results/TestBuiltinFunctions/testSplitPartNested.result
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testRightOuterJoinWithEmptyTable1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestLeftOuterNLJoinExec.java
        • tajo-core/src/test/java/org/apache/tajo/cli/TestTajoCli.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testRightOuterJoin1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashJoinExec.java
        • tajo-client/src/main/java/org/apache/tajo/cli/SetCommand.java
        • tajo-core/src/main/java/org/apache/tajo/master/GlobalEngine.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestLeftOuterHashJoinExec.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestFullOuterMergeJoinExec.java
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashAntiJoinExec.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testFullOuterJoinWithEmptyTable1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/query/TestInsertQuery.java
        • tajo-core/src/test/resources/results/TestTajoCli/testSelectResultWithNullTrue.result
        • tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.result
        • tajo-client/src/main/java/org/apache/tajo/cli/TajoCli.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithEmptyTable1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestProgressExternalSortExec.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithConstantExpr1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestBNLJoinExec.java
        • tajo-core/src/test/java/org/apache/tajo/client/TestTajoClient.java
        • tajo-core/src/main/java/org/apache/tajo/worker/TaskAttemptContext.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithConstantExpr2.result
        • tajo-core/src/test/resources/results/TestTablePartitions/case2.result
        • tajo-client/src/main/java/org/apache/tajo/jdbc/TajoResultSetBase.java
        • tajo-common/src/main/java/org/apache/tajo/conf/TajoConf.java
        • tajo-core/src/test/java/org/apache/tajo/worker/TestRangeRetrieverHandler.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testFullOuterJoinWithEmptyTable1.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestSortExec.java
        • tajo-core/src/test/java/org/apache/tajo/engine/query/TestNullValues.java
        • tajo-core/src/main/java/org/apache/tajo/engine/planner/physical/StoreTableExec.java
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testFullOuterJoin1.result
        • tajo-core/src/test/resources/results/TestNetTypes/testJoin.result
        • tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashSemiJoinExec.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testFullOuterJoin1.result
        • tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin1.result
        • tajo-client/src/main/java/org/apache/tajo/cli/DefaultTajoCliOutputFormatter.java
        • tajo-core/src/test/resources/results/TestJoinQuery/testOuterJoinAndCaseWhen1.result
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Tajo-master-build #229 (See https://builds.apache.org/job/Tajo-master-build/229/ ) TAJO-842 : NULL handling in JDBC. (Hyoungjun Kim via jinho) (jinossy: rev 300fde0a6f26bab558d5bfe50498b1c4c5cccbfd) tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestBSTIndexExec.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestNLJoinExec.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestPhysicalPlanner.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestRightOuterMergeJoinExec.java tajo-core/src/main/java/org/apache/tajo/master/querymaster/Query.java tajo-core/src/test/resources/results/TestTablePartitions/case1.result tajo-core/src/test/resources/results/TestJoinQuery/testRightOuterJoin1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestRightOuterHashJoinExec.java tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithEmptyTable1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestMergeJoinExec.java tajo-core/src/test/resources/results/TestJoinQuery/testRightOuterJoinWithEmptyTable1.result tajo-core/src/main/java/org/apache/tajo/worker/Task.java tajo-core/src/test/resources/results/TestSelectQuery/testCaseWhenWithoutElse.result tajo-core/src/test/resources/results/TestTajoCli/testSelectResultWithNullFalse.result tajo-core/src/test/resources/results/TestNetTypes/testSort2.result tajo-core/src/test/java/org/apache/tajo/TajoTestingCluster.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestFullOuterHashJoinExec.java tajo-core/src/test/resources/results/TestCaseByCases/testTAJO415Case.result tajo-core/src/test/java/org/apache/tajo/QueryTestCaseBase.java tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoin1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestExternalSortExec.java tajo-core/src/test/resources/results/TestBuiltinFunctions/testSplitPartNested.result tajo-core/src/test/resources/results/TestJoinBroadcast/testRightOuterJoinWithEmptyTable1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestLeftOuterNLJoinExec.java tajo-core/src/test/java/org/apache/tajo/cli/TestTajoCli.java tajo-core/src/test/resources/results/TestJoinBroadcast/testRightOuterJoin1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashJoinExec.java tajo-client/src/main/java/org/apache/tajo/cli/SetCommand.java tajo-core/src/main/java/org/apache/tajo/master/GlobalEngine.java tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithConstantExpr2.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestLeftOuterHashJoinExec.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestFullOuterMergeJoinExec.java tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashAntiJoinExec.java tajo-core/src/test/resources/results/TestJoinBroadcast/testFullOuterJoinWithEmptyTable1.result tajo-core/src/test/java/org/apache/tajo/engine/query/TestInsertQuery.java tajo-core/src/test/resources/results/TestTajoCli/testSelectResultWithNullTrue.result tajo-core/src/test/resources/results/TestJoinQuery/testLeftOuterJoinWithConstantExpr1.result tajo-client/src/main/java/org/apache/tajo/cli/TajoCli.java tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithEmptyTable1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestProgressExternalSortExec.java tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithConstantExpr1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestBNLJoinExec.java tajo-core/src/test/java/org/apache/tajo/client/TestTajoClient.java tajo-core/src/main/java/org/apache/tajo/worker/TaskAttemptContext.java tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoinWithConstantExpr2.result tajo-core/src/test/resources/results/TestTablePartitions/case2.result tajo-client/src/main/java/org/apache/tajo/jdbc/TajoResultSetBase.java tajo-common/src/main/java/org/apache/tajo/conf/TajoConf.java tajo-core/src/test/java/org/apache/tajo/worker/TestRangeRetrieverHandler.java tajo-core/src/test/resources/results/TestJoinQuery/testFullOuterJoinWithEmptyTable1.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestSortExec.java tajo-core/src/test/java/org/apache/tajo/engine/query/TestNullValues.java tajo-core/src/main/java/org/apache/tajo/engine/planner/physical/StoreTableExec.java tajo-core/src/test/resources/results/TestJoinBroadcast/testFullOuterJoin1.result tajo-core/src/test/resources/results/TestNetTypes/testJoin.result tajo-core/src/test/java/org/apache/tajo/engine/planner/physical/TestHashSemiJoinExec.java tajo-core/src/test/resources/results/TestJoinQuery/testFullOuterJoin1.result tajo-core/src/test/resources/results/TestJoinBroadcast/testLeftOuterJoin1.result tajo-client/src/main/java/org/apache/tajo/cli/DefaultTajoCliOutputFormatter.java tajo-core/src/test/resources/results/TestJoinQuery/testOuterJoinAndCaseWhen1.result
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Tajo-master-build #230 (See https://builds.apache.org/job/Tajo-master-build/230/)
        TAJO-842: NULL handling in JDBC. (fixed a missing log in CHANGES) (jinossy: rev 25c04db39d33d59b245730b5f2778a49e2042294)

        • CHANGES
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Tajo-master-build #230 (See https://builds.apache.org/job/Tajo-master-build/230/ ) TAJO-842 : NULL handling in JDBC. (fixed a missing log in CHANGES) (jinossy: rev 25c04db39d33d59b245730b5f2778a49e2042294) CHANGES

          People

          • Assignee:
            hjkim Hyoungjun Kim
            Reporter:
            hjkim Hyoungjun Kim
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development