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

Example 46 with DefaultCapabilitiesFinder

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

the class TestSubqueryPushdown method testAggSubqueryAsJoin.

@Test
public void testAggSubqueryAsJoin() throws Exception {
    // $NON-NLS-1$
    String sql = "SELECT INTKEY, LONGNUM FROM BQT1.SMALLA AS A WHERE LONGNUM > (SELECT SUM(LONGNUM) FROM BQT1.SMALLA AS B WHERE A.INTKEY = B.INTKEY) ORDER BY INTKEY";
    TransformationMetadata metadata = RealMetadataFactory.exampleBQT();
    RealMetadataFactory.setCardinality("BQT1.smalla", 1000, metadata);
    HardcodedDataManager dataMgr = new HardcodedDataManager();
    dataMgr.addData("SELECT g_0.LongNum AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0 ORDER BY c_1", Arrays.asList(1l, 1));
    dataMgr.addData("SELECT SUM(g_0.LongNum) AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey ORDER BY c_1", Arrays.asList(1l, 1));
    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.QUERY_ORDERBY, true);
    bsc.setCapabilitySupport(Capability.QUERY_GROUP_BY, true);
    bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true);
    ProcessorPlan pp = TestProcessor.helpGetPlan(sql, metadata, new DefaultCapabilitiesFinder(bsc));
    TestProcessor.helpProcess(pp, dataMgr, new List[] {});
}

Example 47 with DefaultCapabilitiesFinder

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

the class TestSubqueryPushdown method testSubuqeryOn1.

/**
 * Shows the pushdown is inhibited due to lack of support
 */
@Test
public void testSubuqeryOn1() throws Exception {
    BasicSourceCapabilities bsc = getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true);
    bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
    bsc.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, ExecutionFactory.SupportedJoinCriteria.ANY);
    bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
    bsc.setCapabilitySupport(Capability.CRITERIA_EXISTS, true);
    bsc.setCapabilitySupport(Capability.QUERY_FROM_ANSI_JOIN, true);
    // $NON-NLS-1$
    TestOptimizer.helpPlan(// $NON-NLS-1$
    "SELECT 1 FROM bqt1.smalla as Y93 LEFT OUTER JOIN bqt1.smallb as AG5 ON EXISTS (SELECT 'Y' FROM bqt1.mediuma WHERE AG5.intkey = 1 AND Y93.intkey = 1 )", RealMetadataFactory.exampleBQTCached(), null, new DefaultCapabilitiesFinder(bsc), new String[] { "SELECT g_0.IntKey FROM BQT1.SmallA AS g_0", "SELECT g_0.IntKey FROM BQT1.SmallB AS g_0" }, // $NON-NLS-1$
    ComparisonMode.EXACT_COMMAND_STRING);
}

Example 48 with DefaultCapabilitiesFinder

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

the class TestSubqueryPushdown method testPreEvaluationInAggregate1.

@Test
public void testPreEvaluationInAggregate1() throws Exception {
    TransformationMetadata tm = RealMetadataFactory.fromDDL("x", new DDLHolder("my", "CREATE foreign TABLE test_b (b integer, c integer)"), new DDLHolder("pg", "CREATE foreign TABLE test_a (a integer, b integer); CREATE foreign TABLE test_only_pg (a integer, b integer);"));
    String sql = "SELECT SUM(x.b - (SELECT a FROM pg.test_only_pg WHERE b = 1)) FROM my.test_b x";
    BasicSourceCapabilities bsc = getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
    bsc.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR_PROJECTION, true);
    bsc.setCapabilitySupport(Capability.QUERY_GROUP_BY, true);
    bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_SUM, true);
    bsc.setFunctionSupport("-", true);
    ProcessorPlan plan = // $NON-NLS-1$
    TestOptimizer.helpPlan(// $NON-NLS-1$
    sql, tm, null, new DefaultCapabilitiesFinder(bsc), new String[] { "SELECT SUM((g_0.b - (SELECT a FROM pg.test_only_pg WHERE b = 1 LIMIT 2))) FROM my.test_b AS g_0" }, // $NON-NLS-1$
    ComparisonMode.EXACT_COMMAND_STRING);
    HardcodedDataManager hdm = new HardcodedDataManager(tm);
    hdm.addData("SELECT g_0.a FROM test_only_pg AS g_0 WHERE g_0.b = 1", Arrays.asList(2));
    hdm.addData("SELECT SUM((g_0.b - 2)) FROM test_b AS g_0", Arrays.asList(Long.valueOf(3)));
    CommandContext cc = TestProcessor.createCommandContext();
    cc.setMetadata(tm);
    TestProcessor.helpProcess(plan, cc, hdm, new List[] { Arrays.asList(Long.valueOf(3)) });
}

