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

Example 21 with TestTable

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

the class TestMySqlLegacyConnectorTest method testCountDistinctWithStringTypes.

@Test
@Override
public void testCountDistinctWithStringTypes() {
    // Create a table with CHARACTER SET utf8mb4 option in MySQL side to insert unicode values
    List<String> rows = Stream.of("a", "b", "A", "B", " a ", "a", "b", " b ", "ą").map(value -> format("'%1$s', '%1$s'", value)).collect(toImmutableList());
    try (TestTable testTable = new TestTable(onRemoteDatabase(), "tpch.distinct_strings", "(t_char CHAR(5), t_varchar VARCHAR(5) CHARACTER SET utf8mb4)", rows)) {
        // disabling hash generation to prevent extra projections in the plan which make it hard to write matchers for isNotFullyPushedDown
        Session optimizeHashGenerationDisabled = Session.builder(getSession()).setSystemProperty("optimize_hash_generation", "false").build();
        // It is not captured in the `isNotFullyPushedDown` calls (can't do that) but depending on the connector in use some aggregations
        // still can be pushed down to connector.
        // the DISTINCT part of aggregation will still be pushed down to connector as `GROUP BY`. Only the `count` part will remain on the Trino side.
        assertThat(query(optimizeHashGenerationDisabled, "SELECT count(DISTINCT t_varchar) FROM " + testTable.getName())).matches("VALUES BIGINT '7'").isNotFullyPushedDown(AggregationNode.class);
        assertThat(query(optimizeHashGenerationDisabled, "SELECT count(DISTINCT t_char) FROM " + testTable.getName())).matches("VALUES BIGINT '7'").isNotFullyPushedDown(AggregationNode.class);
        assertThat(query("SELECT count(DISTINCT t_char), count(DISTINCT t_varchar) FROM " + testTable.getName())).matches("VALUES (BIGINT '7', BIGINT '7')").isNotFullyPushedDown(MarkDistinctNode.class, ExchangeNode.class, ExchangeNode.class, ProjectNode.class);
    }
}

Example 22 with TestTable

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

the class TestMySqlLegacyConnectorTest method testInsertUnicode.

@Test
@Override
public void testInsertUnicode() {
    // Create tables with CHARACTER SET utf8mb4 option in MySQL side to allow inserting unicode values
    try (TestTable table = new TestTable(onRemoteDatabase(), "tpch.test_insert_unicode_", "(test varchar(50) CHARACTER SET utf8mb4)")) {
        assertUpdate("INSERT INTO " + table.getName() + "(test) VALUES 'Hello', U&'hello\\6d4B\\8Bd5world\\7F16\\7801' ", 2);
        assertThat(computeActual("SELECT test FROM " + table.getName()).getOnlyColumnAsSet()).containsExactlyInAnyOrder("Hello", "hello测试world编码");
    }
    try (TestTable table = new TestTable(onRemoteDatabase(), "tpch.test_insert_unicode_", "(test varchar(50) CHARACTER SET utf8mb4)")) {
        assertUpdate("INSERT INTO " + table.getName() + "(test) VALUES 'aa', 'bé'", 2);
        assertQuery("SELECT test FROM " + table.getName(), "VALUES 'aa', 'bé'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test = 'aa'", "VALUES 'aa'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test > 'ba'", "VALUES 'bé'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test < 'ba'", "VALUES 'aa'");
        assertQueryReturnsEmptyResult("SELECT test FROM " + table.getName() + " WHERE test = 'ba'");
    }
    try (TestTable table = new TestTable(onRemoteDatabase(), "tpch.test_insert_unicode_", "(test varchar(50) CHARACTER SET utf8mb4)")) {
        assertUpdate("INSERT INTO " + table.getName() + "(test) VALUES 'a', 'é'", 2);
        assertQuery("SELECT test FROM " + table.getName(), "VALUES 'a', 'é'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test = 'a'", "VALUES 'a'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test > 'b'", "VALUES 'é'");
        assertQuery("SELECT test FROM " + table.getName() + " WHERE test < 'b'", "VALUES 'a'");
        assertQueryReturnsEmptyResult("SELECT test FROM " + table.getName() + " WHERE test = 'b'");
    }
}

Example 23 with TestTable

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

the class TestPhoenixTypeMapping method testUnsupportedSmallint.

@Test
public void testUnsupportedSmallint() {
    try (TestTable table = new TestTable(new PhoenixSqlExecutor(phoenixServer.getJdbcUrl()), "tpch.test_unsupported_smallint", "(data smallint, pk smallint primary key)")) {
        assertPhoenixQueryFails(// min - 1
        format("INSERT INTO %s VALUES (-32769, 1)", table.getName()), "ERROR 203 (22005): Type mismatch. BIGINT and SMALLINT for expression: -32769 in column 0.DATA");
        assertPhoenixQueryFails(// max + 1
        format("INSERT INTO %s VALUES (32768, 2)", table.getName()), "ERROR 203 (22005): Type mismatch. SMALLINT and INTEGER for 32768");
    }
}

Example 24 with TestTable

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

the class TestPhoenixTypeMapping method testUnsupportedBigInt.

@Test
public void testUnsupportedBigInt() {
    try (TestTable table = new TestTable(new PhoenixSqlExecutor(phoenixServer.getJdbcUrl()), "tpch.test_unsupported_bigint", "(data bigint, pk bigint primary key)")) {
        assertPhoenixQueryFails(// min - 1
        format("INSERT INTO %s VALUES (-9223372036854775809, 1)", table.getName()), "ERROR 203 (22005): Type mismatch. DECIMAL and BIGINT for expression: -9223372036854775809 in column 0.DATA");
        // Phoenix JDBC driver throws ArithmeticException instead of SQLException when the value is larger than max of bigint
        // max + 1
        assertThatThrownBy(() -> new PhoenixSqlExecutor(phoenixServer.getJdbcUrl()).execute(format("INSERT INTO %s VALUES (9223372036854775808, 2)", table.getName()))).isInstanceOf(ArithmeticException.class).hasMessage("Overflow");
    }
}

Example 25 with TestTable

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

the class TestPhoenixTypeMapping method testDecimalUnspecifiedPrecision.

@Test
public void testDecimalUnspecifiedPrecision() {
    PhoenixSqlExecutor phoenixSqlExecutor = new PhoenixSqlExecutor(phoenixServer.getJdbcUrl());
    try (TestTable testTable = new TestTable(phoenixSqlExecutor, "tpch.test_var_decimal", "(pk bigint primary key, d_col decimal)", asList("1, 1.12", "2, 123456.789", "3, -1.12", "4, -123456.789"))) {
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 0), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 0), "SELECT d_col FROM " + testTable.getName(), "VALUES (1), (123457), (-1), (-123457)");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 1), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 1), "SELECT d_col FROM " + testTable.getName(), "VALUES (1.1), (123456.8), (-1.1), (-123456.8)");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 2), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 2), "SELECT d_col FROM " + testTable.getName(), "VALUES (1.12), (123456.79), (-1.12), (-123456.79)");
        assertQuery(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 3), "SELECT d_col FROM " + testTable.getName(), "VALUES (1.12), (123456.789), (-1.12), (-123456.789)");
        assertQueryFails(sessionWithDecimalMappingStrict(CONVERT_TO_VARCHAR), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
    }
}