Używam funkcji okienkowej i działa to inaczej w rekurencyjnym CTE, niż poza nim. Weźmy taki kod:
WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase
UNION ALL
SELECT 2 AS rowValue, 0 AS phase
),
solution AS (
SELECT * FROM dummy
),
solution2 AS(
SELECT
SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,
phase + 1 AS phase
FROM solution
WHERE phase = 0
)
SELECT *
FROM solution2
WHERE phase = 1
To zwraca następujące wyniki:
rowValue phase
----------- -----------
3 1
3 1
(2 rows affected)
Takie same wyniki daje wersja 2017: http://sqlfiddle.com/#!18/9eecb/39725
Póki co wszystko jest okej. Na początku mam dwa wiersze z wartościami kolejno (1, 0) i (2, 0), chcę sumować pierwszą kolumnę. Teraz gdy zmienię kod na rekurencyjne CTE:
WITH dummy AS(
SELECT 1 AS rowValue, 0 AS phase
UNION ALL
SELECT 2 AS rowValue, 0 AS phase
),
solution AS (
SELECT * FROM dummy
UNION ALL
SELECT
SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,
phase + 1 AS phase
FROM solution
WHERE phase = 0
)
SELECT * FROM solution WHERE phase = 1;
to otrzymam następujący wynik:
rowValue phase
----------- -----------
2 1
1 1
(2 rows affected)
Czyli sumowanie działa inaczej, serwer nie uwzględnił wszystkich wierszy. Wersja 2017 robi to samo: http://sqlfiddle.com/#!18/9eecb/39726
Jest to irytujące, bo w PostgreSQL 9.6 wszystko działa: http://sqlfiddle.com/#!17/9eecb/27690
Pytanie 1: czy to jest gdzieś opisane w dokumentacji? Czytam https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017 i nie mogę się doszukać informacji, że funkcje okienkowe w rekurencyjnych CTE działają inaczej.
Pytanie 2: czy to da się naprawić? Chciałbym mieć takie wyniki, jak daje PostgreSQL, jednocześnie chcę uniknąć GROUP BY.
Pytanie 3: czy standard precyzuje, jaki powinien być wynik w sytuacji z rekurencyjnym CTE?