Re: Hey! ORDER BY in VIEWS?

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco