Examples with PlanMatchPattern io.trino.sql.planner.assertions.PlanMatchPattern used on opensource projects

Example 11 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestEliminateSorts method testNotEliminateSorts.

@Test
public void testNotEliminateSorts() {
    @Language("SQL") String sql = "SELECT quantity, row_number() OVER (ORDER BY quantity) FROM lineitem ORDER BY tax";
    PlanMatchPattern pattern = anyTree(sort(anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(windowSpec).addFunction(functionCall("row_number", Optional.empty(), ImmutableList.of())), anyTree(LINEITEM_TABLESCAN_Q)))));
    assertUnitPlan(sql, pattern);
}

Example 12 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestEliminateSorts method testEliminateSorts.

@Test
public void testEliminateSorts() {
    @Language("SQL") String sql = "SELECT quantity, row_number() OVER (ORDER BY quantity) FROM lineitem ORDER BY quantity";
    PlanMatchPattern pattern = output(window(windowMatcherBuilder -> windowMatcherBuilder.specification(windowSpec).addFunction(functionCall("row_number", Optional.empty(), ImmutableList.of())), anyTree(LINEITEM_TABLESCAN_Q)));
    assertUnitPlan(sql, pattern);
}

Example 13 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestRuleTester method testReportWrongMatch.

@Test
public void testReportWrongMatch() {
    try (RuleTester tester = defaultRuleTester()) {
        RuleAssert ruleAssert = tester.assertThat(rule("testReportWrongMatch rule", Pattern.typeOf(PlanNode.class), (node, captures, context) -> Result.ofPlanNode(node.replaceChildren(node.getSources())))).on(p -> p.project(Assignments.of(p.symbol("y"), expression("x")), p.values(ImmutableList.of(p.symbol("x")), ImmutableList.of(ImmutableList.of(expression("1"))))));
        PlanMatchPattern expected = values(ImmutableList.of("different"), ImmutableList.of());
        assertThatThrownBy(() -> ruleAssert.matches(expected)).isInstanceOf(AssertionError.class).hasMessageMatching("(?s)Plan does not match, expected .* but found .*");
    }
}

Example 14 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class BaseDruidConnectorTest method testLimitPushDown.

@Test
public void testLimitPushDown() {
    // Use high limit for result determinism
    assertThat(query("SELECT name FROM nation LIMIT 30")).isFullyPushedDown();
    // with filter over numeric column
    assertThat(query("SELECT name FROM nation WHERE regionkey = 3 LIMIT 5")).isFullyPushedDown();
    // with filter over varchar column
    assertThat(query("SELECT name FROM nation WHERE name < 'EEE' LIMIT 5")).isFullyPushedDown();
    // with aggregation
    // global aggregation, LIMIT removed TODO https://github.com/trinodb/trino/pull/4313
    assertThat(query("SELECT max(regionkey) FROM nation LIMIT 5")).isNotFullyPushedDown(AggregationNode.class);
    // TODO https://github.com/trinodb/trino/pull/4313
    assertThat(query("SELECT regionkey, max(name) FROM nation GROUP BY regionkey LIMIT 5")).isNotFullyPushedDown(AggregationNode.class);
    // distinct limit can be pushed down even without aggregation pushdown
    assertThat(query("SELECT DISTINCT regionkey FROM nation LIMIT 5")).isFullyPushedDown();
    // with aggregation and filter over numeric column
    // TODO https://github.com/trinodb/trino/pull/4313
    assertThat(query("SELECT regionkey, count(*) FROM nation WHERE nationkey < 5 GROUP BY regionkey LIMIT 3")).isNotFullyPushedDown(AggregationNode.class);
    // with aggregation and filter over varchar column
    // TODO https://github.com/trinodb/trino/pull/4313
    assertThat(query("SELECT regionkey, count(*) FROM nation WHERE name < 'EGYPT' GROUP BY regionkey LIMIT 3")).isNotFullyPushedDown(AggregationNode.class);
    // with TopN over numeric column
    assertThat(query("SELECT * FROM (SELECT regionkey FROM nation ORDER BY nationkey ASC LIMIT 10) LIMIT 5")).isNotFullyPushedDown(TopNNode.class);
    // with TopN over varchar column
    assertThat(query("SELECT * FROM (SELECT regionkey FROM nation ORDER BY name ASC LIMIT 10) LIMIT 5")).isNotFullyPushedDown(TopNNode.class);
    // with join
    PlanMatchPattern joinOverTableScans = node(JoinNode.class, anyTree(node(TableScanNode.class)), anyTree(node(TableScanNode.class)));
    assertThat(query(joinPushdownEnabled(getSession()), "SELECT n.name, r.name " + "FROM nation n " + "LEFT JOIN region r USING (regionkey) " + "LIMIT 30")).isNotFullyPushedDown(joinOverTableScans);
}

