public static ProcessorPlan helpPlan(String sql, QueryMetadataInterface md, String[] expected, CapabilitiesFinder capFinder, ComparisonMode mode) throws TeiidComponentException, TeiidProcessingException { return helpPlan(sql, md, null, capFinder, expected, mode); }
@Test public void testUnionWithAccessPatternFails() { TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1 UNION select pm4.g1.e1 from pm4.g1 where pm4.g1.e2 = 1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, null, null, TestOptimizer.SHOULD_FAIL); }
/** * Access patterns on models that support joins requires that the access patterns are satisfied prior to * RulePlanJoins */ @Test public void testAccessPatternsGroupsInSameModelFails() { TestOptimizer.helpPlan("select pm5.g1.e1 from pm5.g1, pm5.g2 where pm5.g1.e1 = pm5.g2.e1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, null, null, TestOptimizer.SHOULD_FAIL); }
@Test public void testCase6425_4() throws Exception { String sql = "SELECT e1 FROM pm4.g1 WHERE e1 = '1' OR e2 = '2'"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); TestOptimizer.helpPlan(sql, metadata, null, TestOptimizer.ComparisonMode.FAILED_PLANNING); }
/** * See also case 2912. */ @Test public void testCopyCriteriaWithOuterJoin5_defect10050(){ helpPlan("select pm2.g1.e1, pm1.g2.e1, pm2.g3.e1 from ( (pm2.g1 right outer join pm1.g2 on pm2.g1.e1=pm1.g2.e1) right outer join pm2.g3 on pm1.g2.e1=pm2.g3.e1) where pm2.g3.e1 = 'a'", example1(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm1.g2 AS g_0 WHERE g_0.e1 = 'a'", "SELECT g_0.e1 FROM pm2.g1 AS g_0 WHERE g_0.e1 = 'a'", "SELECT g_0.e1 FROM pm2.g3 AS g_0 WHERE g_0.e1 = 'a'" }); //$NON-NLS-1$ }
@Test public void testOuterToInnerJoinConversion2() { QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached(); String sql = "select bqt1.smalla.intkey from bqt1.smalla full outer join bqt1.smallb on (bqt1.smalla.intkey = bqt1.smallb.intkey) where bqt1.smallb.intnum = 1"; //$NON-NLS-1$ TestOptimizer.helpPlan(sql, metadata, new String[]{"SELECT bqt1.smalla.intkey FROM bqt1.smallb LEFT OUTER JOIN bqt1.smalla ON bqt1.smalla.intkey = bqt1.smallb.intkey WHERE bqt1.smallb.intnum = 1"}); //$NON-NLS-1$ }
@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 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 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 that access node with unsatisfied access pattern is made dependent * (Same query written slightly different way) */ @Test public void testMakeDependentAccessPattern1a() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm4.g1, pm1.g1 where pm4.g1.e1 = pm1.g1.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE g_0.e1 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
/** * Test that second access pattern of access node is chosen to make * dependent with */ @Test public void testMakeDependentAccessPattern3() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g2 where pm1.g1.e1 = pm4.g2.e5", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e5 FROM pm4.g2 AS g_0 WHERE g_0.e5 IN (<dependent values>)", "SELECT g_0.e1 FROM pm1.g1 AS g_0" }, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ checkDependentGroups(plan, new String[] {"pm4.g2"}); //$NON-NLS-1$ }
@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 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$ }
@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 testLeftOuterAssocitivtyRightLinearSwap() throws Exception { BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true); TestOptimizer.helpPlan("SELECT pm1.g1.e3 from pm1.g1 left outer join (pm2.g2 left outer join pm1.g3 on pm2.g2.e2 = pm1.g3.e2) on pm1.g1.e1 = pm1.g3.e1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), new String[] { "SELECT g_0.e2 AS c_0 FROM pm2.g2 AS g_0 ORDER BY c_0", "SELECT g_1.e2 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.e1 = g_1.e1 ORDER BY c_0"}, new DefaultCapabilitiesFinder(caps), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testFullOuterJoinPredicatePlacement() throws TeiidComponentException, TeiidProcessingException { String sql = "select b1.intkey, b2.intkey from (select * from bqt1.smalla where bqt1.smalla.stringkey = 'a') b1 full outer join (select * from bqt1.smallb where bqt1.smallb.stringkey = 'b') b2 on (b1.intkey = b2.intkey)"; //$NON-NLS-1$ BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities(); bsc.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER_FULL, true); bsc.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, false); TestOptimizer.helpPlan(sql, RealMetadataFactory.exampleBQTCached(), new String[] {"SELECT g_0.IntKey FROM BQT1.SmallA AS g_0 WHERE g_0.StringKey = 'a'", "SELECT g_0.IntKey FROM BQT1.SmallB AS g_0 WHERE g_0.StringKey = 'b'"}, new DefaultCapabilitiesFinder(bsc), ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }
@Test public void testCantPushGroupBy() throws Exception { String sql = "select e3, e2 from test.group group by e3, e2"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_GROUP_BY, true); capFinder.addCapabilities("test", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, TestValidator.exampleMetadata(), new String[] {"SELECT g_0.e3, g_0.e2 FROM test.\"group\" AS g_0"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testCrossJoinWithRestriction() throws Exception { String sql = "select pm1.g1.e2, pm1.g2.e2 from pm1.g1, pm1.g2"; //$NON-NLS-1$ FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder(); BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities(); caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_INNER, true); caps.setSourceProperty(Capability.JOIN_CRITERIA_ALLOWED, SupportedJoinCriteria.THETA); capFinder.addCapabilities("pm1", caps); //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), new String[] {"SELECT g_0.e2 FROM pm1.g2 AS g_0", "SELECT g_0.e2 FROM pm1.g1 AS g_0"}, capFinder, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ }