/*
 * Copyright (c) 2012-2020 Snowflake Computing Inc. All right reserved.
 */
package net.snowflake.client.jdbc;

import net.snowflake.client.ConditionalIgnoreRule;
import net.snowflake.client.RunningOnGithubAction;
import net.snowflake.client.category.TestCategoryResultSet;
import net.snowflake.client.jdbc.telemetry.Telemetry;
import net.snowflake.client.jdbc.telemetry.TelemetryClient;
import net.snowflake.client.jdbc.telemetry.TelemetryData;
import net.snowflake.client.jdbc.telemetry.TelemetryField;
import net.snowflake.client.jdbc.telemetry.TelemetryUtil;
import net.snowflake.common.core.SFBinary;
import org.apache.arrow.vector.Float8Vector;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.experimental.categories.Category;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Pattern;

import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.CoreMatchers.nullValue;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

/**
 * Test ResultSet
 */
@Category(TestCategoryResultSet.class)
public class ResultSetIT extends BaseJDBCTest
{
  protected static String queryResultFormat = "json";

  private final String selectAllSQL = "select * from test_rs";

  private static ResultSet numberCrossTesting() throws SQLException
  {
    Connection con = getConnection();
    Statement statement = con.createStatement();

    statement.execute("create or replace table test_types(c1 number, c2 integer, c3 float, c4 boolean," +
                      "c5 char, c6 varchar, c7 date, c8 datetime, c9 time, c10 timestamp_ltz, " +
                      "c11 timestamp_tz, c12 binary)");
    statement.execute("insert into test_types values (null, null, null, null, null, null, null, null, null, null, " +
                      "null, null)");
    statement.execute(
        "insert into test_types values(2, 5, 3.5, true," +
        "'1','1', '1994-12-27', " +
        "'1994-12-27 05:05:05', '05:05:05', '1994-12-27 05:05:05', '1994-12-27 05:05:05', '48454C4C4F')");
    statement.execute(
        "insert into test_types (c5, c6) values('h', 'hello')");
    ResultSet resultSet = statement.executeQuery("select * from test_types");
    return resultSet;

  }

  public static Connection getConnection(int injectSocketTimeout)
  throws SQLException
  {
    Connection connection = BaseJDBCTest.getConnection(injectSocketTimeout);

    Statement statement = connection.createStatement();
    statement.execute(
        "alter session set " +
        "TIMEZONE='America/Los_Angeles'," +
        "TIMESTAMP_TYPE_MAPPING='TIMESTAMP_LTZ'," +
        "TIMESTAMP_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
        "TIMESTAMP_TZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
        "TIMESTAMP_LTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'," +
        "TIMESTAMP_NTZ_OUTPUT_FORMAT='DY, DD MON YYYY HH24:MI:SS TZHTZM'");
    statement.close();
    return connection;
  }

  public static Connection getConnection()
  throws SQLException
  {
    Connection conn = getConnection(BaseJDBCTest.DONT_INJECT_SOCKET_TIMEOUT);
    Statement stmt = conn.createStatement();
    stmt.execute("alter session set jdbc_query_result_format = '" + queryResultFormat + "'");
    stmt.close();
    return conn;
  }

  public static Connection getConnection(Properties paramProperties)
  throws SQLException
  {
    Connection conn = getConnection(
        DONT_INJECT_SOCKET_TIMEOUT, paramProperties, false, false);
    Statement stmt = conn.createStatement();
    stmt.execute("alter session set jdbc_query_result_format = '" + queryResultFormat + "'");
    stmt.close();
    return conn;
  }

  @Before
  public void setUp() throws SQLException
  {
    Connection con = getConnection();

    // TEST_RS
    con.createStatement().execute("create or replace table test_rs (colA string)");
    con.createStatement().execute("insert into test_rs values('rowOne')");
    con.createStatement().execute("insert into test_rs values('rowTwo')");
    con.createStatement().execute("insert into test_rs values('rowThree')");

    // ORDERS_JDBC
    Statement statement = con.createStatement();
    statement.execute("create or replace table orders_jdbc" +
                      "(C1 STRING NOT NULL COMMENT 'JDBC', "
                      + "C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, "
                      + "C7 STRING, C8 STRING, C9 STRING) "
                      + "stage_file_format = (field_delimiter='|' "
                      + "error_on_column_count_mismatch=false)");
    // put files
    assertTrue("Failed to put a file",
               statement.execute(
                   "PUT file://" +
                   getFullPathFileInResource(TEST_DATA_FILE) + " @%orders_jdbc"));
    assertTrue("Failed to put a file",
               statement.execute(
                   "PUT file://" +
                   getFullPathFileInResource(TEST_DATA_FILE_2) + " @%orders_jdbc"));

    int numRows =
        statement.executeUpdate("copy into orders_jdbc");

    assertEquals("Unexpected number of rows copied: " + numRows, 73, numRows);


    con.close();
  }

