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.johnsen@sun.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
--
Bernt Marius Johnsen wrote:
Dump of the database:
It looks like the dump got word-wrapped somewhere along the way. Could
you gzip it and post it again, please?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote (2009-09-28 18:10:07):
Bernt Marius Johnsen wrote:
Dump of the database:
Attached.
Show quoted text
It looks like the dump got word-wrapped somewhere along the way. Could
you gzip it and post it again, please?--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Attachments:
Bernt Marius Johnsen wrote:
Dump of the database:
To save anyone else the bother, there's a VASTLY simpler testcase for
this one, requiring no tables at all:
test1=# explain select * from (values (1),(null)) v(k) where k = k order by k;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=0.04..0.04 rows=2 width=4)
Sort Key: "*VALUES*".column1
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4)
(3 rows)
test1=# explain select * from (values (1),(null)) v(k) where k = k;
QUERY PLAN
-------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=4)
Filter: (column1 = column1)
(2 rows)
Notice that the (k = k) qual is being dropped somewhere, which changes
the output since that's a disguised not-null condition.
--
Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
Notice that the (k = k) qual is being dropped somewhere, which changes
the output since that's a disguised not-null condition.
Huh ... I'm surprised it only does that with the ORDER BY present.
I suppose it's got something to do with the equivalence-class machinery.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
Notice that the (k = k) qual is being dropped somewhere, which changes
the output since that's a disguised not-null condition.
Huh ... I'm surprised it only does that with the ORDER BY present.
I suppose it's got something to do with the equivalence-class machinery.
After digging into it, I find that:
1. Without ORDER BY, process_equivalence generates an equivalence class
that lists k twice. This is pretty bogus but it happens to produce the
desired results in the example at hand. (In some other cases you'll get
redundant clauses out, because the eclass machinery isn't expecting
this.)
2. With ORDER BY k, the code first creates a single-element equivalence
class containing k, because it needs that to represent the desired
pathkey. Then, process_equivalence finds that both sides of the k = k
clause are already known to be in the same eclass, so it concludes that
this is redundant information.
I'm inclined to think that the best solution is to have
process_equivalence just reject any clauses that have equal() left and
right sides, ie, throw them back to be processed as ordinary
non-equivalence clauses. The only case I can think of where this might
be less than ideal is if you have "k = k AND k = x"; if both operators
are strict then the k = k test is indeed redundant and could be
discarded, but it won't be. But it doesn't seem like that's going to
come up enough to be worth stressing about. If we wanted to be smart
about it we'd have to have two kinds of single-element equivalence
classes (one that implies a k = k check is needed, and one that does
not). It doesn't seem worth the complication.
regards, tom lane
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> After digging into it, I find that:
Tom> 1. Without ORDER BY, process_equivalence generates an
Tom> equivalence class that lists k twice. This is pretty bogus but
Tom> it happens to produce the desired results in the example at
Tom> hand. (In some other cases you'll get redundant clauses out,
Tom> because the eclass machinery isn't expecting this.)
Tom> 2. With ORDER BY k, the code first creates a single-element
Tom> equivalence class containing k, because it needs that to
Tom> represent the desired pathkey. Then, process_equivalence finds
Tom> that both sides of the k = k clause are already known to be in
Tom> the same eclass, so it concludes that this is redundant
Tom> information.
I'd found the right place in the code, but I hadn't twigged that the
ORDER BY one was being called _first_, so I hadn't spotted the bug yet.
Tom> I'm inclined to think that the best solution is to have
Tom> process_equivalence just reject any clauses that have equal()
Tom> left and right sides, ie, throw them back to be processed as
Tom> ordinary non-equivalence clauses. The only case I can think of
Tom> where this might be less than ideal is if you have "k = k AND k
Tom> = x"; if both operators are strict then the k = k test is indeed
Tom> redundant and could be discarded, but it won't be. But it
Tom> doesn't seem like that's going to come up enough to be worth
Tom> stressing about. If we wanted to be smart about it we'd have to
Tom> have two kinds of single-element equivalence classes (one that
Tom> implies a k = k check is needed, and one that does not). It
Tom> doesn't seem worth the complication.
Hmm. Is it ever possible for mergejoinable operators to be non-strict?
Does that matter?
--
Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I'm inclined to think that the best solution is to have
Tom> process_equivalence just reject any clauses that have equal()
Tom> left and right sides, ie, throw them back to be processed as
Tom> ordinary non-equivalence clauses.
Hmm. Is it ever possible for mergejoinable operators to be non-strict?
Does that matter?
I'm not sure. ISTR that nodeMergejoin makes some effort to support such
operators, but the btree code doesn't really. In any case, it doesn't
matter. Leaving the clause out of the equivalence machinery is
certainly safe; at worst we'll end up with a redundant test or two in
the final plan.
regards, tom lane
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> I'm inclined to think that the best solution is to have
Tom> process_equivalence just reject any clauses that have equal()
Tom> left and right sides, ie, throw them back to be processed as
Tom> ordinary non-equivalence clauses.
Hmm. Is it ever possible for mergejoinable operators to be
non-strict? Does that matter?
Tom> I'm not sure. ISTR that nodeMergejoin makes some effort to
Tom> support such operators, but the btree code doesn't really. In
Tom> any case, it doesn't matter. Leaving the clause out of the
Tom> equivalence machinery is certainly safe; at worst we'll end up
Tom> with a redundant test or two in the final plan.
Yeah, and clearly leaving in that kind of redundant test is no big
deal.
--
Andrew (irc:RhodiumToad)