Fix ExecSubPlan to handle nulls per the SQL spec --- it didn't combine

nulls with non-nulls using proper three-valued boolean logic.  Also clean
up ExecQual to make it clearer that ExecQual *does* follow the SQL spec
for boolean nulls.  See '[BUGS] (null) != (null)' thread around 10/26/99
for more detail.

1 files changed, 112 insertions, 46 deletions

diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c
index 32a39ee18d9..452e3414b3c 100644
--- a/src/backend/executor/nodeSubplan.c
+++ b/src/backend/executor/nodeSubplan.c
@@ -18,34 +18,39 @@
 #include "executor/nodeSubplan.h"
 #include "tcop/pquery.h"
 
+/* should be exported by execMain.c */
+extern void ExecCheckPerms(CmdType op, int resRel, List *rtable, Query *q);
+
 /* ----------------------------------------------------------------
  *		ExecSubPlan(node)
  *
  * ----------------------------------------------------------------
  */
 Datum
-ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext)
+ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext, bool *isNull)
 {
 	Plan	   *plan = node->plan;
 	SubLink    *sublink = node->sublink;
 	SubLinkType subLinkType = sublink->subLinkType;
+	bool		useor = sublink->useor;
 	TupleTableSlot *slot;
-	List	   *lst;
-	Datum		result = (Datum) false;
+	Datum		result;
 	bool		found = false;	/* TRUE if got at least one subplan tuple */
+	List	   *lst;
 
-	if (node->setParam != NULL)
+	if (node->setParam != NIL)
 		elog(ERROR, "ExecSubPlan: can't set parent params from subquery");
 
 	/*
 	 * Set Params of this plan from parent plan correlation Vars
 	 */
-	if (node->parParam != NULL)
+	if (node->parParam != NIL)
 	{
 		foreach(lst, node->parParam)
 		{
 			ParamExecData *prm = &(econtext->ecxt_param_exec_vals[lfirsti(lst)]);
 
+			Assert(pvar != NIL);
 			prm->value = ExecEvalExpr((Node *) lfirst(pvar),
 									  econtext,
 									  &(prm->isnull), NULL);
@@ -53,21 +58,32 @@ ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext)
 		}
 		plan->chgParam = nconc(plan->chgParam, listCopy(node->parParam));
 	}
+	Assert(pvar == NIL);
 
 	ExecReScan(plan, (ExprContext *) NULL, plan);
 
 	/*
-	 * For all sublink types except EXPR_SUBLINK, the result type is
-	 * boolean, and we have a fairly clear idea of how to combine multiple
-	 * subitems and deal with NULL values or an empty subplan result.
+	 * For all sublink types except EXPR_SUBLINK, the result is boolean
+	 * as are the results of the combining operators.  We combine results
+	 * within a tuple (if there are multiple columns) using OR semantics
+	 * if "useor" is true, AND semantics if not.  We then combine results
+	 * across tuples (if the subplan produces more than one) using OR
+	 * semantics for ANY_SUBLINK or AND semantics for ALL_SUBLINK.  NULL
+	 * results from the combining operators are handled according to the
+	 * usual SQL semantics for OR and AND.  The result for no input
+	 * tuples is FALSE for ANY_SUBLINK, TRUE for ALL_SUBLINK.
 	 *
-	 * For EXPR_SUBLINK, the result type is whatever the combining operator
-	 * returns.  We have no way to deal with more than one column in the
-	 * subplan result --- hopefully the parser forbids that.  More
-	 * seriously, it's unclear what to do with NULL values or an empty
-	 * subplan result. For now, we error out, but should something else
-	 * happen?
+	 * For EXPR_SUBLINK we require the subplan to produce no more than one
+	 * tuple, else an error is raised.  If zero tuples are produced, we
+	 * return NULL.  (XXX it would probably be more correct to evaluate
+	 * the combining operator with a NULL input?)  Assuming we get a tuple:
+	 * if there is only one column then we just return its result as-is, NULL
+	 * or otherwise.  If there is more than one column we combine the results
+	 * per "useor" --- this only makes sense if the combining operators yield
+	 * boolean, and we assume the parser has checked that.
 	 */
