Understanding the Impact of ORDER BY in Window Functions on other Partitions in PostgreSQL

Question

I have a simple table odd_nums with columns num and odd:

create table odd_nums
(
    num numeric,
    odd numeric
);

INSERT INTO odd_nums VALUES (0, 0);
INSERT INTO odd_nums VALUES (1, 1);
INSERT INTO odd_nums VALUES (2, 0);
INSERT INTO odd_nums VALUES (3, 1);

I am playing around with window functions. This query produces the expected results:

SELECT num,
       FIRST_VALUE(num) OVER (PARTITION BY odd) a,
       FIRST_VALUE(num) OVER (PARTITION BY odd) b
FROM odd_nums;

num	a	b
0	0	0
2	0	0
1	1	1
3	1	1

When I order the partition in column b the results change like this:

SELECT NUM,
       FIRST_VALUE(num) OVER (PARTITION BY odd)                   a,
       FIRST_VALUE(num) OVER (PARTITION BY odd ORDER BY num DESC) b
FROM odd_nums;

num	a	b
2	2	2
0	2	2
3	3	3
1	3	3

Column b is what I would expect, but why is column a changing as well?

DB Fiddle

Can someone help me understand why adding the ORDER BY num DESC clause in the second window function causes a change in the first window function? Is there some specific database implementation detail or optimization that might be affecting the result?

I have looked through the PostgreSQL documentation but without success.

Answer 1

Think of your data as a pile of clothes, where the odd column stores the id of a type of clothing (shirt, pants, socks, etc). The PARTITION clause on odd is the equivalent of organizing the clothes into separate piles by each type. So one pile of clothing is only shirts, another pile is only socks. Then, the FIRST_VALUE() window function itself is like someone saying to you, give me the first piece of clothing from each pile.

Well a pile of clothes is unorganized, so which clothing item is the first, in each pile? You may grab a blue shirt and red socks, randomly, the first time. If you put those clothes back into their respective piles, and do it again, you may end up with a green shirt and purple socks.

This is your data. It has no order to it until you explicitly specify one in a query that's fetching that data. Most window functions don't make sense without an ORDER BY clause. Without one, the data being returned is semi-random, and known as nondeterministic.

When you add the ORDER BY clause to your query, it's like taking your clothing piles and laying out the pieces of clothes in a line, ordered, let's say by size. Now every time someone asks you for the first clothing item of each line of clothes, you'll always pull the smallest shirt, the smallest socks, etc, repeatedly. This is known as deterministic. Same for your data, make sure to explicitly order it when using window functions that depend on ordering (e.g. FIRST_VALUE()).

And as far as why did the a column end up just following the same ordering of the b column when the window function for the a column doesn't use an ORDER BY clause: database systems are lazy aka efficient. This will vary a little from one database system to another, and there's no guarantee you'll always see the same behavior based on a large number of factors of how the SQL engine of the particular database system works. But as jjanes noted, to be most efficient, when it saw no specific ordering provided for the a column, it likely re-used the already sorted list of data from the b column to be most efficient. Sorting something once is more efficient than sorting that same thing two different ways.

Answer 2

Without the ORDER BY, the expression is underdetermined. There are multiple possible answers, of which it returns the most convenient to compute. Once one expression gains an ORDER BY, then it becomes most convenient to apply that same ordering to the other expression as well.