Details

    • Type: Improvement
    • Status: Open
    • Severity: Medium
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Not Planned
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Closer:
      nobody

      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

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated: