Examples with ASCENDING io.trino.sql.tree.SortItem.Ordering.ASCENDING used on opensource projects

Example 1 with ASCENDING

use of io.trino.sql.tree.SortItem.Ordering.ASCENDING in project trino by trinodb.

the class TestLogicalPlanner method testOffset.

@Test
public void testOffset() {
    assertPlan("SELECT name FROM nation OFFSET 2 ROWS", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), any(tableScan("nation", ImmutableMap.of("name", "name")))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation ORDER BY regionkey OFFSET 2 ROWS", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), anyTree(sort(ImmutableList.of(sort("regionkey", ASCENDING, LAST)), any(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation ORDER BY regionkey OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), any(topN(7, ImmutableList.of(sort("regionkey", ASCENDING, LAST)), TopNNode.Step.FINAL, anyTree(tableScan("nation", ImmutableMap.of("name", "name", "regionkey", "regionkey")))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
    assertPlan("SELECT name FROM nation OFFSET 2 ROWS FETCH NEXT 5 ROWS ONLY", any(strictProject(ImmutableMap.of("name", new ExpressionMatcher("name")), filter("row_num > BIGINT '2'", rowNumber(pattern -> pattern.partitionBy(ImmutableList.of()), limit(7, any(tableScan("nation", ImmutableMap.of("name", "name"))))).withAlias("row_num", new RowNumberSymbolMatcher())))));
}

Example 2 with ASCENDING

use of io.trino.sql.tree.SortItem.Ordering.ASCENDING in project trino by trinodb.

the class TestWindowClause method testWindowFunctionsInSelectAndOrderBy.

@Test
public void testWindowFunctionsInSelectAndOrderBy() {
    @Language("SQL") String sql = "SELECT array_agg(a) OVER (w ORDER BY a + 1), -a a FROM (VALUES 1, 2, 3) t(a) WINDOW w AS () ORDER BY max(a) OVER (w ORDER BY a + 1)";
    PlanMatchPattern pattern = anyTree(sort(ImmutableList.of(sort("max_result", ASCENDING, LAST)), any(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("order_by_window_sortkey"), ImmutableMap.of("order_by_window_sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("max_result", functionCall("max", ImmutableList.of("minus_a"))), any(project(ImmutableMap.of("order_by_window_sortkey", expression("minus_a + 1")), project(ImmutableMap.of("minus_a", expression("-a")), window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("select_window_sortkey"), ImmutableMap.of("select_window_sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("array_agg_result", functionCall("array_agg", ImmutableList.of("a"))), anyTree(project(ImmutableMap.of("select_window_sortkey", expression("a + 1")), anyTree(values("a"))))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 3 with ASCENDING

use of io.trino.sql.tree.SortItem.Ordering.ASCENDING in project trino by trinodb.

the class TestPushTopNIntoTableScan method testPushSingleTopNIntoTableScanNotGuaranteed.

@Test
public void testPushSingleTopNIntoTableScanNotGuaranteed() {
    try (RuleTester ruleTester = defaultRuleTester()) {
        MockConnectorTableHandle connectorHandle = new MockConnectorTableHandle(TEST_SCHEMA_TABLE);
        // make the mock connector return a new connectorHandle
        MockConnectorFactory.ApplyTopN applyTopN = (session, handle, topNCount, sortItems, tableAssignments) -> Optional.of(new TopNApplicationResult<>(connectorHandle, false, false));
        MockConnectorFactory mockFactory = createMockFactory(assignments, Optional.of(applyTopN));
        ruleTester.getQueryRunner().createCatalog(MOCK_CATALOG, mockFactory, ImmutableMap.of());
        ruleTester.assertThat(new PushTopNIntoTableScan(ruleTester.getMetadata())).on(p -> {
            Symbol dimension = p.symbol(dimensionName, VARCHAR);
            Symbol metric = p.symbol(metricName, BIGINT);
            return p.topN(1, ImmutableList.of(dimension), p.tableScan(TEST_TABLE_HANDLE, ImmutableList.of(dimension, metric), ImmutableMap.of(dimension, dimensionColumn, metric, metricColumn)));
        }).withSession(MOCK_SESSION).matches(topN(1, ImmutableList.of(sort(dimensionName, ASCENDING, FIRST)), TopNNode.Step.SINGLE, tableScan(connectorHandle::equals, TupleDomain.all(), ImmutableMap.of(dimensionName, dimensionColumn::equals, metricName, metricColumn::equals))));
    }
}

Example 4 with ASCENDING

use of io.trino.sql.tree.SortItem.Ordering.ASCENDING in project trino by trinodb.

the class TestWindowClause method testWindowWithFrameCoercions.

@Test
public void testWindowWithFrameCoercions() {
    @Language("SQL") String sql = "SELECT a old_a, 2e0 a FROM (VALUES -100, -99, -98) t(a) WINDOW w AS (ORDER BY a + 1) ORDER BY count(*) OVER (w RANGE BETWEEN CURRENT ROW AND a + 1e0 FOLLOWING)";
    PlanMatchPattern pattern = anyTree(sort(ImmutableList.of(sort("count_result", ASCENDING, LAST)), project(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("sortkey"), ImmutableMap.of("sortkey", SortOrder.ASC_NULLS_LAST))).addFunction("count_result", functionCall("count", ImmutableList.of()), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("count"), fromTypes()), windowFrame(RANGE, CURRENT_ROW, Optional.empty(), Optional.empty(), FOLLOWING, Optional.of("frame_bound"), Optional.of("coerced_sortkey"))), project(ImmutableMap.of("frame_bound", expression(new FunctionCall(QualifiedName.of("$operator$add"), ImmutableList.of(new SymbolReference("coerced_sortkey"), new SymbolReference("frame_offset"))))), project(ImmutableMap.of("coerced_sortkey", expression("CAST(sortkey AS double)")), node(FilterNode.class, project(project(ImmutableMap.of(// sort key based on "a" in source scope
    "sortkey", expression("a + 1"), // frame offset based on "a" in output scope
    "frame_offset", expression("new_a + 1E0")), project(ImmutableMap.of("new_a", expression("2E0")), project(project(values("a")))))))))))));
    assertPlan(sql, CREATED, pattern);
}

Example 5 with ASCENDING

use of io.trino.sql.tree.SortItem.Ordering.ASCENDING in project trino by trinodb.

the class TestPushTopNIntoTableScan method testPushPartialTopNIntoTableScanNotGuaranteed.

@Test
public void testPushPartialTopNIntoTableScanNotGuaranteed() {
    try (RuleTester ruleTester = defaultRuleTester()) {
        MockConnectorTableHandle connectorHandle = new MockConnectorTableHandle(TEST_SCHEMA_TABLE);
        // make the mock connector return a new connectorHandle
        MockConnectorFactory.ApplyTopN applyTopN = (session, handle, topNCount, sortItems, tableAssignments) -> Optional.of(new TopNApplicationResult<>(connectorHandle, false, false));
        MockConnectorFactory mockFactory = createMockFactory(assignments, Optional.of(applyTopN));
        ruleTester.getQueryRunner().createCatalog(MOCK_CATALOG, mockFactory, ImmutableMap.of());
        ruleTester.assertThat(new PushTopNIntoTableScan(ruleTester.getMetadata())).on(p -> {
            Symbol dimension = p.symbol(dimensionName, VARCHAR);
            Symbol metric = p.symbol(metricName, BIGINT);
            return p.topN(1, ImmutableList.of(dimension), TopNNode.Step.PARTIAL, p.tableScan(TEST_TABLE_HANDLE, ImmutableList.of(dimension, metric), ImmutableMap.of(dimension, dimensionColumn, metric, metricColumn)));
        }).withSession(MOCK_SESSION).matches(topN(1, ImmutableList.of(sort(dimensionName, ASCENDING, FIRST)), TopNNode.Step.PARTIAL, tableScan(connectorHandle::equals, TupleDomain.all(), ImmutableMap.of(dimensionName, dimensionColumn::equals, metricName, metricColumn::equals))));
    }
}