使用列而非字符串常量时SQL日期范围查询无限运行的问题

需要从CustomersHub表的最大firstloadedDate开始，生成到'2022-11-16'的日期范围。但使用临时表#Temp中的dt列作为起始日期时，查询会无限运行；将dt替换为相同值的常量后，查询能正常执行。

表定义 #

create table #Temp
(
    dt DateTime,
);

create table CustomersHub
(
    id int,
    firstloadedDate DateTime,
);

插入临时表语句 #

insert into #Temp
select top 1 hub.firstloadedDate max_date from CustomersHub hub order by max_date desc;

原查询语句 #

WITH e00(n)
     AS (SELECT 1
         UNION ALL
         SELECT 1),
     e02(n)
     AS (SELECT 1
         FROM   [e00] [a],
                [e00] [b]),
     e04(n)
     AS (SELECT 1
         FROM   [e02] [a],
                [e02] [b]),
     e08(n)
     AS (SELECT 1
         FROM   [e04] [a],
                [e04] [b]),
     e16(n)
     AS (SELECT 1
         FROM   [e08] [a],
                [e08] [b]),
     e32(n)
     AS (SELECT 1
         FROM   [e16] [a],
                [e16] [b]),
     num_tally(n)
     AS (SELECT Row_number()
                  OVER (
                    ORDER BY ( SELECT NULL) )
         FROM   [e32]),
     tally
     AS (SELECT Dateadd(day, n - 1, dt) dates,
                n,
                dt
         FROM   [num_tally],
                #temp
         WHERE  Datediff(day, dt, '2022-11-16') >= n)
SELECT *
FROM   tally

DROP TABLE #temp 

问题原因 #

原查询中，num_tally基于e32生成，e32包含2^32行数据（约40亿行）。当num_tally与#temp做笛卡尔积时，SQL Server查询优化器无法识别#temp仅包含一行数据，也无法提前计算Datediff(day, dt, '2022-11-16')的固定值，导致tally CTE持续生成数据，无法触发停止条件。而使用常量时，优化器能直接计算出差值，限制num_tally的行数，查询正常终止。

解决方案 #

方案1：使用变量存储起始日期 #

先将#Temp中的日期值赋值给变量，在CTE中使用变量替代列，让优化器能提前计算终止条件：

DECLARE @StartDate DATETIME
SELECT @StartDate = dt FROM #Temp

WITH e00(n)
     AS (SELECT 1
         UNION ALL
         SELECT 1),
     e02(n)
     AS (SELECT 1
         FROM   [e00] [a],
                [e00] [b]),
     e04(n)
     AS (SELECT 1
         FROM   [e02] [a],
                [e02] [b]),
     e08(n)
     AS (SELECT 1
         FROM   [e04] [a],
                [e04] [b]),
     e16(n)
     AS (SELECT 1
         FROM   [e08] [a],
                [e08] [b]),
     e32(n)
     AS (SELECT 1
         FROM   [e16] [a],
                [e16] [b]),
     num_tally(n)
     AS (SELECT TOP (DATEDIFF(day, @StartDate, '2022-11-16') + 1)
             Row_number() OVER (ORDER BY (SELECT NULL))
         FROM   [e32]),
     tally
     AS (SELECT Dateadd(day, n - 1, @StartDate) dates,
                n,
                @StartDate dt
         FROM   [num_tally])
SELECT *
FROM   tally

DROP TABLE #temp 

方案2：提前计算总天数限制行数 #

在num_tally中直接通过TOP限制生成的行数，避免无限制生成数据：

WITH e00(n)
     AS (SELECT 1
         UNION ALL
         SELECT 1),
     e02(n)
     AS (SELECT 1
         FROM   [e00] [a],
                [e00] [b]),
     e04(n)
     AS (SELECT 1
         FROM   [e02] [a],
                [e02] [b]),
     e08(n)
     AS (SELECT 1
         FROM   [e04] [a],
                [e04] [b]),
     e16(n)
     AS (SELECT 1
         FROM   [e08] [a],
                [e08] [b]),
     e32(n)
     AS (SELECT 1
         FROM   [e16] [a],
                [e16] [b]),
     num_tally(n)
     AS (SELECT Row_number() OVER (ORDER BY (SELECT NULL))
         FROM   [e32]),
     temp_dt AS (SELECT dt FROM #Temp)
SELECT Dateadd(day, n - 1, dt) dates,
       n,
       dt
FROM   num_tally, temp_dt
WHERE  n <= DATEDIFF(day, dt, '2022-11-16') + 1

DROP TABLE #temp 

内容的提问来源于stack exchange，提问作者Muhammad Taha