Search in sources :

Example 6 with CREATED

use of io.trino.sql.planner.LogicalPlanner.Stage.CREATED in project trino by trinodb.

the class TestWindowFrameRange method testFramePrecedingWithSortKeyCoercions.

@Test
public void testFramePrecedingWithSortKeyCoercions() {
    @Language("SQL") String sql = "SELECT array_agg(key) OVER(ORDER BY key RANGE x PRECEDING) " + "FROM (VALUES (1, 1.1), (2, 2.2)) t(key, x)";
    PlanMatchPattern pattern = anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("key"), ImmutableMap.of("key", SortOrder.ASC_NULLS_LAST))).addFunction("array_agg_result", functionCall("array_agg", ImmutableList.of("key")), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("array_agg"), fromTypes(INTEGER)), windowFrame(RANGE, PRECEDING, Optional.of("frame_start_value"), Optional.of("key_for_frame_start_comparison"), CURRENT_ROW, Optional.empty(), Optional.empty())), project(ImmutableMap.of("key_for_frame_start_comparison", expression("CAST(key AS decimal(12, 1))")), project(ImmutableMap.of("frame_start_value", expression(new FunctionCall(QualifiedName.of("$operator$subtract"), ImmutableList.of(new SymbolReference("key_for_frame_start_calculation"), new SymbolReference("x"))))), project(ImmutableMap.of("key_for_frame_start_calculation", expression("CAST(key AS decimal(10, 0))")), filter("IF((x >= CAST(0 AS DECIMAL(2,1))), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", anyTree(values(ImmutableList.of("key", "x"), ImmutableList.of(ImmutableList.of(new LongLiteral("1"), new DecimalLiteral("1.1")), ImmutableList.of(new LongLiteral("2"), new DecimalLiteral("2.2")))))))))));
    assertPlan(sql, CREATED, pattern);
}
Also used : CREATED(io.trino.sql.planner.LogicalPlanner.Stage.CREATED) CURRENT_ROW(io.trino.sql.tree.FrameBound.Type.CURRENT_ROW) TypeSignatureProvider.fromTypes(io.trino.sql.analyzer.TypeSignatureProvider.fromTypes) PlanMatchPattern.window(io.trino.sql.planner.assertions.PlanMatchPattern.window) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) Test(org.testng.annotations.Test) PlanMatchPattern.filter(io.trino.sql.planner.assertions.PlanMatchPattern.filter) FOLLOWING(io.trino.sql.tree.FrameBound.Type.FOLLOWING) PlanMatchPattern.specification(io.trino.sql.planner.assertions.PlanMatchPattern.specification) ImmutableList(com.google.common.collect.ImmutableList) LongLiteral(io.trino.sql.tree.LongLiteral) TEST_SESSION(io.trino.SessionTestUtils.TEST_SESSION) INTEGER(io.trino.spi.type.IntegerType.INTEGER) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest) FunctionCall(io.trino.sql.tree.FunctionCall) PlanMatchPattern.expression(io.trino.sql.planner.assertions.PlanMatchPattern.expression) DecimalType.createDecimalType(io.trino.spi.type.DecimalType.createDecimalType) ImmutableMap(com.google.common.collect.ImmutableMap) Language(org.intellij.lang.annotations.Language) PRECEDING(io.trino.sql.tree.FrameBound.Type.PRECEDING) RANGE(io.trino.sql.tree.WindowFrame.Type.RANGE) PlanMatchPattern.values(io.trino.sql.planner.assertions.PlanMatchPattern.values) SortOrder(io.trino.spi.connector.SortOrder) PlanMatchPattern.functionCall(io.trino.sql.planner.assertions.PlanMatchPattern.functionCall) PlanMatchPattern.windowFrame(io.trino.sql.planner.assertions.PlanMatchPattern.windowFrame) QualifiedName(io.trino.sql.tree.QualifiedName) PlanMatchPattern.anyTree(io.trino.sql.planner.assertions.PlanMatchPattern.anyTree) PlanMatchPattern.project(io.trino.sql.planner.assertions.PlanMatchPattern.project) SymbolReference(io.trino.sql.tree.SymbolReference) MetadataManager.createTestMetadataManager(io.trino.metadata.MetadataManager.createTestMetadataManager) Optional(java.util.Optional) DecimalLiteral(io.trino.sql.tree.DecimalLiteral) Language(org.intellij.lang.annotations.Language) LongLiteral(io.trino.sql.tree.LongLiteral) SymbolReference(io.trino.sql.tree.SymbolReference) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) DecimalLiteral(io.trino.sql.tree.DecimalLiteral) FunctionCall(io.trino.sql.tree.FunctionCall) Test(org.testng.annotations.Test) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest)

Example 7 with CREATED

