@Test public void testEnhancedJoinWithLeftDuplicates() throws Exception { String sql = "select t2.*, t1.* from (select 3 as a, 3 as b union all select 1 as a, 1 as b union all select 3 as a, 3 as b) as t1 join test_a t2 on t1.a=t2.a limit 10"; TransformationMetadata metadata = RealMetadataFactory.fromDDL("CREATE foreign TABLE test_a ( a integer, b integer );", "x", "y"); HardcodedDataManager hdm = new HardcodedDataManager(); hdm.addData("SELECT g_0.a AS c_0, g_0.b AS c_1 FROM y.test_a AS g_0 WHERE g_0.a IN (1, 3) ORDER BY c_0", Arrays.asList(1, 1), Arrays.asList(1, 2), Arrays.asList(3, 2), Arrays.asList(3, 10)); ProcessorPlan plan = TestProcessor.helpGetPlan(sql, metadata, TestOptimizer.getGenericFinder()); TestProcessor.helpProcess(plan, TestProcessor.createCommandContext(), hdm, new List<?>[] { Arrays.asList(1, 1, 1, 1), Arrays.asList(1, 2, 1, 1), Arrays.asList(3, 2, 3, 3), Arrays.asList(3, 10, 3, 3), Arrays.asList(3, 2, 3, 3), Arrays.asList(3, 10, 3, 3) }); }
/** * Will fail missing param2 assignment */ @Test public void testProcAsTable5(){ String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=e2 and param2 = 'a'"; //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false); }
@Test public void testCopyOrAcrossJoin() throws Exception { helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and (pm1.g1.e1 = 'abc' OR pm1.g1.e1 = 'def')", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e1 = 'def')", //$NON-NLS-1$ "SELECT pm1.g2.e1 FROM pm1.g2 WHERE (pm1.g2.e1 = 'abc') OR (pm1.g2.e1 = 'def')" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ }
/** * Will fail due to access pattern validation (missing param2 assignment) */ @Test public void testProcAsTable1(){ String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=1"; //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false); }
/** * Will fail missing param2 assignment */ @Test public void testProcAsTable4(){ String sql = "select param1, param2, e1, e2 from pm1.vsp26 where param1=1 and not(param2 = 'a')"; //$NON-NLS-1$ TestOptimizer.helpPlan(sql, RealMetadataFactory.example1Cached(), null, TestOptimizer.getGenericFinder(), null, false); }
@Test public void testCantCopyAcrossJoin1() throws Exception { helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and concat(pm1.g1.e1, pm1.g1.e2) = 'abc'", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1, pm1.g1.e2 FROM pm1.g1", //$NON-NLS-1$ "SELECT pm1.g2.e1 FROM pm1.g2" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testCantCopyAcrossJoin2() throws Exception { helpPlan("select pm1.g1.e1, pm1.g2.e1 from pm1.g1, pm1.g2 where pm1.g1.e1=pm1.g2.e1 and (pm1.g1.e1 = 'abc' OR pm1.g1.e2 = 5)", example1(), //$NON-NLS-1$ new String[] { "SELECT pm1.g1.e1 FROM pm1.g1 WHERE (pm1.g1.e1 = 'abc') OR (pm1.g1.e2 = 5)", //$NON-NLS-1$ "SELECT pm1.g2.e1 FROM pm1.g2" }, getGenericFinder(false), ComparisonMode.CORRECTED_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testPushSortOverAliases() throws Exception { String sql = "select column_a, column_b from (select sum(column_a) over (partition by key_column) as column_a, key_column from a ) a left outer join ( " + " select sum(column_b) over (partition by key_column) as column_b, key_column from b) b on a.key_column = b.key_column order by column_a desc limit 10"; TransformationMetadata tm = RealMetadataFactory.fromDDL("create foreign table a (column_a integer, key_column string primary key);" + " create foreign table b (column_b integer, key_column string primary key);", "x", "y"); ProcessorPlan plan = TestOptimizer.helpPlan(sql, tm, new String[] {"SELECT g_0.key_column, g_0.column_b FROM y.b AS g_0", "SELECT g_0.key_column, g_0.column_a FROM y.a AS g_0"}, TestOptimizer.getGenericFinder(false), ComparisonMode.EXACT_COMMAND_STRING); HardcodedDataManager hdm = new HardcodedDataManager(); hdm.addData("SELECT g_0.key_column, g_0.column_a FROM y.a AS g_0", Arrays.asList("a", 1)); hdm.addData("SELECT g_0.key_column, g_0.column_b FROM y.b AS g_0", Arrays.asList("a", 1)); TestProcessor.helpProcess(plan, hdm, new List[] {Arrays.asList(1l, 1l)}); }
@Test(expected=QueryProcessingException.class) public void testUpdateFilter2() throws Exception { HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e3, g_0.e1 FROM pm1.g1 AS g_0 WHERE (g_0.e1 = 'user') AND (g_0.e2 = 5)", new List<?>[] {Arrays.asList(Boolean.TRUE, "user")}); ProcessorPlan plan = helpGetPlan(helpParse("update pm1.g1 set e1 = e3 || 'r' where e2 = 5"), RealMetadataFactory.example4(), TestOptimizer.getGenericFinder(), context); List<?>[] expectedResults = new List<?>[] {Arrays.asList(0)}; helpProcess(plan, context, dataManager, expectedResults); }
/** * Tests two access nodes, each with access patterns, but one already * satisfied by user criteria - the other should be made dependent * (same query written slightly different). */ @Test public void testNodesBothHaveAccessPatterns1a() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm4.g1.e1 from pm4.g2, pm4.g1 where pm4.g2.e1 = pm4.g1.e1 and pm4.g2.e5 = 'abc'", 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 pm4.g2 AS g_0 WHERE g_0.e5 = 'abc'"}, TestOptimizer.getGenericFinder(false), TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ //$NON-NLS-2$ TestDependentJoins.checkDependentGroups(plan, new String[] {"pm4.g1"}); //$NON-NLS-1$ }
@Test public void testDistinctConstant() throws Exception { String sql = "select distinct 1 from pm1.g1"; //$NON-NLS-1$ ProcessorPlan plan = helpPlan(sql, RealMetadataFactory.example1Cached(), null, getGenericFinder(), new String[] {"SELECT DISTINCT 1 FROM pm1.g1 AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ checkNodeTypes(plan, FULL_PUSHDOWN); }
@Test public void testRecursiveView() throws Exception { String ddl = "CREATE view x (y string) as (select * from x)"; QueryMetadataInterface metadata = RealMetadataFactory.fromDDL(ddl, "x", "phy"); getPlan(helpGetCommand("select * from x", metadata), metadata, getGenericFinder(), null, false, null); }
@Test public void testEmptyAggregate() throws Exception { String sql = "select count(e1) from pm1.g1 where 1 = 0"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, TestOptimizer.getGenericFinder()); List[] expected = new List[] { Arrays.asList(new Object[] { new Integer(0) }) }; FakeDataManager manager = new FakeDataManager(); sampleData1(manager); helpProcess(plan, manager, expected); }
/** * Test that access node with unsatisfied access pattern is made dependent */ @Test public void testMakeDependentAccessPattern2() throws Exception { ProcessorPlan plan = TestOptimizer.helpPlan("select pm1.g1.e1 from pm1.g1, pm4.g1 where pm4.g1.e2 = 1 and pm1.g1.e1 = pm4.g1.e1", RealMetadataFactory.example1Cached(), //$NON-NLS-1$ new String[] { "SELECT g_0.e1 FROM pm4.g1 AS g_0 WHERE (g_0.e2 = 1) AND (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 public void testUpdateProcedureCriteria() throws Exception { String preparedSql = "delete from vm1.g37 where e1=?"; //$NON-NLS-1$ List<?>[] expected = new List<?>[] { Arrays.asList(1), }; List<String> values = Arrays.asList("aa "); //$NON-NLS-1$ FakeDataManager dataManager = new FakeDataManager(); TestProcessor.sampleData2b(dataManager, RealMetadataFactory.example1Cached()); helpTestProcessing(preparedSql, values, expected, dataManager, TestOptimizer.getGenericFinder(), RealMetadataFactory.example1Cached(), null, false, false, false, RealMetadataFactory.example1VDB()); }
@Test public void testInsertDefaultResolvingExpression() throws Exception { String sql = "insert into x (y) values ('1')"; //$NON-NLS-1$ TransformationMetadata tm = RealMetadataFactory.fromDDL("" + "create foreign table t (y string, z string) options (updatable true); " + "create view x (y string, z string default 'a' || user()) options (updatable true) as select * from t; " + "create trigger on x instead of insert as for each row begin insert into t (y, z) values (new.y, new.z); end;", "vdb", "source"); Command command = helpParse(sql); ProcessorPlan plan = helpGetPlan(command, tm, TestOptimizer.getGenericFinder()); HardcodedDataManager dataManager = new HardcodedDataManager(tm); dataManager.addData("INSERT INTO t (y, z) VALUES ('1', 'auser')", new List<?>[] {Arrays.asList(1)}); helpProcess(plan, dataManager, new List<?>[] {Arrays.asList(1)}); }
@Ignore("we no longer auto detect this case, if we need this logic it will have to be added to the rewriter since it changes select into to an insert") @Test public void testMaterializedTransformationLoading() throws Exception { String userSql = "SELECT MATVIEW.E1 INTO MatTable.MatStage FROM MATVIEW"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ Collection<Annotation> annotations = analysis.getAnnotations(); assertNotNull("Expected annotations but got none", annotations); //$NON-NLS-1$ assertTrue("Expected one annotation", annotations.size() == 1); //$NON-NLS-1$ assertEquals("Expected catagory mat view", annotations.iterator().next().getCategory(), Annotation.MATERIALIZED_VIEW); //$NON-NLS-1$ }
@Test public void testNoCacheInTransformation() throws Exception { String userSql = "SELECT VGROUP.E1 FROM VGROUP"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.exampleMaterializedView(); AnalysisRecord analysis = new AnalysisRecord(true, DEBUG); Command command = helpGetCommand(userSql, metadata); TestOptimizer.helpPlanCommand(command, metadata, getGenericFinder(), analysis, new String[] {"SELECT g_0.x FROM MatSrc.MatSrc AS g_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ }
@Test public void testLimitZero2() { String sql = "select vm1.g1.e1 from vm1.g1 union select e1 from pm1.g2 limit 0"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, TestOptimizer.getGenericFinder()); List[] expected = new List[] { }; FakeDataManager manager = new FakeDataManager(); sampleData1(manager); helpProcess(plan, manager, expected); }
@Test public void testLimitZero() { String sql = "select e1 from pm1.g1 limit 0"; //$NON-NLS-1$ QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = helpGetPlan(helpParse(sql), metadata, TestOptimizer.getGenericFinder()); TestOptimizer.checkNodeTypes(plan, TestRuleRaiseNull.FULLY_NULL); List[] expected = new List[] { }; FakeDataManager manager = new FakeDataManager(); sampleData1(manager); helpProcess(plan, manager, expected); }