Examples with WithQuery - io.trino.sql.tree.WithQuery

Example 1 with WithQuery

use of io.trino.sql.tree.WithQuery in project trino by trinodb.

the class TestSqlParser method testShowStatsForQuery.

@Test
public void testShowStatsForQuery() {
    String[] tableNames = { "t", "s.t", "c.s.t" };
    for (String fullName : tableNames) {
        QualifiedName qualifiedName = makeQualifiedName(fullName);
        // Simple SELECT
        assertStatement(format("SHOW STATS FOR (SELECT * FROM %s)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.empty()));
        // SELECT with predicate
        assertStatement(format("SHOW STATS FOR (SELECT * FROM %s WHERE field > 0)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.of(new ComparisonExpression(ComparisonExpression.Operator.GREATER_THAN, new Identifier("field"), new LongLiteral("0")))));
        // SELECT with more complex predicate
        assertStatement(format("SHOW STATS FOR (SELECT * FROM %s WHERE field > 0 or field < 0)", qualifiedName), createShowStats(qualifiedName, ImmutableList.of(new AllColumns()), Optional.of(LogicalExpression.or(new ComparisonExpression(ComparisonExpression.Operator.GREATER_THAN, new Identifier("field"), new LongLiteral("0")), new ComparisonExpression(ComparisonExpression.Operator.LESS_THAN, new Identifier("field"), new LongLiteral("0"))))));
    }
    // SELECT with LIMIT
    assertThat(statement("SHOW STATS FOR (SELECT * FROM t LIMIT 10)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(1, 17), Optional.empty(), new QuerySpecification(location(1, 17), new Select(location(1, 17), false, ImmutableList.of(new AllColumns(location(1, 24), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(1, 31), QualifiedName.of(ImmutableList.of(new Identifier(location(1, 31), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.of(new Limit(location(1, 33), new LongLiteral(location(1, 39), "10")))), Optional.empty(), Optional.empty(), Optional.empty()))));
    // SELECT with ORDER BY ... LIMIT
    assertThat(statement("SHOW STATS FOR (SELECT * FROM t ORDER BY field LIMIT 10)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(1, 17), Optional.empty(), new QuerySpecification(location(1, 17), new Select(location(1, 17), false, ImmutableList.of(new AllColumns(location(1, 24), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(1, 31), QualifiedName.of(ImmutableList.of(new Identifier(location(1, 31), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.of(new OrderBy(location(1, 33), ImmutableList.of(new SortItem(location(1, 42), new Identifier(location(1, 42), "field", false), ASCENDING, UNDEFINED)))), Optional.empty(), Optional.of(new Limit(location(1, 48), new LongLiteral(location(1, 54), "10")))), Optional.empty(), Optional.empty(), Optional.empty()))));
    // SELECT with WITH
    assertThat(statement("SHOW STATS FOR (\n" + "   WITH t AS (SELECT 1 )\n" + "   SELECT * FROM t)")).isEqualTo(new ShowStats(Optional.of(location(1, 1)), new TableSubquery(new Query(location(2, 4), Optional.of(new With(location(2, 4), false, ImmutableList.of(new WithQuery(location(2, 9), new Identifier(location(2, 9), "t", false), new Query(location(2, 15), Optional.empty(), new QuerySpecification(location(2, 15), new Select(location(2, 15), false, ImmutableList.of(new SingleColumn(location(2, 22), new LongLiteral(location(2, 22), "1"), Optional.empty()))), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty())))), new QuerySpecification(location(3, 4), new Select(location(3, 4), false, ImmutableList.of(new AllColumns(location(3, 11), Optional.empty(), ImmutableList.of()))), Optional.of(new Table(location(3, 18), QualifiedName.of(ImmutableList.of(new Identifier(location(3, 18), "t", false))))), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableList.of(), Optional.empty(), Optional.empty(), Optional.empty()), Optional.empty(), Optional.empty(), Optional.empty()))));
}

Also used : OrderBy(io.trino.sql.tree.OrderBy) CreateTable(io.trino.sql.tree.CreateTable) DropTable(io.trino.sql.tree.DropTable) Table(io.trino.sql.tree.Table) TruncateTable(io.trino.sql.tree.TruncateTable) RenameTable(io.trino.sql.tree.RenameTable) QueryUtil.simpleQuery(io.trino.sql.QueryUtil.simpleQuery) Query(io.trino.sql.tree.Query) WithQuery(io.trino.sql.tree.WithQuery) LongLiteral(io.trino.sql.tree.LongLiteral) QualifiedName(io.trino.sql.tree.QualifiedName) AllColumns(io.trino.sql.tree.AllColumns) SingleColumn(io.trino.sql.tree.SingleColumn) TableSubquery(io.trino.sql.tree.TableSubquery) With(io.trino.sql.tree.With) QuantifiedComparisonExpression(io.trino.sql.tree.QuantifiedComparisonExpression) ComparisonExpression(io.trino.sql.tree.ComparisonExpression) QuerySpecification(io.trino.sql.tree.QuerySpecification) SortItem(io.trino.sql.tree.SortItem) QueryUtil.quotedIdentifier(io.trino.sql.QueryUtil.quotedIdentifier) Identifier(io.trino.sql.tree.Identifier) ShowStats(io.trino.sql.tree.ShowStats) WithQuery(io.trino.sql.tree.WithQuery) CreateTableAsSelect(io.trino.sql.tree.CreateTableAsSelect) Select(io.trino.sql.tree.Select) Limit(io.trino.sql.tree.Limit) Test(org.junit.jupiter.api.Test)

Example 2 with WithQuery

use of io.trino.sql.tree.WithQuery in project trino by trinodb.

the class TestSqlParser method testCreateMaterializedView.

@Test
public void testCreateMaterializedView() {
    Query query = simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("t")));
    Optional<NodeLocation> location = Optional.empty();
    assertStatement("CREATE MATERIALIZED VIEW a AS SELECT * FROM t", new CreateMaterializedView(location, QualifiedName.of("a"), query, false, false, new ArrayList<>(), Optional.empty()));
    Query query2 = simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("catalog2", "schema2", "tab")));
    assertStatement("CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.matview COMMENT 'A simple materialized view'" + " AS SELECT * FROM catalog2.schema2.tab", new CreateMaterializedView(location, QualifiedName.of("catalog", "schema", "matview"), query2, true, false, new ArrayList<>(), Optional.of("A simple materialized view")));
    assertStatement("CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.matview COMMENT 'A simple materialized view'" + " AS SELECT * FROM catalog2.schema2.tab", new CreateMaterializedView(location, QualifiedName.of("catalog", "schema", "matview"), query2, true, false, new ArrayList<>(), Optional.of("A simple materialized view")));
    List<Property> properties = ImmutableList.of(new Property(new Identifier("partitioned_by"), new ArrayConstructor(ImmutableList.of(new StringLiteral("dateint")))));
    assertStatement("CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.matview COMMENT 'A simple materialized view'" + "WITH (partitioned_by = ARRAY ['dateint'])" + " AS SELECT * FROM catalog2.schema2.tab", new CreateMaterializedView(location, QualifiedName.of("catalog", "schema", "matview"), query2, true, false, properties, Optional.of("A simple materialized view")));
    Query query3 = new Query(Optional.of(new With(false, ImmutableList.of(new WithQuery(identifier("a"), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("x"))), Optional.of(ImmutableList.of(identifier("t"), identifier("u")))), new WithQuery(identifier("b"), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("a"))), Optional.empty())))), new Table(QualifiedName.of("b")), Optional.empty(), Optional.empty(), Optional.empty());
    assertStatement("CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.matview COMMENT 'A partitioned materialized view' " + "WITH (partitioned_by = ARRAY ['dateint'])" + " AS WITH a (t, u) AS (SELECT * FROM x), b AS (SELECT * FROM a) TABLE b", new CreateMaterializedView(location, QualifiedName.of("catalog", "schema", "matview"), query3, true, false, properties, Optional.of("A partitioned materialized view")));
}

Also used : CreateTable(io.trino.sql.tree.CreateTable) DropTable(io.trino.sql.tree.DropTable) Table(io.trino.sql.tree.Table) TruncateTable(io.trino.sql.tree.TruncateTable) RenameTable(io.trino.sql.tree.RenameTable) QueryUtil.simpleQuery(io.trino.sql.QueryUtil.simpleQuery) Query(io.trino.sql.tree.Query) WithQuery(io.trino.sql.tree.WithQuery) ArrayList(java.util.ArrayList) AllColumns(io.trino.sql.tree.AllColumns) With(io.trino.sql.tree.With) CreateMaterializedView(io.trino.sql.tree.CreateMaterializedView) QueryUtil.quotedIdentifier(io.trino.sql.QueryUtil.quotedIdentifier) Identifier(io.trino.sql.tree.Identifier) NodeLocation(io.trino.sql.tree.NodeLocation) StringLiteral(io.trino.sql.tree.StringLiteral) WithQuery(io.trino.sql.tree.WithQuery) ArrayConstructor(io.trino.sql.tree.ArrayConstructor) Property(io.trino.sql.tree.Property) Test(org.junit.jupiter.api.Test)

Example 3 with WithQuery

use of io.trino.sql.tree.WithQuery in project trino by trinodb.

the class TestSqlParser method testWith.

@Test
public void testWith() {
    assertStatement("WITH a (t, u) AS (SELECT * FROM x), b AS (SELECT * FROM y) TABLE z", new Query(Optional.of(new With(false, ImmutableList.of(new WithQuery(identifier("a"), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("x"))), Optional.of(ImmutableList.of(identifier("t"), identifier("u")))), new WithQuery(identifier("b"), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("y"))), Optional.empty())))), new Table(QualifiedName.of("z")), Optional.empty(), Optional.empty(), Optional.empty()));
    assertStatement("WITH RECURSIVE a AS (SELECT * FROM x) TABLE y", new Query(Optional.of(new With(true, ImmutableList.of(new WithQuery(identifier("a"), simpleQuery(selectList(new AllColumns()), table(QualifiedName.of("x"))), Optional.empty())))), new Table(QualifiedName.of("y")), Optional.empty(), Optional.empty(), Optional.empty()));
}

