Result set order broken when calling procedure

[NeWbLt123]

conn.exec("select * from my_procedure()" ... 

my_procedure returns a result set with the correct order (tested in interactive sql) but then when I check the result passed to exec's callback method, the order is wrong.

The following works:

conn.exec("select * from my_procedure() order by something" ... 

Are you able to replicate this issue on your side?

Thank you!

[efarrar]

This is the expected behaviour for a relational database. SQL uses set based operations, and unless an ORDER BY is explicitly specified, the ordering of the result set is non-deterministic and cannot be relied upon. This is not specific to SQL Anywhere, but a property shared by all relational databases (e.g. http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause)

One reason for this is performance. If no ordering is required, the query planner has more flexibility on how to execute the query. For example, is no ordering is required, the query planner can execute the query in parallel across multiple cores and start returning rows as soon as they are available (giving better performance). By comparison, if a ORDER BY is specified (or an aggregate), all of the result rows have to be buffered together until all rows are available so they can be globally ordered, and then returned. In other words, no rows can start flowing over the wire until all rows are available and ordered.

In many cases, a query is doing either a table scan or an index lookup. In that case, rows will often tend to come back in the order they were inserted. This can give the illusion that there is some kind of default ordering. However, this is just a by-product of the way the rows are stored internally.

In your case, you are selecting from procedure (my_procedure). Even if the query inside of my_procedure specifies an order, due to the set-based nature of SQL, that order is not necessarily respected by an outer SELECT. Again, in many cases it probably will be ordered the same way, but it should not be relied upon. (By comparison, I believe that if you were to do a CALL my_procedure(), the order would be preserved because in that case the procedure is the top-level query).

In summary, if order is important, the top-level query must have an ORDER BY specified.

[NeWbLt123]

Thanks for the great explanation!