Examples with DefaultCapabilitiesFinder org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder used on opensource projects

Example 21 with DefaultCapabilitiesFinder

use of org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder in project teiid by teiid.

the class TestJoinOptimization method testLateralProcedurePushdown.

@Test
public void testLateralProcedurePushdown() throws Exception {
    String sql = "select smallb.intkey, x.stringkey, x.intkey " + // $NON-NLS-1$
    "from smallb left outer join lateral (exec spTest5(smallb.intkey)) as x on (true)";
    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.ROW_LIMIT, true);
    bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
    bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_LATERAL, true);
    bsc.setCapabilitySupport(Capability.QUERY_FROM_PROCEDURE_TABLE, true);
    bsc.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
    bsc.setCapabilitySupport(Capability.QUERY_ONLY_FROM_JOIN_LATERAL_PROCEDURE, true);
    TransformationMetadata metadata = RealMetadataFactory.fromDDL("create foreign table smallb (intkey integer, stringkey string); " + "create foreign procedure spTest5 (param integer) returns table(stringkey string, intkey integer)", "x", "y");
    ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, new String[] { "SELECT g_0.intkey, v_0.stringkey, v_0.intkey FROM y.smallb AS g_0 LEFT OUTER JOIN LATERAL(EXEC spTest5(g_0.intkey)) AS v_0 ON 1 = 1" }, new DefaultCapabilitiesFinder(bsc), // $NON-NLS-1$ //$NON-NLS-2$
    ComparisonMode.EXACT_COMMAND_STRING);
    HardcodedDataManager hdm = new HardcodedDataManager(metadata);
    hdm.addData("SELECT g_0.intkey, v_0.stringkey, v_0.intkey FROM smallb AS g_0 LEFT OUTER JOIN LATERAL (EXEC spTest5(g_0.intkey)) AS v_0 ON 1 = 1", Arrays.asList(1, "2", 1));
    TestProcessor.helpProcess(plan, hdm, new List<?>[] { Arrays.asList(1, "2", 1) });
    bsc.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
    // with an extra inline view, should result in same plan - but is not currently as we can't remove the intermediate view without
    // a lot of work
    sql = "SELECT g_0.intkey, v_1.c_0, v_1.c_1 FROM y.smallb AS g_0 LEFT OUTER JOIN LATERAL(SELECT v_0.stringkey AS c_0, v_0.intkey AS c_1 FROM (EXEC spTest5(g_0.intkey)) AS v_0 limit 1) AS v_1 ON 1 = 1";
    plan = TestOptimizer.helpPlan(sql, metadata, new String[] { "SELECT g_0.intkey FROM y.smallb AS g_0", "EXEC spTest5(g_0.intkey)" }, new DefaultCapabilitiesFinder(bsc), // $NON-NLS-1$ //$NON-NLS-2$
    ComparisonMode.EXACT_COMMAND_STRING);
    hdm.addData("SELECT g_0.intkey FROM smallb AS g_0", Arrays.asList(1));
    hdm.addData("EXEC spTest5(1)", Arrays.asList("2", 1));
    TestProcessor.helpProcess(plan, hdm, new List<?>[] { Arrays.asList(1, "2", 1) });
}

Example 22 with DefaultCapabilitiesFinder

use of org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder in project teiid by teiid.

the class TestJoinOptimization method testDetectInvalidSort.

@Test(expected = TeiidComponentException.class)
public void testDetectInvalidSort() throws Exception {
    // $NON-NLS-1$
    String sql = "select * from (with a (x, y, z) as /*+ no_inline */ (select e1, e2, e3 from pm1.g1) SELECT pm1.g2.e2, a.x, z from pm1.g2, a where e1 = x order by x) as x where z = 1";
    FakeDataManager dataManager = new FakeDataManager();
    sampleData1(dataManager);
    // we're allowing the sort to be pushed, but it's not honored by FakeDataManager
    ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, new DefaultCapabilitiesFinder(TestOptimizer.getTypicalCapabilities()), new String[] { "SELECT a.x, a.z FROM a WHERE a.z = TRUE", "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0 WHERE g_0.e1 IN (<dependent values>) ORDER BY c_0" }, ComparisonMode.EXACT_COMMAND_STRING);
    helpProcess(plan, createCommandContext(), dataManager, null);
}

Example 23 with DefaultCapabilitiesFinder

use of org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder in project teiid by teiid.

the class TestJoinOptimization method testInnerOuterOptimization.

