Re: upgrade 8.1.4 -> latest, sort order subquery - Mailing list pgsql-general

jef peeraer wrote:
> i decide this weekend to upgrade to the latest stable version from an
> 8.1.4 . Upgrade went smootly, as usual, but today, i've got some
> phonecalls of something weird. The query is as follows :
>
>
> registratie=# select * from module_info where type_module_id = 1;

> i combine this with the next query in a subquery

> The resulting query , which should return the same result as the first one

Here's where I think you're wrong.

> registratie=# select * from module_info where type_module_id in
>             (select * from get_parent_type_modules(1));

> The order is completely ignored, although there is an order by in the view
> 'module_info'

You're applying a where clause to the output of your view - filtering it
after the sort is done. If that filter is e.g. by a hash then the result
will be in a different order (or at least might be).

In general, the only ORDER BY you can rely on is one applied to the
final results of your SELECT.

--
   Richard Huxton
   Archonet Ltd