Derby
  1. Derby
  2. DERBY-591

Several builtin functions are incorrectly exposed through the JDBC escaped function syntax {fn <function_call>}

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2.0, 10.1.1.0, 10.3.1.4, 10.4.1.3
    • Fix Version/s: None
    • Component/s: JDBC, SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Deviation from standard

      Description

      Due to lack of direction in the way functions are added into the parser, any functions that are added into the parser's miscBuiltinsCore are also automatically JDBC/ODBC escaped functions.

      E.g. the new XML functions (e.g. XMLPARSE), COALESCE, VALUE etc. etc.

      The correct set of escaped scaler functions is defined in appendex C (C1- C5) of JDBC 3.0.

      Discovered this while trying to make the JDBC escape functions table driven, at least for the valid subset of table driven functions added in DERBY-475.

      Probably just need to split the miscBuiltins core into two functions, one for functions that are SQL and allowed in JDBC escape, and one for ones only allowed in SQL.

        Issue Links

          Activity

          Hide
          Satheesh Bandaram added a comment -

          THANKS for filing this one... This has been wrong for a sometime.

          I think non-escaped functions should be added to miscBuiltins() and escaped functions that could also be SQL operators should have been part of miscBuiltinsCore(). But looks like most recently added SQL only operators where put in miscBuiltinsCore() by mistake.

          Show
          Satheesh Bandaram added a comment - THANKS for filing this one... This has been wrong for a sometime. I think non-escaped functions should be added to miscBuiltins() and escaped functions that could also be SQL operators should have been part of miscBuiltinsCore(). But looks like most recently added SQL only operators where put in miscBuiltinsCore() by mistake.
          Hide
          Daniel John Debrunner added a comment -

          These functions also incorrectly work as escaped functions. Prior to DERBY-471 being fixed they were incorrectly listed in the various DatabaseMetaData methods that return the supported escaped functions.

          values

          {fn LOWER('HELLO')}

          ;
          values

          {fn SUBSTR('Bonjour', 4)}

          ;
          values

          {fn SUBSTRING('bonjour', 4)}

          ;
          values

          {fn UPPER('goodbye')}

          ;
          values

          {fn CURRENT_USER() }

          ;
          values

          {fn SESSION_USER() }

          ;

          Show
          Daniel John Debrunner added a comment - These functions also incorrectly work as escaped functions. Prior to DERBY-471 being fixed they were incorrectly listed in the various DatabaseMetaData methods that return the supported escaped functions. values {fn LOWER('HELLO')} ; values {fn SUBSTR('Bonjour', 4)} ; values {fn SUBSTRING('bonjour', 4)} ; values {fn UPPER('goodbye')} ; values {fn CURRENT_USER() } ; values {fn SESSION_USER() } ;
          Hide
          Daniel John Debrunner added a comment -

          Note that SUBSTRING with three arguments is a valid JDBC/ODBC escaped function, while that with two arguments is not.

          Show
          Daniel John Debrunner added a comment - Note that SUBSTRING with three arguments is a valid JDBC/ODBC escaped function, while that with two arguments is not.
          Hide
          Mike Matrigali added a comment -

          not quite sure if this is a jdbc or sql issue, or both. For now setting both.

          Show
          Mike Matrigali added a comment - not quite sure if this is a jdbc or sql issue, or both. For now setting both.
          Hide
          Bernt M. Johnsen added a comment -

          What about the functions that are allowed in JDBC but not in SQL (or have different name/syntax in SQL)? The ones I know is:

          JDBC SQL

          {fn LENGTH(...)}

          CHARACTER_LENGTH(....)

          {fn SUBSTR(....)}

          SUBSTRING(....)

          {fn LTRIM(...)}

          TRIM(....)

          {fn RTRIM(...)}

          TRIM(....)

          {fn LOCATE(...)}

          POSITION(....)

          {fn CURTIME()}

          LOCALTIME

          (The parameters may not have the same order/syntax/number)

          Show
          Bernt M. Johnsen added a comment - What about the functions that are allowed in JDBC but not in SQL (or have different name/syntax in SQL)? The ones I know is: JDBC SQL {fn LENGTH(...)} CHARACTER_LENGTH(....) {fn SUBSTR(....)} SUBSTRING(....) {fn LTRIM(...)} TRIM(....) {fn RTRIM(...)} TRIM(....) {fn LOCATE(...)} POSITION(....) {fn CURTIME()} LOCALTIME (The parameters may not have the same order/syntax/number)
          Hide
          Daniel John Debrunner added a comment -

          Not sure what you are asking here Bernt. This is a bug reporting that several Derby functions are incorrectly exposed thorugh the JDBC escaped function mechanism. It's not about implementing more escaped functions.

          If you were concerned that a patch might break the set of functions you listed then I've already added tests in jdbcapi/metadata.java that see which of the standard escaped functions are implemented by Derby and which are declared to be implemented by Derby. And that those match.

          I have reported also DERB-723 where

          {fn CHAR(...)}

          is being incorrectly mapped to CHAR(....).

          Show
          Daniel John Debrunner added a comment - Not sure what you are asking here Bernt. This is a bug reporting that several Derby functions are incorrectly exposed thorugh the JDBC escaped function mechanism. It's not about implementing more escaped functions. If you were concerned that a patch might break the set of functions you listed then I've already added tests in jdbcapi/metadata.java that see which of the standard escaped functions are implemented by Derby and which are declared to be implemented by Derby. And that those match. I have reported also DERB-723 where {fn CHAR(...)} is being incorrectly mapped to CHAR(....).
          Hide
          Satheesh Bandaram added a comment -

          I am not planning to work on this bug in the short term, so unassigning from myself.

          Show
          Satheesh Bandaram added a comment - I am not planning to work on this bug in the short term, so unassigning from myself.
          Hide
          Dag H. Wanvik added a comment -

          I think what Bernt is refering to is the fact that some of the
          functions defined by JDBC, e.g. LTRIM, are not a part of the SQL
          standard, but Derby allows these functions outside JDBC escape syntax:

          ij> values LTRIM(' foo');
          1


          foo

          whereas others, for example CURTIME, only works with escape syntax.
          Again, I think this happens because miscBuiltinsCore is shared between
          the escaped and the non-escaped contexts in the parser. (CURTIME is
          defined outside of miscBuiltinsCore.)

          Show
          Dag H. Wanvik added a comment - I think what Bernt is refering to is the fact that some of the functions defined by JDBC, e.g. LTRIM, are not a part of the SQL standard, but Derby allows these functions outside JDBC escape syntax: ij> values LTRIM(' foo'); 1 foo whereas others, for example CURTIME, only works with escape syntax. Again, I think this happens because miscBuiltinsCore is shared between the escaped and the non-escaped contexts in the parser. (CURTIME is defined outside of miscBuiltinsCore.)
          Hide
          Kristian Waagan added a comment -

          I plan to have a look at this and some related issues.
          This is my first adventure in the realm of the parser, so I would be very thankful for suggestions and feedback along the way.
          The first patch will aim to fix the problem in the way described (splitting a single method into two), and then I'll work on it to see how much more code must be moved around or duplicated.

          I have one question already.
          Is it okay to clean this up properly, or do we have to keep certain things the way they have used to be for compatibility?

          Can we remove all misplaced functions from the fn-set? (for instance the XML functions)
          Can we remove all misplaced fn-functions from the SQL-set? (for instance LTRIM)

          Show
          Kristian Waagan added a comment - I plan to have a look at this and some related issues. This is my first adventure in the realm of the parser, so I would be very thankful for suggestions and feedback along the way. The first patch will aim to fix the problem in the way described (splitting a single method into two), and then I'll work on it to see how much more code must be moved around or duplicated. I have one question already. Is it okay to clean this up properly, or do we have to keep certain things the way they have used to be for compatibility? Can we remove all misplaced functions from the fn-set? (for instance the XML functions) Can we remove all misplaced fn-functions from the SQL-set? (for instance LTRIM)
          Hide
          Kristian Waagan added a comment -

          Don't have time for this one now, feel free to grab it.

          Show
          Kristian Waagan added a comment - Don't have time for this one now, feel free to grab it.
          Hide
          Dyre Tjeldvoll added a comment -

          Removing Fix-version for unassigned issues

          Show
          Dyre Tjeldvoll added a comment - Removing Fix-version for unassigned issues
          Hide
          Mike Matrigali added a comment -

          Triaged July 10, 2009: assigned normal urgency.

          Show
          Mike Matrigali added a comment - Triaged July 10, 2009: assigned normal urgency.

            People

            • Assignee:
              Unassigned
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development