Allow merge and hash joins to occur on arbitrary expressions (anything not

containing a volatile function), rather than only on 'Var = Var' clauses
as before.  This makes it practical to do flatten_join_alias_vars at the
start of planning, which in turn eliminates a bunch of klugery inside the
planner to deal with alias vars.  As a free side effect, we now detect
implied equality of non-Var expressions; for example in
	SELECT ... WHERE a.x = b.y and b.y = 42
we will deduce a.x = 42 and use that as a restriction qual on a.  Also,
we can remove the restriction introduced 12/5/02 to prevent pullup of
subqueries whose targetlists contain sublinks.
Still TODO: make statistical estimation routines in selfuncs.c and costsize.c
smarter about expressions that are more complex than plain Vars.  The need
for this is considerably greater now that we have to be able to estimate
the suitability of merge and hash join techniques on such expressions.

4 files changed, 32 insertions, 6 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 3decbdb7991..7ef807a95db 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -498,6 +498,17 @@ WHERE p1.oprcode = p2.oid AND
 -----+---------+-----+---------
 (0 rows)
 
+-- If the operator is mergejoinable or hashjoinable, its underlying function
+-- should not be volatile.
+SELECT p1.oid, p1.oprname, p2.oid, p2.proname
+FROM pg_operator AS p1, pg_proc AS p2
+WHERE p1.oprcode = p2.oid AND
+    (p1.oprlsortop != 0 OR p1.oprcanhash) AND
+    p2.provolatile = 'v';
+ oid | oprname | oid | proname 
+-----+---------+-----+---------
+(0 rows)
+
 -- If oprrest is set, the operator must return boolean,
 -- and it must link to a proc with the right signature
 -- to be a restriction selectivity estimator.
@@ -583,7 +594,8 @@ WHERE a.aggfnoid = p.oid AND
      a.aggtranstype != p2.prorettype OR
      a.aggtranstype != p2.proargtypes[0] OR
      NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR
-          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)));
+          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)))
+ORDER BY 1;
  aggfnoid | proname | oid |   proname   
 ----------+---------+-----+-------------
      2121 | max     | 768 | int4larger
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index 46c153e5d14..8f943188d24 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -107,7 +107,8 @@ SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2
 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     (p1.typelem != 0 AND p1.typlen < 0) AND NOT
-    (p2.prorettype = p1.oid AND NOT p2.proretset);
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
  oid  |  typname  | oid |  proname  
 ------+-----------+-----+-----------
    32 | SET       | 109 | unknownin
@@ -132,7 +133,8 @@ FROM pg_type AS p1, pg_proc AS p2
 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     ((p2.pronargs = 1 AND p2.proargtypes[0] = p1.oid) OR
      (p2.oid = 'array_out'::regproc AND
-      p1.typelem != 0 AND p1.typlen = -1));
+      p1.typelem != 0 AND p1.typlen = -1))
+ORDER BY 1;
  oid  |  typname  | oid |  proname   
 ------+-----------+-----+------------
    32 | SET       | 110 | unknownout
diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql
index faacc83850e..650073cccc1 100644
--- a/src/test/regress/sql/opr_sanity.sql
+++ b/src/test/regress/sql/opr_sanity.sql
@@ -416,6 +416,15 @@ WHERE p1.oprcode = p2.oid AND
      (p1.oprleft != p2.proargtypes[0] AND p2.proargtypes[0] != 0) OR
      p1.oprright != 0);
 
+-- If the operator is mergejoinable or hashjoinable, its underlying function
+-- should not be volatile.
+
+SELECT p1.oid, p1.oprname, p2.oid, p2.proname
+FROM pg_operator AS p1, pg_proc AS p2
+WHERE p1.oprcode = p2.oid AND
+    (p1.oprlsortop != 0 OR p1.oprcanhash) AND
+    p2.provolatile = 'v';
+
 -- If oprrest is set, the operator must return boolean,
 -- and it must link to a proc with the right signature
 -- to be a restriction selectivity estimator.
@@ -490,7 +499,8 @@ WHERE a.aggfnoid = p.oid AND
      a.aggtranstype != p2.prorettype OR
      a.aggtranstype != p2.proargtypes[0] OR
      NOT ((p2.pronargs = 2 AND p.proargtypes[0] = p2.proargtypes[1]) OR
-          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)));
+          (p2.pronargs = 1 AND p.proargtypes[0] = '"any"'::regtype)))
+ORDER BY 1;
 
 -- Cross-check finalfn (if present) against its entry in pg_proc.
 -- FIXME: what about binary-compatible types?
diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql
index 0cc4748fa89..f0ffa5984dc 100644
--- a/src/test/regress/sql/type_sanity.sql
+++ b/src/test/regress/sql/type_sanity.sql
@@ -90,7 +90,8 @@ SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2
 WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     (p1.typelem != 0 AND p1.typlen < 0) AND NOT
-    (p2.prorettype = p1.oid AND NOT p2.proretset);
+    (p2.prorettype = p1.oid AND NOT p2.proretset)
+ORDER BY 1;
 
 -- Varlena array types will point to array_in
 SELECT p1.oid, p1.typname, p2.oid, p2.proname
@@ -108,7 +109,8 @@ FROM pg_type AS p1, pg_proc AS p2
 WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     ((p2.pronargs = 1 AND p2.proargtypes[0] = p1.oid) OR
      (p2.oid = 'array_out'::regproc AND
-      p1.typelem != 0 AND p1.typlen = -1));
+      p1.typelem != 0 AND p1.typlen = -1))
+ORDER BY 1;
 
 SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2