Example 4 with WithQuery

use of io.trino.sql.tree.WithQuery in project trino by trinodb.

the class TestSqlParser method testCreateTableAsWith.

@Test
public void testCreateTableAsWith() {
    String queryParenthesizedWith = "CREATE TABLE foo " + "AS " + "( WITH t(x) AS (VALUES 1) " + "TABLE t ) " + "WITH NO DATA";
    String queryUnparenthesizedWith = "CREATE TABLE foo " + "AS " + "WITH t(x) AS (VALUES 1) " + "TABLE t " + "WITH NO DATA";
    String queryParenthesizedWithHasAlias = "CREATE TABLE foo(a) " + "AS " + "( WITH t(x) AS (VALUES 1) " + "TABLE t ) " + "WITH NO DATA";
    String queryUnparenthesizedWithHasAlias = "CREATE TABLE foo(a) " + "AS " + "WITH t(x) AS (VALUES 1) " + "TABLE t " + "WITH NO DATA";
    QualifiedName table = QualifiedName.of("foo");
    Query query = new Query(Optional.of(new With(false, ImmutableList.of(new WithQuery(identifier("t"), query(new Values(ImmutableList.of(new LongLiteral("1")))), Optional.of(ImmutableList.of(identifier("x"))))))), new Table(QualifiedName.of("t")), Optional.empty(), Optional.empty(), Optional.empty());
    assertStatement(queryParenthesizedWith, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.empty(), Optional.empty()));
    assertStatement(queryUnparenthesizedWith, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.empty(), Optional.empty()));
    assertStatement(queryParenthesizedWithHasAlias, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("a"))), Optional.empty()));
    assertStatement(queryUnparenthesizedWithHasAlias, new CreateTableAsSelect(table, query, false, ImmutableList.of(), false, Optional.of(ImmutableList.of(new Identifier("a"))), Optional.empty()));
}

