Examples with INNER io.trino.sql.planner.plan.JoinNode.Type.INNER used on opensource projects

Example 6 with INNER

use of io.trino.sql.planner.plan.JoinNode.Type.INNER in project trino by trinodb.

the class TestReplaceRedundantJoinWithSource method testPruneOutputs.

@Test
public void testPruneOutputs() {
    tester().assertThat(new ReplaceRedundantJoinWithSource()).on(p -> {
        Symbol a = p.symbol("a");
        Symbol b = p.symbol("b");
        return p.join(LEFT, p.values(10, a, b), p.values(1), ImmutableList.of(), ImmutableList.of(a), ImmutableList.of(), Optional.of(expression("a > b")));
    }).matches(project(ImmutableMap.of("a", PlanMatchPattern.expression("a")), values(ImmutableList.of("a", "b"), nCopies(10, ImmutableList.of(new NullLiteral(), new NullLiteral())))));
    tester().assertThat(new ReplaceRedundantJoinWithSource()).on(p -> {
        Symbol a = p.symbol("a");
        Symbol b = p.symbol("b");
        return p.join(INNER, p.values(10, a, b), p.values(1), ImmutableList.of(), ImmutableList.of(a), ImmutableList.of(), Optional.of(expression("a > b")));
    }).matches(project(ImmutableMap.of("a", PlanMatchPattern.expression("a")), filter("a > b", values(ImmutableList.of("a", "b"), nCopies(10, ImmutableList.of(new NullLiteral(), new NullLiteral()))))));
}

Example 7 with INNER

use of io.trino.sql.planner.plan.JoinNode.Type.INNER in project trino by trinodb.

the class TestHiveProjectionPushdownIntoTableScan method testDereferencePushdown.

