/*
 * 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.processor;

import static org.junit.Assert.*;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import org.junit.Test;
import org.teiid.common.buffer.BufferManager;
import org.teiid.common.buffer.BufferManagerFactory;
import org.teiid.common.buffer.TupleBuffer;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidException;
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.QueryMetadataInterface;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.optimizer.TestOptimizer;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.unittest.TimestampUtil;
import org.teiid.query.util.CommandContext;
import org.teiid.query.validator.TestValidator;

@SuppressWarnings("nls")
public class TestVirtualDepJoin {
    
    /** 
     * @param usAcctsElem
     * @param is
     * @param is2
     * @param object
     * @param object2
     * @since 4.3
     */
    private static void setStats(List<Column> elementObjects,
                                 int[] ndvs,
                                 int[] nnvs,
                                 String[] mins,
                                 String[] maxs) {
        for (int i = 0; i < elementObjects.size(); i++) {
        	Column obj = elementObjects.get(i);
            if(ndvs != null) {
                obj.setDistinctValues(ndvs[i]);
            }
            if(nnvs != null) {
                obj.setNullValues(nnvs[i]);
            }
            if(mins != null) {
                obj.setMinimumValue(mins[i]);
            }
            if(maxs != null) {
                obj.setMaximumValue(maxs[i]);
            }
        }
    }
    
    public static TransformationMetadata exampleVirtualDepJoin() {
    	MetadataStore metadataStore = new MetadataStore();
        Schema us = RealMetadataFactory.createPhysicalModel("US", metadataStore); //$NON-NLS-1$
        Table usAccts = RealMetadataFactory.createPhysicalGroup("Accounts", us); //$NON-NLS-1$
        usAccts.setCardinality(1000000);
        List<Column> usAcctsElem = RealMetadataFactory.createElements(usAccts, 
                                    new String[] { "customer", "account", "txn", "txnid", "pennies" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
                                    new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER });
        setStats(usAcctsElem,
                 new int[] { 1000, 1250, 4, 1000000, 800000}, // NDV per column
                 new int[] { 0, 0, 0, 0, 0}, // NNV per column
                 new String[] {"0", null, null, null, "-10"}, // min per column - use defaults //$NON-NLS-1$ //$NON-NLS-2$
                 new String[] {"1000", null, null, null, "-5"}  // max per column - use defaults //$NON-NLS-1$ //$NON-NLS-2$
        );

        Schema europe = RealMetadataFactory.createPhysicalModel("Europe", metadataStore); //$NON-NLS-1$
        Table euAccts = RealMetadataFactory.createPhysicalGroup("CustAccts", europe); //$NON-NLS-1$
        euAccts.setCardinality(1000000);
        List<Column> euAcctsElem = RealMetadataFactory.createElements(euAccts, 
                                    new String[] { "id", "accid", "type", "amount" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
                                    new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.SHORT, DataTypeManager.DefaultDataTypes.BIG_DECIMAL });
        setStats(euAcctsElem,
                 new int[] { 10000, 1000000, 4, 1000000, 750000}, // NDV per column
                 new int[] { 0, 0, 0, 0, 0}, // NNV per column
                 null, // min per column - use defaults
                 null  // max per column - use defaults
        );

        Schema cust = RealMetadataFactory.createPhysicalModel("CustomerMaster", metadataStore); //$NON-NLS-1$
        Table customers = RealMetadataFactory.createPhysicalGroup("Customers", cust); //$NON-NLS-1$
        customers.setCardinality(1000);
        List<Column> customersElem = RealMetadataFactory.createElements(customers, 
                                    new String[] { "id", "first", "last", "birthday" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
                                    new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.DATE });
        setStats(customersElem,
                 new int[] { 1000, 800, 800, 365}, // NDV per column
                 new int[] { 0, 0, 0, 0}, // NNV per column
                 null, // min per column - use defaults
                 null  // max per column - use defaults
        );
        Table locations = RealMetadataFactory.createPhysicalGroup("Locations", cust); //$NON-NLS-1$
        locations.setCardinality(1200);
        List<Column> locationsElem = RealMetadataFactory.createElements(locations, 
                                    new String[] { "id", "location" }, //$NON-NLS-1$ //$NON-NLS-2$ 
                                    new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING });
        setStats(locationsElem,
                 new int[] { 1000, 2}, // NDV per column
                 new int[] { 0, 0, 0, 0}, // NNV per column
                 null, // min per column - use defaults
                 null  // max per column - use defaults
        );

        Schema vAccts = RealMetadataFactory.createVirtualModel("Accounts", metadataStore); //$NON-NLS-1$
        QueryNode accountsPlan = new QueryNode("SELECT customer as customer_id, convert(account, long) as account_id, convert(txnid, long) as transaction_id, case txn when 'DEP' then 1 when 'TFR' then 2 when 'WD' then 3 else -1 end as txn_type, (pennies + convert('0.00', bigdecimal)) / 100 as amount, 'US' as source FROM US.Accounts where txn != 'X'" +  //$NON-NLS-1$
		   "UNION ALL " +  //$NON-NLS-1$
		   "SELECT id, convert(accid / 10000, long), mod(accid, 10000), convert(type, integer), amount, 'EU' from Europe.CustAccts"); //$NON-NLS-1$ 
        Table accounts = RealMetadataFactory.createVirtualGroup("Accounts", vAccts, accountsPlan); //$NON-NLS-1$
        RealMetadataFactory.createElements(accounts, 
                                            new String[] { "customer_id", "account_id", "transaction_id", "txn_type", "amount", "source" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
                                            new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL, DataTypeManager.DefaultDataTypes.STRING });
        
        Schema master = RealMetadataFactory.createVirtualModel("Master", metadataStore); //$NON-NLS-1$
        QueryNode masterPlan = new QueryNode("select id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount from CustomerMaster.Customers c, Accounts.Accounts a where c.id=a.customer_id"); //$NON-NLS-1$ 
        Table transactions = RealMetadataFactory.createVirtualGroup("Transactions", master, masterPlan); //$NON-NLS-1$
        RealMetadataFactory.createElements(transactions, 
                                            new String[] { "CustomerID", "First", "Last", "AccountID", "TransactionID", "TxnCode", "Amount" }, //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
                                            new String[] { DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.LONG, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.BIG_DECIMAL });
        
        return RealMetadataFactory.createTransformationMetadata(metadataStore, "virtualDepJoin");
    }

    @Test public void testVirtualDepJoinNoValues() throws Exception {  
        // Create query  
        String sql = "select first, last, sum(amount) from Europe.CustAccts e join CustomerMaster.Customers c on c.id=e.id where c.first=-9999 group by c.id, first, last"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();

        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);    
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
 
        TestOptimizer.checkDependentJoinCount(plan, 1);
        // Run query 
        TestProcessor.helpProcess(plan, context, dataManager, expected); 

        // Second query *will not be run* as no values were passed and dependent side has always false criteria
        // So, the list should contain only the first query
        assertEquals(3, dataManager.getQueries().size());       
    }  
    
    public void helpTestVirtualDepJoinSourceSelection(boolean setPushdown) throws Exception {  
        // Create query  
        String sql = "select c.id as CustomerID, First, Last, a.account_id as AccountID, transaction_id as TransactionID, txn_type AS TxnCode, Amount, source from (CustomerMaster.Customers c join CustomerMaster.Locations l on c.id=l.id) join Accounts.Accounts a on c.id=a.customer_id and l.location=a.source where c.first='Miles' order by accountid option makenotdep c, l"; //$NON-NLS-1$
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20"), "EU" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00"), "EU" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00"), "US" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00"), "US" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00"), "US" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00"), "US" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00"), "US" }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ 
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();
        
        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_ORDERBY, false);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, false);
        caps.setCapabilitySupport(Capability.CRITERIA_ISNULL, false);
        caps.setCapabilitySupport(Capability.CRITERIA_IN, setPushdown);    
        finder.addCapabilities("US", caps); //$NON-NLS-1$
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
         
        // Check plan contents
        int selectCount = !setPushdown ? 2 : 0;
        int accessCount = setPushdown ? 2 : 4;
        int depAccessCount = 4 - accessCount;
        TestOptimizer.checkNodeTypes(plan, new int[] {
            accessCount,      // Access
            depAccessCount,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            3,      // Project
            selectCount,      // Select
            1,      // Sort
            1       // UnionAll
        });   
                
        // Run query 
        TestProcessor.helpProcess(plan, context, dataManager, expected); 
    }
    
    @Test public void testVirtualDepJoinSourceSelectionPushdown() throws Exception {
        helpTestVirtualDepJoinSourceSelection(true);
    }

    @Test public void testVirtualDepJoinSourceSelectionNoPushdown() throws Exception {
        helpTestVirtualDepJoinSourceSelection(false);
    }

    @Test public void testVirtualDepJoinPartialPushdown() throws Exception {  
        // Create query  
        String sql = "SELECT * from Master.Transactions where last = 'Davis'"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();

        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps1 = TestOptimizer.getTypicalCapabilities();
        BasicSourceCapabilities caps2 = TestOptimizer.getTypicalCapabilities();
        caps2.setCapabilitySupport(Capability.CRITERIA_IN, false);    
        finder.addCapabilities("US", caps1); //$NON-NLS-1$
        finder.addCapabilities("Europe", caps2);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps1);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
 
        TestOptimizer.checkNodeTypes(plan, new int[] {
            2,      // Access
            1,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            3,      // Project
            1,      // Select
            0,      // Sort
            1       // UnionAll
        });   
        
        TestOptimizer.checkDependentJoinCount(plan, 1);        
        // Run query 
        TestProcessor.helpProcess(plan, context, dataManager, expected); 
    }    

    @Test public void testVirtualDepJoinOverAggregates() throws Exception {  
        // Create query  
        String sql = "select first, last, sum(amount) from Europe.CustAccts e join CustomerMaster.Customers c on c.id=e.id where c.first='Miles' group by c.id, first, last"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { "Miles", "Davis", new BigDecimal("1007.20") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();

        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);    
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
 
        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            1,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            2,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });   
        
        TestOptimizer.checkDependentJoinCount(plan, 1);        
        // Run query 
        TestProcessor.helpProcess(plan, context, dataManager, expected); 
        
        List<String> expectedQueries = new ArrayList<String>(6);
        for (int i = 0; i < 3; i++) {
        	expectedQueries.add("SELECT g_0.id AS c_0, g_0.first AS c_1, g_0.last AS c_2 FROM CustomerMaster.Customers AS g_0 WHERE g_0.first = 'Miles' ORDER BY c_0"); //$NON-NLS-1$
        	expectedQueries.add("SELECT g_0.id, g_0.amount FROM Europe.CustAccts AS g_0 WHERE g_0.id = 100"); //$NON-NLS-1$
        }
        
        assertEquals(expectedQueries, dataManager.getQueries());
    }    
    
    @Test public void testVirtualDepJoinSelects() throws Exception {
        helpTestVirtualDepJoin(false);
    }
    
    @Test public void testVirtualDepJoinPushdown() throws Exception {
        helpTestVirtualDepJoin(true);
    }
    
    @Test public void testVirtualDepMultipleDependentBatches() throws Exception {  
        helpTestMultipleBatches(true);
    }
    
    @Test public void testVirtualDepMultipleDependentBatchesNonUnique() throws Exception {  
        helpTestMultipleBatches(false);
    }

    private void helpTestMultipleBatches(boolean unique) throws Exception,
                                          TeiidComponentException,
                                          TeiidException,
                                          SQLException {
        // Create query  
        String sql = "SELECT * from Master.Transactions where last = 'Davis' order by CustomerID, TransactionID"; //$NON-NLS-1$ 
         
        List<List<Object>> expected = new LinkedList<List<Object>>();
        
        
        // Create expected results 
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }            
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        }
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        }
        expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        if (!unique) {
            expected.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        }
        expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(16000), new Long(207), new Integer(3), new BigDecimal("12.34") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(16000), new Long(299), new Integer(3), new BigDecimal("950.34") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(550), new Long(1004), new Integer(3), new BigDecimal("542.20") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", new Long(550), new Long(1005), new Integer(1), new BigDecimal("99.99") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(620), new Long(1006), new Integer(1), new BigDecimal("10000.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(620), new Long(1007), new Integer(2), new BigDecimal("0.75") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
        expected.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", new Long(630), new Long(1008), new Integer(2), new BigDecimal("62.00") })); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager();
        sampleDataVirtualDepJoin(dataManager, metadata); 
        overrideVirtualDepJoinData(dataManager, metadata, unique);
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();

        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setSourceProperty(Capability.MAX_IN_CRITERIA_SIZE, new Integer(1));
        finder.addCapabilities("US", caps); //$NON-NLS-1$
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
 
        // Run query 
        BufferManager bufferMgr = createCustomBufferMgr(2);
        QueryProcessor processor = new QueryProcessor(plan, context, bufferMgr, dataManager);
        processor.setNonBlocking(true);
        BatchCollector collector = processor.createBatchCollector();
        TupleBuffer id = collector.collectTuples();

        TestProcessor.examineResults(expected.toArray(new List[expected.size()]), bufferMgr, id);
    }

    private BufferManager createCustomBufferMgr(int batchSize) {
        return BufferManagerFactory.getTestBufferManager(200000, batchSize);
    }

    public void helpTestVirtualDepJoin(boolean pushCriteria) throws Exception {  
        // Create query  
        String sql = "SELECT * from Master.Transactions where last = 'Davis'"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(123), new Integer(1), new BigDecimal("100.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(127), new Integer(2), new BigDecimal("250.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15000), new Long(128), new Integer(3), new BigDecimal("1000.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(134), new Integer(1), new BigDecimal("10.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(15001), new Long(201), new Integer(1), new BigDecimal("10.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1002), new Integer(1), new BigDecimal("7.20") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                        Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", new Long(540), new Long(1003), new Integer(2), new BigDecimal("1000.00") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.CRITERIA_IN, pushCriteria);    
        finder.addCapabilities("US", caps); //$NON-NLS-1$
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder); 
 
        // Run query 
        CommandContext context = TestProcessor.createCommandContext();
        TestProcessor.helpProcess(plan, context, dataManager, expected); 
    }
    
    private void sampleDataVirtualDepJoin(FakeDataManager dataMgr, QueryMetadataInterface metadata) throws Exception {
        dataMgr.setBlockOnce();
    
        dataMgr.registerTuples(
            metadata,
            "US.Accounts", new List[] { 
				    Arrays.asList(new Object[] { new Long(100), new Integer(15000), "DEP", new Integer(123), new Integer(10000) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(100), new Integer(15000), "TFR", new Integer(127), new Integer(25000) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(100), new Integer(15000), "WD", new Integer(128), new Integer(100000) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(100), new Integer(15001), "DEP", new Integer(134), new Integer(1000) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(100), new Integer(15001), "DEP", new Integer(201), new Integer(1000) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(200), new Integer(16000), "WD", new Integer(207), new Integer(1234) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(200), new Integer(16000), "WD", new Integer(299), new Integer(95034) }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(200), new Integer(16000), "X", new Integer(301), new Integer(5000) }), //$NON-NLS-1$
				    } );    

        dataMgr.registerTuples(
            metadata,
            "Europe.CustAccts", new List[] { 
				    Arrays.asList(new Object[] { new Long(100), new Long(5401002), new Short((short)1), new BigDecimal("7.20") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(100), new Long(5401003), new Short((short)2), new BigDecimal("1000.00") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(200), new Long(5501004), new Short((short)3), new BigDecimal("542.20") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(200), new Long(5501005), new Short((short)1), new BigDecimal("99.99") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(300), new Long(6201006), new Short((short)1), new BigDecimal("10000.00") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(300), new Long(6201007), new Short((short)2), new BigDecimal("0.75") }), //$NON-NLS-1$
				    Arrays.asList(new Object[] { new Long(300), new Long(6301008), new Short((short)2), new BigDecimal("62.00") }), //$NON-NLS-1$
				    } );    

        dataMgr.registerTuples(metadata, "CustomerMaster.Customers", new List[] { 
			       Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", TimestampUtil.createDate(1926, 4, 25) }), //$NON-NLS-1$ //$NON-NLS-2$
			       Arrays.asList(new Object[] { new Long(200), "John", "Coltrane", TimestampUtil.createDate(1926, 8, 23) }), //$NON-NLS-1$ //$NON-NLS-2$
			       Arrays.asList(new Object[] { new Long(300), "Thelonious", "Monk", TimestampUtil.createDate(1917, 9, 10) }), //$NON-NLS-1$ //$NON-NLS-2$
			       } );    

        dataMgr.registerTuples(metadata, "CustomerMaster.Locations", new List[] { 
			       Arrays.asList(new Object[] { new Long(100), "US" }), //$NON-NLS-1$ 
			       Arrays.asList(new Object[] { new Long(100), "EU" }), //$NON-NLS-1$ 
			       Arrays.asList(new Object[] { new Long(200), "US" }), //$NON-NLS-1$ 
			       Arrays.asList(new Object[] { new Long(200), "EU" }), //$NON-NLS-1$ 
			       Arrays.asList(new Object[] { new Long(300), "EU" }), //$NON-NLS-1$ 
			       } );        
    }                    

    private void overrideVirtualDepJoinData(FakeDataManager dataMgr, QueryMetadataInterface metadata, boolean unique) throws Exception {
        // Group CustomerMaster.Customers
        List<List<?>> data = new LinkedList<List<?>>();
        
        data.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", TimestampUtil.createDate(1926, 4, 25) })); //$NON-NLS-1$ //$NON-NLS-2$
        if (!unique) {
            data.add(Arrays.asList(new Object[] { new Long(100), "Miles", "Davis", TimestampUtil.createDate(1926, 4, 25) })); //$NON-NLS-1$ //$NON-NLS-2$
        }
        data.add(Arrays.asList(new Object[] { new Long(200), "CloneA", "Davis", TimestampUtil.createDate(1926, 4, 26) })); //$NON-NLS-1$ //$NON-NLS-2$
        data.add(Arrays.asList(new Object[] { new Long(300), "CloneB", "Davis", TimestampUtil.createDate(1926, 4, 27) })); //$NON-NLS-1$ //$NON-NLS-2$
        data.add(Arrays.asList(new Object[] { new Long(400), "CloneC", "Davis", TimestampUtil.createDate(1926, 4, 28) })); //$NON-NLS-1$ //$NON-NLS-2$
        
        dataMgr.registerTuples(
            metadata,
            "CustomerMaster.Customers", data.toArray(new List[data.size()]));
    }
    
    @Test public void testVirtualAccessVirtualDep() throws Exception {
        String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e0) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$
        
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        finder.addCapabilities("test", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = TestOptimizer.helpPlan(sql, TestValidator.exampleMetadata4(), null, finder, 
                                                    new String[] {"SELECT g_0.e0 AS c_0, convert(g_0.e0, string) AS c_1 FROM test.\"group\" AS g_0 WHERE (g_0.e1 = '1') AND (convert(g_0.e0, string) IN (<dependent values>)) ORDER BY c_1", 
        	"SELECT g_0.e2 AS c_0 FROM test.\"group\" AS g_0 WHERE (g_0.e0 = 1) AND (g_0.e1 = '2') ORDER BY c_0"}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            1,      // 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
        });
    }
    
    /**
     * Here the virtual makenotdep hint causes us to throw an exception 
     *
     */
    @Test public void testVirtualAccessVirtualDep2() {
        String sql = "SELECT a.e0, b.e2 FROM vTest.vGroup a makenotdep inner join vTest.vGroup b on (a.e0 = b.e2 and a.e1 = b.e0) where b.e0=1 and b.e1='2'"; //$NON-NLS-1$
        
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        finder.addCapabilities("test", caps); //$NON-NLS-1$
        
        TestOptimizer.helpPlan(sql, TestValidator.exampleMetadata4(), null, finder, 
                                                    new String[] {}, TestOptimizer.SHOULD_FAIL);
        
    }
    
    /**
     *  same as testVirtualDepJoinOverAggregate, but the makenotdep hint prevents the
     *  dependent join from happening
     */
    @Test public void testVirtualDepJoinOverAggregates2() throws Exception {  
        // Create query  
        String sql = "select first, last, sum(amount) from Europe.CustAccts e makenotdep join CustomerMaster.Customers c on c.id=e.id where c.first='Miles' group by c.id, first, last"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { "Miles", "Davis", new BigDecimal("1007.20") }), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = exampleVirtualDepJoin();        
        FakeDataManager dataManager = new FakeDataManager(); 
        sampleDataVirtualDepJoin(dataManager, metadata); 
         
        // Plan query 
        CommandContext context = TestProcessor.createCommandContext();

        Command command = TestProcessor.helpParse(sql);   
        FakeCapabilitiesFinder finder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, false);    
        finder.addCapabilities("Europe", caps);//$NON-NLS-1$
        finder.addCapabilities("CustomerMaster", caps);//$NON-NLS-1$
        ProcessorPlan plan = TestProcessor.helpGetPlan(command, metadata, finder, context); 
 
        TestOptimizer.checkNodeTypes(plan, new int[] {
            1,      // Access
            1,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            0,      // DupRemove
            2,      // Grouping
            0,      // NestedLoopJoinStrategy
            1,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            0,      // Select
            0,      // Sort
            0       // UnionAll
        });   
        
        TestOptimizer.checkDependentJoinCount(plan, 1);        
        // Run query 
        TestProcessor.helpProcess(plan, context, dataManager, expected);         
    }    

    
    @Test public void testVirtualMakeDepHint() throws Exception {  
        // Create query  
        String sql = "select distinct pm1.g1.e1 from (pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1) makedep inner join pm2.g1 on pm2.g1.e1 = pm1.g1.e1 where pm2.g1.e3 = 1"; //$NON-NLS-1$ 
         
        // Create expected results 
        List[] expected = new List[] {  
                        Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$
                        Arrays.asList(new Object[] { "c" }), //$NON-NLS-1$
                };     

        // Construct data manager with data 
        QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();        
        FakeDataManager dataManager = new FakeDataManager(); 
        TestProcessor.sampleData1(dataManager); 
         
        // Plan query 
        ProcessorPlan plan = TestProcessor.helpGetPlan(sql, metadata); 
 
        TestOptimizer.checkNodeTypes(plan, new int[] {
            3,      // Access
            0,      // DependentAccess
            0,      // DependentSelect
            0,      // DependentProject
            1,      // DupRemove
            0,      // Grouping
            0,      // NestedLoopJoinStrategy
            2,      // MergeJoinStrategy
            0,      // Null
            0,      // PlanExecution
            1,      // Project
            2,      // Select
            0,      // Sort
            0       // UnionAll
        });   
        
        TestOptimizer.checkDependentJoinCount(plan, 1);        
        // Run query 
        TestProcessor.helpProcess(plan, new CommandContext(), dataManager, expected);         
    }    

    
}
