Ejemplo n.º 1
0
  @Test
  public void testPartialProjection() throws TeiidComponentException, TeiidProcessingException {
    String sql =
        "SELECT user() AS a, "
            + " AVG(e2) OVER ( ) AS b,"
            + " MAX(e2) OVER ( ) AS b"
            + " FROM pm1.g1";

    HardcodedDataManager dataMgr = new HardcodedDataManager();
    dataMgr.addData(
        "SELECT ROUND(convert((g_0.L_DISCOUNT - AVG(g_0.L_DISCOUNT) OVER ()), FLOAT), 0) FROM TPCR_Oracle_9i.LINEITEM AS g_0",
        Arrays.asList(2.0f),
        Arrays.asList(2.0f));

    BasicSourceCapabilities bsc = TestOptimizer.getTypicalCapabilities();
    bsc.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    bsc.setCapabilitySupport(Capability.QUERY_AGGREGATES_AVG, true);
    bsc.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true);

    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            sql,
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(bsc),
            new String[] {"SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(
        plan,
        new int[] {1, 1, 1},
        new Class<?>[] {AccessNode.class, WindowFunctionProjectNode.class, ProjectNode.class});

    List<?>[] expected =
        new List<?>[] {
          Arrays.asList(null, BigDecimal.valueOf(1.5), 2),
          Arrays.asList(null, BigDecimal.valueOf(1.5), 2)
        };

    dataMgr.addData(
        "SELECT AVG(g_0.e2) OVER (), g_0.e2 FROM pm1.g1 AS g_0", //$NON-NLS-1$
        Arrays.asList(1.5, 2),
        Arrays.asList(1.5, 1));

    helpProcess(plan, dataMgr, expected);

    // should completely eliminate the window function node
    plan =
        TestOptimizer.helpPlan(
            "SELECT uuid() AS a, AVG(e2) OVER ( ) AS b FROM pm1.g1",
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(bsc),
            new String[] {"SELECT AVG(g_0.e2) OVER () FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, new int[] {1, 1}, new Class<?>[] {AccessNode.class, ProjectNode.class});
  }
  @Test
  public void testDeleteComplex() throws Exception {
    String userSql = "delete from vm1.gx where e2 < 10"; // $NON-NLS-1$
    String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";

    HardcodedDataManager dm = new HardcodedDataManager();
    dm.addData(
        "SELECT g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e2 < 10)",
        new List[] {Arrays.asList(2)});
    dm.addData("DELETE FROM pm1.g2 WHERE pm1.g2.e2 = 2", new List[] {Arrays.asList(1)});

    helpTest(
        userSql,
        viewSql,
        "CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT pm1.g2.e2 AS s_0 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e2 < 10) AS X\nBEGIN\nDELETE FROM pm1.g2 WHERE pm1.g2.e2 = X.s_0;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
        dm);
  }
  @Test
  public void testUpdateComplex() throws Exception {
    String userSql = "update vm1.gx set e1 = e2 where e3 is null"; // $NON-NLS-1$
    String viewSql = "select g2.* from pm1.g1 inner join pm1.g2 on g1.e1 = g2.e1";

    HardcodedDataManager dm = new HardcodedDataManager();
    dm.addData(
        "SELECT convert(g_1.e2, string), g_1.e2 FROM pm1.g1 AS g_0, pm1.g2 AS g_1 WHERE (g_0.e1 = g_1.e1) AND (g_1.e3 IS NULL)",
        new List[] {Arrays.asList("1", 1)});
    dm.addData(
        "UPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = 1",
        new List[] {Arrays.asList(1)});

    helpTest(
        userSql,
        viewSql,
        "CREATE VIRTUAL PROCEDURE\nBEGIN ATOMIC\nDECLARE integer VARIABLES.ROWS_UPDATED = 0;\nLOOP ON (SELECT convert(pm1.g2.e2, string) AS s_0, pm1.g2.e2 AS s_1 FROM pm1.g1 INNER JOIN pm1.g2 ON g1.e1 = g2.e1 WHERE pm1.g2.e3 IS NULL) AS X\nBEGIN\nUPDATE pm1.g2 SET e1 = convert(pm1.g2.e2, string) WHERE pm1.g2.e2 = X.s_1;\nVARIABLES.ROWS_UPDATED = (VARIABLES.ROWS_UPDATED + 1);\nEND\nSELECT VARIABLES.ROWS_UPDATED;\nEND",
        dm);
  }
Ejemplo n.º 4
0
  /**
   * Note that we've optimized the ordering to be performed prior to the windowing. If we change the
   * windowing logic to not preserve the incoming row ordering, then this optimization will need to
   * change
   *
   * @throws Exception
   */
  @Test
  public void testCountDuplicates() throws Exception {
    String sql = "select e1, count(e1) over (order by e1) as c from pm1.g1 order by e1";

    List<?>[] expected =
        new List[] {
          Arrays.asList("a", 2), Arrays.asList("a", 2), Arrays.asList("b", 3),
        };

    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList("a"), Arrays.asList("a"), Arrays.asList("b")});
    ProcessorPlan plan =
        helpGetPlan(sql, RealMetadataFactory.example1Cached(), TestOptimizer.getGenericFinder());

    helpProcess(plan, dataManager, expected);
  }