subquerynode.java

derby database source code.good for you.

	 * Bind this expression.  This means binding the sub-expressions,
	 * as well as figuring out what the return type is for this expression.
	 *
	 * @param fromList			The FROM list for the query this
	 *							expression is in, for binding columns.
	 *							NOTE: fromList will be null if the subquery appears
	 *							in a VALUES clause.
	 * @param subqueryList		The subquery list being built as we find SubqueryNodes
	 * @param aggregateVector	The aggregate vector being built as we find AggregateNodes
	 *
	 * @return	The new top of the expression tree.
	 *
	 * @exception StandardException		Thrown on error
	 */
	public ValueNode bindExpression(FromList fromList, SubqueryList subqueryList,
					Vector	aggregateVector)
				throws StandardException
	{
		ResultColumnList	resultColumns;

		//check if subquery is allowed in expression tree
		checkReliability( CompilerContext.SUBQUERY_ILLEGAL, SQLState.LANG_SUBQUERY );

		resultColumns = resultSet.getResultColumns();

		/* The parser does not enforce the fact that a subquery can only return
		 * a single column, so we must check here.
		 */
		if (resultColumns.size() != 1)
		{
			throw StandardException.newException(SQLState.LANG_NON_SINGLE_COLUMN_SUBQUERY);
		}

		/* Verify the usage of "*" in the select list:
		 *	o  Only valid in EXISTS subqueries
		 *	o  If the AllResultColumn is qualified, then we have to verify
		 *	   that the qualification is a valid exposed name.
		 *	   NOTE: The exposed name can come from an outer query block.
		 */
		resultSet.verifySelectStarSubquery(fromList, subqueryType);

		/* For an EXISTS subquery:
		 *	o  If the SELECT list is a "*", then we convert it to a true.
		 *	   (We need to do the conversion since we don't want the "*" to
		 *	   get expanded.)
		 *  o  We then must bind the expression under the SELECT list to
		 *	   verify that it is a valid expression.  (We must do this as a
		 *	   separate step because we need to validate the expression and
		 *	   we need to handle EXISTS (select * ... union all select 1 ...)
		 *	   without getting a type compatability error.)
		 *	o  Finally, we convert the expression to a SELECT true.
		 */
		if (subqueryType == EXISTS_SUBQUERY)
		{
			/* Transform the * into true (EXISTS). */
			resultSet.setResultToBooleanTrueNode(true);
		}

		/* We need to bind the tables before we can bind the target list
		 * (for exists subqueries).  However, we need to wait until after
		 * any *'s have been replaced, so that they don't get expanded.
		 */
		CompilerContext cc = getCompilerContext();

		resultSet = resultSet.bindNonVTITables(getDataDictionary(), fromList);
		resultSet = resultSet.bindVTITables(fromList);

		/* Set the subquery # for this SubqueryNode */
		if (subqueryNumber == -1)
			subqueryNumber = cc.getNextSubqueryNumber();

		/* reject ? parameters in the select list of subqueries */
		resultSet.rejectParameters();

		if (subqueryType == EXISTS_SUBQUERY)
		{
			/* Bind the expression in the SELECT list */
			resultSet.bindTargetExpressions(fromList);

			/* Transform the ResultColumn into true.
			 * NOTE: This may be a 2nd instance of the same transformation for
			 * an EXISTS (select * ...), since we had to transform the 
			 * AllResultColumn above, but we have to also handle
			 * EXISTS (select r from s ...)
			 */
			resultSet.setResultToBooleanTrueNode(false);
		}

		/* bind the left operand, if there is one */
		if (leftOperand != null)
		{
			leftOperand = leftOperand.bindExpression(fromList, subqueryList,
									   aggregateVector);
		}

		/* bind the expressions in the underlying subquery */
		resultSet.bindExpressions(fromList);

		resultSet.bindResultColumns(fromList);

		/* We need to reset resultColumns since the underlying resultSet may
		 * be a UNION (and UnionNode.bindResultColumns() regens a new RCL).
		 */
		resultColumns = resultSet.getResultColumns();

		/*
		 * A ? parameter to the left of this subquery gets type of the
		 * subquery's sole column.
		 */
		if (leftOperand != null && leftOperand.isParameterNode())
		{
			((ParameterNode) leftOperand).setDescriptor(
				((ResultColumn) resultColumns.elementAt(0)).getTypeServices());
		}

		// Set the DataTypeServices
		setDataTypeServices(resultColumns);

		/* Add this subquery to the subquery list */
		subqueryList.addSubqueryNode(this);

		return this;
	}

	/**
	 * Preprocess an expression tree.  We do a number of transformations
	 * here (including subqueries, IN lists, LIKE and BETWEEN) plus
	 * subquery flattening.
	 * NOTE: This is done before the outer ResultSetNode is preprocessed.
	 *
	 * @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
	 */
	public ValueNode preprocess(int numTables,
								FromList outerFromList,
								SubqueryList outerSubqueryList,
								PredicateList outerPredicateList) 
					throws StandardException
	{
		/* Only preprocess this node once.  We may get called multiple times
		 * due to tree transformations.
		 */
		if (preprocessed)
		{
			return this;
		}
		preprocessed = true;

		boolean		flattenable;
		ValueNode	topNode = this;

		resultSet = resultSet.preprocess(numTables, null, (FromList) null);

		// Eliminate any unnecessary DISTINCTs
		if (resultSet instanceof SelectNode)
		{
			if (((SelectNode) resultSet).hasDistinct())
			{
				((SelectNode) resultSet).clearDistinct();
				/* We need to remember to check for single unique value
				 * at execution time for expression subqueries.
				 */
				if  (subqueryType == EXPRESSION_SUBQUERY)
				{
					distinctExpression = true;
				}
			}
		}

		/* Lame transformation - For IN/ANY subqueries, if
		 * result set is guaranteed to return at most 1 row
		 * and it is not correlated
		 * then convert the subquery into the matching expression
		 * subquery type.  For example:
		 *	c1 in (select min(c1) from t2)
		 * becomes:
		 *	c1 = (select min(c1) from t2)
		 * (This actually showed up in an app that a potential customer
		 * was porting from SQL Server.)
		 * The transformed query can then be flattened if appropriate.
		 */
		if ((isIN() || isANY()) &&
			resultSet.returnsAtMostOneRow())
		{
			if (! hasCorrelatedCRs())
			{
				changeToCorrespondingExpressionType();
			}
		}

		/* NOTE: Flattening occurs before the pushing of
		 * the predicate, since the pushing will add a node 
		 * above the SubqueryNode.
		 */

		/* Values subquery is flattenable if:
		 *  o It is not under an OR.
		 *  o It is an expression subquery on the right side
		 *	  of a BinaryComparisonOperatorNode.
		 */
		flattenable = (resultSet instanceof RowResultSetNode) &&
					  underTopAndNode &&
					  parentComparisonOperator instanceof BinaryComparisonOperatorNode;
		if (flattenable)
		{
			/* If we got this far and we are an expression subquery
			 * then we want to set leftOperand to be the left side
			 * of the comparison in case we pull the comparison into
			 * the flattened subquery.
			 */
			leftOperand = parentComparisonOperator.getLeftOperand();
			// Flatten the subquery
			RowResultSetNode rrsn = (RowResultSetNode) resultSet;
			FromList   fl = new FromList();

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

			/* We only need to add the table from the subquery into 
			 * the outer from list if the subquery itself contains
			 * another subquery.  Otherwise, it just becomes a constant.
			 */
			if (rrsn.subquerys.size() != 0)
			{
				fl.addElement(rrsn);
				outerFromList.destructiveAppend(fl);
			}

			/* Append the subquery's subquery list to the 
			 * outer subquery list.
			 */
			outerSubqueryList.destructiveAppend(rrsn.subquerys);

			/* 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.
			 */
			ValueNode rightOperand;
			rightOperand = ((ResultColumn) rrsn.getResultColumns().elementAt(0)).
								getExpression();
			return getNewJoinCondition(leftOperand, rightOperand);
		}

		/* Select subquery is flattenable if:
		 *  o It is not under an OR.
		 *  o The subquery type is IN, ANY or EXISTS or
		 *    an expression subquery on the right side
		 *	  of a BinaryComparisonOperatorNode.
		 *  o There are no aggregates in the select list
		 *  o There is no group by clause
		 *  o There is a uniqueness condition that ensures
		 *	  that the flattening of the subquery will not
		 *	  introduce duplicates into the result set.
		 *
		 *	OR,
		 *  o The subquery is NOT EXISTS, NOT IN, ALL (beetle 5173).
		 */
		boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());

		flattenable = (resultSet instanceof SelectNode) &&
					  underTopAndNode &&
					  (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
                       parentComparisonOperator != null);

		if (flattenable)
		{
			SelectNode	select = (SelectNode) resultSet;
			if ((select.getAggregateVector(IN_SELECT_LIST).size() == 0) &&
				(! select.getGeneratedForGroupbyClause()))
			{
				ValueNode origLeftOperand = leftOperand;

				/* Check for uniqueness condition. */
				/* Is the column being returned by the subquery
				 * a candidate for an = condition?
				 */
				boolean additionalEQ =
							(subqueryType == IN_SUBQUERY) ||
							(subqueryType == EQ_ANY_SUBQUERY);


				additionalEQ = additionalEQ &&
								((leftOperand instanceof ConstantNode) ||
								 (leftOperand instanceof ColumnReference) ||

								 (leftOperand.isParameterNode()));
				/* If we got this far and we are an expression subquery
				 * then we want to set leftOperand to be the left side
				 * of the comparison in case we pull the comparison into
				 * the flattened subquery.
				 */
				if (parentComparisonOperator instanceof BinaryComparisonOperatorNode)
				{
					leftOperand = parentComparisonOperator.getLeftOperand();
				}
				/* Never flatten to normal join for NOT EXISTS.
				 */
				if ((! flattenableNotExists) && select.uniqueSubquery(additionalEQ))
				{
					// Flatten the subquery
					return flattenToNormalJoin(numTables,
										   outerFromList, outerSubqueryList,
										   outerPredicateList);
				}
				/* We can flatten into an EXISTS join if all of the above
				 * conditions except for a uniqueness condition are true
				 * and:
				 *	o Subquery only has a single entry in its from list
				 *	  and that entry is a FromBaseTable
				 *	o All predicates in the subquery's where clause are
				 *	  pushable.
				 *  o The leftOperand, if non-null, is pushable.
				 * If the subquery meets these conditions then we will flatten
				 * the FBT into an EXISTS FBT, pushd the subquery's
				 * predicates down to the PRN above the EBT and
				 * mark the predicates to say that they cannot be pulled 
				 * above the PRN. (The only way that we can guarantee correctness
				 * is if the predicates do not get pulled up.  If they get pulled
				 * up then the single next logic for an EXISTS join does not work
				 * because that row may get disqualified at a higher level.)
				 */
				else if ( (isIN() || isANY() || isEXISTS() || flattenableNotExists) &&
						  ((leftOperand == null) ? true :
							 leftOperand.categorize(new JBitSet(numTables), false)) &&
						  select.getWherePredicates().allPushable() &&
						  singleFromBaseTable(select.getFromList()))
				{
					return flattenToExistsJoin(numTables,
										   outerFromList, outerSubqueryList,
										   outerPredicateList, flattenableNotExists);
				}

				// restore leftOperand to its original value
				leftOperand = origLeftOperand;
			}
		}

		/* We transform the leftOperand and the select list for quantified 
		 * predicates that have a leftOperand into a new predicate and push it
		 * down to the subquery after we preprocess the subquery's resultSet.
		 * We must do this after preprocessing the underlying subquery so that
		 * we know where to attach the new predicate.
		 * NOTE - If we pushed the predicate before preprocessing the underlying
		 * subquery, then the point of attachment would depend on the form of
		 * that subquery.  (Where clause?  Having clause?)
		 */
		if (leftOperand != null)
		{
			topNode = pushNewPredicate(numTables);
			pushedNewPredicate = true;
		}
		/* Since NOT EXISTS subquery is not flattened, now is good time to create
		 * an IS NULL node on top.  Other cases are taken care of in pushNewPredicate.
		 */
		else if (subqueryType == NOT_EXISTS_SUBQUERY)
		{
			topNode = genIsNullTree();
			subqueryType = EXISTS_SUBQUERY;
		}

		/*
		** Do inVariant and correlated checks now.  We
		** aren't going to use the results here, but they
		** have been stashed away by isInvariant() and hasCorrelatedCRs()
		*/
		isInvariant();
		hasCorrelatedCRs();

		/* If parentComparisonOperator is non-null then we are an
		 * expression subquery that was considered to be a candidate 
		 * for flattening, but we didn't get flattened.  In that case
		 * we are the rightOperand of the parent.  We need to update
		 * the parent's rightOperand with the new topNode and return
		 * the parent because the parent is letting us decide whether
		 * or not to replace the entire comparison, which we can do
		 * if we flatten.  Otherwise we simply return the new top node.
		 */
		if (parentComparisonOperator != null)
		{
			parentComparisonOperator.setRightOperand(topNode);
			return parentComparisonOperator;
		}

		return topNode;
	}

	/**
	 * Does the from list from the subquery contain a
	 * single entry which is a FBT or a PRN/FBT.
	 *
	 * @param fromList	The from list from the subquery
	 *
	 * @return Whether or not the from list from the subquery contains a
	 *			single entry which is a FBT or a PRN/FBT.
	 */
	private boolean singleFromBaseTable(FromList fromList)
	{
		boolean retCode = (fromList.size() == 1);

		if (retCode)
		{
			FromTable ft = (FromTable) fromList.elementAt(0);

			if (((ft instanceof ProjectRestrictNode) &&
				 ((ProjectRestrictNode) ft).getChildResult() instanceof FromBaseTable) ||
				ft instanceof FromBaseTable)
			{
			}