/*
 * 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 java.util.Arrays;
import java.util.List;

import org.junit.Test;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataStore;
import org.teiid.metadata.Schema;
import org.teiid.metadata.Table;
import org.teiid.query.mapping.relational.QueryNode;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.optimizer.TestOptimizer.ComparisonMode;
import org.teiid.query.optimizer.TestOptimizer.DependentProjectNode;
import org.teiid.query.optimizer.TestOptimizer.DependentSelectNode;
import org.teiid.query.optimizer.TestOptimizer.DupRemoveSortNode;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
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.processor.FakeDataManager;
import org.teiid.query.processor.HardcodedDataManager;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.processor.TestProcessor;
import org.teiid.query.processor.relational.*;
import org.teiid.query.unittest.RealMetadataFactory;

@SuppressWarnings("nls")
public class TestLimit {

    private static final int[] FULL_PUSHDOWN = new int[] {
                1,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                0,      // Limit
                0,      // NestedLoopJoinStrategy
                0,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                0,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
            };
    
    public static final Class<?>[] NODE_TYPES = new Class[] {
        AccessNode.class,
        DependentAccessNode.class,
        DependentSelectNode.class,
        DependentProjectNode.class,
        DupRemoveNode.class,
        GroupingNode.class,
        LimitNode.class,
        NestedLoopJoinStrategy.class,
        MergeJoinStrategy.class,
        NullNode.class,
        PlanExecutionNode.class,
        ProjectNode.class,
        SelectNode.class,
        SortNode.class,
        UnionAllNode.class
    };
    
    private static TransformationMetadata exampleMetadata() {
    	MetadataStore metadataStore = new MetadataStore();
        // Create models
        Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore); //$NON-NLS-1$
        Schema vm1 = RealMetadataFactory.createVirtualModel("vm1", metadataStore);  //$NON-NLS-1$

        // Create physical groups
        Table pm1g1 = RealMetadataFactory.createPhysicalGroup("g1", pm1); //$NON-NLS-1$
        Table pm1g2 = RealMetadataFactory.createPhysicalGroup("g2", pm1); //$NON-NLS-1$
        Table pm1g3 = RealMetadataFactory.createPhysicalGroup("g3", pm1); //$NON-NLS-1$
        Table pm1g4 = RealMetadataFactory.createPhysicalGroup("g4", pm1); //$NON-NLS-1$
        Table pm1g5 = RealMetadataFactory.createPhysicalGroup("g5", pm1); //$NON-NLS-1$
        Table pm1g6 = RealMetadataFactory.createPhysicalGroup("g6", pm1); //$NON-NLS-1$
                
        // Create physical elements
        RealMetadataFactory.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 });
        RealMetadataFactory.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 });
        RealMetadataFactory.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<Column> pm1g4e = RealMetadataFactory.createElements(pm1g4,
            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 });
        pm1g4e.get(1).setSelectable(false);
        pm1g4e.get(3).setSelectable(false);
        List<Column> pm1g5e = RealMetadataFactory.createElements(pm1g5,
            new String[] { "e1" }, //$NON-NLS-1$
            new String[] { DataTypeManager.DefaultDataTypes.STRING });
        pm1g5e.get(0).setSelectable(false);
        RealMetadataFactory.createElements(pm1g6,
            new String[] { "in", "in3" }, //$NON-NLS-1$ //$NON-NLS-2$ 
            new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING });
         
        // Create virtual groups
        QueryNode vm1g1n1 = new QueryNode("SELECT * FROM pm1.g1 LIMIT 100"); //$NON-NLS-1$ //$NON-NLS-2$
        Table vm1g1 = RealMetadataFactory.createVirtualGroup("g1", vm1, vm1g1n1); //$NON-NLS-1$

        // Create virtual elements
        RealMetadataFactory.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 });

        QueryNode vm1g2n1 = new QueryNode("SELECT * FROM vm1.g1 ORDER BY e1"); //$NON-NLS-1$ //$NON-NLS-2$
        Table vm1g2 = RealMetadataFactory.createVirtualGroup("g2", vm1, vm1g2n1); //$NON-NLS-1$

        // Create virtual elements
        RealMetadataFactory.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 });

        return RealMetadataFactory.createTransformationMetadata(metadataStore, "example");
    }
    @Test public void testLimit() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

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

        String sql = "SELECT * FROM pm1.g1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }

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

        String sql = "SELECT * FROM pm1.g1 limit 50, 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

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

        String sql = "SELECT * FROM pm1.g1 limit 50, 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 LIMIT 150" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);  

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

        String sql = "SELECT * FROM pm1.g1 limit 50, 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 LIMIT 50, 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }
    
    @Test public void testSort() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 order by e1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

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

    @Test public void testSortPushed() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm3.g1 order by e1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

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

    @Test public void testSortPushedWithLimit() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm3.g1 order by e1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1 LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }

    @Test public void testSortUnderLimitNotRemoved() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM (SELECT * FROM pm3.g1 order by e1 limit 100) AS V1 ORDER BY v1.e2";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            2,      // Sort
            0       // UnionAll
        }, NODE_TYPES);
    }
    
    //TODO: there is a redundent project node here
    @Test public void testSortAboveLimitNotPushed() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM vm1.g2 order by e1";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, exampleMetadata(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            1,      // Sort
            0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testLimitNotPushedWithUnion() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 UNION SELECT * FROM PM1.g2 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1", "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM PM1.g2" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testStrictLimitWithUnion() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM (select e2 from pm1.g1 where e1 = 'a') x UNION ALL SELECT e2 FROM PM1.g2 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g2.e2 FROM pm1.g2 LIMIT 100", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            2,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            1,      // Select
            0,      // Sort
            1       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testPushedLimit() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_UNION, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT e2 from pm1.g2 UNION ALL SELECT e2 FROM PM1.g2 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g2.e2 AS c_0 FROM pm1.g2 UNION ALL SELECT pm1.g2.e2 AS c_0 FROM pm1.g2 LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
    }
    
    @Test public void testLimitNotPushedWithDupRemove() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT distinct * FROM pm1.g1 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testLimitPushedWithUnionAll() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 UNION ALL SELECT * FROM PM1.g2 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM PM1.g2 LIMIT 100", "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1 LIMIT 100" //$NON-NLS-1$ //$NON-NLS-2$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testLimitWithOffsetPushedWithUnion() throws Exception {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.ROW_OFFSET, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 UNION ALL SELECT * FROM PM1.g2 LIMIT 50, 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1 AS c_0, pm1.g1.e2 AS c_1, pm1.g1.e3 AS c_2, pm1.g1.e4 AS c_3 FROM pm1.g1 LIMIT 150", "SELECT pm1.g2.e1 AS c_0, pm1.g2.e2 AS c_1, pm1.g2.e3 AS c_2, pm1.g2.e4 AS c_3 FROM pm1.g2 LIMIT 150" //$NON-NLS-1$ //$NON-NLS-2$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testLimitNotPushedWithUnionOrderBy() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM pm1.g1 UNION SELECT * FROM PM1.g2 ORDER BY e1 LIMIT 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g2.e1, pm1.g2.e2, pm1.g2.e3, pm1.g2.e4 FROM PM1.g2", "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1" //$NON-NLS-1$ //$NON-NLS-2$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            1,      // Limit
            0,      // NestedLoopJoinStrategy
            0,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            0,      // Project
            0,      // Select
            0,      // Sort
            1       // UnionAll
        }, NODE_TYPES);
        TestOptimizer.checkNodeTypes(plan, new int[] {1}, new Class[]{DupRemoveSortNode.class});
    }
    
    @Test public void testCombinedLimits() throws Exception {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * from (SELECT pm1.g1.e1 FROM pm1.g1 LIMIT 10, 100) x LIMIT 20, 75";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1 AS c_0 FROM pm1.g1 LIMIT 105" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);  

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

    @Test public void testCombinedLimitsWithOffset() throws Exception {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.ROW_OFFSET, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        // pm1 model supports order by
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT * from (SELECT pm1.g1.e1 FROM pm1.g1 LIMIT 10, 100) x LIMIT 40, 75";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm1.g1.e1 AS c_0 FROM pm1.g1 LIMIT 10, 60" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }

    @Test public void testInlineView() throws TeiidComponentException, TeiidProcessingException {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        //caps.setCapabilitySupport(SourceCapabilities.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM (SELECT * FROM pm3.g1) as v1 limit 100";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm3.g1 AS g_0 LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, ComparisonMode.EXACT_COMMAND_STRING);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }
    
    /**
     * This turns out to be an important test for LIMIT: there are several nodes
     * (e.g. grouping, inline views, aggregates, sorting, joins, etc) that should not be pushed
     * down (because they change row order or row count) if there is already a limit node in that plan branch,
     * which can only be placed above LIMIT with an inline view. This test acts as a gatekeeper for avoiding
     * several of those pushdowns.
     * @throws TeiidProcessingException 
     * @throws TeiidComponentException 
     * 
     * @since 4.3
     */
    @Test public void testInlineViewAboveLimitNotMerged() throws TeiidComponentException, TeiidProcessingException {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_GROUP_ALIAS, true);
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM (SELECT * FROM pm3.g1 limit 100) as v1 order by e1";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT v_0.c_0, v_0.c_1, v_0.c_2, v_0.c_3 FROM (SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm3.g1 AS g_0 LIMIT 100) AS v_0 ORDER BY c_0" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, ComparisonMode.EXACT_COMMAND_STRING);  

        TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
    }
    
    /**
     * since there is no order by with the nested limit, the criteria can be pushed through 
     *
     */
    @Test public void testCriteriaPushedUnderLimit() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        // pm3 model supports order by
        capFinder.addCapabilities("pm3", caps); //$NON-NLS-1$

        String sql = "SELECT * FROM (SELECT * FROM pm3.g1 /*+ non_strict */ limit 100) as v1 where v1.e1 = 1";//$NON-NLS-1$
        String[] expectedSql = new String[] {
            "SELECT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 WHERE pm3.g1.e1 = '1' LIMIT 100" //$NON-NLS-1$
            };
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                                    null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }
    
    @Test public void testInlineViewJoin() throws TeiidComponentException, TeiidProcessingException {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_UNION, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "SELECT x FROM ((SELECT e1 as x FROM pm1.g1 LIMIT 700) c INNER JOIN (SELECT e1 FROM pm1.g2) d ON d.e1 = c.x) order by x LIMIT 5";//$NON-NLS-1$
        String[] expectedSql = new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 LIMIT 700", "SELECT g_0.e1 FROM pm1.g2 AS g_0"};//$NON-NLS-1$ //$NON-NLS-2$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                      null, capFinder, expectedSql, ComparisonMode.EXACT_COMMAND_STRING);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
                                        2,      // Access
                                        0,      // DependentAccess
                                        0,      // DependentSelect
                                        0,      // DependentProject
                                        0,      // DupRemove
                                        0,      // Grouping
                                        1,      // Limit
                                        0,      // NestedLoopJoinStrategy
                                        1,      // MergeJoinStrategy
                                        0,      // Null
                                        0,      // PlanExecution
                                        1,      // Project
                                        0,      // Select
                                        1,      // Sort
                                        0       // UnionAll
        }, NODE_TYPES);
        
        //test to ensure that the unnecessary inline view removal is done properly
        FakeDataManager fdm = new FakeDataManager();
        TestProcessor.sampleData1(fdm);
        TestProcessor.helpProcess(plan, fdm, new List[] {
        		Arrays.asList("a"), //$NON-NLS-1$
        		Arrays.asList("a"), //$NON-NLS-1$
        		Arrays.asList("a"), //$NON-NLS-1$
        		Arrays.asList("a"), //$NON-NLS-1$
        		Arrays.asList("a"), //$NON-NLS-1$
        });
    }
    
    @Test public void testDontPushSelectWithOrderedLimit() {
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$

        String sql = "select * from (SELECT e1 as x FROM pm1.g1 order by x LIMIT 700) y where x = 1";//$NON-NLS-1$
        String[] expectedSql = new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0"};//$NON-NLS-1$ 
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                      null, capFinder, expectedSql, true);  

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

        String sql = "select * from (SELECT e1 as x FROM pm1.g1 order by x LIMIT 10, 700) y where x = 1";//$NON-NLS-1$
        String[] expectedSql = new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0"};//$NON-NLS-1$ 
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), 
                                      null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
                                        1,      // Access
                                        0,      // DependentAccess
                                        0,      // DependentSelect
                                        0,      // DependentProject
                                        0,      // DupRemove
                                        0,      // Grouping
                                        1,      // Limit
                                        0,      // NestedLoopJoinStrategy
                                        0,      // MergeJoinStrategy
                                        0,      // Null
                                        0,      // PlanExecution
                                        1,      // Project
                                        1,      // Select
                                        0,      // Sort
                                        0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testLimitWithNoAccessNode() {
        String sql = "select 1 limit 1";//$NON-NLS-1$
        String[] expectedSql = new String[] {};
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), expectedSql);  

        TestOptimizer.checkNodeTypes(plan, new int[] {
                                        0,      // Access
                                        0,      // DependentAccess
                                        0,      // DependentSelect
                                        0,      // DependentProject
                                        0,      // DupRemove
                                        0,      // Grouping
                                        1,      // Limit
                                        0,      // NestedLoopJoinStrategy
                                        0,      // MergeJoinStrategy
                                        0,      // Null
                                        0,      // PlanExecution
                                        1,      // Project
                                        0,      // Select
                                        0,      // Sort
                                        0       // UnionAll
        }, NODE_TYPES);
    }
    
    /**
     * Note here that the criteria made it to the having clause 
     */
    @Test public void testAggregateCriteriaOverUnSortedLimit() {
        String sql = "select a from (SELECT MAX(e2) as a FROM pm1.g1 GROUP BY e2 /*+ non_strict */ LIMIT 1) x where a = 0"; //$NON-NLS-1$
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_GROUP_BY, true);
        caps.setCapabilitySupport(Capability.QUERY_HAVING, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        String[] expectedSql = new String[] {"SELECT MAX(e2) FROM pm1.g1 GROUP BY e2 HAVING MAX(e2) = 0 LIMIT 1"};//$NON-NLS-1$ 
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, capFinder, expectedSql, true);  

        TestOptimizer.checkNodeTypes(plan, FULL_PUSHDOWN, NODE_TYPES);
    }
    
    @Test public void testSortWithLimitInlineView() {
        String sql = "select e1 from (select pm1.g1.e1, pm1.g1.e2 from pm1.g1 order by pm1.g1.e1, pm1.g1.e2 limit 1) x"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0, g_0.e2"}); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                1,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                0,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testCrossJoinLimit() throws Exception {
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1, pm2.g1 limit 5, 5"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 LIMIT 10", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 LIMIT 10"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                1,      // NestedLoopJoinStrategy
                0,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testCrossJoinLimitNestedTable() throws Exception {
        BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1, TABLE(select pm2.g1.e1 FROM pm2.g1 WHERE pm2.g1.e1 = pm1.g1.e1) as x limit 5, 5"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 WHERE pm2.g1.e1 = pm1.g1.e1 LIMIT 10", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                0,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
    }
    
    /**
     * Note that the limit is not pushed below the select nodes under the join
     */
    @Test public void testEffectivelyCrossJoinLimit() throws Exception {
    	 BasicSourceCapabilities caps = new BasicSourceCapabilities();
         caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
         DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
          
         String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1, pm2.g1 where pm1.g1.e1 = pm2.g1.e1 and pm1.g1.e1 = 2 limit 5"; //$NON-NLS-1$
         
         ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
         
         TestOptimizer.checkNodeTypes(plan, new int[] {
                 2,      // Access
                 0,      // DependentAccess
                 0,      // DependentSelect
                 0,      // DependentProject
                 0,      // DupRemove
                 0,      // Grouping
                 3,      // Limit
                 1,      // NestedLoopJoinStrategy
                 0,      // MergeJoinStrategy
                 0,      // Null
                 0,      // PlanExecution
                 1,      // Project
                 2,      // Select
                 0,      // Sort
                 0       // UnionAll
         }, NODE_TYPES);
    }
    
    @Test public void testOuterJoinLimit() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1 left outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 limit 2"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 LIMIT 2"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
    }
    
    @Test public void testOrderedOuterJoinLimit() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1 left outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 order by pm1.g1.e1 limit 3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                1,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
        
        HardcodedDataManager hdm = new HardcodedDataManager();
        hdm.addData("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3", Arrays.asList("a", 1), Arrays.asList("a", 2), Arrays.asList("c", 3));
        hdm.addData("SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", Arrays.asList("a"), Arrays.asList("a"), Arrays.asList("c"));
        TestProcessor.helpProcess(plan, hdm, new List[] {
        		Arrays.asList("a", 1), //$NON-NLS-1$
        		Arrays.asList("a", 1), //$NON-NLS-1$
        		Arrays.asList("a", 2), //$NON-NLS-1$
        });
    }
    
    @Test public void testOrderedOuterJoinLimit1() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1 full outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 order by pm1.g1.e1 limit 3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                1,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
        
        HardcodedDataManager hdm = new HardcodedDataManager();
        hdm.addData("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3", Arrays.asList(null, 1), Arrays.asList("a", 2), Arrays.asList("c", 3));
        hdm.addData("SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", Arrays.asList((String)null), Arrays.asList("a"), Arrays.asList("c"));
        TestProcessor.helpProcess(plan, hdm, new List[] {
        		Arrays.asList(null, null), //$NON-NLS-1$
        		Arrays.asList(null, 1), //$NON-NLS-1$
        		Arrays.asList("a", 2), //$NON-NLS-1$
        });
    }
    
    /**
     * limit won't be pushed as the ordering is over both sides
     * @throws Exception
     */
    @Test public void testOrderedOuterJoinLimit2() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2 from pm1.g1 left outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 order by pm1.g1.e1, pm2.g1.e1 limit 3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                1,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
    }
    
    /**
     * ensure we can push through multiple joins and handle an offset
     * @throws Exception
     */
    @Test public void testOrderedOuterJoinLimit3() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select pm1.g1.e1, pm1.g1.e2, pm1.g2.e3 from pm1.g1 left outer join pm2.g1 on pm1.g1.e1 = pm2.g1.e1 left outer join pm1.g2 on pm1.g1.e2 = pm1.g2.e2 order by pm1.g1.e4 limit 3,3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e4 FROM pm1.g1 ORDER BY pm1.g1.e4 LIMIT 6", "SELECT pm1.g2.e2, pm1.g2.e3 FROM pm1.g2 ORDER BY pm1.g2.e2"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                3,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                2,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                1,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
        
        HardcodedDataManager hdm = new HardcodedDataManager();
        hdm.addData("SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e4 FROM pm1.g1 ORDER BY pm1.g1.e4 LIMIT 6", Arrays.asList(null, 4, null), Arrays.asList("a", 5, false), Arrays.asList("c", 6, false), Arrays.asList(null, 1, true), Arrays.asList("a", 2, true), Arrays.asList("c", 3, true));
        hdm.addData("SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", Arrays.asList((String)null), Arrays.asList("a"), Arrays.asList("c"));
        hdm.addData("SELECT pm1.g2.e2, pm1.g2.e3 FROM pm1.g2 ORDER BY pm1.g2.e2", Arrays.asList(1, 1.0), Arrays.asList(2, 2.0));
        TestProcessor.helpProcess(plan, hdm, new List[] {
        		Arrays.asList(null, 1, 1.0), //$NON-NLS-1$
        		Arrays.asList("a", 2, 2.0), //$NON-NLS-1$
        		Arrays.asList("c", 3, null), //$NON-NLS-1$
        });
    }
    
    @Test public void testOrderedOuterJoinLimitInlineView() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select u.e1, u.e2 from (select pm1.g1.e1, pm1.g1.e2 from pm1.g1 union all select pm3.g1.e1, pm3.g1.e2 from pm3.g1 )as u left outer join pm2.g1 on u.e1 = pm2.g1.e1 order by u.e1 limit 3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3", "SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1 ORDER BY pm3.g1.e1 LIMIT 3", "SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                3,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                2,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                3,      // Project
                0,      // Select
                2,      // Sort
                1       // UnionAll
        }, NODE_TYPES);
        
        HardcodedDataManager hdm = new HardcodedDataManager();
        hdm.addData("SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1 ORDER BY pm1.g1.e1 LIMIT 3", Arrays.asList(null, 4), Arrays.asList("a", 5), Arrays.asList("c", 6));
        hdm.addData("SELECT pm3.g1.e1, pm3.g1.e2 FROM pm3.g1 ORDER BY pm3.g1.e1 LIMIT 3", Arrays.asList(null, 7), Arrays.asList("a", 8), Arrays.asList("c", 9));
        hdm.addData("SELECT pm2.g1.e1 FROM pm2.g1 ORDER BY pm2.g1.e1", Arrays.asList("a"), Arrays.asList("b"), Arrays.asList("c"));
        TestProcessor.helpProcess(plan, hdm, new List[] {
        		Arrays.asList(null, 4), //$NON-NLS-1$
        		Arrays.asList(null, 7), //$NON-NLS-1$
        		Arrays.asList("a", 5), //$NON-NLS-1$
        });
        
    }
    
    @Test public void testOrderedOuterJoinLimitUnionPushdown() throws Exception {
    	BasicSourceCapabilities caps = new BasicSourceCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
        caps.setCapabilitySupport(Capability.ROW_LIMIT, true);
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, false);
        caps.setCapabilitySupport(Capability.QUERY_SET_ORDER_BY, true);
        caps.setCapabilitySupport(Capability.QUERY_UNION, true);
        caps.setCapabilitySupport(Capability.CRITERIA_ISNULL, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(caps);
         
        String sql = "select u.e1, u.e2, pm2.g1.e2 from (select pm1.g1.e1, pm1.g1.e2 from pm1.g1 union all select pm1.g2.e1, pm1.g2.e2 from pm1.g2 )as u left outer join pm2.g1 on u.e1 = pm2.g1.e1 where u.e2 is null order by u.e1 limit 3"; //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT pm1.g1.e1 AS c_0, pm1.g1.e2 AS c_1 FROM pm1.g1 WHERE pm1.g1.e2 IS NULL UNION ALL SELECT pm1.g2.e1 AS c_0, pm1.g2.e2 AS c_1 FROM pm1.g2 WHERE pm1.g2.e2 IS NULL ORDER BY c_0 LIMIT 3", 
        	"SELECT pm2.g1.e1 AS c_0, pm2.g1.e2 AS c_1 FROM pm2.g1 ORDER BY c_0"}, capFinder, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                1,      // Limit
                0,      // NestedLoopJoinStrategy
                1,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                1,      // Sort
                0       // UnionAll
        }, NODE_TYPES);
        
        HardcodedDataManager hdm = new HardcodedDataManager();
        hdm.addData("SELECT pm1.g1.e1 AS c_0, pm1.g1.e2 AS c_1 FROM pm1.g1 WHERE pm1.g1.e2 IS NULL UNION ALL SELECT pm1.g2.e1 AS c_0, pm1.g2.e2 AS c_1 FROM pm1.g2 WHERE pm1.g2.e2 IS NULL ORDER BY c_0 LIMIT 3", Arrays.asList(null, null), Arrays.asList("a", null), Arrays.asList("c", null));
        hdm.addData("SELECT pm2.g1.e1 AS c_0, pm2.g1.e2 AS c_1 FROM pm2.g1 ORDER BY c_0", Arrays.asList(null, 0), Arrays.asList("a", 1), Arrays.asList("e", 2));
        TestProcessor.helpProcess(plan, hdm, new List[] {
        		Arrays.asList(null, null, null), //$NON-NLS-1$
        		Arrays.asList("a", null, 1), //$NON-NLS-1$
        		Arrays.asList("c", null, null), //$NON-NLS-1$
        });
        
    }
    
    @Test public void testPushSortOverAliases() throws Exception {
		String sql = "select column_a, column_b from (select sum(column_a) over (partition by key_column) as column_a, key_column from a ) a left outer join ( "
				+ " select sum(column_b) over (partition by key_column) as column_b, key_column from b) b on a.key_column = b.key_column order by column_a desc limit 10";
		
		
		TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table a (column_a integer, key_column string primary key);"
				+ " create foreign table b (column_b integer, key_column string primary key);", "x", "y");
		
		ProcessorPlan plan = TestOptimizer.helpPlan(sql, tm, new String[] {"SELECT g_0.key_column, g_0.column_b FROM y.b AS g_0", "SELECT g_0.key_column, g_0.column_a FROM y.a AS g_0"}, TestOptimizer.getGenericFinder(false), ComparisonMode.EXACT_COMMAND_STRING);
			   	
		HardcodedDataManager hdm = new HardcodedDataManager();
		hdm.addData("SELECT g_0.key_column, g_0.column_a FROM y.a AS g_0", Arrays.asList("a", 1));
		hdm.addData("SELECT g_0.key_column, g_0.column_b FROM y.b AS g_0", Arrays.asList("a", 1));
		TestProcessor.helpProcess(plan, hdm, new List[] {Arrays.asList(1l, 1l)});
	}

}
