Derby
  1. Derby
  2. DERBY-5557

NULLIF topic in reference manual should explain NULLIF

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.8.2.2
    • Fix Version/s: 10.9.1.0
    • Component/s: Documentation
    • Labels:
      None

      Description

      The section describing NULLIF in the reference manual is a bit cryptic. http://db.apache.org/derby/docs/10.8/ref/rrefcasenullif.html

      It says that NULLIF is used for conditional expressions, and that it is similar to CASE, but it doesn't say in plain words how it works. It would be good if it could mention that NULLIF returns NULL if the two arguments are equal, and that it returns the first argument if they are not equal.

      1. DERBY-5557.diff
        0.7 kB
        Kim Haase
      2. DERBY-5557-2.diff
        6 kB
        Kim Haase
      3. DERBY-5557-2.stat
        0.1 kB
        Kim Haase
      4. DERBY-5557-2.zip
        11 kB
        Kim Haase
      5. rrefcasenullif.html
        3 kB
        Kim Haase

        Activity

        Hide
        Kim Haase added a comment -

        Changes have appeared in Latest Alpha Manuals.

        Show
        Kim Haase added a comment - Changes have appeared in Latest Alpha Manuals.
        Hide
        Kim Haase added a comment -

        Thanks, Knut! Haven't seen a commit email after several hours, but maybe it's just me.

        Committed patch DERBY-5557-2.diff to documentation trunk at revision 1325247.

        Show
        Kim Haase added a comment - Thanks, Knut! Haven't seen a commit email after several hours, but maybe it's just me. Committed patch DERBY-5557 -2.diff to documentation trunk at revision 1325247.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks, Kim. The changes look good to me. +1

        Show
        Knut Anders Hatlen added a comment - Thanks, Kim. The changes look good to me. +1
        Hide
        Kim Haase added a comment -

        Attaching DERBY-5557-2.diff, DERBY-5557-2.stat, and DERBY-5557-2.zip, with the following changed files:

        M src/ref/rrefcase.dita
        M src/ref/rrefcasenullif.dita
        M src/ref/rrefsqlj19433.dita
        M src/ref/refderby.ditamap

        I created reltable entries in the map file for the three conditional expression topics so that they cross-reference each other, and included the COALESCE topic output in the zip so you can see that it too contains the reference.

        Thanks in advance!

        Show
        Kim Haase added a comment - Attaching DERBY-5557 -2.diff, DERBY-5557 -2.stat, and DERBY-5557 -2.zip, with the following changed files: M src/ref/rrefcase.dita M src/ref/rrefcasenullif.dita M src/ref/rrefsqlj19433.dita M src/ref/refderby.ditamap I created reltable entries in the map file for the three conditional expression topics so that they cross-reference each other, and included the COALESCE topic output in the zip so you can see that it too contains the reference. Thanks in advance!
        Hide
        Knut Anders Hatlen added a comment -

        Sounds like a good plan. I think it's fine to mention all three in the table under conditional expression.

        Show
        Knut Anders Hatlen added a comment - Sounds like a good plan. I think it's fine to mention all three in the table under conditional expression.
        Hide
        Kim Haase added a comment -

        Thanks for the advice, Dag and Knut!

        I think it would be a good idea to provide a second patch instead of committing this one. The additional changes would do the following:

        1) call NULLIF a function rather than an expression
        2) move the CASE expression topic under "SQL expressions" (not sure where; possibly after "Boolean expressions"?)
        3) modify the "SQL expressions" topic (http://db.apache.org/derby/docs/dev/ref/rrefsqlj19433.html) to add a mention of CASE expressions to the "Conditional expression" row of the first table. As far as I can tell, conditional expressions include only the CASE expression and the NULLIF and COALESCE functions, so perhaps all three should be mentioned?

        I'll work on this – if you have more suggestions, or answers to my questions, please go ahead!

        Show
        Kim Haase added a comment - Thanks for the advice, Dag and Knut! I think it would be a good idea to provide a second patch instead of committing this one. The additional changes would do the following: 1) call NULLIF a function rather than an expression 2) move the CASE expression topic under "SQL expressions" (not sure where; possibly after "Boolean expressions"?) 3) modify the "SQL expressions" topic ( http://db.apache.org/derby/docs/dev/ref/rrefsqlj19433.html ) to add a mention of CASE expressions to the "Conditional expression" row of the first table. As far as I can tell, conditional expressions include only the CASE expression and the NULLIF and COALESCE functions, so perhaps all three should be mentioned? I'll work on this – if you have more suggestions, or answers to my questions, please go ahead!
        Hide
        Knut Anders Hatlen added a comment -

        The patch looks good to me. +1 to commit.

        Show
        Knut Anders Hatlen added a comment - The patch looks good to me. +1 to commit.
        Hide
        Knut Anders Hatlen added a comment -

        I agree with Dag that it's probably OK to call COALESCE and NULLIF functions. CASE, on the other hand, isn't a function, so it might make sense to move "CASE expression" to the "SQL expressions" topic.

        Show
        Knut Anders Hatlen added a comment - I agree with Dag that it's probably OK to call COALESCE and NULLIF functions. CASE, on the other hand, isn't a function, so it might make sense to move "CASE expression" to the "SQL expressions" topic.
        Hide
        Dag H. Wanvik added a comment -

        In the standard, both COALESCE and NULLIF are called "case abbreviations". Neither "function" nor "expression" is used for these two builtins.
        Syntactically, they are siblings, cf. this quote from SQL 2003:

        <case abbreviation> ::=
        NULLIF <left paren> <value expression> <comma> <value expression> <right paren>

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

        ... <right paren>

        Both have parentheses around their arguments, so it is natural to think of them as "functions", I guess. If we choose to call COALESCE a function, we should use that term for NULLIF, too.

        Show
        Dag H. Wanvik added a comment - In the standard, both COALESCE and NULLIF are called "case abbreviations". Neither "function" nor "expression" is used for these two builtins. Syntactically, they are siblings, cf. this quote from SQL 2003: <case abbreviation> ::= NULLIF <left paren> <value expression> <comma> <value expression> <right paren> COALESCE <left paren> <value expression> { <comma> <value expression> } ... <right paren> Both have parentheses around their arguments, so it is natural to think of them as "functions", I guess. If we choose to call COALESCE a function, we should use that term for NULLIF, too.
        Hide
        Kim Haase added a comment -

        Thanks for noticing this problem, Knut. I'm attaching DERBY-5557.diff and rrefcasenullif.html, with your suggested modification to the NULLIF topic.

        Not to open a can of worms here, but I think that in SQL, NULLIF really is a function rather than an expression. Should I retitle it?

        CASE is an expression, though.

        Neither CASE nor NULLIF is listed in either the "Standard built-in functions" or the "SQL expressions" topic. Is this correct?

        Show
        Kim Haase added a comment - Thanks for noticing this problem, Knut. I'm attaching DERBY-5557 .diff and rrefcasenullif.html, with your suggested modification to the NULLIF topic. Not to open a can of worms here, but I think that in SQL, NULLIF really is a function rather than an expression. Should I retitle it? CASE is an expression, though. Neither CASE nor NULLIF is listed in either the "Standard built-in functions" or the "SQL expressions" topic. Is this correct?

          People

          • Assignee:
            Kim Haase
            Reporter:
            Knut Anders Hatlen
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development