Refine search
@Test public void testInnerInequalityJoinNoEquiJoinConjuncts() { assertPlan("SELECT 1 FROM orders o JOIN lineitem l ON o.orderkey < l.orderkey", anyTree( filter("O_ORDERKEY < L_ORDERKEY", join(INNER, ImmutableList.of(), Optional.empty(), tableScan("orders", ImmutableMap.of("O_ORDERKEY", "orderkey")), any(tableScan("lineitem", ImmutableMap.of("L_ORDERKEY", "orderkey"))))))); }
@Test public void testNotIntersects() { assertPlan("SELECT b.name, a.name " + "FROM (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS a (wkt, name), (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS b (wkt, name) " + "WHERE NOT ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", anyTree( filter("NOT ST_Intersects(ST_GeometryFromText(cast(wkt_a as varchar)), ST_GeometryFromText(cast(wkt_b as varchar)))", join(JoinNode.Type.INNER, emptyList(), anyTree(values(ImmutableMap.of("wkt_a", 0, "name_a", 1))), values(ImmutableMap.of("wkt_b", 0, "name_b", 1)))))); }
private void testReplicateNoEquiCriteria(Type joinType) { assertDetermineJoinDistributionType() .on(p -> p.join( joinType, p.values(ImmutableList.of(p.symbol("A1")), ImmutableList.of(expressions("10"), expressions("11"))), p.values(ImmutableList.of(p.symbol("B1")), ImmutableList.of(expressions("50"), expressions("11"))), ImmutableList.of(), ImmutableList.of(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT)), Optional.of(expression("A1 * B1 > 100")))) .setSystemProperty(JOIN_DISTRIBUTION_TYPE, JoinDistributionType.PARTITIONED.name()) .matches(join( joinType, ImmutableList.of(), Optional.of("A1 * B1 > 100"), Optional.of(DistributionType.REPLICATED), values(ImmutableMap.of("A1", 0)), values(ImmutableMap.of("B1", 0)))); }
@Test public void testGiveUpOnCrossJoin() { assertPlan("SELECT o.orderkey FROM part p, orders o, lineitem l WHERE l.orderkey = o.orderkey", anyTree( join(INNER, ImmutableList.of(equiJoinClause("O_ORDERKEY", "L_ORDERKEY")), anyTree( join(INNER, ImmutableList.of(), tableScan("part"), anyTree(tableScan("orders", ImmutableMap.of("O_ORDERKEY", "orderkey"))))), anyTree(tableScan("lineitem", ImmutableMap.of("L_ORDERKEY", "orderkey")))))); }
private void testDetermineDistributionType(JoinDistributionType sessionDistributedJoin, Type joinType, DistributionType expectedDistribution) { assertDetermineJoinDistributionType() .on(p -> p.join( joinType, p.values(ImmutableList.of(p.symbol("A1")), ImmutableList.of(expressions("10"), expressions("11"))), p.values(ImmutableList.of(p.symbol("B1")), ImmutableList.of(expressions("50"), expressions("11"))), ImmutableList.of(new JoinNode.EquiJoinClause(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT))), ImmutableList.of(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT)), Optional.empty())) .setSystemProperty(JOIN_DISTRIBUTION_TYPE, sessionDistributedJoin.name()) .matches(join( joinType, ImmutableList.of(equiJoinClause("B1", "A1")), Optional.empty(), Optional.of(expectedDistribution), values(ImmutableMap.of("B1", 0)), values(ImmutableMap.of("A1", 0)))); }
@Test public void testNotContains() { assertPlan("SELECT b.name, a.name " + "FROM " + POINTS_SQL + ", " + POLYGONS_SQL + " " + "WHERE NOT ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( filter("NOT ST_Contains(ST_GeometryFromText(cast(wkt as varchar)), ST_Point(lng, lat))", join(JoinNode.Type.INNER, emptyList(), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1))), values(ImmutableMap.of("wkt", 0)))))); }
@Test public void testLeftConvertedToInnerInequalityJoinNoEquiJoinConjuncts() { assertPlan("SELECT 1 FROM orders o LEFT JOIN lineitem l ON o.orderkey < l.orderkey WHERE l.orderkey IS NOT NULL", anyTree( filter("O_ORDERKEY < L_ORDERKEY", join(INNER, ImmutableList.of(), Optional.empty(), tableScan("orders", ImmutableMap.of("O_ORDERKEY", "orderkey")), any( filter("NOT (L_ORDERKEY IS NULL)", tableScan("lineitem", ImmutableMap.of("L_ORDERKEY", "orderkey")))))))); }
private void testRepartitionRightOuter(JoinDistributionType sessionDistributedJoin, Type joinType) { assertDetermineJoinDistributionType() .on(p -> p.join( joinType, p.values(ImmutableList.of(p.symbol("A1")), ImmutableList.of(expressions("10"), expressions("11"))), p.values(ImmutableList.of(p.symbol("B1")), ImmutableList.of(expressions("50"), expressions("11"))), ImmutableList.of(new JoinNode.EquiJoinClause(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT))), ImmutableList.of(p.symbol("A1", BIGINT), p.symbol("B1", BIGINT)), Optional.empty())) .setSystemProperty(JOIN_DISTRIBUTION_TYPE, sessionDistributedJoin.name()) .matches(join( joinType, ImmutableList.of(equiJoinClause("A1", "B1")), Optional.empty(), Optional.of(DistributionType.PARTITIONED), values(ImmutableMap.of("A1", 0)), values(ImmutableMap.of("B1", 0)))); }
@Test public void testIntersectsWithEquiClause() { assertPlan("SELECT b.name, a.name " + "FROM (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS a (wkt, name), (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS b (wkt, name) " + "WHERE a.name = b.name AND ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", anyTree( join(JoinNode.Type.INNER, ImmutableList.of(equiJoinClause("name_a", "name_b")), Optional.of("ST_Intersects(ST_GeometryFromText(cast(wkt_a as varchar)), ST_GeometryFromText(cast(wkt_B as varchar)))"), anyTree(values(ImmutableMap.of("wkt_a", 0, "name_a", 1))), anyTree(values(ImmutableMap.of("wkt_b", 0, "name_b", 1)))))); }
@Test public void testNotContains() { assertPlan("SELECT b.name, a.name " + "FROM " + POINTS_SQL + ", " + POLYGONS_SQL + " " + "WHERE NOT ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( filter("NOT ST_Contains(ST_GeometryFromText(cast(wkt as varchar)), ST_Point(lng, lat))", join(JoinNode.Type.INNER, emptyList(), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1))), values(ImmutableMap.of("wkt", 0)))))); }
@Test public void testEliminateSimpleCrossJoin() { assertPlan("SELECT * FROM part p, orders o, lineitem l WHERE p.partkey = l.partkey AND l.orderkey = o.orderkey", anyTree( join(INNER, ImmutableList.of(equiJoinClause("L_ORDERKEY", "O_ORDERKEY")), anyTree( join(INNER, ImmutableList.of(equiJoinClause("P_PARTKEY", "L_PARTKEY")), anyTree(PART_TABLESCAN), anyTree(LINEITEM_TABLESCAN))), anyTree(ORDERS_TABLESCAN)))); }
@Test public void testNotIntersects() { assertPlan("SELECT b.name, a.name " + "FROM (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS a (wkt, name), (VALUES ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))', 'a')) AS b (wkt, name) " + "WHERE NOT ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", anyTree( filter("NOT ST_Intersects(ST_GeometryFromText(cast(wkt_a as varchar)), ST_GeometryFromText(cast(wkt_b as varchar)))", join(JoinNode.Type.INNER, emptyList(), anyTree(values(ImmutableMap.of("wkt_a", 0, "name_a", 1))), values(ImmutableMap.of("wkt_b", 0, "name_b", 1)))))); }
@Test public void testJoin() { assertPlan( "SELECT *\n" + "FROM (\n" + " SELECT EXTRACT(DAY FROM DATE '2017-01-01')\n" + ") t\n" + "CROSS JOIN (VALUES 1)", anyTree( join(INNER, ImmutableList.of(), Optional.empty(), project( ImmutableMap.of("X", expression("BIGINT '1'")), values(ImmutableMap.of())), values(ImmutableMap.of())))); }
@Test public void testContainsWithEquiClause() { assertPlan("SELECT b.name, a.name " + "FROM " + POINTS_SQL + ", " + POLYGONS_SQL + " " + "WHERE a.name = b.name AND ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))", anyTree( join(JoinNode.Type.INNER, ImmutableList.of(equiJoinClause("name_a", "name_b")), Optional.of("ST_Contains(ST_GeometryFromText(cast(wkt as varchar)), ST_Point(lng, lat))"), anyTree(values(ImmutableMap.of("lng", 0, "lat", 1, "name_a", 2))), anyTree(values(ImmutableMap.of("wkt", 0, "name_b", 1)))))); }
@Test public void testEliminateCrossJoinWithNonEqualityCondition() { assertPlan("SELECT o.orderkey FROM part p, orders o, lineitem l " + "WHERE p.partkey = l.partkey AND l.orderkey = o.orderkey AND p.partkey <> o.orderkey AND p.name < l.comment", anyTree( join(INNER, ImmutableList.of(equiJoinClause("L_ORDERKEY", "O_ORDERKEY")), anyTree( join(INNER, ImmutableList.of(equiJoinClause("P_PARTKEY", "L_PARTKEY")), Optional.of("P_NAME < cast(L_COMMENT AS varchar(55))"), anyTree(PART_WITH_NAME_TABLESCAN), anyTree(filter("L_PARTKEY <> L_ORDERKEY", LINEITEM_WITH_COMMENT_TABLESCAN)))), anyTree(ORDERS_TABLESCAN)))); }
@Test public void testJoin() { assertPlan("SELECT o.orderkey FROM orders o, lineitem l WHERE l.orderkey = o.orderkey", anyTree( join(INNER, ImmutableList.of(equiJoinClause("ORDERS_OK", "LINEITEM_OK")), any( tableScan("orders", ImmutableMap.of("ORDERS_OK", "orderkey"))), anyTree( tableScan("lineitem", ImmutableMap.of("LINEITEM_OK", "orderkey")))))); }
@Test public void testJoinWithOrderBySameKey() { assertPlan("SELECT o.orderkey FROM orders o, lineitem l WHERE l.orderkey = o.orderkey ORDER BY l.orderkey ASC, o.orderkey ASC", anyTree( join(INNER, ImmutableList.of(equiJoinClause("ORDERS_OK", "LINEITEM_OK")), any( tableScan("orders", ImmutableMap.of("ORDERS_OK", "orderkey"))), anyTree( tableScan("lineitem", ImmutableMap.of("LINEITEM_OK", "orderkey")))))); }
@Test public void testCorrelatedScalarAggregationRewriteToLeftOuterJoin() { assertPlan( "SELECT orderkey FROM orders WHERE EXISTS(SELECT 1 WHERE orderkey = 3)", // EXISTS maps to count(*) > 0 anyTree( filter("FINAL_COUNT > BIGINT '0'", aggregation(ImmutableMap.of("FINAL_COUNT", functionCall("count", ImmutableList.of("NON_NULL"))), join(LEFT, ImmutableList.of(), Optional.of("BIGINT '3' = ORDERKEY"), any( tableScan("orders", ImmutableMap.of("ORDERKEY", "orderkey"))), project(ImmutableMap.of("NON_NULL", expression("true")), node(ValuesNode.class))))))); }
@Test public void testJoinOutputPruning() { assertPlan("SELECT nationkey FROM nation JOIN region ON nation.regionkey = region.regionkey", anyTree( join(INNER, ImmutableList.of(equiJoinClause("REGIONKEY_LEFT", "REGIONKEY_RIGHT")), anyTree( tableScan("nation", ImmutableMap.of("REGIONKEY_LEFT", "regionkey", "NATIONKEY", "nationkey"))), anyTree( tableScan("region", ImmutableMap.of("REGIONKEY_RIGHT", "regionkey"))))) .withNumberOfOutputColumns(1) .withOutputs(ImmutableList.of("NATIONKEY"))); }