Re: Caution: Bug or "feature" - row ordering within temp tables in V7.x

vjaco@hcia.com wrote: > This is one of those things that is serious enough to warrant a mass mailing > to Informix for an immediate "feature" request. Don't hold your breath for that :-) > Using anybody's SQL (Sybase's, Oracle's and even Informix's), if I have an ORDER > BY clause in my SELECT statement, I expect to see my results ordered according > to the criteria that I specified. Sure, that's what the ORDER BY clause is for. > Here's the problem: > In V7.x if you are using multiple temp dbspaces (DBSPACETEMP) and you have a > statement like the following: > SELECT * FROM table_a INTO TEMP ORDER BY 1 temp_a Shouldn't it read "SELECT * FROM table_a ORDER BY 1 INTO TEM temp_a"? > Do not expect the rows you get back from temp_a (SELECT * from temp_a) to be > ordered in any fashion, least of all by the first column. I wouldn't expect that for any relational database, because that be- havior is by design. The rows in a table can be arranged in any possible fashion, the user has no control over that. You can have the RDBMS arrange the rows physically in an index order by creating or altering an index "TO CLUSTER", but that only holds until the next time rows are deleted or inserted. If you need the result set of a SELECT statement to be ordered, then use an explicit ORDER BY. > Please correct me if I'm missing something, but when I ORDER BY rows I expect it > to be ordered; temp or permanent tables. That's a misconception. ORDER BY will only affect the order of rows returned in a result set, not in a table itself. > The main problem is that the user is expecting ordered rows, and the statement > completes without any error indication. Hence, syntax that may have worked just > fine with pre V7.x engines or instances where only one temp dbspace was used, > will now all of a sudden produce incorrect results with no warning to the user. The result isn't incorrect, the user's assumptions are. The fact that it worked in pre-7 versions was maybe just chance. > Workarounds (I'm sure there are more): > 1. Always include an ORDER BY statement when SELECTing from temp tables. Right. What's the problem with this? Of course, it would mean a lot of work for you if you have tons of legacy code that relies on your assumed behavior... Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | | +--------------------------+------------------------------------------+