Refine search
@Test public void testStrictProject() { assertMinimallyOptimizedPlan("SELECT orderkey, 1 + orderkey FROM lineitem", output(ImmutableList.of("ORDERKEY", "EXPRESSION"), strictProject(ImmutableMap.of("EXPRESSION", expression("CAST(1 AS BIGINT) + ORDERKEY"), "ORDERKEY", expression("ORDERKEY")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey"))))); }
@Test public void testValueNotNeeded() { tester().assertThat(new PruneSemiJoinColumns()) .on(p -> buildProjectedSemiJoin(p, symbol -> symbol.getName().equals("match"))) .matches( strictProject( ImmutableMap.of("match", expression("match")), semiJoin("leftKey", "rightKey", "match", strictProject( ImmutableMap.of( "leftKey", expression("leftKey"), "leftKeyHash", expression("leftKeyHash")), values("leftKey", "leftKeyHash", "leftValue")), values("rightKey")))); }
private void testPointExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
private void assertExistsRewrittenToAggregationAboveJoin(@Language("SQL") String actual, @Language("SQL") String expected, boolean extraAggregation) { Consumer<Plan> singleStreamingAggregationValidator = plan -> assertEquals(countSingleStreamingAggregations(plan), 1); Consumer<Plan> finalAggregationValidator = plan -> assertEquals(countFinalAggregationNodes(plan), extraAggregation ? 1 : 0); assertions.assertQueryAndPlan(actual, expected, anyTree( aggregation( ImmutableMap.of("COUNT", functionCall("count", ImmutableList.of("NON_NULL"))), SINGLE, node(JoinNode.class, anyTree( node(ValuesNode.class)), anyTree( node(ProjectNode.class, anyTree( node(ValuesNode.class))) .withAlias("NON_NULL", expression("true")))))), singleStreamingAggregationValidator.andThen(finalAggregationValidator)); }
@Test public void testPartitioningColumnAndHashWithoutIdentityMappingInProjection() .addInputsSet(a, b, h) .fixedHashDistributionParitioningScheme( ImmutableList.of(a, b, h), ImmutableList.of(b), h))); }) project( values( ImmutableList.of("a", "b", "h")) ).withNumberOfOutputColumns(5) .withAlias("b", expression("b")) .withAlias("h", expression("h")) .withAlias("a_times_5", expression("a * 5")) .withAlias("b_times_5", expression("b * 5")) .withAlias("h_times_5", expression("h * 5"))) ).withNumberOfOutputColumns(3) .withExactOutputs("a_times_5", "b_times_5", "h_times_5"));
@Test public void testIdentityAliasFromProject() { assertMinimallyOptimizedPlan("SELECT orderkey, 1 + orderkey FROM lineitem", output(ImmutableList.of("ORDERKEY", "EXPRESSION"), project(ImmutableMap.of("ORDERKEY", expression("ORDERKEY"), "EXPRESSION", expression("CAST(1 AS bigint) + ORDERKEY")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey"))))); }
@Test public void testNotAllInputsReferenced() { tester().assertThat(new PruneLimitColumns()) .on(p -> buildProjectedLimit(p, symbol -> symbol.getName().equals("b"))) .matches( strictProject( ImmutableMap.of("b", expression("b")), limit( 1, strictProject( ImmutableMap.of("b", expression("b")), values("a", "b"))))); }
@Test public void testSimpleMultipleInputs() exchange( project( values(ImmutableList.of("a"))) .withAlias("x1", expression("3")), project( values(ImmutableList.of("b"))) .withAlias("x2", expression("3")))
private void testPointAndRadiusExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
@Test(expectedExceptions = {IllegalStateException.class}, expectedExceptionsMessageRegExp = "missing expression for alias .*") public void testProjectLimitsScope() { assertMinimallyOptimizedPlan("SELECT 1 + orderkey FROM lineitem", output(ImmutableList.of("ORDERKEY"), project(ImmutableMap.of("EXPRESSION", expression("CAST(1 AS bigint) + ORDERKEY")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey"))))); } }
@Test public void testNotAllInputsReferenced() { tester().assertThat(new PruneFilterColumns()) .on(p -> buildProjectedFilter(p, symbol -> symbol.getName().equals("b"))) .matches( strictProject( ImmutableMap.of("b", expression("b")), filter( "b > 5", strictProject( ImmutableMap.of("b", expression("b")), values("a", "b"))))); }
private void testRadiusExpressionInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("a", GEOMETRY), p.symbol("name_a")), p.values(p.symbol("b", GEOMETRY), p.symbol("name_b"), p.symbol("r"))))) .matches( spatialJoin(newFilter, values(ImmutableMap.of("a", 0, "name_a", 1)), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("b", 0, "name_b", 1, "r", 2))))); }
@Test public void testStrictProjectExtraSymbols() { assertFails(() -> assertMinimallyOptimizedPlan("SELECT discount, orderkey, 1 + orderkey FROM lineitem", output(ImmutableList.of("ORDERKEY", "EXPRESSION"), strictProject(ImmutableMap.of("EXPRESSION", expression("1 + ORDERKEY"), "ORDERKEY", expression("ORDERKEY")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey")))))); }
spatialJoin("st_contains(g1, g3)", Optional.of(KDB_TREE_JSON), anyTree(unnest(exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, project(ImmutableMap.of("p1", expression(format("spatial_partitions(cast('%s' as kdbtree), g1)", KDB_TREE_JSON))), project(ImmutableMap.of("g1", expression("ST_GeometryFromText(cast(name_a1 as varchar))")), tableScan("region", ImmutableMap.of("name_a1", "name")))), project(ImmutableMap.of("p2", expression(format("spatial_partitions(cast('%s' as kdbtree), g2)", KDB_TREE_JSON))), project(ImmutableMap.of("g2", expression("ST_GeometryFromText(cast(name_a2 as varchar))")), tableScan("nation", ImmutableMap.of("name_a2", "name"))))))), anyTree(unnest( project(ImmutableMap.of("p3", expression(format("spatial_partitions(cast('%s' as kdbtree), g3)", KDB_TREE_JSON))), project(ImmutableMap.of("g3", expression("ST_GeometryFromText(cast(name_b as varchar))")), tableScan("customer", ImmutableMap.of("name_b", "name"))))))))); spatialJoin("st_contains(g1, g2)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("p1", expression(format("spatial_partitions(cast('%s' as kdbtree), g1)", KDB_TREE_JSON))), project(ImmutableMap.of("g1", expression("ST_GeometryFromText(cast(name_a as varchar))")), tableScan("customer", ImmutableMap.of("name_a", "name")))))), anyTree(unnest(exchange(ExchangeNode.Scope.REMOTE, ExchangeNode.Type.REPARTITION, project(ImmutableMap.of("p2", expression(format("spatial_partitions(cast('%s' as kdbtree), g2)", KDB_TREE_JSON))), project(ImmutableMap.of("g2", expression("ST_GeometryFromText(cast(name_b1 as varchar))")), tableScan("region", ImmutableMap.of("name_b1", "name")))), project(ImmutableMap.of("p3", expression(format("spatial_partitions(cast('%s' as kdbtree), g3)", KDB_TREE_JSON))), project(ImmutableMap.of("g3", expression("ST_GeometryFromText(cast(name_b2 as varchar))")), tableScan("nation", ImmutableMap.of("name_b2", "name"))))))))));
private void testPointExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
@Test public void testAliasConstantFromProject() { assertMinimallyOptimizedPlan("SELECT orderkey, 2 FROM lineitem", output(ImmutableList.of("ORDERKEY", "TWO"), project(ImmutableMap.of("TWO", expression("2")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey"))))); }
@Test public void testSpatialJoinIntersects() { // broadcast 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 ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", anyTree( spatialJoin("st_intersects(geometry_a, geometry_b)", project(ImmutableMap.of("geometry_a", expression("ST_GeometryFromText(cast(wkt_a as varchar))")), anyTree(values(ImmutableMap.of("wkt_a", 0)))), anyTree(project(ImmutableMap.of("geometry_b", expression("ST_GeometryFromText(cast(wkt_b as varchar))")), anyTree(values(ImmutableMap.of("wkt_b", 0)))))))); // distributed assertDistributedPlan("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 ST_Intersects(ST_GeometryFromText(a.wkt), ST_GeometryFromText(b.wkt))", withSpatialPartitioning("default.kdb_tree"), anyTree( spatialJoin("st_intersects(geometry_a, geometry_b)", Optional.of(KDB_TREE_JSON), anyTree(unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), geometry_a)", KDB_TREE_JSON))), project(ImmutableMap.of("geometry_a", expression("ST_GeometryFromText(cast(wkt_a as varchar))")), anyTree(values(ImmutableMap.of("wkt_a", 0))))))), anyTree( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), geometry_b)", KDB_TREE_JSON))), project(ImmutableMap.of("geometry_b", expression("ST_GeometryFromText(cast(wkt_b as varchar))")), anyTree(values(ImmutableMap.of("wkt_b", 0))))))))); }
private void testPointAndRadiusExpressionsInDistanceQuery(String filter, String newFilter, String radiusExpression) { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression(filter), p.join(INNER, p.values(p.symbol("lat_a"), p.symbol("lng_a"), p.symbol("name_a")), p.values(p.symbol("lat_b"), p.symbol("lng_b"), p.symbol("name_b"))))) .matches( spatialJoin(newFilter, project(ImmutableMap.of("point_a", expression("ST_Point(lng_a, lat_a)")), values(ImmutableMap.of("lat_a", 0, "lng_a", 1, "name_a", 2))), project(ImmutableMap.of("point_b", expression("ST_Point(lng_b, lat_b)")), project(ImmutableMap.of("radius", expression(radiusExpression)), values(ImmutableMap.of("lat_b", 0, "lng_b", 1, "name_b", 2)))))); }
@Test public void testAliasExpressionFromProject() { assertMinimallyOptimizedPlan("SELECT orderkey, 1 + orderkey FROM lineitem", output(ImmutableList.of("ORDERKEY", "EXPRESSION"), project(ImmutableMap.of("EXPRESSION", expression("CAST(1 AS bigint) + ORDERKEY")), tableScan("lineitem", ImmutableMap.of("ORDERKEY", "orderkey"))))); }
@Test public void testDistanceQuery() anyTree( spatialJoin("st_distance(st_point_a, st_point_b) <= radius", project(ImmutableMap.of("st_point_a", expression("ST_Point(cast(a_lng as double), cast(a_lat as double))")), anyTree(values(ImmutableMap.of("a_lng", 0, "a_lat", 1)))), anyTree(project(ImmutableMap.of("st_point_b", expression("ST_Point(cast(b_lng as double), cast(b_lat as double))"), "radius", expression("3.1e0")), anyTree(values(ImmutableMap.of("b_lng", 0, "b_lat", 1)))))))); anyTree( spatialJoin("st_distance(st_point_a, st_point_b) <= radius", project(ImmutableMap.of("st_point_a", expression("ST_Point(cast(a_lng as double), cast(a_lat as double))")), anyTree(values(ImmutableMap.of("a_lng", 0, "a_lat", 1)))), anyTree(project(ImmutableMap.of("st_point_b", expression("ST_Point(cast(b_lng as double), cast(b_lat as double))"), "radius", expression("3e2 / (111.321e3 * cos(radians(cast(b_lat as double))))")), anyTree(values(ImmutableMap.of("b_lng", 0, "b_lat", 1)))))))); anyTree( unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), st_point_a)", KDB_TREE_JSON))), project(ImmutableMap.of("st_point_a", expression("ST_Point(cast(a_lng as double), cast(a_lat as double))")), anyTree(values(ImmutableMap.of("a_lng", 0, "a_lat", 1))))))), anyTree( unnest( project(ImmutableMap.of("partitions", expression(format("spatial_partitions(cast('%s' as kdbtree), st_point_b, 3.1e0)", KDB_TREE_JSON)), "radius", expression("3.1e0")), project(ImmutableMap.of("st_point_b", expression("ST_Point(cast(b_lng as double), cast(b_lat as double))")), anyTree(values(ImmutableMap.of("b_lng", 0, "b_lat", 1))))))))));