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