Thread: Hey! ORDER BY in VIEWS?
Tom, Stephan,
Hey! I thought you couldn't do ORDER BY in views ... yet I just did.
Is this a new thing, or am I just getting my Trasact-SQL and my
PostgreSQL mixed up again?
-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
Attachment
I think PostgreSQL allows you to do an ORDER BY in a view, but the real message is that it just doesn't make any sense. Remember that a view is just a "virtual table", not a query. If you "order by" as part of it's definition, there's no guarantee that the data will be orded when you SELECT FROM later on. Always, always, always include an ORDER BY clause in every select you do. (I personally think SQL ought to REQUIRE it!) -Robby -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Josh Berkus Sent: Sunday, July 15, 2001 12:22 PM To: pgsql-sql@postgresql.org Subject: [SQL] Hey! ORDER BY in VIEWS? Tom, Stephan, Hey! I thought you couldn't do ORDER BY in views ... yet I just did. Is this a new thing, or am I just getting my Trasact-SQL and my PostgreSQL mixed up again? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I think Tom fixed that in 7.1.X. That is why it now works. > Tom, Stephan, > > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? > > -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 [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Josh Berkus writes: > Hey! I thought you couldn't do ORDER BY in views ... yet I just did. > Is this a new thing, or am I just getting my Trasact-SQL and my > PostgreSQL mixed up again? I think it was allowed from 7.1 on to enable LIMIT in views to work sensibly. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Pater, Robbie, Bruce, > > Hey! I thought you couldn't do ORDER BY in views ... yet I just > did. > > Is this a new thing, or am I just getting my Trasact-SQL and my > > PostgreSQL mixed up again? > > I think it was allowed from 7.1 on to enable LIMIT in views to work > sensibly. Makes sense. I take it that this is a deviation from the ANSI 92 standard, then? What happens if I put an ORDER BY in a view, then call an ORDER BY in a query, e.g.: CREATE VIEW test_view AS SELECT client_name, city, zip FROM clients WHERE zip IS NOT NULL ORDER BY zip; SELECT * FROM test_view ORDER BY city; Does the second ORDER BY override or suppliment the view ORDER BY, or is it ignored? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Josh: You wondered: >What happens if I put an ORDER BY in a view, then call an ORDER BY in a >query, e.g.: > >CREATE VIEW test_view AS >SELECT client_name, city, zip FROM clients >WHERE zip IS NOT NULL >ORDER BY zip; > >SELECT * FROM test_view ORDER BY city; > >Does the second ORDER BY override or suppliment the view ORDER BY, or is >it ignored? I think this question falls into the "Don't ask, don't tell" category of computer related questions. I can't think of a reason to design this way, the behavior isn't specified or sensible, so just don't do it! Sorry if I'm over-admonishing. Curiosity killed the cat. -Robby
Peter Eisentraut <peter_e@gmx.net> writes:
> Josh Berkus writes:
>> Hey! I thought you couldn't do ORDER BY in views ... yet I just did.
>> Is this a new thing, or am I just getting my Trasact-SQL and my
>> PostgreSQL mixed up again?
> I think it was allowed from 7.1 on to enable LIMIT in views to work
> sensibly.
The point being that ORDER BY + LIMIT is actually a useful computational
extension. ORDER BY per se, in a view, is rather pointless since any
query that selects from the view will feel free to rearrange the data
for its convenience.
Hmm, I just realized that there's a bug here: let's say you have
CREATE VIEW latest ASSELECT * 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.
regards, tom lane
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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
>> 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.
> Why, exactly, do we need to support ORDER BY ... LIMIT in VIEWS, anyway?
> I'm frankly unclear on the utility of this ...
I think the above example is pretty compelling, don't you? Easy to read
and it generates a very nice indexscan plan.
> 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?
It's a one-line addition to code that already knows that certain kinds
of clauses (like UNION) prevent pushdown. Just an oversight, not a
fundamental flaw.
regards, tom lane
Josh Berkus wrote: > > Pater, Robbie, Bruce, > > Makes sense. I take it that this is a deviation from the ANSI 92 > standard, then? > > What happens if I put an ORDER BY in a view, then call an ORDER BY in a > query, e.g.: > Does the second ORDER BY override or suppliment the view ORDER BY, or is > it ignored? It overrides. People seem to be forgetting ORDER BY ... LIMIT has selective qualities as well as ordering ones. The example someone gave me was when you use LIMIT ... OFFSET to fetch results a page at a time. If you want the last page of your results you need to do something like: SELECT * FROM messages ORDER BY msg_timestamp DESC LIMIT 20; But - this gives them in reverse timestamp order. So - wrap the query in a view and then apply your own ORDER BY. Can't remember who came up with this (some evil genius :-) - but it seemed to make sense so I stuck the example in my PostgreSQL notes. - Richard Huxton
> But - this gives them in reverse timestamp order. So - wrap the query in > a view and then apply your own ORDER BY. > > Can't remember who came up with this (some evil genius :-) - but it > seemed to make sense so I stuck the example in my PostgreSQL notes. We kept rejecting the idea of ORDER BY in a view until someone suggested LIMIT and ORDER BY, at which point we had to enable it. I think Tom did the work. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026