@Test
public void testDereferencePushdown() {
    String testTable = "test_simple_projection_pushdown";
    QualifiedObjectName completeTableName = new QualifiedObjectName(HIVE_CATALOG_NAME, SCHEMA_NAME, testTable);
    getQueryRunner().execute(format("CREATE TABLE %s (col0, col1) AS" + " SELECT cast(row(5, 6) as row(x bigint, y bigint)) AS col0, 5 AS col1 WHERE false", testTable));
    Session session = getQueryRunner().getDefaultSession();
    Optional<TableHandle> tableHandle = getTableHandle(session, completeTableName);
    assertTrue(tableHandle.isPresent(), "expected the table handle to be present");
    Map<String, ColumnHandle> columns = getColumnHandles(session, completeTableName);
    HiveColumnHandle column0Handle = (HiveColumnHandle) columns.get("col0");
    HiveColumnHandle column1Handle = (HiveColumnHandle) columns.get("col1");
    HiveColumnHandle columnX = createProjectedColumnHandle(column0Handle, ImmutableList.of(0));
    HiveColumnHandle columnY = createProjectedColumnHandle(column0Handle, ImmutableList.of(1));
    // Simple Projection pushdown
    assertPlan("SELECT col0.x expr_x, col0.y expr_y FROM " + testTable, any(tableScan(((HiveTableHandle) tableHandle.get().getConnectorHandle()).withProjectedColumns(ImmutableSet.of(columnX, columnY))::equals, TupleDomain.all(), ImmutableMap.of("col0#x", columnX::equals, "col0#y", columnY::equals))));
    // Projection and predicate pushdown
    assertPlan(format("SELECT col0.x FROM %s WHERE col0.x = col1 + 3 and col0.y = 2", testTable), anyTree(filter("col0_y = BIGINT '2' AND (col0_x =  cast((col1 + 3) as BIGINT))", tableScan(table -> {
        HiveTableHandle hiveTableHandle = (HiveTableHandle) table;
        return hiveTableHandle.getCompactEffectivePredicate().equals(TupleDomain.withColumnDomains(ImmutableMap.of(columnY, Domain.singleValue(BIGINT, 2L)))) && hiveTableHandle.getProjectedColumns().equals(ImmutableSet.of(column1Handle, columnX, columnY));
    }, TupleDomain.all(), ImmutableMap.of("col0_y", columnY::equals, "col0_x", columnX::equals, "col1", column1Handle::equals)))));
    // Projection and predicate pushdown with overlapping columns
    assertPlan(format("SELECT col0, col0.y expr_y FROM %s WHERE col0.x = 5", testTable), anyTree(filter("col0_x = BIGINT '5'", tableScan(table -> {
        HiveTableHandle hiveTableHandle = (HiveTableHandle) table;
        return hiveTableHandle.getCompactEffectivePredicate().equals(TupleDomain.withColumnDomains(ImmutableMap.of(columnX, Domain.singleValue(BIGINT, 5L)))) && hiveTableHandle.getProjectedColumns().equals(ImmutableSet.of(column0Handle, columnX));
    }, TupleDomain.all(), ImmutableMap.of("col0", column0Handle::equals, "col0_x", columnX::equals)))));
    // Projection and predicate pushdown with joins
    assertPlan(format("SELECT T.col0.x, T.col0, T.col0.y FROM %s T join %s S on T.col1 = S.col1 WHERE (T.col0.x = 2)", testTable, testTable), anyTree(project(ImmutableMap.of("expr_0_x", expression("expr_0[1]"), "expr_0", expression("expr_0"), "expr_0_y", expression("expr_0[2]")), join(INNER, ImmutableList.of(equiJoinClause("t_expr_1", "s_expr_1")), anyTree(filter("expr_0_x = BIGINT '2'", tableScan(table -> ((HiveTableHandle) table).getCompactEffectivePredicate().getDomains().get().equals(ImmutableMap.of(columnX, Domain.singleValue(BIGINT, 2L))), TupleDomain.all(), ImmutableMap.of("expr_0_x", columnX::equals, "expr_0", column0Handle::equals, "t_expr_1", column1Handle::equals)))), anyTree(tableScan(((HiveTableHandle) tableHandle.get().getConnectorHandle()).withProjectedColumns(ImmutableSet.of(column1Handle))::equals, TupleDomain.all(), ImmutableMap.of("s_expr_1", column1Handle::equals)))))));
}

Example 8 with INNER

use of io.trino.sql.planner.plan.JoinNode.Type.INNER in project trino by trinodb.

the class TestDetermineJoinDistributionType method testFlipAndReplicateWhenOneTableMuchSmaller.

@Test
public void testFlipAndReplicateWhenOneTableMuchSmaller() {
    // variable width so that average row size is respected
    VarcharType symbolType = createUnboundedVarcharType();
    int aRows = 100;
    int bRows = 10_000;
    assertDetermineJoinDistributionType().setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.AUTOMATIC.name()).overrideStats("valuesA", PlanNodeStatsEstimate.builder().setOutputRowCount(aRows).addSymbolStatistics(ImmutableMap.of(new Symbol("A1"), new SymbolStatsEstimate(0, 100, 0, 6400, 100))).build()).overrideStats("valuesB", PlanNodeStatsEstimate.builder().setOutputRowCount(bRows).addSymbolStatistics(ImmutableMap.of(new Symbol("B1"), new SymbolStatsEstimate(0, 100, 0, 640000, 100))).build()).on(p -> {
        Symbol a1 = p.symbol("A1", symbolType);
        Symbol b1 = p.symbol("B1", symbolType);
        return p.join(INNER, p.values(new PlanNodeId("valuesA"), aRows, a1), p.values(new PlanNodeId("valuesB"), bRows, b1), ImmutableList.of(new JoinNode.EquiJoinClause(a1, b1)), ImmutableList.of(a1), ImmutableList.of(b1), Optional.empty());
    }).matches(join(INNER, ImmutableList.of(equiJoinClause("B1", "A1")), Optional.empty(), Optional.of(REPLICATED), values(ImmutableMap.of("B1", 0)), values(ImmutableMap.of("A1", 0))));
}

