@Test public void testValueExpressionRewrite() { tester().assertThat(zeroRewriter.valuesExpressionRewrite()) .on(p -> p.values( ImmutableList.<Symbol>of(p.symbol("a")), ImmutableList.of((ImmutableList.of(PlanBuilder.expression("1")))))) .matches( values(ImmutableList.of("a"), ImmutableList.of(ImmutableList.of(new LongLiteral("0"))))); }
@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 testPredicatePushDownThroughMarkDistinct() { assertPlan( "SELECT (SELECT a FROM (VALUES 1, 2, 3) t(a) WHERE a = b) FROM (VALUES 0, 1) p(b) WHERE b = 1", // TODO this could be optimized to VALUES with values from partitions anyTree( join( LEFT, ImmutableList.of(equiJoinClause("A", "B")), project(assignUniqueId("unique", filter("A = 1", values("A")))), project(filter("1 = B", values("B")))))); }
@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 testPruneCountAggregationOverScalar() { assertPlan( "SELECT count(*) FROM (SELECT sum(orderkey) FROM orders)", output( values(ImmutableList.of("_col0"), ImmutableList.of(ImmutableList.of(new LongLiteral("1")))))); assertPlan( "SELECT count(s) FROM (SELECT sum(orderkey) AS s FROM orders)", anyTree( tableScan("orders"))); assertPlan( "SELECT count(*) FROM (SELECT sum(orderkey) FROM orders GROUP BY custkey)", anyTree( tableScan("orders"))); }
@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 testQuantifiedComparisonNotEqualsAll() { String query = "SELECT orderkey, custkey FROM orders WHERE orderkey <> ALL (VALUES ROW(CAST(5 as BIGINT)), ROW(CAST(3 as BIGINT)))"; assertPlan(query, anyTree( filter("NOT S", project( semiJoin("X", "Y", "S", anyTree(tableScan("orders", ImmutableMap.of("X", "orderkey"))), anyTree(values(ImmutableMap.of("Y", 0)))))))); }
@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 testQuantifiedComparisonEqualsAny() { String query = "SELECT orderkey, custkey FROM orders WHERE orderkey = ANY (VALUES ROW(CAST(5 as BIGINT)), ROW(CAST(3 as BIGINT)))"; assertPlan(query, anyTree( filter("S", project( semiJoin("X", "Y", "S", anyTree(tableScan("orders", ImmutableMap.of("X", "orderkey"))), anyTree(values(ImmutableMap.of("Y", 0)))))))); }
@Test public void testNotAllOutputsReferenced() { tester().assertThat(new PruneJoinColumns()) .on(p -> buildProjectedJoin(p, symbol -> symbol.getName().equals("rightValue"))) .matches( strictProject( ImmutableMap.of("rightValue", PlanMatchPattern.expression("rightValue")), join( JoinNode.Type.INNER, ImmutableList.of(equiJoinClause("leftKey", "rightKey")), Optional.empty(), values(ImmutableList.of("leftKey", "leftValue")), values(ImmutableList.of("rightKey", "rightValue"))) .withExactOutputs("rightValue"))); }
@Test public void testNestedType() { // Second Aggregation data Map<String, ExpectedValueProvider<FunctionCall>> aggregationsSecond = ImmutableMap.of( "arbitrary", PlanMatchPattern.functionCall("arbitrary", false, ImmutableList.of(anySymbol())), "count", PlanMatchPattern.functionCall("count", false, ImmutableList.of(anySymbol()))); // First Aggregation data Map<String, ExpectedValueProvider<FunctionCall>> aggregationsFirst = ImmutableMap.of( "max", PlanMatchPattern.functionCall("max", false, ImmutableList.of(anySymbol()))); assertUnitPlan("SELECT count(DISTINCT a), max(b) FROM (VALUES (ROW(1, 2), 3)) t(a, b)", anyTree( aggregation(aggregationsSecond, project( aggregation(aggregationsFirst, anyTree(values(ImmutableMap.of()))))))); }
@Test public void testSemiJoinNotNeeded() { tester().assertThat(new PruneSemiJoinColumns()) .on(p -> buildProjectedSemiJoin(p, symbol -> symbol.getName().equals("leftValue"))) .matches( strictProject( ImmutableMap.of("leftValue", expression("leftValue")), values("leftKey", "leftKeyHash", "leftValue"))); }
@Test public void testNotAllColumnsReferenced() { tester().assertThat(new PruneSemiJoinFilteringSourceColumns()) .on(p -> buildSemiJoin(p, symbol -> true)) .matches( semiJoin("leftKey", "rightKey", "match", values("leftKey"), strictProject( ImmutableMap.of( "rightKey", expression("rightKey"), "rightKeyHash", expression("rightKeyHash")), values("rightKey", "rightKeyHash", "rightValue")))); }
@Test public void testWindowNotNeeded() { tester().assertThat(new PruneWindowColumns()) .on(p -> buildProjectedWindow(p, symbol -> inputSymbolNameSet.contains(symbol.getName()), alwaysTrue())) .matches( strictProject( Maps.asMap(inputSymbolNameSet, PlanMatchPattern::expression), values(inputSymbolNameList))); }
@Test public void testBasics() { tester().assertThat(new PruneOrderByInAggregation(functionRegistry)) .on(this::buildAggregation) .matches( aggregation( singleGroupingSet("key"), ImmutableMap.of( Optional.of("avg"), functionCall("avg", ImmutableList.of("input")), Optional.of("array_agg"), functionCall( "array_agg", ImmutableList.of("input"), ImmutableList.of(sort("input", SortItem.Ordering.ASCENDING, SortItem.NullOrdering.UNDEFINED)))), ImmutableMap.of(), Optional.empty(), SINGLE, values("input", "key", "keyHash", "mask"))); }