/*
* JBoss, Home of Professional Open Source.
* See the COPYRIGHT.txt file distributed with this work for information
* regarding copyright ownership. Some portions may be licensed
* to Red Hat, Inc. under one or more contributor license agreements.
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
* 02110-1301 USA.
*/
package org.teiid.query.optimizer;
import org.junit.Ignore;
import org.junit.Test;
import org.teiid.query.metadata.TempMetadataAdapter;
import org.teiid.query.metadata.TempMetadataStore;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.unittest.FakeMetadataFactory;
public class TestStoredProcedurePlanning {
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 1a
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery1() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq1()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1, e2 FROM pm1.g1" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery2() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq1()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1, e2 FROM pm1.g1" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 1b
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery3() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq2('1')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1, e2 FROM pm1.g1 WHERE e1 = '1'" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery4() {
ProcessorPlan plan = TestOptimizer.helpPlan("select x.e1 from (EXEC pm1.sq1()) as x", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
@Test public void testStoredQuery5() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sp1()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp1()" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
@Test public void testStoredQuery6() {
ProcessorPlan plan = TestOptimizer.helpPlan("select x.e1 from (EXEC pm1.sp1()) as x", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp1()" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery7() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sqsp1()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp1()" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 1c
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery8() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq3('1', 1)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1, e2 FROM pm1.g1 WHERE e1 = '1'", "SELECT e1, e2 FROM pm1.g1 WHERE e2 = 1" }); //$NON-NLS-1$ //$NON-NLS-2$
TestOptimizer.checkNodeTypes(plan, new int[] {
2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
0, // Project
0, // Select
0, // Sort
1 // UnionAll
});
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 5a
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery9() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq4()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] {"SELECT e1, e2 FROM pm1.g1" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 5b
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery10() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq5('1')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1, e2 FROM pm1.g1 WHERE e1 = '1'"}); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 5c
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery11() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq6()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] {"SELECT e1, e2 FROM pm1.g1 WHERE e1 = '1'" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 6a
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery12() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq7()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1 FROM pm1.g1" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 6c
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery13() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq8('1')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 = '1'" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 6b
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery14() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq9('1')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 = '1'" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 6d
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery15() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq10('1', 2)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT e1 FROM pm1.g1 WHERE (e1 = '1') AND (e2 = 2)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
/**
* Test planning stored queries.
*/
@Test public void testStoredQuery16() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sp2(1)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp2(1)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
/**
* Test planning stored queries. GeminiStoredQueryTestPlan - 6d
*/
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery17() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq11(1, 2)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp2(?)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
1, // Select
0, // Sort
0 // UnionAll
});
}
//GeminiStoredQueryTestPlan - 2a, 2b
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery18() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq12('1', 1)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "INSERT INTO pm1.g1 (e1, e2) VALUES ('1', 1)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
//GeminiStoredQueryTestPlan - 2c
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery19() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq13('1')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "INSERT INTO pm1.g1 (e1, e2) VALUES ('1', 2)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
//GeminiStoredQueryTestPlan - 3c
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery20() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq14('1', 2)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "UPDATE pm1.g1 SET e1 = '1' WHERE e2 = 2" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
//GeminiStoredQueryTestPlan - 4b
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery21() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq15('1', 2)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "DELETE FROM pm1.g1 WHERE (e1 = '1') AND (e2 = 2)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery22() {
ProcessorPlan plan = TestOptimizer.helpPlan("select e1 from (EXEC pm1.sq1()) as x where e1='a' union (select e1 from vm1.g2 where e1='b')", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "SELECT g_0.e1 FROM pm1.g1 AS g_0 WHERE g_0.e1 = 'a'", "SELECT g_0.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_0.e1 = 'b') AND (g_1.e1 = 'b')" }); //$NON-NLS-1$ //$NON-NLS-2$
TestOptimizer.checkNodeTypes(plan, new int[] {
2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
1, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
0, // Project
0, // Select
0, // Sort
1 // UnionAll
});
}
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery23() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq16()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "INSERT INTO pm1.g1 (e1, e2) VALUES ('1', 2)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
@Test public void testStoredQuery24() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sp3()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp3()" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, new int[] {
1, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
1, // Project
0, // Select
0, // Sort
0 // UnionAll
});
}
// test implicit type conversion of argument
@Ignore("stored procedure wrapper removal logic has been removed")
@Test public void testStoredQuery25() {
ProcessorPlan plan = TestOptimizer.helpPlan("EXEC pm1.sq15(1, 2)", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "DELETE FROM pm1.g1 WHERE (e1 = '1') AND (e2 = 2)" }); //$NON-NLS-1$
TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);
}
@Test public void testStoredQueryXML1() {
TestOptimizer.helpPlan("EXEC pm1.sq18()", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), new String[] { }); //$NON-NLS-1$
}
/**
* union of two stored procs - case #1466
*/
@Test public void testStoredProc1() {
ProcessorPlan plan = TestOptimizer.helpPlan("SELECT * FROM (EXEC pm1.sp2(1)) AS x UNION ALL SELECT * FROM (EXEC pm1.sp2(2)) AS y", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp2(1)", "EXEC pm1.sp2(2)" }); //$NON-NLS-1$ //$NON-NLS-2$
TestOptimizer.checkNodeTypes(plan, new int[] {
2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
4, // Project
0, // Select
0, // Sort
1 // UnionAll
});
}
/**
* union of stored proc and query - case #1466
*/
@Test public void testStoredProc2() {
ProcessorPlan plan = TestOptimizer.helpPlan("SELECT * FROM (EXEC pm1.sp2(1)) AS x UNION ALL SELECT e1, e2 FROM pm1.g1", new TempMetadataAdapter(FakeMetadataFactory.example1Cached(), new TempMetadataStore()), //$NON-NLS-1$
new String[] { "EXEC pm1.sp2(1)", "SELECT e1, e2 FROM pm1.g1" }); //$NON-NLS-1$ //$NON-NLS-2$
TestOptimizer.checkNodeTypes(plan, new int[] {
2, // Access
0, // DependentAccess
0, // DependentSelect
0, // DependentProject
0, // DupRemove
0, // Grouping
0, // NestedLoopJoinStrategy
0, // MergeJoinStrategy
0, // Null
0, // PlanExecution
2, // Project
0, // Select
0, // Sort
1 // UnionAll
});
}
}