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

Please read up on relational databases and how they work. Understanding relational database theory is essential to work with them. A table (or a relation) is by definition an unordered set (with SQL it can be a multiset). This is as true for regular tables as for temporary ones. (Even if you create clustered indexes the table is buy definition still an unordered set and you still need order by in your select. Clustered indexes are a performance issue only.) If Sybase and Oracle are behaving otherwise they are wrong. I tend to believe if they do it's an accident that I wouldn't rely on. If Informix ever where to try to do anything about this that would be the time to look for some other vendor. You allways use order by in the final select that returns data to the user, and nowhere else. 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. : :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. : :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 :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. : :Informix's explanation for this, is that they are fragmenting the individual :rows over the temp dbspaces in a round_robin pattern when the temp table is :being created. However, when retrieving the rows back, they may not be using :the same round-robin sequence. : :Please correct me if I'm missing something, but when I ORDER BY rows I expect it :to be ordered; temp or permanent tables. : :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. : :Workarounds (I'm sure there are more): :1. Always include an ORDER BY statement when SELECTing from temp tables. :2. Set your environment variable DBSPACETEMP to only one temp dbspace. :3. Use a cursor to insert into a "real" temporary table. : :How is everybody else dealing with this "feature"? Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org