Examples with QueryTemplate io.trino.tests.QueryTemplate used on opensource projects

Example 1 with QueryTemplate

use of io.trino.tests.QueryTemplate in project trino by trinodb.

the class AbstractTestJoinQueries method testJoinWithScalarSubqueryToBeExecutedAsPostJoinFilter.

@Test
public void testJoinWithScalarSubqueryToBeExecutedAsPostJoinFilter() {
    QueryTemplate.Parameter type = parameter("type").of("");
    QueryTemplate.Parameter condition = parameter("condition");
    QueryTemplate queryTemplate = queryTemplate("SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition);
    QueryTemplate.Parameter xPlusYEqualsSubqueryJoinCondition = condition.of("(x+y = (SELECT 4))");
    assertQuery(queryTemplate.replace(xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1)");
    assertQuery(queryTemplate.replace(condition.of("(x+y = (VALUES 4)) AND (x*y = (VALUES 4))")), "VALUES (2,2)");
    // all combination of duplicated subquery
    assertQuery(queryTemplate.replace(condition.of("x+y > (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,1), (3,2), (1,3), (2,3), (4,3), (3,5)");
    assertQuery(queryTemplate.replace(condition.of("x+y >= (VALUES 5) AND (x = (VALUES 3)) != (y = (VALUES 3))")), "VALUES (3,2), (2,3), (4,3), (3,5)");
    assertQuery(queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 5)) != (y = (VALUES 3))")), "VALUES (1,3), (2,3), (3,3), (4,3)");
    assertQuery(queryTemplate.replace(condition.of("x+y >= (VALUES 3) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)");
    assertQuery(queryTemplate.replace(condition.of("x+y >= (VALUES 4) AND (x = (VALUES 3)) != (y = (VALUES 5))")), "VALUES (3,1), (3,2), (3,3), (1,5), (2,5), (4,5)");
    // non inner joins
    assertQuery(queryTemplate.replace(type.of("left"), xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1), (4, null)");
    assertQuery(queryTemplate.replace(type.of("right"), xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1), (null, 5)");
    assertQuery(queryTemplate.replace(type.of("full"), xPlusYEqualsSubqueryJoinCondition), "VALUES (1,3), (2,2), (3,1), (4, null), (null, 5)");
}

Example 2 with QueryTemplate

use of io.trino.tests.QueryTemplate in project trino by trinodb.

the class AbstractTestJoinQueries method testJoinWithInSubqueryToBeExecutedAsPostJoinFilter.

@Test
public void testJoinWithInSubqueryToBeExecutedAsPostJoinFilter() {
    QueryTemplate.Parameter type = parameter("type").of("");
    QueryTemplate.Parameter condition = parameter("condition").of("true");
    QueryTemplate queryTemplate = queryTemplate("SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition);
    assertQuery(queryTemplate.replace(condition.of("(x+y in (VALUES 4))")), "VALUES (1,3), (2,2), (3,1)");
    assertQuery(queryTemplate.replace(condition.of("(x+y in (VALUES 4)) AND (x*y in (VALUES 4,5))")), "VALUES (2,2)");
    assertQuery(queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x*y IN (VALUES 4,5))")), "VALUES (4,1), (2,2)");
    assertQuery(queryTemplate.replace(condition.of("(x+y in (VALUES 4,5)) AND (x in (VALUES 4,5)) != (y in (VALUES 4,5))")), "VALUES (4,1)");
}

Example 3 with QueryTemplate

use of io.trino.tests.QueryTemplate in project trino by trinodb.

the class AbstractTestJoinQueries method testJoinWithMultipleInSubqueryClauses.

@Test
public void testJoinWithMultipleInSubqueryClauses() {
    QueryTemplate.Parameter type = parameter("type").of("");
    QueryTemplate.Parameter condition = parameter("condition").of("true");
    QueryTemplate queryTemplate = queryTemplate("SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition);
    QueryTemplate.Parameter twoDuplicatedInSubqueriesCondition = condition.of("(x in (VALUES 1,2,3)) = (y in (VALUES 1,2,3)) AND (x in (VALUES 1,2,4)) = (y in (VALUES 1,2,4))");
    assertQuery(queryTemplate.replace(twoDuplicatedInSubqueriesCondition), "VALUES (1,1), (1,2), (2,2), (2,1), (3,3)");
    assertQuery(queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) = (y in (VALUES 3))")), "VALUES (2,2), (2,1), (3,5), (4,5)");
    assertQuery(queryTemplate.replace(condition.of("(x in (VALUES 1,2)) = (y in (VALUES 1,2)) AND (x in (VALUES 1)) != (y in (VALUES 3))")), "VALUES (1,2), (1,1), (3, 3), (4,3)");
    assertQuery(queryTemplate.replace(condition.of("(x in (VALUES 1)) = (y in (VALUES 1)) AND (x in (SELECT 2)) != (y in (SELECT 2))")), "VALUES (2,3), (2,5), (3, 2), (4,2)");
    QueryTemplate.Parameter left = type.of("left");
    QueryTemplate.Parameter right = type.of("right");
    QueryTemplate.Parameter full = type.of("full");
    assertQuery(queryTemplate.replace(left, twoDuplicatedInSubqueriesCondition), "VALUES (1,1), (1,2), (2,2), (2,1), (3,3), (4, null)");
    assertQuery(queryTemplate.replace(right, twoDuplicatedInSubqueriesCondition), "VALUES (1,1), (1,2), (2,2), (2,1), (3,3), (null, 5)");
    assertQuery(queryTemplate.replace(full, twoDuplicatedInSubqueriesCondition), "VALUES (1,1), (1,2), (2,2), (2,1), (3,3), (4, null), (null, 5)");
}

Example 4 with QueryTemplate

use of io.trino.tests.QueryTemplate in project trino by trinodb.

the class AbstractTestEngineOnlyQueries method testExistsSubquery.

@Test
public void testExistsSubquery() {
    // nested
    assertQuery("SELECT EXISTS(SELECT NOT EXISTS(SELECT EXISTS(SELECT 1)))");
    // aggregation
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "EXISTS(SELECT max(orderkey) FROM orders)");
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "NOT EXISTS(SELECT max(orderkey) FROM orders)");
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "NOT EXISTS(SELECT orderkey FROM orders WHERE false)");
    // no output
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "EXISTS(SELECT orderkey FROM orders WHERE false)");
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "NOT EXISTS(SELECT orderkey FROM orders WHERE false)");
    // exists with in-predicate
    assertQuery("SELECT (EXISTS(SELECT 1)) IN (false)", "SELECT false");
    assertQuery("SELECT (NOT EXISTS(SELECT 1)) IN (false)", "SELECT true");
    assertQuery("SELECT (EXISTS(SELECT 1)) IN (true, false)", "SELECT true");
    assertQuery("SELECT (NOT EXISTS(SELECT 1)) IN (true, false)", "SELECT true");
    assertQuery("SELECT (EXISTS(SELECT 1 WHERE false)) IN (true, false)", "SELECT true");
    assertQuery("SELECT (NOT EXISTS(SELECT 1 WHERE false)) IN (true, false)", "SELECT true");
    assertQuery("SELECT (EXISTS(SELECT 1 WHERE false)) IN (false)", "SELECT true");
    assertQuery("SELECT (NOT EXISTS(SELECT 1 WHERE false)) IN (false)", "SELECT false");
    // multiple exists
    assertQuery("SELECT (EXISTS(SELECT 1)) = (EXISTS(SELECT 1)) WHERE NOT EXISTS(SELECT 1)", "SELECT true WHERE false");
    assertQuery("SELECT (EXISTS(SELECT 1)) = (EXISTS(SELECT 3)) WHERE NOT EXISTS(SELECT 1 WHERE false)", "SELECT true");
    assertQuery("SELECT COUNT(*) FROM lineitem WHERE " + "(EXISTS(SELECT min(orderkey) FROM orders))" + "=" + "(NOT EXISTS(SELECT orderkey FROM orders WHERE false))", "SELECT count(*) FROM lineitem");
    assertQuery("SELECT EXISTS(SELECT 1), EXISTS(SELECT 1), EXISTS(SELECT 3), NOT EXISTS(SELECT 1), NOT EXISTS(SELECT 1 WHERE false)");
    // distinct
    assertQuery("SELECT DISTINCT orderkey FROM lineitem " + "WHERE EXISTS(SELECT avg(orderkey) FROM orders)");
    // subqueries used with joins
    QueryTemplate.Parameter joinType = parameter("join_type");
    QueryTemplate.Parameter condition = parameter("condition");
    QueryTemplate queryTemplate = queryTemplate("SELECT o1.orderkey, COUNT(*) " + "FROM orders o1 %join_type% JOIN (SELECT * FROM orders LIMIT 10) o2 ON %condition% " + "GROUP BY o1.orderkey ORDER BY o1.orderkey LIMIT 5", joinType, condition);
    List<QueryTemplate.Parameter> conditions = condition.of("EXISTS(SELECT avg(orderkey) FROM orders)", "(SELECT avg(orderkey) FROM orders) > 3");
    for (QueryTemplate.Parameter actualCondition : conditions) {
        for (QueryTemplate.Parameter actualJoinType : joinType.of("", "LEFT", "RIGHT")) {
            assertQuery(queryTemplate.replace(actualJoinType, actualCondition));
        }
        assertQuery(queryTemplate.replace(joinType.of("FULL"), actualCondition), "VALUES (1, 10), (2, 10), (3, 10), (4, 10), (5, 10)");
    }
    // subqueries with ORDER BY
    assertQuery("SELECT orderkey, totalprice FROM orders ORDER BY EXISTS(SELECT 2)");
    assertQuery("SELECT orderkey, totalprice FROM orders ORDER BY NOT(EXISTS(SELECT 2))");
}

Example 5 with QueryTemplate

use of io.trino.tests.QueryTemplate in project trino by trinodb.

the class AbstractTestJoinQueries method testJoinWithMultipleScalarSubqueryClauses.

@Test
public void testJoinWithMultipleScalarSubqueryClauses() {
    QueryTemplate.Parameter type = parameter("type").of("");
    QueryTemplate.Parameter condition = parameter("condition");
    QueryTemplate queryTemplate = queryTemplate("SELECT * FROM (VALUES 1,2,3,4) t(x) %type% JOIN (VALUES 1,2,3,5) t2(y) ON %condition%", type, condition);
    QueryTemplate.Parameter multipleScalarJoinCondition = condition.of("(x = (VALUES 1)) AND (y = (VALUES 2)) AND (x in (VALUES 2)) = (y in (VALUES 1))");
    assertQuery(queryTemplate.replace(multipleScalarJoinCondition), "VALUES (1,2)");
    assertQuery(queryTemplate.replace(condition.of("(x = (VALUES 2)) = (y > (VALUES 0)) AND (x > (VALUES 1)) = (y < (VALUES 3))")), "VALUES (2,2), (2,1)");
    assertQuery(queryTemplate.replace(condition.of("(x = (VALUES 1)) = (y = (VALUES 1)) AND (x = (SELECT 2)) != (y = (SELECT 3))")), "VALUES (2,5), (2,2), (3,3), (4,3)");
    assertQuery(queryTemplate.replace(type.of("left"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null)");
    assertQuery(queryTemplate.replace(type.of("right"), multipleScalarJoinCondition), "VALUES (1,2), (null,1), (null, 3), (null, 5)");
    assertQuery(queryTemplate.replace(type.of("full"), multipleScalarJoinCondition), "VALUES (1,2), (2,null), (3, null), (4, null), (null,1), (null, 3), (null, 5)");
}