/*
 * 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 static org.teiid.query.optimizer.TestOptimizer.*;

import java.sql.Timestamp;
import java.util.Arrays;
import java.util.List;
import java.util.TimeZone;

import org.junit.Test;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.dqp.internal.process.PreparedPlan;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.MetadataStore;
import org.teiid.query.function.FunctionTree;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.optimizer.FakeFunctionMetadataSource;
import org.teiid.query.optimizer.TestOptimizer;
import org.teiid.query.optimizer.TestOptimizer.ComparisonMode;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
import org.teiid.query.optimizer.capabilities.CapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
import org.teiid.query.parser.TestDDLParser;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.unittest.RealMetadataFactory.DDLHolder;
import org.teiid.query.unittest.TimestampUtil;
import org.teiid.query.util.CommandContext;
import org.teiid.translator.ExecutionFactory;
import org.teiid.translator.SourceSystemFunctions;

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

	@Test public void testMustPushdownOverMultipleSourcesWithoutSupport() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource()));
		
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$
        
        String sql = "select func(x.e1) from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2"; //$NON-NLS-1$
        
        helpPlan(sql, metadata, null, capFinder, 
                                      new String[] {}, ComparisonMode.FAILED_PLANNING); //$NON-NLS-1$ 
	}

	@Test public void testMustPushdownOverMultipleSources() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource()));
		
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("misc.namespace.func", true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$
        
        String sql = "select func(x.e1) from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, 
                                      new String[] {"SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0", "SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0", new List[] {Arrays.asList(1, "a")});
        dataManager.addData("SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0", new List[] {Arrays.asList(1), Arrays.asList(2)});
        CommandContext cc = TestProcessor.createCommandContext();
        cc.setMetadata(metadata);
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList("a")});
	}
	
	@Test public void testSimpleFunctionPushdown() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign function func (param integer) returns integer; create foreign table g1 (e1 integer)", "x", "y");
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.SELECT_WITHOUT_FROM, true);
		bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, false);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setQueryProcessorFactory(new QueryProcessor.ProcessorFactory() {
			
			@Override
			public PreparedPlan getPreparedPlan(String query, String recursionGroup,
					CommandContext commandContext, QueryMetadataInterface metadata)
					throws TeiidProcessingException, TeiidComponentException {
				return null;
			}
			
			@Override
			public CapabilitiesFinder getCapabiltiesFinder() {
				return capFinder;
			}
			
			@Override
			public QueryProcessor createQueryProcessor(String query,
					String recursionGroup, CommandContext commandContext,
					Object... params) throws TeiidProcessingException,
					TeiidComponentException {
				// TODO Auto-generated method stub
				return null;
			}
		});
        cc.setMetadata(tm);
		
		String sql = "select func(1)"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT func(1)", new List[] {Arrays.asList(2)});
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(2)});
        
        //ensure that pseudo-correlation works
        sql = "select func(0) from g1 where func(e1) = 2"; //$NON-NLS-1$
        
        plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT y.g1.e1 FROM y.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT y.g1.e1 FROM y.g1", new List[] {Arrays.asList(1), Arrays.asList(2)});
        dataManager.addData("SELECT func(0)", new List[] {Arrays.asList(1)});
        dataManager.addData("SELECT func(1)", new List[] {Arrays.asList(2)});
        dataManager.addData("SELECT func(2)", new List[] {Arrays.asList(3)});
        
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(1)});
        
        bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        
        //ensure that pseudo-correlation works
        sql = "select case when hasrole('x') then func(0) else 2 end from g1"; //$NON-NLS-1$
        
        plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT func(0) FROM y.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT func(0) FROM g1", new List[] {Arrays.asList(1), Arrays.asList(1)});
        
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(1), Arrays.asList(1)});
	}
	
	@Test public void testSimpleFunctionPushdown1() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource()));
		
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.SELECT_WITHOUT_FROM, true);
		bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, false);
		bsc.setFunctionSupport("parseDate_", true);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setQueryProcessorFactory(new QueryProcessor.ProcessorFactory() {
			
			@Override
			public PreparedPlan getPreparedPlan(String query, String recursionGroup,
					CommandContext commandContext, QueryMetadataInterface metadata)
					throws TeiidProcessingException, TeiidComponentException {
				return null;
			}
			
			@Override
			public CapabilitiesFinder getCapabiltiesFinder() {
				return capFinder;
			}
			
			@Override
			public QueryProcessor createQueryProcessor(String query,
					String recursionGroup, CommandContext commandContext,
					Object... params) throws TeiidProcessingException,
					TeiidComponentException {
				// TODO Auto-generated method stub
				return null;
			}
		});
        cc.setMetadata(tm);
		
		String sql = "select parseDate_('2011-11-11')"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT parsedate_('2011-11-11')", new List[] {Arrays.asList(TimestampUtil.createDate(0, 0, 0))});
        cc.setDQPWorkContext(RealMetadataFactory.buildWorkContext(tm));
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(TimestampUtil.createDate(0, 0, 0))});
        
		sql = "select misc.namespace.func('2011-11-11')"; //$NON-NLS-1$
        
        plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT parseDate_('2011-11-11')", new List[] {Arrays.asList(TimestampUtil.createDate(0, 0, 0))});
        try {
        	TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(TimestampUtil.createDate(0, 0, 0))});
        	fail();
        } catch (TeiidProcessingException e) {
        	//not supported by any source
        }
	}
	
	@Test public void testSimpleFunctionPushdown2() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.fromDDL("x", new DDLHolder("y", "CREATE FOREIGN FUNCTION func(a object, b object) RETURNS string;"), 
				new DDLHolder("z", "CREATE FOREIGN FUNCTION func1(a object, b object) RETURNS string; create foreign table g1 (e1 object)"));
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.SELECT_WITHOUT_FROM, true);
		bsc.setCapabilitySupport(Capability.CRITERIA_COMPARE_EQ, true);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setQueryProcessorFactory(new QueryProcessor.ProcessorFactory() {
			
			@Override
			public PreparedPlan getPreparedPlan(String query, String recursionGroup,
					CommandContext commandContext, QueryMetadataInterface metadata)
					throws TeiidProcessingException, TeiidComponentException {
				return null;
			}
			
			@Override
			public CapabilitiesFinder getCapabiltiesFinder() {
				return capFinder;
			}
			
			@Override
			public QueryProcessor createQueryProcessor(String query,
					String recursionGroup, CommandContext commandContext,
					Object... params) throws TeiidProcessingException,
					TeiidComponentException {
				// TODO Auto-generated method stub
				return null;
			}
		});
        cc.setMetadata(tm);
		
        String sql = "select e1 from g1 where func(1, 1) = '2'"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT z.g1.e1 FROM z.g1 WHERE func(1, 1) = '2'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        HardcodedDataManager dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT func(1, 1)", new List[] {Arrays.asList("hello world")});
    	TestProcessor.helpProcess(plan, cc, dataManager, new List[] {});
    	
    	sql = "select e1 from g1 where func1(1, 1) = '2'"; //$NON-NLS-1$
        
        plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT z.g1.e1 FROM z.g1 WHERE func1(1, 1) = '2'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT g1.e1 FROM g1 WHERE func1(1, 1) = '2'", new List[] {Arrays.asList("hello world")});
    	TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList("hello world")});
	}

	@Test public void testMustPushdownOverMultipleSourcesWithView() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource()));
		
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("misc.namespace.func", true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$
        
        String sql = "select func(x.e1) from (select x.* from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2 order by e1 limit 10) as x"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, 
                                      new String[] {"SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0", "SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1, g_0.e1 AS c_2 FROM pm1.g1 AS g_0 ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0", new List[] {Arrays.asList(1)});
        dataManager.addData("SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1, g_0.e1 AS c_2 FROM pm1.g1 AS g_0 ORDER BY c_0", new List[] {Arrays.asList(1, "aa", "a"), Arrays.asList(2, "bb", "b")});
        CommandContext cc = TestProcessor.createCommandContext();
        cc.setMetadata(metadata);
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList("aa")});
	}
	
	@Test public void testMustPushdownOverMultipleSourcesWithViewDupRemoval() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Cached().getMetadataStore(), "example1", new FunctionTree("foo", new FakeFunctionMetadataSource()));
		
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("misc.namespace.func", true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("pm2", caps); //$NON-NLS-1$
        
        String sql = "select func(x.e1) from (select distinct x.* from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2 order by e1 limit 10) as x"; //$NON-NLS-1$
        
        helpPlan(sql, metadata, null, capFinder, 
                                      new String[] {}, ComparisonMode.FAILED_PLANNING); //$NON-NLS-1$ 
	}
	
	@Test public void testDDLMetadata() throws Exception {
		String ddl = "CREATE VIRTUAL FUNCTION SourceFunc(msg varchar) RETURNS varchar " +
				"OPTIONS(CATEGORY 'misc', DETERMINISM 'DETERMINISTIC', " +
				"\"NULL-ON-NULL\" 'true', JAVA_CLASS '"+TestFunctionPushdown.class.getName()+"', JAVA_METHOD 'sourceFunc');" +
				"CREATE VIEW X (Y varchar) as SELECT e1 from pm1.g1;";

		MetadataFactory mf = TestDDLParser.helpParse(ddl, "model");
		mf.getSchema().setPhysical(false);
		MetadataStore ms = mf.asMetadataStore();
		ms.merge(RealMetadataFactory.example1Cached().getMetadataStore());
		
		QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(ms, "example1");
		
		FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport("model.SourceFunc", true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        helpPlan("select sourceFunc(y) from x", metadata, null, capFinder, 
                new String[] {"SELECT sourceFunc(g_0.e1) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$         

        caps.setFunctionSupport("model.SourceFunc", false);
        
        helpPlan("select sourceFunc(y) from x", metadata, null, capFinder, 
                new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$         
	}
	
	@Test public void testDDLMetadata1() throws Exception {
		String ddl = "CREATE foreign FUNCTION sourceFunc(msg varchar) RETURNS varchar options (nameinsource 'a.sourcefunc'); " +
		              "CREATE foreign FUNCTION b.sourceFunc(msg varchar) RETURNS varchar; " +
				"CREATE foreign table X (Y varchar);";

		QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy");
		
		FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        capFinder.addCapabilities("phy", caps); //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan("SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0", metadata, null, capFinder, 
                new String[] {"SELECT sourceFunc(g_0.Y), phy.b.sourceFunc(g_0.Y) FROM phy.X AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        
        //ensure that the source query contains the function schemas
        HardcodedDataManager dm = new HardcodedDataManager(metadata);
        dm.addData("SELECT a.sourcefunc(g_0.Y), b.sourceFunc(g_0.Y) FROM X AS g_0", new List[0]);
        CommandContext cc = TestProcessor.createCommandContext();
        cc.setMetadata(metadata);
        TestProcessor.helpProcess(plan, cc, dm, new List[0]);
	}
	
	@Test public void testDDLMetadataNameConflict() throws Exception {
		String ddl = "CREATE foreign FUNCTION \"convert\"(msg integer, type varchar) RETURNS varchar; " +
				"CREATE foreign table X (Y integer);";

		QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy");
		
		FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        capFinder.addCapabilities("phy", caps); //$NON-NLS-1$
        
        helpPlan("select phy.convert(y, 'z') from x", metadata, null, capFinder, 
                new String[] {"SELECT phy.convert(g_0.Y, 'z') FROM phy.X AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
	}
	
	@Test public void testConcat2() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
		
        FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, true);
        capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
        
        String sql = "select concat2(x.e1, x.e1) from pm1.g1 as x"; //$NON-NLS-1$
        
        helpPlan(sql, metadata, null, capFinder, 
                                      new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        //cannot pushdown
        caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, false);
        
        ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, 
                new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        HardcodedDataManager dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT g_0.e1 FROM pm1.g1 AS g_0", new List[] {Arrays.asList("a"), Arrays.asList((String)null)});
        TestProcessor.helpProcess(plan, dataManager, new List[] {Arrays.asList("aa"), Arrays.asList((String)null)});
        
        caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true);
        caps.setFunctionSupport(SourceSystemFunctions.IFNULL, true);
        caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
        
        //will get replaced in the LanguageBridgeFactory
        helpPlan(sql, metadata, null, capFinder, 
                new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
	}
	
	@Test
	public void testFromUnitTime() throws Exception {
	    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
	    
	    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
	    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
	    capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$
	    
	    String sql = "SELECT from_unixtime(x.e2) from pm1.g1 as x"; //$NON-NLS-1$
	    
	    // can pushdown
	    String expected = "SELECT from_unixtime(g_0.e2) FROM pm1.g1 AS g_0"; //$NON-NLS-1$ 
	    caps.setFunctionSupport(SourceSystemFunctions.FROM_UNIXTIME, true);
	    helpPlan(sql, metadata, null, capFinder, new String[] {expected}, ComparisonMode.EXACT_COMMAND_STRING);
	    
	    // can not pushdown
	    expected = "SELECT g_0.e2 FROM pm1.g1 AS g_0"; //$NON-NLS-1$
	    caps.setFunctionSupport(SourceSystemFunctions.FROM_UNIXTIME, false);
	    ProcessorPlan plan = helpPlan(sql, metadata, null, capFinder, new String[] {expected}, ComparisonMode.EXACT_COMMAND_STRING);
	    HardcodedDataManager dataManager = new HardcodedDataManager();
	    dataManager.addData(expected, new List[] {Arrays.asList(1500000000)});
	    TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("GMT-06:00")); //$NON-NLS-1$
	    try {
            TestProcessor.helpProcess(plan, dataManager, new List[] {Arrays.asList(Timestamp.valueOf("2017-07-13 20:40:00.0"))}); //$NON-NLS-1$
        } finally {
            TimestampWithTimezone.resetCalendar(null);
        }
	    
	    // will get replaced in the LanguageBridgeFactory
	    expected = "SELECT from_unixtime(g_0.e2) FROM pm1.g1 AS g_0"; //$NON-NLS-1$
	    caps.setFunctionSupport(SourceSystemFunctions.TIMESTAMPADD, true);
	    helpPlan(sql, metadata, null, capFinder, new String[] {expected}, ComparisonMode.EXACT_COMMAND_STRING);
	}
	
	@Test public void testPartialProjectPushdown() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
		
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
        
        ProcessorPlan plan = helpPlan("select case when e1 = 1 then 1 else 0 end, e2 + e4 from pm1.g1", metadata, null, new DefaultCapabilitiesFinder(caps), 
                new String[] {"SELECT CASE WHEN g_0.e1 = '1' THEN 1 ELSE 0 END, g_0.e2, g_0.e4 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        HardcodedDataManager dm = new HardcodedDataManager(metadata);
        dm.addData("SELECT CASE WHEN g_0.e1 = '1' THEN 1 ELSE 0 END, g_0.e2, g_0.e4 FROM g1 AS g_0", new List[] {Arrays.asList(1, 2, 3.1)});
        TestProcessor.helpProcess(plan, dm, new List[] {Arrays.asList(1, 5.1)});
	}
	
	@Test public void testMustPushdownOverGrouping() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign function func (param integer) returns integer; create foreign table g1 (e1 integer)", "x", "y");
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.SELECT_WITHOUT_FROM, true);
		bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setQueryProcessorFactory(new QueryProcessor.ProcessorFactory() {
			
			@Override
			public PreparedPlan getPreparedPlan(String query, String recursionGroup,
					CommandContext commandContext, QueryMetadataInterface metadata)
					throws TeiidProcessingException, TeiidComponentException {
				return null;
			}
			
			@Override
			public CapabilitiesFinder getCapabiltiesFinder() {
				return capFinder;
			}
			
			@Override
			public QueryProcessor createQueryProcessor(String query,
					String recursionGroup, CommandContext commandContext,
					Object... params) throws TeiidProcessingException,
					TeiidComponentException {
				// TODO Auto-generated method stub
				return null;
			}
		});
        cc.setMetadata(tm);
		
        String sql = "select func(e1) from g1 group by e1"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT y.g1.e1 FROM y.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT y.g1.e1 FROM y.g1", new List[] {Arrays.asList(1), Arrays.asList(2)});
        dataManager.addData("SELECT func(1)", new List[] {Arrays.asList(2)});
        dataManager.addData("SELECT func(2)", new List[] {Arrays.asList(3)});
        
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(2), Arrays.asList(3)});
	}
	
	@Test public void testMustPushdownSubexpressionOverGrouping() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign function func (param integer) returns integer; create foreign table g1 (e1 integer, e2 integer)", "x", "y");
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.SELECT_WITHOUT_FROM, true);
		bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setQueryProcessorFactory(new QueryProcessor.ProcessorFactory() {
			
			@Override
			public PreparedPlan getPreparedPlan(String query, String recursionGroup,
					CommandContext commandContext, QueryMetadataInterface metadata)
					throws TeiidProcessingException, TeiidComponentException {
				return null;
			}
			
			@Override
			public CapabilitiesFinder getCapabiltiesFinder() {
				return capFinder;
			}
			
			@Override
			public QueryProcessor createQueryProcessor(String query,
					String recursionGroup, CommandContext commandContext,
					Object... params) throws TeiidProcessingException,
					TeiidComponentException {
				// TODO Auto-generated method stub
				return null;
			}
		});
        cc.setMetadata(tm);
		
        String sql = "select max(func(e2)) from g1 group by e1"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT y.g1.e1, func(y.g1.e2) FROM y.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager();
        dataManager.addData("SELECT y.g1.e1, func(y.g1.e2) FROM y.g1", new List[] {Arrays.asList(1, 2), Arrays.asList(2, 3)});
        
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList(2), Arrays.asList(3)});
	}
	
	public static String sourceFunc(String msg) {
		return msg;
	}
	
	@Test public void testPartialProjectPushdownCorrelatedSubquery() throws Exception {
		QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();
		
        BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
        caps.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR_PROJECTION, true);
        ProcessorPlan plan = helpPlan("select x.c, case when e1 = 1 then 1 else 0 end, (select e1 from pm1.g1 where pm1.g1.e1 = pm1.g2.e1) from pm1.g2, (select max(e1) as c from pm1.g2) x where x.c = pm1.g2.e1", metadata, null, new DefaultCapabilitiesFinder(caps), 
                new String[] {"SELECT v_0.c_0, g_0.e1, (SELECT g_2.e1 FROM pm1.g1 AS g_2 WHERE g_2.e1 = g_0.e1) FROM pm1.g2 AS g_0, (SELECT MAX(g_1.e1) AS c_0 FROM pm1.g2 AS g_1) AS v_0 WHERE v_0.c_0 = g_0.e1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
        HardcodedDataManager dm = new HardcodedDataManager(metadata);
        dm.addData("SELECT v_0.c_0, g_0.e1, (SELECT g_2.e1 FROM g1 AS g_2 WHERE g_2.e1 = g_0.e1) FROM g2 AS g_0, (SELECT MAX(g_1.e1) AS c_0 FROM g2 AS g_1) AS v_0 WHERE v_0.c_0 = g_0.e1", new List[] {Arrays.asList("a", "a", "a")});
        TestProcessor.helpProcess(plan, dm, new List[] {Arrays.asList("a", 0, "a")});
	}

	@Test public void testMustPushdownSubexpression() throws Exception {
		TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign function func (param integer) returns integer; create foreign table g1 (e1 integer)", "x", "y");
		BasicSourceCapabilities bsc = new BasicSourceCapabilities();
		bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
		final DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
        
		CommandContext cc = TestProcessor.createCommandContext();
        cc.setMetadata(tm);
		
		String sql = "select xmlelement(name x, func(1) + e1) from g1"; //$NON-NLS-1$
        
        ProcessorPlan plan = helpPlan(sql, tm, null, capFinder, 
                                      new String[] {"SELECT func(1), y.g1.e1 FROM y.g1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ 
        
        HardcodedDataManager dataManager = new HardcodedDataManager(tm);
        dataManager.addData("SELECT func(1), g1.e1 FROM g1", new List[] {Arrays.asList(2, 0)});
        TestProcessor.helpProcess(plan, cc, dataManager, new List[] {Arrays.asList("<x>2</x>")});
        
	}
	
    @Test public void testParseFormatNameCase() throws Exception {
    	BasicSourceCapabilities caps = getTypicalCapabilities();
    	caps.setCapabilitySupport(Capability.ONLY_FORMAT_LITERALS, true);
    	caps.setFunctionSupport(SourceSystemFunctions.FORMATTIMESTAMP, true);
    	caps.setTranslator(new ExecutionFactory<Object, Object> () {
    		@Override
    		public boolean supportsFormatLiteral(String literal,
    				org.teiid.translator.ExecutionFactory.Format format) {
    			return literal.equals("yyyy");
    		}
    	});
        ProcessorPlan plan = TestOptimizer.helpPlan("SELECT stringkey from bqt1.smalla where formatTimestamp(timestampvalue, 'yyyy') = '1921' and parsebigdecimal(stringkey, 'yyyy') = 1 and formatTimestamp(timestampvalue, stringkey) = '19'", //$NON-NLS-1$
                                      RealMetadataFactory.exampleBQTCached(), null, new DefaultCapabilitiesFinder(caps),
                                      new String[] {
                                          "SELECT g_0.TimestampValue, g_0.StringKey FROM BQT1.SmallA AS g_0 WHERE formatTimestamp(g_0.TimestampValue, 'yyyy') = '1921'"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$
    
        checkNodeTypes(plan, new int[] {
                1,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                0,      // NestedLoopJoinStrategy
                0,      // MergeJoinStrategy
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                1,      // Select
                0,      // Sort
                0       // UnionAll
            });                                    
    }
    
    @Test public void testSystemNameConflict() throws Exception {
        TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table t (x char); create foreign function chr(x char) returns string", "x", "y");
        
        HardcodedDataManager dataMgr = new HardcodedDataManager();

        String sql = "SELECT chr(x) from t";
        
        BasicSourceCapabilities bsc = new BasicSourceCapabilities();
        bsc.setCapabilitySupport(Capability.QUERY_SELECT_EXPRESSION, true);
        
        dataMgr.addData("SELECT y.chr(y.t.x) FROM y.t", Arrays.asList("a"));
        
        ProcessorPlan plan = TestProcessor.helpGetPlan(sql, tm, new DefaultCapabilitiesFinder(bsc));
        TestProcessor.helpProcess(plan, TestProcessor.createCommandContext(),
                dataMgr, new List<?>[] {Arrays.asList("a")});
    }
	
}
