Re: Question about sorting internals

Hi deepesz,
You might want to see their EXPLAIN VERBOSE outputs. Having one of them
(2004 one) lesser number of rows, might be getting picked up as first
relation being union and thus ends up having it's rows before the second
one. Explain output would make it more clear. Also, try having same number
of rows in both the relations.

On Wed, Dec 11, 2013 at 3:26 PM, hubert depesz lubaczewski <
depesz(at)depesz(dot)com> wrote:

> 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/
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company