From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hey! ORDER BY in VIEWS?
Date: Sun, 15 Jul 2001 14:40:30 -0700
Message-ID: <web-85979@davinci.ethosmedia.com> (raw)
In-Reply-To: <2121.995231683@sss.pgh.pa.us>
Tom,
> Hmm, I just realized that there's a bug here: let's say you have
>
> CREATE VIEW latest AS
> SELECT * FROM news ORDER BY story_timestamp DESC LIMIT 1;
>
> ie, this view gives you the latest news story. If you do
>
> SELECT * FROM latest WHERE story_text LIKE '%Joe Smith%';
>
> what you will get in 7.1 is the latest story mentioning Joe Smith,
> because the planner will push down the WHERE clause into the view's
> SELECT, where it'll be applied before the LIMIT. Perhaps some would
> call this useful behavior, but I'd say it has to be considered a bug
> :-(.
> The outer WHERE should not cause the VIEW to return a different row
> than it otherwise would.
Now you see why SQL92 doesn't support ORDER BY in views. ;-)
Why, exactly, do we need to support ORDER BY ... LIMIT in VIEWS, anyway?
I'm frankly unclear on the utility of this ... I make SQL jump through
some pretty fancy hoops, myself (4 section UNION query with nested
subselects, anyone?) and I've never needed ... or wanted ... a view with
a built-in LIMIT.
If we gotta have 'em, though, Tom, you'd have to code in an exception to
the VIEW optimizer that doesn't push down WHERE clauses if the VIEW has
an ORDER BY ... LIMIT statement. Sure you wanna get into this?
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
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: josh@agliodbs.com, tgl@sss.pgh.pa.us, peter_e@gmx.net
Subject: Re: Hey! ORDER BY in VIEWS?
In-Reply-To: <web-85979@davinci.ethosmedia.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