+	result = (Datum) (subLinkType == ALL_SUBLINK ? true : false);
+	*isNull = false;
 
 	for (slot = ExecProcNode(plan, plan);
 		 !TupIsNull(slot);
@@ -75,24 +91,26 @@ ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext)
 	{
 		HeapTuple	tup = slot->val;
 		TupleDesc	tdesc = slot->ttc_tupleDescriptor;
-		int			i = 1;
-
-		if (subLinkType == EXPR_SUBLINK && found)
-		{
-			elog(ERROR, "ExecSubPlan: more than one tuple returned by expression subselect");
-			return (Datum) false;
-		}
+		Datum		rowresult = (Datum) (useor ? false : true);
+		bool		rownull = false;
+		int			col = 1;
 
 		if (subLinkType == EXISTS_SUBLINK)
 			return (Datum) true;
 
+		/* cannot allow multiple input tuples for EXPR sublink */
+		if (subLinkType == EXPR_SUBLINK && found)
+			elog(ERROR, "ExecSubPlan: more than one tuple returned by expression subselect");
+
 		found = true;
 
+		/* iterate over combining operators for columns of tuple */
 		foreach(lst, sublink->oper)
 		{
 			Expr	   *expr = (Expr *) lfirst(lst);
 			Const	   *con = lsecond(expr->args);
-			bool		isnull;
+			Datum		expresult;
+			bool		expnull;
 
 			/*
 			 * The righthand side of the expression should be either a Const
@@ -107,41 +125,90 @@ ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext)
 				con = lfirst(((Expr *) con)->args);
 				Assert(IsA(con, Const));
 			}
-			con->constvalue = heap_getattr(tup, i, tdesc, &(con->constisnull));
+			con->constvalue = heap_getattr(tup, col, tdesc,
+										   &(con->constisnull));
 			/*
-			 * Now we can eval the expression.
+			 * Now we can eval the combining operator for this column.
 			 */
-			result = ExecEvalExpr((Node *) expr, econtext, &isnull,
-								  (bool *) NULL);
-			if (isnull)
+			expresult = ExecEvalExpr((Node *) expr, econtext, &expnull,
+									 (bool *) NULL);
+			/*
+			 * Combine the result into the row result as appropriate.
+			 */
+			if (col == 1)
 			{
-				if (subLinkType == EXPR_SUBLINK)
-					elog(ERROR, "ExecSubPlan: null value returned by expression subselect");
-				else
-					result = (Datum) false;
+				rowresult = expresult;
+				rownull = expnull;
 			}
-			if (subLinkType != EXPR_SUBLINK)
+			else if (useor)
 			{
-				if ((!(bool) result && !(sublink->useor)) ||
-					((bool) result && sublink->useor))
-					break;
+				/* combine within row per OR semantics */
+				if (expnull)
+					rownull = true;
+				else if (DatumGetInt32(expresult) != 0)
+				{
+					rowresult = (Datum) true;
+					rownull = false;
+					break;		/* needn't look at any more columns */
+				}
 			}
-			i++;
+			else
+			{
+				/* combine within row per AND semantics */
+				if (expnull)
+					rownull = true;
+				else if (DatumGetInt32(expresult) == 0)
+				{
+					rowresult = (Datum) false;
+					rownull = false;
+					break;		/* needn't look at any more columns */
+				}
+			}
+			col++;
 		}
 
-		if (subLinkType == ALL_SUBLINK && !(bool) result)
-			break;
-		if (subLinkType == ANY_SUBLINK && (bool) result)
-			break;
+		if (subLinkType == ANY_SUBLINK)
+		{
+			/* combine across rows per OR semantics */
+			if (rownull)
+				*isNull = true;
+			else if (DatumGetInt32(rowresult) != 0)
+			{
+				result = (Datum) true;
+				*isNull = false;
+				break;			/* needn't look at any more rows */
+			}
+		}
+		else if (subLinkType == ALL_SUBLINK)
+		{
+			/* combine across rows per AND semantics */
+			if (rownull)
+				*isNull = true;
+			else if (DatumGetInt32(rowresult) == 0)
+			{
+				result = (Datum) false;
+				*isNull = false;
+				break;			/* needn't look at any more rows */
+			}
+		}
+		else
+		{
+			/* must be EXPR_SUBLINK */
+			result = rowresult;
+			*isNull = rownull;
+		}
 	}
 
 	if (!found)
 	{
-		/* deal with empty subplan result.	Note default result is 'false' */
-		if (subLinkType == ALL_SUBLINK)
-			result = (Datum) true;
-		else if (subLinkType == EXPR_SUBLINK)
-			elog(ERROR, "ExecSubPlan: no tuples returned by expression subselect");
+		/* deal with empty subplan result.  result/isNull were previously
+		 * initialized correctly for all sublink types except EXPR.
+		 */
+		if (subLinkType == EXPR_SUBLINK)
+		{
+				result = (Datum) false;
+				*isNull = true;
+		}
 	}
 
 	return result;
@@ -152,7 +219,6 @@ ExecSubPlan(SubPlan *node, List *pvar, ExprContext *econtext)
  *
  * ----------------------------------------------------------------
  */
-extern void ExecCheckPerms(CmdType op, int resRel, List *rtable, Query *q);
 bool
 ExecInitSubPlan(SubPlan *node, EState *estate, Plan *parent)
 {