use of io.trino.sql.planner.LogicalPlanner.Stage.CREATED 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);
}
Also used : CREATED(io.trino.sql.planner.LogicalPlanner.Stage.CREATED) PlanMatchPattern.any(io.trino.sql.planner.assertions.PlanMatchPattern.any) CURRENT_ROW(io.trino.sql.tree.FrameBound.Type.CURRENT_ROW) TypeSignatureProvider.fromTypes(io.trino.sql.analyzer.TypeSignatureProvider.fromTypes) PlanMatchPattern.window(io.trino.sql.planner.assertions.PlanMatchPattern.window) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) Test(org.testng.annotations.Test) FOLLOWING(io.trino.sql.tree.FrameBound.Type.FOLLOWING) FilterNode(io.trino.sql.planner.plan.FilterNode) PlanMatchPattern.specification(io.trino.sql.planner.assertions.PlanMatchPattern.specification) ImmutableList(com.google.common.collect.ImmutableList) PlanMatchPattern.sort(io.trino.sql.planner.assertions.PlanMatchPattern.sort) TEST_SESSION(io.trino.SessionTestUtils.TEST_SESSION) INTEGER(io.trino.spi.type.IntegerType.INTEGER) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest) FunctionCall(io.trino.sql.tree.FunctionCall) LAST(io.trino.sql.tree.SortItem.NullOrdering.LAST) ASCENDING(io.trino.sql.tree.SortItem.Ordering.ASCENDING) PlanMatchPattern.expression(io.trino.sql.planner.assertions.PlanMatchPattern.expression) ImmutableMap(com.google.common.collect.ImmutableMap) Language(org.intellij.lang.annotations.Language) PRECEDING(io.trino.sql.tree.FrameBound.Type.PRECEDING) RANGE(io.trino.sql.tree.WindowFrame.Type.RANGE) PlanMatchPattern.values(io.trino.sql.planner.assertions.PlanMatchPattern.values) SortOrder(io.trino.spi.connector.SortOrder) PlanMatchPattern.functionCall(io.trino.sql.planner.assertions.PlanMatchPattern.functionCall) PlanMatchPattern.windowFrame(io.trino.sql.planner.assertions.PlanMatchPattern.windowFrame) QualifiedName(io.trino.sql.tree.QualifiedName) PlanMatchPattern.node(io.trino.sql.planner.assertions.PlanMatchPattern.node) PlanMatchPattern.anyTree(io.trino.sql.planner.assertions.PlanMatchPattern.anyTree) PlanMatchPattern.project(io.trino.sql.planner.assertions.PlanMatchPattern.project) SymbolReference(io.trino.sql.tree.SymbolReference) MetadataManager.createTestMetadataManager(io.trino.metadata.MetadataManager.createTestMetadataManager) Optional(java.util.Optional) Language(org.intellij.lang.annotations.Language) SymbolReference(io.trino.sql.tree.SymbolReference) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) FunctionCall(io.trino.sql.tree.FunctionCall) Test(org.testng.annotations.Test) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest)

Example 8 with CREATED

use of io.trino.sql.planner.LogicalPlanner.Stage.CREATED in project trino by trinodb.

the class TestWindowFrameRange method testFrameFollowingWithOffsetCoercion.

