io.trino.tests.product.hive.TestHiveSparkCompatibility Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of trino-product-tests Show documentation
Trino - Product tests
There is a newer version: 464
/*
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package io.trino.tests.product.hive;

import com.google.common.collect.ImmutableList;
import com.google.inject.Inject;
import io.trino.tempto.ProductTest;
import io.trino.tempto.hadoop.hdfs.HdfsClient;
import org.testng.SkipException;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import static com.google.common.collect.ImmutableList.toImmutableList;
import static io.trino.tempto.assertions.QueryAssert.Row;
import static io.trino.tempto.assertions.QueryAssert.Row.row;
import static io.trino.tempto.assertions.QueryAssert.assertQueryFailure;
import static io.trino.testing.TestingNames.randomNameSuffix;
import static io.trino.tests.product.TestGroups.HIVE_SPARK;
import static io.trino.tests.product.TestGroups.HIVE_SPARK_NO_STATS_FALLBACK;
import static io.trino.tests.product.TestGroups.PROFILE_SPECIFIC_TESTS;
import static io.trino.tests.product.utils.QueryExecutors.onHive;
import static io.trino.tests.product.utils.QueryExecutors.onSpark;
import static io.trino.tests.product.utils.QueryExecutors.onTrino;
import static java.lang.String.format;
import static java.lang.String.join;
import static java.util.Collections.nCopies;
import static java.util.Locale.ENGLISH;
import static org.assertj.core.api.Assertions.assertThat;

public class TestHiveSparkCompatibility
        extends ProductTest
{
    // see spark-defaults.conf
    private static final String TRINO_CATALOG = "hive";

    @Inject
    private HdfsClient hdfsClient;

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS}, dataProvider = "testReadSparkCreatedTableDataProvider")
    public void testReadSparkCreatedTable(String sparkTableFormat, String expectedTrinoTableFormat)
    {
        String sparkTableName = "spark_created_table_" + sparkTableFormat.replaceAll("[^a-zA-Z]", "").toLowerCase(ENGLISH) + "_" + randomNameSuffix();
        String trinoTableName = format("%s.default.%s", TRINO_CATALOG, sparkTableName);

        onSpark().executeQuery(
                "CREATE TABLE default." + sparkTableName + "( " +
                        "  a_boolean boolean, " +
                        "  a_tinyint tinyint, " +
                        "  a_smallint smallint, " +
                        "  an_integer int, " +
                        "  a_bigint bigint, " +
                        "  a_real float, " +
                        "  a_double double, " +
                        "  a_short_decimal decimal(11, 4), " +
                        "  a_long_decimal decimal(26, 7), " +
                        "  a_string string, " +
                        // TODO "  a_binary binary, " +
                        "  a_date date, " +
                        "  a_timestamp_seconds timestamp, " +
                        "  a_timestamp_millis timestamp, " +
                        "  a_timestamp_micros timestamp, " +
                        "  a_timestamp_nanos timestamp, " +
                        // TODO interval
                        // TODO array
                        // TODO struct
                        // TODO map
                        "  a_dummy string) " +
                        sparkTableFormat + " " +
                        // By default Spark creates table as "transactional=true", but doesn't conform to Hive transactional format,
                        // nor file naming convention, so such table cannot be read. As a workaround, force table to be marked
                        // non-transactional.
                        "TBLPROPERTIES ('transactional'='false')");

        // nulls
        onSpark().executeQuery("INSERT INTO " + sparkTableName + " VALUES (" + join(",", nCopies(16, "NULL")) + ")");
        // positive values
        onSpark().executeQuery(
                "INSERT INTO " + sparkTableName + " VALUES (" +
                        "true, " + // a_boolean
                        "127, " + // a_tinyint
                        "32767, " + // a_smallint
                        "1000000000, " + // an_integer
                        "1000000000000000, " + // a_bigint
                        "10000000.123, " + // a_real
                        "100000000000.123, " + // a_double
                        "CAST('1234567.8901' AS decimal(11, 4)), " + // a_short_decimal
                        "CAST('1234567890123456789.0123456' AS decimal(26, 7)), " + // a_short_decimal
                        "'some string', " + // a_string
                        "DATE '2005-09-10', " +  // a_date
                        "TIMESTAMP '2005-09-10 13:00:00', " + // a_timestamp_seconds
                        "TIMESTAMP '2005-09-10 13:00:00.123', " + // a_timestamp_millis
                        "TIMESTAMP '2005-09-10 13:00:00.123456', " + // a_timestamp_micros
                        "TIMESTAMP '2005-09-10 13:00:00.123456789', " + // a_timestamp_nanos
                        "'dummy')");
        // negative values
        onSpark().executeQuery(
                "INSERT INTO " + sparkTableName + " VALUES (" +
                        "false, " + // a_boolean
                        "-128, " + // a_tinyint
                        "-32768, " + // a_smallint
                        "-1000000012, " + // an_integer
                        "-1000000000000012, " + // a_bigint
                        "-10000000.123, " + // a_real
                        "-100000000000.123, " + // a_double
                        "CAST('-1234567.8901' AS decimal(11, 4)), " + // a_short_decimal
                        "CAST('-1234567890123456789.0123456' AS decimal(26, 7)), " + // a_short_decimal
                        "'', " + // a_string
                        "DATE '1965-09-10', " + // a_date
                        "TIMESTAMP '1965-09-10 13:00:00', " + // a_timestamp_seconds
                        "TIMESTAMP '1965-09-10 13:00:00.123', " + // a_timestamp_millis
                        "TIMESTAMP '1965-09-10 13:00:00.123456', " + // a_timestamp_micros
                        "TIMESTAMP '1965-09-10 13:00:00.123456789', " + // a_timestamp_nanos
                        "'dummy')");

        List expected = List.of(
                row(nCopies(16, null).toArray()),
                row(
                        true, // a_boolean≥
                        (byte) 127, // a_tinyint
                        (short) 32767, // a_smallint
                        1000000000, // an_integer
                        1000000000000000L, // a_bigint
                        10000000.123F, // a_real
                        100000000000.123, // a_double
                        new BigDecimal("1234567.8901"), // a_short_decimal
                        new BigDecimal("1234567890123456789.0123456"), // a_long_decimal
                        "some string", // a_string
                        java.sql.Date.valueOf(LocalDate.of(2005, 9, 10)), // a_date
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0)), // a_timestamp_seconds
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0, 123_000_000)), // a_timestamp_millis
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_micros
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_nanos; note that Spark timestamp has microsecond precision
                        "dummy"),
                row(
                        false, // a_bigint
                        (byte) -128, // a_tinyint
                        (short) -32768, // a_smallint
                        -1000000012, // an_integer
                        -1000000000000012L, // a_bigint
                        -10000000.123F, // a_real
                        -100000000000.123, // a_double
                        new BigDecimal("-1234567.8901"), // a_short_decimal
                        new BigDecimal("-1234567890123456789.0123456"), // a_long_decimal
                        "", // a_string
                        java.sql.Date.valueOf(LocalDate.of(1965, 9, 10)), // a_date
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0)), // a_timestamp_seconds
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0, 123_000_000)), // a_timestamp_millis
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_micros
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_nanos; note that Spark timestamp has microsecond precision
                        "dummy"));
        assertThat(onSpark().executeQuery("SELECT * FROM " + sparkTableName)).containsOnly(expected);
        onTrino().executeQuery("SET SESSION hive.timestamp_precision = 'NANOSECONDS'");
        assertThat(onTrino().executeQuery("SELECT * FROM " + trinoTableName)).containsOnly(expected);

        assertThat(onTrino().executeQuery("SHOW CREATE TABLE " + trinoTableName))
                .containsOnly(row(format(
                        "CREATE TABLE %s (\n" +
                                "   a_boolean boolean,\n" +
                                "   a_tinyint tinyint,\n" +
                                "   a_smallint smallint,\n" +
                                "   an_integer integer,\n" +
                                "   a_bigint bigint,\n" +
                                "   a_real real,\n" +
                                "   a_double double,\n" +
                                "   a_short_decimal decimal(11, 4),\n" +
                                "   a_long_decimal decimal(26, 7),\n" +
                                "   a_string varchar,\n" +
                                "   a_date date,\n" +
                                "   a_timestamp_seconds timestamp(9),\n" +
                                "   a_timestamp_millis timestamp(9),\n" +
                                "   a_timestamp_micros timestamp(9),\n" +
                                "   a_timestamp_nanos timestamp(9),\n" +
                                "   a_dummy varchar\n" +
                                ")\n" +
                                "WITH (\n" +
                                "   format = '%s'\n" +
                                ")",
                        trinoTableName,
                        expectedTrinoTableFormat)));

        onSpark().executeQuery("DROP TABLE " + sparkTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS}, dataProvider = "sparkParquetTimestampFormats")
    public void testSparkParquetTimestampCompatibility(String sparkTimestampFormat, String sparkTimestamp, String[] expectedValues)
    {
        String sparkTableName = "test_spark_parquet_timestamp_compatibility_" + sparkTimestampFormat.toLowerCase(ENGLISH) + "_" + randomNameSuffix();
        String trinoTableName = format("%s.default.%s", TRINO_CATALOG, sparkTableName);

        onSpark().executeQuery("SET spark.sql.parquet.outputTimestampType = " + sparkTimestampFormat);
        onSpark().executeQuery(
                "CREATE TABLE default." + sparkTableName + "(a_timestamp timestamp) " +
                        "USING PARQUET " +
                        "TBLPROPERTIES ('transactional'='false')");

        onSpark().executeQuery("INSERT INTO " + sparkTableName + " VALUES (TIMESTAMP '" + sparkTimestamp + "')");

        for (int i = 0; i < HIVE_TIMESTAMP_PRECISIONS.length; i++) {
            String trinoTimestampPrecision = HIVE_TIMESTAMP_PRECISIONS[i];
            String expected = expectedValues[i];
            onTrino().executeQuery("SET SESSION hive.timestamp_precision = '" + trinoTimestampPrecision + "'");
            assertThat(onTrino().executeQuery("SELECT * FROM " + trinoTableName)).containsOnly(row(java.sql.Timestamp.valueOf(expected)));
            assertThat(onTrino().executeQuery("SELECT count(*) FROM " + trinoTableName + " WHERE a_timestamp = TIMESTAMP '" + expected + "'")).containsOnly(row(1));
            assertThat(onTrino().executeQuery("SELECT count(*) FROM " + trinoTableName + " WHERE a_timestamp != TIMESTAMP '" + expected + "'")).containsOnly(row(0));
        }

        onSpark().executeQuery("DROP TABLE " + sparkTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testSparkClusteringCaseSensitiveCompatibility()
    {
        String sparkTableNameWithClusteringDifferentCase = "test_spark_clustering_case_sensitive_" + randomNameSuffix();
        onSpark().executeQuery(
                String.format("CREATE TABLE %s (row_id int, `segment_id` int, value long) ", sparkTableNameWithClusteringDifferentCase) +
                        "USING PARQUET " +
                        "PARTITIONED BY (`part` string) " +
                        "CLUSTERED BY (`SEGMENT_ID`) " +
                        "  SORTED BY (`SEGMENT_ID`) " +
                        "  INTO 10 BUCKETS");

        onSpark().executeQuery(format("INSERT INTO %s ", sparkTableNameWithClusteringDifferentCase) +
                "VALUES " +
                "  (1, 1, 100, 'part1')," +
                "  (100, 1, 123, 'part2')," +
                "  (101, 2, 202, 'part2')");

        // Ensure that Trino can successfully read from the Spark bucketed table even though the clustering
        // column `SEGMENT_ID` is in a different case than the data column `segment_id`
        assertThat(onTrino().executeQuery(format("SELECT * FROM %s.default.%s", TRINO_CATALOG, sparkTableNameWithClusteringDifferentCase)))
                .containsOnly(List.of(
                        row(1, 1, 100, "part1"),
                        row(100, 1, 123, "part2"),
                        row(101, 2, 202, "part2")));
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testSparkParquetBloomFilterCompatibility()
    {
        String sparkTableNameWithBloomFilter = "test_spark_parquet_bloom_filter_compatibility_enabled_" + randomNameSuffix();
        String sparkTableNameNoBloomFilter = "test_spark_parquet_bloom_filter_compatibility_disabled_" + randomNameSuffix();

        // disable dictionary predicate when testing bloom filter predicate
        onSpark().executeQuery(
                String.format("CREATE TABLE %s (testInteger INT, testLong Long, testString STRING, testDouble DOUBLE, testFloat FLOAT) ", sparkTableNameWithBloomFilter) +
                        "USING PARQUET OPTIONS (" +
                        "'parquet.bloom.filter.enabled'='true'," +
                        "'parquet.enable.dictionary'='false'" +
                        ")");
        onSpark().executeQuery(
                String.format("CREATE TABLE %s (testInteger INT, testLong Long, testString STRING, testDouble DOUBLE, testFloat FLOAT) ", sparkTableNameNoBloomFilter) +
                        "USING PARQUET OPTIONS (" +
                        "'parquet.bloom.filter.enabled'='false'," +
                        "'parquet.enable.dictionary'='false'" +
                        ")");
        String[] sparkTables = new String[] {sparkTableNameWithBloomFilter, sparkTableNameNoBloomFilter};
        String[] trinoTables = new String[] {
                format("%s.default.%s", TRINO_CATALOG, sparkTableNameWithBloomFilter),
                format("%s.default.%s", TRINO_CATALOG, sparkTableNameNoBloomFilter)};

        // control number of spark output files via hint: https://issues.apache.org/jira/browse/SPARK-24940
        // contain values such as aaaaaaaaaaa and zzzzzzzzzzz, this made sure file level statistics: min and max won't take effect
        for (String sparkTable : sparkTables) {
            onSpark().executeQuery(format(
                    "INSERT INTO %s " +
                            "SELECT /*+ REPARTITION(1) */  testInteger, testLong, testString, testDouble, testFloat FROM VALUES " +
                            "  (-999999, -999999, 'aaaaaaaaaaa', -9999999999.99D, -9999999.9999F)" +
                            ", (3, 30, 'fdsvxxbv33cb', 97662.2D, 98862.2F)" +
                            ", (5324, 2466, 'refgfdfrexx', 8796.1D, -65496.1F)" +
                            ", (999999, 9999999999999, 'zzzzzzzzzzz', 9999999999.99D, -9999999.9999F)" +
                            ", (9444, 4132455, 'ff34322vxff', 32137758.7892D, 9978.129887F) AS DATA(testInteger, testLong, testString, testDouble, testFloat)",
                    sparkTable));
        }

        // explicitly make sure using bloom filter statistics
        onTrino().executeQuery("set session hive.parquet_use_bloom_filter=true");
        assertTrinoBloomFilterTableSelectResult(trinoTables);

        // explicitly make sure not using bloom filter statistics
        onTrino().executeQuery("set session hive.parquet_use_bloom_filter=false");
        assertTrinoBloomFilterTableSelectResult(trinoTables);

        for (String sparkTable : sparkTables) {
            onSpark().executeQuery("DROP TABLE " + sparkTable);
        }
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testTrinoSparkParquetBloomFilterCompatibility()
    {
        String trinoTableNameWithBloomFilter = "test_trino_spark_parquet_bloom_filter_compatibility_enabled_" + randomNameSuffix();
        String trioTableNameNoBloomFilter = "test_trino_spark_parquet_bloom_filter_compatibility_disabled_" + randomNameSuffix();

        onTrino().executeQuery(
                String.format("CREATE TABLE %s.default.%s (testInteger INTEGER, testLong BIGINT, testString VARCHAR, testDouble DOUBLE, testFloat REAL) ", TRINO_CATALOG, trinoTableNameWithBloomFilter) +
                        "WITH (" +
                        "format = 'PARQUET'," +
                        "parquet_bloom_filter_columns = ARRAY['testInteger', 'testLong', 'testString', 'testDouble', 'testFloat']" +
                        ")");
        onTrino().executeQuery(
                String.format("CREATE TABLE %s.default.%s (testInteger INTEGER, testLong BIGINT, testString VARCHAR, testDouble DOUBLE, testFloat REAL) WITH (FORMAT = 'PARQUET')", TRINO_CATALOG, trioTableNameNoBloomFilter));
        String[] sparkTables = new String[] {trinoTableNameWithBloomFilter, trioTableNameNoBloomFilter};
        String[] trinoTables = new String[] {
                format("%s.default.%s", TRINO_CATALOG, trinoTableNameWithBloomFilter),
                format("%s.default.%s", TRINO_CATALOG, trioTableNameNoBloomFilter)};

        for (String trinoTable : trinoTables) {
            onTrino().executeQuery(format(
                    "INSERT INTO %s " +
                            "SELECT testInteger, testLong, testString, testDouble, testFloat FROM (VALUES " +
                            "  (-999999, -999999, 'aaaaaaaaaaa', DOUBLE '-9999999999.99', REAL '-9999999.9999')" +
                            ", (3, 30, 'fdsvxxbv33cb', DOUBLE '97662.2', REAL '98862.2')" +
                            ", (5324, 2466, 'refgfdfrexx', DOUBLE '8796.1', REAL '-65496.1')" +
                            ", (999999, 9999999999999, 'zzzzzzzzzzz', DOUBLE '9999999999.99', REAL '-9999999.9999')" +
                            ", (9444, 4132455, 'ff34322vxff', DOUBLE '32137758.7892', REAL '9978.129887')) AS DATA(testInteger, testLong, testString, testDouble, testFloat)",
                    trinoTable));
        }

        assertSparkBloomFilterTableSelectResult(sparkTables);

        for (String trinoTable : trinoTables) {
            onTrino().executeQuery("DROP TABLE " + trinoTable);
        }
    }

    private static void assertTrinoBloomFilterTableSelectResult(String[] trinoTables)
    {
        for (String trinoTable : trinoTables) {
            assertThat(onTrino().executeQuery("SELECT COUNT(*) FROM " + trinoTable + " WHERE testInteger IN (9444, -88777, 6711111)")).containsOnly(List.of(row(1)));
            assertThat(onTrino().executeQuery("SELECT COUNT(*) FROM " + trinoTable + " WHERE testLong IN (4132455, 321324, 312321321322)")).containsOnly(List.of(row(1)));
            assertThat(onTrino().executeQuery("SELECT COUNT(*) FROM " + trinoTable + " WHERE testString IN ('fdsvxxbv33cb', 'cxxx322', 'cxxx323')")).containsOnly(List.of(row(1)));
            assertThat(onTrino().executeQuery("SELECT COUNT(*) FROM " + trinoTable + " WHERE testDouble IN (DOUBLE '97662.2', DOUBLE '-97221.2', DOUBLE '-88777.22233')")).containsOnly(List.of(row(1)));
            assertThat(onTrino().executeQuery("SELECT COUNT(*) FROM " + trinoTable + " WHERE testFloat IN (REAL '-65496.1', REAL '98211862.2', REAL '6761111555.1222')")).containsOnly(List.of(row(1)));
        }
    }

    private static void assertSparkBloomFilterTableSelectResult(String[] sparkTables)
    {
        for (String sparkTable : sparkTables) {
            assertThat(onSpark().executeQuery("SELECT COUNT(*) FROM " + sparkTable + " WHERE testInteger IN (9444, -88777, 6711111)")).containsOnly(List.of(row(1)));
            assertThat(onSpark().executeQuery("SELECT COUNT(*) FROM " + sparkTable + " WHERE testLong IN (4132455, 321324, 312321321322)")).containsOnly(List.of(row(1)));
            assertThat(onSpark().executeQuery("SELECT COUNT(*) FROM " + sparkTable + " WHERE testString IN ('fdsvxxbv33cb', 'cxxx322', 'cxxx323')")).containsOnly(List.of(row(1)));
            assertThat(onSpark().executeQuery("SELECT COUNT(*) FROM " + sparkTable + " WHERE testDouble IN (97662.2D, -97221.2D, -88777.22233D)")).containsOnly(List.of(row(1)));
            assertThat(onSpark().executeQuery("SELECT COUNT(*) FROM " + sparkTable + " WHERE testFloat IN (-65496.1F, 98211862.2F, 6761111555.1222F)")).containsOnly(List.of(row(1)));
        }
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testInsertFailsOnBucketedTableCreatedBySpark()
    {
        String hiveTableName = "spark_insert_bucketed_table_" + randomNameSuffix();

        onSpark().executeQuery(
                "CREATE TABLE default." + hiveTableName + "(a_key integer, a_value integer) " +
                        "USING PARQUET " +
                        "CLUSTERED BY (a_key) INTO 3 BUCKETS");

        assertQueryFailure(() -> onTrino().executeQuery("INSERT INTO default." + hiveTableName + " VALUES (1, 100)"))
                .hasMessageContaining("Inserting into Spark bucketed tables is not supported");

        onSpark().executeQuery("DROP TABLE " + hiveTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testUpdateFailsOnBucketedTableCreatedBySpark()
    {
        String hiveTableName = "spark_update_bucketed_table_" + randomNameSuffix();

        onSpark().executeQuery(
                "CREATE TABLE default." + hiveTableName + "(a_key integer, a_value integer) " +
                        "USING ORC " +
                        "CLUSTERED BY (a_key) INTO 3 BUCKETS");

        assertQueryFailure(() -> onTrino().executeQuery("UPDATE default." + hiveTableName + " SET a_value = 100 WHERE a_key = 1"))
                .hasMessageContaining("Merging into Spark bucketed tables is not supported");

        onSpark().executeQuery("DROP TABLE " + hiveTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testDeleteFailsOnBucketedTableCreatedBySpark()
    {
        String hiveTableName = "spark_delete_bucketed_table_" + randomNameSuffix();

        onSpark().executeQuery(
                "CREATE TABLE default." + hiveTableName + "(a_key integer, a_value integer) " +
                        "USING ORC " +
                        "CLUSTERED BY (a_key) INTO 3 BUCKETS");

        assertQueryFailure(() -> onTrino().executeQuery("DELETE FROM default." + hiveTableName + " WHERE a_key = 1"))
                .hasMessageContaining("Merging into Spark bucketed tables is not supported");

        onSpark().executeQuery("DROP TABLE " + hiveTableName);
    }

    private static final String[] HIVE_TIMESTAMP_PRECISIONS = new String[] {"MILLISECONDS", "MICROSECONDS", "NANOSECONDS"};

    @DataProvider
    public static Object[][] sparkParquetTimestampFormats()
    {
        String millisTimestamp = "2005-09-10 13:00:00.123";
        String microsTimestamp = "2005-09-10 13:00:00.123456";
        String nanosTimestamp = "2005-09-10 13:00:00.123456789";

        // Ordering of expected values matches the ordering in HIVE_TIMESTAMP_PRECISIONS
        return new Object[][] {
                {"TIMESTAMP_MILLIS", millisTimestamp, new String[] {millisTimestamp, millisTimestamp, millisTimestamp}},
                {"TIMESTAMP_MICROS", microsTimestamp, new String[] {millisTimestamp, microsTimestamp, microsTimestamp}},
                // note that Spark timestamp has microsecond precision
                {"INT96", nanosTimestamp, new String[] {millisTimestamp, microsTimestamp, microsTimestamp}},
        };
    }

    @DataProvider
    public static Object[][] testReadSparkCreatedTableDataProvider()
    {
        return new Object[][] {
                {"USING ORC", "ORC"},
                {"USING PARQUET", "PARQUET"},
                // TODO add Avro
        };
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testReadTrinoCreatedOrcTable()
    {
        testReadTrinoCreatedTable("using_orc", "ORC");
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testReadTrinoCreatedParquetTable()
    {
        testReadTrinoCreatedTable("using_parquet", "PARQUET");
    }

    private void testReadTrinoCreatedTable(String tableName, String tableFormat)
    {
        String sparkTableName = "trino_created_table_" + tableName + "_" + randomNameSuffix();
        String trinoTableName = format("%s.default.%s", TRINO_CATALOG, sparkTableName);

        // Spark timestamps are in microsecond precision
        onTrino().executeQuery("SET SESSION hive.timestamp_precision = 'MICROSECONDS'");
        onTrino().executeQuery(format(
                "CREATE TABLE %s ( " +
                        "   a_boolean boolean, " +
                        "   a_tinyint tinyint, " +
                        "   a_smallint smallint, " +
                        "   an_integer integer, " +
                        "   a_bigint bigint, " +
                        "   a_real real, " +
                        "   a_double double, " +
                        "   a_short_decimal decimal(11, 4), " +
                        "   a_long_decimal decimal(26, 7), " +
                        "   a_string varchar, " +
                        // TODO binary
                        "   a_date date, " +
                        "   a_timestamp_seconds timestamp(6), " +
                        "   a_timestamp_millis timestamp(6), " +
                        "   a_timestamp_micros timestamp(6), " +
                        // TODO interval
                        // TODO array
                        // TODO struct
                        // TODO map
                        "   a_dummy varchar " +
                        ") " +
                        "WITH ( " +
                        "   format = '%s' " +
                        ")",
                trinoTableName,
                tableFormat));

        // nulls
        onTrino().executeQuery("INSERT INTO " + trinoTableName + " VALUES (" + join(",", nCopies(15, "NULL")) + ")");
        // positive values
        onTrino().executeQuery(
                "INSERT INTO " + trinoTableName + " VALUES (" +
                        "true, " + // a_boolean
                        "127, " + // a_tinyint
                        "32767, " + // a_smallint
                        "1000000000, " + // an_integer
                        "1000000000000000, " + // a_bigint
                        "10000000.123, " + // a_real
                        "100000000000.123, " + // a_double
                        "CAST('1234567.8901' AS decimal(11, 4)), " + // a_short_decimal
                        "CAST('1234567890123456789.0123456' AS decimal(26, 7)), " + // a_short_decimal
                        "'some string', " + // a_string
                        "DATE '2005-09-10', " +  // a_date
                        "TIMESTAMP '2005-09-10 13:00:00', " + // a_timestamp_seconds
                        "TIMESTAMP '2005-09-10 13:00:00.123', " + // a_timestamp_millis
                        "TIMESTAMP '2005-09-10 13:00:00.123456', " + // a_timestamp_micros
                        "'dummy')");
        // negative values
        onTrino().executeQuery(
                "INSERT INTO " + trinoTableName + " VALUES (" +
                        "false, " + // a_boolean
                        "-128, " + // a_tinyint
                        "-32768, " + // a_smallint
                        "-1000000012, " + // an_integer
                        "-1000000000000012, " + // a_bigint
                        "-10000000.123, " + // a_real
                        "-100000000000.123, " + // a_double
                        "CAST('-1234567.8901' AS decimal(11, 4)), " + // a_short_decimal
                        "CAST('-1234567890123456789.0123456' AS decimal(26, 7)), " + // a_short_decimal
                        "'', " + // a_string
                        "DATE '1965-09-10', " + // a_date
                        "TIMESTAMP '1965-09-10 13:00:00', " + // a_timestamp_seconds
                        "TIMESTAMP '1965-09-10 13:00:00.123', " + // a_timestamp_millis
                        "TIMESTAMP '1965-09-10 13:00:00.123456', " + // a_timestamp_micros
                        "'dummy')");

        List expected = List.of(
                row(nCopies(15, null).toArray()),
                row(
                        true, // a_boolean
                        (byte) 127, // a_tinyint
                        (short) 32767, // a_smallint
                        1000000000, // an_integer
                        1000000000000000L, // a_bigint
                        10000000.123F, // a_real
                        100000000000.123, // a_double
                        new BigDecimal("1234567.8901"), // a_short_decimal
                        new BigDecimal("1234567890123456789.0123456"), // a_long_decimal
                        "some string", // a_string
                        java.sql.Date.valueOf(LocalDate.of(2005, 9, 10)), // a_date
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0)), // a_timestamp_seconds
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0, 123_000_000)), // a_timestamp_millis
                        java.sql.Timestamp.valueOf(LocalDateTime.of(2005, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_micros
                        "dummy"),
                row(
                        false, // a_bigint
                        (byte) -128, // a_tinyint
                        (short) -32768, // a_smallint
                        -1000000012, // an_integer
                        -1000000000000012L, // a_bigint
                        -10000000.123F, // a_real
                        -100000000000.123, // a_double
                        new BigDecimal("-1234567.8901"), // a_short_decimal
                        new BigDecimal("-1234567890123456789.0123456"), // a_long_decimal
                        "", // a_string
                        java.sql.Date.valueOf(LocalDate.of(1965, 9, 10)), // a_date
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0)), // a_timestamp_seconds
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0, 123_000_000)), // a_timestamp_millis
                        java.sql.Timestamp.valueOf(LocalDateTime.of(1965, 9, 10, 13, 0, 0, 123_456_000)), // a_timestamp_micros
                        "dummy"));
        assertThat(onSpark().executeQuery("SELECT * FROM " + sparkTableName)).containsOnly(expected);
        assertThat(onTrino().executeQuery("SELECT * FROM " + trinoTableName)).containsOnly(expected);

        onTrino().executeQuery("DROP TABLE " + trinoTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testReadSparkdDateAndTimePartitionName()
    {
        String sparkTableName = "test_trino_reading_spark_date_and_time_type_partitioned_" + randomNameSuffix();
        String trinoTableName = format("%s.default.%s", TRINO_CATALOG, sparkTableName);

        onSpark().executeQuery(format("CREATE TABLE default.%s (value integer) PARTITIONED BY (dt date)", sparkTableName));

        // Spark allows creating partition with time unit
        // Hive denies creating such partitions, but allows reading
        onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='2022-04-13 00:00:00.000000000') VALUES (1)", sparkTableName));
        onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='2022-04-13 00:00:00') VALUES (2)", sparkTableName));
        onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='2022-04-13 00:00') VALUES (3)", sparkTableName));
        onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='12345-06-07') VALUES (4)", sparkTableName));
        /// This INSERT statement was supported in older Spark version
        assertQueryFailure(() -> onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='123-04-05') VALUES (5)", sparkTableName)))
                .hasMessageContaining("cannot be cast to \"DATE\" because it is malformed");
        onSpark().executeQuery(format("INSERT INTO %s PARTITION(dt='-0001-01-01') VALUES (6)", sparkTableName));

        assertThat(onTrino().executeQuery("SELECT value, \"$partition\" FROM " + trinoTableName))
                .containsOnly(List.of(
                        row(1, "dt=2022-04-13"),
                        row(2, "dt=2022-04-13"),
                        row(3, "dt=2022-04-13"),
                        row(4, "dt=+12345-06-07"),
                        row(6, "dt=-0001-01-01")));

        // Use date_format function to avoid exception due to java.sql.Date.valueOf() with 5 digit year
        assertThat(onSpark().executeQuery("SELECT value, date_format(dt, 'yyyy-MM-dd') FROM " + sparkTableName))
                .containsOnly(List.of(
                        row(1, "2022-04-13"),
                        row(2, "2022-04-13"),
                        row(3, "2022-04-13"),
                        row(4, "+12345-06-07"),
                        row(6, "-0001-01-01")));

        // Use date_format function to avoid exception due to java.sql.Date.valueOf() with 5 digit year
        assertThat(onHive().executeQuery("SELECT value, date_format(dt, 'yyyy-MM-dd') FROM " + sparkTableName))
                .containsOnly(List.of(
                        row(1, "2022-04-13"),
                        row(2, "2022-04-13"),
                        row(3, "2022-04-13"),
                        row(4, null),
                        row(6, "0002-01-03")));

        // Cast to varchar so that we can compare with Spark & Hive easily
        assertThat(onTrino().executeQuery("SELECT value, CAST(dt AS VARCHAR) FROM " + trinoTableName))
                .containsOnly(List.of(
                        row(1, "2022-04-13"),
                        row(2, "2022-04-13"),
                        row(3, "2022-04-13"),
                        row(4, "12345-06-07"),
                        row(6, "-0001-01-01")));

        onTrino().executeQuery("DROP TABLE " + trinoTableName);
    }

    @Test(groups = {HIVE_SPARK, PROFILE_SPECIFIC_TESTS})
    public void testTextInputFormatWithParquetHiveSerDe()
            throws IOException
    {
        String tableName = "test_text_input_format_with_parquet_hive_ser_de" + randomNameSuffix();
        onHive().executeQuery("" +
                "CREATE EXTERNAL TABLE " + tableName +
                "(col INT) " +
                "ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' " +
                "STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' " +
                "OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' " +
                "LOCATION '/tmp/" + tableName + "'");
        onSpark().executeQuery("INSERT INTO " + tableName + " VALUES(1)");
        assertThat(onSpark().executeQuery("SELECT * FROM " + tableName)).containsOnly(row(1));
        assertThat(onTrino().executeQuery("SELECT * FROM " + tableName)).containsOnly(row(1));
        List files = hdfsClient.listDirectory("/tmp/" + tableName + "/");
        assertThat(files).hasSize(2);
        assertThat(files.stream().filter(name -> !name.contains("SUCCESS")).collect(toImmutableList()).get(0)).endsWith("parquet");
        List