@Test public void testQuerySingleSourceVirtual() { ProcessorPlan plan = helpPlan("SELECT * FROM vm1.g1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1, pm1.g1.e2, pm1.g1.e3, pm1.g1.e4 FROM pm1.g1"} ); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testSubquery2() { ProcessorPlan plan = helpPlan("Select e1, a from (select e1 FROM pm1.g1) AS x, (select e1 as a FROM pm1.g2) AS y WHERE x.e1=y.a", example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1, g_1.e1 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE g_0.e1 = g_1.e1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testSubquery4() { ProcessorPlan plan = helpPlan("Select e1 from (select e1 FROM pm1.g1 WHERE e1 = 'a') AS x", example1(), //$NON-NLS-1$ new String[] { "SELECT e1 FROM pm1.g1 WHERE e1 = 'a'" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushDistinctSort() { ProcessorPlan plan = helpPlan("select distinct e1 from pm3.g1 order by e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT DISTINCT e1 FROM pm3.g1 ORDER BY e1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushDistinctVirtual2() { ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g12", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushDistinctVirtual3() { ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g12 ORDER BY e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushDistinctVirtual6() { ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g13 ORDER BY e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testPushDistinctVirtual9() { ProcessorPlan plan = helpPlan("select DISTINCT * from vm1.g14 ORDER BY e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT DISTINCT pm3.g1.e1, pm3.g1.e2, pm3.g1.e3, pm3.g1.e4 FROM pm3.g1 ORDER BY pm3.g1.e1" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
/** defect 11630 - note that the lookup function is not pushed down, it will actually be evaluated before being sent to the connector */ @Test public void testLookupFunction() { ProcessorPlan plan = helpPlan("SELECT e1 FROM pm1.g2 WHERE LOOKUP('pm1.g1','e1', 'e2', 1) IS NULL", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e1 FROM pm1.g2 WHERE LOOKUP('pm1.g1', 'e1', 'e2', 1) IS NULL" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
/** defect 21965 */ @Test public void testLookupFunctionInSelect() { ProcessorPlan plan = helpPlan("SELECT e1, LOOKUP('pm1.g1','e1', 'e2', 1) FROM pm1.g2", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e1, LOOKUP('pm1.g1','e1', 'e2', 1) FROM pm1.g2" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testVirtualGroup1() { ProcessorPlan plan = helpPlan("select e2 from vm1.g35", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT e2 FROM pm1.g1" } ); //$NON-NLS-1$ checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testOptionalJoinNode1() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g1.e1 FROM pm1.g1, /* optional */ pm1.g2", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g1.e1 FROM pm1.g1"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testOptionalJoinNode7() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g3.e1 FROM /* optional */ (pm1.g1 LEFT OUTER JOIN pm1.g2 on pm1.g1.e1 = pm1.g2.e1) LEFT OUTER JOIN pm1.g3 on pm1.g1.e1 = pm1.g3.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g3.e1 FROM pm1.g3"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testOptionalJoinNode9() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT pm1.g2.e1 FROM pm1.g2, /* optional */ vm1.g1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] {"SELECT pm1.g2.e1 FROM pm1.g2"} ); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testCopyCriteriaCreatesFalseCriteria() { String sql = "select bqt1.smalla.intkey, bqt2.smalla.intkey from bqt1.smalla, bqt2.smalla where bqt1.smalla.stringkey = bqt2.smalla.intkey and bqt2.smalla.intkey = 1 and bqt1.smalla.stringkey = '2'"; //$NON-NLS-1$ // Plan query ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {}); TestOptimizer.checkNodeTypes(plan, TestRuleRaiseNull.FULLY_NULL); }
@Test public void testCopyCriteriaWithOuterJoin7_defect10050(){ ProcessorPlan plan = helpPlan("select pm2.g1.e1, pm2.g2.e1 from pm2.g1 right outer join pm2.g2 on pm2.g2.e1=pm2.g1.e1 where pm2.g2.e1 IN ('a', 'b')", example1(), //$NON-NLS-1$ new String[] { "SELECT pm2.g1.e1, pm2.g2.e1 FROM pm2.g2 LEFT OUTER JOIN pm2.g1 ON pm2.g2.e1 = pm2.g1.e1 AND pm2.g1.e1 IN ('a', 'b') WHERE pm2.g2.e1 IN ('a', 'b')" }); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testAliasCreationWithInlineView() throws TeiidComponentException, TeiidProcessingException { FakeCapabilitiesFinder capFinder = getInliveViewCapabilitiesFinder(); QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); ProcessorPlan plan = helpPlan("select a, b from (select distinct count(intNum) a, count(stringKey), bqt1.smalla.intkey as b from bqt1.smalla group by bqt1.smalla.intkey) q1 order by q1.a", //$NON-NLS-1$ metadata, null, capFinder, new String[] {"SELECT COUNT(g_0.IntNum) AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testSimpleMergeGroupBy7() { QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = TestOptimizer.helpPlan("SELECT distinct x, e1 FROM (SELECT distinct min(e1) as e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z", //$NON-NLS-1$ metadata, null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT DISTINCT MAX(e2) AS x, MIN(e1) FROM pm1.g1 GROUP BY e1"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testInsertQueryExpression() throws Exception { String sql = "insert into pm1.g1 (e1) select e1 from pm1.g2"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = helpPlan(sql, metadata, new String[] {"SELECT g_0.e1 FROM pm1.g2 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //requires a txn, since an non pushdown/iterated insert is used assertTrue(plan.requiresTransaction(false)); checkNodeTypes(plan, new int[] {1}, new Class[] {ProjectIntoNode.class}); }
@Test public void testCopyCriteriaWithFunction3() throws TeiidComponentException, TeiidProcessingException { String sql = "select bqt1.smalla.intkey, bqt1.smallb.intkey from bqt1.smalla, bqt1.smallb where bqt1.smalla.stringkey = bqt1.smallb.intkey and bqt1.smallb.intkey = 1"; //$NON-NLS-1$ BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); // Plan query ProcessorPlan plan = TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey, g_1.IntKey FROM BQT1.SmallA AS g_0, BQT1.SmallB AS g_1 WHERE (g_0.StringKey = '1') AND (g_1.IntKey = 1)"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.THETA); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey FROM BQT1.SmallA AS g_0 WHERE g_0.StringKey = '1'", "SELECT g_0.IntKey FROM BQT1.SmallB AS g_0 WHERE g_0.IntKey = 1"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }