Examples with TestTable io.trino.testing.sql.TestTable used on opensource projects

Example 6 with TestTable

use of io.trino.testing.sql.TestTable in project trino by trinodb.

the class BaseJdbcConnectorTest method testAggregationPushdown.

// TODO move common tests from connector-specific classes here
@Test
public void testAggregationPushdown() {
    if (!hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN)) {
        assertThat(query("SELECT count(nationkey) FROM nation")).isNotFullyPushedDown(AggregationNode.class);
        return;
    }
    // TODO support aggregation pushdown with GROUPING SETS
    // TODO support aggregation over expressions
    // count()
    assertThat(query("SELECT count(*) FROM nation")).isFullyPushedDown();
    assertThat(query("SELECT count(nationkey) FROM nation")).isFullyPushedDown();
    assertThat(query("SELECT count(1) FROM nation")).isFullyPushedDown();
    assertThat(query("SELECT count() FROM nation")).isFullyPushedDown();
    assertThat(query("SELECT regionkey, count(1) FROM nation GROUP BY regionkey")).isFullyPushedDown();
    try (TestTable emptyTable = createAggregationTestTable(getSession().getSchema().orElseThrow() + ".empty_table", ImmutableList.of())) {
        assertThat(query("SELECT count(*) FROM " + emptyTable.getName())).isFullyPushedDown();
        assertThat(query("SELECT count(a_bigint) FROM " + emptyTable.getName())).isFullyPushedDown();
        assertThat(query("SELECT count(1) FROM " + emptyTable.getName())).isFullyPushedDown();
        assertThat(query("SELECT count() FROM " + emptyTable.getName())).isFullyPushedDown();
        assertThat(query("SELECT a_bigint, count(1) FROM " + emptyTable.getName() + " GROUP BY a_bigint")).isFullyPushedDown();
    }
    // GROUP BY
    assertThat(query("SELECT regionkey, min(nationkey) FROM nation GROUP BY regionkey")).isFullyPushedDown();
    assertThat(query("SELECT regionkey, max(nationkey) FROM nation GROUP BY regionkey")).isFullyPushedDown();
    assertThat(query("SELECT regionkey, sum(nationkey) FROM nation GROUP BY regionkey")).isFullyPushedDown();
    assertThat(query("SELECT regionkey, avg(nationkey) FROM nation GROUP BY regionkey")).isFullyPushedDown();
    try (TestTable emptyTable = createAggregationTestTable(getSession().getSchema().orElseThrow() + ".empty_table", ImmutableList.of())) {
        assertThat(query("SELECT t_double, min(a_bigint) FROM " + emptyTable.getName() + " GROUP BY t_double")).isFullyPushedDown();
        assertThat(query("SELECT t_double, max(a_bigint) FROM " + emptyTable.getName() + " GROUP BY t_double")).isFullyPushedDown();
        assertThat(query("SELECT t_double, sum(a_bigint) FROM " + emptyTable.getName() + " GROUP BY t_double")).isFullyPushedDown();
        assertThat(query("SELECT t_double, avg(a_bigint) FROM " + emptyTable.getName() + " GROUP BY t_double")).isFullyPushedDown();
    }
    // GROUP BY and WHERE on bigint column
    // GROUP BY and WHERE on aggregation key
    assertThat(query("SELECT regionkey, sum(nationkey) FROM nation WHERE regionkey < 4 GROUP BY regionkey")).isFullyPushedDown();
    // GROUP BY and WHERE on varchar column
    // GROUP BY and WHERE on "other" (not aggregation key, not aggregation input)
    assertConditionallyPushedDown(getSession(), "SELECT regionkey, sum(nationkey) FROM nation WHERE regionkey < 4 AND name > 'AAA' GROUP BY regionkey", hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY), node(FilterNode.class, node(TableScanNode.class)));
    // GROUP BY above WHERE and LIMIT
    assertConditionallyPushedDown(getSession(), "SELECT regionkey, sum(nationkey) FROM (SELECT * FROM nation WHERE regionkey < 2 LIMIT 11) GROUP BY regionkey", hasBehavior(SUPPORTS_LIMIT_PUSHDOWN), node(LimitNode.class, anyTree(node(TableScanNode.class))));
    // GROUP BY above TopN
    assertConditionallyPushedDown(getSession(), "SELECT custkey, sum(totalprice) FROM (SELECT custkey, totalprice FROM orders ORDER BY orderdate ASC, totalprice ASC LIMIT 10) GROUP BY custkey", hasBehavior(SUPPORTS_TOPN_PUSHDOWN), node(TopNNode.class, anyTree(node(TableScanNode.class))));
    // GROUP BY with JOIN
    assertConditionallyPushedDown(joinPushdownEnabled(getSession()), "SELECT n.regionkey, sum(c.acctbal) acctbals FROM nation n LEFT JOIN customer c USING (nationkey) GROUP BY 1", hasBehavior(SUPPORTS_JOIN_PUSHDOWN), node(JoinNode.class, anyTree(node(TableScanNode.class)), anyTree(node(TableScanNode.class))));
    // GROUP BY with WHERE on neither grouping nor aggregation column
    assertConditionallyPushedDown(getSession(), "SELECT nationkey, min(regionkey) FROM nation WHERE name = 'ARGENTINA' GROUP BY nationkey", hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_EQUALITY), node(FilterNode.class, node(TableScanNode.class)));
    // GROUP BY with WHERE complex predicate
    assertConditionallyPushedDown(getSession(), "SELECT regionkey, sum(nationkey) FROM nation WHERE name LIKE '%N%' GROUP BY regionkey", hasBehavior(SUPPORTS_PREDICATE_EXPRESSION_PUSHDOWN_WITH_LIKE), node(FilterNode.class, node(TableScanNode.class)));
    // aggregation on varchar column
    assertThat(query("SELECT count(name) FROM nation")).isFullyPushedDown();
    // aggregation on varchar column with GROUPING
    assertThat(query("SELECT nationkey, count(name) FROM nation GROUP BY nationkey")).isFullyPushedDown();
    // aggregation on varchar column with WHERE
    assertConditionallyPushedDown(getSession(), "SELECT count(name) FROM nation WHERE name = 'ARGENTINA'", hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_EQUALITY), node(FilterNode.class, node(TableScanNode.class)));
}

Example 7 with TestTable

use of io.trino.testing.sql.TestTable in project trino by trinodb.

the class BaseJdbcConnectorTest method testDeleteWithVarcharGreaterAndLowerPredicate.

@Test
public void testDeleteWithVarcharGreaterAndLowerPredicate() {
    skipTestUnless(hasBehavior(SUPPORTS_CREATE_TABLE) && hasBehavior(SUPPORTS_ROW_LEVEL_DELETE));
    // TODO (https://github.com/trinodb/trino/issues/5901) Use longer table name once Oracle version is updated
    try (TestTable table = new TestTable(getQueryRunner()::execute, "test_delete_varchar", "(col varchar(1))", ImmutableList.of("'0'", "'a'", "'A'", "'b'", "null"))) {
        if (!hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY)) {
            assertQueryFails("DELETE FROM " + table.getName() + " WHERE col < 'A'", "Unsupported delete");
            assertQueryFails("DELETE FROM " + table.getName() + " WHERE col > 'A'", "Unsupported delete");
            return;
        }
        assertUpdate("DELETE FROM " + table.getName() + " WHERE col < 'A'", 1);
        assertQuery("SELECT * FROM " + table.getName(), "VALUES 'a', 'A', 'b', null");
        assertUpdate("DELETE FROM " + table.getName() + " WHERE col > 'A'", 2);
        assertQuery("SELECT * FROM " + table.getName(), "VALUES 'A', null");
    }
}

Example 8 with TestTable

use of io.trino.testing.sql.TestTable in project trino by trinodb.

the class BaseJdbcConnectorTest method testCaseSensitiveAggregationPushdown.

@Test
public void testCaseSensitiveAggregationPushdown() {
    if (!hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN)) {
        // Covered by testAggregationPushdown
        return;
    }
    boolean supportsPushdownWithVarcharInequality = hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY);
    boolean supportsCountDistinctPushdown = hasBehavior(SUPPORTS_AGGREGATION_PUSHDOWN_COUNT_DISTINCT);
    PlanMatchPattern aggregationOverTableScan = node(AggregationNode.class, node(TableScanNode.class));
    PlanMatchPattern groupingAggregationOverTableScan = node(AggregationNode.class, node(ProjectNode.class, node(TableScanNode.class)));
    try (TestTable table = new TestTable(getQueryRunner()::execute, "test_case_sensitive_aggregation_pushdown", "(a_string varchar(1), a_char char(1), a_bigint bigint)", ImmutableList.of("'A', 'A', 1", "'B', 'B', 2", "'a', 'a', 3", "'b', 'b', 4"))) {
        // case-sensitive functions prevent pushdown
        assertConditionallyPushedDown(getSession(), "SELECT max(a_string), min(a_string), max(a_char), min(a_char) FROM " + table.getName(), supportsPushdownWithVarcharInequality, aggregationOverTableScan).skippingTypesCheck().matches("VALUES ('b', 'A', 'b', 'A')");
        // distinct over case-sensitive column prevents pushdown
        assertConditionallyPushedDown(getSession(), "SELECT distinct a_string FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES 'A', 'B', 'a', 'b'");
        assertConditionallyPushedDown(getSession(), "SELECT distinct a_char FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES 'A', 'B', 'a', 'b'");
        // case-sensitive grouping sets prevent pushdown
        assertConditionallyPushedDown(getSession(), "SELECT a_string, count(*) FROM " + table.getName() + " GROUP BY a_string", supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES ('A', BIGINT '1'), ('a', BIGINT '1'), ('b', BIGINT '1'), ('B', BIGINT '1')");
        assertConditionallyPushedDown(getSession(), "SELECT a_char, count(*) FROM " + table.getName() + " GROUP BY a_char", supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES ('A', BIGINT '1'), ('B', BIGINT '1'), ('a', BIGINT '1'), ('b', BIGINT '1')");
        // case-insensitive functions can still be pushed down as long as grouping sets are not case-sensitive
        assertThat(query("SELECT count(a_string), count(a_char) FROM " + table.getName())).isFullyPushedDown();
        assertThat(query("SELECT count(a_string), count(a_char) FROM " + table.getName() + " GROUP BY a_bigint")).isFullyPushedDown();
        // DISTINCT over case-sensitive columns prevents pushdown
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_string) FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES BIGINT '4'");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_char) FROM " + table.getName(), supportsPushdownWithVarcharInequality, groupingAggregationOverTableScan).skippingTypesCheck().matches("VALUES BIGINT '4'");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_string), count(DISTINCT a_bigint) FROM " + table.getName(), supportsPushdownWithVarcharInequality && supportsCountDistinctPushdown, node(ExchangeNode.class, node(AggregationNode.class, anyTree(node(TableScanNode.class))))).skippingTypesCheck().matches("VALUES (BIGINT '4', BIGINT '4')");
        assertConditionallyPushedDown(getSession(), "SELECT count(DISTINCT a_char), count(DISTINCT a_bigint) FROM " + table.getName(), supportsPushdownWithVarcharInequality && supportsCountDistinctPushdown, node(ExchangeNode.class, node(AggregationNode.class, anyTree(node(TableScanNode.class))))).skippingTypesCheck().matches("VALUES (BIGINT '4', BIGINT '4')");
    }
}

Example 9 with TestTable

use of io.trino.testing.sql.TestTable in project trino by trinodb.

the class BaseJdbcConnectorTest method testDeleteWithVarcharInequalityPredicate.

@Test
public void testDeleteWithVarcharInequalityPredicate() {
    skipTestUnless(hasBehavior(SUPPORTS_CREATE_TABLE) && hasBehavior(SUPPORTS_ROW_LEVEL_DELETE));
    // TODO (https://github.com/trinodb/trino/issues/5901) Use longer table name once Oracle version is updated
    try (TestTable table = new TestTable(getQueryRunner()::execute, "test_delete_varchar", "(col varchar(1))", ImmutableList.of("'a'", "'A'", "null"))) {
        if (!hasBehavior(SUPPORTS_PREDICATE_PUSHDOWN_WITH_VARCHAR_INEQUALITY)) {
            assertQueryFails("DELETE FROM " + table.getName() + " WHERE col != 'A'", "Unsupported delete");
            return;
        }
        assertUpdate("DELETE FROM " + table.getName() + " WHERE col != 'A'", 1);
        assertQuery("SELECT * FROM " + table.getName(), "VALUES 'A', null");
    }
}

Example 10 with TestTable

use of io.trino.testing.sql.TestTable in project trino by trinodb.

the class TestJdbcConnectorTest method testTableWithOnlyUnsupportedColumns.

@Test
public void testTableWithOnlyUnsupportedColumns() {
    Session session = Session.builder(getSession()).setSchema("public").build();
    try (TestTable table = new TestTable(onRemoteDatabase(), "unsupported_table", "(geometry_column GEOMETRY)", ImmutableList.of("NULL", "'POINT(7 52)'"))) {
        // SELECT all tables to avoid any optimizations that could skip the table listing
        assertThat(getQueryRunner().execute("SELECT table_name FROM information_schema.tables").getOnlyColumn()).contains(table.getName());
        assertQuery(format("SELECT count(*) FROM information_schema.tables WHERE table_name = '%s'", table.getName()), "SELECT 1");
        assertQuery(format("SELECT count(*) FROM information_schema.columns WHERE table_name = '%s'", table.getName()), "SELECT 0");
        assertQuery(session, format("SHOW TABLES LIKE '%s'", table.getName()), format("SELECT '%s'", table.getName()));
        String unsupportedTableErrorMessage = "Table 'public.*' has no supported columns.*";
        assertQueryFails(session, "SELECT * FROM " + table.getName(), unsupportedTableErrorMessage);
        assertQueryFails(session, "SHOW CREATE TABLE " + table.getName(), unsupportedTableErrorMessage);
        assertQueryFails(session, "SHOW COLUMNS FROM " + table.getName(), unsupportedTableErrorMessage);
        assertQueryFails(session, "DESCRIBE " + table.getName(), unsupportedTableErrorMessage);
    }
}