BUG #14316: union all with order by "missing from-clause"

From: ozzi_99(at)hotmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14316: union all with order by "missing from-clause"
Date: 2016-09-08 09:45:56
Message-ID: 20160908094556.1403.54409@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14316
Logged by: Ossi Kasurinen
Email address: ozzi_99(at)hotmail(dot)com
PostgreSQL version: 9.5.4
Operating system: windows 8.1 pro
Description:

/*I cannot do "union all" operation with "order by" to another table than
the primary.

example tables and select clause:*/
--table creation: bar
CREATE TABLE public.bar
(
id integer NOT NULL,
sortcolumn integer,
CONSTRAINT pk_bar PRIMARY KEY (id)
)
--table creation:foo
CREATE TABLE public.foo
(
id integer NOT NULL,
barid integer,
sortcolumn integer,
CONSTRAINT pk_foo PRIMARY KEY (id),
CONSTRAINT fk_second FOREIGN KEY (barid)
REFERENCES public.bar (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)

-- insert some dummy data.
-- the following doesn't work

select origin.id, origin.barid, origin.sortcolumn from foo origin
inner join bar on origin.barId = bar.Id
union all
select a.id, a.barid, a.sortcolumn from foo as a
inner join bar as b on a.barid = b.id
order by b.sortcolumn
limit 1

/* If I take "b" away from order by, it will order by table foo, and it
gives incorrect results. */

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vitaly Burovoy 2016-09-08 11:19:02 Re: BUG #14316: union all with order by "missing from-clause"
Previous Message abcz2.uprola 2016-09-08 08:19:53 BUG #14315: pg_dump --no-tablespaces doesn't do it's job when --create specified