BUG #5084: Query gives different number of rows depending on ORDER BY

The following bug has been logged online:

Bug reference: 5084
Logged by: Bernt Marius Johnsen
Email address: bernt(dot)johnsen(at)sun(dot)com
PostgreSQL version: 8.3.7
Operating system: Linux 2.6.24-23-generic
Description: Query gives different number of rows depending on ORDER
BY
Details:

The following queries gives different number of rows (247 vs 260):

SELECT table1 . varchar_key AS field1 , table2 . varchar_key AS field2 ,
table2 . datetime_key AS field3 , table2 . int_key AS field4 , table2 . pk
AS field5 FROM ( C AS table1 INNER JOIN ( ( CC AS table2 LEFT JOIN C AS
table3 ON (( table3 .pk <= table2 . int_nokey ) AND (table3 .pk = table2 .
int_nokey ) ) ) ) ON (( table3 . varchar_key != table2 . varchar_key ) AND (
table3 . varchar_nokey <> table2 . varchar_key ) ) ) WHERE table1 .
varchar_key = table1 . varchar_key;

and

SELECT table1 . varchar_key AS field1 , table2 . varchar_key AS field2 ,
table2 . datetime_key AS field3 , table2 . int_key AS field4 , table2 . pk
AS field5 FROM ( C AS table1 INNER JOIN ( ( CC AS table2 LEFT JOIN C AS
table3 ON (( table3 .pk <= table2 . int_nokey ) AND (table3 .pk = table2 .
int_nokey ) ) ) ) ON (( table3 . varchar_key != table2 . varchar_key ) AND (
table3 . varchar_nokey <> table2 . varchar_key ) ) ) WHERE table1 .
varchar_key = table1 . varchar_key ORDER BY table1 . time_key DESC , field1
ASC , field4 ASC , field4 , table1 . int_key , table1 .pk DESC , ( table2 .
varchar_key || table1 . varchar_key ) , field1;

Dump of the database:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

--
-- Name: c_seq; Type: SEQUENCE; Schema: public; Owner: NNuser
--

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

ALTER TABLE public.c_seq OWNER TO NNuser;

--
-- Name: c_seq; Type: SEQUENCE SET; Schema: public; Owner: NNuser
--

SELECT pg_catalog.setval('c_seq', 20, true);

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: c; Type: TABLE; Schema: public; Owner: NNuser; Tablespace:
--

CREATE TABLE c (
pk integer DEFAULT nextval('c_seq'::regclass) NOT NULL,
int_nokey integer,
int_key integer,
date_key date,
date_nokey date,
time_key time without time zone,
time_nokey time without time zone,
datetime_key timestamp without time zone,
datetime_nokey timestamp without time zone,
varchar_key character varying(1),
varchar_nokey character varying(1)
);

ALTER TABLE public.c OWNER TO NNuser;

--
-- Name: cc_seq; Type: SEQUENCE; Schema: public; Owner: NNuser
--

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

ALTER TABLE public.cc_seq OWNER TO NNuser;

--
-- Name: cc_seq; Type: SEQUENCE SET; Schema: public; Owner: NNuser
--

SELECT pg_catalog.setval('cc_seq', 29, true);

--
-- Name: cc; Type: TABLE; Schema: public; Owner: NNuser; Tablespace:
--

CREATE TABLE cc (
pk integer DEFAULT nextval('cc_seq'::regclass) NOT NULL,
int_nokey integer,
int_key integer,
date_key date,
date_nokey date,
time_key time without time zone,
time_nokey time without time zone,
datetime_key timestamp without time zone,
datetime_nokey timestamp without time zone,
varchar_key character varying(1),
varchar_nokey character varying(1)
);

ALTER TABLE public.cc OWNER TO NNuser;

--
-- Data for Name: c; Type: TABLE DATA; Schema: public; Owner: NNuser
--

COPY c (pk, int_nokey, int_key, date_key, date_nokey, time_key, time_nokey,
datetime_key, datetime_nokey, varchar_key, varchar_nokey) FROM stdin;
1 \N 2 \N \N 11:28:45 11:28:45 2004-10-11 18:13:16 2004-10-11 18:13:16 w w
2 7 9 2001-09-19 2001-09-19 20:25:14 20:25:14 \N \N m m
3 9 3 2004-09-12 2004-09-12 13:47:24 13:47:24 1900-01-01 00:00:00 1900-01-01
00:00:00 m m
4 7 9 \N \N 19:24:11 19:24:11 2009-07-25 00:00:00 2009-07-25 00:00:00 k k
5 4 \N 2002-07-19 2002-07-19 15:59:13 15:59:13 \N \N r r
6 2 9 2002-12-16 2002-12-16 00:00:00 00:00:00 2008-07-27 00:00:00 2008-07-27
00:00:00 t t
7 6 3 2006-02-08 2006-02-08 15:15:04 15:15:04 2002-11-13 16:37:31 2002-11-13
16:37:31 j j
8 8 8 2006-08-28 2006-08-28 11:32:06 11:32:06 1900-01-01 00:00:00 1900-01-01
00:00:00 u u
9 \N 8 2001-04-14 2001-04-14 18:32:33 18:32:33 2003-12-10
00:00:00 2003-12-10 00:00:00 h h
10 5 53 2000-01-05 2000-01-05 15:19:25 15:19:25 2001-12-21
22:38:22 2001-12-21 22:38:22 o o
11 \N 0 2003-12-06 2003-12-06 19:03:19 19:03:19 2008-12-13
23:16:44 2008-12-13 23:16:44 \N \N
12 6 5 1900-01-01 1900-01-01 00:39:46 00:39:46 2005-08-15
12:39:41 2005-08-15 12:39:41 k k
13 188 166 2002-11-27 2002-11-27 \N \N \N \N e e
14 2 3 \N \N 00:00:00 00:00:00 2006-09-11 12:06:14 2006-09-11 12:06:14 n n
15 1 0 2003-05-27 2003-05-27 13:12:11 13:12:11 2007-12-15
12:39:34 2007-12-15 12:39:34 t t
16 1 1 2005-05-03 2005-05-03 04:56:48 04:56:48 2005-08-09
00:00:00 2005-08-09 00:00:00 c c
17 0 9 2001-04-18 2001-04-18 19:56:05 19:56:05 2001-09-02
22:50:02 2001-09-02 22:50:02 m m
18 9 5 2005-12-27 2005-12-27 19:35:19 19:35:19 2005-12-16
22:58:11 2005-12-16 22:58:11 y y
19 \N 6 2004-08-20 2004-08-20 05:03:03 05:03:03 2007-04-19
00:19:53 2007-04-19 00:19:53 f f
20 4 2 1900-01-01 1900-01-01 18:38:59 18:38:59 1900-01-01
00:00:00 1900-01-01 00:00:00 d d
\.

--
-- Data for Name: cc; Type: TABLE DATA; Schema: public; Owner: NNuser
--

COPY cc (pk, int_nokey, int_key, date_key, date_nokey, time_key, time_nokey,
datetime_key, datetime_nokey, varchar_key, varchar_nokey) FROM stdin;
10 7 8 \N \N 01:27:35 01:27:35 2002-02-26 06:14:37 2002-02-26 06:14:37 v v
11 1 9 2006-06-14 2006-06-14 19:48:31 19:48:31 1900-01-01
00:00:00 1900-01-01 00:00:00 r r
12 5 9 2002-09-12 2002-09-12 00:00:00 00:00:00 2006-12-03
09:37:26 2006-12-03 09:37:26 a a
13 3 186 2005-02-15 2005-02-15 19:53:05 19:53:05 2008-05-26
12:27:10 2008-05-26 12:27:10 m m
14 6 \N \N \N 19:18:56 19:18:56 2004-12-14 16:37:30 2004-12-14 16:37:30 y y
15 92 2 2008-11-04 2008-11-04 10:55:12 10:55:12 2003-02-11
21:19:41 2003-02-11 21:19:41 j j
16 7 3 2004-09-04 2004-09-04 00:25:00 00:25:00 2009-10-18
02:27:49 2009-10-18 02:27:49 d d
17 \N 0 2006-06-05 2006-06-05 12:35:47 12:35:47 2000-09-26
07:45:57 2000-09-26 07:45:57 z z
18 3 133 1900-01-01 1900-01-01 19:53:03 19:53:03 \N \N e e
19 5 1 1900-01-01 1900-01-01 17:53:30 17:53:30 2005-11-10
12:40:29 2005-11-10 12:40:29 h h
20 1 8 1900-01-01 1900-01-01 11:35:49 11:35:49 2009-04-25
00:00:00 2009-04-25 00:00:00 b b
21 2 5 2005-01-13 2005-01-13 \N \N 2002-11-27 00:00:00 2002-11-27
00:00:00 s s
22 \N 5 2006-05-21 2006-05-21 06:01:40 06:01:40 2004-01-26
20:32:32 2004-01-26 20:32:32 e e
23 1 8 2003-09-08 2003-09-08 05:45:11 05:45:11 2007-10-26
11:41:40 2007-10-26 11:41:40 j j
24 0 6 2006-12-23 2006-12-23 00:00:00 00:00:00 2005-10-07
00:00:00 2005-10-07 00:00:00 e e
25 210 51 2006-10-15 2006-10-15 00:00:00 00:00:00 2000-07-15
05:00:34 2000-07-15 05:00:34 f f
26 8 4 2005-04-06 2005-04-06 06:11:01 06:11:01 2000-04-03
16:33:32 2000-04-03 16:33:32 v v
27 7 7 2008-04-07 2008-04-07 13:02:46 13:02:46 \N \N x x
28 5 6 2006-10-10 2006-10-10 21:44:25 21:44:25 2001-04-25
01:26:12 2001-04-25 01:26:12 m m
29 \N 4 1900-01-01 1900-01-01 22:43:58 22:43:58 2000-12-27
00:00:00 2000-12-27 00:00:00 c c
\.

--
-- Name: c_pkey; Type: CONSTRAINT; Schema: public; Owner: NNuser;
Tablespace:
--

ALTER TABLE ONLY c
ADD CONSTRAINT c_pkey PRIMARY KEY (pk);

--
-- Name: cc_pkey; Type: CONSTRAINT; Schema: public; Owner: NNuser;
Tablespace:
--

ALTER TABLE ONLY cc
ADD CONSTRAINT cc_pkey PRIMARY KEY (pk);

--
-- Name: c_date_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:

--

CREATE INDEX c_date_key ON c USING btree (date_key);

--
-- Name: c_datetime_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX c_datetime_key ON c USING btree (datetime_key);

--
-- Name: c_int_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:

--

CREATE INDEX c_int_key ON c USING btree (int_key);

--
-- Name: c_time_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:

--

CREATE INDEX c_time_key ON c USING btree (time_key);

--
-- Name: c_varchar_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX c_varchar_key ON c USING btree (varchar_key, int_key);

--
-- Name: cc_date_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX cc_date_key ON cc USING btree (date_key);

--
-- Name: cc_datetime_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX cc_datetime_key ON cc USING btree (datetime_key);

--
-- Name: cc_int_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:

--

CREATE INDEX cc_int_key ON cc USING btree (int_key);

--
-- Name: cc_time_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX cc_time_key ON cc USING btree (time_key);

--
-- Name: cc_varchar_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--

CREATE INDEX cc_varchar_key ON cc USING btree (varchar_key, int_key);

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--