Example 15 with PlanMatchPattern

use of io.trino.sql.planner.assertions.PlanMatchPattern in project trino by trinodb.

the class TestPostgreSqlConnectorTest method testStringJoinPushdownWithCollate.

@Test
public void testStringJoinPushdownWithCollate() {
    PlanMatchPattern joinOverTableScans = node(JoinNode.class, anyTree(node(TableScanNode.class)), anyTree(node(TableScanNode.class)));
    PlanMatchPattern broadcastJoinOverTableScans = node(JoinNode.class, node(TableScanNode.class), exchange(ExchangeNode.Scope.LOCAL, exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPLICATE, node(TableScanNode.class))));
    Session sessionWithCollatePushdown = Session.builder(getSession()).setCatalogSessionProperty("postgresql", "enable_string_pushdown_with_collate", "true").build();
    Session session = joinPushdownEnabled(sessionWithCollatePushdown);
    // Disable DF here for the sake of negative test cases' expected plan. With DF enabled, some operators return in DF's FilterNode and some do not.
    Session withoutDynamicFiltering = Session.builder(getSession()).setSystemProperty("enable_dynamic_filtering", "false").setCatalogSessionProperty("postgresql", "enable_string_pushdown_with_collate", "true").build();
    String notDistinctOperator = "IS NOT DISTINCT FROM";
    List<String> nonEqualities = Stream.concat(Stream.of(JoinCondition.Operator.values()).filter(operator -> operator != JoinCondition.Operator.EQUAL).map(JoinCondition.Operator::getValue), Stream.of(notDistinctOperator)).collect(toImmutableList());
    try (TestTable nationLowercaseTable = new TestTable(// If a connector supports Join pushdown, but does not allow CTAS, we need to make the table creation here overridable.
    getQueryRunner()::execute, "nation_lowercase", "AS SELECT nationkey, lower(name) name, regionkey FROM nation")) {
        // basic case
        assertThat(query(session, "SELECT r.name, n.name FROM nation n JOIN region r ON n.regionkey = r.regionkey")).isFullyPushedDown();
        // join over different columns
        assertThat(query(session, "SELECT r.name, n.name FROM nation n JOIN region r ON n.nationkey = r.regionkey")).isFullyPushedDown();
        // pushdown when using USING
        assertThat(query(session, "SELECT r.name, n.name FROM nation n JOIN region r USING(regionkey)")).isFullyPushedDown();
        // varchar equality predicate
        assertConditionallyPushedDown(session, "SELECT n.name, n2.regionkey FROM nation n JOIN nation n2 ON n.name = n2.name", true, joinOverTableScans);
        assertConditionallyPushedDown(session, format("SELECT n.name, nl.regionkey FROM nation n JOIN %s nl ON n.name = nl.name", nationLowercaseTable.getName()), true, joinOverTableScans);
        // multiple bigint predicates
        assertThat(query(session, "SELECT n.name, c.name FROM nation n JOIN customer c ON n.nationkey = c.nationkey and n.regionkey = c.custkey")).isFullyPushedDown();
        // inequality
        for (String operator : nonEqualities) {
            // bigint inequality predicate
            assertThat(query(withoutDynamicFiltering, format("SELECT r.name, n.name FROM nation n JOIN region r ON n.regionkey %s r.regionkey", operator))).isNotFullyPushedDown(broadcastJoinOverTableScans);
            // varchar inequality predicate
            assertThat(query(withoutDynamicFiltering, format("SELECT n.name, nl.name FROM nation n JOIN %s nl ON n.name %s nl.name", nationLowercaseTable.getName(), operator))).isNotFullyPushedDown(broadcastJoinOverTableScans);
        }
        // inequality along with an equality, which constitutes an equi-condition and allows filter to remain as part of the Join
        for (String operator : nonEqualities) {
            assertConditionallyPushedDown(session, format("SELECT n.name, c.name FROM nation n JOIN customer c ON n.nationkey = c.nationkey AND n.regionkey %s c.custkey", operator), expectJoinPushdown(operator), joinOverTableScans);
        }
        // varchar inequality along with an equality, which constitutes an equi-condition and allows filter to remain as part of the Join
        for (String operator : nonEqualities) {
            assertConditionallyPushedDown(session, format("SELECT n.name, nl.name FROM nation n JOIN %s nl ON n.regionkey = nl.regionkey AND n.name %s nl.name", nationLowercaseTable.getName(), operator), expectJoinPushdown(operator), joinOverTableScans);
        }
        // LEFT JOIN
        assertThat(query(session, "SELECT r.name, n.name FROM nation n LEFT JOIN region r ON n.nationkey = r.regionkey")).isFullyPushedDown();
        assertThat(query(session, "SELECT r.name, n.name FROM region r LEFT JOIN nation n ON n.nationkey = r.regionkey")).isFullyPushedDown();
        // RIGHT JOIN
        assertThat(query(session, "SELECT r.name, n.name FROM nation n RIGHT JOIN region r ON n.nationkey = r.regionkey")).isFullyPushedDown();
        assertThat(query(session, "SELECT r.name, n.name FROM region r RIGHT JOIN nation n ON n.nationkey = r.regionkey")).isFullyPushedDown();
        // FULL JOIN
        assertConditionallyPushedDown(session, "SELECT r.name, n.name FROM nation n FULL JOIN region r ON n.nationkey = r.regionkey", true, joinOverTableScans);
        // Join over a (double) predicate
        assertThat(query(session, "" + "SELECT c.name, n.name " + "FROM (SELECT * FROM customer WHERE acctbal > 8000) c " + "JOIN nation n ON c.custkey = n.nationkey")).isFullyPushedDown();
        // Join over a varchar equality predicate
        assertConditionallyPushedDown(session, "SELECT c.name, n.name FROM (SELECT * FROM customer WHERE address = 'TcGe5gaZNgVePxU5kRrvXBfkasDTea') c " + "JOIN nation n ON c.custkey = n.nationkey", hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_EQUALITY), joinOverTableScans);
        // join over aggregation
        assertConditionallyPushedDown(session, "SELECT * FROM (SELECT regionkey rk, count(nationkey) c FROM nation GROUP BY regionkey) n " + "JOIN region r ON n.rk = r.regionkey", hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN), joinOverTableScans);
        // join over LIMIT
        assertConditionallyPushedDown(session, "SELECT * FROM (SELECT nationkey FROM nation LIMIT 30) n " + "JOIN region r ON n.nationkey = r.regionkey", hasBehavior(SUPPORTS_LIMIT_PUSHDOWN), joinOverTableScans);
        // join over TopN
        assertConditionallyPushedDown(session, "SELECT * FROM (SELECT nationkey FROM nation ORDER BY regionkey LIMIT 5) n " + "JOIN region r ON n.nationkey = r.regionkey", hasBehavior(SUPPORTS_TOPN_PUSHDOWN), joinOverTableScans);
        // join over join
        assertThat(query(session, "SELECT * FROM nation n, region r, customer c WHERE n.regionkey = r.regionkey AND r.regionkey = c.custkey")).isFullyPushedDown();
    }
}