@Test
public void testFrameFollowingWithOffsetCoercion() {
    @Language("SQL") String sql = "SELECT array_agg(key) OVER(ORDER BY key RANGE BETWEEN CURRENT ROW AND x FOLLOWING) " + "FROM (VALUES (1.1, 1), (2.2, 2)) t(key, x)";
    PlanMatchPattern pattern = anyTree(window(windowMatcherBuilder -> windowMatcherBuilder.specification(specification(ImmutableList.of(), ImmutableList.of("key"), ImmutableMap.of("key", SortOrder.ASC_NULLS_LAST))).addFunction("array_agg_result", functionCall("array_agg", ImmutableList.of("key")), createTestMetadataManager().resolveFunction(TEST_SESSION, QualifiedName.of("array_agg"), fromTypes(createDecimalType(2, 1))), windowFrame(RANGE, CURRENT_ROW, Optional.empty(), Optional.empty(), FOLLOWING, Optional.of("frame_end_value"), Optional.of("key_for_frame_end_comparison"))), project(ImmutableMap.of("key_for_frame_end_comparison", expression("CAST(key AS decimal(12, 1))")), project(ImmutableMap.of("frame_end_value", expression(new FunctionCall(QualifiedName.of("$operator$add"), ImmutableList.of(new SymbolReference("key"), new SymbolReference("offset"))))), filter("IF((offset >= CAST(0 AS DECIMAL(10, 0))), " + "true, " + "CAST(fail(CAST('Window frame offset value must not be negative or null' AS varchar)) AS boolean))", project(ImmutableMap.of("offset", expression("CAST(x AS decimal(10, 0))")), anyTree(values(ImmutableList.of("key", "x"), ImmutableList.of(ImmutableList.of(new DecimalLiteral("1.1"), new LongLiteral("1")), ImmutableList.of(new DecimalLiteral("2.2"), new LongLiteral("2")))))))))));
    assertPlan(sql, CREATED, pattern);
}
Also used : CREATED(io.trino.sql.planner.LogicalPlanner.Stage.CREATED) CURRENT_ROW(io.trino.sql.tree.FrameBound.Type.CURRENT_ROW) TypeSignatureProvider.fromTypes(io.trino.sql.analyzer.TypeSignatureProvider.fromTypes) PlanMatchPattern.window(io.trino.sql.planner.assertions.PlanMatchPattern.window) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) Test(org.testng.annotations.Test) PlanMatchPattern.filter(io.trino.sql.planner.assertions.PlanMatchPattern.filter) FOLLOWING(io.trino.sql.tree.FrameBound.Type.FOLLOWING) PlanMatchPattern.specification(io.trino.sql.planner.assertions.PlanMatchPattern.specification) ImmutableList(com.google.common.collect.ImmutableList) LongLiteral(io.trino.sql.tree.LongLiteral) TEST_SESSION(io.trino.SessionTestUtils.TEST_SESSION) INTEGER(io.trino.spi.type.IntegerType.INTEGER) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest) FunctionCall(io.trino.sql.tree.FunctionCall) PlanMatchPattern.expression(io.trino.sql.planner.assertions.PlanMatchPattern.expression) DecimalType.createDecimalType(io.trino.spi.type.DecimalType.createDecimalType) ImmutableMap(com.google.common.collect.ImmutableMap) Language(org.intellij.lang.annotations.Language) PRECEDING(io.trino.sql.tree.FrameBound.Type.PRECEDING) RANGE(io.trino.sql.tree.WindowFrame.Type.RANGE) PlanMatchPattern.values(io.trino.sql.planner.assertions.PlanMatchPattern.values) SortOrder(io.trino.spi.connector.SortOrder) PlanMatchPattern.functionCall(io.trino.sql.planner.assertions.PlanMatchPattern.functionCall) PlanMatchPattern.windowFrame(io.trino.sql.planner.assertions.PlanMatchPattern.windowFrame) QualifiedName(io.trino.sql.tree.QualifiedName) PlanMatchPattern.anyTree(io.trino.sql.planner.assertions.PlanMatchPattern.anyTree) PlanMatchPattern.project(io.trino.sql.planner.assertions.PlanMatchPattern.project) SymbolReference(io.trino.sql.tree.SymbolReference) MetadataManager.createTestMetadataManager(io.trino.metadata.MetadataManager.createTestMetadataManager) Optional(java.util.Optional) DecimalLiteral(io.trino.sql.tree.DecimalLiteral) Language(org.intellij.lang.annotations.Language) LongLiteral(io.trino.sql.tree.LongLiteral) SymbolReference(io.trino.sql.tree.SymbolReference) PlanMatchPattern(io.trino.sql.planner.assertions.PlanMatchPattern) DecimalLiteral(io.trino.sql.tree.DecimalLiteral) FunctionCall(io.trino.sql.tree.FunctionCall) Test(org.testng.annotations.Test) BasePlanTest(io.trino.sql.planner.assertions.BasePlanTest)

Aggregations

ImmutableList (com.google.common.collect.ImmutableList)8 ImmutableMap (com.google.common.collect.ImmutableMap)8 CREATED (io.trino.sql.planner.LogicalPlanner.Stage.CREATED)8 BasePlanTest (io.trino.sql.planner.assertions.BasePlanTest)8 PlanMatchPattern (io.trino.sql.planner.assertions.PlanMatchPattern)8 PlanMatchPattern.anyTree (io.trino.sql.planner.assertions.PlanMatchPattern.anyTree)8 PlanMatchPattern.expression (io.trino.sql.planner.assertions.PlanMatchPattern.expression)8 PlanMatchPattern.functionCall (io.trino.sql.planner.assertions.PlanMatchPattern.functionCall)8 PlanMatchPattern.project (io.trino.sql.planner.assertions.PlanMatchPattern.project)8 PlanMatchPattern.values (io.trino.sql.planner.assertions.PlanMatchPattern.values)8 PlanMatchPattern.window (io.trino.sql.planner.assertions.PlanMatchPattern.window)8 Language (org.intellij.lang.annotations.Language)8 Test (org.testng.annotations.Test)8 TEST_SESSION (io.trino.SessionTestUtils.TEST_SESSION)7 MetadataManager.createTestMetadataManager (io.trino.metadata.MetadataManager.createTestMetadataManager)7 SortOrder (io.trino.spi.connector.SortOrder)7 INTEGER (io.trino.spi.type.IntegerType.INTEGER)7 TypeSignatureProvider.fromTypes (io.trino.sql.analyzer.TypeSignatureProvider.fromTypes)7 PlanMatchPattern.specification (io.trino.sql.planner.assertions.PlanMatchPattern.specification)7 PlanMatchPattern.windowFrame (io.trino.sql.planner.assertions.PlanMatchPattern.windowFrame)7