<commit_before>/*
 * 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.plugin.sqlserver;

import com.google.common.collect.ImmutableList;
import io.trino.Session;
import io.trino.spi.type.TimeZoneKey;
import io.trino.testing.AbstractTestQueryFramework;
import io.trino.testing.TestingSession;
import io.trino.testing.datatype.CreateAndInsertDataSetup;
import io.trino.testing.datatype.CreateAndTrinoInsertDataSetup;
import io.trino.testing.datatype.CreateAsSelectDataSetup;
import io.trino.testing.datatype.DataSetup;
import io.trino.testing.datatype.SqlDataTypeTest;
import io.trino.testing.sql.SqlExecutor;
import io.trino.testing.sql.TestTable;
import io.trino.testing.sql.TrinoSqlExecutor;
import org.intellij.lang.annotations.Language;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.function.Function;

import static com.google.common.base.Preconditions.checkState;
import static com.google.common.base.Verify.verify;
import static io.trino.spi.type.BigintType.BIGINT;
import static io.trino.spi.type.BooleanType.BOOLEAN;
import static io.trino.spi.type.CharType.createCharType;
import static io.trino.spi.type.DateType.DATE;
import static io.trino.spi.type.DecimalType.createDecimalType;
import static io.trino.spi.type.DoubleType.DOUBLE;
import static io.trino.spi.type.IntegerType.INTEGER;
import static io.trino.spi.type.RealType.REAL;
import static io.trino.spi.type.SmallintType.SMALLINT;
import static io.trino.spi.type.TimeType.createTimeType;
import static io.trino.spi.type.TimeZoneKey.UTC_KEY;
import static io.trino.spi.type.TimestampType.createTimestampType;
import static io.trino.spi.type.TimestampWithTimeZoneType.createTimestampWithTimeZoneType;
import static io.trino.spi.type.VarbinaryType.VARBINARY;
import static io.trino.spi.type.VarcharType.createUnboundedVarcharType;
import static io.trino.spi.type.VarcharType.createVarcharType;
import static io.trino.testing.sql.TestTable.randomTableSuffix;
import static java.lang.String.format;
import static java.time.ZoneOffset.UTC;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

public abstract class BaseSqlServerTypeMapping
        extends AbstractTestQueryFramework
{
    private final ZoneId jvmZone = ZoneId.systemDefault();
    private final LocalDateTime timeGapInJvmZone1 = LocalDateTime.of(1970, 1, 1, 0, 13, 42);
    private final LocalDateTime timeGapInJvmZone2 = LocalDateTime.of(2018, 4, 1, 2, 13, 55, 123_000_000);
    private final LocalDateTime timeDoubledInJvmZone = LocalDateTime.of(2018, 10, 28, 1, 33, 17, 456_000_000);

    // no DST in 1970, but has DST in later years (e.g. 2018)
    private final ZoneId vilnius = ZoneId.of("Europe/Vilnius");
    private final LocalDateTime timeGapInVilnius = LocalDateTime.of(2018, 3, 25, 3, 17, 17);
    private final LocalDateTime timeDoubledInVilnius = LocalDateTime.of(2018, 10, 28, 3, 33, 33, 333_000_000);

    // minutes offset change since 1970-01-01, no DST
    private final ZoneId kathmandu = ZoneId.of("Asia/Kathmandu");
    private final LocalDateTime timeGapInKathmandu = LocalDateTime.of(1986, 1, 1, 0, 13, 7);

    protected TestingSqlServer sqlServer;

    @BeforeClass
    public void setUp()
    {
        checkIsGap(jvmZone, timeGapInJvmZone1);
        checkIsGap(jvmZone, timeGapInJvmZone2);
        checkIsDoubled(jvmZone, timeDoubledInJvmZone);

        checkIsGap(vilnius, timeGapInVilnius);
        checkIsDoubled(vilnius, timeDoubledInVilnius);

        checkIsGap(kathmandu, timeGapInKathmandu);
    }

    @Test
    public void testTrinoBoolean()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("boolean", "null", BOOLEAN, "CAST(NULL AS BOOLEAN)")
                .addRoundTrip("boolean", "true", BOOLEAN)
                .addRoundTrip("boolean", "false", BOOLEAN)
                .execute(getQueryRunner(), trinoCreateAsSelect("test_boolean"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_boolean"));
    }

    @Test
    public void testSqlServerBit()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("bit", "null", BOOLEAN, "CAST(NULL AS BOOLEAN)")
                .addRoundTrip("bit", "1", BOOLEAN, "true")
                .addRoundTrip("bit", "0", BOOLEAN, "false")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_bit"));
    }

    @Test
    public void testTinyint()
    {
        // Map SQL Server TINYINT to Trino SMALLINT because SQL Server TINYINT is actually "unsigned tinyint"
        SqlDataTypeTest.create()
                .addRoundTrip("tinyint", "NULL", SMALLINT, "CAST(NULL AS SMALLINT)")
                .addRoundTrip("tinyint", "0", SMALLINT, "SMALLINT '0'") // min value in SQL Server
                .addRoundTrip("tinyint", "5", SMALLINT, "SMALLINT '5'")
                .addRoundTrip("tinyint", "255", SMALLINT, "SMALLINT '255'") // max value in SQL Server
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_tinyint"))
                .execute(getQueryRunner(), sqlServerCreateAndTrinoInsert("test_tinyint"));
    }

    @Test
    public void testUnsupportedTinyint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_tinyint", "(data tinyint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-1)", table.getName()), // min - 1
                    "Arithmetic overflow error for data type tinyint, value = -1");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (256)", table.getName()), // max + 1
                    "Arithmetic overflow error for data type tinyint, value = 256.");
        }
    }

    @Test
    public void testSmallint()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("smallint", "NULL", SMALLINT, "CAST(NULL AS SMALLINT)")
                .addRoundTrip("smallint", "-32768", SMALLINT, "SMALLINT '-32768'") // min value in SQL Server and Trino
                .addRoundTrip("smallint", "32456", SMALLINT, "SMALLINT '32456'")
                .addRoundTrip("smallint", "32767", SMALLINT, "SMALLINT '32767'") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_smallint"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_smallint"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_smallint"));
    }

    @Test
    public void testUnsupportedSmallint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_smallint", "(data smallint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-32769)", table.getName()), // min - 1
                    "Arithmetic overflow error for data type smallint, value = -32769.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (32768)", table.getName()), // max + 1
                    "Arithmetic overflow error for data type smallint, value = 32768.");
        }
    }

    @Test
    public void testInteger()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("integer", "NULL", INTEGER, "CAST(NULL AS INTEGER)")
                .addRoundTrip("integer", "-2147483648", INTEGER, "-2147483648") // min value in SQL Server and Trino
                .addRoundTrip("integer", "1234567890", INTEGER, "1234567890")
                .addRoundTrip("integer", "2147483647", INTEGER, "2147483647") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_int"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_int"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_int"));
    }

    @Test
    public void testUnsupportedInteger()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_integer", "(data integer)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-2147483649)", table.getName()), // min - 1
                    "Arithmetic overflow error converting expression to data type int.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (2147483648)", table.getName()), // max + 1
                    "Arithmetic overflow error converting expression to data type int.");
        }
    }

    @Test
    public void testBigint()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("bigint", "NULL", BIGINT, "CAST(NULL AS BIGINT)")
                .addRoundTrip("bigint", "-9223372036854775808", BIGINT, "-9223372036854775808") // min value in SQL Server and Trino
                .addRoundTrip("bigint", "123456789012", BIGINT, "123456789012")
                .addRoundTrip("bigint", "9223372036854775807", BIGINT, "9223372036854775807") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_bigint"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_bigint"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_bigint"));
    }

    @Test
    public void testUnsupportedBigint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_bigint", "(data bigint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-9223372036854775809)", table.getName()), // min - 1
                    "Arithmetic overflow error converting expression to data type bigint.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (9223372036854775808)", table.getName()), // max + 1
                    "Arithmetic overflow error converting expression to data type bigint.");
        }
    }

    @Test
    public void testReal()
    {
        // we are not testing Nan/-Infinity/+Infinity as those are not supported by SQL Server
        SqlDataTypeTest.create()
                .addRoundTrip("real", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("real", "3.14", REAL, "REAL '3.14'")
                .addRoundTrip("real", "3.1415927", REAL, "REAL '3.1415927'")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_real"));

        SqlDataTypeTest.create()
                .addRoundTrip("real", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("real", "3.14", REAL, "REAL '3.14'")
                .addRoundTrip("real", "3.1415927", REAL, "REAL '3.1415927'")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_real"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_real"));
    }

    @Test
    public void testFloat()
    {
        // SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53
        SqlDataTypeTest.create()
                .addRoundTrip("float", "1E100", DOUBLE, "double '1E100'")
                .addRoundTrip("float", "1.0", DOUBLE, "double '1.0'")
                .addRoundTrip("float", "123456.123456", DOUBLE, "double '123456.123456'")
                .addRoundTrip("float", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("float(1)", "100000.0", REAL, "REAL '100000.0'")
                .addRoundTrip("float(24)", "123000.0", REAL, "REAL '123000.0'")
                .addRoundTrip("float(24)", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("float(25)", "1E100", DOUBLE, "double '1E100'")
                .addRoundTrip("float(53)", "1.0", DOUBLE, "double '1.0'")
                .addRoundTrip("float(53)", "1234567890123456789.0123456789", DOUBLE, "double '1234567890123456789.0123456789'")
                .addRoundTrip("float(53)", "NULL", DOUBLE, "CAST(NULL AS double)")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_float"));
    }

    @Test
    public void testDouble()
    {
        // we are not testing Nan/-Infinity/+Infinity as those are not supported by SQL Server
        SqlDataTypeTest.create()
                .addRoundTrip("double precision", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("double precision", "1.0E100", DOUBLE, "1.0E100")
                .addRoundTrip("double precision", "123.456E10", DOUBLE, "123.456E10")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_double"));

        SqlDataTypeTest.create()
                .addRoundTrip("double", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("double", "1.0E100", DOUBLE, "1.0E100")
                .addRoundTrip("double", "123.456E10", DOUBLE, "123.456E10")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_double"));
    }

    @Test
    public void testDecimal()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("decimal(3, 0)", "193", createDecimalType(3, 0), "CAST('193' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 0)", "19", createDecimalType(3, 0), "CAST('19' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 0)", "-193", createDecimalType(3, 0), "CAST('-193' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 1)", "10.0", createDecimalType(3, 1), "CAST('10.0' AS decimal(3, 1))")
                .addRoundTrip("decimal(3, 1)", "10.1", createDecimalType(3, 1), "CAST('10.1' AS decimal(3, 1))")
                .addRoundTrip("decimal(3, 1)", "-10.1", createDecimalType(3, 1), "CAST('-10.1' AS decimal(3, 1))")
                .addRoundTrip("decimal(4, 2)", "2", createDecimalType(4, 2), "CAST('2' AS decimal(4, 2))")
                .addRoundTrip("decimal(4, 2)", "2.3", createDecimalType(4, 2), "CAST('2.3' AS decimal(4, 2))")
                .addRoundTrip("decimal(24, 2)", "2", createDecimalType(24, 2), "CAST('2' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 2)", "2.3", createDecimalType(24, 2), "CAST('2.3' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 2)", "123456789.3", createDecimalType(24, 2), "CAST('123456789.3' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 4)", "12345678901234567890.31", createDecimalType(24, 4), "CAST('12345678901234567890.31' AS decimal(24, 4))")
                .addRoundTrip("decimal(30, 5)", "3141592653589793238462643.38327", createDecimalType(30, 5), "CAST('3141592653589793238462643.38327' AS decimal(30, 5))")
                .addRoundTrip("decimal(30, 5)", "-3141592653589793238462643.38327", createDecimalType(30, 5), "CAST('-3141592653589793238462643.38327' AS decimal(30, 5))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_decimal"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_decimal"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_decimal"));
    }

    @Test
    public void testChar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("char(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("char(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("char(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .addRoundTrip("char(4001)", "'text_c'", createCharType(4001), "CAST('text_c' AS char(4001))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_char"));

        SqlDataTypeTest.create()
                .addRoundTrip("char(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("char(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("char(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .addRoundTrip("char(5)", "CAST('攻殻機動隊' AS char(5))", createCharType(5), "CAST('攻殻機動隊' AS char(5))")
                .addRoundTrip("char(32)", "CAST('攻殻機動隊' AS char(32))", createCharType(32), "CAST('攻殻機動隊' AS char(32))")
                .addRoundTrip("char(20)", "CAST('😂' AS char(20))", createCharType(20), "CAST('😂' AS char(20))")
                .addRoundTrip("char(77)", "CAST('Ну, погоди!' AS char(77))", createCharType(77), "CAST('Ну, погоди!' AS char(77))")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_char"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_char"));
    }

    @Test
    public void testSqlServerNchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("nchar(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("nchar(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("nchar(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_sqlserver_char"));
    }

    @Test
    public void testTrinoLongChar()
    {
        // testing mapping char > 4000 -> varchar(max)
        SqlDataTypeTest.create()
                .addRoundTrip("char(4001)", "'text_c'", createUnboundedVarcharType(), "VARCHAR 'text_c'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_long_char"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_long_char"));
    }

    @Test
    public void testVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varchar(10)", "'text_a'", createVarcharType(10), "CAST('text_a' AS varchar(10))")
                .addRoundTrip("varchar(255)", "'text_b'", createVarcharType(255), "CAST('text_b' AS varchar(255))")
                .addRoundTrip("varchar(4001)", "'text_c'", createVarcharType(4001), "CAST('text_c' AS varchar(4001))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_varchar"));

        SqlDataTypeTest.create()
                .addRoundTrip("varchar(10)", "'text_a'", createVarcharType(10), "CAST('text_a' AS varchar(10))")
                .addRoundTrip("varchar(255)", "'text_b'", createVarcharType(255), "CAST('text_b' AS varchar(255))")
                .addRoundTrip("varchar(5)", "CAST('攻殻機動隊' AS varchar(5))", createVarcharType(5), "CAST('攻殻機動隊' AS varchar(5))")
                .addRoundTrip("varchar(32)", "CAST('攻殻機動隊' AS varchar(32))", createVarcharType(32), "CAST('攻殻機動隊' AS varchar(32))")
                .addRoundTrip("varchar(20)", "CAST('😂' AS varchar(20))", createVarcharType(20), "CAST('😂' AS varchar(20))")
                .addRoundTrip("varchar(77)", "CAST('Ну, погоди!' AS varchar(77))", createVarcharType(77), "CAST('Ну, погоди!' AS varchar(77))")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_varchar"));
    }

    @Test
    public void testSqlServerNvarchar()
    {
        // Unicode literals must be prefixed with N'string'
        // https://docs.microsoft.com/en-us/sql/analytics-platform-system/load-with-insert?view=aps-pdw-2016-au7#char-varchar-nchar-and-nvarchar-data-types
        SqlDataTypeTest.create()
                .addRoundTrip("nvarchar(5)", "N'攻殻機動隊'", createVarcharType(5), "CAST('攻殻機動隊' AS varchar(5))")
                .addRoundTrip("nvarchar(32)", "N'攻殻機動隊'", createVarcharType(32), "CAST('攻殻機動隊' AS varchar(32))")
                .addRoundTrip("nvarchar(20)", "N'😂'", createVarcharType(20), "CAST('😂' AS varchar(20))")
                .addRoundTrip("nvarchar(77)", "N'Ну, погоди!'", createVarcharType(77), "CAST('Ну, погоди!' AS varchar(77))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_sqlserver_nvarchar"));
    }

    @Test
    public void testTrinoLongVarchar()
    {
        // testing mapping varchar > 4000 -> varchar(max)
        SqlDataTypeTest.create()
                .addRoundTrip("varchar(4001)", "'text_c'", createUnboundedVarcharType(), "VARCHAR 'text_c'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_long_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_long_varchar"));
    }

    @Test
    public void testSqlServerLongVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("text", "'text_a'", createUnboundedVarcharType(), "VARCHAR 'text_a'")
                .addRoundTrip("ntext", "'text_a'", createVarcharType(1073741823), "CAST('text_a' as VARCHAR(1073741823))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_long_n_varchar"));
    }

    @Test
    public void testTrinoUnboundedVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varchar", "'text_a'", createUnboundedVarcharType(), "VARCHAR 'text_a'")
                .addRoundTrip("varchar", "'text_b'", createUnboundedVarcharType(), "VARCHAR 'text_b'")
                .addRoundTrip("varchar", "'text_d'", createUnboundedVarcharType(), "VARCHAR 'text_d'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '😂'", createUnboundedVarcharType(), "VARCHAR '😂'")
                .addRoundTrip("varchar", "VARCHAR 'Ну, погоди!'", createUnboundedVarcharType(), "VARCHAR 'Ну, погоди!'")
                .addRoundTrip("varchar", "'text_f'", createUnboundedVarcharType(), "VARCHAR 'text_f'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_unbounded_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_unbounded_varchar"));
    }

    @Test
    public void testVarbinary()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varbinary", "NULL", VARBINARY, "CAST(NULL AS varbinary)")
                .addRoundTrip("varbinary", "X''", VARBINARY, "X''")
                .addRoundTrip("varbinary", "X'68656C6C6F'", VARBINARY, "to_utf8('hello')")
                .addRoundTrip("varbinary", "X'5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD'", VARBINARY, "to_utf8('Piękna łąka w 東京都')")
                .addRoundTrip("varbinary", "X'4261672066756C6C206F6620F09F92B0'", VARBINARY, "to_utf8('Bag full of 💰')")
                .addRoundTrip("varbinary", "X'0001020304050607080DF9367AA7000000'", VARBINARY, "X'0001020304050607080DF9367AA7000000'") // non-text
                .addRoundTrip("varbinary", "X'000000000000'", VARBINARY, "X'000000000000'")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_varbinary"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_varbinary"));

        // Binary literals must be prefixed with 0x
        // https://docs.microsoft.com/en-us/sql/analytics-platform-system/load-with-insert?view=aps-pdw-2016-au7#InsertingLiteralsBinary
        SqlDataTypeTest.create()
                .addRoundTrip("varbinary(10)", "NULL", VARBINARY, "CAST(NULL AS varbinary)")
                .addRoundTrip("varbinary(20)", "0x", VARBINARY, "CAST('' AS varbinary)")
                .addRoundTrip("varbinary(30)", "0x68656C6C6F", VARBINARY, "X'68656C6C6F'")
                .addRoundTrip("varbinary(1000)", "0x5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD", VARBINARY, "X'5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD'")
                .addRoundTrip("varbinary(2000)", "0x4261672066756C6C206F6620F09F92B0", VARBINARY, "X'4261672066756C6C206F6620F09F92B0'")
                .addRoundTrip("varbinary(4000)", "0x0001020304050607080DF9367AA7000000", VARBINARY, "X'0001020304050607080DF9367AA7000000'") // non-text
                .addRoundTrip("varbinary(4000)", "0x000000000000", VARBINARY, "X'000000000000'")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_varbinary"));
    }

    @Test
    public void testDate()
    {
        ZoneId jvmZone = ZoneId.systemDefault();
        checkState(jvmZone.getId().equals("America/Bahia_Banderas"), "This test assumes certain JVM time zone");
        LocalDate dateOfLocalTimeChangeForwardAtMidnightInJvmZone = LocalDate.of(1970, 1, 1);
        checkIsGap(jvmZone, dateOfLocalTimeChangeForwardAtMidnightInJvmZone.atStartOfDay());

        ZoneId someZone = ZoneId.of("Europe/Vilnius");
        LocalDate dateOfLocalTimeChangeForwardAtMidnightInSomeZone = LocalDate.of(1983, 4, 1);
        checkIsGap(someZone, dateOfLocalTimeChangeForwardAtMidnightInSomeZone.atStartOfDay());
        LocalDate dateOfLocalTimeChangeBackwardAtMidnightInSomeZone = LocalDate.of(1983, 10, 1);
        checkIsDoubled(someZone, dateOfLocalTimeChangeBackwardAtMidnightInSomeZone.atStartOfDay().minusMinutes(1));

        SqlDataTypeTest testsSqlServer = dateTest(Function.identity());
        SqlDataTypeTest testsTrino = dateTest(inputLiteral -> format("DATE %s", inputLiteral));

        for (String timeZoneId : ImmutableList.of(UTC_KEY.getId(), jvmZone.getId(), someZone.getId())) {
            Session session = Session.builder(getSession())
                    .setTimeZoneKey(TimeZoneKey.getTimeZoneKey(timeZoneId))
                    .build();
            testsSqlServer.execute(getQueryRunner(), session, sqlServerCreateAndInsert("test_date"));
            testsTrino.execute(getQueryRunner(), session, trinoCreateAsSelect(session, "test_date"));
            testsTrino.execute(getQueryRunner(), session, trinoCreateAndInsert(session, "test_date"));
        }
    }

    private SqlDataTypeTest dateTest(Function<String, String> inputLiteralFactory)
    {
        // BC dates not supported by SQL Server
        return SqlDataTypeTest.create()
                .addRoundTrip("date", "NULL", DATE, "CAST(NULL AS DATE)")
                // first day of AD
                .addRoundTrip("date", inputLiteralFactory.apply("'0001-01-01'"), DATE, "DATE '0001-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'0012-12-12'"), DATE, "DATE '0012-12-12'")
                // before julian->gregorian switch
                .addRoundTrip("date", inputLiteralFactory.apply("'1500-01-01'"), DATE, "DATE '1500-01-01'")
                // before epoch
                .addRoundTrip("date", inputLiteralFactory.apply("'1952-04-03'"), DATE, "DATE '1952-04-03'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1970-01-01'"), DATE, "DATE '1970-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1970-02-03'"), DATE, "DATE '1970-02-03'")
                // summer on northern hemisphere (possible DST)
                .addRoundTrip("date", inputLiteralFactory.apply("'2017-07-01'"), DATE, "DATE '2017-07-01'")
                // winter on northern hemisphere (possible DST on southern hemisphere)
                .addRoundTrip("date", inputLiteralFactory.apply("'2017-01-01'"), DATE, "DATE '2017-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1970-01-01'"), DATE, "DATE '1970-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1983-04-01'"), DATE, "DATE '1983-04-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1983-10-01'"), DATE, "DATE '1983-10-01'");
    }

    @Test
    public void testDateJulianGregorianCalendarSwitch()
    {
        try (TestTable table = new TestTable(getQueryRunner()::execute, "test_old_date", "(dt DATE)", ImmutableList.of("DATE '1582-10-05'", "DATE '1582-10-14'"))) {
            // SQL Server returns +10 days when the date is in the range of 1582-10-05 and 1582-10-14, but we need to pass the original value in predicates
            assertQuery("SELECT * FROM " + table.getName(), "VALUES DATE '1582-10-15', DATE '1582-10-24'");
            assertQuery("SELECT * FROM " + table.getName() + " WHERE dt = DATE '1582-10-05'", "VALUES DATE '1582-10-15'");
            assertQueryReturnsEmptyResult("SELECT * FROM " + table.getName() + " WHERE dt = DATE '1582-10-15'");
        }<commit_msg>Use session data provider in SQL Server date mapping test<commit_after>/*
 * 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.plugin.sqlserver;

import com.google.common.collect.ImmutableList;
import io.trino.Session;
import io.trino.spi.type.TimeZoneKey;
import io.trino.testing.AbstractTestQueryFramework;
import io.trino.testing.TestingSession;
import io.trino.testing.datatype.CreateAndInsertDataSetup;
import io.trino.testing.datatype.CreateAndTrinoInsertDataSetup;
import io.trino.testing.datatype.CreateAsSelectDataSetup;
import io.trino.testing.datatype.DataSetup;
import io.trino.testing.datatype.SqlDataTypeTest;
import io.trino.testing.sql.SqlExecutor;
import io.trino.testing.sql.TestTable;
import io.trino.testing.sql.TrinoSqlExecutor;
import org.intellij.lang.annotations.Language;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.function.Function;

import static com.google.common.base.Preconditions.checkState;
import static com.google.common.base.Verify.verify;
import static io.trino.spi.type.BigintType.BIGINT;
import static io.trino.spi.type.BooleanType.BOOLEAN;
import static io.trino.spi.type.CharType.createCharType;
import static io.trino.spi.type.DateType.DATE;
import static io.trino.spi.type.DecimalType.createDecimalType;
import static io.trino.spi.type.DoubleType.DOUBLE;
import static io.trino.spi.type.IntegerType.INTEGER;
import static io.trino.spi.type.RealType.REAL;
import static io.trino.spi.type.SmallintType.SMALLINT;
import static io.trino.spi.type.TimeType.createTimeType;
import static io.trino.spi.type.TimestampType.createTimestampType;
import static io.trino.spi.type.TimestampWithTimeZoneType.createTimestampWithTimeZoneType;
import static io.trino.spi.type.VarbinaryType.VARBINARY;
import static io.trino.spi.type.VarcharType.createUnboundedVarcharType;
import static io.trino.spi.type.VarcharType.createVarcharType;
import static io.trino.testing.sql.TestTable.randomTableSuffix;
import static java.lang.String.format;
import static java.time.ZoneOffset.UTC;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

public abstract class BaseSqlServerTypeMapping
        extends AbstractTestQueryFramework
{
    private final ZoneId jvmZone = ZoneId.systemDefault();
    private final LocalDateTime timeGapInJvmZone1 = LocalDateTime.of(1970, 1, 1, 0, 13, 42);
    private final LocalDateTime timeGapInJvmZone2 = LocalDateTime.of(2018, 4, 1, 2, 13, 55, 123_000_000);
    private final LocalDateTime timeDoubledInJvmZone = LocalDateTime.of(2018, 10, 28, 1, 33, 17, 456_000_000);

    // no DST in 1970, but has DST in later years (e.g. 2018)
    private final ZoneId vilnius = ZoneId.of("Europe/Vilnius");
    private final LocalDateTime timeGapInVilnius = LocalDateTime.of(2018, 3, 25, 3, 17, 17);
    private final LocalDateTime timeDoubledInVilnius = LocalDateTime.of(2018, 10, 28, 3, 33, 33, 333_000_000);

    // minutes offset change since 1970-01-01, no DST
    private final ZoneId kathmandu = ZoneId.of("Asia/Kathmandu");
    private final LocalDateTime timeGapInKathmandu = LocalDateTime.of(1986, 1, 1, 0, 13, 7);

    protected TestingSqlServer sqlServer;

    @BeforeClass
    public void setUp()
    {
        checkState(jvmZone.getId().equals("America/Bahia_Banderas"), "This test assumes certain JVM time zone");
        LocalDate dateOfLocalTimeChangeForwardAtMidnightInJvmZone = LocalDate.of(1970, 1, 1);
        checkIsGap(jvmZone, dateOfLocalTimeChangeForwardAtMidnightInJvmZone.atStartOfDay());
        checkIsGap(jvmZone, timeGapInJvmZone1);
        checkIsGap(jvmZone, timeGapInJvmZone2);
        checkIsDoubled(jvmZone, timeDoubledInJvmZone);

        LocalDate dateOfLocalTimeChangeForwardAtMidnightInSomeZone = LocalDate.of(1983, 4, 1);
        checkIsGap(vilnius, dateOfLocalTimeChangeForwardAtMidnightInSomeZone.atStartOfDay());
        LocalDate dateOfLocalTimeChangeBackwardAtMidnightInSomeZone = LocalDate.of(1983, 10, 1);
        checkIsDoubled(vilnius, dateOfLocalTimeChangeBackwardAtMidnightInSomeZone.atStartOfDay().minusMinutes(1));
        checkIsGap(vilnius, timeGapInVilnius);
        checkIsDoubled(vilnius, timeDoubledInVilnius);

        checkIsGap(kathmandu, timeGapInKathmandu);
    }

    @Test
    public void testTrinoBoolean()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("boolean", "null", BOOLEAN, "CAST(NULL AS BOOLEAN)")
                .addRoundTrip("boolean", "true", BOOLEAN)
                .addRoundTrip("boolean", "false", BOOLEAN)
                .execute(getQueryRunner(), trinoCreateAsSelect("test_boolean"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_boolean"));
    }

    @Test
    public void testSqlServerBit()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("bit", "null", BOOLEAN, "CAST(NULL AS BOOLEAN)")
                .addRoundTrip("bit", "1", BOOLEAN, "true")
                .addRoundTrip("bit", "0", BOOLEAN, "false")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_bit"));
    }

    @Test
    public void testTinyint()
    {
        // Map SQL Server TINYINT to Trino SMALLINT because SQL Server TINYINT is actually "unsigned tinyint"
        SqlDataTypeTest.create()
                .addRoundTrip("tinyint", "NULL", SMALLINT, "CAST(NULL AS SMALLINT)")
                .addRoundTrip("tinyint", "0", SMALLINT, "SMALLINT '0'") // min value in SQL Server
                .addRoundTrip("tinyint", "5", SMALLINT, "SMALLINT '5'")
                .addRoundTrip("tinyint", "255", SMALLINT, "SMALLINT '255'") // max value in SQL Server
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_tinyint"))
                .execute(getQueryRunner(), sqlServerCreateAndTrinoInsert("test_tinyint"));
    }

    @Test
    public void testUnsupportedTinyint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_tinyint", "(data tinyint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-1)", table.getName()), // min - 1
                    "Arithmetic overflow error for data type tinyint, value = -1");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (256)", table.getName()), // max + 1
                    "Arithmetic overflow error for data type tinyint, value = 256.");
        }
    }

    @Test
    public void testSmallint()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("smallint", "NULL", SMALLINT, "CAST(NULL AS SMALLINT)")
                .addRoundTrip("smallint", "-32768", SMALLINT, "SMALLINT '-32768'") // min value in SQL Server and Trino
                .addRoundTrip("smallint", "32456", SMALLINT, "SMALLINT '32456'")
                .addRoundTrip("smallint", "32767", SMALLINT, "SMALLINT '32767'") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_smallint"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_smallint"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_smallint"));
    }

    @Test
    public void testUnsupportedSmallint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_smallint", "(data smallint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-32769)", table.getName()), // min - 1
                    "Arithmetic overflow error for data type smallint, value = -32769.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (32768)", table.getName()), // max + 1
                    "Arithmetic overflow error for data type smallint, value = 32768.");
        }
    }

    @Test
    public void testInteger()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("integer", "NULL", INTEGER, "CAST(NULL AS INTEGER)")
                .addRoundTrip("integer", "-2147483648", INTEGER, "-2147483648") // min value in SQL Server and Trino
                .addRoundTrip("integer", "1234567890", INTEGER, "1234567890")
                .addRoundTrip("integer", "2147483647", INTEGER, "2147483647") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_int"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_int"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_int"));
    }

    @Test
    public void testUnsupportedInteger()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_integer", "(data integer)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-2147483649)", table.getName()), // min - 1
                    "Arithmetic overflow error converting expression to data type int.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (2147483648)", table.getName()), // max + 1
                    "Arithmetic overflow error converting expression to data type int.");
        }
    }

    @Test
    public void testBigint()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("bigint", "NULL", BIGINT, "CAST(NULL AS BIGINT)")
                .addRoundTrip("bigint", "-9223372036854775808", BIGINT, "-9223372036854775808") // min value in SQL Server and Trino
                .addRoundTrip("bigint", "123456789012", BIGINT, "123456789012")
                .addRoundTrip("bigint", "9223372036854775807", BIGINT, "9223372036854775807") // max value in SQL Server and Trino
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_bigint"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_bigint"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_bigint"));
    }

    @Test
    public void testUnsupportedBigint()
    {
        try (TestTable table = new TestTable(onRemoteDatabase(), "test_unsupported_bigint", "(data bigint)")) {
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (-9223372036854775809)", table.getName()), // min - 1
                    "Arithmetic overflow error converting expression to data type bigint.");
            assertSqlServerQueryFails(
                    format("INSERT INTO %s VALUES (9223372036854775808)", table.getName()), // max + 1
                    "Arithmetic overflow error converting expression to data type bigint.");
        }
    }

    @Test
    public void testReal()
    {
        // we are not testing Nan/-Infinity/+Infinity as those are not supported by SQL Server
        SqlDataTypeTest.create()
                .addRoundTrip("real", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("real", "3.14", REAL, "REAL '3.14'")
                .addRoundTrip("real", "3.1415927", REAL, "REAL '3.1415927'")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_real"));

        SqlDataTypeTest.create()
                .addRoundTrip("real", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("real", "3.14", REAL, "REAL '3.14'")
                .addRoundTrip("real", "3.1415927", REAL, "REAL '3.1415927'")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_real"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_real"));
    }

    @Test
    public void testFloat()
    {
        // SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53
        SqlDataTypeTest.create()
                .addRoundTrip("float", "1E100", DOUBLE, "double '1E100'")
                .addRoundTrip("float", "1.0", DOUBLE, "double '1.0'")
                .addRoundTrip("float", "123456.123456", DOUBLE, "double '123456.123456'")
                .addRoundTrip("float", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("float(1)", "100000.0", REAL, "REAL '100000.0'")
                .addRoundTrip("float(24)", "123000.0", REAL, "REAL '123000.0'")
                .addRoundTrip("float(24)", "NULL", REAL, "CAST(NULL AS real)")
                .addRoundTrip("float(25)", "1E100", DOUBLE, "double '1E100'")
                .addRoundTrip("float(53)", "1.0", DOUBLE, "double '1.0'")
                .addRoundTrip("float(53)", "1234567890123456789.0123456789", DOUBLE, "double '1234567890123456789.0123456789'")
                .addRoundTrip("float(53)", "NULL", DOUBLE, "CAST(NULL AS double)")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_float"));
    }

    @Test
    public void testDouble()
    {
        // we are not testing Nan/-Infinity/+Infinity as those are not supported by SQL Server
        SqlDataTypeTest.create()
                .addRoundTrip("double precision", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("double precision", "1.0E100", DOUBLE, "1.0E100")
                .addRoundTrip("double precision", "123.456E10", DOUBLE, "123.456E10")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_double"));

        SqlDataTypeTest.create()
                .addRoundTrip("double", "NULL", DOUBLE, "CAST(NULL AS double)")
                .addRoundTrip("double", "1.0E100", DOUBLE, "1.0E100")
                .addRoundTrip("double", "123.456E10", DOUBLE, "123.456E10")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_double"));
    }

    @Test
    public void testDecimal()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("decimal(3, 0)", "193", createDecimalType(3, 0), "CAST('193' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 0)", "19", createDecimalType(3, 0), "CAST('19' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 0)", "-193", createDecimalType(3, 0), "CAST('-193' AS decimal(3, 0))")
                .addRoundTrip("decimal(3, 1)", "10.0", createDecimalType(3, 1), "CAST('10.0' AS decimal(3, 1))")
                .addRoundTrip("decimal(3, 1)", "10.1", createDecimalType(3, 1), "CAST('10.1' AS decimal(3, 1))")
                .addRoundTrip("decimal(3, 1)", "-10.1", createDecimalType(3, 1), "CAST('-10.1' AS decimal(3, 1))")
                .addRoundTrip("decimal(4, 2)", "2", createDecimalType(4, 2), "CAST('2' AS decimal(4, 2))")
                .addRoundTrip("decimal(4, 2)", "2.3", createDecimalType(4, 2), "CAST('2.3' AS decimal(4, 2))")
                .addRoundTrip("decimal(24, 2)", "2", createDecimalType(24, 2), "CAST('2' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 2)", "2.3", createDecimalType(24, 2), "CAST('2.3' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 2)", "123456789.3", createDecimalType(24, 2), "CAST('123456789.3' AS decimal(24, 2))")
                .addRoundTrip("decimal(24, 4)", "12345678901234567890.31", createDecimalType(24, 4), "CAST('12345678901234567890.31' AS decimal(24, 4))")
                .addRoundTrip("decimal(30, 5)", "3141592653589793238462643.38327", createDecimalType(30, 5), "CAST('3141592653589793238462643.38327' AS decimal(30, 5))")
                .addRoundTrip("decimal(30, 5)", "-3141592653589793238462643.38327", createDecimalType(30, 5), "CAST('-3141592653589793238462643.38327' AS decimal(30, 5))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_decimal"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_decimal"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_decimal"));
    }

    @Test
    public void testChar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("char(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("char(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("char(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .addRoundTrip("char(4001)", "'text_c'", createCharType(4001), "CAST('text_c' AS char(4001))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_char"));

        SqlDataTypeTest.create()
                .addRoundTrip("char(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("char(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("char(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .addRoundTrip("char(5)", "CAST('攻殻機動隊' AS char(5))", createCharType(5), "CAST('攻殻機動隊' AS char(5))")
                .addRoundTrip("char(32)", "CAST('攻殻機動隊' AS char(32))", createCharType(32), "CAST('攻殻機動隊' AS char(32))")
                .addRoundTrip("char(20)", "CAST('😂' AS char(20))", createCharType(20), "CAST('😂' AS char(20))")
                .addRoundTrip("char(77)", "CAST('Ну, погоди!' AS char(77))", createCharType(77), "CAST('Ну, погоди!' AS char(77))")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_char"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_char"));
    }

    @Test
    public void testSqlServerNchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("nchar(1)", "NULL", createCharType(1), "CAST(NULL AS char(1))")
                .addRoundTrip("nchar(10)", "'text_a'", createCharType(10), "CAST('text_a' AS char(10))")
                .addRoundTrip("nchar(255)", "'text_b'", createCharType(255), "CAST('text_b' AS char(255))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_sqlserver_char"));
    }

    @Test
    public void testTrinoLongChar()
    {
        // testing mapping char > 4000 -> varchar(max)
        SqlDataTypeTest.create()
                .addRoundTrip("char(4001)", "'text_c'", createUnboundedVarcharType(), "VARCHAR 'text_c'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_long_char"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_long_char"));
    }

    @Test
    public void testVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varchar(10)", "'text_a'", createVarcharType(10), "CAST('text_a' AS varchar(10))")
                .addRoundTrip("varchar(255)", "'text_b'", createVarcharType(255), "CAST('text_b' AS varchar(255))")
                .addRoundTrip("varchar(4001)", "'text_c'", createVarcharType(4001), "CAST('text_c' AS varchar(4001))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_varchar"));

        SqlDataTypeTest.create()
                .addRoundTrip("varchar(10)", "'text_a'", createVarcharType(10), "CAST('text_a' AS varchar(10))")
                .addRoundTrip("varchar(255)", "'text_b'", createVarcharType(255), "CAST('text_b' AS varchar(255))")
                .addRoundTrip("varchar(5)", "CAST('攻殻機動隊' AS varchar(5))", createVarcharType(5), "CAST('攻殻機動隊' AS varchar(5))")
                .addRoundTrip("varchar(32)", "CAST('攻殻機動隊' AS varchar(32))", createVarcharType(32), "CAST('攻殻機動隊' AS varchar(32))")
                .addRoundTrip("varchar(20)", "CAST('😂' AS varchar(20))", createVarcharType(20), "CAST('😂' AS varchar(20))")
                .addRoundTrip("varchar(77)", "CAST('Ну, погоди!' AS varchar(77))", createVarcharType(77), "CAST('Ну, погоди!' AS varchar(77))")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_varchar"));
    }

    @Test
    public void testSqlServerNvarchar()
    {
        // Unicode literals must be prefixed with N'string'
        // https://docs.microsoft.com/en-us/sql/analytics-platform-system/load-with-insert?view=aps-pdw-2016-au7#char-varchar-nchar-and-nvarchar-data-types
        SqlDataTypeTest.create()
                .addRoundTrip("nvarchar(5)", "N'攻殻機動隊'", createVarcharType(5), "CAST('攻殻機動隊' AS varchar(5))")
                .addRoundTrip("nvarchar(32)", "N'攻殻機動隊'", createVarcharType(32), "CAST('攻殻機動隊' AS varchar(32))")
                .addRoundTrip("nvarchar(20)", "N'😂'", createVarcharType(20), "CAST('😂' AS varchar(20))")
                .addRoundTrip("nvarchar(77)", "N'Ну, погоди!'", createVarcharType(77), "CAST('Ну, погоди!' AS varchar(77))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_sqlserver_nvarchar"));
    }

    @Test
    public void testTrinoLongVarchar()
    {
        // testing mapping varchar > 4000 -> varchar(max)
        SqlDataTypeTest.create()
                .addRoundTrip("varchar(4001)", "'text_c'", createUnboundedVarcharType(), "VARCHAR 'text_c'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_long_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_long_varchar"));
    }

    @Test
    public void testSqlServerLongVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("text", "'text_a'", createUnboundedVarcharType(), "VARCHAR 'text_a'")
                .addRoundTrip("ntext", "'text_a'", createVarcharType(1073741823), "CAST('text_a' as VARCHAR(1073741823))")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_long_n_varchar"));
    }

    @Test
    public void testTrinoUnboundedVarchar()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varchar", "'text_a'", createUnboundedVarcharType(), "VARCHAR 'text_a'")
                .addRoundTrip("varchar", "'text_b'", createUnboundedVarcharType(), "VARCHAR 'text_b'")
                .addRoundTrip("varchar", "'text_d'", createUnboundedVarcharType(), "VARCHAR 'text_d'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '攻殻機動隊'", createUnboundedVarcharType(), "VARCHAR '攻殻機動隊'")
                .addRoundTrip("varchar", "VARCHAR '😂'", createUnboundedVarcharType(), "VARCHAR '😂'")
                .addRoundTrip("varchar", "VARCHAR 'Ну, погоди!'", createUnboundedVarcharType(), "VARCHAR 'Ну, погоди!'")
                .addRoundTrip("varchar", "'text_f'", createUnboundedVarcharType(), "VARCHAR 'text_f'")
                .execute(getQueryRunner(), trinoCreateAndInsert("test_unbounded_varchar"))
                .execute(getQueryRunner(), trinoCreateAsSelect("test_unbounded_varchar"));
    }

    @Test
    public void testVarbinary()
    {
        SqlDataTypeTest.create()
                .addRoundTrip("varbinary", "NULL", VARBINARY, "CAST(NULL AS varbinary)")
                .addRoundTrip("varbinary", "X''", VARBINARY, "X''")
                .addRoundTrip("varbinary", "X'68656C6C6F'", VARBINARY, "to_utf8('hello')")
                .addRoundTrip("varbinary", "X'5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD'", VARBINARY, "to_utf8('Piękna łąka w 東京都')")
                .addRoundTrip("varbinary", "X'4261672066756C6C206F6620F09F92B0'", VARBINARY, "to_utf8('Bag full of 💰')")
                .addRoundTrip("varbinary", "X'0001020304050607080DF9367AA7000000'", VARBINARY, "X'0001020304050607080DF9367AA7000000'") // non-text
                .addRoundTrip("varbinary", "X'000000000000'", VARBINARY, "X'000000000000'")
                .execute(getQueryRunner(), trinoCreateAsSelect("test_varbinary"))
                .execute(getQueryRunner(), trinoCreateAndInsert("test_varbinary"));

        // Binary literals must be prefixed with 0x
        // https://docs.microsoft.com/en-us/sql/analytics-platform-system/load-with-insert?view=aps-pdw-2016-au7#InsertingLiteralsBinary
        SqlDataTypeTest.create()
                .addRoundTrip("varbinary(10)", "NULL", VARBINARY, "CAST(NULL AS varbinary)")
                .addRoundTrip("varbinary(20)", "0x", VARBINARY, "CAST('' AS varbinary)")
                .addRoundTrip("varbinary(30)", "0x68656C6C6F", VARBINARY, "X'68656C6C6F'")
                .addRoundTrip("varbinary(1000)", "0x5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD", VARBINARY, "X'5069C4996B6E6120C582C4856B61207720E69DB1E4BAACE983BD'")
                .addRoundTrip("varbinary(2000)", "0x4261672066756C6C206F6620F09F92B0", VARBINARY, "X'4261672066756C6C206F6620F09F92B0'")
                .addRoundTrip("varbinary(4000)", "0x0001020304050607080DF9367AA7000000", VARBINARY, "X'0001020304050607080DF9367AA7000000'") // non-text
                .addRoundTrip("varbinary(4000)", "0x000000000000", VARBINARY, "X'000000000000'")
                .execute(getQueryRunner(), sqlServerCreateAndInsert("test_varbinary"));
    }

    @Test(dataProvider = "sessionZonesDataProvider")
    public void testDate(ZoneId sessionZone)
    {
        Session session = Session.builder(getSession())
                .setTimeZoneKey(TimeZoneKey.getTimeZoneKey(sessionZone.getId()))
                .build();

        dateTest(Function.identity())
                .execute(getQueryRunner(), session, sqlServerCreateAndInsert("test_date"));

        dateTest(inputLiteral -> format("DATE %s", inputLiteral))
                .execute(getQueryRunner(), session, trinoCreateAsSelect(session, "test_date"))
                .execute(getQueryRunner(), session, trinoCreateAsSelect("test_date"))
                .execute(getQueryRunner(), session, trinoCreateAndInsert(session, "test_date"))
                .execute(getQueryRunner(), session, trinoCreateAndInsert("test_date"));
    }

    private SqlDataTypeTest dateTest(Function<String, String> inputLiteralFactory)
    {
        // BC dates not supported by SQL Server
        return SqlDataTypeTest.create()
                .addRoundTrip("date", "NULL", DATE, "CAST(NULL AS DATE)")
                // first day of AD
                .addRoundTrip("date", inputLiteralFactory.apply("'0001-01-01'"), DATE, "DATE '0001-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'0012-12-12'"), DATE, "DATE '0012-12-12'")
                // before julian->gregorian switch
                .addRoundTrip("date", inputLiteralFactory.apply("'1500-01-01'"), DATE, "DATE '1500-01-01'")
                // before epoch
                .addRoundTrip("date", inputLiteralFactory.apply("'1952-04-03'"), DATE, "DATE '1952-04-03'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1970-01-01'"), DATE, "DATE '1970-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1970-02-03'"), DATE, "DATE '1970-02-03'")
                // summer on northern hemisphere (possible DST)
                .addRoundTrip("date", inputLiteralFactory.apply("'2017-07-01'"), DATE, "DATE '2017-07-01'")
                // winter on northern hemisphere (possible DST on southern hemisphere)
                .addRoundTrip("date", inputLiteralFactory.apply("'2017-01-01'"), DATE, "DATE '2017-01-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1983-04-01'"), DATE, "DATE '1983-04-01'")
                .addRoundTrip("date", inputLiteralFactory.apply("'1983-10-01'"), DATE, "DATE '1983-10-01'");
    }

    @Test
    public void testDateJulianGregorianCalendarSwitch()
    {
        try (TestTable table = new TestTable(getQueryRunner()::execute, "test_old_date", "(dt DATE)", ImmutableList.of("DATE '1582-10-05'", "DATE '1582-10-14'"))) {
            // SQL Server returns +10 days when the date is in the range of 1582-10-05 and 1582-10-14, but we need to pass the original value in predicates
            assertQuery("SELECT * FROM " + table.getName(), "VALUES DATE '1582-10-15', DATE '1582-10-24'");
            assertQuery("SELECT * FROM " + table.getName() + " WHERE dt = DATE '1582-10-05'", "VALUES DATE '1582-10-15'");
            assertQueryReturnsEmptyResult("SELECT * FROM " + table.getName() + " WHERE dt = DATE '1582-10-15'");
        }
{'fraction_non_alphanumeric': 0.1446011034241177, 'fraction_numerical': 0.06655574043261231, 'mean_word_length': 3.1093997408953507, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 17, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 8, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1699624', 'n_tokens_mistral': 20755, 'n_tokens_neox': 18091, 'n_words': 3477}