BUG #12137: ORDER BY with expresion changes the output if added - Mailing list pgsql-bugs

From dima@koulikoff.ru
Subject BUG #12137: ORDER BY with expresion changes the output if added
Date
Msg-id 20141204064346.2573.21378@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #12137: ORDER BY with expresion changes the output if added
Re: BUG #12137: ORDER BY with expresion changes the output if added
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      12137
Logged by:          Dmitri Koulikov
Email address:      dima@koulikoff.ru
PostgreSQL version: 9.3.5
Operating system:   Linux 3.10.17 #1 SMP x86_64 Intel@ 2.20GHz GNU/Lin
Description:

 CREATE TABLE geonames
(
  geoname_id integer,
  name character varying(200),
  asciiname character varying(200),
  latitude numeric(19,16),
  longitude numeric(19,16),
  feature character varying(11),
  country_code character varying(2),
  admin1 character varying(20),
  admin2 character varying(80),
  admin3 character varying(20),
  admin4 character varying(20),
  population bigint NOT NULL DEFAULT 0,
  elevation integer,
  dem integer,
  timezone character varying(40),
  modified_on date,
  created_at timestamp without time zone,
  updated_at timestamp without time zone
)
WITH (
  OIDS=FALSE
);

CREATE TABLE geoname_names
(
  name_id integer,
  geoname_id integer,
  language character varying(7),
  name character varying(200),
  preffered boolean NOT NULL DEFAULT false,
  short boolean NOT NULL DEFAULT false,
  colloquial boolean NOT NULL DEFAULT false,
  historic boolean NOT NULL DEFAULT false,
  created_at timestamp without time zone,
  updated_at timestamp without time zone
)
WITH (
  OIDS=FALSE
);

The request

SELECT  geonames.geoname_id, geonames.asciiname, geonames.feature,
geonames.country_code, geonames.admin1, geonames.admin2,
            CASE WHEN geoname_names.name IS NOT NULL
            THEN geoname_names.name ELSE geonames.name END AS name
 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
        ) WHERE country_code = 'RU' and admin1 = '17'

returns Russian names in :name field from the geoname_names table

But if I add a line
ORDER BY CASE WHEN geoname_names.name IS NOT NULL THEN geoname_names.name
ELSE geonames.name END
at the end of the request all the values for name are taken from the
geonames table.

Wrapping into CTE or subquery does not help. The only solution to get sorted
names from geoname_names I found is
ORDER BY geoname_names.name NULLS LAST, geonames.name
but it is not what is needed since for German language, for example, the
sorting order will be wrong.

Is it a bug or feature? How to get the desired result?

Thank you in advance.

With best regards,
Dmitri

pgsql-bugs by date:

Previous
From: rseshadr@uncc.edu
Date:
Subject: BUG #12129: questions on exporting data from a database
Next
From: preeti.karadi@tcs.com
Date:
Subject: BUG #12141: PostgreSQL cannot be started