Package org.teiid.query.optimizer

Source Code of org.teiid.query.optimizer.TestOptimizer$DependentProjectNode

/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership.  Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/

package org.teiid.query.optimizer;

import static org.junit.Assert.*;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.junit.Test;
import org.teiid.api.exception.query.QueryPlannerException;
import org.teiid.common.buffer.BufferManager;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.core.TeiidRuntimeException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.query.analysis.AnalysisRecord;
import org.teiid.query.function.FunctionLibrary;
import org.teiid.query.function.FunctionTree;
import org.teiid.query.mapping.relational.QueryNode;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
import org.teiid.query.optimizer.capabilities.CapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
import org.teiid.query.optimizer.relational.AliasGenerator;
import org.teiid.query.optimizer.relational.rules.CapabilitiesUtil;
import org.teiid.query.optimizer.relational.rules.RuleChooseDependent;
import org.teiid.query.parser.QueryParser;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.processor.relational.AccessNode;
import org.teiid.query.processor.relational.DependentAccessNode;
import org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy;
import org.teiid.query.processor.relational.GroupingNode;
import org.teiid.query.processor.relational.JoinNode;
import org.teiid.query.processor.relational.JoinStrategy;
import org.teiid.query.processor.relational.MergeJoinStrategy;
import org.teiid.query.processor.relational.NestedLoopJoinStrategy;
import org.teiid.query.processor.relational.NestedTableJoinStrategy;
import org.teiid.query.processor.relational.NullNode;
import org.teiid.query.processor.relational.PlanExecutionNode;
import org.teiid.query.processor.relational.ProjectIntoNode;
import org.teiid.query.processor.relational.ProjectNode;
import org.teiid.query.processor.relational.RelationalNode;
import org.teiid.query.processor.relational.RelationalPlan;
import org.teiid.query.processor.relational.SelectNode;
import org.teiid.query.processor.relational.SortNode;
import org.teiid.query.processor.relational.UnionAllNode;
import org.teiid.query.processor.relational.SortUtility.Mode;
import org.teiid.query.resolver.QueryResolver;
import org.teiid.query.resolver.TestResolver;
import org.teiid.query.rewriter.QueryRewriter;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.sql.lang.JoinType;
import org.teiid.query.sql.symbol.GroupSymbol;
import org.teiid.query.sql.visitor.GroupCollectorVisitor;
import org.teiid.query.sql.visitor.ValueIteratorProviderCollectorVisitor;
import org.teiid.query.unittest.FakeMetadataFacade;
import org.teiid.query.unittest.FakeMetadataFactory;
import org.teiid.query.unittest.FakeMetadataObject;
import org.teiid.query.unittest.FakeMetadataStore;
import org.teiid.query.util.CommandContext;
import org.teiid.query.validator.Validator;
import org.teiid.query.validator.ValidatorReport;
import org.teiid.translator.SourceSystemFunctions;

@SuppressWarnings({"nls", "unchecked"})
public class TestOptimizer {

    public interface DependentJoin {}
    public interface DependentSelectNode {}
    public interface SemiJoin {}
    public interface AntiSemiJoin {}
    public interface DependentProjectNode {}
    public interface DupRemoveNode {}
    public interface DupRemoveSortNode {}
   
    public static final int[] FULL_PUSHDOWN = new int[] {
                                            1,      // Access
                                            0,      // DependentAccess
                                            0,      // DependentSelect
                                            0,      // DependentProject
                                            0,      // DupRemove
                                            0,      // Grouping
                                            0,      // NestedLoopJoinStrategy
                                            0,      // MergeJoinStrategy
                                            0,      // Null
                                            0,      // PlanExecution
                                            0,      // Project
                                            0,      // Select
                                            0,      // Sort
                                            0       // UnionAll
                                        };
   
    public enum ComparisonMode { EXACT_COMMAND_STRING, CORRECTED_COMMAND_STRING, FAILED_PLANNING }
   
    public static final boolean SHOULD_SUCCEED = true;
    public static final boolean SHOULD_FAIL = false;

  // ################################## TEST HELPERS ################################

