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@depesz.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
