Named Sets, AutoExists and Katmai

A couple of months ago Radim Hampel pointed out to me some very weird stuff happening with named sets and the Where clause. Since it turned out that Darren had run into the same issue and also been thrown by it, and since I tested it out on Katmai CTP6 and could see that it was behaving differently from AS2005, I opened an item on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331186

And now, after a long and detailed email thread involving Mosha, Edward Melomed, Marius Dumitru, Darren and Deepak we’ve got to the stage where I understand what’s going on, Katmai RC0 does roughly what I want, and I can blog about it!

Let me explain what I saw first. What would you expect the calculated member in following query to return?

–Query 1
WITH
  SET mytestset AS
    PeriodsToDate
    (
      [Date].[Calendar].[Calendar Year]
     ,[Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

Just from looking at the code I would have set the calculated member should return the string representation of the set from January 2004 to June 2004. But if you run the query you will in fact see that it returns the set containing the member July 2004 on both AS2005 and Katmai. To me that made absolutely no sense… Now, take a look at this query:

–Query 2
WITH
  SET mytestset AS
    [Date].[Calendar].[Month].&[2004]&[6]
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

On AS2005 the calculation returns June as I would expect, on Katmai it returns an empty set. Now run this query:

–Query 3
WITH
  SET mytestset AS
    {
      [Date].[Calendar].[Month].&[2004]&[5]
     ,[Date].[Calendar].[Month].&[2004]&[6]
    }
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

and this query:

–Query 4
WITH
  SET mytestset AS
    (
      [Date].[Calendar].[Month].&[2004]&[5]
     :
      [Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM [adventure works]
WHERE
  [Date].[Calendar].[Month].&[2004]&[7]

…which to me should do the same thing. On AS2005 query 3 returns the set May and June but Katmai returns an empty set; query 4 returns an empty set on both AS2005 and Katmai. At this point I could see that something funny was happening that I didn’t like!

What are the practical implications of this? Take the following query from Mosha’s blog entry on ranking:

WITH
  SET OrderedEmployees AS
    Order
    (
      [Employee].[Employee].[Employee].MEMBERS
     ,[Measures].[Reseller Sales Amount]
     ,BDESC
    )
  MEMBER [Measures].[Employee Rank] AS
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,OrderedEmployees
    )
SELECT
  [Measures].[Employee Rank] ON 0
,[Employee].[Employee].[Employee].MEMBERS ON 1
FROM [Adventure Works]

Run it and you’ll see that the Employee with the key 46, A Scott Wright, has a rank of 18. Now let’s slice by this Employee:

WITH
  SET orderedemployees AS
    Order
    (
      [Employee].[Employee].[Employee].MEMBERS
     ,[Measures].[Reseller Sales Amount]
     ,bdesc
    )
  MEMBER measures.[employee rank] AS
    Rank
    (
      [Employee].[Employee].CurrentMember
     ,orderedemployees
    )
SELECT
  measures.[employee rank] ON 0
FROM [adventure works]
WHERE
  [Employee].[Employee].&[46]

Run this and you’ll see that A Scott Wright has now supposedly got a rank of 1. Whatever the logic behind this, it doesn’t make sense from an end user perspective does it?

So how can we explain what’s happening here? It’s all to do with autoexists: in some cases it makes sense to apply autoexists to named sets, but in others (mostly when the set is intended for use in a calculation) then it doesn’t. Let’s forget about trying to understand what AS2005 does because it tries to guess when it should apply autoexists and gets very confused, but Katmai is mostly consistent and logical: by default it applies autoexists to all named sets. That explains why queries 2,3 and 4 all return empty sets on Katmai: May and June don’t exist with July.

After my initial item on Connect was opened those nice people in Redmond (who agreed with me that the way things were working wasn’t ideal) added a new connection string property, Autoexists, which can have the following values:

0 – default (same as 1)
1 – Apply deep autoexists for query axes and named sets (with WHERE clause and subselects)
2 – Apply deep autoexists for query axes and no autoexists for named sets (with WHERE clause and subselects)
3 – Apply shallow autoexists for query axes with WHERE clause, deep autoexists for query axes with subselects, no autoexists for named sets with WHERE clause and deep autoexists for named sets with subselects

Here’s the explanation I got from Marius about what’s meant by ‘deep’ and ‘shallow’ autoexists:

Suppose a query axis or named set involves a set expression of the form F(G(s)), with F and G being set functions (e.g. TopCount, Tail etc.)
Let SSW denote the Where clause slice and Subselect restrictions present in the query.
“Shallow autoexists” evaluates the set expression as Exists(F(G(s)), SSW).
“Deep autoexists” evaluates the set expression as Exists( F( Exists( G( Exists(s, SSW) ), SSW ), SSW) – it applies autoexists with the Where and Subselects at every intermediary step. Deep autoexists applies to all set-valued subexpressions being evaluated in the root context of the query (i.e. the context of default member coordinates, or Where clause coordinates, if a Where clause is present).
For many/most set functions (e.g. Union), the rules above produce the same result.
For others (e.g. TopCount, Head/Tail), the results differ in the general case.

So the behaviour I describe above for RC0 is also what you get when you put Autoexists=1 in the connection string. But what about Query 1 – why does the PeriodsToDate function return July with this setting? Hmm, well I think this is a bug and it should return an empty set. I opened another Connect about this:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356193
and I’ll update this post when I get an answer on it.

What happens with Autoexists=2 and Autoexists=3 then? In both cases, Query 1 returns the set of months from January to June; Query 2 returns June, Query 3 and Query 4 both return May and June. To see the difference between these two settings take a look at this query:

WITH
  SET mytestset AS
    (
      [Date].[Calendar].[Month].&[2004]&[5]
     :
      [Date].[Calendar].[Month].&[2004]&[6]
    )
  MEMBER measures.test AS
    SetToStr(mytestset)
SELECT
  measures.test ON 0
FROM (SELECT
  [Date].[Calendar].[Month].&[2004]&[7] ON 0
FROM [adventure works] )

With Autoexists=1 and Autoexists=3 the set here is empty; with Autoexists=2 then it contains May and June.