Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Severe Severe
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: None
    • Labels:
      None

      Description

      The IS EMPTY and IS NULL operators are not implemented.
      Mondrian already has the IsEmpty(<expr>) operator, but
      its behavior is probably not correct.

      First, we need to implement IS EMPTY and IS NULL operators.

      Then, we need to take Michael's examples below, turn
      them into testcases, and make the testcases work.

      ==================================
      Michael Bienstein wrote:

      Julian,

      On NULL vs EMPTY, my take after reading William
      Pearson's articles on databasejournal.com was that NULL
      is for metadata objects and EMPTY is for a cell value.
      E.g. "[Time].[All Time].Parent IS NULL" will evaluate
      to true.

      Any functions that return a member from parameters that
      include a member and that member is NULL also give a
      NULL.
      E.g. "[Time].CurrentMember.Parent.NextMember" when
      applied to [Time].[All Time] will return NULL as well
      and will NOT cause a run time exception.
       
      When resolving a tuple's value in the cube, if there is
      at least one NULL member in the tuple should return a
      NULL cell value.
      E.g. "([Time].currentMember.Parent,[Measures].[Sales])"
      when resolved in the context where [Time].CurrentMember
      is [Time].[All Time] should resolve to NULL as a value.

      EMPTY refers to a genuine cell value that exists in the
      cube space but that has no value in the underlying data
      source.
      E.g. "([Product].[All Products].[Ski Boots],
      [Geography].[All Geography].[Hawaii])" has no NULL
      members in the tuple but has no fact data at that
      crossing, so it evaluates to EMPTY as a cell value.

      The empty set is neither EMPTY nor NULL.
      E.g.
      "WITH SET [empty set] AS '{}'
      WITH MEMBER [Measures].[Set Size] AS 'Count([empty set])'
      SELECT [Measures].[Set Size] on columns"
      should give 0 as a result, not NULL and not EMPTY.

      Run time errors are BAD things. They should not occur
      in almost all cases. In fact there should be no
      logically formed MDX that generates them. An ERROR
      value in a cell though is perfectly legal - e.g. a
      divide by 0.
      E.g.
      "WITH [Measures].[Ratio This Period to Previous] as
      '([Measures].[Sales],[Time].CurrentMember/([Measures].[Sales],[Time].CurrentMember.PrevMember)'
      SELECT [Measures].[Ratio This Period to Previous] ON
      COLUMNS,
      [Time].Members ON ROWS
      FROM ..."
      For the [Time].[All Time] row as well as the first
      year, first month etc, the PrevMember will evaluate to
      NULL, the tuple will evaluate to NULL and the division
      will implicitly convert the NULL to 0 and then evaluate
      to an ERROR value due to a divide by 0.

      This leads to another point: NULL and EMPTY values get
      implicitly converted to 0 when treated as numeric
      values for division and multiplication but for addition
      and subtraction, NULL is treated as NULL (5+NULL yields
      NULL).
      I have no idea about how EMPTY works. I.e. is does
      5+EMPTY yield 5 or EMPTY or NULL or what?
      E.g.
      "WITH MEMBER [Measures].[5 plus empty] AS
      '5+([Product].[All Products].[Ski
      boots],[Geography].[All Geography].[Hawaii])'
      SELECT [Measures].[5 plus empty] ON COLUMNS
      FROM ..."
      Does this yield EMPTY, 5, NULL or ERROR?

      Lastly, IS NULL and IS EMPTY are both legal and
      distinct. <<Object>> IS {<<Object>> | NULL} and
      <<Value>> IS EMPTY.
      E.g.
      a) [Time].CurrentMember.Parent IS [Time].[Year].[2004]
      is also a perfectly legal expression and better than
      [Time].CurrentMember.Parent.Name="2004".
      b) ([Measures].[Sales],[Time].FirstSibling) IS EMPTY is
      a legal expression.


      Michael

        Activity

        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 09/04/2006: priority, 5 |
        {jhyde}, 09/04/2006: assigned_to, 100 |
        {mkambol}, 11/09/2006: status_id, 1 |
        {mkambol}, 11/09/2006: close_date, 0 |
        {jhyde}, 11/13/2006: status_id, 2 |
        {jhyde}, 11/13/2006: priority, 7 |
        {jhyde}, 11/13/2006: assigned_to, 1479052 |
        {jhyde}, 11/13/2006: close_date, 1163102213
        Show
        Mondrian Importer User added a comment - {jhyde}, 09/04/2006: priority, 5 | {jhyde}, 09/04/2006: assigned_to, 100 | {mkambol}, 11/09/2006: status_id, 1 | {mkambol}, 11/09/2006: close_date, 0 | {jhyde}, 11/13/2006: status_id, 2 | {jhyde}, 11/13/2006: priority, 7 | {jhyde}, 11/13/2006: assigned_to, 1479052 | {jhyde}, 11/13/2006: close_date, 1163102213
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 09/04/2006: Logged In: YES
        user_id=312935

        Matt, I believe that this functionality is implemented: you
        implemented IS NULL, and I implemented IS EMPTY. Can you add
        the test cases and close the bug.
        Show
        Mondrian Importer User added a comment - {jhyde}, 09/04/2006: Logged In: YES user_id=312935 Matt, I believe that this functionality is implemented: you implemented IS NULL, and I implemented IS EMPTY. Can you add the test cases and close the bug.
        Hide
        Mondrian Importer User added a comment -
        {jhyde}, 11/13/2006: Logged In: YES
        user_id=312935

        I am reopening this bug. I added Michael's testcases and
        there are some problems.

        1. IS EMPTY is not implemented.

        Michael, Where did you dig up the IS EMPTY operator? It's
        not in MSAS 2005. I'll add it if you can show me evidence
        that it is in 'standard MDX' (whatever that is). Otherwise
        we'll stick with IsEmpty().

        2. IS NULL precedence is wrong.

        Matt's fix parses the postfix 'IS NULL' operator as if it
        were a call to the infix 'IS' operator, but this is flawed.
        For example, '[Measures].[Unit Sales] IS NULL + 5' should
        parse to '([Measures].[Unit Sales] IS NULL) + 5'. 'IS NULL'
        has higher precedence than '+', whereas 'IS' has lower.
        Granted, it's hard to add both operators without making the
        grammar ambiguous. I'll see what I can do.

        3. Empty cells have a value, EMPTY, which is distinct from
        0, can be combined with other values:

        # When the empty cell value is an operand for any one of
        the numeric operators (+, -, *, /), the empty cell value is
        treated as zero if the other operand is a nonempty value. If
        both operands are empty, the numeric operator returns the
        empty cell value.
        # When the empty cell value is an operand for the string
        concatenation operator (+), the empty cell value is treated
        as an empty string if the other operand is a nonempty value.
        If both operands are empty, the string concatenation
        operator returns the empty cell value.
        # When the empty cell value is an operand for any one of the
        comparison operators (=. <>, >=, <=, >, <), the empty cell
        value is treated as zero or an empty string, depending on
        whether the data type of the other operand is numeric or
        string, respectively. If both operands are empty, both
        operands are treated as zero.
        # When collating numeric values, the empty cell value
        collates in the same place as zero. Between the empty cell
        value and zero, empty collates before zero.
        # When collating string values, the empty cell value
        collates in the same place as the empty string. Between the
        empty cell value and the empty string, empty collates before
        an empty string.

        See "Working with empty values",
        http://msdn2.microsoft.com/en-us/library/ms145626.aspx
        Show
        Mondrian Importer User added a comment - {jhyde}, 11/13/2006: Logged In: YES user_id=312935 I am reopening this bug. I added Michael's testcases and there are some problems. 1. IS EMPTY is not implemented. Michael, Where did you dig up the IS EMPTY operator? It's not in MSAS 2005. I'll add it if you can show me evidence that it is in 'standard MDX' (whatever that is). Otherwise we'll stick with IsEmpty(). 2. IS NULL precedence is wrong. Matt's fix parses the postfix 'IS NULL' operator as if it were a call to the infix 'IS' operator, but this is flawed. For example, '[Measures].[Unit Sales] IS NULL + 5' should parse to '([Measures].[Unit Sales] IS NULL) + 5'. 'IS NULL' has higher precedence than '+', whereas 'IS' has lower. Granted, it's hard to add both operators without making the grammar ambiguous. I'll see what I can do. 3. Empty cells have a value, EMPTY, which is distinct from 0, can be combined with other values: # When the empty cell value is an operand for any one of the numeric operators (+, -, *, /), the empty cell value is treated as zero if the other operand is a nonempty value. If both operands are empty, the numeric operator returns the empty cell value. # When the empty cell value is an operand for the string concatenation operator (+), the empty cell value is treated as an empty string if the other operand is a nonempty value. If both operands are empty, the string concatenation operator returns the empty cell value. # When the empty cell value is an operand for any one of the comparison operators (=. <>, >=, <=, >, <), the empty cell value is treated as zero or an empty string, depending on whether the data type of the other operand is numeric or string, respectively. If both operands are empty, both operands are treated as zero. # When collating numeric values, the empty cell value collates in the same place as zero. Between the empty cell value and zero, empty collates before zero. # When collating string values, the empty cell value collates in the same place as the empty string. Between the empty cell value and the empty string, empty collates before an empty string. See "Working with empty values", http://msdn2.microsoft.com/en-us/library/ms145626.aspx
        Hide
        Will Gorman added a comment -
        These are unimplemented features, these aren't bugs.
        Show
        Will Gorman added a comment - These are unimplemented features, these aren't bugs.

          People

          • Assignee:
            Unassigned User
            Reporter:
            Julian Hyde
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: