/*
* 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.util.Arrays;
import java.util.List;
import org.junit.Test;
import org.teiid.api.exception.query.QueryValidatorException;
import org.teiid.client.metadata.ParameterInfo;
import org.teiid.common.buffer.TupleSource;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.metadata.ColumnSet;
import org.teiid.metadata.MetadataStore;
import org.teiid.metadata.Procedure;
import org.teiid.metadata.ProcedureParameter;
import org.teiid.metadata.Schema;
import org.teiid.query.mapping.relational.QueryNode;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.optimizer.TestOptimizer;
import org.teiid.query.processor.proc.CreateCursorResultSetInstruction;
import org.teiid.query.processor.proc.ProcedurePlan;
import org.teiid.query.processor.proc.TestProcedureProcessor;
import org.teiid.query.processor.relational.DependentProcedureExecutionNode;
import org.teiid.query.processor.relational.RelationalNode;
import org.teiid.query.processor.relational.RelationalPlan;
import org.teiid.query.sql.lang.Command;
import org.teiid.query.sql.lang.SPParameter;
import org.teiid.query.sql.lang.StoredProcedure;
import org.teiid.query.sql.symbol.Constant;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.query.util.CommandContext;
@SuppressWarnings("nls")
public class TestProcedureRelational {
@Test public void testProcInExistsSubquery() throws Exception {
String sql = "select pm1.g1.e1 from pm1.g1 where exists (select * from (EXEC pm1.vsp9(pm1.g1.e2 + 1)) x where x.e1 = pm1.g1.e1)"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$
Arrays.asList(new Object[] { "a" }), //$NON-NLS-1$
};
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcTableFunction() throws Exception {
String sql = "select param1, param2, e1, e2 from pm1.vsp26 as x, texttable('abc' columns a string) as y where param1=1 and param2='a'"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
};
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcInSelectScalarSubquery() throws Exception {
String sql = "select (EXEC pm1.vsp36(pm1.g1.e2)) from pm1.g1 where pm1.g1.e1 = 'a'"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(0) }),
Arrays.asList(new Object[] { new Integer(6) }),
Arrays.asList(new Object[] { new Integer(0) }),
};
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsTable(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
assertFalse(plan.requiresTransaction(false));
assertTrue(plan.requiresTransaction(true)); //TODO: this should be false as there is only a single execution possible
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
//virtual group with procedure in transformation
@Test public void testAliasedProcAsTable(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 as x where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testAliasedJoin(){
String sql = "select x.param1, x.param2, y.param1, y.param2, x.e1 from pm1.vsp26 as x, pm1.vsp26 as y where x.param1=1 and x.param2='a' and y.param1 = 2 and y.param2 = 'b'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", new Integer(2), "b", "a"}), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testAliasedJoin1(){
String sql = "select x.param1, x.param2, y.param1, y.param2, x.e1 from pm1.vsp26 as x, pm1.vsp26 as y where x.param1=1 and x.param2='a' and y.param1 = x.param1 and y.param2 = x.param2"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", new Integer(1), "a", "a"}), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Will fail due to access pattern validation (missing param2 assignment)
*/
@Test public void testProcAsTable1(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=1"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false);
}
/**
* Will fail since less than does not constitue an input
*/
@Test public void testProcAsTable2(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1<1 and param2='a'"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false);
}
@Test public void testProcAsTable3(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1 in (1,2,3) and param2 in ('a', 'b') order by param1, param2"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
Arrays.asList(new Object[] { new Integer(1), "b", "b", new Integer(2)}), //$NON-NLS-1$ //$NON-NLS-2$
Arrays.asList(new Object[] { new Integer(2), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
Arrays.asList(new Object[] { new Integer(2), "b", "b", new Integer(2)}), //$NON-NLS-1$ //$NON-NLS-2$
Arrays.asList(new Object[] { new Integer(3), "a", "a", new Integer(3)}), //$NON-NLS-1$ //$NON-NLS-2$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Will fail missing param2 assignment
*/
@Test public void testProcAsTable4(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=1 and not(param2 = 'a')"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false);
}
/**
* Will fail missing param2 assignment
*/
@Test public void testProcAsTable5(){
String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=e2 and param2 = 'a'"; //$NON-NLS-1$
TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false);
}
@Test public void testProcAsTableInJoin(){
String sql = "select param1, param2, pm1.vsp26.e2 from pm1.vsp26, pm1.g1 where param1 = pm1.g1.e2 and param2 = pm1.g1.e1 order by param1, param2, e2"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(3)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(3)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(1), "c", new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(2), "b", new Integer(2)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(3), "a", new Integer(3)}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsTableInJoinWithOutJoinPredicate(){
String sql = "select param1, param2, pm1.vsp26.e2, pm1.g1.e2 from pm1.vsp26, pm1.g1 where pm1.vsp26.e2 = pm1.g1.e2 and param1 = pm1.g1.e2 and param2 = pm1.g1.e1 order by param1, param2, pm1.vsp26.e2"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), 0}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(1), "c", new Integer(1), 1}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(2), "b", new Integer(2), 2}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(3), "a", new Integer(3), 3}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsTableInSubquery(){
String sql = "select param1, param2, pm1.vsp26.e2, (select count(e1) from pm1.vsp26 where param1 = 1 and param2 = 'a') x from pm1.vsp26, pm1.g1 where param1 = pm1.g1.e2 and param2 = pm1.g1.e1 order by param1, param2, e2"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(0), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(3), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(0), "a", new Integer(3), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(1), "c", new Integer(1), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(2), "b", new Integer(2), new Integer(1)}), //$NON-NLS-1$
Arrays.asList(new Object[] { new Integer(3), "a", new Integer(3), new Integer(1)}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
private void helpTestProcRelational(String userQuery,
String inputCriteria,
String atomicQuery) {
ProcessorPlan plan = TestOptimizer.helpPlan(userQuery, RealMetadataFactory.example1Cached(),
new String[] {} );
RelationalPlan rplan = (RelationalPlan)plan;
RelationalNode root = rplan.getRootNode();
while (root.getChildren() != null) {
root = root.getChildren()[0];
if (root instanceof DependentProcedureExecutionNode) {
break;
}
}
DependentProcedureExecutionNode dep = (DependentProcedureExecutionNode)root;
assertEquals(inputCriteria, dep.getInputCriteria().toString());
ProcedurePlan pp = (ProcedurePlan)dep.getProcessorPlan();
CreateCursorResultSetInstruction ccrsi = (CreateCursorResultSetInstruction)pp.getOriginalProgram().getInstructionAt(0);
plan = ccrsi.getCommand();
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
TestOptimizer.checkAtomicQueries(new String[] {atomicQuery}, plan);
}
//virtual group with procedure in transaformation
@Test public void testProcInVirtualGroup1() {
String userQuery = "select e1 from pm1.vsp26 where param1=1 and param2='a'"; //$NON-NLS-1$
String inputCriteria = "(pm1.vsp26.param1 = 1) AND (pm1.vsp26.param2 = 'a')"; //$NON-NLS-1$
String atomicQuery = "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0 WHERE (g_0.e2 >= pm1.vsp26.param1) AND (g_0.e1 = pm1.vsp26.param2)"; //$NON-NLS-1$
helpTestProcRelational(userQuery, inputCriteria, atomicQuery);
}
//virtual group with procedure in transformation
@Test public void testCase3403() {
String userQuery = "select e1 from pm1.vsp26 where param1=2 and param2='a' and 'x'='x'"; //$NON-NLS-1$
String inputCriteria = "(pm1.vsp26.param1 = 2) AND (pm1.vsp26.param2 = 'a')"; //$NON-NLS-1$
String atomicQuery = "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0 WHERE (g_0.e2 >= pm1.vsp26.param1) AND (g_0.e1 = pm1.vsp26.param2)"; //$NON-NLS-1$
helpTestProcRelational(userQuery, inputCriteria, atomicQuery);
}
@Test public void testCase3448() {
String userQuery = "select e1 from pm1.vsp26 where (param1=1 and e2=2) and param2='a'"; //$NON-NLS-1$
String inputCriteria = "(pm1.vsp26.param1 = 1) AND (pm1.vsp26.param2 = 'a')"; //$NON-NLS-1$
String atomicQuery = "SELECT g_0.e1, g_0.e2 FROM pm1.g1 AS g_0 WHERE (g_0.e2 >= pm1.vsp26.param1) AND (g_0.e1 = pm1.vsp26.param2)"; //$NON-NLS-1$
helpTestProcRelational(userQuery, inputCriteria, atomicQuery);
}
@Test public void testProcAsVirtualGroup2(){
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup3(){
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup4(){
String sql = "SELECT P.e1 as ve3 FROM pm1.vsp26 as P, pm1.g2 where P.e1=g2.e1 and param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}) //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup5(){
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where param1=1 and param2='a' and e1='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup6(){
String sql = "SELECT P.e1 as ve3 FROM pm1.vsp26 as P, vm1.g1 where P.e1=g1.e1 and param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}) //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup7(){
String sql = "SELECT e1 FROM (SELECT p.e1, param1, param2 FROM pm1.vsp26 as P, vm1.g1 where P.e1=g1.e1) x where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}), //$NON-NLS-1$
Arrays.asList(new Object[] { "a"}) //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup10_Defect20164(){
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where (param1=1 and param2='a') and e1='c'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[0];
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcAsVirtualGroup8(){
String sql = "SELECT P.e1 as ve3, P.e2 as ve4 FROM pm1.vsp26 as P where param1=1 and param2='a' and e2=3"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "a", new Integer(3)}), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
//virtual group with procedure in transformation
@Test public void testProcAsVirtualGroup9(){
String sql = "SELECT P.e2 as ve3, P.e1 as ve4 FROM pm1.vsp47 as P where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "FOO" }), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Relies upon a default value of null for param1
*
* This is marked as defered since it is not desirable to support this behavior for a single default value
*/
public void defer_testProcAsVirtualGroup9a(){
String sql = "SELECT P.e2 as ve3, P.e1 as ve4 FROM pm1.vsp47 as P where param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(2112), "a" }), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Relies upon a default value of null for both parameters
*
* This is marked as defered since it is not desirable to support this behavior for a single default value
*/
public void defer_testProcAsVirtualGroup9b(){
String sql = "SELECT P.e2 as ve3, P.e1 as ve4 FROM pm1.vsp47 as P"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(2112), null })
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* test for defect 22376
*/
@Test public void testParameterPassing() throws Exception {
MetadataStore metadataStore = new MetadataStore();
Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore); //$NON-NLS-1$
ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.rs1", new String[] {"e1"}, new String[] { DataTypeManager.DefaultDataTypes.STRING }); //$NON-NLS-1$ //$NON-NLS-2$
QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN declare string VARIABLES.x = '1'; SELECT e1 FROM v1.vp2 where v1.vp2.in = VARIABLES.x; END"); //$NON-NLS-1$
Procedure vt1 = RealMetadataFactory.createVirtualProcedure("vp1", v1, null, n1); //$NON-NLS-1$
vt1.setResultSet(rs1);
ProcedureParameter p1 = RealMetadataFactory.createParameter("in", ParameterInfo.IN, DataTypeManager.DefaultDataTypes.STRING); //$NON-NLS-1$
QueryNode n2 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN declare string VARIABLES.x; declare string VARIABLES.y; VARIABLES.x = '2'; VARIABLES.y = v1.vp2.in; select VARIABLES.y; end"); //$NON-NLS-1$
Procedure vt2 = RealMetadataFactory.createVirtualProcedure("vp2", v1, Arrays.asList(p1), n2); //$NON-NLS-1$
vt2.setResultSet(RealMetadataFactory.createResultSet("v1.rs1", new String[] {"e1"}, new String[] { DataTypeManager.DefaultDataTypes.STRING }));
String sql = "select * from (exec v1.vp1()) foo"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { "1" }), //$NON-NLS-1$
};
QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
// Construct data manager with data
// Plan query
ProcessorPlan plan = TestProcedureProcessor.getProcedurePlan(sql, metadata);
// Run query
TestProcedureProcessor.helpTestProcess(plan, expected, new FakeDataManager(), metadata);
}
//virtual group with procedure in transformation
@Test public void testCase6395ProcAsVirtualGroup9(){
String sql = "SELECT P.e2 as ve3, P.e1 as ve4 FROM pm1.vsp47 as P where param1=1 and param2='a'"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { new Integer(1), "FOO" }), //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Case 6395 - This test case will now raise a QueryPlannerException. param2 is required
* and not nullable. This case is expected to fail because of 'param2 is null'
*/
@Test public void testProcAsVirtualGroup2WithNull() throws Exception {
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where param1=1 and param2 is null"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
try {
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.doProcess(plan, dataManager, expected, TestProcessor.createCommandContext());
fail("QueryPlannerException was expected."); //$NON-NLS-1$
} catch (QueryValidatorException e) {
assertEquals("TEIID30164 The procedure parameter pm1.vsp26.param2 is not nullable, but is set to null.",e.getMessage()); //$NON-NLS-1$
}
}
/**
* Case 6395 - This case is expected to succeed. param1 and param2 are both required, but nulls
* are acceptable for both.
*/
@Test public void testProcAsVirtualGroup2WithNull2() throws Exception {
String sql = "select * from pm1.vsp47 where param1 is null and param2 is null"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList(new Object[] { null, new Integer(2112), null, null })
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/**
* Case 6395 - This will not throw an exception and the proc will not be invoked.
*/
@Test public void testProcAsVirtualGroup2WithNull3() throws Exception {
String sql = "select e1 from (SELECT * FROM pm1.vsp26 as P where P.e1='a') x where param1=1 and param2 = commandpayload()"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
/*
* The following are tests that were removed from the validator. We are no longer trying to validate a priori whether
* procedure input criteria is valid. This can be addressed later more generally when we do up front validation of
* access patterns and access patterns have a wider range of semantics.
*
@Test public void testProcInVirtualGroupDefect14609_1() throws Exception{
helpValidate("select ve3 from vm1.vgvp1 where ve1=1.1 and ve2='a'", new String[] {"ve1 = 1.1"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testProcInVirtualGroupDefect14609_2() throws Exception{
helpValidate("select ve3 from vm1.vgvp1 where convert(ve1, integer)=1 and ve2='a'", new String[] {"convert(ve1, integer) = 1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testProcInVirtualGroupDefect14609_3() throws Exception{
helpValidate("select ve3 from vm1.vgvp1 where 1.1=ve1 and ve2='a'", new String[] {"1.1 = ve1" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testProcInVirtualGroupDefect14609_4() throws Exception{
helpValidate("select ve3 from vm1.vgvp1 where 1=convert(ve1, integer) and ve2='a'", new String[] {"1 = convert(ve1, integer)" }, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$
}
@Test public void testDefect15861() throws Exception{
helpValidate("select ve3 from vm1.vgvp1 where (ve1=1 or ve1=2) and ve2='a'", new String[] {"(ve1 = 1) OR (ve1 = 2)", "ve1 = 2"}, RealMetadataFactory.example1Cached()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
}
@Test public void testProcInVirtualGroup1_Defect20164() {
helpFailProcedure("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') or ve3='c'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup2_Defect20164() {
helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 or ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup3_Defect20164() {
helpFailProcedure("select ve3 from vm1.vgvp2, pm1.g1 where ve1=pm1.g1.e2 and ve2='a'", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup4_Defect20164() {
helpValidate("select ve3 from vm1.vgvp2 where (ve1=1 and ve2='a') and (ve3='a' OR ve3='c')", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup5_Defect20164() {
helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and NOT(ve2='a')", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup6_Defect20164() {
helpValidate("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is null", new String[0], RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}
@Test public void testProcInVirtualGroup7_Defect20164() {
helpFailProcedure("select ve3 from vm1.vgvp2 where ve1=1 and ve2 is not null", RealMetadataFactory.example1Cached()); //$NON-NLS-1$
}*/
/**
* Ensures that dependent procedures are processed 1 at a time so that projected input values
* are set correctly.
*/
@Test public void testIssue119() throws Exception {
MetadataStore metadataStore = new MetadataStore();
Schema v1 = RealMetadataFactory.createVirtualModel("v1", metadataStore); //$NON-NLS-1$
Schema pm1 = RealMetadataFactory.createPhysicalModel("pm1", metadataStore); //$NON-NLS-1$
ProcedureParameter in = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER); //$NON-NLS-1$
ColumnSet<Procedure> rs1 = RealMetadataFactory.createResultSet("v1.vp1.rs1", new String[] {"e1", "e2", "e3", "e4", "e5"}, new String[] { DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
QueryNode n1 = new QueryNode("CREATE VIRTUAL PROCEDURE BEGIN SELECT vp1.in1 e1, x.in1 e2, x.e1 e3, y.in1 e4, y.e1 e5 FROM pm1.sp119 x, pm1.sp119 y where x.in1 = vp1.in1 and y.in1 = x.e1; END"); //$NON-NLS-1$
Procedure vt1 = RealMetadataFactory.createVirtualProcedure("vp1", v1, Arrays.asList(in), n1); //$NON-NLS-1$
vt1.setResultSet(rs1);
ProcedureParameter in1 = RealMetadataFactory.createParameter("in1", SPParameter.IN, DataTypeManager.DefaultDataTypes.INTEGER); //$NON-NLS-1$
ColumnSet<Procedure> rs3 = RealMetadataFactory.createResultSet("pm1.sp119.rs1", new String[] { "e1" }, new String[] { DataTypeManager.DefaultDataTypes.INTEGER }); //$NON-NLS-1$ //$NON-NLS-2$
Procedure sp1 = RealMetadataFactory.createStoredProcedure("sp119", pm1, Arrays.asList(in1)); //$NON-NLS-1$
sp1.setResultSet(rs3);
String sql = "select * from (exec v1.vp1(1)) foo order by e4, e5"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList(1, 1, 3, 3, 5),
Arrays.asList(1, 1, 3, 3, 8),
Arrays.asList(1, 1, 6, 6, 8),
Arrays.asList(1, 1, 6, 6, 11),
};
QueryMetadataInterface metadata = RealMetadataFactory.createTransformationMetadata(metadataStore, "foo");
// Construct data manager with data
// Plan query
ProcessorPlan plan = TestProcedureProcessor.getProcedurePlan(sql, metadata);
// Run query
HardcodedDataManager dataManager = new HardcodedDataManager() {
@Override
public TupleSource registerRequest(CommandContext context,
Command command, String modelName,
RegisterRequestParameter parameterObject)
throws TeiidComponentException {
if (command instanceof StoredProcedure) {
StoredProcedure proc = (StoredProcedure)command;
List<SPParameter> params = proc.getInputParameters();
assertEquals(1, params.size());
int value = (Integer)((Constant)params.get(0).getExpression()).getValue();
return new FakeTupleSource(command.getProjectedSymbols(), new List[] {
Arrays.asList(value+2), Arrays.asList(value+5)
});
}
return super.registerRequest(context, command, modelName,
parameterObject);
}
};
TestProcedureProcessor.helpTestProcess(plan, expected, dataManager, metadata);
}
@Test public void testProcRelationalWithNoInputs() {
String sql = "select e1 from pm1.vsp2 order by e1 desc limit 1"; //$NON-NLS-1$
// Create expected results
List<?>[] expected = new List[] {
Arrays.asList("c") //$NON-NLS-1$
};
// Construct data manager with data
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
// Plan query
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
// Run query
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testProcRelationalWithNoInputs1() {
String sql = "select e1 from pm1.sp1"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList("c") //$NON-NLS-1$
};
HardcodedDataManager dataManager = new HardcodedDataManager();
dataManager.addData("EXEC pm1.sp1()", expected);
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.createTransformationMetadata(RealMetadataFactory.example1Store(), "e1"));
TestProcessor.helpProcess(plan, dataManager, expected);
}
@Test public void testNestedJoin(){
String sql = "select y.e1 from pm1.g1 join pm1.vsp26 as x on x.param1=1 and x.param2=pm1.g1.e1 join pm1.vsp26 as y on y.param1 = pm1.g1.e2 and y.param2 = x.e1"; //$NON-NLS-1$
List<?>[] expected = new List[] {
Arrays.asList("a"),Arrays.asList("a"),Arrays.asList("a"),Arrays.asList("a"),Arrays.asList("a"),Arrays.asList("a"),Arrays.asList("c"),Arrays.asList("b"),Arrays.asList("a"), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
};
FakeDataManager dataManager = new FakeDataManager();
TestProcessor.sampleData1(dataManager);
ProcessorPlan plan = TestProcessor.helpGetPlan(sql, RealMetadataFactory.example1Cached());
TestProcessor.helpProcess(plan, dataManager, expected);
}
}