Ошибки сервера Sql? Результат запроса не детерминирован, если группа по выражению?

У меня есть следующий запрос

with cte1 as (
    select isnull(A, 'Unknown') as A,
           isnull(nullif(B, 'NULL'), 'Unknown') as B,
           C
    from   ... -- uses collate SQL_Latin1_General_CP1_CI_AS when joining 
    group by isnull(A, 'Unknown'), isnull(nullif(B, 'NULL'), 'Unknown'), C
    ),
    cte2 as (select top (2147483647) A, B, C from cte1 order by A, B, C),
      -- Removing cte2 makes it work if running directly as SQL query. However, 
      -- it still behave the same if the code is in view or table function 
    ctes as (
    .... -- pretty complex query joining cte2 multiple times
         -- uses row_number(), ntile
    )
    select count(*) from finalCTE

Результат (количество) изменяется каждый раз при его выполнении. И это намного меньше, чем должно быть. Я нашел, что любой из следующих шагов может сделать это правильно.

• Составьте (временную или постоянную таблицу) CTE cte1 и используйте вместо этого материализованную таблицу.
• Измените группу на cte1 на любую следующих форм.
  • group by A, isnull(nullif(B, 'NULL'), 'Unknown'), C
  • group by isnull(A, 'Unknown'), nullif(B, 'NULL'), C
  • group by A, nullif(B, 'NULL'), C
  • Используйте cte1 вместо cte2 в других CTE. ( Обновление: Этот шаг не всегда работает. Тем не менее проблема связана с функцией таблицы, хотя она работает, если вы запускаете SQL напрямую)

Однако, почему исходный запрос ведет себя странно? Это ошибка в SQL Server?

Полный функциональный код:

ALTER function [dbo].[fn] (@para1 char(3))
returns table
return
with    cte1 as ( select AAA, BBB, CCC
               from     dbo.fnBBB(12)
               where    @para1 = 'xxxx'
               union all
               select   AAA, BBB, CCC
               from     dbo.fnBBB2(12)
               where    @para1 = 'yyyy'
             ),
        -- Tested not using cte2, the same behave
        cte2 as (select top (2147483647) AAA, BBB, CCC from cte1 order by AAA, BBB, CCC),
        t as ( select   e.CCC, e.value1, cte2.BBB, cte2.AAA
               from     dbo.T1 e
                        join cte2 on e.CCC = cte2.CCC
             ),
        b as ( select   BBB, AAA, count(*) count, 
                        case when count(*) / 5 > 10 then 10 
                             else count(*) / 5 
                        end as buckets
               from     t 
               group by BBB, AAA 
               having   count(*) >= 5 
             ),
        b2
          as ( select   t.*
               from     b
                        cross apply ( select    *,
                                                ntile(b.buckets) over ( partition by t.BBB, t.AAA order by value1, CCC )
                                                as bucket
                                      from      t
                                      where     BBB = b.BBB
                                                and AAA = b.AAA
                                    ) t
             ),
        m1
          as ( select   AAA, BBB, b2.CCC, Date, SId, value2, b2.bucket, --
                        _asc = row_number() over ( partition by BBB, AAA, bucket, Date, SId order by value2, b2.CCC ),
                        _desc = row_number() over ( partition by BBB, AAA, bucket, Date, SId order by value2 desc, b2.CCC desc )
                        ,count(*) over (partition by BBB, AAA, bucket, Date, SId) scount
               from     b2 join dbo.T2 e on b2.CCC = e.CCC
             ),
        median
          as ( select   BBB, AAA, bucket, Date, SId, avg(value2) value2Median, min(scount) sCount
               from     m1
               where    _asc in ( _desc, _desc - 1, _desc + 1 )
               group by BBB, AAA, bucket, Date, SId
             ),
        bounds
          as ( select   BBB, AAA, bucket, min(value1) dboMin, max(value1) value1Max, count(*) count 
               from     b2
               group by BBB, AAA, bucket 
             )
    select  m.*, b.dboMin, b.value1Max, Count
    from    median m join bounds b on m.BBB = b.BBB and m.AAA = b.AAA and m.bucket = b.bucket 
    -- order by BBB, AAA, bucket 

Функция, используемая в cte1:

CREATE function [dbo].[fnBBB](@param int) 
returns table
return
with    m as ( select   * -- only this view has non default collate (..._CS_AS)
               from     dbo.view1 -- indxed view. 
             )
    select  isnull(g.AAA, 'Unknown') as AAA,
            isnull(nullif(m1.value, 'NULL'), 'Unknown') as BBB
            , m.CCC
    from    m 
            left join dbo.mapping m0 on m0.id = 12
                and m0.value = m. v1 collate SQL_Latin1_General_CP1_CI_AS
            left join dbo.map1 r on r.Country = m0.value
            left join dbo.map2 g on g.N = r.N
            left join dbo.mapping m1 on m1.id = 20
                and m1.value = m.v2 collate SQL_Latin1_General_CP1_CI_AS
    where   m.run_date > dateadd(mm, [email protected], getdate())
    group by isnull(g.AAA, 'Unknown'), isnull(nullif(m1.value, 'NULL'), 'Unknown'), m.CCC

Ответы

AND ISNULL(<column name>,'''') LIKE ' +
CASE WHEN @customer  IS NOT NULL
    THEN '''' [email protected]  + ''''
ELSE 
    'ISNULL(c.<column_name> , '''')'
END