Refine search
@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 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 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 testPredicateFromSourceSideNotPropagatesToFilterSideOfSemiJoinIfNotIn() { assertPlan("SELECT quantity FROM (SELECT * FROM lineitem WHERE orderkey NOT IN (SELECT orderkey FROM orders) AND orderkey > 2)", anyTree( semiJoin("LINE_ORDER_KEY", "ORDERS_ORDER_KEY", "SEMI_JOIN_RESULT", project( filter("LINE_ORDER_KEY > BIGINT '2'", tableScan("lineitem", ImmutableMap.of( "LINE_ORDER_KEY", "orderkey", "LINE_QUANTITY", "quantity")))), node(ExchangeNode.class, // NO filter here project( tableScan("orders", ImmutableMap.of("ORDERS_ORDER_KEY", "orderkey"))))))); }
@Test public void testDistinctOverConstants() { assertPlan("SELECT count(*), count(distinct orderstatus) FROM (SELECT * FROM orders WHERE orderstatus = 'F')", anyTree( markDistinct( "is_distinct", ImmutableList.of("orderstatus"), "hash", anyTree( project(ImmutableMap.of("hash", expression("combine_hash(bigint '0', coalesce(\"$operator$hash_code\"(orderstatus), 0))")), tableScan("orders", ImmutableMap.of("orderstatus", "orderstatus"))))))); }
@Test public void testNonDeterministicPredicateDoesNotPropagateFromFilteringSideToSourceSideOfSemiJoin() { assertPlan("SELECT * FROM lineitem WHERE orderkey IN (SELECT orderkey FROM orders WHERE orderkey = random(5))", anyTree( semiJoin("LINE_ORDER_KEY", "ORDERS_ORDER_KEY", "SEMI_JOIN_RESULT", // NO filter here project( tableScan("lineitem", ImmutableMap.of( "LINE_ORDER_KEY", "orderkey"))), node(ExchangeNode.class, project( filter("ORDERS_ORDER_KEY = CAST(random(5) AS bigint)", tableScan("orders", ImmutableMap.of("ORDERS_ORDER_KEY", "orderkey")))))))); }
@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 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 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 testPredicateFromFilterSideNotPropagatesToSourceSideOfSemiJoinUsedInProjection() { assertPlan("SELECT orderkey IN (SELECT orderkey FROM orders WHERE orderkey > 2) FROM lineitem", anyTree( semiJoin("LINE_ORDER_KEY", "ORDERS_ORDER_KEY", "SEMI_JOIN_RESULT", // NO filter here project( tableScan("lineitem", ImmutableMap.of( "LINE_ORDER_KEY", "orderkey"))), anyTree( filter("ORDERS_ORDER_KEY > BIGINT '2'", tableScan("orders", ImmutableMap.of("ORDERS_ORDER_KEY", "orderkey"))))))); }
@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 testDoubleNestedCorrelatedSubqueries() { assertPlan( "SELECT orderkey FROM orders o " + "WHERE 3 IN (SELECT o.custkey FROM lineitem l WHERE (SELECT l.orderkey = o.orderkey))", LogicalPlanner.Stage.OPTIMIZED, anyTree( filter("OUTER_FILTER", apply(ImmutableList.of("C", "O"), ImmutableMap.of("OUTER_FILTER", expression("THREE IN (C)")), project(ImmutableMap.of("THREE", expression("BIGINT '3'")), tableScan("orders", ImmutableMap.of( "O", "orderkey", "C", "custkey"))), project( any( any( tableScan("lineitem", ImmutableMap.of("L", "orderkey")))))))), MorePredicates.<PlanOptimizer>isInstanceOfAny(AddLocalExchanges.class, CheckSubqueryNodesAreRewritten.class).negate()); }
@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 testPredicateFromFilterSideNotPropagatesToSourceSideOfSemiJoinIfNotIn() { assertPlan("SELECT quantity FROM (SELECT * FROM lineitem WHERE orderkey NOT IN (SELECT orderkey FROM orders WHERE orderkey > 2))", anyTree( semiJoin("LINE_ORDER_KEY", "ORDERS_ORDER_KEY", "SEMI_JOIN_RESULT", // There should be no Filter above table scan, because we don't know whether SemiJoin's filtering source is empty. // And filter would filter out NULLs from source side which is not what we need then. project( tableScan("lineitem", ImmutableMap.of( "LINE_ORDER_KEY", "orderkey", "LINE_QUANTITY", "quantity"))), anyTree( filter("ORDERS_ORDER_KEY > BIGINT '2'", tableScan("orders", ImmutableMap.of("ORDERS_ORDER_KEY", "orderkey"))))))); }
@Test public void testPredicateOnNonDeterministicSymbolsPushedDown() { assertPlan( "SELECT * FROM (" + "SELECT random_column, orderkey, rank() OVER (PARTITION BY random_column ORDER BY orderdate ASC)" + "FROM (select round(custkey*rand()) random_column, * from orders) " + ") WHERE random_column > 100", anyTree( node(WindowNode.class, anyTree( filter("\"ROUND\" > 1E2", project(ImmutableMap.of("ROUND", expression("round(CAST(CUST_KEY AS double) * rand())")), tableScan( "orders", ImmutableMap.of("CUST_KEY", "custkey")))))))); }
@Test public void testPushDownBothArguments() { assertRuleApplication() .on(p -> p.join(LEFT, p.values(p.symbol("wkt", VARCHAR)), p.values(p.symbol("lat"), p.symbol("lng")), expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"))) .matches( spatialLeftJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_geometryfromtext", PlanMatchPattern.expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))), project(ImmutableMap.of("st_point", PlanMatchPattern.expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))))); }
@Test public void testPushDownOppositeOrder() { assertRuleApplication() .on(p -> p.join(LEFT, p.values(p.symbol("lat"), p.symbol("lng")), p.values(p.symbol("wkt", VARCHAR)), expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"))) .matches( spatialLeftJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_point", PlanMatchPattern.expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))), project(ImmutableMap.of("st_geometryfromtext", PlanMatchPattern.expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))))); }
@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 testPushDownBothArguments() { assertRuleApplication() .on(p -> p.filter(PlanBuilder.expression("ST_Contains(ST_GeometryFromText(wkt), ST_Point(lng, lat))"), p.join(INNER, p.values(p.symbol("wkt", VARCHAR)), p.values(p.symbol("lat"), p.symbol("lng"))))) .matches( spatialJoin("ST_Contains(st_geometryfromtext, st_point)", project(ImmutableMap.of("st_geometryfromtext", expression("ST_GeometryFromText(wkt)")), values(ImmutableMap.of("wkt", 0))), project(ImmutableMap.of("st_point", expression("ST_Point(lng, lat)")), values(ImmutableMap.of("lat", 0, "lng", 1))))); }