Today we will see an interesting case of incompatibility between MS SQL Server 2017 and PostgreSQL 9.6 (and different versions as well). Let’s start with this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
We emulate a recursive CTE. We have two columns in source dataset, we want to sum first column for rows partitioned by second column. This gives a very expected result:
|
1 2 3 4 |
rowValue phase ----------- ----------- 3 1 3 1 |
Now let’s use recursive CTE in MS SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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; |
|
1 2 3 4 |
rowValue phase ----------- ----------- 2 1 1 1 |
However, PostgreSQL gives correct values:
|
1 2 3 4 |
rowValue phase ----------- ----------- 3 1 3 1 |
Beware! Also, see this great post explaining row-based approach and set-based approach for implementing CTE.