Also used : CreateTable(io.trino.sql.tree.CreateTable) DropTable(io.trino.sql.tree.DropTable) Table(io.trino.sql.tree.Table) TruncateTable(io.trino.sql.tree.TruncateTable) RenameTable(io.trino.sql.tree.RenameTable) QueryUtil.quotedIdentifier(io.trino.sql.QueryUtil.quotedIdentifier) Identifier(io.trino.sql.tree.Identifier) QueryUtil.simpleQuery(io.trino.sql.QueryUtil.simpleQuery) Query(io.trino.sql.tree.Query) WithQuery(io.trino.sql.tree.WithQuery) LongLiteral(io.trino.sql.tree.LongLiteral) WithQuery(io.trino.sql.tree.WithQuery) QualifiedName(io.trino.sql.tree.QualifiedName) CreateTableAsSelect(io.trino.sql.tree.CreateTableAsSelect) Values(io.trino.sql.tree.Values) With(io.trino.sql.tree.With) Test(org.junit.jupiter.api.Test)

Aggregations

QueryUtil.simpleQuery (io.trino.sql.QueryUtil.simpleQuery)4 CreateTable (io.trino.sql.tree.CreateTable)4 DropTable (io.trino.sql.tree.DropTable)4 Query (io.trino.sql.tree.Query)4 RenameTable (io.trino.sql.tree.RenameTable)4 Table (io.trino.sql.tree.Table)4 TruncateTable (io.trino.sql.tree.TruncateTable)4 With (io.trino.sql.tree.With)4 WithQuery (io.trino.sql.tree.WithQuery)4 Test (org.junit.jupiter.api.Test)4 QueryUtil.quotedIdentifier (io.trino.sql.QueryUtil.quotedIdentifier)3 AllColumns (io.trino.sql.tree.AllColumns)3 Identifier (io.trino.sql.tree.Identifier)3 CreateTableAsSelect (io.trino.sql.tree.CreateTableAsSelect)2 LongLiteral (io.trino.sql.tree.LongLiteral)2 QualifiedName (io.trino.sql.tree.QualifiedName)2 ArrayConstructor (io.trino.sql.tree.ArrayConstructor)1 ComparisonExpression (io.trino.sql.tree.ComparisonExpression)1 CreateMaterializedView (io.trino.sql.tree.CreateMaterializedView)1 Limit (io.trino.sql.tree.Limit)1