Re: Caution: Bug or "feature" - row ordering within temp...

Subject: Re: Caution: Bug or "feature" - row ordering within temp table I don't see what the problem is. The engine is working as expected, i.e., it is ordering the rows during the insert. From your description, the physical order of the data being output is more an artifact of the internal workings of a specific RDBMS than a desirable feature. If you want a specific order for the output, in other words, order the output there...NOT when you are building the table. Another work around you may want to consider is to explicitly create the temp table in a single dbspace. That would eliminate the need for the DBSPACETEMP setting. But it still strikes me as a bad practice, since you are relying on the internals of OnLine for the ordering methods. David vjaco @ hcia.com said: >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"?