Subject: Re: Column "..." does not exist (view + union)
Mime-Version: 1.0 (Apple Message framework v1251.1)
Content-Type: text/plain; charset=us-ascii
From: Brent Dombrowski <brent.dombrowski@gmail.com>
In-Reply-To: <4EED15EA.6000507@gmail.com>
Date: Sat, 17 Dec 2011 18:58:18 -0800
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <7E407B4A-8F66-45CD-804D-E97A56C808DA@gmail.com>
References: <4EEC0533.9050007@gmail.com>	<20111217090246.GA12209@tux>
	<4EECA699.2020700@gmail.com>
	<CAKwGa_9D9M6V+bYJwayx_JtD1Tq3oQ0vouSXSshebQ8bbiJGMg@mail.gmail.com>
	<4EED15EA.6000507@gmail.com>
To: Stefan Weiss <krewecherl@gmail.com>

On Dec 17, 2011, at 2:21 PM, Stefan Weiss wrote:

> I know, but the problem only occurs when I want to sort by a column
> which hasn't been selected, and thus cannot be referred to by its =
index.
> For normal (non-union) queries, this is possible:
>=20
>    SELECT relname
>      FROM pg_class
>     WHERE relhasindex
>  ORDER BY relpages;
>=20
> In this trivial case, PostgreSQL knows where to look for "relpages".
> Not so in a union:
>=20
>    SELECT relname
>      FROM pg_class
>     WHERE relhasindex
> UNION
>    SELECT relname
>      FROM pg_class
>     WHERE relhasoids
> ORDER BY relpages;
>=20
> (ERROR: column "relpages" does not exist)
>=20
> I understand the error now (I think), and I know how to avoid it.

The real problem here is the order of operations. This is what Postgres =
did:

SELECT * FROM (
		SELECT relname
		FROM pg_class
		WHERE relhasindex
	UNION
		SELECT relname
		FROM pg_class
		WHERE relhasoids
	) as foo
ORDER BY relpages;

It applied the union before the order by. After the union, the relpages =
column was projected away. Thus relpages does not exist for the order =
by. The postgres manual states that this is what will happen.

The UNION clause has this general form:

select_statement UNION [ ALL ] select_statement
select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR =
UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a =
subexpression if it is enclosed in parentheses. Without parentheses, =
these clauses will be taken to apply to the result of the UNION, not to =
its right-hand input expression.)=