Re: Question about sorting internals

Hi,

before I'll go any further - this is only thought-experiment. I do not
plan to use such queries in real-life applications. I was just presented
with a question that I can't answer in any logical way.

There are two simple queries:

#v+
with rok2005 (miesiac,wynik) as (VALUES (1,1),(2,2)      ,(4,4),(5,NULL),(6,6))
    ,rok2004 (miesiac,wynik) as (VALUES (1,3)      ,(3,3),(4,5)         ,(6,6))
SELECT
distinct on (miesiac) *
FROM (
    SELECT miesiac, 2005 as rok, wynik FROM rok2005
    union all
    SELECT miesiac, 2004 as rok, wynik FROM rok2004
) as polaczone
ORDER BY miesiac, wynik desc;
#v-

#v+
with rok2005 (miesiac,wynik) as (VALUES (1,1),(2,2)      ,(4,4),(5,NULL),(6,6))
    ,rok2004 (miesiac,wynik) as (VALUES (1,3)      ,(3,3),(4,5)         ,(6,6))
SELECT
distinct on (miesiac) *
FROM (
    SELECT miesiac, 2004 as rok, wynik FROM rok2004
    union all
    SELECT miesiac, 2005 as rok, wynik FROM rok2005
) as polaczone
ORDER BY miesiac, wynik desc;
#v-

They differ only in order of queries in union all part.

The thing is that they return the same result. Why isn't one of them returning
"2005" for 6th "miesiac"?

I know I'm not sorting using "rok", which means I'm getting "undefined
functionality". Fine. But what exactly is happening that regardless of
order of rows in subquery, I get the same, always lower, rok in output?

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/