Derby
  1. Derby
  2. DERBY-390

Import/export fails with table names that are quoted in SQL like import to table "Order"

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.1
    • Fix Version/s: 10.1.1.0, 10.2.1.6
    • Component/s: Tools
    • Labels:
      None

      Description

      import fails irrespective off whether the name is passed as a quoted sttring or not:
      ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
      null, null, null, 0) ;
      ERROR XIE0M: Table '"ORDER"' does not exist.
      ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ORDER' , 'order.dat' ,
      null, null, null, 0) ;
      ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
      line 1, column 13.' was thrown while evaluating an expression.
      ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 13.

      Export works with a quoted table name but fails if the name is not quoted :
      call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
      null, null, null) ; — PASSES

      ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'ORDER' , 'order.dat' ,
      null, null, null) ;
      ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
      line 1, column 15.' was thrown while evaluating an expression.
      ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 15.

      Repro:
      connect 'jdbc:derby:wombat;create=true';
      create table "ORDER"(a int ) ;
      insert into "ORDER" values(1) ;
      insert into "ORDER" values(2) ;
      insert into "ORDER" values(3) ;
      call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
      null, null, null) ;
      – Perform Import
      call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' ,
      null, null, null, 0) ;

      ERROR XIE0M: Table '"ORDER"' does not exist.
      call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'ORDER' , 'order.dat' ,
      null, null, null, 0) ;
      ERROR 38000: The exception 'SQL Exception: Syntax error: Encountered "ORDER" at
      line 1, column 13.' was thrown while evaluating an expression.
      ERROR 42X01: Syntax error: Encountered "ORDER" at line 1, column 13.

      Note: I think the same problem will occur with when quoted schema Names and column names are passed to import/export procedures.

      1. derby390.diff
        181 kB
        Suresh Thalamati

        Activity

        Suresh Thalamati created issue -
        Hide
        Suresh Thalamati added a comment -

        There are two ways to fix this problem:
        1) Import/Export should treat schema Names , table Names , Column Names as case-insensitive , unless delimited by double quotes. For example , valid call for a quoted table name is :
        call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' , null, null, null, 0) ;
        call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' , null, null, null) ;

        Note that table name is passed is delimited by double quotes.

        2) Import/Export should treat schema Names , table Names , Column Names as case sensitive. Because
        the database automatically translated the SQL identifier into uppercase by the system. All unquoted
        table names ..etc should be passed in upper case. For example:
        To import/export into a table emp (create table emp(a int ) ) .
        call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EMP' , 'emp.dat' , null, null, null, 0) ;
        call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EMP' , 'emp.dat' , null, null, null) ;

        Note that table name is passed in upper case.

        I like the first approach because it is more consistent with SQL92 names and users need not pass upper case
        names for all unquoted names(cases insensitive names). and also will not break any existing application that
        might be working becasue current import/export procedures accepts names in case-insensitive form.
        I would like to fix this problem with the first approach, unless some one has objections.

        Show
        Suresh Thalamati added a comment - There are two ways to fix this problem: 1) Import/Export should treat schema Names , table Names , Column Names as case-insensitive , unless delimited by double quotes. For example , valid call for a quoted table name is : call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, '"ORDER"' , 'order.dat' , null, null, null, 0) ; call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, '"ORDER"' , 'order.dat' , null, null, null) ; Note that table name is passed is delimited by double quotes. 2) Import/Export should treat schema Names , table Names , Column Names as case sensitive. Because the database automatically translated the SQL identifier into uppercase by the system. All unquoted table names ..etc should be passed in upper case. For example: To import/export into a table emp (create table emp(a int ) ) . call SYSCS_UTIL.SYSCS_IMPORT_TABLE (null, 'EMP' , 'emp.dat' , null, null, null, 0) ; call SYSCS_UTIL.SYSCS_EXPORT_TABLE (null, 'EMP' , 'emp.dat' , null, null, null) ; Note that table name is passed in upper case. I like the first approach because it is more consistent with SQL92 names and users need not pass upper case names for all unquoted names(cases insensitive names). and also will not break any existing application that might be working becasue current import/export procedures accepts names in case-insensitive form. I would like to fix this problem with the first approach, unless some one has objections.
        Hide
        Suresh Thalamati added a comment -

        By looking at other system procedures and the way JDBC methods takes the table names, it looks like following the second approach is more consistent with other system procedures in Derby. i.e
        Import/Export should treat schema Names , table Names , Column Names as case sensitive. Because
        the database automatically translated the SQL identifier into uppercase by the system. All unquoted
        table names ..etc should be passed in upper case to import/export procedure.

        Any comments ?

        Show
        Suresh Thalamati added a comment - By looking at other system procedures and the way JDBC methods takes the table names, it looks like following the second approach is more consistent with other system procedures in Derby. i.e Import/Export should treat schema Names , table Names , Column Names as case sensitive. Because the database automatically translated the SQL identifier into uppercase by the system. All unquoted table names ..etc should be passed in upper case to import/export procedure. Any comments ?
        Hide
        Satheesh Bandaram added a comment -

        I agree with the suggestion for system procedures to treat arguments as case sensitive. This has the unwanted side-effect of requiring users to provide table/column names (unquoted identifiers) in upper case while calling system procedures and hence should be documented clearly. Can we add the documentation, where applicable, for 10.1 manuals?

        This should also go into release notes, I think.

        Show
        Satheesh Bandaram added a comment - I agree with the suggestion for system procedures to treat arguments as case sensitive. This has the unwanted side-effect of requiring users to provide table/column names (unquoted identifiers) in upper case while calling system procedures and hence should be documented clearly. Can we add the documentation, where applicable, for 10.1 manuals? This should also go into release notes, I think.
        Suresh Thalamati made changes -
        Field Original Value New Value
        Assignee Suresh Thalamati [ tsuresh ]
        Hide
        Suresh Thalamati added a comment -

        patch to handle case-sensitive SQL identifiers correctly. Import/export procedure parameters for
        table names , schema names , columns names should be passed in the case-sensitive form
        if they are quoted identfiers and in upper case if they are not quoted SQL identifiers. Import/export
        will generate insert/select statements with quoted table names , schema name and column names
        to be execute on the database after this patch.

        Ran debyall test suite , all tests passed.

        I will create sub-task to modify the documentaion for import/export , once the patch is accepted.
        I would like this fix to be ported to 10.1 also.

        Show
        Suresh Thalamati added a comment - patch to handle case-sensitive SQL identifiers correctly. Import/export procedure parameters for table names , schema names , columns names should be passed in the case-sensitive form if they are quoted identfiers and in upper case if they are not quoted SQL identifiers. Import/export will generate insert/select statements with quoted table names , schema name and column names to be execute on the database after this patch. Ran debyall test suite , all tests passed. I will create sub-task to modify the documentaion for import/export , once the patch is accepted. I would like this fix to be ported to 10.1 also.
        Suresh Thalamati made changes -
        Attachment derby390.diff [ 12311035 ]
        Hide
        Suresh Thalamati added a comment -

        Same patch (derby390.diff) also works fine for 10.1 branch also. Ran Derbyall on 10.1 after applying the patch for a 10.1 client , all tests passed.

        Show
        Suresh Thalamati added a comment - Same patch (derby390.diff) also works fine for 10.1 branch also. Ran Derbyall on 10.1 after applying the patch for a 10.1 client , all tests passed.
        Hide
        Suresh Thalamati added a comment -

        patch was committed to 10.1 branch wiith svn 208776
        patch was committed to 10.2 (main) branch wiith svn 208770

        Show
        Suresh Thalamati added a comment - patch was committed to 10.1 branch wiith svn 208776 patch was committed to 10.2 (main) branch wiith svn 208770
        Suresh Thalamati made changes -
        Fix Version/s 10.1.1.0 [ 10993 ]
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 10.2.0.0 [ 11187 ]
        Resolution Fixed [ 1 ]
        Hide
        Rick Hillegas added a comment -

        Assigning to Tools component.

        Show
        Rick Hillegas added a comment - Assigning to Tools component.
        Rick Hillegas made changes -
        Component/s Tools [ 11414 ]
        Kristian Waagan made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Gavin made changes -
        Workflow jira [ 12321647 ] Default workflow, editable Closed status [ 12800542 ]

          People

          • Assignee:
            Suresh Thalamati
            Reporter:
            Suresh Thalamati
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development