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

[lirex . software]

The following bug has been logged on the website:

Bug reference:  7499
Logged by:  Denis Kolesnik
Email address:  lirex.softw...@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 = 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  |  | X   |  | XX
years X mons XX days
 224 |  | Kolesnik  |  | XX  |  | X
years XX mons XX days
 144 |  | Kolesnik  |  | XX  |  | 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 | XX  
 112 | X   
...

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.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

[Tom Lane]

lirex.softw...@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 | XX  
>  112 | X   
> ...

> 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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs