From: Brent Dombrowski <brent.dombrowski@gmail.com>
To: Stefan Weiss <krewecherl@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Column "..." does not exist (view + union)
Date: Sat, 17 Dec 2011 18:58:18 -0800
Message-ID: <7E407B4A-8F66-45CD-804D-E97A56C808DA@gmail.com> (raw)
In-Reply-To: <4EED15EA.6000507@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>
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:
>
> SELECT relname
> FROM pg_class
> WHERE relhasindex
> ORDER BY relpages;
>
> In this trivial case, PostgreSQL knows where to look for "relpages".
> Not so in a union:
>
> SELECT relname
> FROM pg_class
> WHERE relhasindex
> UNION
> SELECT relname
> FROM pg_class
> WHERE relhasoids
> ORDER BY relpages;
>
> (ERROR: column "relpages" does not exist)
>
> 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.)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: brent.dombrowski@gmail.com, krewecherl@gmail.com
Subject: Re: Column "..." does not exist (view + union)
In-Reply-To: <7E407B4A-8F66-45CD-804D-E97A56C808DA@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox