subquerynode.java

derby database source code.good for you.

			else
			{
				retCode = false;
			}
		}

		return retCode;
	}

	/**
	 * Can NOT IN, ALL be falttened to NOT EXISTS join?  We can't or the flattening doesn't
	 * easily make sense if either side of the comparison is nullable. (beetle 5173)
	 *
	 * @return Whether or not the NOT IN or ALL subquery can be flattened.
	 */
	private boolean canAllBeFlattened ()
	{
		boolean result = false;
		if (isNOT_IN() || isALL())
		{
			ValueNode rightOperand = ((ResultColumn) resultSet.getResultColumns().elementAt(0)).
									getExpression();
			result = (! leftOperand.getTypeServices().isNullable() &&
						! rightOperand.getTypeServices().isNullable());
		}
		return result;
	}

	/**
	 * Flatten this subquery into the outer query block.  
	 * At this point we are only flattening based on a uniqueness
	 * condition and only flattening non-aggregate subqueries.
	 * So, we promote the subquery's from list, as is, into 
	 * the outer from list.  For EXISTS subquerys, we return a 
	 * TRUE.  Otherwise we return a new comparison between
	 * the leftOperand and the expression in the subquery's
	 * SELECT list.
	 * RESOLVE - we will need to modify this logic to account
	 * for exists joins and aggregates as we support flattening
	 * for them.
	 *
	 * Anyway, here's what we do:
	 *	o We remove ourself from the outer subquery list.
	 *	o We decrement the nesting level for all tables
	 *	  in the subquery tree.
	 *	o We append the subquery's from list to the outer
	 *	  from list.
	 *	o We add the subquery's predicate list to the outer
	 *	  predicate list.  (The subquery has already been
	 *	  preprocessed.)
	 *  o We add the subquery's subquery list to the outer
	 *	  subquery list.
	 *	o For EXISTS, we return a true.
	 *	o Otherwise, we return a new comparison between the
	 *	  leftOperand and the expression in the inner select's
	 *	  RCL.
	 *
	 * @param	numTables			Number of tables in the DML Statement
	 * @param	outerFromList		FromList from outer query block
	 * @param	outerSubqueryList	SubqueryList from outer query block
	 * @param	outerPredicateList	PredicateList from outer query block
	 *
	 * @return	The modified expression
	 *
	 * @exception StandardException		Thrown on error
	 */
	private ValueNode flattenToNormalJoin(int numTables,
										  FromList outerFromList, 
										  SubqueryList outerSubqueryList,
										  PredicateList outerPredicateList)
		throws StandardException
	{
		SelectNode select = (SelectNode) resultSet;
		FromList   fl = select.getFromList();
		int[] tableNumbers = fl.getTableNumbers();

		// Remove ourselves from the outer subquery list
		outerSubqueryList.removeElement(this);

		/* Decrease the nesting level for all
		 * tables in the subquey tree.
		 */
		select.decrementLevel(1);

		/* Add the table(s) from the subquery into the outer from list */
		outerFromList.destructiveAppend(fl);

		/* Append the subquery's predicate list to the
		 * outer predicate list.
		 */
		outerPredicateList.destructiveAppend(select.getWherePredicates());

		/* Append the subquery's subquery list to the 
		 * outer subquery list.
		 * NOTE: We must propagate any subqueries from both the
		 * SELECT list and WHERE clause of the subquery that's
		 * getting flattened.
		 */
		outerSubqueryList.destructiveAppend(select.getWhereSubquerys());
		outerSubqueryList.destructiveAppend(select.getSelectSubquerys());

		/* return the new join condition 
		 * If we are flattening an EXISTS then there is no new join
		 * condition since there is no leftOperand.  Simply return
		 * TRUE.
		 *
		 * NOTE: The outer where clause, etc. has already been normalized,
		 * so we simply return the BinaryComparisonOperatorNode above
		 * the new join condition.
		 */
		if (leftOperand == null)
		{
			return (ValueNode) getNodeFactory().getNode(
											C_NodeTypes.BOOLEAN_CONSTANT_NODE,
											Boolean.TRUE,
											getContextManager());
		}
		else
		{
			ValueNode rightOperand;
			rightOperand = ((ResultColumn) select.getResultColumns().elementAt(0)).
								getExpression();
			/* If the right operand is a CR, then we need to decrement
			 * its source level as part of flattening so that
			 * transitive closure will work correctly.
			 */
			if (rightOperand instanceof ColumnReference)
			{
				ColumnReference cr = (ColumnReference) rightOperand;
				int tableNumber = cr.getTableNumber();
				for (int index = 0; index < tableNumbers.length; index++)
				{
					if (tableNumber == tableNumbers[index])
					{
						cr.setSourceLevel(
							cr.getSourceLevel() - 1);
						break;
					}
				}
			}
			return getNewJoinCondition(leftOperand, rightOperand);
		}
	}

	/**
	 * Flatten this subquery into the outer query block
	 * as an exists join.  
	 * At this point we are only flattening non-aggregate subqueries
	 * with a single FBT in the from list.
	 * So, we transform all FBTs in the from list into ExistBaseTables,
	 * update the dependency lists for each of the tables and then
	 * flatten the subquery.
	 * RESOLVE - we will need to modify this logic to account
	 * for aggregates as we support flattening
	 * for them.
	 *
	 * @param	numTables			Number of tables in the DML Statement
	 * @param	outerFromList		FromList from outer query block
	 * @param	outerSubqueryList	SubqueryList from outer query block
	 * @param	outerPredicateList	PredicateList from outer query block
	 * @param	flattenableNotExists Is it a flattening into a NOT EXISTS join
	 *
	 * @return	The modified expression
	 *
	 * @exception StandardException		Thrown on error
	 */
	private ValueNode flattenToExistsJoin(int numTables,
										  FromList outerFromList, 
										  SubqueryList outerSubqueryList,
										  PredicateList outerPredicateList,
										  boolean flattenableNotExists)
		throws StandardException
	{
		SelectNode select = (SelectNode) resultSet;

		// Replace the FromBaseTables in the from list with ExistBaseTables
		select.getFromList().genExistsBaseTables(resultSet.getReferencedTableMap(),
				outerFromList, flattenableNotExists);

		/* NOTE: Because we are currently only flattening single table subqueries
		 * whose predicates are all pushable, we simply follow the rest of the
		 * flattening algorithm for unique subqueries.  Should we decide to 
		 * loosen these restrictions then we need to do more work such as:
		 *
		 * Mark all of the predicates from the subquery as non-pullable. They must
		 * not be pulled so that we can guarantee correctness.  Otherwise, we could
		 * add or subtract rows from the result set.
		 *
		 * Remap all of the non-correlated CRs in the predicate list so that they
		 * point to the correct source.  (We've chopped a level out of the RCL/VCN
		 * chain.)  We then transfer those predicates to the PRN in the subquery's
		 * from list.
		 */

		return flattenToNormalJoin(numTables, outerFromList,
								   outerSubqueryList, outerPredicateList);
	}

	/**
	 * Check to see if we have a Variant value below us.
	 * If so, return true.  Caches the result so multiple
	 * calls are ok.
	 *  
	 * @return boolean whether we have 
	 *
	 * @exception StandardException		Thrown on error
	 */
	private boolean isInvariant() throws StandardException
	{
		if (doneInvariantCheck)
		{
			return !foundVariant;
		}

		doneInvariantCheck = true;
		HasVariantValueNodeVisitor visitor = new HasVariantValueNodeVisitor();
		resultSet.accept(visitor);
		foundVariant = visitor.hasVariant();
		return !foundVariant;
	}

	/**
	 * Check to see if this subquery has correlated
	 * column references.  Only useful results if
	 * called AFTER binding (after CRs have been bound).
	 *
	 * @return whether the subquery has correlated column
	 *	references.
	 * @exception StandardException		Thrown on error
	 */
	public boolean hasCorrelatedCRs() throws StandardException
	{
		if (doneCorrelationCheck)
		{
			return foundCorrelation;
		}
		doneCorrelationCheck = true;

		ResultSetNode realSubquery = resultSet;
		ResultColumnList oldRCL = null;

		/* If we have pushed the new join predicate on top, we want to disregard it
		 * to see if anything under the predicate is correlated.  If nothing correlated
		 * under the new join predicate, we could then materialize the subquery.
		 * See beetle 4373.
		 */
		if (pushedNewPredicate)
		{
			if (SanityManager.DEBUG)
			{
				SanityManager.ASSERT(resultSet instanceof ProjectRestrictNode,
					"resultSet expected to be a ProjectRestrictNode!");
			}

			realSubquery = ((ProjectRestrictNode) resultSet).getChildResult();
			oldRCL = realSubquery.getResultColumns();

			/* Only first column matters.
			 */
			if (oldRCL.size() > 1)
			{
				ResultColumnList newRCL = new ResultColumnList();
				newRCL.addResultColumn(oldRCL.getResultColumn(1));
				realSubquery.setResultColumns(newRCL);
			}
		}

		HasCorrelatedCRsVisitor visitor = new HasCorrelatedCRsVisitor();
		realSubquery.accept(visitor);
		foundCorrelation = visitor.hasCorrelatedCRs();

		if (pushedNewPredicate && (oldRCL.size() > 1))
		{
			realSubquery.setResultColumns(oldRCL);
		}

		return foundCorrelation;
	}
				
	/**
	 * Transform:
	 *		expresion QuantifiedOperator (select x from ...)
	 * into
	 *		(select true from .. where expression <BinaryComparisonOperator> x ...)
	 *		IS [NOT] NULL
	 *
	 * or, if we have an aggregate:
	 *		(select true from 
	 *			(select AGG(x) from ...)
	 *		where expression <BinaryComparisonOperator> x ...)
	 *		IS [NOT] NULL
	 *
	 *
	 * For ANY and IN subqueries:
	 *		o  We generate an IS NULL above the SubqueryNode and return the top of
	 *		   the new tree to the caller.
	 *		o  The operator in the new predicate that is added to the subquery
	 *		   will correspond to the operator that modifies the ANY.
	 *		   (eg, = for = ANY, with = for IN.)
	 * For ALL and NOT IN subqueries:
	 *		o  We generate an IS NOT NULL above the SubqueryNode and return the top of
	 *		   the new tree to the caller.
	 *		o  The operator in the new predicate that is added to the subquery
	 *		   will be a BinaryAllOperatorNode whose bcoNodeType corresponds to 
	 *		   the negation of the operator that modifies the ALL.
	 *		   (eg, <> for = ALL, with <> for NOT IN.)
	 *
	 * NOTE: This method is called after the underlying subquery has been
	 * preprocessed, so we build a new Predicate, not just a new expression.
	 *
	 * @param numTables			Number of tables in DML Statement
	 *
	 * @return UnaryComparisonOperatorNode	An IS [NOT] NULL above the 
	 *										transformed subquery.
	 *
	 * @exception StandardException		Thrown on error
	 */
	private UnaryComparisonOperatorNode pushNewPredicate(
				int numTables)
			throws StandardException
	{
		AndNode						andNode;
		BinaryComparisonOperatorNode bcoNode = null;
		JBitSet						tableMap;
		Predicate					predicate;
		ResultColumn				firstRC;
		ResultColumnList			resultColumns;
		UnaryComparisonOperatorNode	ucoNode = null;
		ValueNode					oldWhereClause;
		ValueNode					rightOperand;

		/* We have to ensure that the resultSet immediately under us has
		 * a PredicateList, otherwise we can't push the predicate down.
		 */
		resultSet = resultSet.ensurePredicateList(numTables);

		/* RESOLVE - once we understand how correlated columns will work, 
		 * we probably want to mark leftOperand as a correlated column
		 */
		resultColumns = resultSet.getResultColumns();

		/*
		** Create a new PR node.  Put it over the original subquery.  resulSet
		** is now the new PR.  We give the chance that things under the PR node
		** can be materialized.  See beetle 4373.
		*/
		ResultColumnList newRCL = resultColumns.copyListAndObjects();
		newRCL.genVirtualColumnNodes(resultSet, resultColumns);
		resultSet = (ResultSetNode) getNodeFactory().getNode(
										C_NodeTypes.PROJECT_RESTRICT_NODE,
										resultSet,	// child
										newRCL,			// result columns
										null,			// restriction
										null, 			// restriction list
										null,			// project subqueries
										null,			// restrict subqueries	
										null,
										getContextManager());
		resultColumns = newRCL;
	
		firstRC = (ResultColumn) resultColumns.elementAt(0);
		rightOperand = firstRC.getExpression();

		bcoNode = getNewJoinCondition(leftOperand, rightOperand);

		ValueNode andLeft = bcoNode;

		/* For NOT IN or ALL, and if either side of the comparison is nullable, and the
		 * subquery can not be flattened (because of that), we need to add IS NULL node
		 * on top of the nullables, such that the behavior is (beetle 5173):
		 *
		 *    (1) If we have nulls in right operand, no row is returned.
		 *    (2) If subquery result is empty before applying join predicate, every
		 *		  left row (including NULLs) is returned.
		 *	  (3) Otherwise, return {all left row} - {NULLs}
		 */
		if (isNOT_IN() || isALL())
		{
			boolean leftNullable = leftOperand.getTypeServices().isNullable();
			boolean rightNullable = rightOperand.getTypeServices().isNullable();
			if (leftNullable || rightNullable)
			{
				/* Create a normalized structure.
				 */
				BooleanConstantNode falseNode = (BooleanConstantNode) getNodeFactory().getNode(
												C_NodeTypes.BOOLEAN_CONSTANT_NODE,
												Boolean.FALSE,
												getContextManager());
				OrNode newOr = (OrNode) getNodeFactory().getNode(
												C_NodeTypes.OR_NODE,
												bcoNode,
												falseNode,
												getContextManager());
				newOr.postBindFixup();
				andLeft = newOr;

				if (leftNullable)
				{
					UnaryComparisonOperatorNode leftIsNull = (UnaryComparisonOperatorNode)
									getNodeFactory().getNode(
														C_NodeTypes.IS_NULL_NODE,
														leftOperand,
														getContextManager());
					leftIsNull.bindComparisonOperator();
					newOr = (OrNode) getNodeFactory().getNode(
													C_NodeTypes.OR_NODE,
													leftIsNull,
													andLeft,
													getContextManager());
					newOr.postBindFixup();
					andLeft = newOr;
				}
				if (rightNullable)
				{
					UnaryComparisonOperatorNode rightIsNull = (UnaryComparisonOperatorNode)
									getNodeFactory().getNode(
														C_NodeTypes.IS_NULL_NODE,
														rightOperand,
														getContextManager());