Adjust parser so that 'x NOT IN (subselect)' is converted to

'NOT (x IN (subselect))', that is 'NOT (x = ANY (subselect))',
rather than 'x <> ALL (subselect)' as we formerly did.  This
opens the door to optimizing NOT IN the same way as IN, whereas
there's no hope of optimizing the expression using <>.  Also,
convert 'x <> ALL (subselect)' to the NOT(IN) style, so that
the optimization will be available when processing rules dumped
by older Postgres versions.
initdb forced due to small change in SubLink node representation.

2 files changed, 65 insertions, 27 deletions

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1809d515461..7626fe0e449 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.391 2003/01/08 00:22:27 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.392 2003/01/09 20:50:51 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -5420,28 +5420,30 @@ opt_interval:
 
 /* Expressions using row descriptors
  * Define row_descriptor to allow yacc to break the reduce/reduce conflict
- *	with singleton expressions. Use SQL99's ROW keyword to allow rows of
- *  one element.
+ * with singleton expressions. Use SQL99's ROW keyword to allow rows of
+ * one element.
  */
 r_expr:  row IN_P select_with_parens
 				{
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $1;
 					n->oper = (List *) makeSimpleA_Expr(OP, "=", NULL, NULL);
-					n->useor = FALSE;
 					n->subLinkType = ANY_SUBLINK;
+					/* operIsEquals and useOr will be set later */
 					n->subselect = $3;
 					$$ = (Node *)n;
 				}
 			| row NOT IN_P select_with_parens
 				{
+					/* Make an IN node */
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $1;
-					n->oper = (List *) makeSimpleA_Expr(OP, "<>", NULL, NULL);
-					n->useor = TRUE;
-					n->subLinkType = ALL_SUBLINK;
+					n->oper = (List *) makeSimpleA_Expr(OP, "=", NULL, NULL);
+					n->subLinkType = ANY_SUBLINK;
+					/* operIsEquals and useOr will be set later */
 					n->subselect = $4;
-					$$ = (Node *)n;
+					/* Stick a NOT on top */
+					$$ = (Node *) makeA_Expr(NOT, NIL, NULL, (Node *) n);
 				}
 			| row qual_all_Op sub_type select_with_parens
 			%prec Op
@@ -5449,11 +5451,8 @@ r_expr:  row IN_P select_with_parens
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $1;
 					n->oper = (List *) makeA_Expr(OP, $2, NULL, NULL);
-					if (strcmp(strVal(llast($2)), "<>") == 0)
-						n->useor = TRUE;
-					else
-						n->useor = FALSE;
 					n->subLinkType = $3;
+					/* operIsEquals and useOr will be set later */
 					n->subselect = $4;
 					$$ = (Node *)n;
 				}
@@ -5463,11 +5462,8 @@ r_expr:  row IN_P select_with_parens
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = $1;
 					n->oper = (List *) makeA_Expr(OP, $2, NULL, NULL);
-					if (strcmp(strVal(llast($2)), "<>") == 0)
-						n->useor = TRUE;
-					else
-						n->useor = FALSE;
 					n->subLinkType = MULTIEXPR_SUBLINK;
+					/* operIsEquals and useOr will be set later */
 					n->subselect = $3;
 					$$ = (Node *)n;
 				}
@@ -5850,8 +5846,8 @@ a_expr:		c_expr									{ $$ = $1; }
 							SubLink *n = (SubLink *)$3;
 							n->lefthand = makeList1($1);
 							n->oper = (List *) makeSimpleA_Expr(OP, "=", NULL, NULL);
-							n->useor = FALSE;
 							n->subLinkType = ANY_SUBLINK;
+							/* operIsEquals and useOr will be set later */
 							$$ = (Node *)n;
 					}
 					else
@@ -5875,12 +5871,14 @@ a_expr:		c_expr									{ $$ = $1; }
 					/* in_expr returns a SubLink or a list of a_exprs */
 					if (IsA($4, SubLink))
 					{
+						/* Make an IN node */
 						SubLink *n = (SubLink *)$4;
 						n->lefthand = makeList1($1);
-						n->oper = (List *) makeSimpleA_Expr(OP, "<>", NULL, NULL);
-						n->useor = FALSE;
-						n->subLinkType = ALL_SUBLINK;
-						$$ = (Node *)n;
+						n->oper = (List *) makeSimpleA_Expr(OP, "=", NULL, NULL);
+						n->subLinkType = ANY_SUBLINK;
+						/* operIsEquals and useOr will be set later */
+						/* Stick a NOT on top */
+						$$ = (Node *) makeA_Expr(NOT, NIL, NULL, (Node *) n);
 					}
 					else
 					{
@@ -5903,8 +5901,8 @@ a_expr:		c_expr									{ $$ = $1; }
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = makeList1($1);
 					n->oper = (List *) makeA_Expr(OP, $2, NULL, NULL);
-					n->useor = FALSE; /* doesn't matter since only one col */
 					n->subLinkType = $3;
+					/* operIsEquals and useOr will be set later */
 					n->subselect = $4;
 					$$ = (Node *)n;
 				}
@@ -6447,7 +6445,6 @@ c_expr:		columnref								{ $$ = (Node *) $1; }
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = NIL;
 					n->oper = NIL;
-					n->useor = FALSE;
 					n->subLinkType = EXPR_SUBLINK;
 					n->subselect = $1;
 					$$ = (Node *)n;
@@ -6457,7 +6454,6 @@ c_expr:		columnref								{ $$ = (Node *) $1; }
 					SubLink *n = makeNode(SubLink);
 					n->lefthand = NIL;
 					n->oper = NIL;
-					n->useor = FALSE;
 					n->subLinkType = EXISTS_SUBLINK;
 					n->subselect = $2;
 					$$ = (Node *)n;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9705ca5d7de..3701f41dca0 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.138 2002/12/27 20:06:19 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v 1.139 2003/01/09 20:50:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -367,6 +367,8 @@ transformExpr(ParseState *pstate, Node *expr)
 					 */
 					sublink->lefthand = NIL;
 					sublink->oper = NIL;
+					sublink->operIsEquals = FALSE;
+					sublink->useOr = FALSE;
 				}
 				else if (sublink->subLinkType == EXPR_SUBLINK)
 				{
@@ -391,27 +393,60 @@ transformExpr(ParseState *pstate, Node *expr)
 					 */
 					sublink->lefthand = NIL;
 					sublink->oper = NIL;
+					sublink->operIsEquals = FALSE;
+					sublink->useOr = FALSE;
 				}
 				else
 				{
 					/* ALL, ANY, or MULTIEXPR: generate operator list */
 					List	   *left_list = sublink->lefthand;
 					List	   *right_list = qtree->targetList;
+					int			row_length = length(left_list);
+					bool		needNot = false;
 					List	   *op;
 					char	   *opname;
 					List	   *elist;
 
+					/* transform lefthand expressions */
 					foreach(elist, left_list)
 						lfirst(elist) = transformExpr(pstate, lfirst(elist));
 
+					/* get the combining-operator name */
 					Assert(IsA(sublink->oper, A_Expr));
 					op = ((A_Expr *) sublink->oper)->name;
 					opname = strVal(llast(op));
 					sublink->oper = NIL;
 
+					/*
+					 * If the expression is "<> ALL" (with unqualified opname)
+					 * then convert it to "NOT IN".  This is a hack to improve
+					 * efficiency of expressions output by pre-7.4 Postgres.
+					 */
+					if (sublink->subLinkType == ALL_SUBLINK &&
+						length(op) == 1 && strcmp(opname, "<>") == 0)
+					{
+						sublink->subLinkType = ANY_SUBLINK;
+						opname = pstrdup("=");
+						op = makeList1(makeString(opname));
+						needNot = true;
+					}
+
+					/* Set operIsEquals if op is unqualified "=" */
+					if (length(op) == 1 && strcmp(opname, "=") == 0)
+						sublink->operIsEquals = TRUE;
+					else
+						sublink->operIsEquals = FALSE;
+
+					/* Set useOr if op is "<>" (possibly qualified) */
+					if (strcmp(opname, "<>") == 0)
+						sublink->useOr = TRUE;
+					else
+						sublink->useOr = FALSE;
+
 					/* Combining operators other than =/<> is dubious... */
-					if (length(left_list) != 1 &&
-					strcmp(opname, "=") != 0 && strcmp(opname, "<>") != 0)
+					if (row_length != 1 &&
+						strcmp(opname, "=") != 0 &&
+						strcmp(opname, "<>") != 0)
 						elog(ERROR, "Row comparison cannot use operator %s",
 							 opname);
 
@@ -474,6 +509,13 @@ transformExpr(ParseState *pstate, Node *expr)
 					}
 					if (left_list != NIL)
 						elog(ERROR, "Subselect has too few fields");
+
+					if (needNot)
+					{
+						expr = coerce_to_boolean(expr, "NOT");
+						expr = (Node *) makeBoolExpr(NOT_EXPR,
+													 makeList1(expr));
+					}
 				}
 				result = (Node *) expr;
 				break;