  @After
  public void tearDown() throws SQLException
  {
    Connection con = getConnection();
    con.createStatement().execute("drop table if exists orders_jdbc");
    con.createStatement().execute("drop table if exists test_rs");
    con.close();
  }

  @Test
  public void testFindColumn() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(selectAllSQL);
    assertEquals(1, resultSet.findColumn("COLA"));
    statement.close();
    connection.close();
  }

  @Test
  public void testGetColumnClassNameForBinary() throws Throwable
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    statement.execute("create or replace table bintable (b binary)");
    statement.execute("insert into bintable values ('00f1f2')");
    ResultSet resultSet = statement.executeQuery("select * from bintable");
    ResultSetMetaData metaData = resultSet.getMetaData();
    assertEquals(SnowflakeType.BINARY_CLASS_NAME, metaData.getColumnClassName(1));
    assertTrue(resultSet.next());
    Class<?> klass = Class.forName(SnowflakeType.BINARY_CLASS_NAME);
    Object ret0 = resultSet.getObject(1);
    assertEquals(ret0.getClass(), klass);
    byte[] ret = (byte[]) ret0;
    assertEquals(3, ret.length);
    assertEquals(ret[0], (byte) 0);
    assertEquals(ret[1], (byte) -15);
    assertEquals(ret[2], (byte) -14);
    statement.execute("drop table if exists bintable");
    statement.close();
    connection.close();
  }

  @Test
  public void testGetMethod() throws Throwable
  {
    String prepInsertString = "insert into test_get values(?, ?, ?, ?, ?, ?, ?, ?)";
    int bigInt = Integer.MAX_VALUE;
    long bigLong = Long.MAX_VALUE;
    short bigShort = Short.MAX_VALUE;
    String str = "hello";
    double bigDouble = Double.MAX_VALUE;
    float bigFloat = Float.MAX_VALUE;

    Connection connection = getConnection();
    Clob clob = connection.createClob();
    clob.setString(1, "hello world");
    Statement statement = connection.createStatement();
    statement.execute("create or replace table test_get(colA integer, colB number, colC number, "
                      + "colD string, colE double, colF float, colG boolean, colH text)");

    PreparedStatement prepStatement = connection.prepareStatement(prepInsertString);
    prepStatement.setInt(1, bigInt);
    prepStatement.setLong(2, bigLong);
    prepStatement.setLong(3, bigShort);
    prepStatement.setString(4, str);
    prepStatement.setDouble(5, bigDouble);
    prepStatement.setFloat(6, bigFloat);
    prepStatement.setBoolean(7, true);
    prepStatement.setClob(8, clob);
    prepStatement.execute();

    statement.execute("select * from test_get");
    ResultSet resultSet = statement.getResultSet();
    resultSet.next();
    assertEquals(bigInt, resultSet.getInt(1));
    assertEquals(bigInt, resultSet.getInt("COLA"));
    assertEquals(bigLong, resultSet.getLong(2));
    assertEquals(bigLong, resultSet.getLong("COLB"));
    assertEquals(bigShort, resultSet.getShort(3));
    assertEquals(bigShort, resultSet.getShort("COLC"));
    assertEquals(str, resultSet.getString(4));
    assertEquals(str, resultSet.getString("COLD"));
    Reader reader = resultSet.getCharacterStream("COLD");
    char[] sample = new char[str.length()];

    assertEquals(str.length(), reader.read(sample));
    assertEquals(str.charAt(0), sample[0]);
    assertEquals(str, new String(sample));

    //assertEquals(bigDouble, resultSet.getDouble(5), 0);
    //assertEquals(bigDouble, resultSet.getDouble("COLE"), 0);
    assertEquals(bigFloat, resultSet.getFloat(6), 0);
    assertEquals(bigFloat, resultSet.getFloat("COLF"), 0);
    assertTrue(resultSet.getBoolean(7));
    assertTrue(resultSet.getBoolean("COLG"));
    assertEquals("hello world", resultSet.getClob("COLH").toString());

    //test getStatement method
    assertEquals(statement, resultSet.getStatement());

    prepStatement.close();
    statement.execute("drop table if exists table_get");
    statement.close();
    resultSet.close();
    connection.close();
  }

  @Test
  public void testGetObjectOnDatabaseMetadataResultSet()
  throws SQLException
  {
    Connection connection = getConnection();
    DatabaseMetaData databaseMetaData = connection.getMetaData();
    ResultSet resultSet = databaseMetaData.getTypeInfo();
    resultSet.next();
    // SNOW-21375 "NULLABLE" Column is a SMALLINT TYPE
    assertEquals(DatabaseMetaData.typeNullable, resultSet.getObject("NULLABLE"));
    resultSet.close();
    connection.close();
  }

  @Test
  public void testGetShort() throws SQLException
  {
    ResultSet resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertEquals(0, resultSet.getShort(i));
    }

    resultSet.next();
    assertEquals(2, resultSet.getShort(1));
    assertEquals(5, resultSet.getShort(2));
    assertEquals(3, resultSet.getShort(3));
    assertEquals(1, resultSet.getShort(4));
    assertEquals(1, resultSet.getShort(5));
    assertEquals(1, resultSet.getShort(6));
    assertEquals(9126, resultSet.getShort(7));

    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getShort(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    // certain column types can only have certain values when called by getShort() or else a SQLexception is thrown.
    // These column types are varchar, char, and float.

    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getShort(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testGetInt() throws SQLException
  {
    ResultSet resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertEquals(0, resultSet.getInt(i));
    }

    resultSet.next();
    assertEquals(2, resultSet.getInt(1));
    assertEquals(5, resultSet.getInt(2));
    assertEquals(3, resultSet.getInt(3));
    assertEquals(1, resultSet.getInt(4));
    assertEquals(1, resultSet.getInt(5));
    assertEquals(1, resultSet.getInt(6));
    assertEquals(9126, resultSet.getInt(7));

    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getInt(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    // certain column types can only have certain values when called by getInt() or else a SQLException is thrown.
    // These column types are varchar, char, and float.
    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getInt(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testGetLong() throws SQLException
  {
    ResultSet resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertEquals(0, resultSet.getLong(i));
    }

    resultSet.next();
    assertEquals(2, resultSet.getLong(1));
    assertEquals(5, resultSet.getLong(2));
    assertEquals(3, resultSet.getLong(3));
    assertEquals(1, resultSet.getLong(4));
    assertEquals(1, resultSet.getLong(5));
    assertEquals(1, resultSet.getLong(6));
    assertEquals(9126, resultSet.getLong(7));

    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getLong(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    // certain column types can only have certain values when called by getLong() or else a SQLexception is thrown.
    // These column types are varchar, char, and float.
    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getLong(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testGetFloat() throws SQLException
  {
    ResultSet resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertEquals(0, resultSet.getFloat(i), .1);
    }

    resultSet.next();
    assertEquals(2, resultSet.getFloat(1), .1);
    assertEquals(5, resultSet.getFloat(2), .1);
    assertEquals(3.5, resultSet.getFloat(3), .1);
    assertEquals(1, resultSet.getFloat(4), .1);
    assertEquals(1, resultSet.getFloat(5), .1);
    assertEquals(1, resultSet.getFloat(6), .1);
    assertEquals(9126, resultSet.getFloat(7), .1);

    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getFloat(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    // certain column types can only have certain values when called by getFloat() or else a SQLexception is thrown.
    // These column types are varchar and char.
    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getFloat(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testGetDouble() throws SQLException
  {
    ResultSet resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertEquals(0, resultSet.getDouble(i), .1);
    }

    resultSet.next();
    assertEquals(2, resultSet.getDouble(1), .1);
    assertEquals(5, resultSet.getDouble(2), .1);
    assertEquals(3.5, resultSet.getDouble(3), .1);
    assertEquals(1, resultSet.getDouble(4), .1);
    assertEquals(1, resultSet.getDouble(5), .1);
    assertEquals(1, resultSet.getDouble(6), .1);
    assertEquals(9126, resultSet.getDouble(7), .1);

    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getDouble(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    // certain column types can only have certain values when called by getDouble() or else a SQLexception is thrown.
    // These column types are varchar and char.
    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getDouble(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testGetBigDecimal() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    statement.execute("create or replace table test_get(colA number(38,9))");
    PreparedStatement preparedStatement = connection.prepareStatement(
        "insert into test_get values(?)");
    BigDecimal bigDecimal1 = new BigDecimal("10000000000");
    preparedStatement.setBigDecimal(1, bigDecimal1);
    preparedStatement.executeUpdate();

    BigDecimal bigDecimal2 = new BigDecimal("100000000.123456789");
    preparedStatement.setBigDecimal(1, bigDecimal2);
    preparedStatement.execute();

    statement.execute("select * from test_get order by 1");
    ResultSet resultSet = statement.getResultSet();
    resultSet.next();
    assertEquals(bigDecimal2, resultSet.getBigDecimal(1));
    assertEquals(bigDecimal2, resultSet.getBigDecimal("COLA"));

    preparedStatement.close();
    statement.execute("drop table if exists test_get");
    statement.close();
    resultSet.close();
    connection.close();

    resultSet = numberCrossTesting();
    resultSet.next();
    for (int i = 1; i < 13; i++)
    {
      assertEquals(null, resultSet.getBigDecimal(i));
    }
    resultSet.next();
    assertEquals(new BigDecimal(2), resultSet.getBigDecimal(1));
    assertEquals(new BigDecimal(5), resultSet.getBigDecimal(2));
    assertEquals(new BigDecimal(3.5), resultSet.getBigDecimal(3));
    assertEquals(new BigDecimal(1), resultSet.getBigDecimal(4));
    assertEquals(new BigDecimal(1), resultSet.getBigDecimal(5));
    assertEquals(new BigDecimal(1), resultSet.getBigDecimal(6));
    assertEquals(new BigDecimal(9126), resultSet.getBigDecimal(7));
    for (int i = 8; i < 13; i++)
    {
      try
      {
        resultSet.getBigDecimal(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
    resultSet.next();
    for (int i = 5; i < 7; i++)
    {
      try
      {
        resultSet.getBigDecimal(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }
  }

  @Test
  public void testCursorPosition() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    statement.execute(selectAllSQL);
    ResultSet resultSet = statement.getResultSet();
    resultSet.next();
    assertTrue(resultSet.isFirst());
    assertEquals(1, resultSet.getRow());
    resultSet.next();
    assertTrue(!resultSet.isFirst());
    assertEquals(2, resultSet.getRow());
    assertTrue(!resultSet.isLast());
    resultSet.next();
    assertEquals(3, resultSet.getRow());
    assertTrue(resultSet.isLast());
    resultSet.next();
    assertTrue(resultSet.isAfterLast());
    statement.close();
    connection.close();
  }

  private byte[] intToByteArray(int i)
  {
    return BigInteger.valueOf(i).toByteArray();
  }

  private byte[] floatToByteArray(float i)
  {
    return ByteBuffer.allocate(Float8Vector.TYPE_WIDTH).putDouble(0, i).array();
  }

  @Test
  public void testGetBytes() throws SQLException
  {
    Properties props = new Properties();
    props.setProperty("enable_binary_datatype", Boolean.TRUE.toString());
    Connection connection = getConnection(props);
    Statement statement = connection.createStatement();
    statement.execute("create or replace table bin (b Binary)");

    byte[] bytes1 = new byte[0];
    byte[] bytes2 = {(byte) 0xAB, (byte) 0xCD, (byte) 0x12};
    byte[] bytes3 = {(byte) 0x00, (byte) 0xFF, (byte) 0x42, (byte) 0x01};

    PreparedStatement prepStatement = connection.prepareStatement(
        "insert into bin values (?), (?), (?)");
    prepStatement.setBytes(1, bytes1);
    prepStatement.setBytes(2, bytes2);
    prepStatement.setBytes(3, bytes3);
    prepStatement.execute();

    // Get results in hex format (default).
    ResultSet resultSet = statement.executeQuery("select * from bin");
    resultSet.next();
    assertArrayEquals(bytes1, resultSet.getBytes(1));
    assertEquals("", resultSet.getString(1));
    resultSet.next();
    assertArrayEquals(bytes2, resultSet.getBytes(1));
    assertEquals("ABCD12", resultSet.getString(1));
    resultSet.next();
    assertArrayEquals(bytes3, resultSet.getBytes(1));
    assertEquals("00FF4201", resultSet.getString(1));

    // Get results in base64 format.
    props.setProperty("binary_output_format", "BAse64");
    connection = getConnection(props);
    statement = connection.createStatement();
    resultSet = statement.executeQuery("select * from bin");
    resultSet.next();
    assertArrayEquals(bytes1, resultSet.getBytes(1));
    assertEquals("", resultSet.getString(1));
    resultSet.next();
    assertArrayEquals(bytes2, resultSet.getBytes(1));
    assertEquals("q80S", resultSet.getString(1));
    resultSet.next();
    assertArrayEquals(bytes3, resultSet.getBytes(1));
    assertEquals("AP9CAQ==", resultSet.getString(1));

    statement.execute("drop table if exists bin");
    connection.close();

    resultSet = numberCrossTesting();
    resultSet.next();
    // assert that 0 is returned for null values for every type of value
    for (int i = 1; i < 13; i++)
    {
      assertArrayEquals(null, resultSet.getBytes(i));
    }
    resultSet.next();
    assertArrayEquals(intToByteArray(2), resultSet.getBytes(1));
    assertArrayEquals(intToByteArray(5), resultSet.getBytes(2));
    assertArrayEquals(floatToByteArray(3.5f), resultSet.getBytes(3));
    assertArrayEquals(new byte[]{1}, resultSet.getBytes(4));
    assertArrayEquals(new byte[]{(byte) '1'}, resultSet.getBytes(5));
    assertArrayEquals("1".getBytes(), resultSet.getBytes(6));

    for (int i = 7; i < 12; i++)
    {
      try
      {
        resultSet.getBytes(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }

    byte[] decoded = SFBinary.fromHex("48454C4C4F").getBytes();

    assertArrayEquals(decoded, resultSet.getBytes(12));

  }

  @Test
  public void testResultSetMetadata() throws SQLException
  {
    Connection connection = getConnection();
    final Map<String, String> params = getConnectionParameters();
    Statement statement = connection.createStatement();

    statement.execute("create or replace table test_rsmd(colA number(20, 5), colB string)");
    statement.execute("insert into test_rsmd values(1.00, 'str'),(2.00, 'str2')");
    ResultSet resultSet = statement.executeQuery("select * from test_rsmd");
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    assertEquals(params.get("database").toUpperCase(),
                 resultSetMetaData.getCatalogName(1).toUpperCase());
    assertEquals(params.get("schema").toUpperCase(),
                 resultSetMetaData.getSchemaName(1).toUpperCase());
    assertEquals("TEST_RSMD", resultSetMetaData.getTableName(1));
    assertEquals(String.class.getName(),
                 resultSetMetaData.getColumnClassName(2));
    assertEquals(2, resultSetMetaData.getColumnCount());
    assertEquals(22, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals("COLA", resultSetMetaData.getColumnLabel(1));
    assertEquals("COLA", resultSetMetaData.getColumnName(1));
    assertEquals(3, resultSetMetaData.getColumnType(1));
    assertEquals("NUMBER", resultSetMetaData.getColumnTypeName(1));
    assertEquals(20, resultSetMetaData.getPrecision(1));
    assertEquals(5, resultSetMetaData.getScale(1));
    assertFalse(resultSetMetaData.isAutoIncrement(1));
    assertFalse(resultSetMetaData.isCaseSensitive(1));
    assertFalse(resultSetMetaData.isCurrency(1));
    assertFalse(resultSetMetaData.isDefinitelyWritable(1));
    assertEquals(ResultSetMetaData.columnNullable,
                 resultSetMetaData.isNullable(1));
    assertTrue(resultSetMetaData.isReadOnly(1));
    assertTrue(resultSetMetaData.isSearchable(1));
    assertTrue(resultSetMetaData.isSigned(1));
    SnowflakeResultSetMetaData secretMetaData =
        resultSetMetaData.unwrap(SnowflakeResultSetMetaData.class);
    List<String> colNames = secretMetaData.getColumnNames();
    assertEquals("COLA", colNames.get(0));
    assertEquals("COLB", colNames.get(1));
    assertEquals(Types.DECIMAL, secretMetaData.getInternalColumnType(1));
    assertEquals(Types.VARCHAR, secretMetaData.getInternalColumnType(2));
    assertTrue(Pattern
                   .matches("[a-z0-9]{8}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{4}-[a-z0-9]{12}",
                            secretMetaData.getQueryID()));

    statement.execute("drop table if exists test_rsmd");
    statement.close();
    connection.close();
  }

  // SNOW-31647
  @Test
  public void testColumnMetaWithZeroPrecision() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();

    statement.execute("create or replace table testColDecimal(cola number(38, 0), " +
                      "colb number(17, 5))");

    ResultSet resultSet = statement.executeQuery("select * from testColDecimal");
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

    assertThat(resultSetMetaData.getColumnType(1), is(Types.BIGINT));
    assertThat(resultSetMetaData.getColumnType(2), is(Types.DECIMAL));
    assertThat(resultSetMetaData.isSigned(1), is(true));
    assertThat(resultSetMetaData.isSigned(2), is(true));


    statement.execute("drop table if exists testColDecimal");

    connection.close();
  }

  @Test
  public void testGetObjectOnFixedView() throws Exception
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();

    statement.execute(
        "create or replace table testFixedView" +
        "(C1 STRING NOT NULL COMMENT 'JDBC', "
        + "C2 STRING, C3 STRING, C4 STRING, C5 STRING, C6 STRING, "
        + "C7 STRING, C8 STRING, C9 STRING) "
        + "stage_file_format = (field_delimiter='|' "
        + "error_on_column_count_mismatch=false)");

    // put files
    assertTrue("Failed to put a file",
               statement.execute(
                   "PUT file://" +
                   getFullPathFileInResource(TEST_DATA_FILE) + " @%testFixedView"));

    ResultSet resultSet = statement.executeQuery(
        "PUT file://" +
        getFullPathFileInResource(TEST_DATA_FILE_2) + " @%testFixedView");

    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    while (resultSet.next())
    {
      for (int i = 0; i < resultSetMetaData.getColumnCount(); i++)
      {
        assertNotNull(resultSet.getObject(i + 1));
      }
    }

    resultSet.close();
    statement.execute("drop table if exists testFixedView");
    statement.close();
    connection.close();
  }

  @Test
  @ConditionalIgnoreRule.ConditionalIgnore(condition = RunningOnGithubAction.class)
  public void testGetColumnDisplaySizeAndPrecision() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();

    ResultSet resultSet = statement.executeQuery("select cast(1 as char)");
    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
    assertEquals(1, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(1, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select cast(1 as number(38, 0))");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(39, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(38, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select cast(1 as decimal(25, 15))");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(27, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(25, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select cast(1 as string)");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(1, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(1, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select cast(1 as string(30))");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(1, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(1, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select to_date('2016-12-13', 'YYYY-MM-DD')");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(10, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(10, resultSetMetaData.getPrecision(1));

    resultSet = statement.executeQuery("select to_time('12:34:56', 'HH24:MI:SS')");
    resultSetMetaData = resultSet.getMetaData();
    assertEquals(8, resultSetMetaData.getColumnDisplaySize(1));
    assertEquals(8, resultSetMetaData.getPrecision(1));

    statement.close();
    connection.close();
  }

  @Test
  public void testGetBoolean() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    statement.execute("create or replace table testBoolean(cola boolean)");
    statement.execute("insert into testBoolean values(false)");
    ResultSet resultSet = statement.executeQuery("select * from testBoolean");
    resultSet.next();
    assertFalse(resultSet.getBoolean(1));

    statement.execute("insert into testBoolean values(true)");
    resultSet = statement.executeQuery("select * from testBoolean");
    resultSet.next();
    assertFalse(resultSet.getBoolean(1));
    resultSet.next();
    assertTrue(resultSet.getBoolean(1));
    statement.execute("drop table if exists testBoolean");

    statement.execute("create or replace table test_types(c1 number, c2 integer,  c3 varchar, c4 char, " +
                      "c5 boolean, c6 float, c7 binary, c8 date, c9 datetime, c10 time, c11 timestamp_ltz, " +
                      "c12 timestamp_tz)");
    statement.execute("insert into test_types values (null, null, null, null, null, null, null, null, null, null, " +
                      "null, null)");
    statement.execute(
        "insert into test_types (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12) values(1, 1, '1'," +
        "'1', true, 1.0, '48454C4C4F', '1994-12-27', " +
        "'1994-12-27 05:05:05', '05:05:05', '1994-12-27 05:05:05 +00:05', '1994-12-27 05:05:05')");
    statement.execute(
        "insert into test_types (c1, c2, c3, c4) values(2, 3, '4', '5')");
    resultSet = statement.executeQuery("select * from test_types");

    resultSet.next();
    // assert that getBoolean returns false for null values
    for (int i = 1; i < 13; i++)
    {
      assertFalse(resultSet.getBoolean(i));
    }
    // do the other columns that are out of order
    // go to next row of result set column
    resultSet.next();
    // assert that getBoolean returns true for values that equal 1
    assertTrue(resultSet.getBoolean(1));
    assertTrue(resultSet.getBoolean(2));
    assertTrue(resultSet.getBoolean(3));
    assertTrue(resultSet.getBoolean(4));
    assertTrue(resultSet.getBoolean(5));
    for (int i = 6; i < 13; i++)
    {
      try
      {
        resultSet.getBoolean(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }

    resultSet.next();
    for (int i = 1; i < 5; i++)
    {
      try
      {
        resultSet.getBoolean(i);
        fail("Failing on " + i);
      }
      catch (SQLException ex)
      {
        assertEquals(200038, ex.getErrorCode());
      }
    }

    statement.close();
    connection.close();
  }

  @Test
  public void testGetClob() throws Throwable
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    statement.execute("create or replace table testClob(cola text)");
    statement.execute("insert into testClob values('hello world')");
    statement.execute("insert into testClob values('hello world1')");
    statement.execute("insert into testClob values('hello world2')");
    statement.execute("insert into testClob values('hello world3')");
    ResultSet resultSet = statement.executeQuery("select * from testClob");
    resultSet.next();
    // test reading Clob
    char[] chars = new char[100];
    Reader reader = resultSet.getClob(1).getCharacterStream();
    int charRead;
    charRead = reader.read(chars, 0, chars.length);
    assertEquals(charRead, 11);
    assertEquals("hello world", resultSet.getClob(1).toString());

    // test reading truncated clob
    resultSet.next();
    Clob clob = resultSet.getClob(1);
    assertEquals(clob.length(), 12);
    clob.truncate(5);
    reader = clob.getCharacterStream();

    charRead = reader.read(chars, 0, chars.length);
    assertEquals(charRead, 5);

    // read from input stream
    resultSet.next();
    final InputStream input = resultSet.getClob(1).getAsciiStream();

    Reader in = new InputStreamReader(input, StandardCharsets.UTF_8);
    charRead = in.read(chars, 0, chars.length);
    assertEquals(charRead, 12);

    statement.close();
    connection.close();
  }

  @Test
  public void testFetchOnClosedResultSet() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(selectAllSQL);
    assertTrue(!resultSet.isClosed());
    resultSet.close();
    assertTrue(resultSet.isClosed());
    assertFalse(resultSet.next());
  }

  @Test
  public void testReleaseDownloaderCurrentMemoryUsage() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    final long initialMemoryUsage = SnowflakeChunkDownloader.getCurrentMemoryUsage();

    statement.executeQuery(
        "select current_date(), true,2345234, 2343.0, 'testrgint\\n\\t' from table(generator(rowcount=>1000000))");

    assertThat("hold memory usage for the resultSet before close",
               SnowflakeChunkDownloader.getCurrentMemoryUsage() - initialMemoryUsage >= 0);
    statement.close();
    assertThat("closing statement didn't release memory allocated for result",
               SnowflakeChunkDownloader.getCurrentMemoryUsage(), equalTo(initialMemoryUsage));
    connection.close();
  }

  @Test
  @ConditionalIgnoreRule.ConditionalIgnore(condition = RunningOnGithubAction.class)
  public void testResultColumnSearchCaseSensitiveOld() throws Exception
  {
    subTestResultColumnSearchCaseSensitive("JDBC_RS_COLUMN_CASE_INSENSITIVE");
  }

  @Test
  public void testResultColumnSearchCaseSensitive() throws Exception
  {
    subTestResultColumnSearchCaseSensitive("CLIENT_RESULT_COLUMN_CASE_INSENSITIVE");
  }

  private void subTestResultColumnSearchCaseSensitive(String parameterName) throws Exception
  {
    Properties prop = new Properties();
    prop.put("tracing", "FINEST");
    Connection connection = getConnection(prop);
    Statement statement = connection.createStatement();

    ResultSet resultSet = statement.executeQuery("select 1 AS TESTCOL");

    resultSet.next();
    assertEquals("1", resultSet.getString("TESTCOL"));
    assertEquals("1", resultSet.getString("TESTCOL"));
    try
    {
      resultSet.getString("testcol");
      fail();
    }
    catch (SQLException e)
    {
      assertEquals("Column not found: testcol", e.getMessage());
    }

    // try to do case-insensitive search
    statement.executeQuery(
        String.format("alter session set %s=true", parameterName));

    resultSet = statement.executeQuery("select 1 AS TESTCOL");
    resultSet.next();

    // get twice so that the code path can hit the place where
    // we use cached key pair (columnName, index)
    assertEquals("1", resultSet.getString("TESTCOL"));
    assertEquals("1", resultSet.getString("TESTCOL"));
    assertEquals("1", resultSet.getString("testcol"));
    assertEquals("1", resultSet.getString("testcol"));
  }

  @Test
  public void testInvalidColumnIndex() throws SQLException
  {
    Connection connection = getConnection();
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(selectAllSQL);

    resultSet.next();
    try
    {
      resultSet.getString(0);
      fail();
    }
    catch (SQLException e)
    {
      assertEquals(200032, e.getErrorCode());
    }
    try
    {
      resultSet.getString(2);
      fail();
    }
    catch (SQLException e)
    {
      assertEquals(200032, e.getErrorCode());
    }
    resultSet.close();
    statement.close();
    connection.close();
  }

  /**
   * SNOW-28882: wasNull was not set properly
   */
  @Test
  public void testWasNull() throws Exception
  {
    Connection con = getConnection();
    ResultSet ret = con.createStatement().executeQuery(
        "select cast(1/nullif(0,0) as double)," +
        "cast(1/nullif(0,0) as int), 100, " +
        "cast(1/nullif(0,0) as number(8,2))");
    ret.next();
    assertThat("Double value cannot be null",
               ret.getDouble(1), equalTo(0.0));
    assertThat("wasNull should be true", ret.wasNull());
    assertThat("Integer value cannot be null",
               ret.getInt(2), equalTo(0));
    assertThat("wasNull should be true", ret.wasNull());
    assertThat("Non null column",
               ret.getInt(3), equalTo(100));
    assertThat("wasNull should be false", !ret.wasNull());
    assertThat("BigDecimal value must be null",
               ret.getBigDecimal(4), nullValue());
    assertThat("wasNull should be true", ret.wasNull());
  }

  /**
   * SNOW-28390
   */
  @Test
  public void testParseInfAndNaNNumber() throws Exception
  {
    Connection con = getConnection();
    ResultSet ret = con.createStatement().executeQuery(
        "select to_double('inf'), to_double('-inf')");
    ret.next();
    assertThat("Positive Infinite Number",
               ret.getDouble(1), equalTo(Double.POSITIVE_INFINITY));
    assertThat("Negative Infinite Number",
               ret.getDouble(2), equalTo(Double.NEGATIVE_INFINITY));
    assertThat("Positive Infinite Number",
               ret.getFloat(1), equalTo(Float.POSITIVE_INFINITY));
    assertThat("Negative Infinite Number",
               ret.getFloat(2), equalTo(Float.NEGATIVE_INFINITY));

    ret = con.createStatement().executeQuery(
        "select to_double('nan')");
    ret.next();
    assertThat("Parse NaN",
               ret.getDouble(1), equalTo(Double.NaN));
    assertThat("Parse NaN",
               ret.getFloat(1), equalTo(Float.NaN));
  }

  /**
   * SNOW-33227
   */
  @Test
  public void testTreatDecimalAsInt() throws Exception
  {
    Connection con = getConnection();
    ResultSet ret = con.createStatement().executeQuery(
        "select 1");

    ResultSetMetaData metaData = ret.getMetaData();
    assertThat(metaData.getColumnType(1), equalTo(Types.BIGINT));

    con.createStatement().execute("alter session set jdbc_treat_decimal_as_int = false");

    ret = con.createStatement().executeQuery("select 1");
    metaData = ret.getMetaData();
    assertThat(metaData.getColumnType(1), equalTo(Types.DECIMAL));

    con.close();
  }

  @Test
  public void testIsLast() throws Exception
  {

    Connection con = getConnection();
    ResultSet ret = con.createStatement().executeQuery(
        "select * from orders_jdbc");
    assertTrue("should be before the first", ret.isBeforeFirst());
    assertFalse("should not be the first", ret.isFirst());

    ret.next();

    assertFalse("should not be before the first", ret.isBeforeFirst());
    assertTrue("should be the first", ret.isFirst());

    int cnt = 0;
    while (ret.next())
    {
      cnt++;
      if (cnt == 72)
      {
        assertTrue("should be the last", ret.isLast());
        assertFalse("should not be after the last", ret.isAfterLast());
      }
    }
    assertEquals(72, cnt);

    ret.next();

    assertFalse("should not be the last", ret.isLast());
    assertTrue("should be afterthe last", ret.isAfterLast());

    // PUT one file
    ret = con.createStatement().executeQuery(
        "PUT file://" +
        getFullPathFileInResource(TEST_DATA_FILE) + " @~");

    assertTrue("should be before the first", ret.isBeforeFirst());
    assertFalse("should not be the first", ret.isFirst());

    ret.next();

    assertFalse("should not be before the first", ret.isBeforeFirst());
    assertTrue("should be the first", ret.isFirst());

    assertTrue("should be the last", ret.isLast());
    assertFalse("should not be after the last", ret.isAfterLast());

    ret.next();

    assertFalse("should not be the last", ret.isLast());
    assertTrue("should be after the last", ret.isAfterLast());
  }

  @Test
  public void testMultipleChunks() throws SQLException, IOException
  {
    Connection con = getConnection();
    Statement statement = con.createStatement();

    // 10000 rows should be enough to force result into multiple chunks
    ResultSet resultSet =
        statement.executeQuery("select seq8(), randstr(1000, random()) from table(generator(rowcount => 10000))");
    int cnt = 0;
    while (resultSet.next())
    {
      ++cnt;
    }
    assertTrue(cnt >= 0);
    Telemetry telemetry = con.unwrap(SnowflakeConnectionV1.class).getSfSession().getTelemetryClient();
    LinkedList<TelemetryData> logs = ((TelemetryClient) telemetry).logBuffer();

    // there should be a log for each of the following fields
    TelemetryField[] expectedFields =
        {TelemetryField.TIME_CONSUME_FIRST_RESULT, TelemetryField.TIME_CONSUME_LAST_RESULT,
         TelemetryField.TIME_WAITING_FOR_CHUNKS, TelemetryField.TIME_DOWNLOADING_CHUNKS,
         TelemetryField.TIME_PARSING_CHUNKS};
    boolean[] succeeded = new boolean[expectedFields.length];

    for (int i = 0; i < expectedFields.length; i++)
    {
      succeeded[i] = false;
      for (TelemetryData log : logs)
      {
        if (log.getMessage().get(TelemetryUtil.TYPE).textValue().equals(expectedFields[i].field))
        {
          succeeded[i] = true;
          break;
        }
      }
    }

    for (int i = 0; i < expectedFields.length; i++)
    {
      assertThat(String.format("%s field not found in telemetry logs\n", expectedFields[i].field), succeeded[i]);
    }
    telemetry.sendBatchAsync();
  }

  @Test
  public void testUpdateCountOnCopyCmd() throws Exception
  {
    Connection con = getConnection();
    Statement statement = con.createStatement();

    statement.execute("create or replace table testcopy(cola string)");

    // stage table has no file. Should return 0.
    int rowCount = statement.executeUpdate("copy into testcopy");
    assertThat(rowCount, is(0));

    // copy one file into table stage
    statement.execute("copy into @%testcopy from (select 'test_string')");
    rowCount = statement.executeUpdate("copy into testcopy");
    assertThat(rowCount, is(1));

    //cleanup
    statement.execute("drop table if exists testcopy");

    con.close();
  }

  @Test
  public void testGetTimeNullTimestampAndTimestampNullTime() throws Throwable
  {
    try (Connection con = getConnection())
    {
      con.createStatement().execute("create or replace table testnullts(c1 timestamp, c2 time)");
      try
      {
        con.createStatement().execute("insert into testnullts(c1, c2) values(null, null)");
        ResultSet rs = con.createStatement().executeQuery("select * from testnullts");
        assertTrue("should return result", rs.next());
        assertNull("return value must be null", rs.getTime(1));
        assertNull("return value must be null", rs.getTimestamp(2));
        rs.close();
      }
      finally
      {
        con.createStatement().execute("drop table if exists testnullts");
      }
    }
  }
}