Обсуждение: BUG #12137: ORDER BY with expresion changes the output if added

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

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.

dima@koulikoff.ru writes:
> The request ...
> 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.

I can't reproduce any such misbehavior with the given information
and some made-up data.  Can you provide a self-contained test case?
Or at least EXPLAIN (ANALYZE, VERBOSE) output for both queries?

            regards, tom lane

Dmitri Koulikov <koulikoff@gmail.com> writes:
> Hello. I've prepared a sample. It turned out that the problem lies in other
> place.
> In the attachment there is output from explain and a sample

This isn't a self-contained test case :-(.  I guessed at some index
definitions but couldn't reproduce the plan exactly, and in any case
it's clear from your EXPLAIN output there are more than just these
three rows in your tables.

            regards, tom lane

On Thursday, December 04, 2014 11:22:21 Tom Lane wrote:

> dima@koulikoff.ru writes:
> > The request ...
> > 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.
>
> I can't reproduce any such misbehavior with the given information
> and some made-up data.  Can you provide a self-contained test case?
> Or at least EXPLAIN (ANALYZE, VERBOSE) output for both queries?
>
>             regards, tom lane

Hello. I've prepared a sample. It turned out that the problem lies in other
place.

In the attachment there is output from explain and a sample

The problem is with ORDER  clause I get only one record while without it there
are two.

As I understand this clause should not affect the size of the output.

Tank you in advance. And sorry for the delayed response.

--

With best regards,
   Dmitri Koulikoff

mailto:koulikoff@gmail.com
skype: dima.koulikoff
phone: +7-495-5052185