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

Example 6 with JdbcSqlExecutor

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

the class TestPostgreSqlTypeMapping method testUnsupportedDataTypeAsIgnored.

private void testUnsupportedDataTypeAsIgnored(Session session, String dataTypeName, String databaseValue) {
    JdbcSqlExecutor jdbcSqlExecutor = new JdbcSqlExecutor(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
    try (TestTable table = new TestTable(jdbcSqlExecutor, "unsupported_type", format("(key varchar(5), unsupported_column %s)", dataTypeName), ImmutableList.of("'1', NULL", "'2', " + databaseValue))) {
        assertQuery(session, "SELECT * FROM " + table.getName(), "VALUES 1, 2");
        assertQuery(session, "DESC " + table.getName(), // no 'unsupported_column'
        "VALUES ('key', 'varchar(5)','', '')");
        assertUpdate(session, format("INSERT INTO %s VALUES '3'", table.getName()), 1);
        assertQuery(session, "SELECT * FROM " + table.getName(), "VALUES '1', '2', '3'");
    }
}

Example 7 with JdbcSqlExecutor

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

the class TestPostgreSqlTypeMapping method testDecimalUnspecifiedPrecisionWithExceedingValue.

@Test
public void testDecimalUnspecifiedPrecisionWithExceedingValue() {
    JdbcSqlExecutor jdbcSqlExecutor = new JdbcSqlExecutor(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
    try (TestTable testTable = new TestTable(jdbcSqlExecutor, "test_var_decimal_with_exceeding_value", "(key varchar(5), d_col decimal)", asList("NULL, '1.12'", "NULL, '1234567890123456789012345678901234567890.1234567'"))) {
        assertQuery(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 0), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('key', 'varchar(5)'),('d_col', 'decimal(38,0)')");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 0), "SELECT * FROM " + testTable.getName(), "Rounding necessary");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(HALF_UP, 0), "SELECT * FROM " + testTable.getName(), "Decimal overflow");
        assertQuery(sessionWithDecimalMappingStrict(CONVERT_TO_VARCHAR), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('key', 'varchar(5)'),('d_col', 'varchar')");
        assertQuery(sessionWithDecimalMappingStrict(CONVERT_TO_VARCHAR), "SELECT * FROM " + testTable.getName(), "VALUES (NULL, '1.12'), (NULL, '1234567890123456789012345678901234567890.1234567')");
        assertQuery(sessionWithDecimalMappingStrict(IGNORE), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('key', 'varchar(5)')");
    }
}

Example 8 with JdbcSqlExecutor

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

the class TestPostgreSqlTypeMapping method testDecimalExceedingPrecisionMaxWithNonExceedingIntegerValues.

@Test
public void testDecimalExceedingPrecisionMaxWithNonExceedingIntegerValues() {
    JdbcSqlExecutor jdbcSqlExecutor = new JdbcSqlExecutor(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
    try (TestTable testTable = new TestTable(jdbcSqlExecutor, "test_exceeding_max_decimal", "(d_col decimal(60,20))", asList("123456789012345678901234567890.123456789012345", "-123456789012345678901234567890.123456789012345"))) {
        assertQuery(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 0), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('d_col', 'decimal(38,0)')");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 0), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 0), "SELECT d_col FROM " + testTable.getName(), "VALUES (123456789012345678901234567890), (-123456789012345678901234567890)");
        assertQuery(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 8), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('d_col', 'decimal(38,8)')");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(UNNECESSARY, 8), "SELECT d_col FROM " + testTable.getName(), "Rounding necessary");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 8), "SELECT d_col FROM " + testTable.getName(), "VALUES (123456789012345678901234567890.12345679), (-123456789012345678901234567890.12345679)");
        assertQuery(sessionWithDecimalMappingAllowOverflow(HALF_UP, 22), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('d_col', 'decimal(38,20)')");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(HALF_UP, 20), "SELECT d_col FROM " + testTable.getName(), "Decimal overflow");
        assertQueryFails(sessionWithDecimalMappingAllowOverflow(HALF_UP, 9), "SELECT d_col FROM " + testTable.getName(), "Decimal overflow");
        assertQuery(sessionWithDecimalMappingStrict(CONVERT_TO_VARCHAR), format("SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = '%s'", testTable.getName()), "VALUES ('d_col', 'varchar')");
        assertQuery(sessionWithDecimalMappingStrict(CONVERT_TO_VARCHAR), "SELECT d_col FROM " + testTable.getName(), "VALUES ('123456789012345678901234567890.12345678901234500000'), ('-123456789012345678901234567890.12345678901234500000')");
    }
}

Example 9 with JdbcSqlExecutor

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

the class TestPostgreSqlTypeMapping method testForcedMappingToVarchar.

@Test
public void testForcedMappingToVarchar() {
    JdbcSqlExecutor jdbcSqlExecutor = new JdbcSqlExecutor(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
    jdbcSqlExecutor.execute("CREATE TABLE test_forced_varchar_mapping(tsrange_col tsrange, inet_col inet, tsrange_arr_col tsrange[], unsupported_nonforced_column tstzrange)");
    jdbcSqlExecutor.execute("INSERT INTO test_forced_varchar_mapping(tsrange_col, inet_col, tsrange_arr_col, unsupported_nonforced_column) " + "VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange, '172.0.0.1'::inet, array['[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange], '[2010-01-01 14:30, 2010-01-01 15:30)'::tstzrange)");
    try {
        assertQuery(sessionWithArrayAsArray(), "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = 'tpch' AND table_name = 'test_forced_varchar_mapping'", // no 'unsupported_nonforced_column'
        "VALUES ('tsrange_col','varchar'),('inet_col','varchar'),('tsrange_arr_col','array(varchar)')");
        assertQuery(sessionWithArrayAsArray(), "SELECT * FROM test_forced_varchar_mapping", "VALUES ('[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")','172.0.0.1',ARRAY['[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")'])");
        // test predicate pushdown to column that has forced varchar mapping
        assertThat(query("SELECT 1 FROM test_forced_varchar_mapping WHERE tsrange_col = '[\"2010-01-01 14:30:00\",\"2010-01-01 15:30:00\")'")).matches("VALUES 1").isNotFullyPushedDown(FilterNode.class);
        assertThat(query("SELECT 1 FROM test_forced_varchar_mapping WHERE tsrange_col = 'some value'")).returnsEmptyResult().isNotFullyPushedDown(FilterNode.class);
        // test insert into column that has forced varchar mapping
        assertQueryFails("INSERT INTO test_forced_varchar_mapping (tsrange_col) VALUES ('some value')", "Underlying type that is mapped to VARCHAR is not supported for INSERT: tsrange");
    } finally {
        jdbcSqlExecutor.execute("DROP TABLE test_forced_varchar_mapping");
    }
}

Example 10 with JdbcSqlExecutor

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

the class TestPostgreSqlTypeMapping method testUnsupportedDataTypeConvertedToVarchar.

private void testUnsupportedDataTypeConvertedToVarchar(Session session, String dataTypeName, String internalDataTypeName, String databaseValue, String trinoValue) {
    JdbcSqlExecutor jdbcSqlExecutor = new JdbcSqlExecutor(postgreSqlServer.getJdbcUrl(), postgreSqlServer.getProperties());
    try (TestTable table = new TestTable(jdbcSqlExecutor, "unsupported_type", format("(key varchar(5), unsupported_column %s)", dataTypeName), ImmutableList.of("1, NULL", "2, " + databaseValue))) {
        Session convertToVarchar = Session.builder(session).setCatalogSessionProperty("postgresql", UNSUPPORTED_TYPE_HANDLING, CONVERT_TO_VARCHAR.name()).build();
        assertQuery(convertToVarchar, "SELECT * FROM " + table.getName(), format("VALUES ('1', NULL), ('2', %s)", trinoValue));
        assertQuery(convertToVarchar, format("SELECT key FROM %s WHERE unsupported_column = %s", table.getName(), trinoValue), "VALUES '2'");
        assertQuery(convertToVarchar, "DESC " + table.getName(), "VALUES " + "('key', 'varchar(5)', '', ''), " + "('unsupported_column', 'varchar', '', '')");
        assertUpdate(convertToVarchar, format("INSERT INTO %s (key, unsupported_column) VALUES ('3', NULL)", table.getName()), 1);
        assertQueryFails(convertToVarchar, format("INSERT INTO %s (key, unsupported_column) VALUES ('4', %s)", table.getName(), trinoValue), "\\QUnderlying type that is mapped to VARCHAR is not supported for INSERT: " + internalDataTypeName);
        assertUpdate(convertToVarchar, format("INSERT INTO %s (key) VALUES '5'", table.getName()), 1);
        assertQuery(convertToVarchar, "SELECT * FROM " + table.getName(), format("VALUES ('1', NULL), ('2', %s), ('3', NULL), ('5', NULL)", trinoValue));
    }
}