title: Unexpected analytic function output in common table expression
body: <p>In SQL Server 2019, analytic functions are not returning the results that I would expect in the context of recursive common table expressions. Consider the following non-recursive T-SQL query:</p>
  <pre><code>WITH SourceData (RowNum, Uniform, RowVal) AS (
    SELECT 1, 'A', 'A' UNION ALL
    SELECT 2, 'A', 'B' UNION ALL
    SELECT 3, 'A', 'C' UNION ALL
    SELECT 4, 'A', 'D'
  ),
  RecursiveCte0 (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
    SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0
    FROM SourceData
  ),
  RecursiveCte1 (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
    SELECT * FROM RecursiveCte0
    UNION ALL
    SELECT
      RowNum, Uniform, RowVal,
      MIN(MinVal) OVER (PARTITION BY Uniform),
      SUM(RowNum) OVER (PARTITION BY Uniform),
      ROW_NUMBER() OVER (PARTITION BY Uniform ORDER BY RowNum),
      RecursiveLevel + 1
    FROM RecursiveCte0
  )
  SELECT *
  FROM RecursiveCte1
  ORDER BY RecursiveLevel, RowNum;
  </code></pre>
  <p>Results:</p>
  <pre><code>RowNum Uniform RowVal MinVal SomeSum RowNumCalc RecursiveLevel
  1      A       A      A      1       1          0
  2      A       B      B      2       2          0
  3      A       C      C      3       3          0
  4      A       D      D      4       4          0
  1      A       A      A      10      1          1
  2      A       B      A      10      2          1
  3      A       C      A      10      3          1
  4      A       D      A      10      4          1
  </code></pre>
  <p>As expected, the <code>MIN</code>, <code>SUM</code>, and <code>ROW_NUMBER</code> functions generate the appropriate values based on all rows from <code>RecursiveCte0</code>. I would expect the following recursive query to be logically identical to the non-recursive version above, but it produces different results:</p>
  <pre><code>WITH SourceData (RowNum, Uniform, RowVal) AS (
    SELECT 1, 'A', 'A' UNION ALL
    SELECT 2, 'A', 'B' UNION ALL
    SELECT 3, 'A', 'C' UNION ALL
    SELECT 4, 'A', 'D'
  ),
  RecursiveCte (RowNum, Uniform, RowVal, MinVal, SomeSum, RowNumCalc, RecursiveLevel) AS (
    SELECT RowNum, Uniform, RowVal, RowVal, RowNum, CAST(RowNum AS BIGINT), 0
    FROM SourceData
    UNION ALL
    SELECT
      RowNum, Uniform, RowVal,
      MIN(MinVal) OVER (PARTITION BY Uniform),
      SUM(RowNum) OVER (PARTITION BY Uniform),
      ROW_NUMBER() OVER (PARTITION BY Uniform ORDER BY RowNum),
      RecursiveLevel + 1
    FROM RecursiveCte
    WHERE RecursiveLevel &lt; 1
  )
  SELECT *
  FROM RecursiveCte
  ORDER BY RecursiveLevel, RowNum;
  </code></pre>
  <p>Results:</p>
  <pre><code>RowNum Uniform RowVal MinVal SomeSum RowNumCalc RecursiveLevel
  1      A       A      A      1       1          0
  2      A       B      B      2       2          0
  3      A       C      C      3       3          0
  4      A       D      D      4       4          0
  1      A       A      A      1       1          1
  2      A       B      B      2       1          1
  3      A       C      C      3       1          1
  4      A       D      D      4       1          1
  </code></pre>
  <p>For each of the three analytic functions, it appears that the grouping is only being applied within the context of each individual row, rather than all of the rows at that level. This unexpected behavior also happens if I partition over <code>(SELECT NULL)</code>. I would expect the analytic functions to apply to the entire recursion level, as per <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql" rel="nofollow noreferrer">MSDN</a>:</p>
  <blockquote>
  <p>Analytic and aggregate functions in the recursive part of the CTE are
  applied to the set for the current recursion level and not to the set
  for the CTE. Functions like <code>ROW_NUMBER</code> operate only on the subset of
  data passed to them by the current recursion level and not the entire
  set of data passed to the recursive part of the CTE.</p>
  </blockquote>
  <p>Why do these two queries produce different results? Is there a way to effectively use analytic functions with recursive common table expressions?</p>