SubqueryNode


1   /*
2   
3      Derby - Class org.apache.derby.impl.sql.compile.SubqueryNode
4   
5      Licensed to the Apache Software Foundation (ASF) under one or more
6      contributor license agreements.  See the NOTICE file distributed with
7      this work for additional information regarding copyright ownership.
8      The ASF licenses this file to you under the Apache License, Version 2.0
9      (the "License"); you may not use this file except in compliance with
10     the License.  You may obtain a copy of the License at
11  
12        http://www.apache.org/licenses/LICENSE-2.0
13  
14     Unless required by applicable law or agreed to in writing, software
15     distributed under the License is distributed on an "AS IS" BASIS,
16     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17     See the License for the specific language governing permissions and
18     limitations under the License.
19  
20   */
21  
22  package org.apache.derby.impl.sql.compile;
23  
24  import org.apache.derby.iapi.services.context.ContextManager;
25  
26  import org.apache.derby.iapi.error.StandardException;
27  
28  import org.apache.derby.iapi.sql.compile.CompilerContext;
29  import org.apache.derby.iapi.sql.compile.CostEstimate;
30  import org.apache.derby.iapi.sql.compile.Visitable;
31  import org.apache.derby.iapi.sql.compile.Visitor;
32  import org.apache.derby.iapi.sql.compile.C_NodeTypes;
33  
34  import org.apache.derby.iapi.sql.dictionary.DataDictionary;
35  import org.apache.derby.iapi.reference.SQLState;
36  import org.apache.derby.iapi.reference.ClassName;
37  
38  import org.apache.derby.iapi.types.DataTypeDescriptor;
39  
40  import org.apache.derby.iapi.sql.execute.ExecRow;
41  
42  import org.apache.derby.iapi.sql.Activation;
43  import org.apache.derby.iapi.types.DataValueDescriptor;
44  import org.apache.derby.iapi.sql.Row;
45  import org.apache.derby.iapi.types.DataTypeDescriptor;
46  import org.apache.derby.iapi.sql.ResultSet;
47  import org.apache.derby.iapi.types.TypeId;
48  
49  import org.apache.derby.iapi.services.loader.GeneratedMethod;
50  
51  import org.apache.derby.iapi.services.compiler.MethodBuilder;
52  import org.apache.derby.iapi.services.compiler.LocalField;
53  
54  
55  import org.apache.derby.iapi.services.sanity.SanityManager;
56  
57  import org.apache.derby.iapi.store.access.Qualifier;
58  
59  import java.lang.reflect.Modifier  ;
60  
61  import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
62  import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
63  import org.apache.derby.impl.sql.execute.OnceResultSet;
64  
65  import org.apache.derby.iapi.util.JBitSet;
66  import org.apache.derby.iapi.util.ReuseFactory;
67  import org.apache.derby.iapi.services.classfile.VMOpcode;
68  
69  import java.util.Properties  ;
70  import java.util.Vector  ;
71  
72  /**
73   * A SubqueryNode represents a subquery.  Subqueries return values to their
74   * outer queries. An quantified subquery is one that appears under a quantified
75   * operator (like IN or EXISTS) - quantified subqueries can return more than
76   * one value per invocation. An expression subquery is one that is not directly
77   * under a quantified operator - expression subqueries are allowed to return
78   * at most one value per invocation (returning no value is considered to be
79   * equivalent to returning NULL).
80   *
81   * There are a large number of subquery types.  Because of the large number of
82   * types, and the large amount of shared code, we have decided to have 1 SubqueryNode
83   * without any subclasses.  The subquery type (and operator) is encoded in the
84   * subqueryType field.
85   *
86   * The query optimizer is responsible for optimizing subqueries, and also for
87   * transforming them so that code can be generated for them. The optimizer may
88   * eliminate some subqueries by transforming them into joins, or it may
89   * change the internal form of a subquery (for example, transforming
90   * 'where x in (select y from z where ...)' into
91   * 'where (select true from z where x = y and ...)').
92   *
93   * Note that aggregates present some additional issues.  A transformation
94   * such as:
95   *  <UL> where x in (SELECT <I>expression</I> FROM z) </UL>
96   * has to be treated specially if <I>expression</I> has an aggregate.
97   * We change it to:
98   *  <UL> where x = (SELECT true FROM (SELECT MAX(x) FROM z) WHERE SQLCOL1 = y) </UL>
99   *
100  * @author Jeff Lichtman
101  */
102 
103 public class SubqueryNode extends ValueNode
104 {
105     /*
106     ** This must be a single-column result set.  If the subquery is
107     ** not quantified, it must also be a single-row result set - that is,
108     ** expression subqueries are allowed to return only a single value
109     ** per invocation.
110     ** NOTE: SubqueryNodes are used as an intermediate step within the parser
111     ** for building a derived table.  Derived tables can be multi-column and 
112     ** multi-table.
113     */
114     ResultSetNode   resultSet;
115 
116     /* Type of this subquery */
117     int             subqueryType;
118 
119     /* Whether or not this subquery is immediately under a top level AndNode.
120      * (Important for subquery flattening.)
121      */
122     boolean         underTopAndNode;
123 
124     /* Whether or not we've been preprocessed. (Only do the work once.) */
125     boolean         preprocessed;
126 
127     /* Whether or not this subquery began life as a distinct expression subquery */
128     boolean         distinctExpression;
129 
130     /* Since we do not have separate subquery operator nodes, the
131      * type of the subquery is stored in the subqueryType field.  Most subquery
132      * types take a left operand (except for expression and exists).  We could
133      * either add a leftOperand field here or subclass SubqueryNode for those
134      * types that take a left operand.  We have decided to add the left operand
135      * here for now.
136      */
137     ValueNode       leftOperand;
138     boolean         pushedNewPredicate;
139 
140     /* Expression subqueries on the right side of a BinaryComparisonOperatorNode
141      * will get passed a pointer to that node prior to preprocess().  This
142      * allows us to replace the entire comparison, if we want to, when
143      * flattening.
144      */
145     BinaryComparisonOperatorNode parentComparisonOperator;
146 
147     /* Private fields (all references via private methods) - 
148      * We reuse true BooleanConstantNodes within
149      * this class, creating them on the first reference.
150      */
151     private BooleanConstantNode trueNode;
152     /* Reuse generated code where possible */
153     //private Expression genResult;
154 
155     /* Subquery # for this subquery */
156     private int subqueryNumber = -1;
157 
158     /* ResultSet # for the point of attachment for this subquery */
159     private int pointOfAttachment = -1;
160 
161     /* 
162     ** Indicate whether we found a correlation or not.
163     ** And track whether we have checked yet.
164     */
165     private boolean foundCorrelation;
166     private boolean doneCorrelationCheck;
167 
168     /* 
169     ** Indicate whether we found an invariant node
170     ** below us or not. And track whether we have 
171     ** checked yet.
172     */
173     private boolean foundVariant;
174     private boolean doneInvariantCheck;
175 
176     /* Subquery types.
177      * NOTE: FROM_SUBQUERY only exists for a brief second in the parser.  It
178      * should never appear in a query tree.
179      * NOTE: NOT EXISTS and NOT IN subquery types do not exist prior to NOT 
180      * elimination during preprocessing.  Prior to that, there is a separate
181      * NotNode above the SubqueryNode in the tree.
182      *
183      */
184     public final static int NOTIMPLEMENTED_SUBQUERY     = -1;
185     public final static int FROM_SUBQUERY       = 0;
186     public final static int IN_SUBQUERY         = 1;
187     public final static int NOT_IN_SUBQUERY     = 2;
188     public final static int EQ_ANY_SUBQUERY     = 3;
189     public final static int EQ_ALL_SUBQUERY     = 4;
190     public final static int NE_ANY_SUBQUERY     = 5;
191     public final static int NE_ALL_SUBQUERY     = 6;
192     public final static int GT_ANY_SUBQUERY     = 7;
193     public final static int GT_ALL_SUBQUERY     = 8;
194     public final static int GE_ANY_SUBQUERY     = 9;
195     public final static int GE_ALL_SUBQUERY     = 10;
196     public final static int LT_ANY_SUBQUERY     = 11;
197     public final static int LT_ALL_SUBQUERY     = 12;
198     public final static int LE_ANY_SUBQUERY     = 13;
199     public final static int LE_ALL_SUBQUERY     = 14;
200     public final static int EXISTS_SUBQUERY     = 15;
201     public final static int NOT_EXISTS_SUBQUERY = 16;
202     public final static int EXPRESSION_SUBQUERY = 17;
203 
204 
205     /**
206      * Initializer.
207      *
208      * @param resultSet     The ResultSetNode for the subquery
209      * @param subqueryType  The type of the subquery
210      * @param leftOperand   The left operand, if any, of the subquery
211      */
212 
213     public void init(
214                             Object   resultSet,
215                             Object   subqueryType,
216                             Object   leftOperand)
217     {
218         this.resultSet = (ResultSetNode) resultSet;
219         this.subqueryType = ((Integer  ) subqueryType).intValue();
220 
221         /* Subqueries are presumed not to be under a top level AndNode by
222          * default.  This is because expression normalization only recurses
223          * under Ands and Ors, not under comparison operators, method calls,
224          * built-in functions, etc.
225          */
226         underTopAndNode = false;
227         this.leftOperand = (ValueNode) leftOperand;
228     }
229 
230     /**
231      * Convert this object to a String.  See comments in QueryTreeNode.java
232      * for how this should be done for tree printing.
233      *
234      * @return  This object as a String
235      */
236 
237     public String   toString()
238     {
239         if (SanityManager.DEBUG)
240         {
241             return "subqueryType: " + subqueryType + "\n" +
242                "underTopAndNode: " + underTopAndNode + "\n" +
243                "subqueryNumber: " + subqueryNumber + "\n" +
244                "pointOfAttachment: " + pointOfAttachment + "\n" +
245                "preprocessed: " + preprocessed + "\n" +
246                "distinctExpression: " + distinctExpression + "\n" +
247                 super.toString();
248         }
249         else
250         {
251             return "";
252         }
253     }
254 
255     /**
256      * Prints the sub-nodes of this object.  See QueryTreeNode.java for
257      * how tree printing is supposed to work.
258      *
259      * @param depth     The depth of this node in the tree
260      */
261 
262     public void printSubNodes(int depth)
263     {
264         if (SanityManager.DEBUG)
265         {
266             super.printSubNodes(depth);
267 
268             if (resultSet != null)
269             {
270                 printLabel(depth, "resultSet: ");
271                 resultSet.treePrint(depth + 1);
272             }
273 
274             if (leftOperand != null)
275             {
276                 printLabel(depth, "leftOperand: ");
277                 leftOperand.treePrint(depth + 1);
278             }
279         }
280     }
281 
282     /**
283      * Return the resultSet for this SubqueryNode.
284      *
285      * @return ResultSetNode underlying this SubqueryNode.
286      */
287     public ResultSetNode getResultSet()
288     {
289         return resultSet;
290     }
291 
292     /**
293      * Return the type of this subquery.
294      *
295      * @return int  Type of this subquery.
296      */
297     public int getSubqueryType()
298     {
299         return subqueryType;
300     }
301 
302     /**
303      * Set the type of this subquery.
304      *
305      * @param subqueryType of this subquery.
306      */
307     public void setSubqueryType(int subqueryType)
308     {
309         this.subqueryType = subqueryType;
310     }
311 
312     /**
313      * Set the point of attachment of this subquery.
314      *
315      * @param pointOfAttachment The point of attachment of this subquery.
316      *
317      * @exception StandardException         Thrown on error
318      */
319     public void setPointOfAttachment(int pointOfAttachment)
320         throws StandardException
321     {
322         /* Materialized subqueries always keep their point of
323          * attachment as -1.
324          */
325         if (! isMaterializable())
326         {
327             this.pointOfAttachment = pointOfAttachment;
328         }
329     }
330 
331     /**
332      * Return whether or not this subquery is immediately under a top level
333      * AndNode.
334      *
335      * @return boolean  Whether or not this subquery is immediately under a
336      *                  top level AndNode.
337      */
338     public boolean getUnderTopAndNode()
339     {
340         return underTopAndNode;
341     }
342 
343     /**
344      * Get the ResultSet # for the point of attachment for this SubqueryNode.
345      *
346      * @return int      The ResultSet # for the point of attachment
347      */
348     public int getPointOfAttachment()
349     {
350         if (SanityManager.DEBUG)
351         {
352             SanityManager.ASSERT(pointOfAttachment >= 0,
353                 "pointOfAttachment expected to be >= 0");
354         }
355         return pointOfAttachment;
356     }
357 
358     /**
359      * Get whether or not this SubqueryNode has already been
360      * preprocessed.
361      * 
362      * @return  Whether or not this SubqueryNode has already been
363      *          preprocessed.
364      */
365     boolean getPreprocessed()
366     {
367         return preprocessed;
368     }
369 
370     /**
371      * Set the parent BCON.  Useful when considering flattening
372      * expression subqueries.
373      *
374      * @param parent    The parent BCON.
375      */
376     void setParentComparisonOperator(BinaryComparisonOperatorNode parent)
377     {
378         parentComparisonOperator = parent;
379     }
380 
381     /**
382      * Remap all ColumnReferences in this tree to be clones of the
383      * underlying expression.
384      *
385      * @return ValueNode            The remapped expression tree.
386      *
387      * @exception StandardException         Thrown on error
388      */
389     public ValueNode remapColumnReferencesToExpressions()
390         throws StandardException
391     {
392         /* We need to remap both the SELECT and Predicate lists 
393          * since there may be correlated columns in either of them.
394          */
395         if (resultSet instanceof SelectNode)
396         {
397             ResultColumnList selectRCL = resultSet.getResultColumns();
398             SelectNode       select = (SelectNode) resultSet;
399             PredicateList    selectPL = select.getWherePredicates();
400 
401             if (SanityManager.DEBUG)
402             {
403                 SanityManager.ASSERT(selectPL != null,
404                     "selectPL expected to be non-null");
405             }
406             selectRCL.remapColumnReferencesToExpressions();
407             selectPL.remapColumnReferencesToExpressions();
408         }
409         return this;
410     }
411 
412     /**
413      * Bind this expression.  This means binding the sub-expressions,
414      * as well as figuring out what the return type is for this expression.
415      *
416      * @param fromList          The FROM list for the query this
417      *                          expression is in, for binding columns.
418      *                          NOTE: fromList will be null if the subquery appears
419      *                          in a VALUES clause.
420      * @param subqueryList      The subquery list being built as we find SubqueryNodes
421      * @param aggregateVector   The aggregate vector being built as we find AggregateNodes
422      *
423      * @return  The new top of the expression tree.
424      *
425      * @exception StandardException     Thrown on error
426      */
427     public ValueNode bindExpression(FromList fromList, SubqueryList subqueryList,
428                     Vector    aggregateVector)
429                 throws StandardException
430     {
431         ResultColumnList    resultColumns;
432 
433         //check if subquery is allowed in expression tree
434         checkReliability( CompilerContext.SUBQUERY_ILLEGAL, SQLState.LANG_SUBQUERY );
435 
436         resultColumns = resultSet.getResultColumns();
437 
438         /* The parser does not enforce the fact that a subquery can only return
439          * a single column, so we must check here.
440          */
441         if (resultColumns.size() != 1)
442         {
443             throw StandardException.newException(SQLState.LANG_NON_SINGLE_COLUMN_SUBQUERY);
444         }
445 
446         /* Verify the usage of "*" in the select list:
447          *  o  Only valid in EXISTS subqueries
448          *  o  If the AllResultColumn is qualified, then we have to verify
449          *     that the qualification is a valid exposed name.
450          *     NOTE: The exposed name can come from an outer query block.
451          */
452         resultSet.verifySelectStarSubquery(fromList, subqueryType);
453 
454         /* For an EXISTS subquery:
455          *  o  If the SELECT list is a "*", then we convert it to a true.
456          *     (We need to do the conversion since we don't want the "*" to
457          *     get expanded.)
458          *  o  We then must bind the expression under the SELECT list to
459          *     verify that it is a valid expression.  (We must do this as a
460          *     separate step because we need to validate the expression and
461          *     we need to handle EXISTS (select * ... union all select 1 ...)
462          *     without getting a type compatability error.)
463          *  o  Finally, we convert the expression to a SELECT true.
464          */
465         if (subqueryType == EXISTS_SUBQUERY)
466         {
467             /* Transform the * into true (EXISTS). */
468             resultSet.setResultToBooleanTrueNode(true);
469         }
470 
471         /* We need to bind the tables before we can bind the target list
472          * (for exists subqueries).  However, we need to wait until after
473          * any *'s have been replaced, so that they don't get expanded.
474          */
475         CompilerContext cc = getCompilerContext();
476 
477         resultSet = resultSet.bindNonVTITables(getDataDictionary(), fromList);
478         resultSet = resultSet.bindVTITables(fromList);
479 
480         /* Set the subquery # for this SubqueryNode */
481         if (subqueryNumber == -1)
482             subqueryNumber = cc.getNextSubqueryNumber();
483 
484         /* reject ? parameters in the select list of subqueries */
485         resultSet.rejectParameters();
486 
487         if (subqueryType == EXISTS_SUBQUERY)
488         {
489             /* Bind the expression in the SELECT list */
490             resultSet.bindTargetExpressions(fromList);
491 
492             /* Transform the ResultColumn into true.
493              * NOTE: This may be a 2nd instance of the same transformation for
494              * an EXISTS (select * ...), since we had to transform the 
495              * AllResultColumn above, but we have to also handle
496              * EXISTS (select r from s ...)
497              */
498             resultSet.setResultToBooleanTrueNode(false);
499         }
500 
501         /* bind the left operand, if there is one */
502         if (leftOperand != null)
503         {
504             leftOperand = leftOperand.bindExpression(fromList, subqueryList,
505                                        aggregateVector);
506         }
507 
508         /* bind the expressions in the underlying subquery */
509         resultSet.bindExpressions(fromList);
510 
511         resultSet.bindResultColumns(fromList);
512 
513         /* We need to reset resultColumns since the underlying resultSet may
514          * be a UNION (and UnionNode.bindResultColumns() regens a new RCL).
515          */
516         resultColumns = resultSet.getResultColumns();
517 
518         /*
519          * A ? parameter to the left of this subquery gets type of the
520          * subquery's sole column.
521          */
522         if (leftOperand != null && leftOperand.requiresTypeFromContext())
523         {
524             leftOperand.setType(
525                 ((ResultColumn) resultColumns.elementAt(0)).getTypeServices());
526         }
527 
528         // Set the DataTypeServices
529         setDataTypeServices(resultColumns);
530 
531         /* Add this subquery to the subquery list */
532         subqueryList.addSubqueryNode(this);
533 
534         return this;
535     }
536 
537     /**
538      * Preprocess an expression tree.  We do a number of transformations
539      * here (including subqueries, IN lists, LIKE and BETWEEN) plus
540      * subquery flattening.
541      * NOTE: This is done before the outer ResultSetNode is preprocessed.
542      *
543      * @param   numTables           Number of tables in the DML Statement
544      * @param   outerFromList       FromList from outer query block
545      * @param   outerSubqueryList   SubqueryList from outer query block
546      * @param   outerPredicateList  PredicateList from outer query block
547      *
548      * @return      The modified expression
549      *
550      * @exception StandardException     Thrown on error
551      */
552     public ValueNode preprocess(int numTables,
553                                 FromList outerFromList,
554                                 SubqueryList outerSubqueryList,
555                                 PredicateList outerPredicateList) 
556                     throws StandardException
557     {
558         /* Only preprocess this node once.  We may get called multiple times
559          * due to tree transformations.
560          */
561         if (preprocessed)
562         {
563             return this;
564         }
565         preprocessed = true;
566 
567         boolean     flattenable;
568         ValueNode   topNode = this;
569 
570         resultSet = resultSet.preprocess(numTables, null, (FromList) null);
571 
572         // Eliminate any unnecessary DISTINCTs
573         if (resultSet instanceof SelectNode)
574         {
575             if (((SelectNode) resultSet).hasDistinct())
576             {
577                 ((SelectNode) resultSet).clearDistinct();
578                 /* We need to remember to check for single unique value
579                  * at execution time for expression subqueries.
580                  */
581                 if  (subqueryType == EXPRESSION_SUBQUERY)
582                 {
583                     distinctExpression = true;
584                 }
585             }
586         }
587 
588         /* Lame transformation - For IN/ANY subqueries, if
589          * result set is guaranteed to return at most 1 row
590          * and it is not correlated
591          * then convert the subquery into the matching expression
592          * subquery type.  For example:
593          *  c1 in (select min(c1) from t2)
594          * becomes:
595          *  c1 = (select min(c1) from t2)
596          * (This actually showed up in an app that a potential customer
597          * was porting from SQL Server.)
598          * The transformed query can then be flattened if appropriate.
599          */
600         if ((isIN() || isANY()) &&
601             resultSet.returnsAtMostOneRow())
602         {
603             if (! hasCorrelatedCRs())
604             {
605                 changeToCorrespondingExpressionType();
606             }
607         }
608 
609         /* NOTE: Flattening occurs before the pushing of
610          * the predicate, since the pushing will add a node 
611          * above the SubqueryNode.
612          */
613 
614         /* Values subquery is flattenable if:
615          *  o It is not under an OR.
616          *  o It is an expression subquery on the right side
617          *    of a BinaryComparisonOperatorNode.
618          */
619         flattenable = (resultSet instanceof RowResultSetNode) &&
620                       underTopAndNode &&
621                       parentComparisonOperator instanceof BinaryComparisonOperatorNode;
622         if (flattenable)
623         {
624             /* If we got this far and we are an expression subquery
625              * then we want to set leftOperand to be the left side
626              * of the comparison in case we pull the comparison into
627              * the flattened subquery.
628              */
629             leftOperand = parentComparisonOperator.getLeftOperand();
630             // Flatten the subquery
631             RowResultSetNode rrsn = (RowResultSetNode) resultSet;
632             FromList   fl = new FromList();
633 
634             // Remove ourselves from the outer subquery list
635             outerSubqueryList.removeElement(this);
636 
637             /* We only need to add the table from the subquery into 
638              * the outer from list if the subquery itself contains
639              * another subquery.  Otherwise, it just becomes a constant.
640              */
641             if (rrsn.subquerys.size() != 0)
642             {
643                 fl.addElement(rrsn);
644                 outerFromList.destructiveAppend(fl);
645             }
646 
647             /* Append the subquery's subquery list to the 
648              * outer subquery list.
649              */
650             outerSubqueryList.destructiveAppend(rrsn.subquerys);
651 
652             /* return the new join condition 
653              * If we are flattening an EXISTS then there is no new join
654              * condition since there is no leftOperand.  Simply return
655              * TRUE.
656              *
657              * NOTE: The outer where clause, etc. has already been normalized,
658              * so we simply return the BinaryComparisonOperatorNode above
659              * the new join condition.
660              */
661             ValueNode rightOperand;
662             rightOperand = ((ResultColumn) rrsn.getResultColumns().elementAt(0)).
663                                 getExpression();
664             return getNewJoinCondition(leftOperand, rightOperand);
665         }
666 
667         /* Select subquery is flattenable if:
668          *  o It is not under an OR.
669          *  o The subquery type is IN, ANY or EXISTS or
670          *    an expression subquery on the right side
671          *    of a BinaryComparisonOperatorNode.
672          *  o There are no aggregates in the select list
673          *  o There is no group by clause
674          *  o There is a uniqueness condition that ensures
675          *    that the flattening of the subquery will not
676          *    introduce duplicates into the result set.
677          *
678          *  OR,
679          *  o The subquery is NOT EXISTS, NOT IN, ALL (beetle 5173).
680          */
681         boolean flattenableNotExists = (isNOT_EXISTS() || canAllBeFlattened());
682 
683         flattenable = (resultSet instanceof SelectNode) &&
684                       underTopAndNode &&
685                       (isIN() || isANY() || isEXISTS() || flattenableNotExists ||
686                        parentComparisonOperator != null);
687 
688         if (flattenable)
689         {
690             SelectNode  select = (SelectNode) resultSet;
691             if ((select.getAggregateVector(IN_SELECT_LIST).size() == 0) &&
692                 (! select.getGeneratedForGroupbyClause()))
693             {
694                 ValueNode origLeftOperand = leftOperand;
695 
696                 /* Check for uniqueness condition. */
697                 /* Is the column being returned by the subquery
698                  * a candidate for an = condition?
699                  */
700                 boolean additionalEQ =
701                             (subqueryType == IN_SUBQUERY) ||
702                             (subqueryType == EQ_ANY_SUBQUERY);
703 
704 
705                 additionalEQ = additionalEQ &&
706                                 ((leftOperand instanceof ConstantNode) ||
707                                  (leftOperand instanceof ColumnReference) ||
708                                  (leftOperand.requiresTypeFromContext()));
709                 /* If we got this far and we are an expression subquery
710                  * then we want to set leftOperand to be the left side
711                  * of the comparison in case we pull the comparison into
712                  * the flattened subquery.
713                  */
714                 if (parentComparisonOperator instanceof BinaryComparisonOperatorNode)
715                 {
716                     leftOperand = parentComparisonOperator.getLeftOperand();
717                 }
718                 /* Never flatten to normal join for NOT EXISTS.
719                  */
720                 if ((! flattenableNotExists) && select.uniqueSubquery(additionalEQ))
721                 {
722                     // Flatten the subquery
723                     return flattenToNormalJoin(numTables,
724                                            outerFromList, outerSubqueryList,
725                                            outerPredicateList);
726                 }
727                 /* We can flatten into an EXISTS join if all of the above
728                  * conditions except for a uniqueness condition are true
729                  * and:
730                  *  o Subquery only has a single entry in its from list
731                  *    and that entry is a FromBaseTable
732                  *  o All predicates in the subquery's where clause are
733                  *    pushable.
734                  *  o The leftOperand, if non-null, is pushable.
735                  * If the subquery meets these conditions then we will flatten
736                  * the FBT into an EXISTS FBT, pushd the subquery's
737                  * predicates down to the PRN above the EBT and
738                  * mark the predicates to say that they cannot be pulled 
739                  * above the PRN. (The only way that we can guarantee correctness
740                  * is if the predicates do not get pulled up.  If they get pulled
741                  * up then the single next logic for an EXISTS join does not work
742                  * because that row may get disqualified at a higher level.)
743                  */
744                 else if ( (isIN() || isANY() || isEXISTS() || flattenableNotExists) &&
745                           ((leftOperand == null) ? true :
746                              leftOperand.categorize(new JBitSet(numTables), false)) &&
747                           select.getWherePredicates().allPushable() &&
748                           singleFromBaseTable(select.getFromList()))
749                 {
750                     return flattenToExistsJoin(numTables,
751                                            outerFromList, outerSubqueryList,
752                                            outerPredicateList, flattenableNotExists);
753                 }
754 
755                 // restore leftOperand to its original value
756                 leftOperand = origLeftOperand;
757             }
758         }
759 
760         /* We transform the leftOperand and the select list for quantified 
761          * predicates that have a leftOperand into a new predicate and push it
762          * down to the subquery after we preprocess the subquery's resultSet.
763          * We must do this after preprocessing the underlying subquery so that
764          * we know where to attach the new predicate.
765          * NOTE - If we pushed the predicate before preprocessing the underlying
766          * subquery, then the point of attachment would depend on the form of
767          * that subquery.  (Where clause?  Having clause?)
768          */
769         if (leftOperand != null)
770         {
771             topNode = pushNewPredicate(numTables);
772             pushedNewPredicate = true;
773         }
774         /* Since NOT EXISTS subquery is not flattened, now is good time to create
775          * an IS NULL node on top.  Other cases are taken care of in pushNewPredicate.
776          */
777         else if (subqueryType == NOT_EXISTS_SUBQUERY)
778         {
779             topNode = genIsNullTree();
780             subqueryType = EXISTS_SUBQUERY;
781         }
782 
783         /*
784         ** Do inVariant and correlated checks now.  We
785         ** aren't going to use the results here, but they
786         ** have been stashed away by isInvariant() and hasCorrelatedCRs()
787         */
788         isInvariant();
789         hasCorrelatedCRs();
790 
791         /* If parentComparisonOperator is non-null then we are an
792          * expression subquery that was considered to be a candidate 
793          * for flattening, but we didn't get flattened.  In that case
794          * we are the rightOperand of the parent.  We need to update
795          * the parent's rightOperand with the new topNode and return
796          * the parent because the parent is letting us decide whether
797          * or not to replace the entire comparison, which we can do
798          * if we flatten.  Otherwise we simply return the new top node.
799          */
800         if (parentComparisonOperator != null)
801         {
802             parentComparisonOperator.setRightOperand(topNode);
803             return parentComparisonOperator;
804         }
805 
806         return topNode;
807     }
808 
809     /**
810      * Does the from list from the subquery contain a
811      * single entry which is a FBT or a PRN/FBT.
812      *
813      * @param fromList  The from list from the subquery
814      *
815      * @return Whether or not the from list from the subquery contains a
816      *          single entry which is a FBT or a PRN/FBT.
817      */
818     private boolean singleFromBaseTable(FromList fromList)
819     {
820         boolean retCode = (fromList.size() == 1);
821 
822         if (retCode)
823         {
824             FromTable ft = (FromTable) fromList.elementAt(0);
825 
826             if (((ft instanceof ProjectRestrictNode) &&
827                  ((ProjectRestrictNode) ft).getChildResult() instanceof FromBaseTable) ||
828                 ft instanceof FromBaseTable)
829             {
830             }
831             else
832             {
833                 retCode = false;
834             }
835         }
836 
837         return retCode;
838     }
839 
840     /**
841      * Can NOT IN, ALL be falttened to NOT EXISTS join?  We can't or the flattening doesn't
842      * easily make sense if either side of the comparison is nullable. (beetle 5173)
843      *
844      * @return Whether or not the NOT IN or ALL subquery can be flattened.
845      */
846     private boolean canAllBeFlattened () throws StandardException
847     {
848         boolean result = false;
849         if (isNOT_IN() || isALL())
850         {
851             ValueNode rightOperand = ((ResultColumn) resultSet.getResultColumns().elementAt(0)).
852                                     getExpression();
853             result = (! leftOperand.getTypeServices().isNullable() &&
854                         ! rightOperand.getTypeServices().isNullable());
855         }
856         return result;
857     }
858 
859     /**
860      * Flatten this subquery into the outer query block.  
861      * At this point we are only flattening based on a uniqueness
862      * condition and only flattening non-aggregate subqueries.
863      * So, we promote the subquery's from list, as is, into 
864      * the outer from list.  For EXISTS subquerys, we return a 
865      * TRUE.  Otherwise we return a new comparison between
866      * the leftOperand and the expression in the subquery's
867      * SELECT list.
868      * RESOLVE - we will need to modify this logic to account
869      * for exists joins and aggregates as we support flattening
870      * for them.
871      *
872      * Anyway, here's what we do:
873      *  o We remove ourself from the outer subquery list.
874      *  o We decrement the nesting level for all tables
875      *    in the subquery tree.
876      *  o We append the subquery's from list to the outer
877      *    from list.
878      *  o We add the subquery's predicate list to the outer
879      *    predicate list.  (The subquery has already been
880      *    preprocessed.)
881      *  o We add the subquery's subquery list to the outer
882      *    subquery list.
883      *  o For EXISTS, we return a true.
884      *  o Otherwise, we return a new comparison between the
885      *    leftOperand and the expression in the inner select's
886      *    RCL.
887      *
888      * @param   numTables           Number of tables in the DML Statement
889      * @param   outerFromList       FromList from outer query block
890      * @param   outerSubqueryList   SubqueryList from outer query block
891      * @param   outerPredicateList  PredicateList from outer query block
892      *
893      * @return  The modified expression
894      *
895      * @exception StandardException     Thrown on error
896      */
897     private ValueNode flattenToNormalJoin(int numTables,
898                                           FromList outerFromList, 
899                                           SubqueryList outerSubqueryList,
900                                           PredicateList outerPredicateList)
901         throws StandardException
902     {
903         SelectNode select = (SelectNode) resultSet;
904         FromList   fl = select.getFromList();
905         int[] tableNumbers = fl.getTableNumbers();
906 
907         // Remove ourselves from the outer subquery list
908         outerSubqueryList.removeElement(this);
909 
910         /* Decrease the nesting level for all
911          * tables in the subquey tree.
912          */
913         select.decrementLevel(1);
914 
915         /* Add the table(s) from the subquery into the outer from list */
916         outerFromList.destructiveAppend(fl);
917 
918         /* Append the subquery's predicate list to the
919          * outer predicate list.
920          */
921         outerPredicateList.destructiveAppend(select.getWherePredicates());
922 
923         /* Append the subquery's subquery list to the 
924          * outer subquery list.
925          * NOTE: We must propagate any subqueries from both the
926          * SELECT list and WHERE clause of the subquery that's
927          * getting flattened.
928          */
929         outerSubqueryList.destructiveAppend(select.getWhereSubquerys());
930         outerSubqueryList.destructiveAppend(select.getSelectSubquerys());
931 
932         /* return the new join condition 
933          * If we are flattening an EXISTS then there is no new join
934          * condition since there is no leftOperand.  Simply return
935          * TRUE.
936          *
937          * NOTE: The outer where clause, etc. has already been normalized,
938          * so we simply return the BinaryComparisonOperatorNode above
939          * the new join condition.
940          */
941         if (leftOperand == null)
942         {
943             return (ValueNode) getNodeFactory().getNode(
944                                             C_NodeTypes.BOOLEAN_CONSTANT_NODE,
945                                             Boolean.TRUE,
946                                             getContextManager());
947         }
948         else
949         {
950             ValueNode rightOperand;
951             rightOperand = ((ResultColumn) select.getResultColumns().elementAt(0)).
952                                 getExpression();
953             /* If the right operand is a CR, then we need to decrement
954              * its source level as part of flattening so that
955              * transitive closure will work correctly.
956              */
957             if (rightOperand instanceof ColumnReference)
958             {
959                 ColumnReference cr = (ColumnReference) rightOperand;
960                 int tableNumber = cr.getTableNumber();
961                 for (int index = 0; index < tableNumbers.length; index++)
962                 {
963                     if (tableNumber == tableNumbers[index])
964                     {
965                         cr.setSourceLevel(
966                             cr.getSourceLevel() - 1);
967                         break;
968                     }
969                 }
970             }
971             return getNewJoinCondition(leftOperand, rightOperand);
972         }
973     }
974 
975     /**
976      * Flatten this subquery into the outer query block
977      * as an exists join.  
978      * At this point we are only flattening non-aggregate subqueries
979      * with a single FBT in the from list.
980      * So, we transform all FBTs in the from list into ExistBaseTables,
981      * update the dependency lists for each of the tables and then
982      * flatten the subquery.
983      * RESOLVE - we will need to modify this logic to account
984      * for aggregates as we support flattening
985      * for them.
986      *
987      * @param   numTables           Number of tables in the DML Statement
988      * @param   outerFromList       FromList from outer query block
989      * @param   outerSubqueryList   SubqueryList from outer query block
990      * @param   outerPredicateList  PredicateList from outer query block
991      * @param   flattenableNotExists Is it a flattening into a NOT EXISTS join
992      *
993      * @return  The modified expression
994      *
995      * @exception StandardException     Thrown on error
996      */
997     private ValueNode flattenToExistsJoin(int numTables,
998                                           FromList outerFromList, 
999                                           SubqueryList outerSubqueryList,
1000                                          PredicateList outerPredicateList,
1001                                          boolean flattenableNotExists)
1002        throws StandardException
1003    {
1004        SelectNode select = (SelectNode) resultSet;
1005
1006        // Replace the FromBaseTables in the from list with ExistBaseTables
1007        select.getFromList().genExistsBaseTables(resultSet.getReferencedTableMap(),
1008                outerFromList, flattenableNotExists);
1009
1010        /* NOTE: Because we are currently only flattening single table subqueries
1011         * whose predicates are all pushable, we simply follow the rest of the
1012         * flattening algorithm for unique subqueries.  Should we decide to 
1013         * loosen these restrictions then we need to do more work such as:
1014         *
1015         * Mark all of the predicates from the subquery as non-pullable. They must
1016         * not be pulled so that we can guarantee correctness.  Otherwise, we could
1017         * add or subtract rows from the result set.
1018         *
1019         * Remap all of the non-correlated CRs in the predicate list so that they
1020         * point to the correct source.  (We've chopped a level out of the RCL/VCN
1021         * chain.)  We then transfer those predicates to the PRN in the subquery's
1022         * from list.
1023         */
1024
1025        return flattenToNormalJoin(numTables, outerFromList,
1026                                   outerSubqueryList, outerPredicateList);
1027    }
1028
1029    /**
1030     * Check to see if we have a Variant value below us.
1031     * If so, return true.  Caches the result so multiple
1032     * calls are ok.
1033     *  
1034     * @return boolean whether we have 
1035     *
1036     * @exception StandardException     Thrown on error
1037     */
1038    private boolean isInvariant() throws StandardException
1039    {
1040        if (doneInvariantCheck)
1041        {
1042            return !foundVariant;
1043        }
1044
1045        doneInvariantCheck = true;
1046        HasVariantValueNodeVisitor visitor = new HasVariantValueNodeVisitor();
1047        resultSet.accept(visitor);
1048        foundVariant = visitor.hasVariant();
1049        return !foundVariant;
1050    }
1051
1052    /**
1053     * Check to see if this subquery has correlated
1054     * column references.  Only useful results if
1055     * called AFTER binding (after CRs have been bound).
1056     *
1057     * @return whether the subquery has correlated column
1058     *  references.
1059     * @exception StandardException     Thrown on error
1060     */
1061    public boolean hasCorrelatedCRs() throws StandardException
1062    {
1063        if (doneCorrelationCheck)
1064        {
1065            return foundCorrelation;
1066        }
1067        doneCorrelationCheck = true;
1068
1069        ResultSetNode realSubquery = resultSet;
1070        ResultColumnList oldRCL = null;
1071
1072        /* If we have pushed the new join predicate on top, we want to disregard it
1073         * to see if anything under the predicate is correlated.  If nothing correlated
1074         * under the new join predicate, we could then materialize the subquery.
1075         * See beetle 4373.
1076         */
1077        if (pushedNewPredicate)
1078        {
1079            if (SanityManager.DEBUG)
1080            {
1081                SanityManager.ASSERT(resultSet instanceof ProjectRestrictNode,
1082                    "resultSet expected to be a ProjectRestrictNode!");
1083            }
1084
1085            realSubquery = ((ProjectRestrictNode) resultSet).getChildResult();
1086            oldRCL = realSubquery.getResultColumns();
1087
1088            /* Only first column matters.
1089             */
1090            if (oldRCL.size() > 1)
1091            {
1092                ResultColumnList newRCL = new ResultColumnList();
1093                newRCL.addResultColumn(oldRCL.getResultColumn(1));
1094                realSubquery.setResultColumns(newRCL);
1095            }
1096        }
1097
1098        HasCorrelatedCRsVisitor visitor = new HasCorrelatedCRsVisitor();
1099        realSubquery.accept(visitor);
1100        foundCorrelation = visitor.hasCorrelatedCRs();
1101
1102        if (pushedNewPredicate && (oldRCL.size() > 1))
1103        {
1104            realSubquery.setResultColumns(oldRCL);
1105        }
1106
1107        return foundCorrelation;
1108    }
1109                
1110    /**
1111     * Transform:
1112     *      expresion QuantifiedOperator (select x from ...)
1113     * into
1114     *      (select true from .. where expression <BinaryComparisonOperator> x ...)
1115     *      IS [NOT] NULL
1116     *
1117     * or, if we have an aggregate:
1118     *      (select true from 
1119     *          (select AGG(x) from ...)
1120     *      where expression <BinaryComparisonOperator> x ...)
1121     *      IS [NOT] NULL
1122     *
1123     *
1124     * For ANY and IN subqueries:
1125     *      o  We generate an IS NULL above the SubqueryNode and return the top of
1126     *         the new tree to the caller.
1127     *      o  The operator in the new predicate that is added to the subquery
1128     *         will correspond to the operator that modifies the ANY.
1129     *         (eg, = for = ANY, with = for IN.)
1130     * For ALL and NOT IN subqueries:
1131     *      o  We generate an IS NOT NULL above the SubqueryNode and return the top of
1132     *         the new tree to the caller.
1133     *      o  The operator in the new predicate that is added to the subquery
1134     *         will be a BinaryAllOperatorNode whose bcoNodeType corresponds to 
1135     *         the negation of the operator that modifies the ALL.
1136     *         (eg, <> for = ALL, with <> for NOT IN.)
1137     *
1138     * NOTE: This method is called after the underlying subquery has been
1139     * preprocessed, so we build a new Predicate, not just a new expression.
1140     *
1141     * @param numTables         Number of tables in DML Statement
1142     *
1143     * @return UnaryComparisonOperatorNode  An IS [NOT] NULL above the 
1144     *                                      transformed subquery.
1145     *
1146     * @exception StandardException     Thrown on error
1147     */
1148    private UnaryComparisonOperatorNode pushNewPredicate(
1149                int numTables)
1150            throws StandardException
1151    {
1152        AndNode                     andNode;
1153        BinaryComparisonOperatorNode bcoNode = null;
1154        JBitSet                     tableMap;
1155        Predicate                   predicate;
1156        ResultColumn                firstRC;
1157        ResultColumnList            resultColumns;
1158        UnaryComparisonOperatorNode ucoNode = null;
1159        ValueNode                   oldWhereClause;
1160        ValueNode                   rightOperand;
1161
1162        /* We have to ensure that the resultSet immediately under us has
1163         * a PredicateList, otherwise we can't push the predicate down.
1164         */
1165        resultSet = resultSet.ensurePredicateList(numTables);
1166
1167        /* RESOLVE - once we understand how correlated columns will work, 
1168         * we probably want to mark leftOperand as a correlated column
1169         */
1170        resultColumns = resultSet.getResultColumns();
1171
1172        /*
1173        ** Create a new PR node.  Put it over the original subquery.  resulSet
1174        ** is now the new PR.  We give the chance that things under the PR node
1175        ** can be materialized.  See beetle 4373.
1176        */
1177        ResultColumnList newRCL = resultColumns.copyListAndObjects();
1178        newRCL.genVirtualColumnNodes(resultSet, resultColumns);
1179        resultSet = (ResultSetNode) getNodeFactory().getNode(
1180                                        C_NodeTypes.PROJECT_RESTRICT_NODE,
1181                                        resultSet,  // child
1182                                        newRCL,         // result columns
1183                                        null,           // restriction
1184                                        null,           // restriction list
1185                                        null,           // project subqueries
1186                                        null,           // restrict subqueries  
1187                                        null,
1188                                        getContextManager());
1189        resultColumns = newRCL;
1190    
1191        firstRC = (ResultColumn) resultColumns.elementAt(0);
1192        rightOperand = firstRC.getExpression();
1193
1194        bcoNode = getNewJoinCondition(leftOperand, rightOperand);
1195
1196        ValueNode andLeft = bcoNode;
1197
1198        /* For NOT IN or ALL, and if either side of the comparison is nullable, and the
1199         * subquery can not be flattened (because of that), we need to add IS NULL node
1200         * on top of the nullables, such that the behavior is (beetle 5173):
1201         *
1202         *    (1) If we have nulls in right operand, no row is returned.
1203         *    (2) If subquery result is empty before applying join predicate, every
1204         *        left row (including NULLs) is returned.
1205         *    (3) Otherwise, return {all left row} - {NULLs}
1206         */
1207        if (isNOT_IN() || isALL())
1208        {
1209            boolean leftNullable = leftOperand.getTypeServices().isNullable();
1210            boolean rightNullable = rightOperand.getTypeServices().isNullable();
1211            if (leftNullable || rightNullable)
1212            {
1213                /* Create a normalized structure.
1214                 */
1215                BooleanConstantNode falseNode = (BooleanConstantNode) getNodeFactory().getNode(
1216                                                C_NodeTypes.BOOLEAN_CONSTANT_NODE,
1217                                                Boolean.FALSE,
1218                                                getContextManager());
1219                OrNode newOr = (OrNode) getNodeFactory().getNode(
1220                                                C_NodeTypes.OR_NODE,
1221                                                bcoNode,
1222                                                falseNode,
1223                                                getContextManager());
1224                newOr.postBindFixup();
1225                andLeft = newOr;
1226
1227                if (leftNullable)
1228                {
1229                    UnaryComparisonOperatorNode leftIsNull = (UnaryComparisonOperatorNode)
1230                                    getNodeFactory().getNode(
1231                                                        C_NodeTypes.IS_NULL_NODE,
1232                                                        leftOperand,
1233                                                        getContextManager());
1234                    leftIsNull.bindComparisonOperator();
1235                    newOr = (OrNode) getNodeFactory().getNode(
1236                                                    C_NodeTypes.OR_NODE,
1237                                                    leftIsNull,
1238                                                    andLeft,
1239                                                    getContextManager());
1240                    newOr.postBindFixup();
1241                    andLeft = newOr;
1242                }
1243                if (rightNullable)
1244                {
1245                    UnaryComparisonOperatorNode rightIsNull = (UnaryComparisonOperatorNode)
1246                                    getNodeFactory().getNode(
1247                                                        C_NodeTypes.IS_NULL_NODE,
1248                                                        rightOperand,
1249                                                        getContextManager());
1250                    rightIsNull.bindComparisonOperator();
1251                    newOr = (OrNode) getNodeFactory().getNode(
1252                                                    C_NodeTypes.OR_NODE,
1253                                                    rightIsNull,
1254                                                    andLeft,
1255                                                    getContextManager());
1256                    newOr.postBindFixup();
1257                    andLeft = newOr;
1258                }
1259            }
1260        }
1261
1262        /* Place an AndNode above the <BinaryComparisonOperator> */
1263        andNode = (AndNode) getNodeFactory().getNode(
1264                                                    C_NodeTypes.AND_NODE,
1265                                                    andLeft,
1266                                                    getTrueNode(),
1267                                                    getContextManager());
1268
1269        /* Build the referenced table map for the new predicate */
1270        tableMap = new JBitSet(numTables);
1271        andNode.postBindFixup();
1272
1273        /* Put the AndNode under a Predicate */
1274        predicate = (Predicate) getNodeFactory().getNode(
1275                                        C_NodeTypes.PREDICATE,
1276                                        andNode,
1277                                        tableMap,
1278                                        getContextManager());
1279        predicate.categorize();
1280
1281        /* Push the new Predicate to the subquery's list */
1282        resultSet = resultSet.addNewPredicate(predicate);
1283
1284        /* Clean up the leftOperand and subquery ResultColumn */
1285        leftOperand = null;
1286        firstRC.setType(getTypeServices());
1287        firstRC.setExpression(getTrueNode());
1288
1289        /* Add the IS [NOT] NULL above the SubqueryNode */
1290        switch (subqueryType)
1291        {
1292            case IN_SUBQUERY:
1293            case EQ_ANY_SUBQUERY:
1294            case NE_ANY_SUBQUERY:
1295            case LE_ANY_SUBQUERY:
1296            case LT_ANY_SUBQUERY:
1297            case GE_ANY_SUBQUERY:
1298            case GT_ANY_SUBQUERY:
1299                ucoNode = (UnaryComparisonOperatorNode) 
1300                                    getNodeFactory().getNode(
1301                                                C_NodeTypes.IS_NOT_NULL_NODE,
1302                                                this,
1303                                                getContextManager());
1304                break;
1305
1306            case NOT_IN_SUBQUERY:
1307            case EQ_ALL_SUBQUERY:
1308            case NE_ALL_SUBQUERY:
1309            case LE_ALL_SUBQUERY:
1310            case LT_ALL_SUBQUERY:
1311            case GE_ALL_SUBQUERY:
1312            case GT_ALL_SUBQUERY:
1313                ucoNode = (UnaryComparisonOperatorNode) 
1314                                    getNodeFactory().getNode(
1315                                                    C_NodeTypes.IS_NULL_NODE,
1316                                                    this,
1317                                                    getContextManager());
1318                break;
1319        }
1320        ucoNode.bindComparisonOperator();
1321        return ucoNode;
1322    }
1323
1324    /**
1325     * Build a new join condition between the leftOperand
1326     * and the rightOperand.  The comparison operator
1327     * is dependent on the subquery type.
1328     *
1329     * @param leftOperand   The left operand for the new condition.
1330     * @param rightOperand  The right operand for the new condition.
1331     *
1332     * @exception StandardException     Thrown on error
1333     */
1334    private BinaryComparisonOperatorNode getNewJoinCondition(
1335                ValueNode leftOperand, 
1336                ValueNode rightOperand)
1337        throws StandardException
1338    {
1339        BinaryComparisonOperatorNode bcoNode = null;
1340
1341        /* NOTE: If we are an expression subquery that's getting
1342         * flattened then our subqueryType is EXPRESSION_SUBQUERY.
1343         * However, we can get the comparison type from the 
1344         * parentComparisonOperator.  In that case we dovetail on
1345         * the ANY subquery types.
1346         */
1347        int operatorType = subqueryType;
1348        if (subqueryType == EXPRESSION_SUBQUERY)
1349        {
1350            if (SanityManager.DEBUG)
1351            {
1352                SanityManager.ASSERT(parentComparisonOperator != null,
1353                    "parentComparisonOperator expected to be non-null");
1354            }
1355
1356            int parentOperator = -1;
1357
1358            if (parentComparisonOperator.isRelationalOperator())
1359            {
1360                RelationalOperator ro = (RelationalOperator)parentComparisonOperator;
1361                parentOperator = ro.getOperator();
1362            }
1363
1364            if (parentOperator == RelationalOperator.EQUALS_RELOP)
1365            {
1366                operatorType = EQ_ANY_SUBQUERY;
1367            }
1368            else if (parentOperator == RelationalOperator.NOT_EQUALS_RELOP)
1369            {
1370                operatorType = NE_ANY_SUBQUERY;
1371            }
1372            else if (parentOperator == RelationalOperator.LESS_EQUALS_RELOP)
1373            {
1374                operatorType = LE_ANY_SUBQUERY;
1375            }
1376            else if (parentOperator == RelationalOperator.LESS_THAN_RELOP)
1377            {
1378                operatorType = LT_ANY_SUBQUERY;
1379            }
1380            else if (parentOperator == RelationalOperator.GREATER_EQUALS_RELOP)
1381            {
1382                operatorType = GE_ANY_SUBQUERY;
1383            }
1384            else if (parentOperator == RelationalOperator.GREATER_THAN_RELOP)
1385            {
1386                operatorType = GT_ANY_SUBQUERY;
1387            }
1388        }
1389
1390        int bcoType = 0;
1391        int nodeType = 0;
1392
1393        /* Build the <BinaryComparisonOperator> */
1394        switch (operatorType)
1395        {
1396            case IN_SUBQUERY:
1397            case EQ_ANY_SUBQUERY:
1398            case NOT_IN_SUBQUERY:
1399            case NE_ALL_SUBQUERY:
1400                nodeType = C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE;
1401                break;
1402
1403            case NE_ANY_SUBQUERY:
1404            case EQ_ALL_SUBQUERY:
1405                nodeType = C_NodeTypes.BINARY_NOT_EQUALS_OPERATOR_NODE;
1406                break;
1407
1408            case LE_ANY_SUBQUERY:
1409            case GT_ALL_SUBQUERY:
1410                nodeType = C_NodeTypes.BINARY_LESS_EQUALS_OPERATOR_NODE;
1411                break;
1412
1413            case LT_ANY_SUBQUERY:
1414            case GE_ALL_SUBQUERY:
1415                nodeType = C_NodeTypes.BINARY_LESS_THAN_OPERATOR_NODE;
1416                break;
1417
1418            case GE_ANY_SUBQUERY:
1419            case LT_ALL_SUBQUERY:
1420                nodeType = C_NodeTypes.BINARY_GREATER_EQUALS_OPERATOR_NODE;
1421                break;
1422
1423            case GT_ANY_SUBQUERY:
1424            case LE_ALL_SUBQUERY:
1425                nodeType = C_NodeTypes.BINARY_GREATER_THAN_OPERATOR_NODE;
1426                break;
1427
1428            default:
1429                if (SanityManager.DEBUG)
1430                SanityManager.ASSERT(false,
1431                    "subqueryType (" + subqueryType + ") is an unexpected type");
1432        }
1433
1434        bcoNode =  (BinaryComparisonOperatorNode) 
1435                        getNodeFactory().getNode(
1436                            nodeType,
1437                            leftOperand,
1438                            rightOperand,
1439                            getContextManager());
1440
1441        bcoNode.bindComparisonOperator();
1442        return bcoNode;
1443    }
1444
1445
1446    /**
1447     * Eliminate NotNodes in the current query block.  We traverse the tree, 
1448     * inverting ANDs and ORs and eliminating NOTs as we go.  We stop at 
1449     * ComparisonOperators and boolean expressions.  We invert 
1450     * ComparisonOperators and replace boolean expressions with 
1451     * boolean expression = false.
1452     * NOTE: Since we do not recurse under ComparisonOperators, there
1453     * still could be NotNodes left in the tree.
1454     *
1455     * @param   underNotNode        Whether or not we are under a NotNode.
1456     *                          
1457     *
1458     * @return      The modified expression
1459     *
1460     * @exception StandardException     Thrown on error
1461     */
1462    ValueNode eliminateNots(boolean underNotNode) 
1463                    throws StandardException
1464    {
1465        ValueNode result = this;
1466
1467        if (underNotNode)
1468        {
1469            /* Negate the subqueryType. For expression subqueries
1470             * we simply return subquery = false
1471             */
1472            /* RESOLVE - This code needs to get cleaned up once there are
1473             * more subquery types.  (Consider using arrays.)
1474             */
1475            switch (subqueryType)
1476            {
1477                case EXPRESSION_SUBQUERY:
1478                    result = genEqualsFalseTree();
1479                    break;
1480
1481                case EXISTS_SUBQUERY:
1482                    subqueryType = NOT_EXISTS_SUBQUERY;
1483                    break;
1484
1485                /* ANY subqueries */
1486                case IN_SUBQUERY:
1487                case EQ_ANY_SUBQUERY:
1488                    subqueryType = NOT_IN_SUBQUERY;
1489                    break;
1490
1491                case NE_ANY_SUBQUERY:
1492                    subqueryType = EQ_ALL_SUBQUERY;
1493                    break;
1494
1495                case GE_ANY_SUBQUERY:
1496                    subqueryType = LT_ALL_SUBQUERY;
1497                    break;
1498
1499                case GT_ANY_SUBQUERY:
1500                    subqueryType = LE_ALL_SUBQUERY;
1501                    break;
1502
1503                case LE_ANY_SUBQUERY:
1504                    subqueryType = GT_ALL_SUBQUERY;
1505                    break;
1506
1507                case LT_ANY_SUBQUERY:
1508                    subqueryType = GE_ALL_SUBQUERY;
1509                    break;
1510
1511                /* ALL subqueries - no need for NOT NOT_IN_SUBQUERY, since
1512                 * NOT IN only comes into existence here.
1513                 */
1514                case EQ_ALL_SUBQUERY:
1515                    subqueryType = NE_ANY_SUBQUERY;
1516                    break;
1517
1518                case NE_ALL_SUBQUERY:
1519                    subqueryType = EQ_ANY_SUBQUERY;
1520                    break;
1521
1522                case GE_ALL_SUBQUERY:
1523                    subqueryType = LT_ANY_SUBQUERY;
1524                    break;
1525
1526                case GT_ALL_SUBQUERY:
1527                    subqueryType = LE_ANY_SUBQUERY;
1528                    break;
1529
1530                case LE_ALL_SUBQUERY:
1531                    subqueryType = GT_ANY_SUBQUERY;
1532                    break;
1533
1534                case LT_ALL_SUBQUERY:
1535                    subqueryType = GE_ANY_SUBQUERY;
1536                    break;
1537
1538                default:
1539                    if (SanityManager.DEBUG)
1540                    SanityManager.ASSERT(false,
1541                        "NOT is not supported for this time of subquery");
1542            }
1543        }
1544
1545        /* Halt recursion here, as each query block is preprocessed separately */
1546        return result;
1547    }
1548
1549    /**
1550     * Finish putting an expression into conjunctive normal
1551     * form.  An expression tree in conjunctive normal form meets
1552     * the following criteria:
1553     *      o  If the expression tree is not null,
1554     *         the top level will be a chain of AndNodes terminating
1555     *         in a true BooleanConstantNode.
1556     *      o  The left child of an AndNode will never be an AndNode.
1557     *      o  Any right-linked chain that includes an AndNode will
1558     *         be entirely composed of AndNodes terminated by a true BooleanConstantNode.
1559     *      o  The left child of an OrNode will never be an OrNode.
1560     *      o  Any right-linked chain that includes an OrNode will
1561     *         be entirely composed of OrNodes terminated by a false BooleanConstantNode.
1562     *      o  ValueNodes other than AndNodes and OrNodes are considered
1563     *         leaf nodes for purposes of expression normalization.
1564     *         In other words, we won't do any normalization under
1565     *         those nodes.
1566     *
1567     * In addition, we track whether or not we are under a top level AndNode.  
1568     * SubqueryNodes need to know this for subquery flattening.
1569     *
1570     * @param   underTopAndNode     Whether or not we are under a top level AndNode.
1571     *                          
1572     *
1573     * @return      The modified expression
1574     *
1575     * @exception StandardException     Thrown on error
1576     */
1577    public ValueNode changeToCNF(boolean underTopAndNode) 
1578                    throws StandardException
1579    {
1580        /* Remember whether or not we are immediately under a top leve
1581         * AndNode.  This is important for subquery flattening.
1582         * (We can only flatten subqueries under a top level AndNode.)
1583         */
1584         this.underTopAndNode = underTopAndNode;
1585
1586        /* Halt recursion here, as each query block is preprocessed separately */
1587        return this;
1588    }
1589
1590    /**
1591     * Categorize this predicate.  Initially, this means
1592     * building a bit map of the referenced tables for each predicate.
1593     * If the source of this ColumnReference (at the next underlying level) 
1594     * is not a ColumnReference or a VirtualColumnNode then this predicate
1595     * will not be pushed down.
1596     *
1597     * For example, in:
1598     *      select * from (select 1 from s) a (x) where x = 1
1599     * we will not push down x = 1.
1600     * NOTE: It would be easy to handle the case of a constant, but if the
1601     * inner SELECT returns an arbitrary expression, then we would have to copy
1602     * that tree into the pushed predicate, and that tree could contain
1603     * subqueries and method calls.
1604     * RESOLVE - revisit this issue once we have views.
1605     *
1606     * @param referencedTabs    JBitSet with bit map of referenced FromTables
1607     * @return boolean      Whether or not source.expression is a ColumnReference
1608     *                      or a VirtualColumnNode.
1609     *
1610     * @exception StandardException     Thrown on error
1611     */
1612    public boolean categorize(JBitSet referencedTabs, boolean simplePredsOnly)
1613        throws StandardException
1614    {
1615        /* We stop here when only considering simple predicates
1616         *  as we don't consider method calls when looking
1617         * for null invariant predicates.
1618         */
1619        if (simplePredsOnly)
1620        {
1621            return false;
1622        }
1623
1624        /* RESOLVE - We need to or in a bit map when there are correlation columns */
1625
1626        /* We categorize a query block at a time, so stop the recursion here */
1627
1628        /* Predicates with subqueries are not pushable for now */
1629
1630        /*
1631        ** If we can materialize the subquery, then it is 
1632        ** both invariant and non-correlated.  And so it
1633        ** is pushable.
1634        */
1635        return isMaterializable();
1636
1637    }
1638
1639    /*
1640    ** Subquery is materializable if
1641    ** it is an expression subquery that
1642    ** has no correlations and is invariant.
1643    */
1644    boolean isMaterializable() throws StandardException
1645    {
1646        boolean retval = (subqueryType == EXPRESSION_SUBQUERY) && 
1647                          !hasCorrelatedCRs() && 
1648                          isInvariant();
1649        /* If we can materialize the subquery, then we set
1650         * the level of all of the tables to 0 so that we can
1651         * consider bulk fetch for them.
1652         */
1653        if (retval)
1654        {
1655            if (resultSet instanceof SelectNode)
1656            {
1657                SelectNode select = (SelectNode) resultSet;
1658                FromList fromList = select.getFromList();
1659                fromList.setLevel(0);
1660            }
1661        }
1662
1663        return retval;
1664    }
1665
1666    /**
1667     * Optimize this SubqueryNode.  
1668     *
1669     * @param dataDictionary    The DataDictionary to use for optimization
1670     * @param outerRows         The optimizer's estimate of the number of
1671     *                          times this subquery will be executed.
1672     *
1673     * @exception StandardException     Thrown on error
1674     */
1675
1676    public void optimize(DataDictionary dataDictionary, double outerRows) 
1677                    throws StandardException
1678    {
1679        /* RESOLVE - is there anything else that we need to do for this
1680         * node.
1681         */
1682
1683        /* Optimize the underlying result set */
1684        resultSet = resultSet.optimize(dataDictionary, null, outerRows);
1685    }
1686
1687    /**
1688     * Make any changes to the access paths, as decided by the optimizer.
1689     *
1690     * @exception StandardException     Thrown on error
1691     */
1692    public void modifyAccessPaths() throws StandardException
1693    {
1694        resultSet = resultSet.modifyAccessPaths();
1695    }
1696
1697    /**
1698     * Return the variant type for the underlying expression.
1699     * The variant type can be:
1700     *      VARIANT             - variant within a scan
1701     *                            (method calls and non-static field access)
1702     *      SCAN_INVARIANT      - invariant within a scan
1703     *                            (column references from outer tables)
1704     *      QUERY_INVARIANT     - invariant within the life of a query
1705     *                            (constant expressions)
1706     *
1707     * @return  The variant type for the underlying expression.
1708     *
1709     * @exception StandardException     Thrown on error
1710     */
1711    protected int getOrderableVariantType() throws StandardException
1712    {
1713        /* 
1714         * If the subquery is variant, than return
1715         * VARIANT.  Otherwise, if we have an expression
1716         * subquery and no correlated CRs we are going
1717         * to materialize it, so it is QUERY_INVARIANT.
1718         * Otherwise, SCAN_INVARIANT.
1719         */
1720        if (isInvariant())
1721        {
1722            if (!hasCorrelatedCRs() && 
1723                (subqueryType == EXPRESSION_SUBQUERY))
1724            {
1725                return Qualifier.QUERY_INVARIANT;
1726            }
1727            else
1728            {
1729                return Qualifier.SCAN_INVARIANT;
1730            }
1731        }
1732        else
1733        {
1734            return Qualifier.VARIANT;
1735        }
1736    }
1737
1738    /**
1739     * Do code generation for this subquery.
1740     *
1741     * @param expressionBuilder The ExpressionClassBuilder for the class being built
1742     * @param mbex  The method the expression will go into
1743     *
1744     *
1745     * @exception StandardException     Thrown on error
1746     */
1747
1748    public void generateExpression(
1749                                    ExpressionClassBuilder expressionBuilder,
1750                                    MethodBuilder mbex)
1751                                throws StandardException
1752    {
1753        CompilerContext cc = getCompilerContext();
1754        String            resultSetString;
1755
1756        ///////////////////////////////////////////////////////////////////////////
1757        //
1758        //  Subqueries should not appear in Filter expressions. We should get here
1759        //  only if we're compiling a query. That means that our class builder
1760        //  is an activation builder. If we ever allow subqueries in filters, we'll
1761        //  have to revisit this code.
1762        //
1763        ///////////////////////////////////////////////////////////////////////////
1764        
1765        if (SanityManager.DEBUG)
1766        {
1767            SanityManager.ASSERT(expressionBuilder instanceof ActivationClassBuilder,
1768                "Expecting an ActivationClassBuilder");
1769        }
1770
1771        ActivationClassBuilder  acb = (ActivationClassBuilder) expressionBuilder;
1772        /* Reuse generated code, where possible */
1773
1774        /* Generate the appropriate (Any or Once) ResultSet */
1775        if (subqueryType == EXPRESSION_SUBQUERY)
1776        {
1777            resultSetString = "getOnceResultSet";
1778        }
1779        else
1780        {
1781            resultSetString = "getAnyResultSet";
1782        }
1783
1784        // Get cost estimate for underlying subquery
1785        CostEstimate costEstimate = resultSet.getFinalCostEstimate();
1786
1787        /* Generate a new method.  It's only used within the other
1788         * exprFuns, so it could be private. but since we don't
1789         * generate the right bytecodes to invoke private methods,
1790         * we just make it protected.  This generated class won't
1791         * have any subclasses, certainly! (nat 12/97)
1792         */
1793        String   subqueryTypeString =
1794                            getTypeCompiler().interfaceName();
1795        MethodBuilder   mb = acb.newGeneratedFun(subqueryTypeString, Modifier.PROTECTED);
1796
1797        /* Declare the field to hold the suquery's ResultSet tree */
1798        LocalField rsFieldLF = acb.newFieldDeclaration(Modifier.PRIVATE, ClassName.NoPutResultSet);
1799
1800        ResultSetNode subNode = null;
1801
1802        if (!isMaterializable())
1803        {
1804            MethodBuilder executeMB = acb.getExecuteMethod();
1805            if (pushedNewPredicate && (! hasCorrelatedCRs()))
1806            {
1807                /* We try to materialize the subquery if it can fit in the memory.  We
1808                 * evaluate the subquery first.  If the result set fits in the memory,
1809                 * we replace the resultset with in-memory unions of row result sets.
1810                 * We do this trick by replacing the child result with a new node --
1811                 * MaterializeSubqueryNode, which essentially generates the suitable
1812                 * code to materialize the subquery if possible.  This may have big
1813                 * performance improvement.  See beetle 4373.
1814                 */
1815                if (SanityManager.DEBUG)
1816                {
1817                    SanityManager.ASSERT(resultSet instanceof ProjectRestrictNode,
1818                        "resultSet expected to be a ProjectRestrictNode!");
1819                }
1820                subNode = ((ProjectRestrictNode) resultSet).getChildResult();
1821                LocalField subRS = acb.newFieldDeclaration(Modifier.PRIVATE, ClassName.NoPutResultSet);
1822                mb.getField(subRS);
1823                mb.conditionalIfNull();
1824
1825                ResultSetNode materialSubNode = new MaterializeSubqueryNode(subRS);
1826
1827                // Propagate the resultSet's cost estimate to the new node.
1828                materialSubNode.costEstimate = resultSet.getFinalCostEstimate();
1829
1830                ((ProjectRestrictNode) resultSet).setChildResult(materialSubNode);
1831
1832                /* Evaluate subquery resultset here first.  Next time when we come to
1833                 * this subquery it may be replaced by a bunch of unions of rows.
1834                 */
1835                subNode.generate(acb, mb);
1836                mb.startElseCode();
1837                mb.getField(subRS);
1838                mb.completeConditional();
1839        
1840                mb.setField(subRS);
1841
1842                executeMB.pushNull( ClassName.NoPutResultSet);
1843                executeMB.setField(subRS);
1844            }
1845
1846            executeMB.pushNull( ClassName.NoPutResultSet);
1847            executeMB.setField(rsFieldLF);
1848 
1849            // now we fill in the body of the conditional
1850            mb.getField(rsFieldLF);
1851            mb.conditionalIfNull();
1852        }
1853
1854        acb.pushGetResultSetFactoryExpression(mb);
1855
1856        // start of args
1857        int nargs;
1858
1859        /* Inside here is where subquery could already have been materialized. 4373
1860         */
1861        resultSet.generate(acb, mb);
1862
1863        /* Get the next ResultSet #, so that we can number the subquery's 
1864         * empty row ResultColumnList and Once/Any ResultSet.
1865         */
1866        int subqResultSetNumber = cc.getNextResultSetNumber();
1867
1868        /* We will be reusing the RCL from the subquery's ResultSet for the 
1869         * empty row function.  We need to reset the resultSetNumber in the
1870         * RCL, before we generate that function.  Now that we've called
1871         * generate() on the subquery's ResultSet, we can reset that
1872         * resultSetNumber.
1873         */
1874        resultSet.getResultColumns().setResultSetNumber(subqResultSetNumber);
1875
1876        /* Generate code for empty row */
1877        resultSet.getResultColumns().generateNulls(acb, mb);
1878
1879        /*
1880         *  arg1: suqueryExpress - Expression for subquery's
1881         *        ResultSet
1882         *  arg2: Activation
1883         *  arg3: Method to generate Row with null(s) if subquery
1884         *        Result Set is empty
1885         */
1886        if (subqueryType == EXPRESSION_SUBQUERY)
1887        {
1888            int cardinalityCheck;
1889
1890            /* No need to do sort if subquery began life as a distinct expression subquery.
1891             * (We simply check for a single unique value at execution time.)
1892             * No need for cardinality check if we know that underlying
1893             * ResultSet can contain at most 1 row.
1894             * RESOLVE - Not necessary if we know we
1895             * are getting a single row because of a unique index.
1896             */
1897            if (distinctExpression)
1898            {
1899                cardinalityCheck = OnceResultSet.UNIQUE_CARDINALITY_CHECK;
1900            }
1901            else if (resultSet.returnsAtMostOneRow())
1902            {
1903                cardinalityCheck = OnceResultSet.NO_CARDINALITY_CHECK;
1904            }
1905            else
1906            {
1907                cardinalityCheck = OnceResultSet.DO_CARDINALITY_CHECK;
1908            }
1909
1910            /*  arg4: int - whether or not cardinality check is required
1911             *              DO_CARDINALITY_CHECK - required
1912             *              NO_CARDINALITY_CHECK - not required
1913             *              UNIQUE_CARDINALITY_CHECK - verify single
1914             *                                          unique value
1915             */
1916            mb.push(cardinalityCheck);
1917            nargs = 8;
1918
1919        } else {
1920            nargs = 7;
1921        }
1922
1923        mb.push(subqResultSetNumber);
1924        mb.push(subqueryNumber);
1925        mb.push(pointOfAttachment);
1926        mb.push(costEstimate.rowCount());
1927        mb.push(costEstimate.getEstimatedCost());
1928
1929        mb.callMethod(VMOpcode.INVOKEINTERFACE, (String  ) null, resultSetString, ClassName.NoPutResultSet, nargs);
1930
1931
1932
1933        /* Fill in the body of the method
1934         * generates the following.
1935         * (NOTE: the close() method only generated for
1936         * materialized subqueries.  All other subqueries
1937         * closed by top result set in the query.):
1938         * 
1939         *  NoPutResultSet  rsFieldX;
1940         *  {
1941         *      <Datatype interface> col;
1942         *      ExecRow r;
1943         *      rsFieldX = (rsFieldX == null) ? outerRSCall() : rsFieldX; // <== NONmaterialized specific
1944         *      rsFieldX.openCore();
1945         *      r = rsFieldX.getNextRowCore();
1946         *      col = (<Datatype interface>) r.getColumn(1);
1947         *      return col;
1948         *  }
1949         *
1950         * MATERIALIZED:
1951         *  NoPutResultSet  rsFieldX;
1952         *  {
1953         *      <Datatype interface> col;
1954         *      ExecRow r;
1955         *      rsFieldX = outerRSCall();
1956         *      rsFieldX.openCore();
1957         *      r = rsFieldX.getNextRowCore();
1958         *      col = (<Datatype interface>) r.getColumn(1);
1959         *      rsFieldX.close();                               // <== materialized specific
1960         *      return col;
1961         *  }
1962         * and adds it to exprFun
1963         */
1964
1965        /* Generate the declarations */ // PUSHCOMPILE
1966        //VariableDeclaration colVar = mb.addVariableDeclaration(subqueryTypeString);
1967        //VariableDeclaration rVar   = mb.addVariableDeclaration(ClassName.ExecRow);
1968
1969        if (!isMaterializable())
1970        {
1971            /* put it back
1972             */
1973            if (pushedNewPredicate && (! hasCorrelatedCRs()))
1974                ((ProjectRestrictNode) resultSet).setChildResult(subNode);
1975
1976            // now we fill in the body of the conditional
1977            mb.startElseCode();
1978              mb.getField(rsFieldLF);
1979            mb.completeConditional();
1980        }
1981        
1982        mb.setField(rsFieldLF);
1983
1984        /* rs.openCore() */
1985        mb.getField(rsFieldLF);
1986        mb.callMethod(VMOpcode.INVOKEINTERFACE, (String  ) null, "openCore", "void", 0);
1987
1988        /* r = rs.next() */
1989        mb.getField(rsFieldLF);
1990        mb.callMethod(VMOpcode.INVOKEINTERFACE, (String  ) null, "getNextRowCore", ClassName.ExecRow, 0);
1991        //mb.putVariable(rVar);
1992        //mb.endStatement();
1993
1994        /* col = (<Datatype interface>) r.getColumn(1) */
1995        //mb.getVariable(rVar);
1996        mb.push(1); // both the Row interface and columnId are 1-based
1997        mb.callMethod(VMOpcode.INVOKEINTERFACE, ClassName.Row, "getColumn", ClassName.DataValueDescriptor, 1);
1998        mb.cast(subqueryTypeString);
1999        //mb.putVariable(colVar);
2000        //mb.endStatement();
2001
2002        /* Only generate the close() method for materialized
2003         * subqueries.  All others will be closed when the
2004         * close() method is called on the top ResultSet.
2005         */
2006        if (isMaterializable())
2007        {
2008            /* rs.close() */
2009            mb.getField(rsFieldLF);
2010            mb.callMethod(VMOpcode.INVOKEINTERFACE, ClassName.ResultSet, "close", "void", 0);
2011        }
2012
2013        /* return col */
2014        //mb.getVariable(colVar);
2015        mb.methodReturn();
2016        mb.complete();
2017
2018        /*
2019        ** If we have an expression subquery, then we
2020        ** can materialize it if it has no correlated
2021        ** column references and is invariant.
2022        */
2023        if (isMaterializable())
2024        {
2025            LocalField lf = generateMaterialization(acb, mb, subqueryTypeString);
2026            mbex.getField(lf);
2027
2028        } else {
2029            /* Generate the call to the new method */
2030            mbex.pushThis();
2031            mbex.callMethod(VMOpcode.INVOKEVIRTUAL, (String  ) null, mb.getName(), subqueryTypeString, 0);
2032        }
2033    }
2034
2035    /*
2036    ** Materialize the subquery in question.  Given the expression
2037    ** that represents the subquery, this returns fieldX where
2038    ** fieldX is set up as follows:
2039    **
2040    ** private ... fieldX
2041    **
2042    ** execute()
2043    ** {
2044    **  fieldX = <subqueryExpression>
2045    **  ...
2046    ** }
2047    **
2048    ** So we wind up evaluating the subquery when we start
2049    ** execution.  Obviously, it is absolutely necessary that
2050    ** the subquery is invariant and has no correlations
2051    ** for this to work.
2052    **
2053    ** Ideally we wouldn't evaluate the expression subquery
2054    ** until we know we need to, but because we are marking
2055    ** this expression subquery as pushable, we must evaluate
2056    ** it up front because it might wind up as a qualification,
2057    ** and we cannot execute a subquery in the store as a
2058    ** qualification because the store executes qualifications
2059    ** while holding a latch.  
2060    **
2061    ** @param acb
2062    ** @param type 
2063    ** @param subqueryExpression
2064    */
2065    private LocalField generateMaterialization(
2066            ActivationClassBuilder  acb,
2067            MethodBuilder mbsq,
2068            String            type)
2069    {
2070        MethodBuilder mb = acb.executeMethod;
2071
2072        // declare field
2073        LocalField field = acb.newFieldDeclaration(Modifier.PRIVATE, type);
2074
2075        /* Generate the call to the new method */
2076        mb.pushThis();
2077        mb.callMethod(VMOpcode.INVOKEVIRTUAL, (String  ) null, mbsq.getName(), type, 0);
2078
2079        // generate: field = value (value is on stack)
2080        mb.setField(field);
2081
2082        return field;
2083    }
2084
2085    /* Private methods on private variables */
2086    private BooleanConstantNode getTrueNode()
2087        throws StandardException
2088    {
2089        if (trueNode == null)
2090        {
2091            trueNode = (BooleanConstantNode) getNodeFactory().getNode(
2092                                            C_NodeTypes.BOOLEAN_CONSTANT_NODE,
2093                                            Boolean.TRUE,
2094                                            getContextManager());
2095        }
2096        return trueNode;
2097    }
2098
2099    /**
2100     * Accept a visitor, and call v.visit()
2101     * on child nodes as necessary.  
2102     * 
2103     * @param v the visitor
2104     *
2105     * @exception StandardException on error
2106     */
2107    public Visitable accept(Visitor v)
2108        throws StandardException
2109    {
2110        Visitable returnNode = v.visit(this);
2111
2112        /* shortcut if we've already done it
2113         */
2114        if ((v instanceof HasCorrelatedCRsVisitor) && doneCorrelationCheck) 
2115        {
2116            ((HasCorrelatedCRsVisitor) v).setHasCorrelatedCRs(foundCorrelation);
2117            return returnNode;
2118        }
2119    
2120        if (v.skipChildren(this))
2121        {
2122            return returnNode;
2123        }
2124
2125        if (resultSet != null && !v.stopTraversal())
2126        {
2127            resultSet = (ResultSetNode)resultSet.accept(v);
2128        }
2129
2130        if (leftOperand != null && !v.stopTraversal())
2131        {
2132            leftOperand = (ValueNode)leftOperand.accept(v);
2133        }
2134        return returnNode;
2135    }
2136
2137    private boolean isIN()
2138    {
2139        return subqueryType == IN_SUBQUERY;
2140    }
2141
2142    private boolean isNOT_IN()
2143    {
2144        return subqueryType == NOT_IN_SUBQUERY;
2145    }
2146
2147    private boolean isANY()
2148    {
2149        switch (subqueryType)
2150        {
2151            case EQ_ANY_SUBQUERY:
2152            case NE_ANY_SUBQUERY:
2153            case LE_ANY_SUBQUERY:
2154            case LT_ANY_SUBQUERY:
2155            case GE_ANY_SUBQUERY:
2156            case GT_ANY_SUBQUERY:
2157                return true;
2158
2159            default:
2160                return false;
2161        }
2162    }
2163
2164    private boolean isALL()
2165    {
2166        switch (subqueryType)
2167        {
2168            case EQ_ALL_SUBQUERY:
2169            case NE_ALL_SUBQUERY:
2170            case LE_ALL_SUBQUERY:
2171            case LT_ALL_SUBQUERY:
2172            case GE_ALL_SUBQUERY:
2173            case GT_ALL_SUBQUERY:
2174                return true;
2175
2176            default:
2177                return false;
2178        }
2179    }
2180
2181    private boolean isEXISTS()
2182    {
2183        return subqueryType == EXISTS_SUBQUERY;
2184    }
2185
2186    private boolean isNOT_EXISTS()
2187    {
2188        return subqueryType == NOT_EXISTS_SUBQUERY;
2189    }
2190
2191    /**
2192     * Convert this IN/ANY subquery, which is known to return at most 1 row,
2193     * to an equivalent expression subquery.
2194     *
2195     * @exception StandardException     Thrown on error
2196     */
2197    private void changeToCorrespondingExpressionType()
2198        throws StandardException
2199    {
2200        BinaryOperatorNode bcon = null;
2201
2202        switch (subqueryType)
2203        {
2204            case EQ_ANY_SUBQUERY:
2205            case IN_SUBQUERY:
2206                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2207                                    C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,
2208                                    leftOperand,
2209                                    this,
2210                                    getContextManager());
2211                break;
2212
2213            case NE_ANY_SUBQUERY:
2214                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2215                                C_NodeTypes.BINARY_NOT_EQUALS_OPERATOR_NODE,
2216                                leftOperand,
2217                                this,
2218                                getContextManager());
2219                break;
2220
2221            case LE_ANY_SUBQUERY:
2222                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2223                                C_NodeTypes.BINARY_LESS_EQUALS_OPERATOR_NODE,
2224                                leftOperand,
2225                                this,
2226                                getContextManager());
2227                break;
2228
2229            case LT_ANY_SUBQUERY:
2230                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2231                            C_NodeTypes.BINARY_LESS_THAN_OPERATOR_NODE,
2232                            leftOperand,
2233                            this,
2234                            getContextManager());
2235                break;
2236
2237            case GE_ANY_SUBQUERY:
2238                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2239                            C_NodeTypes.BINARY_GREATER_EQUALS_OPERATOR_NODE,
2240                            leftOperand,
2241                            this,
2242                            getContextManager());
2243                break;
2244
2245            case GT_ANY_SUBQUERY:
2246                bcon = (BinaryOperatorNode) getNodeFactory().getNode(
2247                                C_NodeTypes.BINARY_GREATER_THAN_OPERATOR_NODE,
2248                                leftOperand,
2249                                this,
2250                                getContextManager());
2251                break;
2252        }
2253
2254        // clean up the state of the tree to reflect a bound expression subquery
2255        subqueryType = EXPRESSION_SUBQUERY;
2256        setDataTypeServices(resultSet.getResultColumns());
2257
2258        parentComparisonOperator = (BinaryComparisonOperatorNode) bcon;
2259        /* Set type info for the operator node */
2260        parentComparisonOperator.bindComparisonOperator();
2261        leftOperand = null;
2262   }
2263
2264    private void setDataTypeServices(ResultColumnList resultColumns)
2265        throws StandardException
2266    {
2267        DataTypeDescriptor dts;
2268
2269        /* Set the result type for this subquery (must be nullable).
2270         * Quantified predicates will return boolean.
2271         * However, the return type of the subquery's result list will 
2272         * probably not be boolean at this point, because we do not
2273         * transform the subquery (other than EXISTS) into 
2274         * (select true/false ...) until preprocess().  So, we force 
2275         * the return type to boolean.
2276         */
2277        if (subqueryType == EXPRESSION_SUBQUERY)
2278        {
2279            dts = ((ResultColumn) resultColumns.elementAt(0)).getTypeServices();
2280        }
2281        else
2282        {
2283            dts = getTrueNode().getTypeServices();
2284        }
2285
2286        /* If datatype of underlying resultSet is nullable, reuse it, otherwise
2287         * we need to generate a new one.
2288         */
2289        if (! dts.isNullable())
2290        {
2291            dts = new DataTypeDescriptor(dts, true);
2292        }
2293        setType(dts);
2294    }
2295        
2296    /**
2297     * {@inheritDoc}
2298     */    
2299    protected boolean isEquivalent(ValueNode o)
2300    {
2301        return false;
2302    }
2303}
2304
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags