All Downloads are FREE. Search and download functionalities are using the official Maven repository.

net.snowflake.client.SnowflakeDriverIT Maven / Gradle / Ivy

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.snowflake.client;

import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;
import java.util.Properties;
import java.util.TimeZone;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.google.common.io.CountingOutputStream;
import com.google.common.io.FileBackedOutputStream;
import net.snowflake.client.jdbc.ErrorCode;
import net.snowflake.client.jdbc.SnowflakeConnectionV1;
import net.snowflake.client.jdbc.SnowflakeDriver;
import net.snowflake.client.jdbc.SnowflakeSQLException;
import com.snowflake.gscommon.core.SqlState;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Timer;
import java.util.TimerTask;

import java.util.UUID;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import java.util.List;

import static org.junit.Assert.*;


/**
 * This test assumes that GS and XP cluster has been set up
 *
 * @author jhuang
 */
public class SnowflakeDriverIT extends AbstractDriverIT
{
  private static final int MAX_CONCURRENT_QUERIES_PER_USER = 50;

  private ObjectMapper mapper = new ObjectMapper();

  private static Logger logger =
  Logger.getLogger(SnowflakeDriverIT.class.getName());

  public SnowflakeDriverIT()
  {
  }

  @BeforeClass
  public static void setUpClass() throws Exception
  {
    outputLine("[ SnowflakeDriverIT ] start suite");
    outputLine("SnowflakeDriverIT : running");
    outputLine("running driver SnowflakeDriverIT");

    try
    {
      Class.forName("net.snowflake.client.jdbc.SnowflakeDriver");
    }
    catch (ClassNotFoundException ex)
    {
      logger.log(Level.SEVERE, "Driver not found", ex);
    }

    try
    {
      Connection connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                            !USE_V1_QUERY_API,
                                            DONT_INJECT_SOCKET_TIMEOUT);

      Statement statement = connection.createStatement();

      statement.executeUpdate("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)");

      statement.execute("alter session set multi_dimensional_clustering=true");
      statement.executeUpdate("create or replace table clustered_jdbc " +
                                  "(c1 number, c2 number) cluster by (c1)");

      statement.close();

      String sfProjectRoot = getSFProjectRoot();

            // put files
      boolean status =
      statement.execute("PUT file://" + sfProjectRoot +
                    "/ExecPlatform/Database/data/orders_10*.csv @%orders_jdbc");

      assertTrue("put command fails", status);

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

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

      connection.close();
    }
    catch (Throwable ex)
    {
      ex.printStackTrace();
      throw ex;
    }
  }

  private static String getSFProjectRoot() throws UnsupportedEncodingException
  {
    URL location =
        SnowflakeDriverIT.class.getProtectionDomain().getCodeSource().getLocation();
    String testDir = URLDecoder.decode(location.getPath(), "UTF-8");

    String sfProjectRoot =
        testDir.substring(0, testDir.indexOf("Client"));
    return sfProjectRoot;
  }

  @AfterClass
  public static void tearDownClass() throws SQLException
  {
    outputLine("[ SnowflakeDriverIT ] done suite " +
               diffs.toString() + " diff " +
               succs.toString() + " suc");

    Connection connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                          !USE_V1_QUERY_API,
                                          DONT_INJECT_SOCKET_TIMEOUT);

    Statement statement = connection.createStatement();

    statement.executeUpdate("drop table if exists clustered_jdbc");
    statement.executeUpdate("drop table if exists orders_jdbc");
    statement.close();

    connection.close();
  }

  private static Connection getExternalConnection(boolean enableV1Query)
          throws SQLException
  {
    String accountStr = "externalaccount";

    // build connection properties
    Properties properties = new Properties();

    properties.put("user", "snowman");
    properties.put("password", "test");
    properties.put("account", accountStr);
    properties.put("ssl", "off");

    if (enableV1Query)
      properties.put("useV1QueryAPI", "on");

    // create a new connection
    String connectStr = System.getenv("SF_JDBC_CONNECT_STRING");

    // use the default connection string if it is not set in environment
    if(connectStr == null)
    {
      connectStr = "jdbc:snowflake://localhost:8080";
    }

    return DriverManager.getConnection(connectStr, properties);
  }

  /** Create a connection to the snowflake account. */
  private static Connection getSnowflakeConnection(boolean enableV1Query)
          throws SQLException
  {
    String accountStr = "snowflake";

    // build connection properties
    Properties properties = new Properties();

    properties.put("user", "admin");
    properties.put("password", "test");
    properties.put("account", accountStr);
    properties.put("ssl", "off");

    if (enableV1Query)
      properties.put("useV1QueryAPI", "on");

    // create a new connection
    String connectStr = System.getenv("SF_JDBC_CONNECT_STRING");

    // use the default connection string if it is not set in environment
    if(connectStr == null)
    {
      connectStr = "jdbc:snowflake://localhost:8080";
    }

    return DriverManager.getConnection(connectStr, properties);
  }

  @Test
  public void testInvalidURL() throws SQLException
  {
    outputLine("running testInvalidURL");

    try
    {
      // build connection properties
      Properties properties = new Properties();

      // invalid connection url
      String connectStr = "jdbc:";

      assertTrue((new SnowflakeDriver()).connect(connectStr, properties)==null);

      assertTrue(!(new SnowflakeDriver()).acceptsURL(
          "jdbc:"));

      assertTrue((new SnowflakeDriver()).acceptsURL(
          "jdbc:snowflake://localhost:8080"));

      assertTrue((new SnowflakeDriver()).acceptsURL(
          "jdbc:snowflake://localhost:8080/"));

      assertTrue((new SnowflakeDriver()).acceptsURL(
          "jdbc:snowflake://localhost:8080/?a=b"));

      assertTrue((new SnowflakeDriver()).acceptsURL(
          "jdbc:snowflake://localhost:8080/?a=b&c=d"));

      assertTrue(!(new SnowflakeDriver()).acceptsURL(
          "jdbc:snowflake://localhost:8080/a=b"));

      succs.incrementAndGet();
      outputLine("testInvalidURL : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();

      outputLine("testInvalidURL : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }

  }


  //@Test
  public void testConnections() throws SQLException
  {

    outputLine("running testConnections");

    ExecutorService executorService =
        Executors.newFixedThreadPool(MAX_CONCURRENT_QUERIES_PER_USER);

    List futures = new ArrayList();

    try
    {
      // create 30 threads, each open a connection and submit a query that
      // runs for 10 seconds
      for(int idx = 0; idx < MAX_CONCURRENT_QUERIES_PER_USER; idx++)
      {
        logger.info("open a new connection and submit query " + idx);

        final int queryIdx = idx;

        futures.add(executorService.submit(new Callable()
            {
              @Override
              public Boolean call() throws Exception
              {
                Connection connection = null;
                Statement statement = null;
                ResultSet resultSet = null;
                ResultSetMetaData resultSetMetaData = null;

                try
                {
                  connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                             !USE_V1_QUERY_API,
                                             DONT_INJECT_SOCKET_TIMEOUT);

                  statement = connection.createStatement();

                  // execute query
                  resultSet = statement.executeQuery(
                      "SELECT system$sleep(10) % 1");

                  resultSetMetaData = resultSet.getMetaData();

                  // assert column count
                  assertTrue(resultSetMetaData.getColumnCount() == 1);

                  // assert we get 1 row
                  for (int i = 0; i < 1; i++)
                  {
                    assertTrue(resultSet.next());

                    // assert each column is not null except the last one
                    for (int j = 1; j < 2; j++)
                    {
                      assertTrue(resultSet.getInt(j) == 0);
                    }
                  }

                  logger.info("Query " + queryIdx + " passed ");

                  statement.close();
                }
                catch (Throwable ex)
                {
                  logger.log(Level.SEVERE, "Test failed with exception: ", ex);
                  assertTrue(ex.getLocalizedMessage(), false);
                }
                finally
                {
                  closeSQLObjects(resultSet, statement, connection);
                }
                return true;
              }
            }
        ));
      };

      executorService.shutdown();

      for (int idx = 0; idx < MAX_CONCURRENT_QUERIES_PER_USER; idx++)
        futures.get(idx).get();

      succs.incrementAndGet();
      outputLine("testConnections : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();

      outputLine("testConnections : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
  }

  /**
   * Test setting parameter values at the session level during connection
   *
   * @throws IOException
   * @throws SQLException
   */
  @Test
  public void testSessionParameters() throws IOException, SQLException
  {
    outputLine("running testSessionParameters");
    Connection connection_on = null, connection_off = null;
    Statement statement_on = null, statement_off = null;
    ResultSet resultSet_on = null, resultSet_off = null;
    ResultSetMetaData resultSetMetaData_on = null, resultSetMetaData_off = null;

    try
    {
      // Set the following parameters during connection
      Properties paramProperties_on = new Properties();
      paramProperties_on.put("show_invisible_columns", "true");
      // test autocommit parameter setting. Should be ignored on server side
      // and have no effect
      paramProperties_on.put("autocommit", "true");
      connection_on = getConnection(GET_ACCOUNT_FROM_ENV, true,
                                    DONT_INJECT_SOCKET_TIMEOUT,
                                    paramProperties_on);

      // execute query
      statement_on = connection_on.createStatement();

      resultSet_on = statement_on.executeQuery(
          "show columns in clustered_jdbc");

      // Verify that we get three rows
      for (int i = 0; i < 3; i++)
      {
        assertTrue(resultSet_on.next());
      }

      // Now try the same query with properties off, expect different results
      Properties paramProperties_off = new Properties();
      paramProperties_off.put("show_invisible_columns", "false");
      // test autocommit parameter setting. Should be ignored on server side
      // and have no effect
      paramProperties_off.put("autocommit", "false");

      connection_off = getConnection(GET_ACCOUNT_FROM_ENV, true,
                                     DONT_INJECT_SOCKET_TIMEOUT,
                                     paramProperties_off);

      // execute query
      statement_off = connection_off.createStatement();

      resultSet_off = statement_off.executeQuery(
          "show columns in clustered_jdbc");

      // Assert that we got only 2 rows
      for (int i = 0; i < 2; i ++)
      {
        assertTrue(resultSet_off.next());
      }
      assertFalse(resultSet_off.next());

      succs.incrementAndGet();
      outputLine("testSessionParameters: success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSessionParameters: difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet_on, statement_on, connection_on);
      closeSQLObjects(resultSet_off, statement_off, connection_off);
    }
  }

  @Test
  public void testParameters() throws IOException, SQLException
  {
    outputLine("running testParameters");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                  !USE_V1_QUERY_API,
                                  DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      statement.execute("alter session set rows_per_resultset=2048");
      statement.close();

      // execute query
      resultSet = statement.executeQuery("SELECT * FROM orders_jdbc");
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 9);

      // assert we get 28 rows
      for(int i = 0; i < 28; i++)
      {
        assertTrue(resultSet.next());

        // assert each column is not null except the last one
        for(int j = 1; j < 10; j++)
        {
          assertTrue(resultSet.getString(j) != null);
        }
      }

      statement.close();

      succs.incrementAndGet();
      outputLine("testParameters : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testParameters : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testDDLs() throws IOException, SQLException
  {
    outputLine("running testDDLs");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // execute DDLs
      statement.executeUpdate(
          "CREATE OR REPLACE TABLE testDDLs(version number, name string)");
      statement.close();

      succs.incrementAndGet();
      outputLine("testDDLs : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testDDLs : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.executeUpdate("DROP TABLE testDDLs");
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  // ===== Autocommit Tests Start =====

  private final static String enableAutocommitFeatureStmt = "ALTER SESSION " +
      "SET AUTOCOMMIT_API_SUPPORTED=true";
  private final static String disableAutocommitFeatureStmt = "ALTER SESSION " +
      "SET AUTOCOMMIT_API_SUPPORTED=false";
  private final static String beginStmt = "BEGIN";
  private final static String commitStmt = "COMMIT";
  private final static String getCurrenTransactionStmt = "SELECT " +
      "CURRENT_TRANSACTION()";

  private long getCurrentTransaction(Connection connection) throws SQLException
  {
    try (Statement statement = connection.createStatement())
    {
      statement.execute(getCurrenTransactionStmt);
      ResultSet rs = statement.getResultSet();
      if (rs.next())
      {
        String txnId = rs.getString(1);
        return txnId != null ? Long.valueOf(txnId) : 0L;
      }
    }

    throw new SQLException(getCurrenTransactionStmt + " didn't return a " +
                           "result.");
  }

  /**
   * Tests backward compatibility of the client. i.e. when the autocommit api
   * is disabled, calling commit/rollback and set/getAutoCommit() on the
   * connection are no-ops.
   * @throws IOException
   * @throws SQLException
   */
  @Test
  public void testAutocommitApiSupportBackwardCompatibility()
      throws IOException, SQLException
  {
    outputLine("running testAutocommitApiSupportBackwardCompatibility");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // disable autocommit api support to test backward compatibility
      statement.execute(disableAutocommitFeatureStmt);

      // ensure that autocommit is set
      Assert.assertEquals(true, connection.getAutoCommit());

      // set it to false, this should have no effect
      // this is the backward compatible behavior
      connection.setAutoCommit(false);
      Assert.assertEquals(true, connection.getAutoCommit());

      // set it to true, this should have no effect either
      // this is the backward compatible behavior
      connection.setAutoCommit(true);
      Assert.assertEquals(true, connection.getAutoCommit());

      // ensure that autocommit is set
      Assert.assertTrue(connection.getAutoCommit());

      // start a transaction manually
      statement.executeUpdate(beginStmt);

      Assert.assertTrue(getCurrentTransaction(connection) > 0);

      // ensure that commit does nothing
      connection.commit();
      Assert.assertTrue(getCurrentTransaction(connection) == 0);

      // ensure that a rollback dooes nothing either
      connection.rollback();
      Assert.assertTrue(getCurrentTransaction(connection) == 0);

      // enable autocommit api support to test autocommit
      statement.executeUpdate(enableAutocommitFeatureStmt);

      // autocommit api is now enabled
      // commit should work now
      connection.commit();
      Assert.assertEquals(0, getCurrentTransaction(connection));

    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testAutocommit : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  /**
   * Tests autocommit APIs
   * @throws IOException
   * @throws SQLException
   */
  @Test
  public void testAutocommitApis()
      throws IOException, SQLException
  {
    outputLine("running testAutocommitApis");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      statement.executeUpdate(enableAutocommitFeatureStmt);

      // == 1. test commit ==
      // disable autocommit
      connection.setAutoCommit(false);
      Assert.assertEquals(false, connection.getAutoCommit());
      Assert.assertEquals(0, getCurrentTransaction(connection));

      // create a table, this should not start a transaction
      statement.executeUpdate(
          "CREATE OR REPLACE TABLE AUTOCOMMIT_API_TEST (i int)");
      Assert.assertEquals(0, getCurrentTransaction(connection));

      // insert into it this should start a transaction.
      statement.executeUpdate("INSERT INTO AUTOCOMMIT_API_TEST VALUES (1)");
      Assert.assertTrue(getCurrentTransaction(connection) > 0);

      // commit it using the api
      connection.commit();
      Assert.assertEquals(false, connection.getAutoCommit());
      Assert.assertEquals(0, getCurrentTransaction(connection));
      resultSet = statement.executeQuery(
          "SELECT COUNT(*) FROM AUTOCOMMIT_API_TEST WHERE i = 1");
      Assert.assertTrue(resultSet.next());
      Assert.assertEquals(1, resultSet.getInt(1));
      resultSet.close();

      // == 2. test rollback ==
      // delete from the table, should start a transaction.
      statement.executeUpdate("DELETE FROM AUTOCOMMIT_API_TEST");
      Assert.assertTrue(getCurrentTransaction(connection) > 0);

      // roll it back using the api
      connection.rollback();
      Assert.assertEquals(false, connection.getAutoCommit());
      Assert.assertEquals(0, getCurrentTransaction(connection));
      resultSet = statement.executeQuery(
          "SELECT COUNT(*) FROM AUTOCOMMIT_API_TEST WHERE i = 1");
      Assert.assertTrue(resultSet.next());
      Assert.assertEquals(1, resultSet.getInt(1));

    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testAutocommit : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      statement.execute("DROP TABLE AUTOCOMMIT_API_TEST");
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  // ===== Autocommit Tests End =====

  /**
   * Assert utility function for constraints. It asserts that result contains
   * the specified number of rows, and for each row the primary key table
   * name and foreign key table name matches the expected input.
   * @param resultSet
   * @param numRows
   * @param pkTableName
   * @param fkTableName
   */
  private void assertConstraintResults(
      ResultSet resultSet,
      int numRows,
      int numCols,
      String pkTableName,
      String fkTableName)
    throws IOException, SQLException
  {
      ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == numCols);

      // primary key for testConstraintsP1 should contain two rows
      for (int i = 0; i < numRows; i++)
      {
        assertTrue("get constraint result row count", resultSet.next());

        if (pkTableName != null)
          assertTrue("get constraint result primary table name",
                     pkTableName.equalsIgnoreCase(resultSet.getString(3)));

        if (fkTableName != null)
          assertTrue("get constraint result foreign table name",
              fkTableName.equalsIgnoreCase(resultSet.getString(7)));
      }
  }

  @Test
  public void testBoolean() throws IOException, SQLException
  {
    outputLine("running testBoolean");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;
    PreparedStatement preparedStatement = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();
      DatabaseMetaData metadata = connection.getMetaData();

      statement.execute("alter session set enable_boolean_datatype = true");

      // Create a table with boolean columns
      statement.execute("create or replace table testBooleanT1(c1 boolean)");

      // Insert values into the table
      statement.execute("insert into testBooleanT1 values(true), (false), " +
                            "(null)");

      // Get values from the table
      preparedStatement = connection.prepareStatement("select c1 from " +
                                                     "testBooleanT1");

      // I. Test ResultSetMetaData interface
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // Verify the column type is Boolean
      assertTrue(resultSetMetaData.getColumnType(1) == Types.BOOLEAN);

      // II. Test DatabaseMetadata interface
      ResultSet columnMetaDataResultSet =
          metadata.getColumns(null, null, "TESTBOOLEANT1", null);

      resultSetMetaData = columnMetaDataResultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 24);

      assertTrue(columnMetaDataResultSet.next());
      assertTrue(columnMetaDataResultSet.getInt(5) == Types.BOOLEAN);

      succs.incrementAndGet();
      outputLine("testBoolean: success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testBoolean : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally // cleanup
    {
      // drop the table
      statement.execute("drop table testBooleanT1");

      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testConstraints() throws IOException, SQLException
  {
    outputLine("running testConstraints");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();
      DatabaseMetaData metadata = connection.getMetaData();

      // Turn on the constraint related features
      statement.execute(
          "alter session set enable_key_constraints = true");

      statement.execute(
          "alter session set allow_unsupported_constraint_properties = true");

      // Create primary key tables
      statement.execute(
          "CREATE OR REPLACE TABLE testConstraintsP1(c1 number unique, c2 " +
              "number, constraint cons0 primary key (c1, c2))");

      statement.execute(
          "CREATE OR REPLACE TABLE testConstraintsP2(c1 number " +
              "constraint cons1 primary key, c2 number)");

      // Create foreign key tables
      statement.execute(
          "CREATE OR REPLACE TABLE testConstraintsF1(c1 number, c2 number, " +
              "constraint cons3 foreign key (c1, c2) references " +
              "testConstraintsP1(c1, c2))");

      statement.execute(
          "CREATE OR REPLACE TABLE testConstraintsF2(c1 number, c2 number, " +
              "constraint cons4 foreign key (c1, c2) references " +
              "testConstraintsP1(c1, c2), constraint cons5 " +
              "foreign key (c2) references testConstraintsP2(c1))");

      // show primary keys
      resultSet = metadata.getPrimaryKeys(null, null, "TESTCONSTRAINTSP1");

      // primary key for testConstraintsP1 should contain two rows
      assertConstraintResults(resultSet, 2, 6, "testConstraintsP1", null);

      resultSet = metadata.getPrimaryKeys(null, null, "TESTCONSTRAINTSP2");

      // primary key for testConstraintsP2 contains 1 row
      assertConstraintResults(resultSet, 1, 6, "testConstraintsP2", null);

      // Show imported keys
      resultSet = metadata.getImportedKeys(null, null, "TESTCONSTRAINTSF1");

      assertConstraintResults(resultSet, 2, 14, null, "testConstraintsF1");

      resultSet = metadata.getImportedKeys(null, null, "TESTCONSTRAINTSF2");

      assertConstraintResults(resultSet, 3, 14, null, "testConstraintsF2");

      // show exported keys
      resultSet = metadata.getExportedKeys(null, null, "TESTCONSTRAINTSP1");

      assertConstraintResults(resultSet, 4, 14, "testConstraintsP1", null);

      resultSet = metadata.getExportedKeys(null, null, "TESTCONSTRAINTSP2");

      assertConstraintResults(resultSet, 1, 14, "testConstraintsP2", null);

      // show cross references
      resultSet = metadata.getCrossReference(
          null, null, "TESTCONSTRAINTSP1", null, null, "TESTCONSTRAINTSF1");

      assertConstraintResults(resultSet, 2, 14, "testConstraintsP1",
                              "testConstraintsF1");

      resultSet = metadata.getCrossReference(
          null, null, "TESTCONSTRAINTSP2", null, null, "TESTCONSTRAINTSF2");

      assertConstraintResults(resultSet, 1, 14, "testConstraintsP2",
                              "testConstraintsF2");

      resultSet = metadata.getCrossReference(
          null, null, "TESTCONSTRAINTSP1", null, null, "TESTCONSTRAINTSF2");

      assertConstraintResults(resultSet, 2, 14, "testConstraintsP1",
                              "testConstraintsF2");

      resultSet = metadata.getCrossReference(
          null, null, "TESTCONSTRAINTSP2", null, null, "TESTCONSTRAINTSF1");

      assertFalse("cross reference from testConstraintsP2 to " +
                     "testConstraintsF2 should be empty",
                  resultSet.next());
      succs.incrementAndGet();
      outputLine("testConstraints : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testConstraints : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally // cleanup
    {
      // drop the tables
      statement.execute("DROP TABLE TESTCONSTRAINTSF1");
      statement.execute("DROP TABLE TESTCONSTRAINTSF2");
      statement.execute("DROP TABLE TESTCONSTRAINTSP1");
      statement.execute("DROP TABLE TESTCONSTRAINTSP2");

      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testQueries() throws IOException, SQLException
  {
    outputLine("running testQueries");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      statement.setMaxRows(30);

      // execute query
      resultSet = statement.executeQuery("SELECT * FROM orders_jdbc");
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 9);

      // assert we get 28 rows
      for(int i = 0; i < 30; i++)
      {
        assertTrue(resultSet.next());

        // assert each column is not null except the last one
        for(int j = 1; j < 10; j++)
        {
          assertTrue(resultSet.getString(j) != null);
        }
      }

      // max rows should limit the number of rows to 30
      assertTrue(!resultSet.next());

      statement.close();

      succs.incrementAndGet();
      outputLine("testQueries : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testQueries : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testCancelQuery() throws IOException, SQLException
  {
    outputLine("running testCancelQuery");
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    final Connection connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                                !USE_V1_QUERY_API,
                                                DONT_INJECT_SOCKET_TIMEOUT);

    try
    {
      // Get the current session identifier
      Statement getSessionIdStmt = connection.createStatement();
      getSessionIdStmt.setMaxRows(30);
      resultSet = getSessionIdStmt.executeQuery("SELECT current_session()");
      resultSetMetaData = resultSet.getMetaData();
      assertTrue(resultSet.next());
      final long sessionId = resultSet.getLong(1);

      outputLine("Cancel all queries of session in 5s");
      Timer timer = new Timer();
      timer.schedule( new TimerTask()
      {
        @Override
        public void run()
        {
          try
          {
            outputLine("Cancel all queries on session ");

            PreparedStatement cancelAll;
            cancelAll = connection.prepareStatement(
                    "call system$cancel_all_queries(?)");

            // bind integer
            cancelAll.setLong(1, sessionId);
            cancelAll.executeQuery();
          }
          catch (SQLException ex)
          {
            logger.log(Level.SEVERE, "Cancel failed with exception {}", ex);
          }
        }
      }, 5000);

      // execute a query for 120s
      statement = connection.createStatement();
      statement.setMaxRows(30);

      resultSet = statement.executeQuery(
              "SELECT count(*) FROM TABLE(generator(timeLimit => 120))");

      diffs.incrementAndGet();
      outputLine("testCancelQuery : difference");
    }
    catch (SQLException ex)
    {
      // assert the sqlstate is what we expect (QUERY CANCELLED)
      assertEquals("sqlstate mismatch",
                   SqlState.QUERY_CANCELED, ex.getSQLState());

      outputLine("testCancelQuery : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testCancelQuery : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  private static void sql(final Connection connection, String sqlText)
          throws IOException, SQLException
  {
    // Create a warehouse for the test
    Statement stmt = connection.createStatement();
    stmt.setMaxRows(1);
    boolean hasResultSet = stmt.execute(sqlText);
    if (hasResultSet)
      assertTrue(stmt.getResultSet().next());
    stmt.close();
  }

  @Test
  public void testWarehouseQuiescing() throws IOException, SQLException
  {
    outputLine("running testWarehouseQuiescing");
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;
    final Connection connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                                !USE_V1_QUERY_API,
                                                DONT_INJECT_SOCKET_TIMEOUT);

    try
    {
      // Create a warehouse for the test
      sql(connection,
          "create or replace warehouse snow001 warehouse_size=xsmall");

      sql(connection,
          "use warehouse snow001");

      outputLine("Quiesce warehouse snow001 in 5s");
      Timer timer = new Timer();
      timer.schedule( new TimerTask()
      {
        @Override
        public void run()
        {
          try
          {
            outputLine("Quiesce warehouse snow001");
            sql(connection,
                "alter warehouse snow001 suspend");
          }
          catch (IOException | SQLException ex)
          {
            logger.log(Level.SEVERE, "quiesce failed with exception {}", ex);
          }
        }
      }, 5000);

      // execute a query for 120s
      statement = connection.createStatement();
      statement.setMaxRows(30);

      resultSet = statement.executeQuery(
              "SELECT count(*) FROM TABLE(generator(timeLimit => 10))");
      resultSetMetaData = resultSet.getMetaData();
      assertTrue(resultSet.next());

      statement.close();

      sql(connection,
          "drop warehouse snow001");

      succs.incrementAndGet();
      outputLine("testWarehouseQuiescing : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testWarehouseQuiescing : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testDBMetadata() throws IOException, SQLException
  {
    outputLine("running testDBMetadata");
    Connection connection = null;
    Statement statement = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      // get database metadata
      DatabaseMetaData metaData = connection.getMetaData();

      // the following will issue
      // "show databases"
      ResultSet databaseSet = metaData.getCatalogs();
      assertTrue("databases shouldn't be empty", databaseSet.next());

      // get schemas for testdb, the following will issue
      // "show schemas in [databaseName]"
      ResultSet schemaSet = metaData.getSchemas("TESTDB", "TESTSCHEMA");
      assertTrue("schemas shouldn't be empty", schemaSet.next());
      assertTrue("database should be testdb",
          "testdb".equalsIgnoreCase(schemaSet.getString(2)));
      assertTrue("schema should be testschema",
          "testschema".equalsIgnoreCase(schemaSet.getString(1)));

      // get tables under testdb.testschema, the following will issue
      // "show tables in testdb.testschema"
      ResultSet tableSet = metaData.getTables("TESTDB",
          "TESTSCHEMA", "ORDERS_JDBC", null);
      assertTrue("tables shouldn't be empty", tableSet.next());
      assertTrue("database should be testdb",
          "testdb".equalsIgnoreCase(tableSet.getString(1)));
      assertTrue("schema should be testschema",
          "testschema".equalsIgnoreCase(tableSet.getString(2)));
      assertTrue("table should be orders_jdbc",
          "orders_jdbc".equalsIgnoreCase(tableSet.getString(3)));

      ResultSet tableMetaDataResultSet = metaData.getTables(null,
                                                       null,
                                                       "ORDERS_JDBC",
                                                       null);

      resultSetMetaData = tableMetaDataResultSet.getMetaData();

       // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 10);

      // assert we get 1 rows
      for(int i = 0; i < 1; i++)
      {
        assertTrue(tableMetaDataResultSet.next());

        // assert the table name
        assertTrue("orders_jdbc".equalsIgnoreCase(
                tableMetaDataResultSet.getString(3)));
      }

      tableMetaDataResultSet.close();

      // test pattern
      tableMetaDataResultSet = metaData.getTables(null,
                                                  null,
                                                  "%",
                                                  null);

      resultSetMetaData = tableMetaDataResultSet.getMetaData();

       // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 10);

      boolean found = false;

      // assert we get orders_jdbc
      while(tableMetaDataResultSet.next())
      {
        // assert the table name
        if ("orders_jdbc".equalsIgnoreCase(
                tableMetaDataResultSet.getString(3)))
          found = true;
      }

      assertTrue(found);

      tableMetaDataResultSet.close();

      // get column metadata
      ResultSet columnMetaDataResultSet = metaData.getColumns(null, null,
                                                              "ORDERS_JDBC",
                                                              null);

      resultSetMetaData = columnMetaDataResultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 24);

      // assert we get 9 rows
      for(int i = 0; i < 9; i++)
      {
        assertTrue(columnMetaDataResultSet.next());

          // SNOW-16881: assert database name
          assertTrue("testdb".equalsIgnoreCase(
                  columnMetaDataResultSet.getString(1)));

        // assert the table name and column name, data type and type name
        assertTrue("orders_jdbc".equalsIgnoreCase(
                    columnMetaDataResultSet.getString(3)));

        assertTrue(columnMetaDataResultSet.getString(4).startsWith("C"));

        assertTrue(columnMetaDataResultSet.getInt(5) == Types.VARCHAR);

        assertTrue("VARCHAR".equalsIgnoreCase(
                    columnMetaDataResultSet.getString(6)));

        if (i == 0)
        {
          // assert comment
          assertTrue("JDBC".equals(columnMetaDataResultSet.getString(12)));

          // assert nullable
          assertTrue(columnMetaDataResultSet.getInt(11) ==
              DatabaseMetaData.columnNoNulls);

          // assert is_nullable
          assertTrue("NO".equals(columnMetaDataResultSet.getString(18)));
        }
      }

      columnMetaDataResultSet.close();

      // create a table with mix cases
      statement = connection.createStatement();
      statement.execute("create or replace table \"testDBMetadata\" (a timestamp_ltz)");
      columnMetaDataResultSet = metaData.getColumns(null, null,
          "testDBMetadata", null);

      // assert we get 1 row
      for(int i = 0; i < 1; i++)
      {
        assertTrue(columnMetaDataResultSet.next());

        // assert the table name and column name, data type and type name
        assertTrue("testDBMetadata".equalsIgnoreCase(
            columnMetaDataResultSet.getString(3)));

        assertTrue(columnMetaDataResultSet.getInt(5) == Types.TIMESTAMP);

        assertTrue(columnMetaDataResultSet.getString(4).equalsIgnoreCase("a"));
      }

      succs.incrementAndGet();
      outputLine("testDBMetadata : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testDBMetadata : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      statement.execute("DROP TABLE IF EXISTS \"testDBMetadata\"");
      statement.close();

      // close connection
      if (connection != null)
      {
        connection.close();
      }
    }
  }

  @Test
  public void testLoadToLocalFS() throws IOException, SQLException
  {
    outputLine("running testLoadToLocalFS");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // load file test
      // create a unique data file name by using current timestamp in millis
      try
      {
        File srcDir = null;

        // test external table load
        statement.execute("CREATE OR REPLACE TABLE testLoadToLocalFS(a number)");

        String sfProjectRoot = getSFProjectRoot();

        // put files
        boolean status =
        statement.execute("PUT file://" + sfProjectRoot +
                          "/ExecPlatform/Database/data/orders_100.csv "
                          + "@%testLoadToLocalFS/orders parallel=10");

        assertTrue("put command return resutls", status);

        resultSet = statement.getResultSet();

        resultSetMetaData = resultSet.getMetaData();

        // assert column count
        assertTrue(resultSetMetaData.getColumnCount() > 0);

        // assert we get 1 rows
        for(int i = 0; i < 1; i++)
        {
          assertTrue(resultSet.next());
        }

        statement.close();

        Thread.sleep(100);

        // show files
        resultSet = statement.executeQuery(
        "ls @%testLoadToLocalFS/ pattern='.*orders/orders_100.csv.g.*' ");

        resultSetMetaData = resultSet.getMetaData();

        // assert we get 1 row for the file we copied
        assertTrue(resultSet.next());
        assertTrue(resultSet.getString(1) != null);

        statement.close();

        // remove files
        resultSet = statement.executeQuery(
        "rm @%testLoadToLocalFS/ pattern='.*orders/orders_100.csv.g.*'");

        resultSetMetaData = resultSet.getMetaData();

        // assert column count
        assertTrue(resultSetMetaData.getColumnCount() >= 1);

        // assert we get 1 row for the file we copied
        assertTrue(resultSet.next());
        assertTrue(resultSet.getString(1) != null);

        statement.close();

        Thread.sleep(100);

        // show files again
        resultSet = statement.executeQuery(
        "ls @%testLoadToLocalFS/ pattern='.*orders/orders.*' ");

        // assert we get 0 row
        assertTrue(!resultSet.next());

        statement.close();
      }
      finally
      {
        statement.execute("DROP TABLE IF EXISTS testLoadToLocalFS");
        statement.close();
      }

      succs.incrementAndGet();
      outputLine("testLoadToLocalFS : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testLoadToLocalFS : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testLoadToS3() throws IOException, SQLException
  {
    outputLine("running testLoadToS3");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      try
      {
        // test external table load
        statement.execute("CREATE OR REPLACE TABLE testLoadToS3(a number)");

        String sfProjectRoot = getSFProjectRoot();

        // put files
        boolean status =
        statement.execute("PUT file://" + sfProjectRoot +
                          "/ExecPlatform/Database/data/orders_100.csv "
                          + "@%testLoadToS3/orders parallel=10");

        boolean fileFound = false;

        // tolerate at most 60 tries for the following loop
        for(int numSecs = 0; numSecs <= 60; numSecs++)
        {
          // give enough time for s3 eventual consistency for US region
          Thread.sleep(1000);

          // show files
          resultSet = statement.executeQuery(
          "ls @%testLoadToS3/ pattern='.*orders/orders_100.csv.g.*' ");

          if (resultSet.next())
          {
            fileFound = true;
            break;
          }
          statement.close();
        }

        assertTrue(fileFound);

        // assert the first column not null
        assertTrue(resultSet.getString(1) != null);

        statement.close();

        // put again to verify same file not being uploaded
        status = statement.execute("PUT file://" + sfProjectRoot +
                                   "/ExecPlatform/Database/data/orders_100.csv "
                                   + "@%testLoadToS3/orders parallel=10");

        assertTrue("put command return resutls", status);

        resultSet = statement.getResultSet();

        resultSetMetaData = resultSet.getMetaData();

        // assert column count
        assertTrue(resultSetMetaData.getColumnCount() == 8);

        // assert we get at least 1 rows
        for(int i = 0; i < 1; i++)
        {
          assertTrue(resultSet.next());

          // skipping same file processing is only done for s3test account
          if ("s3testaccount".equalsIgnoreCase(System.getenv("SF_ACCOUNT")))
          {
            // assert that the 7th column has a
            assertTrue("SKIPPED".equals(resultSet.getString(7)));
          }
        }

        statement.close();

        // remove files
        resultSet = statement.executeQuery(
        "rm @%testLoadToS3/ pattern='.*orders/orders_100.csv.g.*'");

        resultSetMetaData = resultSet.getMetaData();

        // assert column count
        assertTrue(resultSetMetaData.getColumnCount() >= 1);

        // assert we get 1 row for the file we copied
        assertTrue(resultSet.next());
        assertTrue(resultSet.getString(1) != null);

        statement.close();

        Thread.sleep(100);

        boolean fileRemoved = false;

        // tolerate at most 60 tries for the following loop
        for(int numSecs = 0; numSecs <= 60; numSecs++)
        {
          // give enough time for s3 eventual consistency for US region
          Thread.sleep(1000);

          // show files
          resultSet = statement.executeQuery(
          "ls @%testLoadToS3/ pattern='.*orders/orders_100.csv.g.*'");

          if (!resultSet.next())
          {
            fileRemoved = true;
            break;
          }
          statement.close();
        }

        assertTrue(fileRemoved);

        statement.close();
      }
      finally
      {
        statement.execute("DROP TABLE IF EXISTS testLoadToS3");
        statement.close();
      }

      succs.incrementAndGet();
      outputLine("testLoadToS3 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testLoadToS3 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testSQLErrors() throws IOException, SQLException
  {
    outputLine("running testSQLErrors");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // test exception
      // execute a bad query
      try
      {
        resultSet = statement.executeQuery("SELECT * FROM nonexists");

        // fail
        assertTrue("SQL exception not raised", false);
      }
      catch(SQLException ex1)
      {
        statement.close();
        // assert the sqlstate "02000" which means no_data
        assertEquals("sqlstate mismatch", "02000", ex1.getSQLState());
      }
     succs.incrementAndGet();
      outputLine("testSQLErrors : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSQLErrors : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testExplainPlan() throws IOException, SQLException
  {
    outputLine("running testExplainPlan");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // test explain plan
      resultSet = statement.executeQuery(
      "EXPLAIN PLAN FOR SELECT c1 FROM orders_jdbc");

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() >= 4);

      resultSetMetaData.getColumnType(1);

      // assert we get at least 3 rows
      for(int i = 0; i < 3; i++)
      {
        assertTrue(resultSet.next());
      }

      statement.close();

      succs.incrementAndGet();
      outputLine("testExplainPlan : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testExplainPlan : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testTimestampParsing() throws IOException, SQLException
  {
    outputLine("running testTimestampParsing");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // test timestamp
      resultSet = statement.executeQuery(
      "select to_timestamp('2013-05-08T15:39:20.123-07:00') from orders_jdbc");

      assertTrue(resultSet.next());

      assertTrue("Wed, 08 May 2013 15:39:20 -0700".equals(resultSet.getString(1)));

      statement.close();

      succs.incrementAndGet();
      outputLine("testTimestampParsing : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testTimestampParsing : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testDateParsing() throws IOException, SQLException
  {
    outputLine("running testDateParsing");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // test date
      resultSet = statement.executeQuery(
      "select to_date('0001-01-01')");

      assertTrue(resultSet.next());

      assertTrue("0001-01-01".equals(resultSet.getString(1)));

      statement.close();

      succs.incrementAndGet();
      outputLine("testDateParsing : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testDateParsing : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testTimeParsing() throws IOException, SQLException
  {
    outputLine("running testTimeParsing");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // test time
      resultSet = statement.executeQuery(
      "select to_time('15:39:20.123') from orders_jdbc");

      assertTrue(resultSet.next());

      assertTrue("15:39:20".equals(resultSet.getString(1)));

      statement.close();

      succs.incrementAndGet();
      outputLine("testTimeParsing : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testTimeParsing : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testClientSideSorting() throws IOException, SQLException
  {
    outputLine("running testClientSideSorting");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // turn on sorting mode
      statement.execute("set-sf-property sort on");
      statement.close();

      resultSet = statement.executeQuery("SELECT c3 FROM orders_jdbc");

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert the values for the first 5 rows
      for(int i = 0; i < 5; i++)
      {
        assertTrue(resultSet.next());

        // assert each column is 'F'
        assertTrue("F".equals(resultSet.getString(1)));
      }

      statement.close();

     // turn off sorting mode
      statement.execute("set-sf-property sort off");
      statement.close();

      resultSet = statement.executeQuery(
          "SELECT c3 FROM orders_jdbc order by c3 desc");

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert the values for the first 4 rows
      for(int i = 0; i < 4; i++)
      {
        assertTrue(resultSet.next());

        // assert each column is 'P'
        assertTrue("P".equals(resultSet.getString(1)));
      }

      statement.close();

      succs.incrementAndGet();
      outputLine("testClientSideSorting : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testClientSideSorting : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testUpdateCount() throws IOException, SQLException
  {
    outputLine("running testUpdateCount");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // create test table
      statement.executeUpdate(
          "CREATE OR REPLACE TABLE testUpdateCount(version number, name string)");
      statement.close();

      // insert two rows
      int numRows =
      statement.executeUpdate(
          "INSERT INTO testUpdateCount values (1, 'a'), (2, 'b')");

      assertTrue("Unexpected number of rows inserted: " + numRows, numRows == 2);

      succs.incrementAndGet();
      outputLine("testUpdateCount : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testUpdateCount : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.executeUpdate("DROP TABLE if exists testUpdateCount");
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }


  @Test
  public void testSnow4245() throws IOException, SQLException
  {
    outputLine("running testSnow4245");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // set timestamp format
      statement.executeUpdate(
          "alter session set timestamp_input_format = 'YYYY-MM-DD HH24:MI:SS';");
      statement.close();

      // create test table with different time zone flavors
      String createSQL=
          "create or replace table testSnow4245(t timestamp with local time " +
              "zone,ntz timestamp without time zone,tz  timestamp with time zone)";
      statement.executeUpdate(createSQL);
      statement.close();

      // populate
      int numRows =
      statement.executeUpdate("insert into testSnow4245 values(NULL,NULL,NULL)," +
          "('2013-06-04 01:00:04','2013-06-04 01:00:04','2013-06-04 01:00:04')," +
          "('2013-06-05 23:00:05','2013-06-05 23:00:05','2013-06-05 23:00:05')");
      assertTrue("Unexpected number of rows inserted: " + numRows, numRows == 3);

      // query the data
      resultSet = statement.executeQuery("SELECT * FROM testSnow4245 order by 1 " +
          "nulls first, 2 nulls first, 3 nulls first");

      int i = 0;
      // assert we get 3 rows

      while (resultSet.next() )
      {
         // assert each column is not null except the first row

	if (i == 0)
	{
	    for(int j = 1; j < 4; j++)
	   {
	       assertTrue(resultSet.getString(j),resultSet.getString(j) == null);
	   }
        } else {
	   for(int j = 1; j < 4; j++)
	   {
	       assertTrue(resultSet.getString(j),resultSet.getString(j) != null);
	   }
       }
	i=i+1;
      }
      statement.close();
      succs.incrementAndGet();
      outputLine("testSnow4245 : success");

    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow4245 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      statement.executeUpdate("drop table testSnow4245;");
      statement.close();
      closeSQLObjects(resultSet, statement, connection);
      connection.close();
    }
  }

  /**
   * SNOW-4394 - Four bytes UTF-8 characters are not returned correctly.
   *
   */
  @Test
  public void testSnow4394() throws IOException, SQLException
  {
    outputLine("running testSnow4394");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    String tableName =
        String.format("snow4394_%s", UUID.randomUUID().toString()).
            replaceAll("-", "_");

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // create test table
      statement.executeUpdate(
          String.format("CREATE OR REPLACE TABLE %s(str string)", tableName));
      statement.close();

      String data = "What is \ud83d\ude12?";
      // insert two rows
      int numRows =
      statement.executeUpdate(
          String.format("INSERT INTO %s(str) values('%s')", tableName, data));
      assertEquals("Unexpected number of rows inserted: " + numRows, 1, numRows);
      statement.close();

      ResultSet rset = statement.executeQuery(
          String.format("SELECT str FROM %s", tableName));
      String ret = null;
      while (rset.next()) {
        ret = rset.getString(1);
      }
      rset.close();
      statement.close();
      assertEquals("Unexpected string value: " + ret, data, ret);

      succs.incrementAndGet();
      outputLine("testSnow4394 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow4394 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.executeUpdate(
            String.format("DROP TABLE if exists %s", tableName));
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testUploadStream() throws IOException, SQLException
  {
    outputLine("running testUploadStream");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
          !USE_V1_QUERY_API,
          DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      FileBackedOutputStream outputStream = new FileBackedOutputStream(1000000);
      CountingOutputStream countingStream =
          new CountingOutputStream(outputStream);
      countingStream.write("hello".getBytes(Charset.forName("UTF-8")));
      countingStream.flush();


      // upload the data to user stage under testUploadStream with name hello.txt
      ((SnowflakeConnectionV1)connection).uploadStream("~", "testUploadStream",
          outputStream.asByteSource().openStream(), "hello.txt",
          countingStream.getCount());

      // select from the file to make sure the data is uploaded
      ResultSet rset = statement.executeQuery(
          "SELECT $1 FROM @~/testUploadStream");

      String ret = null;

      while (rset.next()) {
        ret = rset.getString(1);
      }
      rset.close();
      statement.close();
      assertEquals("Unexpected string value: " + ret + " expect: hello",
          "hello", ret);

      succs.incrementAndGet();
      outputLine("testUploadStream : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testUploadStream : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.execute("rm @~/testUploadStream");
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testCompressAndUploadStream() throws IOException, SQLException
  {
    outputLine("running testCompressAndUploadStream");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
          !USE_V1_QUERY_API,
          DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      FileBackedOutputStream outputStream = new FileBackedOutputStream(1000000);
      CountingOutputStream countingStream =
          new CountingOutputStream(outputStream);
      countingStream.write("hello".getBytes(Charset.forName("UTF-8")));
      countingStream.flush();


      // upload the data to user stage under testCompressAndUploadStream
      // with name hello.txt
      ((SnowflakeConnectionV1)connection).compressAndUploadStream(
          "~", "testCompressAndUploadStream",
          outputStream.asByteSource().openStream(), "hello.txt");

      // select from the file to make sure the data is uploaded
      ResultSet rset = statement.executeQuery(
          "SELECT $1 FROM @~/testCompressAndUploadStream");

      String ret = null;

      while (rset.next()) {
        ret = rset.getString(1);
      }
      rset.close();
      statement.close();
      assertEquals("Unexpected string value: " + ret + " expect: hello",
          "hello", ret);

      succs.incrementAndGet();
      outputLine("testCompressAndUploadStream : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testCompressAndUploadStream : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.execute("rm @~/testCompressAndUploadStream");
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  private void addBindBatch(PreparedStatement preparedStatement,
                            java.sql.Date sqlDate) throws SQLException
  {
    preparedStatement.setDouble(1, 1.2);
    preparedStatement.setString(2, "hello");
    preparedStatement.setDate(3, sqlDate);
    preparedStatement.setDate(4, sqlDate);
    preparedStatement.setString(5, "h");
    preparedStatement.setDate(6, sqlDate);
    preparedStatement.setString(7, "h");
    preparedStatement.setString(8, "h");
    preparedStatement.setString(9, "h");
    preparedStatement.setString(10, "h");
    preparedStatement.setString(11, "h");
    preparedStatement.setDate(12, sqlDate);
    preparedStatement.setString(13, "h");
    preparedStatement.setDouble(14, 1.2);
    preparedStatement.setString(15, "h");
    preparedStatement.setString(16, "h");
    preparedStatement.setString(17, "h");
    preparedStatement.setString(18, "h");
    preparedStatement.setString(19, "h");
    preparedStatement.setDate(20, sqlDate);
    preparedStatement.setString(21, "h");
    preparedStatement.addBatch();
  }

  @Test
  public void testBind() throws IOException, SQLException
  {
    outputLine("running testBind");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      preparedStatement = connection.prepareStatement("SELECT ?, ?");

      // bind integer
      preparedStatement.setInt(1, 1);
      preparedStatement.setString(2, "hello");
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertEquals("integer", 1, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));

      // bind float
      preparedStatement.setDouble(1, 1.2);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DOUBLE);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("double", 1.2, resultSet.getDouble(1), 0);
      assertEquals("string", "hello", resultSet.getString(2));

      // bind string
      preparedStatement.setString(1, "hello");
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.VARCHAR);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("string1", "hello", resultSet.getString(1));
      assertEquals("string2", "hello", resultSet.getString(2));

      // bind date
      java.sql.Date sqlDate = java.sql.Date.valueOf("2014-08-26");
      preparedStatement.setDate(1, sqlDate);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DATE);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("string", "2014-08-26", resultSet.getString(1));
      assertEquals("string", "hello", resultSet.getString(2));

      // bind timestamp
      Timestamp ts = new Timestamp(Date.UTC(114, 7, 26, 3, 52, 0));
      preparedStatement.setTimestamp(1, ts);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.TIMESTAMP);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("Incorrect timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(1));
      assertEquals("string", "hello", resultSet.getString(2));

      // bind time
      Time tm = new Time(12345678); // 03:25:45.678
      preparedStatement.setTime(1, tm);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.TIME);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("Incorrect time", "03:25:45",
          resultSet.getString(1));
      assertEquals("string", "hello", resultSet.getString(2));


      preparedStatement.close();

      // bind in where clause
      preparedStatement = connection.prepareStatement(
          "SELECT * FROM orders_jdbc WHERE to_number(c1) = ?");

      preparedStatement.setInt(1, 100);
      resultSet = preparedStatement.executeQuery();
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 9);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.VARCHAR);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("c1", "100", resultSet.getString(1));
      assertEquals("c2", "147004", resultSet.getString(2));

      preparedStatement.close();

      // bind in insert statement
      // create a test table
      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testBind(a int, b string, c double, d date, " +
              "e timestamp, f time, g date)");
      regularStatement.close();

      preparedStatement = connection.prepareStatement(
          "insert into testBind(a, b, c, d, e, f) values(?, ?, ?, ?, ?, ?)");

      preparedStatement.setInt(1, 1);
      preparedStatement.setString(2, "hello");
      preparedStatement.setDouble(3, 1.2);
      preparedStatement.setDate(4, sqlDate);
      preparedStatement.setTimestamp(5, ts);
      preparedStatement.setTime(6, tm);
      int updateCount = preparedStatement.executeUpdate();

      // update count should be 1
      assertEquals("update count", 1, updateCount);

      // test the inserted rows
      resultSet = regularStatement.executeQuery("select * from testBind");

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("int", 1, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));
      assertEquals("double", 1.2, resultSet.getDouble(3), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(4));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
                   resultSet.getString(5));
      assertEquals("time", "03:25:45", resultSet.getString(6));
      assertEquals("date", null, resultSet.getString(7));

      // bind in update statement
      preparedStatement = connection.prepareStatement(
          "update testBind set b=? where a=?");

      preparedStatement.setString(1, "world");
      preparedStatement.setInt(2, 1);
      preparedStatement.execute();

      preparedStatement.close();

      // test the updated rows
      resultSet = regularStatement.executeQuery("select * from testBind");

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("int", 1, resultSet.getInt(1));
      assertEquals("string", "world", resultSet.getString(2));
      assertEquals("double", 1.2, resultSet.getDouble(3), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(4));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(5));
      assertEquals("time", "03:25:45", resultSet.getString(6));
      assertEquals("date", null, resultSet.getString(7));

      regularStatement.close();

      // array bind for insert
      preparedStatement = connection.prepareStatement(
          "insert into testBind (a, b, c, d, e, f, g) " +
              "values(?, ?, ?, ?, ?, ?, current_date())");

      preparedStatement.setInt(1, 2);
      preparedStatement.setString(2, "hello");
      preparedStatement.setDouble(3, 1.2);
      preparedStatement.setDate(4, sqlDate);
      preparedStatement.setTimestamp(5, ts);
      preparedStatement.setTime(6, tm);
      preparedStatement.addBatch();

      preparedStatement.setInt(1, 3);
      preparedStatement.setString(2, "hello");
      preparedStatement.setDouble(3, 1.2);
      preparedStatement.setDate(4, sqlDate);
      preparedStatement.setTimestamp(5, ts);
      preparedStatement.setTime(6, tm);
      preparedStatement.addBatch();

      int[] updateCounts = preparedStatement.executeBatch();

      // GS optimizes this into one insert execution, but we expand the
      // return count into an array
      assertEquals("Number of update counts", 2, updateCounts.length);

      // update count should be 1 for each
      assertEquals("update count", 1, updateCounts[0]);
      assertEquals("update count", 1, updateCounts[1]);

      // test the inserted rows
      resultSet = regularStatement.executeQuery(
          "select * from testBind where a = 2");

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("int", 2, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));
      assertEquals("double", 1.2, resultSet.getDouble(3), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(4));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(5));
      assertEquals("time", "03:25:45", resultSet.getString(6));
      System.out.println("7th column value is current date: " +
                         resultSet.getString(7));
      regularStatement.close();

      resultSet = regularStatement.executeQuery(
          "select * from testBind where a = 3");

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("int", 3, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));
      assertEquals("double", 1.2, resultSet.getDouble(3), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(4));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(5));
      assertEquals("time", "03:25:45", resultSet.getString(6));
      System.out.println("7th column value is current date: " +
                         resultSet.getString(7));

      regularStatement.close();

      // describe mode
      preparedStatement = connection.prepareStatement(
          "select * from testBind WHERE to_number(a) = ?");

      resultSetMetaData = preparedStatement.getMetaData();
      assertTrue(resultSetMetaData.getColumnCount() == 7);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);
      assertTrue(resultSetMetaData.getColumnType(3) == Types.DOUBLE);
      assertTrue(resultSetMetaData.getColumnType(4) == Types.DATE);
      assertTrue(resultSetMetaData.getColumnType(5) == Types.TIMESTAMP);
      assertTrue(resultSetMetaData.getColumnType(6) == Types.TIME);
      assertTrue(resultSetMetaData.getColumnType(7) == Types.DATE);

      preparedStatement.close();
      preparedStatement = connection.prepareStatement(
          "select ?, ?");

      resultSetMetaData = preparedStatement.getMetaData();
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.VARCHAR);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);

      preparedStatement.close();
      preparedStatement = connection.prepareStatement(
          "select ?, ?");

      preparedStatement.setInt(1, 1);
      preparedStatement.setString(2, "hello");

      resultSetMetaData = preparedStatement.getMetaData();
      assertTrue(resultSetMetaData.getColumnCount() == 2);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);

      preparedStatement.close();

      // test null binding
      preparedStatement = connection.prepareStatement(
          "select ?");

      preparedStatement.setNull(1, Types.VARCHAR);
      resultSet = preparedStatement.executeQuery();
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.VARCHAR);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertTrue(resultSet.getObject(1) == null);

      preparedStatement.setNull(1, Types.INTEGER);
      resultSet = preparedStatement.executeQuery();
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertTrue(resultSet.getObject(1) == null);

      preparedStatement.close();

      // test unsupported type
      preparedStatement = connection.prepareStatement(
          "select ?");

      boolean gotException = false;
      try
      {
        preparedStatement.setNull(1, Types.SQLXML);
      }
      catch(SnowflakeSQLException ex)
      {
        if (ex.getErrorCode() ==
            ErrorCode.DATA_TYPE_NOT_SUPPORTED.getMessageCode())
          gotException = true;
        else throw ex;
      }

      assertTrue(gotException);

      preparedStatement.close();

      // bind in insert statement
      // create a test table
      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testBind1(c1 double, c2 string, c3 date, "
                  + "c4 date, c5 string, c6 date, c7 string, c8 string, "
                  + "c9 string, c10 string, c11 string, c12 date, c13 string, "
                  + "c14 float, c15 string, c16 string, c17 string, c18 string,"
                  + "c19 string, c20 date, c21 string)");

      regularStatement.close();

      // array bind for insert
      preparedStatement = connection.prepareStatement(
          "insert into testBind1 (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, "
                  + "c12, c13, c14, c15, c16, c17, c18, c19, c20, c21) values "
                  + "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
                  + " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

      for (int idx = 0; idx < 16; idx++)
        addBindBatch(preparedStatement, sqlDate);

      updateCounts = preparedStatement.executeBatch();

      // GS optimizes this into one insert execution
      assertEquals("Number of update counts", 16, updateCounts.length);

      for (int idx = 0; idx < 16; idx++)
        assertEquals("update count", 1, updateCounts[idx]);

      succs.incrementAndGet();
      outputLine("testBind : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testBind : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testBind");
        regularStatement.executeUpdate("DROP TABLE testBind1");
        regularStatement.close();
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testBindInWithClause() throws IOException, SQLException
  {
    outputLine("running testBindInWithClause");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      // create a test table
      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testBind2(a int, b string, c double, "
                  + "d date, e timestamp, f time, g date)");
      regularStatement.close();

      // bind in where clause
      preparedStatement = connection.prepareStatement(
          "WITH V AS (SELECT * FROM testBind2 WHERE a = ?) "
                  + "SELECT count(*) FROM V");

      preparedStatement.setInt(1, 100);
      resultSet = preparedStatement.executeQuery();
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert we get 1 rows
      assertTrue(resultSet.next());
      preparedStatement.close();

      succs.incrementAndGet();
      outputLine("testBindInWithClause : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testBindInWithClause : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testBind2");
        regularStatement.close();
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testBindTimestampNTZ() throws IOException, SQLException
  {
    outputLine("running testBindTimestampNTZ");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      // create a test table
      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testBindTimestampNTZ(a timestamp_ntz)");
      regularStatement.close();

      regularStatement.executeUpdate(
          "alter session set client_timestamp_type_mapping='timestamp_ntz'");
      regularStatement.close();

      // bind in where clause
      preparedStatement = connection.prepareStatement(
          "insert into testBindTimestampNTZ values(?)");

      Timestamp ts = new Timestamp(Date.UTC(114, 7, 26, 3, 52, 0));

      preparedStatement.setTimestamp(1, ts);

      int updateCount = preparedStatement.executeUpdate();

      // update count should be 1
      assertEquals("update count", 1, updateCount);

      // test the inserted rows
      resultSet = regularStatement.executeQuery("select * from testBindTimestampNTZ");

      // assert we get 1 rows
      assertTrue(resultSet.next());
      assertEquals("timestamp", "Tue, 26 Aug 2014 03:52:00 Z",
                   resultSet.getString(1));

      regularStatement.executeUpdate(
          "truncate table testBindTimestampNTZ");
      regularStatement.close();

      preparedStatement.setTimestamp(1, ts,
             Calendar.getInstance(TimeZone.getTimeZone("America/Los_Angeles")));

      updateCount = preparedStatement.executeUpdate();

      // update count should be 1
      assertEquals("update count", 1, updateCount);

      // test the inserted rows
      resultSet = regularStatement.executeQuery("select * from testBindTimestampNTZ");

      // assert we get 1 rows
      assertTrue(resultSet.next());

      preparedStatement.close();

      succs.incrementAndGet();
      outputLine("testBindTimestampNTZ : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testBindTimestampNTZ : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testBindTimestampNTZ");
        regularStatement.close();
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testNullBind() throws IOException, SQLException
  {
    outputLine("running testNullBind");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testNullBind(a double)");
      regularStatement.close();

      // array bind with nulls
      preparedStatement = connection.prepareStatement(
          "insert into testNullBind (a) values(?)");

      preparedStatement.setDouble(1, 1.2);
      preparedStatement.addBatch();

      preparedStatement.setObject(1, null);
      preparedStatement.addBatch();

      int[] updateCounts = preparedStatement.executeBatch();

      // GS optimizes this into one insert execution
      assertEquals("Number of update counts", 2, updateCounts.length);

      // update count should be 1
      assertEquals("update count", 1, updateCounts[0]);
      assertEquals("update count", 1, updateCounts[1]);

      preparedStatement.clearBatch();

      preparedStatement.setObject(1, null);
      preparedStatement.addBatch();

      preparedStatement.setDouble(1, 1.2);
      preparedStatement.addBatch();

      updateCounts = preparedStatement.executeBatch();

      // GS optimizes this into one insert execution
      assertEquals("Number of update counts", 2, updateCounts.length);

      // update count should be 1
      assertEquals("update count", 1, updateCounts[0]);
      assertEquals("update count", 1, updateCounts[1]);

      preparedStatement.clearBatch();

      preparedStatement.setObject(1, null);
      preparedStatement.addBatch();

      updateCounts = preparedStatement.executeBatch();

      // GS optimizes this into one insert execution
      assertEquals("Number of update counts", 1, updateCounts.length);

      // update count should be 1
      assertEquals("update count", 1, updateCounts[0]);

      preparedStatement.clearBatch();

      boolean gotException = false;

      // this test causes query count in GS not to be decremented because
      // the exception is thrown before registerQC. Discuss with Johnston
      // to resolve the issue before enabling the test.
      try
      {
        preparedStatement.setObject(1, "Null", Types.DOUBLE);
        preparedStatement.addBatch();

        updateCounts = preparedStatement.executeBatch();
      }
      catch(SnowflakeSQLException ex)
      {
        if (ex.getErrorCode() == 2086)
          gotException = true;
        else throw ex;
      }

      assertTrue(gotException);

      gotException = false;

      preparedStatement.clearBatch();

      try
      {
        preparedStatement.setString(1, "hello");
        preparedStatement.addBatch();

        preparedStatement.setDouble(1, 1.2);
        preparedStatement.addBatch();

        updateCounts = preparedStatement.executeBatch();
      }
      catch(SnowflakeSQLException ex)
      {
        if (ex.getErrorCode() ==
            ErrorCode.ARRAY_BIND_MIXED_TYPES_NOT_SUPPORTED.getMessageCode())
          gotException = true;
        else throw ex;
      }

      assertTrue(gotException);

      succs.incrementAndGet();
      outputLine("testNullBind : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testNullBind : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testNullBind");
        regularStatement.close();
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testSnow12603() throws IOException, SQLException
  {
    outputLine("running testSnow12603");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      preparedStatement = connection.prepareStatement("SELECT ?, ?, ?, ?, ?, ?");

      java.sql.Date sqlDate = java.sql.Date.valueOf("2014-08-26");

      Timestamp ts = new Timestamp(Date.UTC(114, 7, 26, 3, 52, 0));

      preparedStatement.setObject(1, Integer.valueOf(1));
      preparedStatement.setObject(2, new String("hello"));
      preparedStatement.setObject(3, new BigDecimal("1.3"));
      preparedStatement.setObject(4, Float.valueOf("1.3"));
      preparedStatement.setObject(5, sqlDate);
      preparedStatement.setObject(6, ts);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 6);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);
      assertTrue(resultSetMetaData.getColumnType(3) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(4) == Types.DOUBLE);
      assertTrue(resultSetMetaData.getColumnType(5) == Types.DATE);
      assertTrue(resultSetMetaData.getColumnType(6) == Types.TIMESTAMP);

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertEquals("integer", 1, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));
      assertEquals("decimal", new BigDecimal("1.3"), resultSet.getBigDecimal(3));
      assertEquals("double", 1.3, resultSet.getDouble(4), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(5));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(6));

      preparedStatement.setObject(1, Integer.valueOf(1), Types.INTEGER);
      preparedStatement.setObject(2, new String("hello"), Types.VARCHAR);
      preparedStatement.setObject(3, new BigDecimal("1.3"), Types.DECIMAL);
      preparedStatement.setObject(4, Float.valueOf("1.3"), Types.DOUBLE);
      preparedStatement.setObject(5, sqlDate, Types.DATE);
      preparedStatement.setObject(6, ts, Types.TIMESTAMP);

      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 6);
      assertTrue(resultSetMetaData.getColumnType(1) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(2) == Types.VARCHAR);
      assertTrue(resultSetMetaData.getColumnType(3) == Types.DECIMAL);
      assertTrue(resultSetMetaData.getColumnType(4) == Types.DOUBLE);
      assertTrue(resultSetMetaData.getColumnType(5) == Types.DATE);
      assertTrue(resultSetMetaData.getColumnType(6) == Types.TIMESTAMP);

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertEquals("integer", 1, resultSet.getInt(1));
      assertEquals("string", "hello", resultSet.getString(2));
      assertEquals("decimal", new BigDecimal("1.3"), resultSet.getBigDecimal(3));
      assertEquals("double", 1.3, resultSet.getDouble(4), 0);
      assertEquals("date", "2014-08-26", resultSet.getString(5));
      assertEquals("timestamp", "Mon, 25 Aug 2014 20:52:00 -0700",
          resultSet.getString(6));

      succs.incrementAndGet();
      outputLine("testSnow12603 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow12603 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  /**
   * SNOW-6290: timestamp value is shifted by local timezone
   *
   */
  @Test
  public void testSnow6290() throws IOException, SQLException
  {
    outputLine("running testSnow6290");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // create test table
      statement.executeUpdate("CREATE OR REPLACE TABLE testSnow6290(ts timestamp)");
      statement.close();

      PreparedStatement preparedStatement =
          connection.prepareStatement("INSERT INTO testSnow6290(ts) values(?)");

      Timestamp ts = new Timestamp(System.currentTimeMillis());

      preparedStatement.setTimestamp(1, ts);
      preparedStatement.executeUpdate();

      ResultSet res = statement.executeQuery("select ts from testSnow6290");

      assertTrue("expect a row", res.next());

      Timestamp tsFromDB = res.getTimestamp(1);

      assertTrue("timestamp mismatch", ts.getTime() == tsFromDB.getTime());

      succs.incrementAndGet();
      outputLine("testSnow6290 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow6290 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (statement != null)
      {
        statement.executeUpdate("DROP TABLE if exists testSnow6290");
        statement.close();
      }
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  /**
   * SNOW-6986: null sql shouldn't be allowed
   *
   * @throws IOException
   * @throws SQLException
   */
  @Test
  public void testInvalidSQL() throws IOException, SQLException
  {
    outputLine("running testInvalidSQL");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // execute DDLs
      statement.executeQuery(null);
      statement.close();

      diffs.incrementAndGet();
      outputLine("testInvalidSQL : difference");

      assertTrue("expected exception, but no exception", false);

    }
    catch (SnowflakeSQLException ex)
    {
      if (ex.getErrorCode() == ErrorCode.INVALID_SQL.getMessageCode())
      {
        succs.incrementAndGet();
        outputLine("testInvalidSQL : success");
      }
      else
      {
        diffs.incrementAndGet();
        outputLine("testInvalidSQL : difference");

        assertTrue("expected invalid sql exception, but got: " +
            ex.getLocalizedMessage(), false);
      }
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testInvalidSQL : difference");

      assertTrue("expected invalid sql exception, but got: " +
          ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testGetObject() throws IOException, SQLException
  {
    outputLine("running testGetObject");
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      preparedStatement = connection.prepareStatement("SELECT ?");

      // bind integer
      preparedStatement.setInt(1, 1);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      assertTrue("column class name=BigDecimal",
                 resultSetMetaData.getColumnClassName(1).equals(
                         BigDecimal.class.getName()));

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertTrue("integer", resultSet.getObject(1) instanceof BigDecimal);

      preparedStatement.setString(1, "hello");
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      assertTrue("column class name=String",
                 resultSetMetaData.getColumnClassName(1).equals(
                         String.class.getName()));

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertTrue("string", resultSet.getObject(1) instanceof String);

      preparedStatement.setDouble(1, 1.2);
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      assertTrue("column class name=Double",
                 resultSetMetaData.getColumnClassName(1).equals(
                         Double.class.getName()));

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertTrue("double", resultSet.getObject(1) instanceof Double);

      preparedStatement.setTimestamp(1, new Timestamp(0));
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      assertTrue("column class name=Timestamp",
                 resultSetMetaData.getColumnClassName(1).equals(
                         Timestamp.class.getName()));

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertTrue("timestamp", resultSet.getObject(1) instanceof Timestamp);

      preparedStatement.setDate(1, new java.sql.Date(0));
      resultSet = preparedStatement.executeQuery();

      resultSetMetaData = resultSet.getMetaData();

      assertTrue("column class name=Date",
                 resultSetMetaData.getColumnClassName(1).equals(
                         java.sql.Date.class.getName()));

      // assert we get 1 rows
      assertTrue(resultSet.next());

      assertTrue("date", resultSet.getObject(1) instanceof java.sql.Date);

      preparedStatement.close();

      outputLine("testGetObject : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testGetObject : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testGetDoubleForNull() throws IOException, SQLException
  {
    outputLine("running testGetDoubleForNull");

    Connection connection = null;
    Statement stmt = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      stmt = connection.createStatement();
      resultSet = stmt.executeQuery("select cast(null as int) as null_int");
      assertTrue(resultSet.next());
      assertTrue("0 for null", resultSet.getDouble(1) ==0);

      outputLine("testGetDoubleForNull : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testGetDoubleForNull : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, stmt, connection);
    }
  }

  // SNOW-27438
  //@Test
  public void testGetDoubleForNaN() throws IOException, SQLException
  {
    outputLine("running testGetDoubleForNaN");

    Connection connection = null;
    Statement stmt = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      stmt = connection.createStatement();
      resultSet = stmt.executeQuery("select 'nan'::float");
      assertTrue(resultSet.next());
      assertTrue("NaN for NaN", resultSet.getDouble(1) == Double.NaN);

      outputLine("testGetDoubleForNaN : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testGetDoubleForNaN : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, stmt, connection);
    }
  }

  @Test
  public void testPutViaExecuteQuery() throws IOException, SQLException
  {
    outputLine("running testPutViaExecuteQuery");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      // load file test
      // create a unique data file name by using current timestamp in millis
      try
      {
        File srcDir = null;

        // test external table load
        statement.execute(
            "CREATE OR REPLACE TABLE testPutViaExecuteQuery(a number)");

        String sfProjectRoot = getSFProjectRoot();

        // put files
        resultSet =
            statement.executeQuery("PUT file://" + sfProjectRoot +
                "/ExecPlatform/Database/data/orders_100.csv "
                + "@%testPutViaExecuteQuery/orders parallel=10");

        resultSetMetaData = resultSet.getMetaData();

        // assert column count
        assertTrue(resultSetMetaData.getColumnCount() > 0);

        // assert we get 1 rows
        for(int i = 0; i < 1; i++)
        {
          assertTrue(resultSet.next());
        }

        statement.close();
      }
      finally
      {
        statement.execute("DROP TABLE IF EXISTS testPutViaExecuteQuery");
        statement.close();
      }

      succs.incrementAndGet();
      outputLine("testPutViaExecuteQuery : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testPutViaExecuteQuery : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  //@Test
  public void testSnow16332() throws IOException, SQLException
  {
    outputLine("running testSnow16332");
    Connection conn = null;
    Connection connWithNwError = null;
    Statement stmt = null;
    Statement stmtWithNwError = null;
    ResultSet resultSet = null;

    try
    {
      // use v1 query request API and inject 200ms socket timeout for first
      // http request to simulate network failure
      conn = getConnection(GET_ACCOUNT_FROM_ENV,
                           USE_V1_QUERY_API,
                           DONT_INJECT_SOCKET_TIMEOUT);

      stmt = conn.createStatement();

      // create a table
      stmt.executeUpdate("CREATE OR REPLACE TABLE SNOW16332 (i int)");

      // make sure QC is JIT optimized. Change the GS JVM args to include
      // -Xcomp or -XX:CompileThreshold = < a number smaller than the
      // stmtCounter


      int stmtCounter = 2000;
      System.out.println("Priming JIT with " + stmtCounter +
                         " queries.");
      while (stmtCounter > 0)
      {
        if (stmtCounter % 100 == 0)
        {
          System.out.println("Running " + stmtCounter +
                                 " more queries.");
        }

        // insert into it this should start a transaction.
        stmt.executeUpdate("INSERT INTO SNOW16332 VALUES (" + stmtCounter + ")");
        --stmtCounter;
      }

      connWithNwError = getConnection(GET_ACCOUNT_FROM_ENV,
                                      USE_V1_QUERY_API,
                                      500); // inject socket timeout in ms

      stmtWithNwError = connWithNwError.createStatement();

      System.out.println("Executing dml with socket timeout");

      // execute dml
      stmtWithNwError.executeUpdate(
          "INSERT INTO SNOW16332 " +
          "SELECT seq8() " +
          "FROM table(generator(timeLimit => 1))");

      System.out.println("Executing stmt2 with socket timeout");
      // and execute another dml
      stmtWithNwError.executeUpdate(
          "INSERT INTO SNOW16332 " +
          "SELECT seq8() " +
          "FROM table(generator(timeLimit => 1))");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow16332 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      stmt.executeQuery("DROP TABLE SNOW16332");
      closeSQLObjects(resultSet, stmt, conn);
      closeSQLObjects(resultSet, stmtWithNwError, connWithNwError);
    }
  }

  @Test
  public void testV1Query() throws IOException, SQLException
  {
    outputLine("running testV1Query");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      // use v1 query request API and inject 200ms socket timeout for first
      // http request to simulate network failure
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 USE_V1_QUERY_API,
                                 200); // inject socket timeout = 200ms

      statement = connection.createStatement();

      // execute query
      resultSet = statement.executeQuery("SELECT count(*) FROM table(generator(rowCount => 100000000))");
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert we get 1 row
      for(int i = 0; i < 1; i++)
      {
        assertTrue(resultSet.next());
        assertTrue(resultSet.getInt(1) > 0);
      }

      // Test parsing for timestamp with timezone value that has new encoding
      // where timezone index follows timestamp value
      resultSet = statement.executeQuery(
              "SELECT 'Fri, 23 Oct 2015 12:35:38 -0700'::timestamp_tz");
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert we get 1 row
      for(int i = 0; i < 1; i++)
      {
        assertTrue(resultSet.next());
        assertTrue("Fri, 23 Oct 2015 12:35:38 -0700".equals(resultSet.getString(1)));
      }

      statement.close();

      succs.incrementAndGet();
      outputLine("testV1Query : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testV1Query : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  @Test
  public void testCancelQueryV1() throws IOException, SQLException
  {
    outputLine("running testCancelQueryV1");
    ResultSet resultSet = null;

    final Connection connection = getConnection(GET_ACCOUNT_FROM_ENV,
        USE_V1_QUERY_API,
        DONT_INJECT_SOCKET_TIMEOUT);

    final Statement statement = connection.createStatement();

    // schedule a cancel in 5 seconds
    try
    {
      Timer timer = new Timer();
      timer.schedule( new TimerTask()
      {
        @Override
        public void run()
        {
          try
          {
            outputLine("Cancel query");
            statement.cancel();
          }
          catch (SQLException ex)
          {
            logger.log(Level.SEVERE, "Cancel failed with exception {}", ex);
          }
        }
      }, 5000);

      outputLine("Start query");
      // now run a query for 120 seconds
      resultSet = statement.executeQuery(
          "SELECT count(*) FROM TABLE(generator(timeLimit => 120))");

      diffs.incrementAndGet();
      outputLine("testCancelQueryV1 : difference");
    }
    catch (SQLException ex)
    {
      // assert the sqlstate is what we expect (QUERY CANCELLED)
      assertEquals("sqlstate mismatch",
          SqlState.QUERY_CANCELED, ex.getSQLState());

      outputLine("testCancelQueryV1 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testCancelQueryV1 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  /**
   * SNOW-14774: timestamp_ntz value should use client time zone to adjust
   * the epoch time.
   *
   */
  @Test
  public void testSnow14774() throws IOException, SQLException
  {
    outputLine("running testSnow14774");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      /*
      statement.execute(
              "alter session set client_honor_client_tz_for_timestamp_ntz=false");
      statement.close();
      */

      // 30 minutes past daylight saving change (from 2am to 3am)
      ResultSet res = statement.executeQuery(
              "select '2015-03-08 03:30:00'::timestamp_ntz");

      res.next();

      // get timestamp in UTC
      Calendar calendar = new GregorianCalendar(TimeZone.getTimeZone("UTC"));
      Timestamp tsInUTC = res.getTimestamp(1, calendar);

      SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
      sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
      String tsStrInUTC = sdf.format(tsInUTC);
      System.out.println("ts1 in UTC: " + tsStrInUTC);

      // get timestamp in LA timezone
      calendar.setTimeZone(TimeZone.getTimeZone("America/Los_Angeles"));
      Timestamp tsInLA = res.getTimestamp(1, calendar);

      sdf.setTimeZone(TimeZone.getTimeZone("America/Los_Angeles"));
      String tsStrInLA = sdf.format(tsInLA);
      System.out.println("ts1 in LA: " + tsStrInLA);

      // the timestamp in LA and in UTC should be the same
      assertEquals("timestamp values not equal", tsStrInUTC, tsStrInLA);

      // 30 minutes before daylight saving change
      res = statement.executeQuery(
              "select '2015-03-08 01:30:00'::timestamp_ntz");

      res.next();

      // get timestamp in UTC
      calendar.setTimeZone(TimeZone.getTimeZone("UTC"));
      tsInUTC = res.getTimestamp(1, calendar);

      sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
      tsStrInUTC = sdf.format(tsInUTC);
      System.out.println("ts2 in UTC: " + tsStrInUTC);

      // get timestamp in LA timezone
      calendar.setTimeZone(TimeZone.getTimeZone("America/Los_Angeles"));
      tsInLA = res.getTimestamp(1, calendar);

      sdf.setTimeZone(TimeZone.getTimeZone("America/Los_Angeles"));
      tsStrInLA = sdf.format(tsInLA);
      System.out.println("ts2 in LA: " + tsStrInLA);

      // the timestamp in LA and in UTC should be the same
      assertEquals("timestamp values not equal", tsStrInUTC, tsStrInLA);

      succs.incrementAndGet();
      outputLine("testSnow14774 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow14774 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }

  /**
   * SNOW-19172: getMoreResults should return false after executeQuery
   *
   */
  @Test
  public void testSnow19172() throws SQLException
  {
    outputLine("running testSnow19172");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      ResultSet res = statement.executeQuery("select 1");

      assertTrue(!statement.getMoreResults());

      outputLine("testSnow19172 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow19172 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }


  @Test
  public void testSnow19819() throws IOException, SQLException
  {
    outputLine("running testSnow19819");

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
          !USE_V1_QUERY_API,
          DONT_INJECT_SOCKET_TIMEOUT);

      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testSnow19819(\n" +
              "s string,\n" +
              "v variant,\n" +
              "t timestamp_ltz)\n");

      preparedStatement = connection.prepareStatement(
          "insert into testSnow19819 (s, v, t)\n" +
              "select ?, parse_json(?), to_timestamp(?)");

      preparedStatement.setString(1, "foo");
      preparedStatement.setString(2, "{ }");
      preparedStatement.setString(3, "2016-05-12 12:15:00");
      preparedStatement.addBatch();

      preparedStatement.setString(1, "foo2");
      preparedStatement.setString(2, "{ \"a\": 1 }");
      preparedStatement.setString(3, "2016-05-12 12:16:00");
      preparedStatement.addBatch();

      preparedStatement.executeBatch();

      outputLine("testSnow19819 : difference");
      logger.log(Level.SEVERE, "Test was expected to fail with error message " +
          "ARRAY_BIND_NOT_SUPPORTED");
    }
    catch (SnowflakeSQLException e)
    {
      if (e.getErrorCode() == 2046) // That's SQLErrorCode.UNSUPPORTED_ARRAY_BIND
      {
        outputLine("testSnow19819 : success");
      }
      else
      {
        outputLine("testSnow19819 : difference");
        logger.log(Level.SEVERE, "Test failed with unexpected error code: " +
            e.getErrorCode());
      }
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow19819 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testSnow19819");
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
    }
  }

  @Test
  public void testClientInfo() throws IOException, SQLException
  {
    outputLine("running testClientInfo");

    Connection connection = null;
    Statement statement = null;
    ResultSet res = null;

    try
    {
      System.setProperty("snowflake.client.info",
                         "{\"sparkVersion\":\"1.2.0\", \"sparkApp\":\"mySparkApp\"}");

      connection = getConnection(GET_ACCOUNT_FROM_ENV,
          !USE_V1_QUERY_API,
          DONT_INJECT_SOCKET_TIMEOUT);

      statement = connection.createStatement();

      res = statement.executeQuery(
              "select current_session_client_info()");

      assertTrue("result expected", res.next());

      String clientInfoJSONStr = res.getString(1);

      JsonNode clientInfoJSON = mapper.readTree(clientInfoJSONStr);

      // assert that spart version and spark app are found
      assertTrue("spark version mismatch",
                 "1.2.0".equals(clientInfoJSON.get("sparkVersion").asText()));

      assertTrue("spark app mismatch",
                 "mySparkApp".equals(clientInfoJSON.get("sparkApp").asText()));

      succs.incrementAndGet();
      outputLine("testClientInfo : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testClientInfo : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(res, statement, connection);
    }
  }


  @Test
  public void testLargeResultPerformance() throws IOException, SQLException
  {
    outputLine("running testLargeResultPerformance");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      // create statement
      System.out.println("Create JDBC statement");
      statement = connection.createStatement();
      System.out.println("Done creating JDBC statement\n");

      // Set the result file strategy.
      System.out.println("Setting result strategy");

      statement.execute("ALTER SESSION SET "
                            + "ENABLE_RESULT_SCAN = true,"
                            + "CLIENT_RESULT_PREFETCH_SLOTS=8, "
                            + "CLIENT_RESULT_PREFETCH_THREADS=4, "
                            + "RESULT_FIRST_CHUNK_MAX_SIZE = 1, "
                            + "RESULT_MIN_CHUNK_SIZE = 1, "
                            + "ROWS_PER_ROWSET=1, "
                            + "LOCAL_DOP = 1, "
                            + "INSTANCES_PER_WORKER = 1, "
                            + "SERVER_COUNT = 1");

      // set RESULT_MAX_CHUNK_SIZE after RESULT_MIN_CHUNK SIZE is set,
      // since there is an interdependence between the two.
      statement.execute("ALTER SESSION SET RESULT_MAX_CHUNK_SIZE = 2");

      System.out.println("Done setting result strategy\n");
      // Time the test query.
      System.out.println("Running test query");
      long startTS = System.currentTimeMillis();

      String sql =
        "SELECT random()||random() FROM table(generator(rowcount => 10000))";
      String qid;

      ResultSet result = statement.executeQuery(sql);


      int rowCount = 0;
      while(result.next())
      {
        rowCount++;
      }
      statement.close();
      long endTS = System.currentTimeMillis();
      double time = (endTS - startTS) / 1000.0;
      System.out.println("Fetched " + rowCount + " rows, took " + time + "s.");

    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testExplainPlan : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);

      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      closeSQLObjects(resultSet, statement, connection);
    }
  }


  @Test
  public void testSnow26503() throws IOException, SQLException
  {
    outputLine("running testSnow26503");
    Connection connection = null;
    Connection snowflakeConnection = null;
    PreparedStatement preparedStatement = null;
    Statement regularStatement = null;
    Statement regularStatementSF = null;
    ResultSet resultSet = null;
    ResultSetMetaData resultSetMetaData = null;

    try
    {
      connection = getConnection(GET_ACCOUNT_FROM_ENV,
                                 !USE_V1_QUERY_API,
                                 DONT_INJECT_SOCKET_TIMEOUT);

      // create a test table
      regularStatement = connection.createStatement();
      regularStatement.executeUpdate(
          "create or replace table testBind2(a int) as select * from values(1),(2),(8),(10)");
      regularStatement.close();

      // test binds in BETWEEN predicate
      preparedStatement = connection.prepareStatement
          ("SELECT * FROM testBind2 WHERE a between ? and ?");

      preparedStatement.setInt(1, 3);
      preparedStatement.setInt(2, 9);
      // test that the query succeeds; used to fail with incident
      resultSet = preparedStatement.executeQuery();
      resultSetMetaData = resultSet.getMetaData();

      // assert column count
      assertTrue(resultSetMetaData.getColumnCount() == 1);

      // assert we get 1 row
      assertTrue(resultSet.next());

      resultSet.close();
      preparedStatement.close();
      preparedStatement = connection.prepareStatement
          ("SELECT last_query_id()");
      resultSet = preparedStatement.executeQuery();
      resultSet.next();
      String queryId = resultSet.getString(1);

      resultSet.close();
      preparedStatement.close();

      // check that the bind values can be retrieved using system$get_bind_values
      snowflakeConnection = getSnowflakeConnection(!USE_V1_QUERY_API);

      regularStatementSF = snowflakeConnection.createStatement();
      regularStatementSF.executeUpdate(
          "create or replace warehouse wh26503 warehouse_size=xsmall");
      regularStatement.close();

      preparedStatement = snowflakeConnection.prepareStatement
          ("select bv:\"1\":\"value\"::string, bv:\"2\":\"value\"::string from (select parse_json(system$get_bind_values(?)) bv)");
      preparedStatement.setString(1, queryId);
      resultSet = preparedStatement.executeQuery();
      resultSet.next();

      // check that the bind values are correct
      assertTrue(resultSet.getInt(1) == 3);
      assertTrue(resultSet.getInt(2) == 9);

      succs.incrementAndGet();
      outputLine("testSnow26503 : success");
    }
    catch (Throwable ex)
    {
      diffs.incrementAndGet();
      outputLine("testSnow26503 : difference");

      logger.log(Level.SEVERE, "Test failed with exception: ", ex);
      assertTrue(ex.getLocalizedMessage(), false);
    }
    finally
    {
      if (regularStatement != null)
      {
        regularStatement.executeUpdate("DROP TABLE testBind2");
        regularStatement.close();

        regularStatementSF.executeUpdate("DROP warehouse wh26503");
        regularStatementSF.close();
      }

      closeSQLObjects(resultSet, preparedStatement, connection);
      snowflakeConnection.close();
    }
  }

}




© 2015 - 2025 Weber Informatics LLC | Privacy Policy