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
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
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
Richard Huxton <dev@archonet.com> 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