"Bernt Marius Johnsen" <bernt.john...@sun.com> writes:
> Description: Query gives different number of rows depending on ORDER
> BY
The attached patch should fix this.
regards, tom lane
Index: src/backend/optimizer/README
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/README,v
retrieving revision 1.41
diff -c -r1.41 README
*** src/backend/optimizer/README 26 Oct 2007 18:10:50 -0000 1.41
--- src/backend/optimizer/README 29 Sep 2009 01:01:55 -0000
***************
*** 467,473 ****
get into EquivalenceClasses otherwise. Aggregates are disallowed in WHERE
altogether, so will never be found in a mergejoinable clause.) This is just
a convenience to maintain a uniform PathKey representation: such an
! EquivalenceClass will never be merged with any other.
An EquivalenceClass also contains a list of btree opfamily OIDs, which
determines what the equalities it represents actually "mean". All the
--- 467,476 ----
get into EquivalenceClasses otherwise. Aggregates are disallowed in WHERE
altogether, so will never be found in a mergejoinable clause.) This is just
a convenience to maintain a uniform PathKey representation: such an
! EquivalenceClass will never be merged with any other. Note in particular
! that a single-item EquivalenceClass {a.x} is *not* meant to imply an
! assertion that a.x = a.x; the practical effect of this is that a.x could
! be NULL.
An EquivalenceClass also contains a list of btree opfamily OIDs, which
determines what the equalities it represents actually "mean". All the
Index: src/backend/optimizer/path/equivclass.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/equivclass.c,v
retrieving revision 1.9.2.2
diff -c -r1.9.2.2 equivclass.c
*** src/backend/optimizer/path/equivclass.c 1 Dec 2008 21:06:20 -0000 1.9.2.2
--- src/backend/optimizer/path/equivclass.c 29 Sep 2009 01:01:55 -0000
***************
*** 114,119 ****
--- 114,132 ----
item2_relids = restrictinfo->right_relids;
/*
+ * Reject clauses of the form X=X. These are not as redundant as they
+ * might seem at first glance: assuming the operator is strict, this is
+ * really an expensive way to write X IS NOT NULL. So we must not risk
+ * just losing the clause, which would be possible if there is already
+ * a single-element EquivalenceClass containing X. The case is not
+ * common enough to be worth contorting the EC machinery for, so just
+ * reject the clause and let it be processed as a normal restriction
+ * clause.
+ */
+ if (equal(item1, item2))
+ return false; /* X=X is not a useful equivalence */
+
+ /*
* If below outer join, check for strictness, else reject.
*/
if (below_outer_join)
***************
*** 151,163 ****
*
* 4. We find neither. Make a new, two-entry EC.
*
! * Note: since all ECs are built through this process, it's impossible
! * that we'd match an item in more than one existing EC. It is possible
! * to match more than once within an EC, if someone fed us something silly
! * like "WHERE X=X". (However, we can't simply discard such clauses,
! * since they should fail when X is null; so we will build a 2-member EC
! * to ensure the correct restriction clause gets generated. Hence there
! * is no shortcut here for item1 and item2 equal.)
*/
ec1 = ec2 = NULL;
em1 = em2 = NULL;
--- 164,173 ----
*
* 4. We find neither. Make a new, two-entry EC.
*
! * Note: since all ECs are built through this process or the similar
! * search in get_eclass_for_sort_expr(), it's impossible that we'd match
! * an item in more than one existing nonvolatile EC. So it's okay to stop
! * at the first match.
*/
ec1 = ec2 = NULL;
em1 = em2 = NULL;
Index: src/test/regress/expected/select.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/select.out,v
retrieving revision 1.18
diff -c -r1.18 select.out
*** src/test/regress/expected/select.out 7 Jul 2007 20:46:45 -0000 1.18
--- src/test/regress/expected/select.out 29 Sep 2009 01:01:55 -0000
***************
*** 768,770 ****
--- 768,786 ----
(4 rows)
drop function sillysrf(int);
+ -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+ -- (see bug #5084)
+ select * from (values (2),(null),(1)) v(k) where k = k order by k;
+ k
+ ---
+ 1
+ 2
+ (2 rows)
+
+ select * from (values (2),(null),(1)) v(k) where k = k;
+ k
+ ---
+ 2
+ 1
+ (2 rows)
+
Index: src/test/regress/sql/select.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/select.sql,v
retrieving revision 1.14
diff -c -r1.14 select.sql
*** src/test/regress/sql/select.sql 7 Jul 2007 20:46:45 -0000 1.14
--- src/test/regress/sql/select.sql 29 Sep 2009 01:01:55 -0000
***************
*** 202,204 ****
--- 202,209 ----
select sillysrf(-1) order by 1;
drop function sillysrf(int);
+
+ -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+ -- (see bug #5084)
+ select * from (values (2),(null),(1)) v(k) where k = k order by k;
+ select * from (values (2),(null),(1)) v(k) where k = k;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
