Derby
  1. Derby
  2. DERBY-1963

Routine parameter names displayed by dblook are not escaped.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: 10.7.1.1
    • Component/s: Tools
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer

      Description

      After using this SQL to create a function with a delimited parameter name

      CREATE FUNCTION FRED ("paramOne" INTEGER) RETURNS INTEGER
      LANGUAGE JAVA PARAMETER STYLE JAVA
      EXTERNAL NAME 'fred.foo'

      dblook will output a CREATE FUNCTION statement with the parameter name without quotes:

      CREATE FUNCTION "APP"."FRED" (paramOne INTEGER) RETURNS INTEGER
      LANGUAGE JAVA PARAMETER STYLE JAVA
      READS SQL DATA CALLED ON NULL INPUT
      EXTERNAL NAME 'fred.foo';

      Using the output from dblook to re-create the function will result in a function with a different parameter name.

      1. d1963.diff
        13 kB
        Knut Anders Hatlen
      2. d1963-2.diff
        41 kB
        Knut Anders Hatlen

        Activity

        Hide
        Saurabh Vyas added a comment -

        I tried reproducing this, but while using the dblook output to create the function again, I got this error "ERROR X0Y68: FUNCTION 'FRED' already exists."
        Did I missed any step.

        Comments/Suggestions ?

        Following are the steps I tried :

        fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.ij
        ij version 10.3
        ij> connect 'jdbc:derby:TestDB;create=true';
        ij> CREATE FUNCTION FRED ("paramOne" INTEGER) RETURNS INTEGER
        LANGUAGE JAVA PARAMETER STYLE JAVA
        EXTERNAL NAME 'fred.foo' ;
        0 rows inserted/updated/deleted
        ij> exit;
        fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.dblook -d 'jdbc:derby:TestDB'
        – Timestamp: 2007-01-11 16:44:49.285
        – Source database is: TestDB
        – Connection URL is: jdbc:derby:TestDB
        – appendLogs: false

        – ----------------------------------------------
        – DDL Statements for functions
        – ----------------------------------------------

        CREATE FUNCTION "APP"."FRED" (paramOne INTEGER) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'fred.foo' ;

        fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.ij
        ij version 10.3
        ij> connect 'jdbc:derby:TestDB';
        ij> CREATE FUNCTION "APP"."FRED" (paramOne INTEGER) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'fred.foo' ;
        ERROR X0Y68: FUNCTION 'FRED' already exists.
        ij> exit;

        Show
        Saurabh Vyas added a comment - I tried reproducing this, but while using the dblook output to create the function again, I got this error "ERROR X0Y68: FUNCTION 'FRED' already exists." Did I missed any step. Comments/Suggestions ? Following are the steps I tried : fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.ij ij version 10.3 ij> connect 'jdbc:derby:TestDB;create=true'; ij> CREATE FUNCTION FRED ("paramOne" INTEGER) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'fred.foo' ; 0 rows inserted/updated/deleted ij> exit; fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.dblook -d 'jdbc:derby:TestDB' – Timestamp: 2007-01-11 16:44:49.285 – Source database is: TestDB – Connection URL is: jdbc:derby:TestDB – appendLogs: false – ---------------------------------------------- – DDL Statements for functions – ---------------------------------------------- CREATE FUNCTION "APP"."FRED" (paramOne INTEGER) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'fred.foo' ; fierro@~/JavaDB/NewTest$ java org.apache.derby.tools.ij ij version 10.3 ij> connect 'jdbc:derby:TestDB'; ij> CREATE FUNCTION "APP"."FRED" (paramOne INTEGER) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'fred.foo' ; ERROR X0Y68: FUNCTION 'FRED' already exists. ij> exit;
        Hide
        Mike Matrigali added a comment -

        the issue being reported is a problem with the parameter name "paramOne" - not the function name. The problem will show up if you use a jdbc interface that requires the parameter name rather than a ordernal of the parameter. Also I assume the metadata will be wrong.

        Show
        Mike Matrigali added a comment - the issue being reported is a problem with the parameter name "paramOne" - not the function name. The problem will show up if you use a jdbc interface that requires the parameter name rather than a ordernal of the parameter. Also I assume the metadata will be wrong.
        Hide
        Andrew McIntyre added a comment -

        Unsetting Fix Version on unassigned issues.

        Show
        Andrew McIntyre added a comment - Unsetting Fix Version on unassigned issues.
        Hide
        Knut Anders Hatlen added a comment -

        Triaged for 10.5.2.

        Show
        Knut Anders Hatlen added a comment - Triaged for 10.5.2.
        Hide
        Knut Anders Hatlen added a comment -

        dblook uses the ALIASINFO column in SYS.SYSALIASES to produce the parameter list for procedures and functions. I found this comment in RoutineAliasInfo.toString():

        /**

        • Get this alias info as a string. NOTE: The "ALIASINFO" column
        • in the SYSALIASES table will return the result of this method
        • on a ResultSet.getString() call. That said, since the dblook
        • utility uses ResultSet.getString() to retrieve ALIASINFO and
        • to generate the DDL, THIS METHOD MUST RETURN A STRING THAT
        • IS SYNTACTICALLY VALID, or else the DDL generated by dblook
        • will be incorrect.
          */

        That method does however not escape the parameter names, and that's also why dblook doesn't.

        The attached patch makes RoutineAliasInfo.toString() escape parameter names using IdUtil.normalToDelimited(). It also adds a test case that demonstrates the bug in tools/dblook_test.java. I haven't run any other tests yet, and there will be other tests that need to be updated because of this change (at least all the variants of dblook_test, and there may be tests that check the SYSALIASES table and don't expect the quotes in the ALIASINFO column).

        Show
        Knut Anders Hatlen added a comment - dblook uses the ALIASINFO column in SYS.SYSALIASES to produce the parameter list for procedures and functions. I found this comment in RoutineAliasInfo.toString(): /** Get this alias info as a string. NOTE: The "ALIASINFO" column in the SYSALIASES table will return the result of this method on a ResultSet.getString() call. That said, since the dblook utility uses ResultSet.getString() to retrieve ALIASINFO and to generate the DDL, THIS METHOD MUST RETURN A STRING THAT IS SYNTACTICALLY VALID, or else the DDL generated by dblook will be incorrect. */ That method does however not escape the parameter names, and that's also why dblook doesn't. The attached patch makes RoutineAliasInfo.toString() escape parameter names using IdUtil.normalToDelimited(). It also adds a test case that demonstrates the bug in tools/dblook_test.java. I haven't run any other tests yet, and there will be other tests that need to be updated because of this change (at least all the variants of dblook_test, and there may be tests that check the SYSALIASES table and don't expect the quotes in the ALIASINFO column).
        Hide
        Knut Anders Hatlen added a comment -

        Here's an updated patch that changes more canons. All the regression tests passed.

        Description of the patch:

        • RoutineAliasInfo.java: Escape the parameter names.
        • dblook_makeDB.sql: Add a procedure and a function that need escaping. Affects all variants of dblook_test.
        • dblook_test.out, dblook_test_territory.out, DerbyNet/dblook_test_net.out, DerbyNet/dblook_test_net_territory.out, DerbyNetClient/dblook_test_net.out, DerbyNetClient/dblook_test_net_territory.out:
          Expect extra output because of the added procedure and function, and expect all parameter names in existing test cases to have double quotes.
        • cast.out: Expect quoted identifiers when selecting ALIASINFO from SYS.SYSALIASES.
        Show
        Knut Anders Hatlen added a comment - Here's an updated patch that changes more canons. All the regression tests passed. Description of the patch: RoutineAliasInfo.java: Escape the parameter names. dblook_makeDB.sql: Add a procedure and a function that need escaping. Affects all variants of dblook_test. dblook_test.out, dblook_test_territory.out, DerbyNet/dblook_test_net.out, DerbyNet/dblook_test_net_territory.out, DerbyNetClient/dblook_test_net.out, DerbyNetClient/dblook_test_net_territory.out: Expect extra output because of the added procedure and function, and expect all parameter names in existing test cases to have double quotes. cast.out: Expect quoted identifiers when selecting ALIASINFO from SYS.SYSALIASES.
        Hide
        Knut Anders Hatlen added a comment -

        [bulk update] Close all resolved issues that haven't been updated for more than one year.

        Show
        Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

          People

          • Assignee:
            Knut Anders Hatlen
            Reporter:
            Daniel John Debrunner
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development