Re: BUG #12137: ORDER BY with expresion changes the output if added

dima-2 wrote
>  FROM "geonames" LEFT OUTER JOIN geoname_names
>         ON geoname_names.geoname_id = geonames.geoname_id
>         AND geoname_names.name_id IN
>         (SELECT name_id FROM geoname_names
>           WHERE geoname_names.geoname_id = geonames.geoname_id
>           AND geoname_names.language = 'ru'
>           ORDER BY geoname_names.short DESC, geoname_names.preffered DESC,
> geoname_names.colloquial, geoname_names.historic
>           LIMIT 1

1. This could use aliases since you have repeating table names
2. I would write the above portion as:

geonames LEFT JOIN (
SELDCT DISTINCT ON name_id, geoname_id FROM ... WHERE ... ORDER BY ...
) gnn USING (geoname_id)

3. I would supply a query of the form:

WITH geonames AS ( values (...),(...) )
, geoname_names AS ( values (...) )
<rest of the query here>

And then show what you want the table output of the query to be.

David J.







--
View this message in context:
http://postgresql.nabble.com/BUG-12137-ORDER-BY-with-expresion-changes-the-output-if-added-tp5829220p5829256.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.