예제 #1
0
  @Test
  public void testOrderByConstant() throws TeiidComponentException, TeiidProcessingException {
    String sql =
        "select 1 as jiraissue_assignee, "
            + "row_number() over(order by subquerytable.jiraissue_id desc) as calculatedfield1 "
            + "from  pm1.g1 as jiraissue left outer join "
            + "(select jiraissue_sub.e1 as jiraissue_assignee, jiraissue_sub.e1 as jiraissue_id from pm2.g2 jiraissue_sub "
            + "where (jiraissue_sub.e4 between null and 2)"
            + " ) subquerytable on jiraissue.e1 = subquerytable.jiraissue_assignee";

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

    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            sql,
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(bsc),
            new String[] {"SELECT 1 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});
  }
예제 #2
0
  @Test
  public void testWindowFunctionPushdown1() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "select max(e1) over (order by e1) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(
        plan,
        new int[] {
          1, // Access
          0, // DependentAccess
          0, // DependentSelect
          0, // DependentProject
          0, // DupRemove
          0, // Grouping
          0, // NestedLoopJoinStrategy
          0, // MergeJoinStrategy
          0, // Null
          0, // PlanExecution
          1, // Project
          0, // Select
          0, // Sort
          0 // UnionAll
        });
  }
예제 #3
0
  @Test
  public void testMustPushdownOverMultipleSourcesWithViewDupRemoval() throws Exception {
    QueryMetadataInterface metadata =
        RealMetadataFactory.createTransformationMetadata(
            RealMetadataFactory.example1Cached().getMetadataStore(),
            "example1",
            new FunctionTree("foo", new FakeFunctionMetadataSource()));

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport("misc.namespace.func", true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps); // $NON-NLS-1$

    String sql =
        "select func(x.e1) from (select distinct x.* from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2 order by e1 limit 10) as x"; //$NON-NLS-1$

    helpPlan(
        sql,
        metadata,
        null,
        capFinder,
        new String[] {},
        ComparisonMode.FAILED_PLANNING); // $NON-NLS-1$
  }
예제 #4
0
  @Test
  public void testWindowFunctionPushdown3() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_DISTINCT, true);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "select count(distinct e1) over (partition by e2) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {"SELECT g_0.e1, 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});

    caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_DISTINCT_AGGREGATES, true);
    plan =
        TestOptimizer.helpPlan(
            "select count(distinct e1) over (partition by e2) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {
              "SELECT COUNT(DISTINCT g_0.e1) OVER (PARTITION BY g_0.e2) FROM pm1.g1 AS g_0"
            },
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, FULL_PUSHDOWN);
  }
예제 #5
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});
  }
예제 #6
0
  @Test
  public void testConcat2() throws Exception {
    QueryMetadataInterface metadata = RealMetadataFactory.example1Cached();

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$

    String sql = "select concat2(x.e1, x.e1) from pm1.g1 as x"; // $NON-NLS-1$

    helpPlan(
        sql,
        metadata,
        null,
        capFinder,
        new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    // cannot pushdown
    caps.setFunctionSupport(SourceSystemFunctions.CONCAT2, false);

    ProcessorPlan plan =
        helpPlan(
            sql,
            metadata,
            null,
            capFinder,
            new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e1 FROM pm1.g1 AS g_0",
        new List[] {Arrays.asList("a"), Arrays.asList((String) null)});
    TestProcessor.helpProcess(
        plan, dataManager, new List[] {Arrays.asList("aa"), Arrays.asList((String) null)});

    caps.setFunctionSupport(SourceSystemFunctions.CONCAT, true);
    caps.setFunctionSupport(SourceSystemFunctions.IFNULL, true);
    caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);

    // will get replaced in the LanguageBridgeFactory
    helpPlan(
        sql,
        metadata,
        null,
        capFinder,
        new String[] {"SELECT concat2(g_0.e1, g_0.e1) FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
  }
예제 #7
0
  @Test
  public void testWindowFunctionPushdown() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "select max(e1) over (order by e1) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {"SELECT MAX(g_0.e1) OVER (ORDER BY g_0.e1) FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, FULL_PUSHDOWN);
  }
예제 #8
0
  @Test
  public void testViewNotRemoved() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "SELECT y FROM (select row_number() over (order by e1) as y from pm1.g1) as x where x.y = 10", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {
              "SELECT v_0.c_0 FROM (SELECT ROW_NUMBER() OVER (ORDER BY g_0.e1) AS c_0 FROM pm1.g1 AS g_0) AS v_0 WHERE v_0.c_0 = 10"
            },
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, FULL_PUSHDOWN);
  }
  private Command helpTest(
      String userSql, String viewSql, String expectedSql, ProcessorDataManager dm)
      throws Exception {
    TransformationMetadata metadata = TestUpdateValidator.example1();
    TestUpdateValidator.createView(viewSql, metadata, "gx");
    Command command = TestQueryRewriter.helpTestRewriteCommand(userSql, expectedSql, metadata);

    if (dm != null) {
      CommandContext context = createCommandContext();
      BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
      caps.setFunctionSupport(SourceSystemFunctions.CONVERT, true);
      ProcessorPlan plan =
          helpGetPlan(helpParse(userSql), metadata, new DefaultCapabilitiesFinder(caps), context);
      List<?>[] expected = new List[] {Arrays.asList(1)};
      helpProcess(plan, context, dm, expected);
    }

    return command;
  }
