@Test public void testWithGroupingAndMultiElement() { String sql = "WITH qry_0 as (SELECT floor(t.e4) AS a1, floor(t2.e4) as b1 FROM pm1.g1 AS t, pm2.g2 as t2 WHERE (t.e4=t2.e4) GROUP BY t.e4, t2.e4) SELECT * from qry_0 GROUP BY a1, b1"; List[] expected = new List[] {Arrays.asList(3.0, 3.0)}; HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e4 AS c_0 FROM pm1.g1 AS g_0 GROUP BY g_0.e4 ORDER BY c_0", Arrays.asList(2.1), Arrays.asList(3.2)); dataManager.addData("SELECT g_0.e4 AS c_0 FROM pm2.g2 AS g_0 GROUP BY g_0.e4 ORDER BY c_0", Arrays.asList(2.0), Arrays.asList(3.2)); ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testMultiCount() throws Exception { // Create query String sql = "SELECT count(pm1.g1.e2), count(pm2.g2.e2) from pm1.g1, pm2.g2 where pm1.g1.e1 = pm2.g2.e1"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { Arrays.asList(3, 2), }; // Construct data manager with data HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e1 AS c_0, COUNT(g_0.e2) AS c_1 FROM pm1.g1 AS g_0 GROUP BY g_0.e1 ORDER BY c_0", new List<?>[] {Arrays.asList("a", 1), Arrays.asList("b", 2)}); dataManager.addData("SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm2.g2 AS g_0 ORDER BY c_0", new List<?>[] {Arrays.asList("a", 6), Arrays.asList("b", 5)}); ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testUnaliasedViewAgg() throws Exception { String sql = "SELECT MIN(x.count) FROM agg x"; //$NON-NLS-1$ TransformationMetadata metadata = RealMetadataFactory.fromDDL("create foreign table smalla (intkey integer); create view agg (count integer) as select count(*) from smalla", "x", "y"); TestOptimizer.helpPlan(sql, metadata, new String[] {"SELECT MIN(v_0.c_0) FROM (SELECT COUNT(*) AS c_0 FROM y.smalla AS g_0) AS v_0"}, TestAggregatePushdown.getAggregatesFinder(), ComparisonMode.EXACT_COMMAND_STRING); }
@Test public void testUnaliasedAggInDeleteCompensation() throws Exception { String sql = "delete from pm3.g1 where e1 = (SELECT MAX(e1) FROM pm3.g1 as z where e2 = pm3.g1.e2)"; //$NON-NLS-1$ List[] expected = new List[] { Arrays.asList(1), }; HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm3.g1 AS g_0 ORDER BY c_1, c_0", new List<?>[] {Arrays.asList("a", 1)}); dataManager.addData("SELECT MAX(g_0.e1) AS c_0, g_0.e2 AS c_1 FROM pm3.g1 AS g_0 GROUP BY g_0.e2 ORDER BY c_1, c_0", new List<?>[] {Arrays.asList("a", 1)}); dataManager.addData("DELETE FROM pm3.g1 WHERE pm3.g1.e1 = 'a'", new List<?>[] {Arrays.asList(1)}); ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example4(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testStringAgg() throws Exception { // Create query String sql = "SELECT string_agg(e1, ',') from pm1.g1 group by e3"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { Arrays.asList("a,b,a"), Arrays.asList("a,c"), }; FakeDataManager dataManager = new FakeDataManager(); sampleData1(dataManager); ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testSimpleMergeGroupBy() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT MAX(e2) AS x FROM pm1.g1 GROUP BY e1"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSimpleMergeGroupBy3() { QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = TestOptimizer.helpPlan("SELECT distinct x, e1 FROM (SELECT 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 testSimpleMergeGroupBy5() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z where z.x = 1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT MAX(e2) AS x FROM pm1.g1 GROUP BY e1 HAVING MAX(e2) = 1"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testNoUnnestView() throws TeiidComponentException, TeiidProcessingException { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT e1 FROM /*+ no_unnest */ vm1.g1 limit 1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT v_0.c_0 AS c_0 FROM (SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0) AS v_0 LIMIT 1"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSimpleMergeGroupBy1() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM (SELECT distinct min(e1), max(e2) as x FROM pm1.g1 GROUP BY e1) AS z", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT v_0.c_1 FROM (SELECT DISTINCT MIN(g_0.e1) AS c_0, MAX(g_0.e2) AS c_1 FROM pm1.g1 AS g_0 GROUP BY g_0.e1) AS v_0"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSubqueryWithGrouping() { String sql = "select q.str_a, q.a from(WITH qry_0 as /*+ no_inline */ (SELECT e2 AS a1, e1 as str FROM pm1.g1 AS t) SELECT a1 as a, str as str_a from qry_0) as q group by q.str_a, q.a"; List[] expected = new List[] {Arrays.asList("a", 1)}; HardcodedDataManager dataManager = new HardcodedDataManager(); dataManager.addData("SELECT g_0.e2, g_0.e1 FROM pm1.g1 AS g_0", Arrays.asList(1, "a")); ProcessorPlan plan = helpGetPlan(helpParse(sql), RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
/** * Same as above but all required symbols are selected */ @Test public void testSimpleMergeGroupBy2() { QueryMetadataInterface metadata = RealMetadataFactory.example1Cached(); ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x, e1 FROM (SELECT distinct 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, e1 FROM pm1.g1 GROUP BY e1"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSimpleMergeGroupBy6() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z where z.x = 1", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT MAX(e2) AS x FROM pm1.g1 GROUP BY e1 HAVING MAX(e2) = 1"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testNoUnnest() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x FROM /*+ no_unnest */ (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z order by x", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT v_0.c_0 FROM (SELECT MAX(g_0.e2) AS c_0 FROM pm1.g1 AS g_0 GROUP BY g_0.e1) AS v_0 ORDER BY c_0"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN); }
@Test public void testSimpleMergeGroupBy4() { ProcessorPlan plan = TestOptimizer.helpPlan("SELECT x, x FROM (SELECT e1, max(e2) as x FROM pm1.g1 GROUP BY e1) AS z", //$NON-NLS-1$ RealMetadataFactory.example1Cached(), null, TestAggregatePushdown.getAggregatesFinder(), new String[] { "SELECT v_0.c_0 FROM (SELECT MAX(g_0.e2) AS c_0 FROM pm1.g1 AS g_0 GROUP BY g_0.e1) AS v_0"}, TestOptimizer.SHOULD_SUCCEED); //$NON-NLS-1$ TestOptimizer.checkNodeTypes(plan, TestOptimizer.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 testStringAggNoRows() throws Exception { // Create query String sql = "SELECT string_agg(e1, ',') from pm1.g1 where e2 > 10"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { Collections.singletonList(null), }; FakeDataManager dataManager = new FakeDataManager(); sampleData1(dataManager); ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testStringAggBinary() throws Exception { // Create query String sql = "SELECT cast(string_agg(to_bytes(e1, 'UTF-8'), X'AB') as varbinary) from pm1.g1 group by e3"; //$NON-NLS-1$ // Create expected results List[] expected = new List[] { Arrays.asList(new BinaryType(new byte[] {(byte)0x61, (byte)0xAB, (byte)0x62, (byte)0xAB, (byte)0x61})), Arrays.asList(new BinaryType(new byte[] {(byte)0x61, (byte)0xAB, (byte)0x63})), }; FakeDataManager dataManager = new FakeDataManager(); sampleData1(dataManager); ProcessorPlan plan = helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestAggregatePushdown.getAggregatesFinder()); helpProcess(plan, dataManager, expected); }
@Test public void testAggregatePushdown2() throws Exception { QueryMetadataInterface metadata = RealMetadataFactory.exampleAggregatesCached(); String sql = "SELECT o_dealerid, o_productid, sum(o_amount) FROM m1.order, m1.dealer, m2.product " + //$NON-NLS-1$ "WHERE o_dealerid=d_dealerid AND o_productid=p_productid AND d_state = 'CA' AND p_divid = 100 " + //$NON-NLS-1$ "GROUP BY o_dealerid, o_productid having max(o_amount) < 100"; //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, null, getAggregatesFinder(), new String[] {"SELECT g_0.P_ProductID AS c_0 FROM m2.product AS g_0 WHERE g_0.P_DivID = 100 ORDER BY c_0", "SELECT g_0.O_ProductID, g_0.O_DealerID, MAX(g_0.O_Amount), SUM(g_0.O_Amount) FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.O_DealerID = g_1.D_DealerID) AND (g_1.D_State = 'CA') AND (g_0.O_ProductID IN (<dependent values>)) GROUP BY g_0.O_ProductID, g_0.O_DealerID"}, //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 1, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 1, // Select 0, // Sort 0 // UnionAll }); }
/** * Note that until we can test the other side cardinality, we cannot fully push the group node */ @Test public void testAggregatePushdown1() throws Exception { QueryMetadataInterface metadata = RealMetadataFactory.exampleAggregatesCached(); String sql = "SELECT o_dealerid, o_productid, sum(o_amount) FROM m1.order, m1.dealer, m2.product " + //$NON-NLS-1$ "WHERE o_dealerid=d_dealerid AND o_productid=p_productid AND d_state = 'CA' AND p_divid = 100 " + //$NON-NLS-1$ "GROUP BY o_dealerid, o_productid"; //$NON-NLS-1$ ProcessorPlan plan = TestOptimizer.helpPlan(sql, metadata, null, getAggregatesFinder(), new String[] {"SELECT g_0.O_ProductID, g_0.O_DealerID, SUM(g_0.O_Amount) FROM m1.\"order\" AS g_0, m1.dealer AS g_1 WHERE (g_0.O_DealerID = g_1.D_DealerID) AND (g_1.D_State = 'CA') AND (g_0.O_ProductID IN (<dependent values>)) GROUP BY g_0.O_ProductID, g_0.O_DealerID", "SELECT g_0.P_ProductID AS c_0 FROM m2.product AS g_0 WHERE g_0.P_DivID = 100 ORDER BY c_0"}, //$NON-NLS-1$ //$NON-NLS-2$ TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); TestOptimizer.checkNodeTypes(plan, new int[] { 1, // Access 1, // DependentAccess 0, // DependentSelect 0, // DependentProject 0, // DupRemove 1, // Grouping 0, // NestedLoopJoinStrategy 1, // MergeJoinStrategy 0, // Null 0, // PlanExecution 1, // Project 0, // Select 0, // Sort 0 // UnionAll }); }