Обсуждение: 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