예제 #10
0
  @Test
  public void testDDLMetadata() throws Exception {
    String ddl =
        "CREATE VIRTUAL FUNCTION SourceFunc(msg varchar) RETURNS varchar "
            + "OPTIONS(CATEGORY 'misc', DETERMINISM 'DETERMINISTIC', "
            + "\"NULL-ON-NULL\" 'true', JAVA_CLASS '"
            + TestFunctionPushdown.class.getName()
            + "', JAVA_METHOD 'sourceFunc');"
            + "CREATE VIEW X (Y varchar) as SELECT e1 from pm1.g1;";

    MetadataFactory mf = TestDDLParser.helpParse(ddl, "model");
    mf.getSchema().setPhysical(false);
    MetadataStore ms = mf.asMetadataStore();
    ms.merge(RealMetadataFactory.example1Cached().getMetadataStore());

    QueryMetadataInterface metadata =
        RealMetadataFactory.createTransformationMetadata(ms, "example1");

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport("model.SourceFunc", true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$

    helpPlan(
        "select sourceFunc(y) from x",
        metadata,
        null,
        capFinder,
        new String[] {"SELECT sourceFunc(g_0.e1) FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    caps.setFunctionSupport("model.SourceFunc", false);

    helpPlan(
        "select sourceFunc(y) from x",
        metadata,
        null,
        capFinder,
        new String[] {"SELECT g_0.e1 FROM pm1.g1 AS g_0"},
        ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$
  }
예제 #11
0
  @Test
  public void testViewLimit() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "SELECT * FROM (select e1, e3, count(distinct e1) over (partition by e3) as r from pm1.g1) as x limit 1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {"SELECT g_0.e1, g_0.e3 FROM pm1.g1 AS g_0"},
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    FakeDataManager dataManager = new FakeDataManager();
    sampleData1(dataManager);
    List<?>[] expected =
        new List<?>[] {
          Arrays.asList("a", Boolean.FALSE, 2),
        };
    helpProcess(plan, dataManager, expected);
  }
예제 #12
0
  @Test
  public void testWindowFunctionPushdown2() throws Exception {
    BasicSourceCapabilities caps = getTypicalCapabilities();
    caps.setCapabilitySupport(Capability.ELEMENTARY_OLAP, true);
    caps.setCapabilitySupport(Capability.WINDOW_FUNCTION_ORDER_BY_AGGREGATES, true);
    caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
    caps.setSourceProperty(Capability.QUERY_ORDERBY_DEFAULT_NULL_ORDER, NullOrder.UNKNOWN);
    ProcessorPlan plan =
        TestOptimizer.helpPlan(
            "select max(e1) over (order by e1 nulls first) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {"SELECT g_0.e1 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});

    caps.setCapabilitySupport(Capability.QUERY_ORDERBY_NULL_ORDERING, true);
    plan =
        TestOptimizer.helpPlan(
            "select max(e1) over (order by e1 nulls first) as y from pm1.g1", //$NON-NLS-1$
            RealMetadataFactory.example1Cached(),
            null,
            new DefaultCapabilitiesFinder(caps),
            new String[] {
              "SELECT MAX(g_0.e1) OVER (ORDER BY g_0.e1 NULLS FIRST) FROM pm1.g1 AS g_0"
            },
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    checkNodeTypes(plan, FULL_PUSHDOWN);
  }
예제 #13
0
  @Test
  public void testMustPushdownOverMultipleSources() throws Exception {
    QueryMetadataInterface metadata =
        RealMetadataFactory.createTransformationMetadata(
            RealMetadataFactory.example1Cached().getMetadataStore(),
            "example1",
            new FunctionTree("foo", new FakeFunctionMetadataSource()));

    FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
    BasicSourceCapabilities caps = TestOptimizer.getTypicalCapabilities();
    caps.setFunctionSupport("misc.namespace.func", true);
    capFinder.addCapabilities("pm1", caps); // $NON-NLS-1$
    capFinder.addCapabilities("pm2", caps); // $NON-NLS-1$

    String sql = "select func(x.e1) from pm1.g1 as x, pm2.g1 as y where x.e2 = y.e2"; // $NON-NLS-1$

    ProcessorPlan plan =
        helpPlan(
            sql,
            metadata,
            null,
            capFinder,
            new String[] {
              "SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
              "SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0"
            },
            ComparisonMode.EXACT_COMMAND_STRING); // $NON-NLS-1$

    HardcodedDataManager dataManager = new HardcodedDataManager();
    dataManager.addData(
        "SELECT g_0.e2 AS c_0, func(g_0.e1) AS c_1 FROM pm1.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList(1, "a")});
    dataManager.addData(
        "SELECT g_0.e2 AS c_0 FROM pm2.g1 AS g_0 ORDER BY c_0",
        new List[] {Arrays.asList(1), Arrays.asList(2)});

    TestProcessor.helpProcess(plan, dataManager, new List[] {Arrays.asList("a")});
  }