Thread: BUG #7499: wrong data sorting if I use "...limit 1..." SQL clause along with "...order by ..."

The following bug has been logged on the website:

Bug reference:      7499
Logged by:          Denis Kolesnik
Email address:      lirex.software@gmail.com
PostgreSQL version: 9.1.3
Operating system:   Windows XP Home Edition Service Pack 3 OEM
Description:        =


firstly a schema of my table:

-- Name: tbl_owners_individual; Type: TABLE; Schema: public; Owner: lurtz;
Tablespace: =

--

CREATE TABLE tbl_owners_individual (
    id integer NOT NULL,
    str_first_name character(20),
    str_last_name character(20),
    dt_birth date,
    str_email character(40),
    str_sex character(1),
    int_icq bigint,
    str_nickname character(30),
    str_cellphone character(14),
    str_comment character(50)
);


ALTER TABLE public.tbl_owners_individual OWNER TO lurtz;

SET default_with_oids =3D false;

--
-- Name: tbl_owners_individual_id_seq; Type: SEQUENCE; Schema: public;
Owner: lurtz
--

CREATE SEQUENCE tbl_owners_individual_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.tbl_owners_individual_id_seq OWNER TO lurtz;

--
-- Name: tbl_owners_individual_id_seq; Type: SEQUENCE OWNED BY; Schema:
public; Owner: lurtz
--

ALTER SEQUENCE tbl_owners_individual_id_seq OWNED BY
tbl_owners_individual.id;


--
-- Name: tbl_owners_individual_id_seq; Type: SEQUENCE SET; Schema: public;
Owner: lurtz
--

SELECT pg_catalog.setval('tbl_owners_individual_id_seq', 51, true);

ALTER TABLE tbl_owners_individual ALTER COLUMN id SET DEFAULT
nextval('tbl_owners_individual_id_seq'::regclass);

----

the problem:

there are 4 queries:
1. select id, str_last_name from tbl_owners_individual order by
str_last_name;

...
  49 | Kolesnik            =

 224 | Kolesnik            =

 144 | Kolesnik            =

   1 | Kolesnik            =

...

2. select id, ' ', regexp_replace(str_last_name,' ','') as lastname, ' ',
regexp_replace(str_first_name,' ','') as firstname, ' ', age(dt_birth) as
age from tbl_owners_individual order by str_last_name;

...
  49 |          | Kolesnik      |          | XXXXX           |          | XX
years X mons XX days
 224 |          | Kolesnik      |          | XXXXXX          |          | X
years XX mons XX days
 144 |          | Kolesnik      |          | XXXXXXXXXX      |          | XX
years XX mons XX days
   1 |          | Kolesnik      |          | Denis           |          | 31
years 4 mons 21 days
...

(I replaced with X sensitive information)

3. select id, str_last_name from tbl_owners_individual order by
str_last_name offset 53;

...
   1 | Kolesnik            =

 111 | Kolesnik            =

 251 | XXXXXXXXXX          =

 112 | XXXXX               =

...

4. select id, str_last_name from tbl_owners_individual order by
str_last_name limit 1 offset 53;

 111 | Kolesnik            =


the 4-rd query should return

1 | Kolesnik            =

instead of
 111 | Kolesnik            =


Regards,
Denis Kolesnik.

lirex.software@gmail.com writes:
> 3. select id, str_last_name from tbl_owners_individual order by
> str_last_name offset 53;

> ...
>    1 | Kolesnik
>  111 | Kolesnik
>  251 | XXXXXXXXXX
>  112 | XXXXX
> ...

> 4. select id, str_last_name from tbl_owners_individual order by
> str_last_name limit 1 offset 53;

>  111 | Kolesnik

This is not a bug.  Your ORDER BY does not fully determine the order of
the rows (since all rows with str_last_name = 'Kolesnik' sort the same);
so Postgres is entitled to return equivalent rows in any order, or to
select any subset of the equivalent rows for a LIMIT query.

It's not completely nondeterministic, of course, but I suspect
if you examine EXPLAIN results you'll find that different query
plans got chosen for the queries with and without LIMIT.

            regards, tom lane