Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0
    • Fix Version/s: 10.6.1.0
    • Component/s: Documentation
    • Labels:
      None
    • Environment:
      n/a

      Description

      The COALESCE function has been implemeted since 10.0.2.0 (at least) but isn't documented.

      1. rreffunccoalesce.html
        5 kB
        Kim Haase
      2. DERBY-495-3.diff
        4 kB
        Kim Haase
      3. rreffunccoalesce.html
        5 kB
        Kim Haase
      4. DERBY-495-2.diff
        4 kB
        Kim Haase
      5. rreffunccoalesce.html
        5 kB
        Kim Haase
      6. DERBY-495.diff
        4 kB
        Kim Haase

        Activity

        Hide
        Mamta A. Satoor added a comment -

        I have some suggestion on what information can be included in the documentation for COALESCE/VALUE.

        First of all, the function can be added to the existing list of Built-in functions in Reference Guide. Functions like IDENTITY_VAL_LOCAL, LOCATE are explained in that section. Definition of COALESCE "This scalar function takes two or more compatible arguments and returns the first argument that is not null. The result will be null only if all the arguments are null. VALUE is another name for COALESCE function and can be used interchangeably." The compatible datatypes and the datatype of resultant is listed in a table in the comments section of "org.apache.derby.impl.sql.compile.CoalesceFunctionNode.java" Feel free to rephrase/reword the informatio I have suggested.
        Syntax
        COALESCE (expression, expression[,expression]...)

        Several examples of this function can be found in org.apache.derbyTesting.tests.lang.coalesceTests.java

        Show
        Mamta A. Satoor added a comment - I have some suggestion on what information can be included in the documentation for COALESCE/VALUE. First of all, the function can be added to the existing list of Built-in functions in Reference Guide. Functions like IDENTITY_VAL_LOCAL, LOCATE are explained in that section. Definition of COALESCE "This scalar function takes two or more compatible arguments and returns the first argument that is not null. The result will be null only if all the arguments are null. VALUE is another name for COALESCE function and can be used interchangeably." The compatible datatypes and the datatype of resultant is listed in a table in the comments section of "org.apache.derby.impl.sql.compile.CoalesceFunctionNode.java" Feel free to rephrase/reword the informatio I have suggested. Syntax COALESCE (expression, expression [,expression] ...) Several examples of this function can be found in org.apache.derbyTesting.tests.lang.coalesceTests.java
        Hide
        Paul Jenkins added a comment -

        The Built-in Functions section of the Reference Guide would be fine.

        Would it be possible to check if there are other undocumented functions ? I've been looking for a function to help me to determine the number of days between two dates. In other systems there are functions like DAYS() and DATEDIFF().

        Paul.

        Show
        Paul Jenkins added a comment - The Built-in Functions section of the Reference Guide would be fine. Would it be possible to check if there are other undocumented functions ? I've been looking for a function to help me to determine the number of days between two dates. In other systems there are functions like DAYS() and DATEDIFF(). Paul.
        Hide
        Jeff Levitt added a comment -

        Hi Mamta,

        Can you provide an example and a description of the example that I can use in a patch for this issue? I know you mentioned examples in coalesceTests.java, but I was wondering if you could choose a good one and describe it and place it here in a comment. Thanks, Jeff

        Show
        Jeff Levitt added a comment - Hi Mamta, Can you provide an example and a description of the example that I can use in a patch for this issue? I know you mentioned examples in coalesceTests.java, but I was wondering if you could choose a good one and describe it and place it here in a comment. Thanks, Jeff
        Hide
        Mamta A. Satoor added a comment -

        Jeff, here are couple examples
        create table temp(smallintcol smallint, bigintcol bigint,intcol integer)
        insert into temp values (1,null,null)
        insert into temp values (null,2,null)
        insert into temp values (null,null,3)

        – the return datatype of coalesce would be bigint
        select coalesce (smallintcol, bigintcol) from temp;
        1
        --------------------
        1
        2
        NULL

        3 rows selected
        – the return datatype of coalesce would be integer
        ij> ij> select coalesce (smallintcol, intcol) from temp;
        1
        -----------
        1
        NULL
        3

        As for compatible datatypes and the datatype of resultant of the coalesce function, please check the comments at the top of org.apache.derby.impl.sql.compile.CoalesceFunctionNode.java, IMHO, we should include this somewhere in our doc so the users knwo what to expect out of various datatype combination in a coalesce function.

        Show
        Mamta A. Satoor added a comment - Jeff, here are couple examples create table temp(smallintcol smallint, bigintcol bigint,intcol integer) insert into temp values (1,null,null) insert into temp values (null,2,null) insert into temp values (null,null,3) – the return datatype of coalesce would be bigint select coalesce (smallintcol, bigintcol) from temp; 1 -------------------- 1 2 NULL 3 rows selected – the return datatype of coalesce would be integer ij> ij> select coalesce (smallintcol, intcol) from temp; 1 ----------- 1 NULL 3 As for compatible datatypes and the datatype of resultant of the coalesce function, please check the comments at the top of org.apache.derby.impl.sql.compile.CoalesceFunctionNode.java, IMHO, we should include this somewhere in our doc so the users knwo what to expect out of various datatype combination in a coalesce function.
        Hide
        Matt Frantz added a comment -

        The proposed documentation does not allow for the possibility of invoking COALESCE with a single argument. Should it read as follows?

        COALESCE (expression[,expression]...)

        If the single-argument case is not supported, then maybe it should be. (This case is nice for SQL-generator applications.)

        Show
        Matt Frantz added a comment - The proposed documentation does not allow for the possibility of invoking COALESCE with a single argument. Should it read as follows? COALESCE (expression [,expression] ...) If the single-argument case is not supported, then maybe it should be. (This case is nice for SQL-generator applications.)
        Hide
        Christian d'Heureuse added a comment -

        COALESCE is an important function and should be documented.

        In ISO/IEC 9075, the syntax for COALESCE is
        COALESCE ( expression [,expression].... )
        but Derby currently does not allow COALESCE with a single argument.
        As long as this is not fixed, the syntax documentation should be
        COALESCE ( expression, expression [,expression].... )

        In ISO 9075-2, COALESCE is defined as:
        COALESCE (V1, V2) is equivalent to the following <case specification>:
        CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END
        COALESCE (V1, V2, ..., Vn), for n ? 3, is equivalent to the following <case specification>:
        CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END
        This could be used for the documentation.

        I wouldn't document that "VALUE is another name for the COALESCE function" in Derby, because thats an "inheritance" from DB2 and not part of the SQL standard.

        Show
        Christian d'Heureuse added a comment - COALESCE is an important function and should be documented. In ISO/IEC 9075, the syntax for COALESCE is COALESCE ( expression [,expression] .... ) but Derby currently does not allow COALESCE with a single argument. As long as this is not fixed, the syntax documentation should be COALESCE ( expression, expression [,expression] .... ) In ISO 9075-2, COALESCE is defined as: COALESCE (V1, V2) is equivalent to the following <case specification>: CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END COALESCE (V1, V2, ..., Vn), for n ? 3, is equivalent to the following <case specification>: CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END This could be used for the documentation. I wouldn't document that "VALUE is another name for the COALESCE function" in Derby, because thats an "inheritance" from DB2 and not part of the SQL standard.
        Hide
        Daniel John Debrunner added a comment -

        If VALUE is a synonym for the COALESCE function then it should be documented, as Derby's documentation should represent its full set of features.

        Show
        Daniel John Debrunner added a comment - If VALUE is a synonym for the COALESCE function then it should be documented, as Derby's documentation should represent its full set of features.
        Hide
        Christian d'Heureuse added a comment -

        > If VALUE is a synonym for the COALESCE function then it should be documented,

        But VALUE is such a general keyword and may be needed for other purposes in the future. It would be a pity to waste this keyword as an alias for COALESCE.

        Show
        Christian d'Heureuse added a comment - > If VALUE is a synonym for the COALESCE function then it should be documented, But VALUE is such a general keyword and may be needed for other purposes in the future. It would be a pity to waste this keyword as an alias for COALESCE.
        Hide
        Kim Haase added a comment -

        Dag Wanvik has answered the questions that seemed to be holding up the resolution of this issue.

        > > Neither COALESCE nor VALUE is documented currently (except for a
        > > mention in an error message).
        > >
        > > Looking at DERBY-495, it appears that the doc effort stalled for lack
        > > of answers to a couple questions –
        > >
        > > I gather VALUE as a synonym of COALESCE is not in the SQL standard,
        > > but COALESCE itself is? Should we mention VALUE, then?

        Yes, since it is implemented we should document it as a synonym, but
        probably recommend the standard form COALESCE.

        > >
        > > What does the SQL standard say about the one-argument form? Is it
        > > permitted?

        The standard does not allow the one-argument form:

        SQL 2003, vol 2 section 6.1 <case expression>:
        :
        <case abbreviation> ::=
        NULLIF <left paren> <value expression> <comma> <value expression> <right paren>

        COALESCE <left paren> <value expression> { <comma> <value expression> }

        ... <right paren>

        This means that 2 or more args are allowed.
        Derby does not permit one argument; it throws 42605. Also, not all arguments
        can be dynamic , if so Derby throws 42610.

        Show
        Kim Haase added a comment - Dag Wanvik has answered the questions that seemed to be holding up the resolution of this issue. > > Neither COALESCE nor VALUE is documented currently (except for a > > mention in an error message). > > > > Looking at DERBY-495 , it appears that the doc effort stalled for lack > > of answers to a couple questions – > > > > I gather VALUE as a synonym of COALESCE is not in the SQL standard, > > but COALESCE itself is? Should we mention VALUE, then? Yes, since it is implemented we should document it as a synonym, but probably recommend the standard form COALESCE. > > > > What does the SQL standard say about the one-argument form? Is it > > permitted? The standard does not allow the one-argument form: SQL 2003, vol 2 section 6.1 <case expression>: : <case abbreviation> ::= NULLIF <left paren> <value expression> <comma> <value expression> <right paren> COALESCE <left paren> <value expression> { <comma> <value expression> } ... <right paren> This means that 2 or more args are allowed. Derby does not permit one argument; it throws 42605. Also, not all arguments can be dynamic , if so Derby throws 42610.
        Hide
        Kim Haase added a comment -

        Attaching DERBY-495.diff and rreffunccoalesce.html, adding a topic documenting the COALESCE function. Please let me know if any changes are needed.

        A src/ref/rreffunccoalesce.dita
        M src/ref/refderby.ditamap

        Show
        Kim Haase added a comment - Attaching DERBY-495 .diff and rreffunccoalesce.html, adding a topic documenting the COALESCE function. Please let me know if any changes are needed. A src/ref/rreffunccoalesce.dita M src/ref/refderby.ditamap
        Hide
        Dag H. Wanvik added a comment -

        Thanks for the patch, Kim! Some small comments:

        > COALESCE ( expression, expression[, expression]... )
        ^ add a space before left bracket for legibility

        Prefer * repetition operator in the grammar production. The manual is
        schizophrenic on whether to use * or ..., though. But * dominates, I
        counted 25 occurences vs. 2-3.

        COALESCE ( expression, expression [, expression]* )

        Maybe it would be nice to have the second example take three args? E.g.

        ij> select coalesce (smallintcol, bigintcol, intcol) from temp;
        1
        --------------------
        1
        2
        3

        Show
        Dag H. Wanvik added a comment - Thanks for the patch, Kim! Some small comments: > COALESCE ( expression, expression [, expression] ... ) ^ add a space before left bracket for legibility Prefer * repetition operator in the grammar production. The manual is schizophrenic on whether to use * or ..., though. But * dominates, I counted 25 occurences vs. 2-3. COALESCE ( expression, expression [, expression] * ) Maybe it would be nice to have the second example take three args? E.g. ij> select coalesce (smallintcol, bigintcol, intcol) from temp; 1 -------------------- 1 2 3
        Hide
        Kim Haase added a comment -

        Thanks, Dag, for checking so carefully for consistency with other syntax formats. I've followed your suggestions, I hope. I'm attaching DERBY-495-2.diff and a new version of the HTML file.

        I wasn't sure exactly which example output you wanted replaced, so I just added the new one. The more examples the better?

        Show
        Kim Haase added a comment - Thanks, Dag, for checking so carefully for consistency with other syntax formats. I've followed your suggestions, I hope. I'm attaching DERBY-495 -2.diff and a new version of the HTML file. I wasn't sure exactly which example output you wanted replaced, so I just added the new one. The more examples the better?
        Hide
        Dag H. Wanvik added a comment -

        Thanks, Kim! +1

        Minor nit: Example header might be nice, but maybe we don't usually use a header for examples? Could we omit the "rows inserted", "rows selected" info to make the important lines stand out better? I don't think the manual usually shows real "ij" sessions in the examples, but I think that's fine, however it gets a bit
        "crowded" in the examples. A blank line between the examples might also help legibility a bit. Your call.

        Show
        Dag H. Wanvik added a comment - Thanks, Kim! +1 Minor nit: Example header might be nice, but maybe we don't usually use a header for examples? Could we omit the "rows inserted", "rows selected" info to make the important lines stand out better? I don't think the manual usually shows real "ij" sessions in the examples, but I think that's fine, however it gets a bit "crowded" in the examples. A blank line between the examples might also help legibility a bit. Your call.
        Hide
        Kim Haase added a comment -

        Thanks, Dag!

        Most of the function topics don't use a title for examples, but it's a good idea, so I've added one. To keep the ij output more or less realistic, I kept the "rows inserted" etc. output in, but I did put spaces between parts of the example for legibility.

        I'll commit the fix with these changes.

        Show
        Kim Haase added a comment - Thanks, Dag! Most of the function topics don't use a title for examples, but it's a good idea, so I've added one. To keep the ij output more or less realistic, I kept the "rows inserted" etc. output in, but I did put spaces between parts of the example for legibility. I'll commit the fix with these changes.
        Hide
        Kim Haase added a comment -

        Final patch for commit.

        Show
        Kim Haase added a comment - Final patch for commit.
        Hide
        Kim Haase added a comment -

        Committed patch DERBY-495-3.diff to documentation trunk at revision 811109.

        Show
        Kim Haase added a comment - Committed patch DERBY-495 -3.diff to documentation trunk at revision 811109.
        Hide
        Kim Haase added a comment -

        New topic has appeared in Latest Alpha Manuals, so closing.

        Show
        Kim Haase added a comment - New topic has appeared in Latest Alpha Manuals, so closing.

          People

          • Assignee:
            Kim Haase
            Reporter:
            Paul Jenkins
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development