[GENERAL] upgrade 8.1.4 - latest, sort order subquery
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; naam | korte_beschrijving | kolom_naam | xul_opties| type_veld_id | lengte | is_verplicht | type_module_id ++---+-+--++--+ alg_02 | naam | naam | | 3 | 0 | f| 1 alg_03 | voornaam | voornamen | | 3 | 0 | f| 1 alg_01 | anoniem| anoniem | | 2 | 0 | f| 1 cl_01 | geslacht | geslacht | | 1 | 0 | t| 1 cl_19 | geboortedatum | geboortedatum | hide_clock,true | 7 | 0 | f| 1 cl_02 | leeftijd | leeftijd | | 1 | 0 | t| 1 cl_15 | origine| origine | | 1 | 0 | f| 1 (7 rows) i combine this with the next query in a subquery registratie=# select * from get_parent_type_modules(1); NOTICE: first query get_parent_type_modules - 1 (1 row) The resulting query , which should return the same result as the first one registratie=# select * from module_info where type_module_id in (select * from get_parent_type_modules(1)); NOTICE: first query naam | korte_beschrijving | kolom_naam | xul_opties| type_veld_id | lengte | is_verplicht | type_module_id ++---+-+--++--+ cl_15 | origine| origine | | 1 | 0 | f| 1 cl_02 | leeftijd | leeftijd | | 1 | 0 | t| 1 cl_19 | geboortedatum | geboortedatum | hide_clock,true | 7 | 0 | f| 1 cl_01 | geslacht | geslacht | | 1 | 0 | t| 1 alg_01 | anoniem| anoniem | | 2 | 0 | f| 1 alg_03 | voornaam | voornamen | | 3 | 0 | f| 1 alg_02 | naam | naam | | 3 | 0 | f| 1 (7 rows) The order is completely ignored, although there is an order by in the view 'module_info' jef peeraer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] upgrade 8.1.4 - latest, sort order subquery
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] upgrade 8.1.4 - latest, sort order subquery
On Jun 26, 2007, at 14:52 , jef peeraer wrote: The order is completely ignored, although there is an order by in the view 'module_info' I don't know what has caused the change in your system, but IIRC, the spec does not require a view to return rows in any particular order— I'm not even sure the spec allows ORDER BY in a view definition. (Whether or not PostgreSQL follows the spec in this case is another matter—I don't believe it does. So there's probably something a little more interesting going on here that others might help you with.) A quick, spec-compliant fix would be to add an ORDER BY clause in the calling query. Other than the row order, the results look correct, or am I missing something? You might also look into using a set returning function, in which you can specify the order of the results. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] upgrade 8.1.4 - latest, sort order subquery
Richard Huxton [EMAIL PROTECTED] writes: jef peeraer wrote: 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). Yeah, that IN-clause will in fact be turned into a join. EXPLAIN would give more info about what's happening, but I suspect the planner chose to do the join via mergejoin, which would sort the inputs by the join columns ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