Example 49 with DefaultCapabilitiesFinder

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

the class TestAggregatePushdown method testExpressions.

@Test
public void testExpressions() throws Exception {
    String sql = "SELECT stringkey, sum(intnum),count(distinct case when floatnum >= 0 then 1 end)" + " FROM bqt1.smalla WHERE intkey=6 GROUP BY stringkey HAVING sum(intnum)>100 AND count(distinct case when floatnum >= 0 then 1 end)=0";
    BasicSourceCapabilities caps = getAggregateCapabilities();
    caps.setCapabilitySupport(Capability.QUERY_FUNCTIONS_IN_GROUP_BY, true);
    caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_DISTINCT, true);
    // $NON-NLS-1$
    TestOptimizer.helpPlan(// $NON-NLS-1$
    sql, // $NON-NLS-1$
    RealMetadataFactory.exampleBQTCached(), new String[] { "SELECT g_0.StringKey, SUM(g_0.IntNum), COUNT(DISTINCT CASE WHEN g_0.FloatNum >= 0.0 THEN 1 END) FROM BQT1.SmallA AS g_0 WHERE g_0.IntKey = 6 GROUP BY g_0.StringKey HAVING (SUM(g_0.IntNum) > 100) AND (COUNT(DISTINCT CASE WHEN g_0.FloatNum >= 0.0 THEN 1 END) = 0)" }, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING);
}

Example 50 with DefaultCapabilitiesFinder

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

the class TestAggregatePushdown method testHavingWithSubqueryPlacement.

@Test
public void testHavingWithSubqueryPlacement() throws Exception {
    TransformationMetadata metadata = RealMetadataFactory.exampleBQTCached();
    String sql = "SELECT INTKEY, STRINGKEY \n" + "  FROM BQT1.SMALLA AS A \n" + "  WHERE NOT (INTKEY IN (10)) \n" + "  GROUP BY INTKEY, STRINGKEY \n" + "  HAVING INTKEY = (SELECT MIN(STRINGKEY) FROM BQT1.SMALLA AS B WHERE A.INTKEY = B.INTKEY)";
    BasicSourceCapabilities aggregateCapabilities = getAggregateCapabilities();
    aggregateCapabilities.setCapabilitySupport(Capability.QUERY_SUBQUERIES_SCALAR, true);
    aggregateCapabilities.setCapabilitySupport(Capability.QUERY_SUBQUERIES_CORRELATED, true);
    aggregateCapabilities.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
    TestOptimizer.helpPlan(sql, metadata, null, new DefaultCapabilitiesFinder(aggregateCapabilities), new String[] { "SELECT g_0.IntKey, g_0.StringKey FROM BQT1.SmallA AS g_0 WHERE (g_0.IntKey <> 10) AND (convert(g_0.IntKey, string) = (SELECT MIN(g_1.StringKey) FROM BQT1.SmallA AS g_1 WHERE g_1.IntKey = g_0.IntKey)) GROUP BY g_0.IntKey, g_0.StringKey" }, ComparisonMode.EXACT_COMMAND_STRING);
}