@Test
public void testInnerOuterOptimization() throws TeiidComponentException, TeiidProcessingException {
    // $NON-NLS-1$
    String sql = "select g4.*, g3.*, g2.*, g1.* from (pm2.g1 inner join pm1.g2 on (pm2.g1.e1 = pm1.g2.e1) inner join pm2.g3 on (pm1.g2.e2 = pm2.g3.e2)) left outer join pm1.g4 on (pm1.g2.e4 = pm1.g4.e4)";
    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    DefaultCapabilitiesFinder capFinder = new DefaultCapabilitiesFinder(bsc);
    ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e2 AS c_0, g_0.e1 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g3 AS g_0 ORDER BY c_0", "SELECT g_1.e1 AS c_0, g_1.e2 AS c_1, g_0.e1 AS c_2, g_0.e3 AS c_3, g_1.e3 AS c_4, g_1.e4 AS c_5 FROM pm1.g4 AS g_0 LEFT OUTER JOIN pm1.g2 AS g_1 ON g_1.e4 = g_0.e4 ORDER BY c_0", "SELECT g_0.e1 AS c_0, g_0.e2 AS c_1, g_0.e3 AS c_2, g_0.e4 AS c_3 FROM pm2.g1 AS g_0 ORDER BY c_0" }, capFinder, // $NON-NLS-1$ //$NON-NLS-2$
    ComparisonMode.EXACT_COMMAND_STRING);
    TestOptimizer.checkNodeTypes(plan, new int[] { // Access
    3, // DependentAccess
    0, // DependentSelect
    0, // DependentProject
    0, // DupRemove
    0, // Grouping
    0, // Join
    0, // MergeJoin
    2, // Null
    0, // PlanExecution
    0, // Project
    1, // Select
    0, // Sort
    0, // UnionAll
    0 });
}

Example 24 with DefaultCapabilitiesFinder

use of org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder in project teiid by teiid.

the class TestJoinOptimization method testOutputColumnsWithMergeJoinAndNonPushedSelect.

@Test
public void testOutputColumnsWithMergeJoinAndNonPushedSelect() throws TeiidComponentException, TeiidProcessingException {
    String sql = "select bqt1.smalla.intkey, bqt2.smalla.intkey " + // $NON-NLS-1$
    "from bqt1.smalla inner join bqt2.smalla on (bqt2.smalla.intkey = case when bqt1.smalla.intkey = 1 then 2 else 3 end) where right(bqt1.smalla.stringkey, 1) = 'a'";
    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
    // Plan query
    ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] { "SELECT g_0.StringKey AS c_0, g_0.IntKey AS c_1, CASE WHEN g_0.IntKey = 1 THEN 2 ELSE 3 END AS c_2 FROM BQT1.SmallA AS g_0 ORDER BY c_2", "SELECT g_0.IntKey AS c_0 FROM BQT2.SmallA AS g_0 ORDER BY c_0" }, new DefaultCapabilitiesFinder(bsc), // $NON-NLS-1$ //$NON-NLS-2$
    ComparisonMode.EXACT_COMMAND_STRING);
    HardcodedDataManager hdm = new HardcodedDataManager();
    hdm.addData("SELECT g_0.StringKey AS c_0, g_0.IntKey AS c_1, CASE WHEN g_0.IntKey = 1 THEN 2 ELSE 3 END AS c_2 FROM BQT1.SmallA AS g_0 ORDER BY c_2", Arrays.asList("aa", 1, 2));
    hdm.addData("SELECT g_0.IntKey AS c_0 FROM BQT2.SmallA AS g_0 ORDER BY c_0", Arrays.asList(1));
    TestProcessor.helpProcess(plan, hdm, new List<?>[] {});
}

Example 25 with DefaultCapabilitiesFinder

use of org.teiid.query.optimizer.capabilities.DefaultCapabilitiesFinder in project teiid by teiid.

the class TestJoinOptimization method testLeftOuterAssocitivtyLeftLinearSwapNested.

@Test
public void testLeftOuterAssocitivtyLeftLinearSwapNested() throws Exception {
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
    ProcessorPlan plan = // $NON-NLS-1$
    TestOptimizer.helpPlan(// $NON-NLS-1$
    "SELECT pm1.g1.e3 from (pm1.g1 left outer join pm2.g2 on pm1.g1.e1 = pm2.g2.e1 left outer join pm1.g3 on pm1.g1.e2 = pm1.g3.e2) inner join pm3.g1 on (pm1.g1.e1 = pm3.g1.e1)", RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", "SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g3 AS g_1 ON g_0.e2 = g_1.e2 ORDER BY c_0", "SELECT g_0.e1 AS c_0 FROM pm3.g1 AS g_0 ORDER BY c_0" }, new DefaultCapabilitiesFinder(caps), // $NON-NLS-1$
    ComparisonMode.EXACT_COMMAND_STRING);
    HardcodedDataManager hdm = new HardcodedDataManager();
    hdm.addData("SELECT g_0.e1 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", Arrays.asList("a"));
    hdm.addData("SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g1 AS g_0 LEFT OUTER JOIN pm1.g3 AS g_1 ON g_0.e2 = g_1.e2 ORDER BY c_0", Arrays.asList("a", true));
    hdm.addData("SELECT g_0.e1 AS c_0 FROM pm3.g1 AS g_0 ORDER BY c_0", Arrays.asList("a"));
    TestProcessor.helpProcess(plan, hdm, new List[] { Arrays.asList(true) });
}