It was always the case that server can return result set in any order including no order :). For me, only simplest queries return results in some kind of logical order.

Yes - but any kind of sort on the server has implications related to locking and performance in a Multi- user transaction environment. Powerbuilder has extremely fast client interfaces and fast internal data window sorts - so most of the time for anything not very large - we sort in the data window - however for journals and other transaction reports it was easy for the programmer to forget to assert a final sort as the transactions came out ordered in V11 and earlier.

It has always been the case that row order was not deterministic in the absence of an ORDER BY. From the SA10.0.1 docs, "(t)he only way to ensure that rows are returned in a particular order is to use ORDER BY. In the absence of an ORDER BY clause, SQL Anywhere returns rows in whatever order is most efficient. This means that the appearance of result sets may vary depending on when you last accessed the row and other factors." Similiar documentation will exist in most if not not all versions of SQL Anywhere.

In your case, you happened to get lucky that the results appeared to be returned in "a natural order" on a consistent basis - a different query or different set of operational conditions could have resulted in a different result order. My guess is that SA12 exposed this behaviour in your application because it is using different access plans than used in prior versions.

"either in the SQL (which can tie the system up a bit)" - for a result set fit for human consumption (such as a visible DataWindow) it is surprising that a server-side ORDER BY will have any effect on performance. If true, it might be an early warning that the server is grossly under-sized.

On the other hand, if the result set is gargantuan, and always filtered on the client side... but even then, it would likely be the client-server communication time that is the bottleneck rather than the ORDER BY.

Unless, again, the server was feeble. The traditional arguments for moving functionality to the client side have faded over time, replaced with "it doesn't matter" in most cases.

As far as ordering being a behavior change between 11 and 12 is concerned, not true. You had a bug (no explicit ordering), it resulted in a symptom, you fixed it (good for you!)

Here's a bad analogy: You used a car as a boat, and it worked... the car floated (it can happen, see the video of the Japanese tsunami).

Then you traded in that car, and the new one sank. You survived! :)

It is the case that every upgrade exposes "bugs" - but nonetheless it is real work to be done - and better if forewarned. We were a little surprised by it.

From you comment it seems to imply that ordering a large query on the server that involves rows used by many concurrent users has much less impact on concurrent users than in the past. How much has that changed recently?

Nowadays - with application publishing, the client is on the server - so network overhead is less of an issue - however the application datawindow sometimes still needs to get a fairly significant set of rows to generate reports from (10K plus) - so I think in our case the client datawindow in-memory sorting might still be less intrusive in a multi-user environment.