SQL Server CROSS APPLY никогда не заканчивается ссылкой на другую таблицу

Question

Я пытаюсь использовать эту функцию ExplodeDate.

Но я не могу запустить его одним простым запросом. Я могу воспроизвести это с пустой базой данных на SQL Server 2016 локально и в базе данных Azure.

Пожалуйста, используйте следующий код, чтобы воспроизвести проблему.

Код для используемых функций:

/*
    FUNCTION:   [dbo].[ExplodeDates]
*/
DROP FUNCTION IF EXISTS [dbo].[ExplodeDates]
GO
CREATE FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startdate) as [Date]
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1
);
GO


/*
    FUNCTION:   [dbo].[ExplodeDatesByPeriod]
*/
DROP FUNCTION IF EXISTS [dbo].[ExplodeDatesByPeriod]
GO
CREATE FUNCTION [dbo].[ExplodeDatesByPeriod]
(
    @StartDate DATE = '20130101', 
    @EndDate DATE ='20301231',
    @ImpactPeriod INT = 4       -- 4 Jahr, 3 Quartal, 2 Monat, 1 Woche, 0 Tag  - siehe ImpactPeriod
)
RETURNS TABLE
AS
RETURN(
    WITH CTE 
    AS
    (
        SELECT 
                  [Date]
                , CASE 
                        WHEN @ImpactPeriod = 0 THEN DATEPART(YEAR, [Date])
                        -- MORE WHEN CASES HERE ....
                        ELSE Year([Date])
                    END AS [Year]
                , CASE 
                        WHEN @ImpactPeriod = 0 THEN DATEPART(DAYOFYEAR, [Date])
                        -- MORE WHEN CASES HERE ....
                        ELSE Year([Date])
                    END AS [Period]
            FROM [dbo].[ExplodeDates](CASE 
                                            WHEN @ImpactPeriod = 0 THEN @StartDate
                                            -- MORE WHEN CASES HERE ....
                                            ELSE DATEFROMPARTS(YEAR(@StartDate), 1, 1)
                                        END, @EndDate)  
    )
    SELECT 
                MIN([Date])  AS [StartOfPeriod]
            , CASE 
                    WHEN @ImpactPeriod = 0 THEN MIN([Date])
                    -- MORE WHEN CASES HERE ....
                    ELSE DATEFROMPARTS(DATEPART(YEAR, MIN([Date])), 12, 31)
                END AS [EndOfPeriod]
            , ([Year] * ( CASE 
                            WHEN @ImpactPeriod = 0 THEN 1000
                            -- MORE WHEN CASES HERE ....
                            ELSE 0
                        END)
                ) + [Period] AS [YearPeriod]
            , [Year]
            , [Period]
        FROM [CTE]
        GROUP BY                
                [Year]
            , [Period]

)
GO

Тогда, если вы запустите следующий код, он никогда не закончится:

/*
    TABLE WITH DUMMY DATA
*/
DROP TABLE IF EXISTS [T1]
CREATE TABLE [T1](
    [StartDate] DATE,
    [EndDate] DATE
)
INSERT INTO [T1] VALUES ('20190101', '20190101')
GO

/*
    FUNCTION CALL TO:   [dbo].[ExplodeDates]  DOESN'T WORK!!
*/
SELECT * FROM [T1] CROSS APPLY [dbo].[ExplodeDates]([T1].[StartDate], [T1].[EndDate]) [D]
GO

Интересно, что следующий запрос выполняется немедленно:

/*
    FUNCTION CALL TO:   [dbo].[ExplodeDatesByPeriod]  THIS WORKS!!
                        PARAM 0 for Days
*/
SELECT * FROM [T1] CROSS APPLY [dbo].[ExplodeDatesByPeriod]([T1].[StartDate], [T1].[EndDate], 0) [D]
GO

[dbo]. [ExplodeDatesByPeriod] вызывает [dbo].[ExplodeDates] внутренне!