    public static BasicSourceCapabilities getTypicalCapabilities() {       
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_DISTINCT, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);   
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);   
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER_FULL, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.CRITERIA_BETWEEN, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_ORDERED, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_LIKE, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_LIKE_ESCAPE, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_ISNULL, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_OR, true);   
        caps.setCapabilitySupport(Capability.CRITERIA_NOT, true);   
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);   
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY_UNRELATED, true);
       
        // set typical max set size
        caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1000));
        return caps;   
    }
   
    public static CapabilitiesFinder getGenericFinder(boolean supportsJoins) {
      final BasicSourceCapabilities caps = getTypicalCapabilities();
      if (!supportsJoins) {
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, false);
      }
        return new DefaultCapabilitiesFinder(caps);
    }
   
    public static CapabilitiesFinder getGenericFinder() {
      return getGenericFinder(true);
    }

  public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, String[] expectedAtomic) {
    return helpPlan(sql, md, null, getGenericFinder(), expectedAtomic, SHOULD_SUCCEED);
  }
 
  public static ProcessorPlan helpPlan(String sql,
      QueryMetadataInterface md, String[] expected,
      CapabilitiesFinder capFinder,
      ComparisonMode mode) throws TeiidComponentException, TeiidProcessingException {
    return helpPlan(sql, md, null, capFinder, expected, mode);
  }   
 
  public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, String[] expectedAtomic, ComparisonMode mode) throws TeiidComponentException, TeiidProcessingException {
        return helpPlan(sql, md, null, getGenericFinder(), expectedAtomic, mode);
    }
 
    public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, List bindings, CapabilitiesFinder capFinder, String[] expectedAtomic, boolean shouldSucceed) {
        Command command;
        try {
            command = helpGetCommand(sql, md, bindings);
        } catch (TeiidException err) {
            throw new TeiidRuntimeException(err);
        }

        return helpPlanCommand(command, md, capFinder, null, expectedAtomic, shouldSucceed ? ComparisonMode.CORRECTED_COMMAND_STRING : ComparisonMode.FAILED_PLANNING);
    }
   
    public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, List bindings, CapabilitiesFinder capFinder, String[] expectedAtomic, ComparisonMode mode) throws TeiidComponentException, TeiidProcessingException {
        Command command = helpGetCommand(sql, md, bindings);

        return helpPlanCommand(command, md, capFinder, null, expectedAtomic, mode);
    }

   
    public static Command helpGetCommand(String sql, QueryMetadataInterface md, List bindings) throws TeiidComponentException, TeiidProcessingException {
    if(DEBUG) System.out.println("\n####################################\n" + sql);   //$NON-NLS-1$
    Command command = null;
    if (bindings != null && !bindings.isEmpty()) {
      command = TestResolver.helpResolveWithBindings(sql, md, bindings);
    } else {
       command = QueryParser.getQueryParser().parseCommand(sql);
      QueryResolver.resolveCommand(command, md);
    }
   
        ValidatorReport repo = Validator.validate(command, md);

        Collection failures = new ArrayList();
        repo.collectInvalidObjects(failures);
        if (failures.size() > 0){
            fail("Exception during validation (" + repo); //$NON-NLS-1$
        }
       
    // rewrite
    command = QueryRewriter.rewrite(command, md, new CommandContext());

        return command;
    }

    public static ProcessorPlan helpPlanCommand(Command command, QueryMetadataInterface md, CapabilitiesFinder capFinder, AnalysisRecord analysisRecord, String[] expectedAtomic, ComparisonMode mode) {
        if (capFinder == null){
            capFinder = getGenericFinder();
        }
       
        // Collect atomic queries
        ProcessorPlan plan = getPlan(command, md, capFinder, analysisRecord, mode != ComparisonMode.FAILED_PLANNING, new CommandContext());
              
        if (mode == ComparisonMode.CORRECTED_COMMAND_STRING) {
            checkAtomicQueries(expectedAtomic, plan, md, capFinder);
        } else if (mode == ComparisonMode.EXACT_COMMAND_STRING) {
            checkAtomicQueries(expectedAtomic, plan);
        }

        return plan;
    }
   
    public static void checkAtomicQueries(String[] expectedAtomic,
                                          ProcessorPlan plan) {
       Set<String> actualQueries = getAtomicQueries(plan);
      
       if (actualQueries.size() != 1 || expectedAtomic.length != 1) {
           // Compare atomic queries
           HashSet<String> expectedQueries = new HashSet<String>(Arrays.asList(expectedAtomic));
           assertEquals("Did not get expected atomic queries: ", expectedQueries, actualQueries); //$NON-NLS-1$
       } else {
           assertEquals("Did not get expected atomic query: ", expectedAtomic[0], actualQueries.iterator().next()); //$NON-NLS-1$
       }
   }

    public static void checkAtomicQueries(String[] expectedAtomic,
                                           ProcessorPlan plan, QueryMetadataInterface md, CapabilitiesFinder capFinder) {
        Set actualQueries = getAtomicQueries(plan);
       
        HashSet<String> expectedQueries = new HashSet<String>();
       
        // Compare atomic queries
        for (int i = 0; i < expectedAtomic.length; i++) {
            final String sql = expectedAtomic[i];
            Command command;
            try {
                command = helpGetCommand(sql, md, null);
                Collection groups = GroupCollectorVisitor.getGroupsIgnoreInlineViews(command, false);
                final GroupSymbol symbol = (GroupSymbol)groups.iterator().next();
                Object modelId = md.getModelID(symbol.getMetadataID());
                boolean supportsGroupAliases = CapabilitiesUtil.supportsGroupAliases(modelId, md, capFinder);
                boolean supportsProjection = CapabilitiesUtil.supports(Capability.QUERY_SELECT_EXPRESSION, modelId, md, capFinder);
                command.acceptVisitor(new AliasGenerator(supportsGroupAliases, !supportsProjection));
                expectedQueries.add(command.toString());
            } catch (Exception err) {
                throw new RuntimeException(err);
            }
        }
       
        assertEquals("Did not get expected atomic queries: ", expectedQueries, actualQueries); //$NON-NLS-1$
    }
   
    public static ProcessorPlan getPlan(Command command, QueryMetadataInterface md, CapabilitiesFinder capFinder, AnalysisRecord analysisRecord, boolean shouldSucceed, CommandContext cc) {
    ProcessorPlan plan = null;
    if (analysisRecord == null) {
          analysisRecord = new AnalysisRecord(false, DEBUG);
    }
    Exception exception = null;
    try {
      //do planning
      plan = QueryOptimizer.optimizePlan(command, md, null, capFinder, analysisRecord, cc);
    } catch (QueryPlannerException e) {
      exception = e;
    } catch (TeiidComponentException e) {
      exception = e;
    } catch (Throwable e) {
      throw new TeiidRuntimeException(e);
    } finally {
            if(DEBUG) {
                System.out.println(analysisRecord.getDebugLog());
            }
    }
    if (!shouldSucceed) {
      assertNotNull("Expected exception but did not get one.", exception); //$NON-NLS-1$
      return null;
    }
    if (plan == null) {
      throw new TeiidRuntimeException(exception);
    }
    assertNotNull("Output elements are null", plan.getOutputElements()); //$NON-NLS-1$
    if(DEBUG) System.out.println("\n" + plan);   //$NON-NLS-1$
    return plan;
  }
   
    public static Set<String> getAtomicQueries(ProcessorPlan plan) {
      Set<Command> atomicQueries = new HashSet<Command>();       
        if(plan instanceof RelationalPlan) {
            getAtomicCommands( ((RelationalPlan)plan).getRootNode(), atomicQueries );   
        }
       
        Set<String> stringQueries = new HashSet<String>();
       
        for (Command command : atomicQueries) {
           stringQueries.add(command.toString());
        }
       
        return stringQueries;
    }
   
    private static void getAtomicCommands(RelationalNode node, Set<Command> atomicQueries) {
        if(node instanceof AccessNode) {
            AccessNode accessNode = (AccessNode) node;
            atomicQueries.add( accessNode.getCommand());  
        }
       
        // Recurse through children
        RelationalNode[] children = node.getChildren();
        for(int i=0; i<children.length; i++) {
            if(children[i] != null) {
                getAtomicCommands(children[i], atomicQueries);
            } else {
                break;
            }
        }
    }
   
    // Counts are (mostly) alphabetical:
    //   Access, DependentAccess, DependentSelect, DependentProject, DupRemove, Grouping, NestedLoopJoinStrategy, Null, PlanExecution, Project, Select, Sort, UnionAll
    private static final Class[] COUNT_TYPES = new Class[] {
        AccessNode.class,
        DependentAccessNode.class,
        DependentSelectNode.class,
        DependentProjectNode.class,
        DupRemoveNode.class,
        GroupingNode.class,
        NestedLoopJoinStrategy.class,
        MergeJoinStrategy.class,
        NullNode.class,
        PlanExecutionNode.class,
        ProjectNode.class,
        SelectNode.class,
        SortNode.class,
        UnionAllNode.class
    };
           
    public static void checkNodeTypes(ProcessorPlan root, int[] expectedCounts) {
        checkNodeTypes(root, expectedCounts, COUNT_TYPES);
    }   
   
    public static void checkNodeTypes(ProcessorPlan root, int[] expectedCounts, Class[] types) {
        if(! (root instanceof RelationalPlan)) {
            return;
        }
               
        int[] actualCounts = new int[types.length];
        collectCounts(((RelationalPlan)root).getRootNode(), actualCounts, types);

        for(int i=0; i<expectedCounts.length; i++) {
            assertEquals("Did not find the correct number of nodes for type " + types[i], //$NON-NLS-1$
                        expectedCounts[i], actualCounts[i]);
        }
    }   
   
    /**
     * Method collectCounts.
     * @param relationalNode
     * @return int[]
     */
    static void collectCounts(RelationalNode relationalNode, int[] counts, Class<?>[] types) {
        Class<?> nodeType = relationalNode.getClass();
        if(nodeType.equals(JoinNode.class)) {
          JoinStrategy strategy = ((JoinNode)relationalNode).getJoinStrategy();
          if (((JoinNode)relationalNode).getJoinType().equals(JoinType.JOIN_SEMI)) {
            updateCounts(SemiJoin.class, counts, types);
          } else if (((JoinNode)relationalNode).getJoinType().equals(JoinType.JOIN_ANTI_SEMI)) {
            updateCounts(AntiSemiJoin.class, counts, types);
          }
            if (strategy instanceof NestedLoopJoinStrategy) {
                updateCounts(NestedLoopJoinStrategy.class, counts, types);
            } else if (strategy instanceof MergeJoinStrategy) {
                updateCounts(MergeJoinStrategy.class, counts, types);
                if (strategy instanceof EnhancedSortMergeJoinStrategy) {
                    updateCounts(EnhancedSortMergeJoinStrategy.class, counts, types);
                }
            } else if (strategy instanceof NestedTableJoinStrategy) {
              updateCounts(NestedTableJoinStrategy.class, counts, types);
            }
            if (((JoinNode)relationalNode).isDependent()) {
                updateCounts(DependentJoin.class, counts, types);
            }
        }else if (nodeType.equals(ProjectNode.class)){
          if (ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(((ProjectNode)relationalNode).getSelectSymbols()).isEmpty()) {
            updateCounts(ProjectNode.class, counts, types);
          } else {
            updateCounts(DependentProjectNode.class, counts, types);
          }
        }else if (nodeType.equals(SelectNode.class)){
          if (ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(((SelectNode)relationalNode).getCriteria()).isEmpty()) {
            updateCounts(SelectNode.class, counts, types);
          } else {
            updateCounts(DependentSelectNode.class, counts, types);
          }
        } else if (nodeType.equals(SortNode.class)) {
          Mode mode = ((SortNode)relationalNode).getMode();
          switch(mode) {
          case DUP_REMOVE:
                updateCounts(DupRemoveNode.class, counts, types);
            break;
          case DUP_REMOVE_SORT:
                updateCounts(DupRemoveSortNode.class, counts, types);
            break;
          case SORT:
                updateCounts(SortNode.class, counts, types);
            break;
          }
        } else {
            updateCounts(nodeType, counts, types);
        }
       
        RelationalNode[] children = relationalNode.getChildren();
        for(int i=0; i<children.length; i++) {
            if(children[i] != null) {
                collectCounts(children[i], counts, types);
            } else {
                break;
            }
        }
    }        
   
    private static void updateCounts(Class nodeClass, int[] counts, Class[] types) {
        for(int i=0; i<types.length; i++) {
            if(types[i].equals(nodeClass)) {
                counts[i] = counts[i] + 1;
                return;
            }   
        }
    }   
   
    public static void checkDependentJoinCount(ProcessorPlan plan, int expectedCount) {
        checkNodeTypes(plan, new int[] {expectedCount}, new Class[] {DependentJoin.class});
    }
               
  public static FakeMetadataFacade example1() {
    // Create models
    FakeMetadataObject pm1 = FakeMetadataFactory.createPhysicalModel("pm1"); //$NON-NLS-1$
        FakeMetadataObject pm2 = FakeMetadataFactory.createPhysicalModel("pm2"); //$NON-NLS-1$
    FakeMetadataObject vm1 = FakeMetadataFactory.createVirtualModel("vm1");   //$NON-NLS-1$

    // Create physical groups
    FakeMetadataObject pm1g1 = FakeMetadataFactory.createPhysicalGroup("pm1.g1", pm1); //$NON-NLS-1$
    FakeMetadataObject pm1g2 = FakeMetadataFactory.createPhysicalGroup("pm1.g2", pm1); //$NON-NLS-1$
    FakeMetadataObject pm1g3 = FakeMetadataFactory.createPhysicalGroup("pm1.g3", pm1); //$NON-NLS-1$
        FakeMetadataObject pm1g4 = FakeMetadataFactory.createPhysicalGroup("pm1.g4", pm1); //$NON-NLS-1$
        FakeMetadataObject pm1g5 = FakeMetadataFactory.createPhysicalGroup("pm1.g5", pm1); //$NON-NLS-1$
        FakeMetadataObject pm1g6 = FakeMetadataFactory.createPhysicalGroup("pm1.g6", pm1); //$NON-NLS-1$
        FakeMetadataObject pm1g7 = FakeMetadataFactory.createPhysicalGroup("pm1.g7", pm1); //$NON-NLS-1$
        FakeMetadataObject pm1g8 = FakeMetadataFactory.createPhysicalGroup("pm1.g8", pm1); //$NON-NLS-1$
        FakeMetadataObject pm2g1 = FakeMetadataFactory.createPhysicalGroup("pm2.g1", pm2); //$NON-NLS-1$
        FakeMetadataObject pm2g2 = FakeMetadataFactory.createPhysicalGroup("pm2.g2", pm2); //$NON-NLS-1$
        FakeMetadataObject pm2g3 = FakeMetadataFactory.createPhysicalGroup("pm2.g3", pm2); //$NON-NLS-1$
       
    // Create physical elements
    List pm1g1e = FakeMetadataFactory.createElements(pm1g1,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List pm1g2e = FakeMetadataFactory.createElements(pm1g2,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List pm1g3e = FakeMetadataFactory.createElements(pm1g3,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
        List pm1g4e = FakeMetadataFactory.createElements(pm1g4,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List pm1g5e = FakeMetadataFactory.createElements(pm1g5,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List pm1g6e = FakeMetadataFactory.createElements(pm1g6,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List pm1g7e = FakeMetadataFactory.createElements(pm1g7,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List pm1g8e = FakeMetadataFactory.createElements(pm1g8,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List pm2g1e = FakeMetadataFactory.createElements(pm2g1,
            new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
        List pm2g2e = FakeMetadataFactory.createElements(pm2g2,
            new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
        List pm2g3e = FakeMetadataFactory.createElements(pm2g3,
            new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });

    // Create virtual groups
    QueryNode vm1g1n1 = new QueryNode("SELECT * FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1g1 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g1", vm1, vm1g1n1); //$NON-NLS-1$

    QueryNode vm1g2n1 = new QueryNode("SELECT * FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1g2 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g2", vm1, vm1g2n1); //$NON-NLS-1$

    //defect 8096
    QueryNode vm1sub1n1 = new QueryNode("SELECT * FROM vm1.g1 WHERE e1 IN (SELECT e1 FROM vm1.g3)"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1sub1 = FakeMetadataFactory.createVirtualGroup("vm1.sub1", vm1, vm1sub1n1); //$NON-NLS-1$

    QueryNode vm1g3n1 = new QueryNode("SELECT * FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1g3 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g3", vm1, vm1g3n1); //$NON-NLS-1$

        QueryNode vm1g4n1 = new QueryNode("SELECT pm1.g1.e1, pm1.g2.e1 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1=pm1.g2.e1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1g4 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g4", vm1, vm1g4n1); //$NON-NLS-1$

        QueryNode vm1g5n1 = new QueryNode("SELECT DISTINCT pm1.g1.e1 FROM pm1.g1 ORDER BY pm1.g1.e1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1g5 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g5", vm1, vm1g5n1); //$NON-NLS-1$

        QueryNode vm1g6n1 = new QueryNode("SELECT e1, convert(e2, string), 3 as e3, ((e2+e4)/3) as e4 FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1g6 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.g6", vm1, vm1g6n1); //$NON-NLS-1$

    QueryNode vm1u1n1 = new QueryNode("SELECT * FROM pm1.g1 UNION SELECT * FROM pm1.g2 UNION ALL SELECT * FROM pm1.g3"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1u1 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u1", vm1, vm1u1n1); //$NON-NLS-1$

    QueryNode vm1u2n1 = new QueryNode("SELECT * FROM pm1.g1 UNION SELECT * FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1u2 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u2", vm1, vm1u2n1); //$NON-NLS-1$

    QueryNode vm1u3n1 = new QueryNode("SELECT e1 FROM pm1.g1 UNION SELECT convert(e2, string) as x FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
    FakeMetadataObject vm1u3 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u3", vm1, vm1u3n1); //$NON-NLS-1$

        QueryNode vm1u4n1 = new QueryNode("SELECT concat(e1, 'x') as v1 FROM pm1.g1 UNION ALL SELECT e1 FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u4 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u4", vm1, vm1u4n1); //$NON-NLS-1$

        QueryNode vm1u5n1 = new QueryNode("SELECT concat(e1, 'x') as v1 FROM pm1.g1 UNION ALL SELECT concat('a', e1) FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u5 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u5", vm1, vm1u5n1); //$NON-NLS-1$

        QueryNode vm1u6n1 = new QueryNode("SELECT x1.e1 AS elem, 'xyz' AS const FROM pm1.g1 AS x1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u6 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u6", vm1, vm1u6n1); //$NON-NLS-1$

        QueryNode vm1u7n1 = new QueryNode("SELECT 's1' AS const, e1 FROM pm1.g1 UNION ALL SELECT 's2', e1 FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u7 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u7", vm1, vm1u7n1); //$NON-NLS-1$

        QueryNode vm1u8n1 = new QueryNode("SELECT const, e1 FROM vm1.u7 UNION ALL SELECT 's3', e1 FROM pm1.g3"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u8 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u8", vm1, vm1u8n1); //$NON-NLS-1$

        QueryNode vm1u9n1 = new QueryNode("SELECT e1 as a, e1 as b FROM pm1.g1 UNION ALL SELECT e1, e1 FROM pm1.g2"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1u9 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.u9", vm1, vm1u9n1); //$NON-NLS-1$

        QueryNode vm1a1n1 = new QueryNode("SELECT e1, SUM(e2) AS sum_e2 FROM pm1.g1 GROUP BY e1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a1 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a1", vm1, vm1a1n1); //$NON-NLS-1$
       
        QueryNode vm1a2n1 = new QueryNode("SELECT e1, SUM(e2) AS sum_e2 FROM pm1.g1 GROUP BY e1 HAVING SUM(e2) > 5"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a2 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a2", vm1, vm1a2n1); //$NON-NLS-1$

        QueryNode vm1a3n1 = new QueryNode("SELECT SUM(e2) AS sum_e2 FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a3 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a3", vm1, vm1a3n1); //$NON-NLS-1$
       
        QueryNode vm1a4n1 = new QueryNode("SELECT COUNT(*) FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a4 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a4", vm1, vm1a4n1); //$NON-NLS-1$

        QueryNode vm1a5n1 = new QueryNode("SELECT vm1.a4.count FROM vm1.a4 UNION ALL SELECT COUNT(*) FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a5 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a5", vm1, vm1a5n1); //$NON-NLS-1$

        QueryNode vm1a6n1 = new QueryNode("SELECT COUNT(*) FROM vm1.u2"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1a6 = FakeMetadataFactory.createUpdatableVirtualGroup("vm1.a6", vm1, vm1a6n1); //$NON-NLS-1$
       
        QueryNode vm1g7n1 = new QueryNode("select DECODESTRING(e1, 'S,Pay,P,Rec') as e1, e2 FROM pm1.g1"); //$NON-NLS-1$ //$NON-NLS-2$
        FakeMetadataObject vm1g7 = FakeMetadataFactory.createVirtualGroup("vm1.g7", vm1, vm1g7n1); //$NON-NLS-1$
       
    // Create virtual elements
    List vm1g1e = FakeMetadataFactory.createElements(vm1g1,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List vm1g2e = FakeMetadataFactory.createElements(vm1g2,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List vm1g3e = FakeMetadataFactory.createElements(vm1g3,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    //for defect 8096
    List vm1sub1e = FakeMetadataFactory.createElements(vm1sub1,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
        List vm1g4e = FakeMetadataFactory.createElements(vm1g4,
            new String[] { "e1", "e2" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
        List vm1g5e = FakeMetadataFactory.createElements(vm1g5,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING});
        List vm1g6e = FakeMetadataFactory.createElements(vm1g6,
            new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.DOUBLE });
        List vm1g7e = FakeMetadataFactory.createElements(vm1g7,
            new String[] { "e1", "e2"}, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER});
        List vm1u1e = FakeMetadataFactory.createElements(vm1u1,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List vm1u2e = FakeMetadataFactory.createElements(vm1u2,
      new String[] { "e1", "e2", "e3", "e4" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
      new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BOOLEAN, DataTypeManager.DefaultDataTypes.DOUBLE });
    List vm1u3e = FakeMetadataFactory.createElements(vm1u3,
      new String[] { "e1" }, //$NON-NLS-1$
      new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List vm1u4e = FakeMetadataFactory.createElements(vm1u4,
            new String[] { "v1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List vm1u5e = FakeMetadataFactory.createElements(vm1u5,
            new String[] { "v1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        List vm1u6e = FakeMetadataFactory.createElements(vm1u6,
            new String[] { "elem", "const" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
        List vm1u7e = FakeMetadataFactory.createElements(vm1u7,
            new String[] { "const", "e1" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
        List vm1u8e = FakeMetadataFactory.createElements(vm1u8,
            new String[] { "const", "e1" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
        List vm1u9e = FakeMetadataFactory.createElements(vm1u9,
            new String[] { "a", "b" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
        List vm1a1e = FakeMetadataFactory.createElements(vm1a1,
            new String[] { "e1", "sum_e2" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.LONG });
        List vm1a2e = FakeMetadataFactory.createElements(vm1a2,
            new String[] { "e1", "sum_e2" }, //$NON-NLS-1$ //$NON-NLS-2$
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.LONG });
        List vm1a3e = FakeMetadataFactory.createElements(vm1a3,
            new String[] { "sum_e2" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.LONG });
        List vm1a4e = FakeMetadataFactory.createElements(vm1a4,
            new String[] { "count" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.INTEGER });
        List vm1a5e = FakeMetadataFactory.createElements(vm1a5,
            new String[] { "count" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.INTEGER });
        List vm1a6e = FakeMetadataFactory.createElements(vm1a6,
            new String[] { "count" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.INTEGER });
     
    // Add all objects to the store
    FakeMetadataStore store = new FakeMetadataStore();
    store.addObject(pm1);
    store.addObject(pm1g1);   
    store.addObjects(pm1g1e);
    store.addObject(pm1g2);   
    store.addObjects(pm1g2e);
     store.addObject(pm1g3);   
    store.addObjects(pm1g3e);
        store.addObject(pm1g4);    
        store.addObjects(pm1g4e);
        store.addObject(pm1g5);    
        store.addObjects(pm1g5e);
        store.addObject(pm1g6);    
        store.addObjects(pm1g6e);
        store.addObject(vm1g7);
        store.addObjects(vm1g7e);
        store.addObject(pm1g7);    
        store.addObjects(pm1g7e);
        store.addObject(pm1g8);    
        store.addObjects(pm1g8e);
       
        store.addObject(pm2);
        store.addObject(pm2g1);    
        store.addObjects(pm2g1e);
        store.addObject(pm2g2);    
        store.addObjects(pm2g2e);
        store.addObject(pm2g3);    
        store.addObjects(pm2g3e);
        
    store.addObject(vm1);
    store.addObject(vm1g1);
    store.addObjects(vm1g1e);
    store.addObject(vm1g2);
    store.addObjects(vm1g2e);
    store.addObject(vm1g3);
    store.addObjects(vm1g3e);

    //for defect 8096
    store.addObject(vm1sub1);
    store.addObjects(vm1sub1e);
   
        store.addObject(vm1g4);
        store.addObjects(vm1g4e);
        store.addObject(vm1g5);
        store.addObjects(vm1g5e);
        store.addObject(vm1g6);
        store.addObjects(vm1g6e);
    store.addObject(vm1u1);
    store.addObjects(vm1u1e);
    store.addObject(vm1u2);
    store.addObjects(vm1u2e);
    store.addObject(vm1u3);
    store.addObjects(vm1u3e);
        store.addObject(vm1u4);
        store.addObjects(vm1u4e);
        store.addObject(vm1u5);
        store.addObjects(vm1u5e);
        store.addObject(vm1u6);
        store.addObjects(vm1u6e);
        store.addObject(vm1u7);
        store.addObjects(vm1u7e);
        store.addObject(vm1u8);
        store.addObjects(vm1u8e);
        store.addObject(vm1u9);
        store.addObjects(vm1u9e);
        store.addObject(vm1a1);
        store.addObjects(vm1a1e);  
        store.addObject(vm1a2);
        store.addObjects(vm1a2e);  
        store.addObject(vm1a3);
        store.addObjects(vm1a3e);  
        store.addObject(vm1a4);
        store.addObjects(vm1a4e);  
        store.addObject(vm1a5);
        store.addObjects(vm1a5e);  
        store.addObject(vm1a6);
        store.addObjects(vm1a6e);  
           
    // Create the facade from the store
    return new FakeMetadataFacade(store);
 
       
  // ################################## ACTUAL TESTS ################################

  /**
   * Test defect 8096 - query a virtual group with subquery of another virtual group
   */
  @Test public void testVirtualSubqueryINClause_8096() {
    helpPlan("SELECT * FROM vm1.sub1", example1(), //$NON-NLS-1$
      new String[] {"SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1"} ); //$NON-NLS-1$
  }

  @Test public void testQueryPhysical() {
    ProcessorPlan plan = helpPlan("SELECT pm1.g1.e1, e2, pm1.g1.e3 as a, e4 as b FROM pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] {"SELECT pm1.g1.e1, e2, pm1.g1.e3, e4 FROM pm1.g1"} ); //$NON-NLS-1$
    assertTrue(!plan.requiresTransaction(true));
        checkNodeTypes(plan, FULL_PUSHDOWN);   
  }
   
  @Test public void testSelectStarPhysical() {
    ProcessorPlan plan = helpPlan("SELECT * FROM pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1"} ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
  }

  @Test public void testQuerySingleSourceVirtual() {
    ProcessorPlan plan = helpPlan("SELECT * FROM vm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1"} ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
  }
 
  @Test public void testQueryMultiSourceVirtual() {
    ProcessorPlan plan = helpPlan("SELECT * FROM vm1.g2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "SELECT g_0.e1, g_0.e2, g_1.e3, g_1.e4 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1"} ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
  }

  @Test public void testPhysicalVirtualJoinWithCriteria() throws Exception {
    ProcessorPlan plan = helpPlan("SELECT vm1.g2.e1 from vm1.g2, pm1.g3 where vm1.g2.e1=pm1.g3.e1 and vm1.g2.e2 > 0", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g3 AS g_2 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = g_2.e1) AND (g_0.e2 > 0)" }, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
  }
   
    @Test public void testQueryWithExpression() {
        helpPlan("SELECT e4 FROM pm3.g1 WHERE e4 < convert('2001-11-01 10:30:40.42', timestamp)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "SELECT e4 FROM pm3.g1 WHERE e4 < {ts'2001-11-01 10:30:40.42'}"} ); //$NON-NLS-1$
    }
   
    @Test public void testInsert() {
        helpPlan("Insert into pm1.g1 (pm1.g1.e1, pm1.g1.e2) values ('MyString', 1)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "INSERT INTO pm1.g1 (pm1.g1.e1, pm1.g1.e2) VALUES ('MyString', 1)"} ); //$NON-NLS-1$
    }
   
    @Test public void testUpdate1() {
        helpPlan("Update pm1.g1 Set pm1.g1.e1= LTRIM('MyString'), pm1.g1.e2= 1 where pm1.g1.e3= 'true'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "UPDATE pm1.g1 SET pm1.g1.e1 = 'MyString', pm1.g1.e2 = 1 WHERE pm1.g1.e3 = TRUE"} ); //$NON-NLS-1$
    }
   
    @Test public void testUpdate2() throws Exception {
      BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
      bsc.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
      DefaultCapabilitiesFinder dcf = new DefaultCapabilitiesFinder(bsc);
        helpPlan("Update pm1.g1 Set pm1.g1.e1= LTRIM('MyString'), pm1.g1.e2= 1 where pm1.g1.e2= convert(pm1.g1.e4, integer)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "UPDATE pm1.g1 SET e1 = 'MyString', e2 = 1 WHERE pm1.g1.e2 = convert(pm1.g1.e4, integer)"}, dcf, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
    }
   
    @Test public void testDelete() throws Exception {
      BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
      bsc.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
      DefaultCapabilitiesFinder dcf = new DefaultCapabilitiesFinder(bsc);
      helpPlan("Delete from pm1.g1 where pm1.g1.e1 = cast(pm1.g1.e2 AS string)", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
      new String[] { "DELETE FROM pm1.g1 WHERE pm1.g1.e1 = convert(pm1.g1.e2, string)"}, dcf, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
    }

  // ############################# TESTS ON EXAMPLE 1 ############################
 
    @Test public void testCopyInAcrossJoin() throws Exception {
        ProcessorPlan plan = helpPlan("select pm1.g1.e1, pm2.g2.e1 from pm1.g1, pm2.g2 where pm1.g1.e1=pm2.g2.e1 and pm1.g1.e1 IN ('a', 'b')", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 WHERE g_0.e1 IN ('a', 'b') ORDER BY c_0", //$NON-NLS-1$
                   "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 WHERE g_0.e1 IN ('a', 'b') ORDER BY c_0" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
       
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testCopyMatchAcrossJoin() throws Exception {
        helpPlan("select pm1.g1.e1, pm2.g2.e1 from pm1.g1, pm2.g2 where pm1.g1.e1=pm2.g2.e1 and pm1.g1.e1 LIKE '%1'", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 WHERE g_0.e1 LIKE '%1' ORDER BY c_0", //$NON-NLS-1$
                  "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 WHERE g_0.e1 LIKE '%1' ORDER BY c_0" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
    }
    @Test public void testCopyOrAcrossJoin() throws Exception {
        helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and (pm1.g1.e1 = 'abc' OR pm1.g1.e1 = 'def')", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e1 = 'def')", //$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e1 = 'def')" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$
    }
    @Test public void testCopyMultiElementCritAcrossJoin() throws Exception {
        helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and pm1.g1.e2=pm1.g2.e2 and (pm1.g1.e1 = 'abc' OR pm1.g1.e2 = 5)", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g2.e1, pm1.g2.e2 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e2 = 5)", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$
    }
   
    @Test public void testCantCopyAcrossJoin1() throws Exception {
        helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and concat(pm1.g1.e1, pm1.g1.e2) = 'abc'", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$
    }

    @Test public void testCantCopyAcrossJoin2() throws Exception {
        helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and (pm1.g1.e1 = 'abc' OR pm1.g1.e2 = 5)", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)", //$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughFrame1() {
      helpPlan("select * from vm1.g1, vm1.g2 where vm1.g1.e1='abc' and vm1.g1.e1=vm1.g2.e1", example1(), //$NON-NLS-1$
      new String[] { "SELECT g1__1.e1, g1__1.e2, g1__1.e3, g1__1.e4 FROM pm1.g1 AS g1__1 WHERE g1__1.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughFrame2() throws Exception {
      helpPlan("select * from vm1.g1, vm1.g3 where vm1.g1.e1='abc' and vm1.g1.e1=vm1.g3.e1", example1(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'"//$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughFrame3() {
      helpPlan("select * from vm1.g1, vm1.g2, vm1.g1 as a where vm1.g1.e1='abc' and vm1.g1.e1=vm1.g2.e1 and vm1.g1.e1=a.e1", example1(), //$NON-NLS-1$
      new String[] { "SELECT g1__1.e1, g1__1.e2, g1__1.e3, g1__1.e4 FROM pm1.g1 AS g1__1 WHERE g1__1.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'", //$NON-NLS-1$
              "SELECT g1__2.e1, g1__2.e2, g1__2.e3, g1__2.e4 FROM pm1.g1 AS g1__2 WHERE g1__2.e1 = 'abc'" } ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughUnion1() {
      helpPlan("select e1 from vm1.u1 where e1='abc'", example1(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE pm1.g3.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughUnion2() {
      helpPlan("select e1 from vm1.u2 where e1='abc'", example1(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughUnion3() {
      helpPlan("select e1 from vm1.u1 where e1='abc' and e2=5", example1(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE (pm1.g3.e1 = 'abc') AND (pm1.g3.e2 = 5)", //$NON-NLS-1$
              "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') AND (pm1.g2.e2 = 5)", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') AND (pm1.g1.e2 = 5)" } ); //$NON-NLS-1$
    }

    @Test public void testPushingCriteriaThroughUnion4() {
      helpPlan("select e1 from vm1.u1 where e1='abc' or e2=5", example1(), //$NON-NLS-1$
      new String[] { "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE (pm1.g3.e1 = 'abc') OR (pm1.g3.e2 = 5)", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)", //$NON-NLS-1$
              "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e2 = 5)" } ); //$NON-NLS-1$
    }

  // expression in a subquery of the union
    @Test public void testPushingCriteriaThroughUnion5() {
      helpPlan("select e1 from vm1.u3 where e1='abc'", example1(), //$NON-NLS-1$
      new String[] { "SELECT DISTINCT e1 FROM pm1.g1 WHERE e1 = 'abc'" } ); //$NON-NLS-1$
    }

    /** defect #4956 */
    @Test public void testPushCriteriaThroughUnion6() {
        helpPlan("select v1 from vm1.u4 where vm1.u4.v1='x'", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT e1 FROM pm1.g2 WHERE e1 = 'x'" } ); //$NON-NLS-1$
    }

    @Test public void testPushCriteriaThroughUnion7() {
        helpPlan("select v1 from vm1.u5 where vm1.u5.v1='x'", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT e1 FROM pm1.g2" } ); //$NON-NLS-1$
    }

    @Test public void testPushCriteriaThroughUnion8() {
        helpPlan("select v1 from vm1.u5 where length(v1) > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT e1 FROM pm1.g2" } ); //$NON-NLS-1$
    }
   
    @Test public void testPushCriteriaThroughUnion11() {
        helpPlan("select * from vm1.u8 where const = 's3' or e1 is null", example1(), //$NON-NLS-1$
            new String[] { "SELECT 's3', e1 FROM pm1.g3", //$NON-NLS-1$
                            "SELECT 's2', e1 FROM pm1.g2 WHERE e1 IS NULL", //$NON-NLS-1$
                            "SELECT 's1', e1 FROM pm1.g1 WHERE e1 IS NULL" } );     //$NON-NLS-1$
    }

    @Test public void testPushCriteriaThroughUnion12() {
        helpPlan("select * from vm1.u8 where const = 's1' or e1 is null", example1(), //$NON-NLS-1$
            new String[] { "SELECT 's3', e1 FROM pm1.g3 WHERE e1 IS NULL", //$NON-NLS-1$
                            "SELECT 's2', e1 FROM pm1.g2 WHERE e1 IS NULL", //$NON-NLS-1$
                            "SELECT 's1', e1 FROM pm1.g1" } );     //$NON-NLS-1$
    }

    /** defect #4997 */
    @Test public void testCountStarNoRows() {
        ProcessorPlan plan = helpPlan("select count(*) from vm1.u4", example1(), //$NON-NLS-1$
            new String[] { "SELECT 1 FROM pm1.g2"//$NON-NLS-1$
                            "SELECT 1 FROM pm1.g1" } ); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        });
    }

    @Test public void testPushingCriteriaWithCopy() {
      ProcessorPlan plan = helpPlan("select vm1.u1.e1 from vm1.u1, pm1.g1 where vm1.u1.e1='abc' and vm1.u1.e1=pm1.g1.e1", example1(), //$NON-NLS-1$
      new String[] { "SELECT 1 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'", //$NON-NLS-1$
                            "SELECT pm1.g3.e1, pm1.g3.e2, pm1.g3.e3, pm1.g3.e4 FROM pm1.g3 WHERE pm1.g3.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2 WHERE pm1.g2.e1 = 'abc'", //$NON-NLS-1$
              "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 WHERE pm1.g1.e1 = 'abc'" } ); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            4,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            1,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            2       // UnionAll
        });
    }

    @Test public void testVirtualGroupWithAliasedElement() {
        helpPlan("select elem FROM vm1.u6 where elem='abc' and const='xyz'", example1(), //$NON-NLS-1$
            new String[] { "SELECT x1.e1 FROM pm1.g1 AS x1 WHERE x1.e1 = 'abc'" } );     //$NON-NLS-1$
    }

    @Test public void testPushThroughGroup1() {
        helpPlan("select * FROM vm1.a1 WHERE e1 = 'x'", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1, e2 FROM pm1.g1 WHERE e1 = 'x'" } );     //$NON-NLS-1$
    }
    @Test public void testPushThroughGroup2() {
        helpPlan("select * FROM vm1.a2 WHERE e1 = 'x'", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1, e2 FROM pm1.g1 WHERE e1 = 'x'" } );     //$NON-NLS-1$
    }

    @Test public void testPushThroughGroup3() {
        helpPlan("select * FROM vm1.a3 WHERE sum_e2 > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT e2 FROM pm1.g1" } );     //$NON-NLS-1$
    }

    @Test public void testPushMultiGroupCriteria() {
        ProcessorPlan plan = helpPlan("select pm2.g1.e1 from pm2.g1, pm2.g2 where pm2.g1.e1 = pm2.g2.e1 and (pm2.g1.e2 = 1 OR pm2.g2.e2 = 2)", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1 FROM pm2.g1, pm2.g2 WHERE (pm2.g1.e1 = pm2.g2.e1) AND ((pm2.g1.e2 = 1) OR (pm2.g2.e2 = 2))" } ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }  

    @Test public void testSimpleCrossJoin1() throws Exception {
        helpPlan("select pm1.g1.e1 FROM pm1.g1, pm1.g2", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$
                "SELECT pm1.g2.e1 FROM pm1.g2" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING );     //$NON-NLS-1$
    }

    @Test public void testSimpleCrossJoin2() {
        helpPlan("select pm2.g1.e1 FROM pm2.g1, pm2.g2", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1 FROM pm2.g1, pm2.g2"} ); //$NON-NLS-1$
              
    }

    @Test public void testSimpleCrossJoin3() {
        helpPlan("select pm2.g1.e1 FROM pm2.g1 CROSS JOIN pm2.g2", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1 FROM pm2.g1, pm2.g2"} ); //$NON-NLS-1$
              
    }

    @Test public void testMultiSourceCrossJoin() throws Exception {
        helpPlan("select pm1.g1.e1 FROM pm1.g1, pm1.g2, pm1.g3", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$
                "SELECT pm1.g2.e1 FROM pm1.g2", //$NON-NLS-1$
                "SELECT pm1.g3.e1 FROM pm1.g3" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING );     //$NON-NLS-1$
    }

    @Test public void testSingleSourceCrossJoin() {
        helpPlan("select pm2.g1.e1 FROM pm2.g1, pm2.g2, pm2.g3", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1 FROM pm2.g1, pm2.g2, pm2.g3"} ); //$NON-NLS-1$
    }

    @Test public void testSelfJoins() {
        helpPlan("select pm2.g1.e1 FROM pm2.g1 JOIN pm2.g1 AS x ON pm2.g1.e1=x.e1", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1 FROM pm2.g1 order by e1", //$NON-NLS-1$
                "SELECT x.e1 FROM pm2.g1 AS x order by e1" } );     //$NON-NLS-1$
    }

    @Test public void testDefect5282_1() {
        helpPlan("select * FROM vm1.a4 WHERE vm1.a4.count > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT 1 FROM pm1.g1" } );     //$NON-NLS-1$
    }

    @Test public void testDefect5282_2() {
        helpPlan("select count(*) FROM vm1.a4", example1(), //$NON-NLS-1$
            new String[] { "SELECT 1 FROM pm1.g1" } );     //$NON-NLS-1$
    }

    @Test public void testDefect5282_3() {
        helpPlan("select * FROM vm1.a5 WHERE vm1.a5.count > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT 1 FROM pm1.g1" } );     //$NON-NLS-1$
    }
   
    @Test public void testDepJoinHintBaseline() throws Exception {
        ProcessorPlan plan = helpPlan("select * FROM vm1.g4", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    @Test public void testDefect6425_1() {
        helpPlan("select * from vm1.u9", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1, e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT e1, e1 FROM pm1.g2" } );     //$NON-NLS-1$
    }

    @Test public void testDefect6425_2() {
        helpPlan("select count(*) from vm1.u9", example1(), //$NON-NLS-1$
            new String[] { "SELECT 1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT 1 FROM pm1.g2" } );     //$NON-NLS-1$
    }
   
    @Test public void testPushMatchCritWithReference() throws Exception {
        List bindings = new ArrayList();
        bindings.add("pm1.g2.e1"); //$NON-NLS-1$
        helpPlan("select e1 FROM pm1.g1 WHERE e1 LIKE ?", example1(), bindings, null,  //$NON-NLS-1$
            new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 LIKE PM1.G2.e1" }, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
    }
   
    @Test public void testDefect6517() {
        helpPlan("select count(*) from vm1.g5", example1(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm1.g1.e1 FROM pm1.g1" });     //$NON-NLS-1$
    }
   
    @Test public void testDefect5283() {       
        helpPlan("select * from vm1.a6", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM pm1.g2" } ); //$NON-NLS-1$
    }
   
    @Test public void testManyJoinsOverThreshold() throws Exception {
        long begin = System.currentTimeMillis();
        helpPlan("SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2, pm1.g3, pm1.g4, pm1.g5, pm1.g6, pm1.g7, pm1.g8, pm1.g1 AS x, pm1.g2 AS y WHERE pm1.g1.e1 = pm1.g2.e1 AND pm1.g2.e1 = pm1.g3.e1 AND pm1.g3.e1 = pm1.g4.e1 AND pm1.g4.e1 = pm1.g5.e1 AND pm1.g5.e1=pm1.g6.e1 AND pm1.g6.e1=pm1.g7.e1 AND pm1.g7.e1=pm1.g8.e1", //$NON-NLS-1$
            example1(),
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", //$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2"//$NON-NLS-1$
                            "SELECT pm1.g3.e1 FROM pm1.g3"//$NON-NLS-1$
                            "SELECT pm1.g4.e1 FROM pm1.g4", //$NON-NLS-1$
                            "SELECT pm1.g5.e1 FROM pm1.g5"//$NON-NLS-1$
                            "SELECT pm1.g6.e1 FROM pm1.g6"//$NON-NLS-1$
                            "SELECT pm1.g7.e1 FROM pm1.g7", //$NON-NLS-1$
                            "SELECT pm1.g8.e1 FROM pm1.g8", //$NON-NLS-1$
                            "SELECT x.e1 FROM pm1.g1 AS x", //$NON-NLS-1$
                            "SELECT y.e1 FROM pm1.g2 AS y" }, new DefaultCapabilitiesFinder(), ComparisonMode.CORRECTED_COMMAND_STRING ); //$NON-NLS-1$
                           
        long elapsed = System.currentTimeMillis() - begin;  
        assertTrue("Did not plan many join query in reasonable time frame: " + elapsed + " ms", elapsed < 4000); //$NON-NLS-1$ //$NON-NLS-2$
    }
   
    @Test public void testAggregateWithoutGroupBy() {
        ProcessorPlan plan = helpPlan("select count(e2) from pm1.g1", example1(), //$NON-NLS-1$
            new String[] { "SELECT e2 FROM pm1.g1" } );         //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    @Test public void testHavingWithoutGroupBy() {
        ProcessorPlan plan = helpPlan("select count(e2) from pm1.g1 HAVING count(e2) > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT e2 FROM pm1.g1" } );         //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testHavingAndGroupBy() {
        ProcessorPlan plan = helpPlan("select e1, count(e2) from pm1.g1 group by e1 having count(e2) > 0 and sum(e2) > 0", example1(), //$NON-NLS-1$
            new String[] { "SELECT e1, e2 FROM pm1.g1" } );         //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    @Test public void testAllJoinsInSingleClause() throws Exception {
        ProcessorPlan plan = helpPlan("select pm1.g1.e1 FROM pm1.g1 join (pm1.g2 right outer join pm1.g3 on pm1.g2.e1=pm1.g3.e1) on pm1.g1.e1=pm1.g3.e1", example1()//$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1"//$NON-NLS-1$
                            "SELECT pm1.g2.e1 FROM pm1.g2", //$NON-NLS-1$
                            "SELECT pm1.g3.e1 FROM pm1.g3" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            3,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testSelectCountStarFalseCriteria() {
        ProcessorPlan plan = helpPlan("Select count(*) from pm1.g1 where 1=0", example1()//$NON-NLS-1$
            new String[] { });
        checkNodeTypes(plan, new int[] {
            0,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            1,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testSubquery1() {
        ProcessorPlan plan = helpPlan("Select e1 from (select e1 FROM pm1.g1) AS x", example1()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testSubquery2() {
        ProcessorPlan plan = helpPlan("Select e1, a from (select e1 FROM pm1.g1) AS x, (select e1 as a FROM pm1.g2) AS y WHERE x.e1=y.a", example1()//$NON-NLS-1$
            new String[] { "SELECT g_0.e1, g_1.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

  @Test public void testSubquery3() {
    ProcessorPlan plan = helpPlan("Select e1 from (select e1 FROM pm1.g1) AS x WHERE x.e1 = 'a'", example1(), //$NON-NLS-1$
      new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 = 'a'" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

  @Test public void testSubquery4() {
    ProcessorPlan plan = helpPlan("Select e1 from (select e1 FROM pm1.g1 WHERE e1 = 'a') AS x", example1(), //$NON-NLS-1$
      new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 = 'a'" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testSubqueryInClause1() {
        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm2.g1)", example1()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            1,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testCompareSubquery1() {
        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 < ALL (select e1 FROM pm2.g1)", example1()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            1,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testCompareSubquery3() {
        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 >= all (select e1 FROM pm2.g1)", example1()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            1,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testExistsSubquery1() {
        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where exists (select e1 FROM pm2.g1)", example1()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            1,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    @Test public void testTempGroup() {
        ProcessorPlan plan = helpPlan("select e1 from tm1.g1 where e1 = 'x'", FakeMetadataFactory.example1Cached()//$NON-NLS-1$
            new String[] { "SELECT e1 FROM tm1.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    @Test public void testNotPushDistinct() throws Exception {
        ProcessorPlan plan = helpPlan("select distinct e1 from pm1.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1" }, new DefaultCapabilitiesFinder(), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testPushDistinct() {
        ProcessorPlan plan = helpPlan("select distinct e1 from pm3.g1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT e1 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctSort() {
        ProcessorPlan plan = helpPlan("select distinct e1 from pm3.g1 order by e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT e1 FROM pm3.g1 ORDER BY e1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctWithCriteria() {
        ProcessorPlan plan = helpPlan("select distinct e1 from pm3.g1 where e1 = 'x'", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT e1 FROM pm3.g1 WHERE e1 = 'x'" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual1() {
        ProcessorPlan plan = helpPlan("select * from vm1.g12", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual2() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g12", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual3() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g12 ORDER BY e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual4() {
        ProcessorPlan plan = helpPlan("select * from vm1.g13", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual5() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g13", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual6() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g13 ORDER BY e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual7() {
        ProcessorPlan plan = helpPlan("select * from vm1.g14", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual8() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g14", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushDistinctVirtual9() {
        ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g14 ORDER BY e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }
    /**
     * Defect #7819
     */
    @Test public void testPushDistinctWithExpressions() {
        ProcessorPlan plan = helpPlan("SELECT DISTINCT * FROM vm1.g15", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT e1, e2 FROM pm3.g1" }); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    @Test public void testNestedSubquery() {
        ProcessorPlan plan = helpPlan("SELECT IntKey, LongNum FROM (SELECT IntKey, LongNum FROM (SELECT IntKey, LongNum, DoubleNum FROM BQT2.SmallA ) AS x ) AS y ORDER BY IntKey", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$
            new String[] { "SELECT IntKey, LongNum FROM BQT2.SmallA order by intkey" }); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    /** Tests a user's order by is pushed to the source */
    @Test public void testPushOrderBy() {
        ProcessorPlan plan = helpPlan("SELECT pm3.g1.e1 FROM pm3.g1 ORDER BY e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm3.g1.e1 FROM pm3.g1 ORDER BY pm3.g1.e1"}); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }
   
    /** Tests an order by is not pushed to source due to join */
    @Test public void testDontPushOrderByWithJoin() {
        ProcessorPlan plan = helpPlan("SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1 INNER JOIN pm2.g2 ON pm3.g1.e1 = pm2.g2.e1 ORDER BY pm3.g1.e2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1 ORDER BY pm3.g1.e1", //$NON-NLS-1$
                           "SELECT pm2.g2.e1 FROM pm2.g2 ORDER BY pm2.g2.e1"}); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            1,      // Sort
            0       // UnionAll
        });                               
    }   

    /**
     * Tests that user's order by gets pushed to the source, but query
     * transformation order by is discarded
     */
    @Test public void testPushOrderByThroughFrame() {
        ProcessorPlan plan = helpPlan("SELECT e1, e2 FROM vm1.g14 ORDER BY e2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1 ORDER BY pm3.g1.e2"}); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }
   
    /**
     * Tests that query transformation order by is discarded by
     */
    @Test public void testPushOrderByThroughFrame2() {
        ProcessorPlan plan = helpPlan("SELECT e1, e2 FROM vm1.g1 ORDER BY e2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 order by e2"}); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }   

    /**
     * Tests that a user's order by does not get pushed to the source
     * if there is a UNION in the query transformation
     */
    @Test public void testPushOrderByThroughFrame4_Union() {
        ProcessorPlan plan = helpPlan("SELECT e1, e2 FROM vm1.g17 ORDER BY e1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1", //$NON-NLS-1$
                           "SELECT pm3.g2.e1, pm3.g2.e2 FROM pm3.g2"}); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            1,      // Sort
            1       // UnionAll
        });                                   
    }

    /** Tests outer join defect #7945 - see also defect #10050*/
    @Test public void testOuterJoinDefect7945() {
        ProcessorPlan plan = helpPlan(
            "SELECT BQT1.SmallA.IntKey AS SmallA_IntKey, BQT2.MediumB.IntKey AS MediumB_IntKey, BQT3.MediumB.IntKey AS MediumC_IntKey " //$NON-NLS-1$
            "FROM (BQT1.SmallA RIGHT OUTER JOIN BQT2.MediumB ON BQT1.SmallA.IntKey = BQT2.MediumB.IntKey) " //$NON-NLS-1$
            "RIGHT OUTER JOIN BQT3.MediumB ON BQT2.MediumB.IntKey = BQT3.MediumB.IntKey " +   //$NON-NLS-1$
            "WHERE BQT3.MediumB.IntKey < 1500"//$NON-NLS-1$
            FakeMetadataFactory.exampleBQTCached(),
            new String[] {
                "SELECT BQT3.MediumB.IntKey FROM BQT3.MediumB WHERE BQT3.MediumB.IntKey < 1500 order by intkey", //$NON-NLS-1$
                "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.IntKey < 1500 order by intkey", //$NON-NLS-1$
                "SELECT BQT2.MediumB.IntKey FROM BQT2.MediumB WHERE BQT2.MediumB.IntKey < 1500 order by intkey" }); //$NON-NLS-1$
               
        checkNodeTypes(plan, new int[] {
            3,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }   

    /** Tests outer join defect #7945 */
    @Test public void testFunctionSimplification1() {
        ProcessorPlan plan = helpPlan(
            "SELECT x FROM vm1.g18 WHERE x = 92.0",   //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            new String[] {
                "SELECT e4 FROM pm1.g1 WHERE e4 = 0.92" }); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }   
           
    @Test public void testCantPushJoin1() {
        ProcessorPlan plan = helpPlan(
            "SELECT a.e1, b.e2 FROM pm1.g1 a, pm1.g2 b WHERE a.e1 = b.e1"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, TestOptimizer.getGenericFinder(false),
            new String[] {"SELECT a.e1 FROM pm1.g1 AS a", "SELECT b.e1, b.e2 FROM pm1.g2 AS b"}, //$NON-NLS-1$ //$NON-NLS-2$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }

    @Test public void testCantPushJoin2() {
        ProcessorPlan plan = helpPlan(
            "SELECT a.e1, b.e2 FROM pm1.g1 a, pm1.g2 b, pm2.g1 c WHERE a.e1 = b.e1 AND b.e1 = c.e1"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, TestOptimizer.getGenericFinder(false),
            new String[] {"SELECT a.e1 FROM pm1.g1 AS a"//$NON-NLS-1$
                           "SELECT b.e1, b.e2 FROM pm1.g2 AS b", //$NON-NLS-1$
                           "SELECT c.e1 FROM pm2.g1 AS c"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            3,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }
   
    @Test public void testPushSelfJoin1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT a.e1, b.e2 FROM pm1.g1 a, pm1.g1 b WHERE a.e1 = b.e1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT a.e1, b.e2 FROM pm1.g1 AS a, pm1.g1 AS b WHERE a.e1 = b.e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    @Test public void testPushSelfJoin2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT a.e1 AS x, concat(a.e2, b.e2) AS y FROM pm1.g1 a, pm1.g1 b WHERE a.e1 = b.e1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT a.e1, a.e2, b.e2 FROM pm1.g1 AS a, pm1.g1 AS b WHERE a.e1 = b.e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }
       
    @Test public void testPushOuterJoin1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1 FROM pm1.g1 RIGHT OUTER JOIN pm1.g2 ON pm1.g1.e1 = pm1.g2.e1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1 FROM pm1.g2 LEFT OUTER JOIN pm1.g1 ON pm1.g1.e1 = pm1.g2.e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    @Test public void testPushOuterJoin2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1 FROM pm1.g1 RIGHT OUTER JOIN pm1.g2 ON pm1.g1.e1 = pm1.g2.e1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm1.g2.e1 FROM pm1.g2"}, //$NON-NLS-1$ //$NON-NLS-2$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }

    // With join expression that can't be pushed
    @Test public void testPushOuterJoin3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1 FROM pm1.g1 RIGHT OUTER JOIN pm1.g2 ON pm1.g1.e1 = pm1.g2.e1 || 'x'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm1.g2.e1 FROM pm1.g2"}, //$NON-NLS-1$ //$NON-NLS-2$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            2,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
                              
    }

    @Test public void testPushGroupBy1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, e2 as x FROM pm1.g1 GROUP BY e1, e2"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1, e2 FROM pm1.g1 GROUP BY e1, e2"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
                              
    }

    @Test public void testPushGroupBy2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1, MAX(e2) FROM pm1.g1 GROUP BY e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
                              
    }

    @Test public void testPushGroupBy3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, e2 as x FROM pm1.g1 GROUP BY e1, e2"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1, e2 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
                              
    }

    @Test public void testPushGroupBy4() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT x+2 AS y FROM (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT MAX(e2) FROM pm1.g1 GROUP BY e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
                              
    }
   
    @Test public void testPushHaving1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1 FROM pm1.g1 GROUP BY e1 HAVING MAX(e1) = 'zzz'"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1 GROUP BY e1 HAVING MAX(e1) = 'zzz'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }

    @Test public void testPushHaving2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1 FROM pm1.g1 GROUP BY e1 HAVING MAX(e1) = 'zzz'"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                                                 
    }

    @Test public void testPushHaving3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, false);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1 FROM pm1.g1 GROUP BY e1 HAVING MAX(e1) = 'zzz'"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                                                 
    }

    @Test public void testPushAggregate1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT MAX(e1) FROM pm1.g1"//$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT MAX(e1) FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }
   
    @Test public void testPushAggregate2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT MAX(e1) FROM pm1.g1 GROUP BY e1", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT MAX(e1) FROM pm1.g1 GROUP BY e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }

    @Test public void testPushAggregate3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e2, MAX(e1) FROM pm1.g1 GROUP BY e2", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e2, MAX(e1) FROM pm1.g1 GROUP BY e2"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }

    @Test public void testPushAggregate4() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_ORDERED, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e2, MAX(e1) FROM pm1.g1 GROUP BY e2 HAVING COUNT(e1) > 0", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e2, MAX(e1) FROM pm1.g1 GROUP BY e2 HAVING COUNT(e1) > 0"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }

    /**
     * Can't push aggs due to not being able to push COUNT in the HAVING clause.
     */
    @Test public void testPushAggregate5() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_ORDERED, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, false);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT e2, MAX(e1) FROM pm1.g1 GROUP BY e2 HAVING COUNT(e1) > 0", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e2, e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                                                 
    }

    /**
     * Can't push aggs due to not being able to push function inside the aggregate
     */
    @Test public void testPushAggregate6() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT COUNT(length(e1)) FROM pm1.g1", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                                                 
    }

    /**
     * Can't push aggs due to not being able to push function inside having
     */
    @Test public void testPushAggregate7() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(
            "SELECT COUNT(*) FROM pm1.g1 GROUP BY e1 HAVING length(e1) > 0", //$NON-NLS-1$
            FakeMetadataFactory.example1Cached(),
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );
   
        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                                                 
    }

    /**
     * BQT query that is failing
     */
    @Test public void testPushAggregate8() throws Exception {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
        capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$
        
        String sqlIn =         
            "SELECT intkey FROM bqt1.smalla AS sa WHERE (sa.intkey = 46) AND " + //$NON-NLS-1$
            "(sa.stringkey IN (46)) AND (sa.datevalue = (" + //$NON-NLS-1$
            "SELECT MAX(sa.datevalue) FROM bqt1.smalla AS sb " + //$NON-NLS-1$
            "WHERE (sb.intkey = sa.intkey) AND (sa.stringkey = sb.stringkey) ))"; //$NON-NLS-1$

        String sqlOut = "SELECT g_0.intkey FROM bqt1.smalla AS g_0 WHERE (g_0.intkey = 46) AND (g_0.stringkey = '46') AND (g_0.datevalue = (SELECT g_0.datevalue FROM bqt1.smalla AS g_1 WHERE (g_1.intkey = g_0.intkey) AND (g_1.stringkey = g_0.stringkey)))"; //$NON-NLS-1$

        ProcessorPlan plan = helpPlan(sqlIn,
            FakeMetadataFactory.exampleBQTCached(),
            null, capFinder,
            new String[] {sqlOut},
            ComparisonMode.EXACT_COMMAND_STRING );
   
        checkNodeTypes(plan, FULL_PUSHDOWN);                                                                 
    }
   
    @Test public void testQueryManyJoin() throws Exception {
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        ProcessorPlan plan = helpPlan("SELECT pm1.g1.e1 FROM pm1.g1 JOIN ((pm1.g2 JOIN pm1.g3 ON pm1.g2.e1=pm1.g3.e1) JOIN pm1.g4 ON pm1.g3.e1=pm1.g4.e1) ON pm1.g1.e1=pm1.g4.e1"//$NON-NLS-1$
            metadata,
            new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1, pm1.g3 AS g_2, pm1.g4 AS g_3 WHERE (g_1.e1 = g_2.e1) AND (g_2.e1 = g_3.e1) AND (g_0.e1 = g_3.e1)"}, ComparisonMode.EXACT_COMMAND_STRING ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }
   
    @Test public void testPushSelectDistinct() {
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        ProcessorPlan plan = helpPlan("SELECT DISTINCT e1 FROM pm3.g1"//$NON-NLS-1$
            metadata,
            new String[] { "SELECT DISTINCT e1 FROM pm3.g1"} ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    @Test public void testPushFunctionInCriteria1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1 FROM pm1.g1 WHERE upper(e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    @Test public void testPushFunctionInSelect1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT lower(e1) FROM pm1.g1 WHERE upper(e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT lower(e1) FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    @Test public void testPushFunctionInSelect2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT lower(e1), upper(e1), e2 FROM pm1.g1 WHERE upper(e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT lower(e1), upper(e1), e2 FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }
   
    @Test public void testPushFunctionInSelect3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, false);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT lower(e1), upper(e1) FROM pm1.g1 WHERE upper(e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }   

    @Test public void testPushFunctionInSelect4() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT x FROM (SELECT lower(e1) AS x, upper(e1) AS y FROM pm1.g1 WHERE upper(e1) = 'X') AS z"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT lcase(e1) FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }   

    @Test public void testPushFunctionInSelect5() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT y, e, x FROM (SELECT lower(e1) AS x, upper(e1) AS y, 5 as z, e1 AS e FROM pm1.g1 WHERE upper(e1) = 'X') AS w"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT ucase(e1), e1, lcase(e1) FROM pm1.g1 WHERE ucase(e1) = 'X'"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }   

    @Test public void testPushFunctionInSelect6_defect_10081() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setFunctionSupport("upper", true); //$NON-NLS-1$
        caps.setFunctionSupport("lower", false); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT upper(lower(e1)) FROM pm1.g1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1 FROM pm1.g1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }
           
    @Test public void testPushFunctionInSelectWithOrderBy1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, lower(e1) FROM pm1.g1 WHERE upper(e1) = 'X' ORDER BY e1"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1, lcase(e1) FROM pm1.g1 WHERE ucase(e1) = 'X' ORDER BY e1"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }

    /** defect 13336 */
    @Test public void testPushFunctionInSelectWithOrderBy1a() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, lower(e1) AS x FROM pm1.g1 WHERE upper(e1) = 'X' ORDER BY x"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1, lcase(e1) AS x FROM pm1.g1 WHERE ucase(e1) = 'X' ORDER BY x"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }   
   
    /** defect 13336 */
    @Test public void testPushFunctionInSelectWithOrderBy2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport(SourceSystemFunctions.LCASE, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT e1, x FROM (SELECT e1, lower(e1) AS x FROM pm1.g1 WHERE upper(e1) = 'X') AS z ORDER BY x"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT e1, lcase(e1) AS EXPR FROM pm1.g1 WHERE ucase(e1) = 'X' ORDER BY EXPR"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }
   
    @Test public void testPushFunctionInJoin1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1 = convert(pm1.g2.e2, string) AND upper(pm1.g1.e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2 WHERE (pm1.g1.e1 = convert(pm1.g2.e2, string)) AND (ucase(pm1.g1.e1) = 'X') AND (ucase(convert(pm1.g2.e2, string)) = 'X')"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, FULL_PUSHDOWN);                                   
    }   

    @Test public void testPushFunctionInJoin2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2, pm1.g3 WHERE pm1.g1.e1 = convert(pm1.g2.e2, string) AND pm1.g1.e1 = concat(pm1.g3.e1, 'a') AND upper(pm1.g1.e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2 WHERE (pm1.g1.e1 = convert(pm1.g2.e2, string)) AND (ucase(pm1.g1.e1) = 'X') AND (ucase(convert(pm1.g2.e2, string)) = 'X')", //$NON-NLS-1$
                    "SELECT pm1.g3.e1 FROM pm1.g3"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            2,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }   

    @Test public void testPushFunctionInJoin3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setFunctionSupport(SourceSystemFunctions.UCASE, true);
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        // Add join capability to pm1
        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2, (SELECT e1 AS x FROM pm1.g3) AS g WHERE pm1.g1.e1 = convert(pm1.g2.e2, string) AND pm1.g1.e1 = concat(g.x, 'a') AND upper(pm1.g1.e1) = 'X'"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT pm1.g1.e1, pm1.g2.e3 FROM pm1.g1, pm1.g2 WHERE (pm1.g1.e1 = convert(pm1.g2.e2, string)) AND (ucase(pm1.g1.e1) = 'X') AND (ucase(convert(pm1.g2.e2, string)) = 'X')", //$NON-NLS-1$
                    "SELECT e1 FROM pm1.g3"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            2,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }   

    @Test public void testUnionOverFunctions() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$

        QueryMetadataInterface metadata = FakeMetadataFactory.exampleBQTCached();
        
        ProcessorPlan plan = helpPlan(
            "SELECT StringCol AS E " //$NON-NLS-1$
            "FROM (SELECT CONVERT(BQT1.SmallA.IntNum, string) AS StringCol, BQT1.SmallA.IntNum AS IntCol FROM BQT1.SmallA " + //$NON-NLS-1$
            "UNION ALL SELECT BQT1.SmallB.StringNum, CONVERT(BQT1.SmallB.StringNum, integer) FROM BQT1.SmallB) AS x"//$NON-NLS-1$
            metadata,
            null, capFinder,
            new String[] {"SELECT CONVERT(BQT1.SmallA.IntNum, string) FROM BQT1.SmallA", //$NON-NLS-1$
                    "SELECT BQT1.SmallB.StringNum FROM BQT1.SmallB"}, //$NON-NLS-1$
            SHOULD_SUCCEED );

        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        });                                   
    }   

    @Test public void testDefect9827() {
        QueryMetadataInterface metadata = FakeMetadataFactory.exampleBQTCached();
       
        ProcessorPlan plan = helpPlan("SELECT intkey, c FROM (SELECT DISTINCT b.intkey, b.intnum, a.stringkey AS c FROM bqt1.smalla AS a, bqt1.smallb AS b WHERE a.INTKEY = b.INTKEY) AS x ORDER BY x.intkey", metadata, //$NON-NLS-1$
            new String[] {"SELECT DISTINCT b.intkey, b.intnum, a.stringkey FROM bqt1.smalla AS a, bqt1.smallb AS b WHERE a.INTKEY = b.INTKEY"} ); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            1,      // Sort
            0       // UnionAll
        });                                   
    }

    /**
     * This tests that a criteria with no elements is not pushed down,
     * but instead is cleaned up properly later
     * See defect 9865
     */
    @Test public void testCrossJoinNoElementCriteriaOptimization2() {
        ProcessorPlan plan = helpPlan("select Y.e1, Y.e2 FROM vm1.g1 X, vm1.g1 Y where {b'true'} = {b'true'}", example1()//$NON-NLS-1$
            new String[]{"SELECT 1 FROM pm1.g1 AS g1__1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1"}); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    /**
     * <p>This tests that a SELECT node with no groups is not pushed down without the capability to have a subquery in the where clause.
     */
    @Test public void testCrossJoinNoElementCriteriaOptimization3() {
        ProcessorPlan plan = helpPlan("select Y.e1, Y.e2 FROM vm1.g1 X, vm1.g1 Y where {b'true'} in (select e3 FROM vm1.g1)", example1()//$NON-NLS-1$
            new String[]{"SELECT 1 FROM pm1.g1 AS g1__1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1"}); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            1,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }

    /**
     * <p>This tests that a SELECT node with no groups is pushed down.
     */
    @Test public void testCrossJoinNoElementCriteriaOptimization4() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
       
        ProcessorPlan plan = helpPlan("select Y.e1, Y.e2 FROM vm1.g1 X, vm1.g1 Y where {b'true'} in (select e3 FROM vm1.g1)", example1(), null, capFinder,  //$NON-NLS-1$
            new String[]{"SELECT 1 FROM pm1.g1 AS g1__1 WHERE TRUE IN (SELECT pm1.g1.e3 FROM pm1.g1)", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1"}, true); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });
    }
   
    /**
     * Criteria should be copied across this join
     */
    @Test public void testCopyCriteriaWithOuterJoin_defect10050(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 left outer join pm2.g2 on pm2.g1.e1=pm2.g2.e1 where pm2.g1.e1 IN ('a', 'b')", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1, pm2.g2.e1 FROM pm2.g1 LEFT OUTER JOIN pm2.g2 ON pm2.g1.e1 = pm2.g2.e1 AND pm2.g2.e1 IN ('a', 'b') WHERE pm2.g1.e1 IN ('a', 'b')" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    /**
     * Criteria should be copied across this join
     */
    @Test public void testCopyCriteriaWithOuterJoin2_defect10050(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 left outer join pm2.g2 on pm2.g1.e1=pm2.g2.e1 and pm2.g1.e2=pm2.g2.e2 where pm2.g1.e1 = 'a' and pm2.g1.e2 = 1", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e1, g_1.e1 FROM pm2.g1 AS g_0 LEFT OUTER JOIN pm2.g2 AS g_1 ON g_0.e1 = g_1.e1 AND g_0.e2 = g_1.e2 AND g_1.e1 = 'a' AND g_1.e2 = 1 WHERE (g_0.e1 = 'a') AND (g_0.e2 = 1)" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    /**
     * See also case 2912.
     */
    @Test public void testCopyCriteriaWithOuterJoin5_defect10050(){
       
        ProcessorPlan plan = helpPlan(
            "select pm2.g1.e1, pm2.g2.e1, pm2.g3.e1 from ( (pm2.g1 right outer join pm2.g2 on pm2.g1.e1=pm2.g2.e1) right outer join pm2.g3 on pm2.g2.e1=pm2.g3.e1) where pm2.g3.e1 = 'a'", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_2.e1, g_1.e1, g_0.e1 FROM pm2.g3 AS g_0 LEFT OUTER JOIN (pm2.g2 AS g_1 LEFT OUTER JOIN pm2.g1 AS g_2 ON g_2.e1 = g_1.e1 AND g_2.e1 = 'a') ON g_1.e1 = g_0.e1 AND g_1.e1 = 'a' WHERE g_0.e1 = 'a'" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }
   
    /**
     *
     */
    @Test public void testCopyCriteriaWithOuterJoin6_defect10050(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 left outer join pm2.g2 on pm2.g2.e1=pm2.g1.e1 where pm2.g1.e1 IN ('a', 'b')", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1, pm2.g2.e1 FROM pm2.g1 LEFT OUTER JOIN pm2.g2 ON pm2.g2.e1 = pm2.g1.e1 AND pm2.g2.e1 IN ('a', 'b') WHERE pm2.g1.e1 IN ('a', 'b')" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    /**
     * Same as previous test, only right outer join
     */
    @Test public void testCopyCriteriaWithOuterJoin7_defect10050(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 right outer join pm2.g2 on pm2.g2.e1=pm2.g1.e1 where pm2.g2.e1 IN ('a', 'b')", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1, pm2.g2.e1 FROM pm2.g2 LEFT OUTER JOIN pm2.g1 ON pm2.g2.e1 = pm2.g1.e1 AND pm2.g1.e1 IN ('a', 'b') WHERE pm2.g2.e1 IN ('a', 'b')" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }        
   
    @Test public void testCleanCriteria(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1, pm2.g2 where pm2.g1.e1=pm2.g2.e1 and pm2.g1.e2 IN (1, 2)", example1(), //$NON-NLS-1$
            new String[] { "SELECT pm2.g1.e1, pm2.g2.e1 FROM pm2.g1, pm2.g2 WHERE (pm2.g1.e1 = pm2.g2.e1) AND (pm2.g1.e2 IN (1, 2))" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    @Test public void testCleanCriteria2(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1, pm2.g2 where pm2.g1.e1=pm2.g2.e1 and pm2.g1.e1 = 'a'", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e1, g_1.e1 FROM pm2.g1 AS g_0, pm2.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = 'a') AND (g_1.e1 = 'a')" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }

    @Test public void testCleanCriteria3(){
       
        ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 inner join pm2.g2 on pm2.g1.e1=pm2.g2.e1 where pm2.g1.e1 = 'a'", example1(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e1, g_1.e1 FROM pm2.g1 AS g_0, pm2.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = 'a') AND (g_1.e1 = 'a')" }); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);        
    }
   
   
    @Test public void testPushSubqueryInWhereClause1() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select e1 FROM pm1.g2)", example1()//$NON-NLS-1$
            null, capFinder,
            new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 IN (SELECT e1 FROM pm1.g2)"}, SHOULD_SUCCEED ); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    @Test public void testPushSubqueryInWhereClause2() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (select max(e1) FROM pm1.g2)", example1()//$NON-NLS-1$
            null, capFinder,
            new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 IN (SELECT MAX(e1) FROM pm1.g2)" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }

    /**
     * Check that subquery is pushed if the subquery selects a function that is pushed
     */
    @Test public void testPushSubqueryInWhereClause3() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
        caps.setFunctionSupport("ltrim", true); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$

        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT ltrim(e1) FROM pm1.g2)", FakeMetadataFactory.example1Cached()//$NON-NLS-1$
            null, capFinder,
            new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 IN (SELECT ltrim(e1) FROM pm1.g2)" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }  

    /**
     * Check that subquery is pushed if the subquery selects an aliased function that is pushed
     */
    @Test public void testPushSubqueryInWhereClause4() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN_SUBQUERY, true);
        caps.setFunctionSupport("ltrim", true); //$NON-NLS-1$
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", new BasicSourceCapabilities()); //$NON-NLS-1$

        ProcessorPlan plan = helpPlan("Select e1 from pm1.g1 where e1 in (SELECT ltrim(e1) as m FROM pm1.g2)", FakeMetadataFactory.example1Cached()//$NON-NLS-1$
            null, capFinder,
            new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 IN (SELECT ltrim(e1) FROM pm1.g2)" }, SHOULD_SUCCEED); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);
    }  

    /** Case 1456, defect 10492*/
    @Test public void testAliasingDefect1(){
        // Create query
        String sql = "SELECT e1 FROM vm1.g1 X WHERE e2 = (SELECT MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1)";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        ProcessorPlan plan = helpPlan(sql, FakeMetadataFactory.example1Cached()
            null, capFinder,
            new String[] { "SELECT g1__1.e1 FROM pm1.g1 AS g1__1 WHERE g1__1.e2 = (SELECT MAX(pm1.g1.e2) FROM pm1.g1 WHERE pm1.g1.e1 = g1__1.e1)" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }  

    /** Case 1456, defect 10492*/
    @Test public void testAliasingDefect2(){
        // Create query
        String sql = "SELECT X.e1 FROM vm1.g1 X, vm1.g1 Z WHERE X.e2 = (SELECT MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1 AND Y.e2 = Z.e2) AND X.e1 = Z.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT g1__1.e1 FROM pm1.g1 AS g1__1, pm1.g1 AS g1__2 WHERE (g1__1.e2 = (SELECT MAX(pm1.g1.e2) FROM pm1.g1 WHERE (pm1.g1.e1 = g1__1.e1) AND (pm1.g1.e2 = g1__2.e2))) AND (g1__1.e1 = g1__2.e1)" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }  

    /** Case 1456, defect 10492*/
    @Test public void testAliasingDefect3() throws Exception {
        // Create query
        String sql = "SELECT X.e1 FROM pm1.g2, vm1.g1 X WHERE X.e2 = ALL (SELECT MAX(e2) FROM vm1.g1 Y WHERE X.e1 = Y.e1) AND X.e1 = pm1.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.CRITERIA_QUANTIFIED_ALL, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT g_1.e1 FROM pm1.g2 AS g_0, pm1.g1 AS g_1 WHERE (g_1.e1 = g_0.e1) AND (g_1.e2 = ALL (SELECT MAX(g_2.e2) FROM pm1.g1 AS g_2 WHERE g_2.e1 = g_1.e1))" }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        checkNodeTypes(plan, FULL_PUSHDOWN);
    }
       
    /** Should use merge join since neither access node is "strong" - order by's pushed to source */
    @Test public void testUseMergeJoin3(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1 = pm1.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 ORDER BY pm1.g1.e1", "SELECT pm1.g2.e1 FROM pm1.g2 ORDER BY pm1.g2.e1" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }   

    /** Model supports order by, should be pushed to the source */
    @Test public void testUseMergeJoin4(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1 = pm1.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 500));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm1.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 ORDER BY pm1.g1.e1", "SELECT pm1.g2.e1 FROM pm1.g2 ORDER BY pm1.g2.e1" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }

    /** Should use merge join, since costs are not known, neither access node is "strong" */
    @Test public void testUseMergeJoin5_CostsNotKnown(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1 = pm1.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1Cached();
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm1.g2.e1 FROM pm1.g2" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }
   
    /** one side of join supports order by, the other doesn't*/
    @Test public void testUseMergeJoin7(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm2.g2 WHERE pm1.g1.e1 = pm2.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 500));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm2.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 ORDER BY pm1.g1.e1", "SELECT pm2.g2.e1 FROM pm2.g2" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }    

    /** reverse of testUseMergeJoin7 */
    @Test public void testUseMergeJoin7a(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm2.g2 WHERE pm1.g1.e1 = pm2.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 500));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm2.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm2.g2.e1 FROM pm2.g2 ORDER BY pm2.g2.e1" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }  

    /** function on one side of join should prevent order by from being pushed down*/
    @Test public void testUseMergeJoin8(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm2.g2 WHERE concat(pm1.g1.e1, 'x') = pm2.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 500));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm2.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm2.g2.e1 FROM pm2.g2 ORDER BY pm2.g2.e1" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            2,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }

    /** Model supports order by, functions in join criteria */
    @Test public void testUseMergeJoin9(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2 WHERE concat(pm1.g1.e1, 'x') = concat(pm1.g2.e1, 'x')";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_SELFJOIN, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 500));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm1.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm1.g2.e1 FROM pm1.g2" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            3,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }

    /** should be one dependent join */
    @Test public void testMultiMergeJoin1(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2, pm1.g3 WHERE pm1.g1.e1 = pm1.g2.e1 AND pm1.g2.e1 = pm1.g3.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE / 4));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm1.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE));
        FakeMetadataObject g3 = metadata.getStore().findObject("pm1.g3", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g3.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + BufferManager.DEFAULT_PROCESSOR_BATCH_SIZE));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1", "SELECT pm1.g2.e1 FROM pm1.g2", "SELECT pm1.g3.e1 FROM pm1.g3" }, SHOULD_SUCCEED); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        checkNodeTypes(plan, new int[] {
            3,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }

    @Test public void testLargeSetCriteria() {
        //      Create query
        String sql = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA INNER JOIN BQT2.SmallB ON BQT1.SmallA.IntKey = BQT2.SmallB.IntKey WHERE BQT1.SmallA.IntKey IN (1,2,3,4,5)";     //$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1));
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("BQT2", caps); //$NON-NLS-1$
       
        ProcessorPlan plan = helpPlan(sql, FakeMetadataFactory.exampleBQTCached()
            null, capFinder,
            new String[] { "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA ORDER BY BQT1.SmallA.IntKey"//$NON-NLS-1$
                            "SELECT BQT2.SmallB.IntKey FROM BQT2.SmallB ORDER BY BQT2.SmallB.IntKey" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            2,      // Select
            0,      // Sort
            0       // UnionAll
        });           
    }
   
    @Test public void testMergeJoin_defect11236(){
        // Create query
        String sql = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT1.SmallB WHERE BQT1.SmallA.IntKey = (BQT1.SmallB.IntKey + 1)";     //$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1000));
        capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$

        QueryMetadataInterface metadata = FakeMetadataFactory.exampleBQTCached();
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT BQT1.SmallB.IntKey FROM BQT1.SmallB"//$NON-NLS-1$
                            "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA ORDER BY BQT1.SmallA.IntKey" }, SHOULD_SUCCEED); //$NON-NLS-1$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            2,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }
       
    @Test public void testNoFrom() {
        ProcessorPlan plan = helpPlan("SELECT 1", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] {} );

        checkNodeTypes(plan, new int[] {
            0,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }

    @Test public void testINCriteria_defect10718(){
        // Create query
        String sql = "SELECT pm1.g1.e1 FROM pm1.g1, pm1.g2 WHERE pm1.g1.e1 = pm1.g2.e1";//$NON-NLS-1$

        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, false);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1000));
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        FakeMetadataFacade metadata = FakeMetadataFactory.example1();
        FakeMetadataObject g1 = metadata.getStore().findObject("pm1.g1", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g1.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY - 1));
        FakeMetadataObject g2 = metadata.getStore().findObject("pm1.g2", FakeMetadataObject.GROUP); //$NON-NLS-1$
        g2.putProperty(FakeMetadataObject.Props.CARDINALITY, new Integer(RuleChooseDependent.DEFAULT_INDEPENDENT_CARDINALITY + 1000));
   
        ProcessorPlan plan = helpPlan(sql, metadata, 
            null, capFinder,
            new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 ORDER BY pm1.g1.e1", "SELECT pm1.g2.e1 FROM pm1.g2 ORDER BY pm1.g2.e1"}, SHOULD_SUCCEED); //$NON-NLS-1$  //$NON-NLS-2$
        checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });        
    }
   
    @Test public void testDefect10711(){
        ProcessorPlan plan = helpPlan("SELECT * from vm1.g1a as X", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] {"SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1"} ); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);        

    }
   
    // SELECT 5, SUM(IntKey) FROM BQT1.SmallA
    @Test public void testAggregateNoGroupByWithExpression() {
        ProcessorPlan plan = helpPlan("SELECT 5, SUM(IntKey) FROM BQT1.SmallA", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$
            new String[] { "SELECT IntKey FROM BQT1.SmallA"  }); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            1,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });                                   
    }
   
    /** defect 11630 - note that the lookup function is not pushed down, it will actually be evaluated before being sent to the connector */
    @Test public void testLookupFunction() {

        ProcessorPlan plan = helpPlan("SELECT e1 FROM pm1.g2 WHERE LOOKUP('pm1.g1','e1', 'e2', 1) IS NULL", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT e1 FROM pm1.g2 WHERE LOOKUP('pm1.g1', 'e1', 'e2', 1) IS NULL"  }); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);

    }
   
    /** case 5213 - note here that the lookup cannot be pushed down since it is dependent upon an element symbol*/
    @Test public void testLookupFunction2() throws Exception {

        ProcessorPlan plan = helpPlan("SELECT e1 FROM pm1.g2 WHERE LOOKUP('pm1.g1','e1', 'e2', e2) IS NULL", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT g_0.e2, g_0.e1 FROM pm1.g2 AS g_0"  }, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            0       // UnionAll
        });

    }
   
    /** defect 21965 */
    @Test public void testLookupFunctionInSelect() {
        ProcessorPlan plan = helpPlan("SELECT e1, LOOKUP('pm1.g1','e1', 'e2', 1) FROM pm1.g2", FakeMetadataFactory.example1Cached(), //$NON-NLS-1$
            new String[] { "SELECT e1, LOOKUP('pm1.g1','e1', 'e2', 1) FROM pm1.g2"  }); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);
    }
   
    // SELECT * FROM (SELECT IntKey FROM BQT1.SmallA UNION ALL SELECT DISTINCT IntNum FROM BQT1.SmallA) AS x WHERE IntKey = 0
    @Test public void testCase1649() {
        ProcessorPlan plan = helpPlan("SELECT * FROM (SELECT DISTINCT IntKey FROM BQT1.SmallA UNION ALL SELECT IntNum FROM BQT1.SmallA) AS x WHERE IntKey = 0", FakeMetadataFactory.exampleBQTCached(), //$NON-NLS-1$
            ne