Re: BUG #5084: Query gives different number of rows depending on ORDER BY - Mailing list pgsql-bugs

"Bernt Marius Johnsen" <bernt.johnsen@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;