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