Example 9 with INNER

use of io.trino.sql.planner.plan.JoinNode.Type.INNER in project trino by trinodb.

the class TestDetermineJoinDistributionType method testPartitionWhenBothTablesEqual.

@Test
public void testPartitionWhenBothTablesEqual() {
    int aRows = 10_000;
    int bRows = 10_000;
    assertDetermineJoinDistributionType().setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.AUTOMATIC.name()).overrideStats("valuesA", PlanNodeStatsEstimate.builder().setOutputRowCount(aRows).addSymbolStatistics(ImmutableMap.of(new Symbol("A1"), new SymbolStatsEstimate(0, 100, 0, 640000, 100))).build()).overrideStats("valuesB", PlanNodeStatsEstimate.builder().setOutputRowCount(bRows).addSymbolStatistics(ImmutableMap.of(new Symbol("B1"), new SymbolStatsEstimate(0, 100, 0, 640000, 100))).build()).on(p -> p.join(INNER, p.values(new PlanNodeId("valuesA"), aRows, p.symbol("A1", BIGINT)), p.values(new PlanNodeId("valuesB"), bRows, p.symbol("B1", BIGINT)), ImmutableList.of(new JoinNode.EquiJoinClause(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT))), ImmutableList.of(p.symbol("A1", BIGINT)), ImmutableList.of(p.symbol("B1", BIGINT)), Optional.empty())).matches(join(INNER, ImmutableList.of(equiJoinClause("A1", "B1")), Optional.empty(), Optional.of(PARTITIONED), values(ImmutableMap.of("A1", 0)), values(ImmutableMap.of("B1", 0))));
}

Example 10 with INNER

use of io.trino.sql.planner.plan.JoinNode.Type.INNER in project trino by trinodb.

the class TestDetermineJoinDistributionType method testGetSourceTablesSizeInBytes.

@Test
public void testGetSourceTablesSizeInBytes() {
    PlanBuilder planBuilder = new PlanBuilder(new PlanNodeIdAllocator(), tester.getMetadata(), tester.getSession());
    Symbol symbol = planBuilder.symbol("col");
    Symbol sourceSymbol1 = planBuilder.symbol("source1");
    Symbol sourceSymbol2 = planBuilder.symbol("soruce2");
    // missing source stats
    assertEquals(getSourceTablesSizeInBytes(planBuilder.values(symbol), noLookup(), node -> PlanNodeStatsEstimate.unknown(), planBuilder.getTypes()), NaN);
    // two source plan nodes
    PlanNodeStatsEstimate sourceStatsEstimate1 = PlanNodeStatsEstimate.builder().setOutputRowCount(10).build();
    PlanNodeStatsEstimate sourceStatsEstimate2 = PlanNodeStatsEstimate.builder().setOutputRowCount(20).build();
    assertEquals(getSourceTablesSizeInBytes(planBuilder.union(ImmutableListMultimap.<Symbol, Symbol>builder().put(symbol, sourceSymbol1).put(symbol, sourceSymbol2).build(), ImmutableList.of(planBuilder.tableScan(ImmutableList.of(sourceSymbol1), ImmutableMap.of(sourceSymbol1, new TestingColumnHandle("col"))), planBuilder.values(new PlanNodeId("valuesNode"), sourceSymbol2))), noLookup(), node -> {
        if (node instanceof TableScanNode) {
            return sourceStatsEstimate1;
        }
        if (node instanceof ValuesNode) {
            return sourceStatsEstimate2;
        }
        return PlanNodeStatsEstimate.unknown();
    }, planBuilder.getTypes()), 270.0);
    // join node
    assertEquals(getSourceTablesSizeInBytes(planBuilder.join(INNER, planBuilder.values(sourceSymbol1), planBuilder.values(sourceSymbol2)), noLookup(), node -> sourceStatsEstimate1, planBuilder.getTypes()), NaN);
}