You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/09/29 02:22:18 UTC
[18/27] phoenix git commit: PHOENIX-4244 Breakup ArrayIT into several
integration tests so as not to create too many tables in one test
http://git-wip-us.apache.org/repos/asf/phoenix/blob/c6f030a8/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java
index cf86614..4c25f4c 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java
@@ -19,35 +19,48 @@ package org.apache.phoenix.end2end;
import static org.apache.phoenix.util.TestUtil.B_VALUE;
import static org.apache.phoenix.util.TestUtil.ROW1;
-import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertNull;
-import static org.junit.Assert.assertTrue;
-import static org.junit.Assert.fail;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
-import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
import org.apache.phoenix.query.BaseTest;
-import org.apache.phoenix.schema.types.PhoenixArray;
-import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.SchemaUtil;
-import org.apache.phoenix.util.StringUtil;
-import org.junit.Test;
-import com.google.common.primitives.Floats;
+public abstract class ArrayIT extends ParallelStatsDisabledIT {
-public class ArrayIT extends ParallelStatsDisabledIT {
+ protected static String createTableWithArray(String url, byte[][] bs, Object object) throws SQLException {
+ String tableName = generateUniqueName();
+ String ddlStmt = "create table "
+ + tableName
+ + " (organization_id char(15) not null, \n"
+ + " entity_id char(15) not null,\n"
+ + " a_string_array varchar(100) array[3],\n"
+ + " b_string varchar(100),\n"
+ + " a_integer integer,\n"
+ + " a_date date,\n"
+ + " a_time time,\n"
+ + " a_timestamp timestamp,\n"
+ + " x_decimal decimal(31,10),\n"
+ + " x_long_array bigint[5],\n"
+ + " x_integer integer,\n"
+ + " a_byte_array tinyint array,\n"
+ + " a_short smallint,\n"
+ + " a_float float,\n"
+ + " a_double_array double array[],\n"
+ + " a_unsigned_float unsigned_float,\n"
+ + " a_unsigned_double unsigned_double \n"
+ + " CONSTRAINT pk PRIMARY KEY (organization_id, entity_id)\n"
+ + ")";
+ BaseTest.createTestTable(url, ddlStmt, bs, null);
+ return tableName;
+ }
- private static void initTablesWithArrays(String tableName, String tenantId, Date date, boolean useNull, String url) throws Exception {
+ protected static void initTablesWithArrays(String tableName, String tenantId, Date date, boolean useNull, String url) throws Exception {
Properties props = new Properties();
Connection conn = DriverManager.getConnection(url, props);
try {
@@ -121,2504 +134,4 @@ public class ArrayIT extends ParallelStatsDisabledIT {
conn.close();
}
}
-
- @Test
- public void testScanByArrayValue() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM " + tableName + " WHERE ?=organization_id and ?=a_float";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- analyzeTable(conn, tableName);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- statement.setString(1, tenantId);
- statement.setFloat(2, 0.01f);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[4];
- doubleArr[0] = 25.343;
- doubleArr[1] = 36.763;
- doubleArr[2] = 37.56;
- doubleArr[3] = 386.63;
- Array array = conn.createArrayOf("DOUBLE",
- doubleArr);
- PhoenixArray resultArray = (PhoenixArray) rs.getArray(1);
- assertEquals(resultArray, array);
- assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
- assertEquals(rs.getString("B_string"), B_VALUE);
- assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- private void analyzeTable(Connection conn, String tableWithArray) throws SQLException {
- String analyse = "UPDATE STATISTICS "+tableWithArray;
- PreparedStatement statement = conn.prepareStatement(analyse);
- statement.execute();
- }
-
- @Test
- public void testScanWithArrayInWhereClause() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM " + tableName + " WHERE ?=organization_id and ?=a_byte_array";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- //TODO: samarth do we need this?
- analyzeTable(conn, tableName);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- statement.setString(1, tenantId);
- // Need to support primitive
- Byte[] byteArr = new Byte[2];
- byteArr[0] = 25;
- byteArr[1] = 36;
- Array array = conn.createArrayOf("TINYINT", byteArr);
- statement.setArray(2, array);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[4];
- doubleArr[0] = 25.343;
- doubleArr[1] = 36.763;
- doubleArr[2] = 37.56;
- doubleArr[3] = 386.63;
- array = conn.createArrayOf("DOUBLE", doubleArr);
- Array resultArray = rs.getArray(1);
- assertEquals(resultArray, array);
- assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
- assertEquals(rs.getString("B_string"), B_VALUE);
- assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testScanWithNonFixedWidthArrayInWhereClause() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM " + tableName + " WHERE ?=organization_id and ?=a_string_array";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- statement.setString(1, tenantId);
- // Need to support primitive
- String[] strArr = new String[4];
- strArr[0] = "ABC";
- strArr[1] = "CEDF";
- strArr[2] = "XYZWER";
- strArr[3] = "AB";
- Array array = conn.createArrayOf("VARCHAR", strArr);
- statement.setArray(2, array);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[4];
- doubleArr[0] = 25.343;
- doubleArr[1] = 36.763;
- doubleArr[2] = 37.56;
- doubleArr[3] = 386.63;
- array = conn.createArrayOf("DOUBLE", doubleArr);
- Array resultArray = rs.getArray(1);
- assertEquals(resultArray, array);
- assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
- assertEquals(rs.getString("B_string"), B_VALUE);
- assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testScanWithNonFixedWidthArrayInSelectClause() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array FROM " + tableName;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[4];
- strArr[0] = "ABC";
- strArr[1] = "CEDF";
- strArr[2] = "XYZWER";
- strArr[3] = "AB";
- Array array = conn.createArrayOf("VARCHAR", strArr);
- PhoenixArray resultArray = (PhoenixArray) rs.getArray(1);
- assertEquals(resultArray, array);
- assertEquals("['ABC', 'CEDF', 'XYZWER', 'AB']", rs.getString(1));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSpecificIndexOfAnArrayAsArrayFunction()
- throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT ARRAY_ELEM(a_double_array,2) FROM " + tableName;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 36.763;
- conn.createArrayOf("DOUBLE", doubleArr);
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSpecificIndexOfAnArray() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array[3] FROM " + tableName;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 37.56;
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testCaseWithArray() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(tableName, tenantId, null, false, getUrl());
- String query = "SELECT CASE WHEN A_INTEGER = 1 THEN a_double_array ELSE null END [3] FROM " + tableName;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 37.56;
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testUpsertValuesWithArray() throws Exception {
- String tenantId = getOrganizationId();
- String tableName = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- String query = "upsert into " + tableName + " (ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
- + "','00A123122312312',ARRAY[2.0,345.8])";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- // at
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- int executeUpdate = statement.executeUpdate();
- assertEquals(1, executeUpdate);
- conn.commit();
- statement.close();
- conn.close();
- // create another connection
- props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- query = "SELECT ARRAY_ELEM(a_double_array,2) FROM " + tableName;
- statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 345.8d;
- conn.createArrayOf("DOUBLE", doubleArr);
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testUpsertSelectWithSelectAsSubQuery1() throws Exception {
- String tenantId = getOrganizationId();
- String table1 = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- Connection conn = null;
- try {
- String table2 = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table2, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "upsert into " + table1 + " (ORGANIZATION_ID,ENTITY_ID,a_double_array) "
- + "SELECT organization_id, entity_id, a_double_array FROM " + table2
- + " WHERE a_double_array[2] = 89.96";
- PreparedStatement statement = conn.prepareStatement(query);
- int executeUpdate = statement.executeUpdate();
- assertEquals(1, executeUpdate);
- conn.commit();
- statement.close();
- conn.close();
- // create another connection
- props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- query = "SELECT ARRAY_ELEM(a_double_array,2) FROM " + table1;
- statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 89.96d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- assertFalse(rs.next());
-
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithORCondition() throws Exception {
- String tenantId = getOrganizationId();
- Connection conn = null;
- try {
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_double_array[1] FROM " + table
- + " WHERE a_double_array[2] = 89.96 or a_char_array[0] = 'a'";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 64.87d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- assertFalse(rs.next());
-
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithANY() throws Exception {
- String tenantId = getOrganizationId();
- Connection conn = null;
- try {
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_double_array[1] FROM " + table
- + " WHERE CAST(89.96 AS DOUBLE) = ANY(a_double_array)";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 64.87d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- assertFalse(rs.next());
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithALL() throws Exception {
- String tenantId = getOrganizationId();
- Connection conn = null;
- try {
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_double_array[1] FROM " + table
- + " WHERE CAST(64.87 as DOUBLE) = ALL(a_double_array)";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertFalse(rs.next());
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithANYCombinedWithOR() throws Exception {
- String tenantId = getOrganizationId();
- Connection conn = null;
- try {
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_double_array[1] FROM " + table
- + " WHERE a_char_array[0] = 'f' or CAST(89.96 AS DOUBLE) > ANY(a_double_array)";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 64.87d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- assertFalse(rs.next());
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithALLCombinedWithOR() throws Exception {
-
- String tenantId = getOrganizationId();
- Connection conn = null;
- try {
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_double_array[1], a_double_array[2] FROM " + table
- + " WHERE a_char_array[0] = 'f' or CAST(100.0 AS DOUBLE) > ALL(a_double_array)";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 64.87d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- doubleArr = new Double[1];
- doubleArr[0] = 89.96d;
- result = rs.getDouble(2);
- assertEquals(result, doubleArr[0]);
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testArraySelectWithANYUsingVarLengthArray() throws Exception {
- Connection conn = null;
- try {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "SELECT a_string_array[1] FROM " + table
- + " WHERE 'XYZWER' = ANY(a_string_array)";
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "ABC";
- String result = rs.getString(1);
- assertEquals(result, strArr[0]);
- assertFalse(rs.next());
- query = "SELECT a_string_array[1] FROM " + table + " WHERE 'AB' = ANY(a_string_array)";
- statement = conn.prepareStatement(query);
- rs = statement.executeQuery();
- assertTrue(rs.next());
- result = rs.getString(1);
- assertEquals(result, strArr[0]);
- assertFalse(rs.next());
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testSelectWithArrayWithColumnRef() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_integer,ARRAY[1,2,a_integer] FROM " + table + " where organization_id = '"
- + tenantId + "'";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- int val = rs.getInt(1);
- assertEquals(val, 1);
- Array array = rs.getArray(2);
- // Need to support primitive
- Integer[] intArr = new Integer[3];
- intArr[0] = 1;
- intArr[1] = 2;
- intArr[2] = 1;
- Array resultArr = conn.createArrayOf("INTEGER", intArr);
- assertEquals(resultArr, array);
- assertEquals("[1, 2, 1]", rs.getString(2));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectWithArrayWithColumnRefWithVarLengthArray() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT b_string,ARRAY['abc','defgh',b_string] FROM " + table + " where organization_id = '"
- + tenantId + "'";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String val = rs.getString(1);
- assertEquals(val, "b");
- Array array = rs.getArray(2);
- // Need to support primitive
- String[] strArr = new String[3];
- strArr[0] = "abc";
- strArr[1] = "defgh";
- strArr[2] = "b";
- Array resultArr = conn.createArrayOf("VARCHAR", strArr);
- assertEquals(resultArr, array);
- // since array is var length, last string element is messed up
- String expectedPrefix = "['abc', 'defgh', 'b";
- assertTrue("Expected to start with " + expectedPrefix,
- rs.getString(2).startsWith(expectedPrefix));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectWithArrayWithColumnRefWithVarLengthArrayWithNullValue() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT b_string,ARRAY['abc',null,'bcd',null,null,b_string] FROM " + table + " where organization_id = '"
- + tenantId + "'";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String val = rs.getString(1);
- assertEquals(val, "b");
- Array array = rs.getArray(2);
- // Need to support primitive
- String[] strArr = new String[6];
- strArr[0] = "abc";
- strArr[1] = null;
- strArr[2] = "bcd";
- strArr[3] = null;
- strArr[4] = null;
- strArr[5] = "b";
- Array resultArr = conn.createArrayOf("VARCHAR", strArr);
- assertEquals(resultArr, array);
- String expectedPrefix = "['abc', null, 'bcd', null, null, 'b";
- assertTrue("Expected to start with " + expectedPrefix,
- rs.getString(2).startsWith(expectedPrefix));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testUpsertSelectWithColumnRef() throws Exception {
-
- String tenantId = getOrganizationId();
- String table1 = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- Connection conn = null;
- try {
- String table2 = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table2, tenantId, null, false);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String query = "upsert into " + table1 + " (ORGANIZATION_ID,ENTITY_ID, a_unsigned_double, a_double_array) "
- + "SELECT organization_id, entity_id, x_double, ARRAY[23.4, 22.1, x_double] FROM " + table2
- + " WHERE a_double_array[2] = 89.96";
- PreparedStatement statement = conn.prepareStatement(query);
- int executeUpdate = statement.executeUpdate();
- assertEquals(1, executeUpdate);
- conn.commit();
- statement.close();
- conn.close();
- // create another connection
- props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- query = "SELECT ARRAY_ELEM(a_double_array,2) FROM " + table1;
- statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 22.1d;
- Double result = rs.getDouble(1);
- assertEquals(result, doubleArr[0]);
- assertFalse(rs.next());
-
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
- }
-
- @Test
- public void testCharArraySpecificIndex() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- initSimpleArrayTable(table, tenantId, null, false);
- String query = "SELECT a_char_array[2] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] charArr = new String[1];
- charArr[0] = "b";
- String result = rs.getString(1);
- assertEquals(charArr[0], result);
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArrayWithDescOrder() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute(
- "CREATE TABLE " + table + " ( k VARCHAR, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4] \n"
- + " CONSTRAINT pk PRIMARY KEY (k, b_string_array DESC)) \n");
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
- Array array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
- array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(3, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT b_string_array FROM " + table);
- assertTrue(rs.next());
- PhoenixArray strArr = (PhoenixArray)rs.getArray(1);
- assertEquals(array, strArr);
- assertEquals("['abc', 'def', 'ghi', 'jkll', null, null, null, 'xxx']", rs.getString(1));
- conn.close();
- }
-
- @Test
- public void testArrayWithFloatArray() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a Float ARRAY[])");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES('a',ARRAY[2.0,3.0])");
- int res = stmt.executeUpdate();
- assertEquals(1, res);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT ARRAY_ELEM(a,2) FROM " + table);
- assertTrue(rs.next());
- Float f = new Float(3.0);
- assertEquals(f, (Float)rs.getFloat(1));
- conn.close();
- }
-
- @Test
- public void testArrayWithVarCharArray() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a VARCHAR ARRAY[])");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES('a',ARRAY['a',null])");
- int res = stmt.executeUpdate();
- assertEquals(1, res);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT ARRAY_ELEM(a,2) FROM " + table);
- assertTrue(rs.next());
- assertEquals(null, rs.getString(1));
- conn.close();
- }
-
- @Test
- public void testArraySelectSingleArrayElemWithCast() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?)");
- stmt.setString(1, "a");
- Long[] s = new Long[] {1l, 2l};
- Array array = conn.createArrayOf("BIGINT", s);
- stmt.setArray(2, array);
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT k, CAST(a[2] AS DOUBLE) FROM " + table);
- assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- Double d = new Double(2.0);
- assertEquals(d, (Double)rs.getDouble(2));
- conn.close();
- }
-
- @Test
- public void testArraySelectGetString() throws Exception {
- Connection conn;
- PreparedStatement stmt;
-
- String tenantId = getOrganizationId();
-
- // create the table
- String tableName = createTableWithAllArrayTypes(getUrl(), getDefaultSplits(tenantId), null);
-
- // populate the table with data
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- stmt =
- conn.prepareStatement("UPSERT INTO "
- + tableName
- + "(ORGANIZATION_ID, ENTITY_ID, BOOLEAN_ARRAY, BYTE_ARRAY, DOUBLE_ARRAY, FLOAT_ARRAY, INT_ARRAY, LONG_ARRAY, SHORT_ARRAY, STRING_ARRAY)\n"
- + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
- stmt.setString(1, tenantId);
- stmt.setString(2, ROW1);
- // boolean array
- Array boolArray = conn.createArrayOf("BOOLEAN", new Boolean[] { true, false });
- int boolIndex = 3;
- stmt.setArray(boolIndex, boolArray);
- // byte array
- Array byteArray = conn.createArrayOf("TINYINT", new Byte[] { 11, 22 });
- int byteIndex = 4;
- stmt.setArray(byteIndex, byteArray);
- // double array
- Array doubleArray = conn.createArrayOf("DOUBLE", new Double[] { 67.78, 78.89 });
- int doubleIndex = 5;
- stmt.setArray(doubleIndex, doubleArray);
- // float array
- Array floatArray = conn.createArrayOf("FLOAT", new Float[] { 12.23f, 45.56f });
- int floatIndex = 6;
- stmt.setArray(floatIndex, floatArray);
- // int array
- Array intArray = conn.createArrayOf("INTEGER", new Integer[] { 5555, 6666 });
- int intIndex = 7;
- stmt.setArray(intIndex, intArray);
- // long array
- Array longArray = conn.createArrayOf("BIGINT", new Long[] { 7777777L, 8888888L });
- int longIndex = 8;
- stmt.setArray(longIndex, longArray);
- // short array
- Array shortArray = conn.createArrayOf("SMALLINT", new Short[] { 333, 444 });
- int shortIndex = 9;
- stmt.setArray(shortIndex, shortArray);
- // create character array
- Array stringArray = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
- int stringIndex = 10;
- stmt.setArray(stringIndex, stringArray);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt =
- conn.prepareStatement("SELECT organization_id, entity_id, boolean_array, byte_array, double_array, float_array, int_array, long_array, short_array, string_array FROM "
- + tableName);
- //TODO: samarth check if this is needed
- analyzeTable(conn, tableName);
-
- ResultSet rs = stmt.executeQuery();
- assertTrue(rs.next());
-
- assertEquals(tenantId, rs.getString(1));
- assertEquals(ROW1, rs.getString(2));
-
- assertArrayGetString(rs, boolIndex, boolArray, "true, false");
- assertArrayGetString(rs, byteIndex, byteArray, "11, 22");
- assertArrayGetString(rs, doubleIndex, doubleArray, "67.78, 78.89");
- assertArrayGetString(rs, floatIndex, floatArray, "12.23, 45.56");
- assertArrayGetString(rs, intIndex, intArray, "5555, 6666");
- assertArrayGetString(rs, longIndex, longArray, "7777777, 8888888");
- assertArrayGetString(rs, shortIndex, shortArray, "333, 444");
- assertArrayGetString(rs, stringIndex, stringArray, "'a', 'b'");
- conn.close();
- }
-
- private void assertArrayGetString(ResultSet rs, int arrayIndex, Array expectedArray, String expectedString)
- throws SQLException {
- assertEquals(expectedArray, rs.getArray(arrayIndex));
- assertEquals("[" + expectedString + "]", rs.getString(arrayIndex));
- }
-
- @Test
- public void testArrayWithCast() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?)");
- stmt.setString(1, "a");
- Long[] s = new Long[] { 1l, 2l };
- Array array = conn.createArrayOf("BIGINT", s);
- stmt.setArray(2, array);
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT CAST(a AS DOUBLE []) FROM " + table);
- assertTrue(rs.next());
- Double[] d = new Double[] { 1.0, 2.0 };
- array = conn.createArrayOf("DOUBLE", d);
- PhoenixArray arr = (PhoenixArray)rs.getArray(1);
- assertEquals(array, arr);
- assertEquals("[1.0, 2.0]", rs.getString(1));
- conn.close();
- }
-
- @Test
- public void testArrayWithCastForVarLengthArr() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a VARCHAR(5) ARRAY)");
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] { "1", "2" };
- PhoenixArray array = (PhoenixArray)conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT CAST(a AS CHAR ARRAY) FROM " + table);
- assertTrue(rs.next());
- PhoenixArray arr = (PhoenixArray)rs.getArray(1);
- String[] array2 = (String[])array.getArray();
- String[] array3 = (String[])arr.getArray();
- assertEquals(array2[0], array3[0]);
- assertEquals(array2[1], array3[1]);
- assertEquals("['1', '2']", rs.getString(1));
- conn.close();
- }
-
- @Test
- public void testFixedWidthCharArray() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement().execute("CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a CHAR(5) ARRAY)");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.getMetaData().getColumns(null, null, table, "A");
- assertTrue(rs.next());
- assertEquals(5, rs.getInt("COLUMN_SIZE"));
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] {"1","2"};
- Array array = conn.createArrayOf("CHAR", s);
- stmt.setArray(2, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT k, a[2] FROM " + table);
- assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals("2",rs.getString(2));
- conn.close();
- }
-
- @Test
- public void testSelectArrayUsingUpsertLikeSyntax() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array FROM " + table + " WHERE a_double_array = CAST(ARRAY [ 25.343, 36.763, 37.56,386.63] AS DOUBLE ARRAY)";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- Double[] doubleArr = new Double[4];
- doubleArr[0] = 25.343;
- doubleArr[1] = 36.763;
- doubleArr[2] = 37.56;
- doubleArr[3] = 386.63;
- Array array = conn.createArrayOf("DOUBLE", doubleArr);
- PhoenixArray resultArray = (PhoenixArray) rs.getArray(1);
- assertEquals(resultArray, array);
- assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArrayIndexUsedInWhereClause() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- int a_index = 0;
- String query = "SELECT a_double_array[2] FROM " + table + " where a_double_array["+a_index+"2]<?";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 40.0;
- conn.createArrayOf("DOUBLE", doubleArr);
- statement.setDouble(1, 40.0d);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- doubleArr = new Double[1];
- doubleArr[0] = 36.763;
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArrayIndexUsedInGroupByClause() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array[2] FROM " + table + " GROUP BY a_double_array[2]";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 40.0;
- conn.createArrayOf("DOUBLE", doubleArr);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- doubleArr = new Double[1];
- doubleArr[0] = 36.763;
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testVariableLengthArrayWithNullValue() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, true, getUrl());
- String query = "SELECT a_string_array[2] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- String result = rs.getString(1);
- assertNull(result);
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSpecificIndexOfAVariableArrayAlongWithAnotherColumn1() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array[3],A_INTEGER FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- String result = rs.getString(1);
- assertEquals(strArr[0], result);
- int a_integer = rs.getInt(2);
- assertEquals(1, a_integer);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSpecificIndexOfAVariableArrayAlongWithAnotherColumn2() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT A_INTEGER, a_string_array[3] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- int a_integer = rs.getInt(1);
- assertEquals(1, a_integer);
- String result = rs.getString(2);
- assertEquals(strArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectMultipleArrayColumns() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array[3], a_double_array[2] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 36.763d;
- Double a_double = rs.getDouble(2);
- assertEquals(doubleArr[0], a_double);
- String result = rs.getString(1);
- assertEquals(strArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSameArrayColumnMultipleTimesWithDifferentIndices() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array[1], a_string_array[2], " +
- "a_string_array[3], a_double_array[1], a_double_array[2], a_double_array[3] " +
- "FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- assertEquals("ABC", rs.getString(1));
- assertEquals("CEDF", rs.getString(2));
- assertEquals("XYZWER", rs.getString(3));
- assertEquals(25.343, rs.getDouble(4), 0.0);
- assertEquals(36.763, rs.getDouble(5), 0.0);
- assertEquals(37.56, rs.getDouble(6), 0.0);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSameArrayColumnMultipleTimesWithSameIndices() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array[3], a_string_array[3] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- String result = rs.getString(1);
- assertEquals(strArr[0], result);
- result = rs.getString(2);
- assertEquals(strArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testSelectSpecificIndexOfAVariableArray() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_string_array[3] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = "XYZWER";
- String result = rs.getString(1);
- assertEquals(strArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testWithOutOfRangeIndex() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT a_double_array[100] FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- PhoenixArray resultArray = (PhoenixArray) rs.getArray(1);
- assertNull(resultArray);
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArrayLengthFunctionForVariableLength() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT ARRAY_LENGTH(a_string_array) FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- int result = rs.getInt(1);
- assertEquals(result, 4);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
-
- @Test
- public void testArrayLengthFunctionForFixedLength() throws Exception {
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- initTablesWithArrays(table, tenantId, null, false, getUrl());
- String query = "SELECT ARRAY_LENGTH(a_double_array) FROM " + table;
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- int result = rs.getInt(1);
- assertEquals(result, 4);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArraySizeRoundtrip() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(),
- getDefaultSplits(tenantId), null);
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- ResultSet rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("x_long_array")));
- assertTrue(rs.next());
- assertEquals(5, rs.getInt("ARRAY_SIZE"));
- assertFalse(rs.next());
-
- rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("a_string_array")));
- assertTrue(rs.next());
- assertEquals(3, rs.getInt("ARRAY_SIZE"));
- assertFalse(rs.next());
-
- rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("a_double_array")));
- assertTrue(rs.next());
- assertEquals(0, rs.getInt("ARRAY_SIZE"));
- assertTrue(rs.wasNull());
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testVarLengthArrComparisonInWhereClauseWithSameArrays() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement()
- .execute(
- "CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] {"abc","def", "ghi","jkl"};
- Array array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- s = new String[] {"abc","def", "ghi","jkl"};
- array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(3, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT k, a_string_array[2] FROM " + table + " where a_string_array=b_string_array");
- assertTrue(rs.next());
- assertEquals("a",rs.getString(1));
- assertEquals("def",rs.getString(2));
- conn.close();
- }
-
- @Test
- public void testVarLengthArrComparisonInWhereClauseWithDiffSizeArrays() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement()
- .execute(
- "CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] { "abc", "def", "ghi", "jkll" };
- Array array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- s = new String[] { "abc", "def", "ghi", "jklm" };
- array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(3, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery(
- "SELECT k, a_string_array[2] FROM " + table + " where a_string_array<b_string_array");
- assertTrue(rs.next());
- assertEquals("a", rs.getString(1));
- assertEquals("def", rs.getString(2));
- conn.close();
- }
-
- @Test
- public void testVarLengthArrComparisonWithNulls() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement()
- .execute(
- "CREATE TABLE " + table + " ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
- Array array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
- array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(3, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery(
- "SELECT k, a_string_array[2] FROM " + table + " where a_string_array>b_string_array");
- assertTrue(rs.next());
- assertEquals("a", rs.getString(1));
- assertEquals("def", rs.getString(2));
- conn.close();
- }
-
- @Test
- public void testUpsertValuesWithNull() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- String query = "upsert into " + table + " (ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
- + "','00A123122312312',null)";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- // at
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- int executeUpdate = statement.executeUpdate();
- assertEquals(1, executeUpdate);
- conn.commit();
- statement.close();
- conn.close();
- // create another connection
- props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- query = "SELECT ARRAY_ELEM(a_double_array,2) FROM " + table;
- statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- // Need to support primitive
- Double[] doubleArr = new Double[1];
- doubleArr[0] = 0.0d;
- conn.createArrayOf("DOUBLE", doubleArr);
- Double result = rs.getDouble(1);
- assertEquals(doubleArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testUpsertValuesWithNullUsingPreparedStmt() throws Exception {
-
- String tenantId = getOrganizationId();
- String table = createTableWithArray(getUrl(), getDefaultSplits(tenantId), null);
- String query = "upsert into " + table + " (ORGANIZATION_ID,ENTITY_ID,a_string_array) values(?, ?, ?)";
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- // at
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- statement.setString(1, tenantId);
- statement.setString(2, "00A123122312312");
- statement.setNull(3, Types.ARRAY);
- int executeUpdate = statement.executeUpdate();
- assertEquals(1, executeUpdate);
- conn.commit();
- statement.close();
- conn.close();
- // create another connection
- props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- query = "SELECT ARRAY_ELEM(a_string_array,1) FROM " + table;
- statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue(rs.next());
- String[] strArr = new String[1];
- strArr[0] = null;
- conn.createArrayOf("VARCHAR", strArr);
- String result = rs.getString(1);
- assertEquals(strArr[0], result);
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testPKWithArray() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement()
- .execute(
- "CREATE TABLE " + table + " ( k VARCHAR, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4] \n"
- + " CONSTRAINT pk PRIMARY KEY (k, b_string_array)) \n");
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?,?,?)");
- stmt.setString(1, "a");
- String[] s = new String[] { "abc", "def", "ghi", "jkll", null, null, "xxx" };
- Array array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(2, array);
- s = new String[] { "abc", "def", "ghi", "jkll", null, null, null, "xxx" };
- array = conn.createArrayOf("VARCHAR", s);
- stmt.setArray(3, array);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery(
- "SELECT k, a_string_array[2] FROM " + table + " where b_string_array[8]='xxx'");
- assertTrue(rs.next());
- assertEquals("a", rs.getString(1));
- assertEquals("def", rs.getString(2));
- conn.close();
- }
-
- @Test
- public void testPKWithArrayNotInEnd() throws Exception {
- Connection conn;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- try {
- conn.createStatement().execute(
- "CREATE TABLE " + table + " ( a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4], k VARCHAR \n"
- + " CONSTRAINT pk PRIMARY KEY (b_string_array, k))");
- conn.close();
- fail();
- } catch (SQLException e) {
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
-
- }
-
- @Test
- public void testArrayRefToLiteral() throws Exception {
- Connection conn;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- try {
- PreparedStatement stmt = conn.prepareStatement("select ?[2] from \"SYSTEM\".\"catalog\" limit 1");
- Array array = conn.createArrayOf("CHAR", new String[] {"a","b","c"});
- stmt.setArray(1, array);
- ResultSet rs = stmt.executeQuery();
- assertTrue(rs.next());
- assertEquals("b", rs.getString(1));
- assertFalse(rs.next());
- } catch (SQLException e) {
- } finally {
- if (conn != null) {
- conn.close();
- }
- }
-
- }
-
- private static String createTableWithAllArrayTypes(String url, byte[][] bs, Object object) throws SQLException {
- String tableName = generateUniqueName();
- String ddlStmt = "create table "
- + tableName
- + " (organization_id char(15) not null, \n"
- + " entity_id char(15) not null,\n"
- + " boolean_array boolean array,\n"
- + " byte_array tinyint array,\n"
- + " double_array double array[],\n"
- + " float_array float array,\n"
- + " int_array integer array,\n"
- + " long_array bigint[5],\n"
- + " short_array smallint array,\n"
- + " string_array varchar(100) array[3],\n"
- + " CONSTRAINT pk PRIMARY KEY (organization_id, entity_id)\n"
- + ")";
- BaseTest.createTestTable(url, ddlStmt, bs, null);
- return tableName;
- }
-
- private static String createTableWithArray(String url, byte[][] bs, Object object) throws SQLException {
- String tableName = generateUniqueName();
- String ddlStmt = "create table "
- + tableName
- + " (organization_id char(15) not null, \n"
- + " entity_id char(15) not null,\n"
- + " a_string_array varchar(100) array[3],\n"
- + " b_string varchar(100),\n"
- + " a_integer integer,\n"
- + " a_date date,\n"
- + " a_time time,\n"
- + " a_timestamp timestamp,\n"
- + " x_decimal decimal(31,10),\n"
- + " x_long_array bigint[5],\n"
- + " x_integer integer,\n"
- + " a_byte_array tinyint array,\n"
- + " a_short smallint,\n"
- + " a_float float,\n"
- + " a_double_array double array[],\n"
- + " a_unsigned_float unsigned_float,\n"
- + " a_unsigned_double unsigned_double \n"
- + " CONSTRAINT pk PRIMARY KEY (organization_id, entity_id)\n"
- + ")";
- BaseTest.createTestTable(url, ddlStmt, bs, null);
- return tableName;
- }
-
- private static String createSimpleTableWithArray(String url, byte[][] bs, Object object) throws SQLException {
- String tableName = generateUniqueName();
- String ddlStmt = "create table "
- + tableName
- + " (organization_id char(15) not null, \n"
- + " entity_id char(15) not null,\n"
- + " x_double double,\n"
- + " a_double_array double array[],\n"
- + " a_char_array char(5) array[],\n"
- + " CONSTRAINT pk PRIMARY KEY (organization_id, entity_id)\n"
- + ")";
- BaseTest.createTestTable(url, ddlStmt, bs, null);
- return tableName;
- }
-
- private static void initSimpleArrayTable(String tableName, String tenantId, Date date, boolean useNull) throws Exception {
- Properties props = new Properties();
-
- Connection conn = DriverManager.getConnection(getUrl(), props);
- try {
- // Insert all rows at ts
- PreparedStatement stmt = conn.prepareStatement(
- "upsert into " + tableName +
- "(" +
- " ORGANIZATION_ID, " +
- " ENTITY_ID, " +
- " x_double, " +
- " a_double_array, a_char_array)" +
- "VALUES (?, ?, ?, ?, ?)");
- stmt.setString(1, tenantId);
- stmt.setString(2, ROW1);
- stmt.setDouble(3, 1.2d);
- // Need to support primitive
- Double[] doubleArr = new Double[2];
- doubleArr[0] = 64.87;
- doubleArr[1] = 89.96;
- //doubleArr[2] = 9.9;
- Array array = conn.createArrayOf("DOUBLE", doubleArr);
- stmt.setArray(4, array);
-
- // create character array
- String[] charArr = new String[2];
- charArr[0] = "a";
- charArr[1] = "b";
- array = conn.createArrayOf("CHAR", charArr);
- stmt.setArray(5, array);
- stmt.execute();
-
- conn.commit();
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testArrayConstructorWithMultipleRows1() throws Exception {
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "CREATE TABLE " + table + " (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
- conn.createStatement().execute(ddl);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('a', 6,3)");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('b', 2,4)");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('c', 6,3)");
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from " + table);
- assertTrue(rs.next());
- assertEquals(2, rs.getInt(1));
- }
-
- @Test
- public void testArrayConstructorWithMultipleRows2() throws Exception {
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "CREATE TABLE " + table + " (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
- conn.createStatement().execute(ddl);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('a', 6,3)");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('b', 2,4)");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('c', 6,3)");
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from " + table + " ");
- assertTrue(rs.next());
- Array arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("INTEGER", new Object[]{2, 4});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- }
-
- @Test
- public void testArrayConstructorWithMultipleRows3() throws Exception {
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "CREATE TABLE " + table + " (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
- conn.createStatement().execute(ddl);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('b', 'abc', 'dfg')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('c', 'foo', 'abc')");
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from " + table + " ");
- assertTrue(rs.next());
- Array arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("VARCHAR", new Object[]{"abc", "dfg"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- }
-
- @Test
- public void testArrayConstructorWithMultipleRows4() throws Exception {
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "CREATE TABLE " + table + " (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
- conn.createStatement().execute(ddl);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('b', 'abc', 'dfg')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('c', 'foo', 'abc')");
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from " + table);
- assertTrue(rs.next());
- assertEquals(2, rs.getInt(1));
- }
-
- @Test
- public void testArrayConstructorWithMultipleRows5() throws Exception {
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "CREATE TABLE " + table + " (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
- conn.createStatement().execute(ddl);
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('b', 'abc', 'dfg')");
- stmt.execute();
- stmt = conn.prepareStatement("UPSERT INTO " + table + " (region_name, a, b) VALUES('c', 'foo', 'abc')");
- stmt.execute();
- conn.commit();
- conn.close();
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(ARRAY[a,b], 'oo') from " + table);
- assertTrue(rs.next());
- Array arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc", "oo"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("VARCHAR", new Object[]{"abc", "dfg", "oo"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc", "oo"});
- assertEquals(arr, rs.getArray(1));
- rs.next();
- }
-
- @Test
- public void testPKWithDescArray() throws Exception {
- Connection conn;
- PreparedStatement stmt;
- ResultSet rs;
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- conn.createStatement()
- .execute(
- "CREATE TABLE " + table + " ( a VARCHAR ARRAY PRIMARY KEY DESC)\n");
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?)");
- Array a1 = conn.createArrayOf("VARCHAR", new String[] { "a", "ba" });
- stmt.setArray(1, a1);
- stmt.execute();
- Array a2 = conn.createArrayOf("VARCHAR", new String[] { "a", "c" });
- stmt.setArray(1, a2);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT a FROM " + table + " ORDER BY a DESC");
- assertTrue(rs.next());
- assertEquals(a2, rs.getArray(1));
- assertTrue(rs.next());
- assertEquals(a1, rs.getArray(1));
- assertFalse(rs.next());
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?)");
- Array a3 = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
- stmt.setArray(1, a3);
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- rs = conn.createStatement().executeQuery("SELECT a FROM " + table + " ORDER BY a DESC");
- assertTrue(rs.next());
- assertEquals(a2, rs.getArray(1));
- assertTrue(rs.next());
- assertEquals(a1, rs.getArray(1));
- assertTrue(rs.next());
- assertEquals(a3, rs.getArray(1));
- assertFalse(rs.next());
- conn.close();
- }
-
- @Test
- public void testComparisonOperatorsForDesc1()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'c'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k >= array['a', 'b']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc2()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'c'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k >= array['a', 'c']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc3()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'c'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k > array['a', 'b']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc4()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'b'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k <= array['a', 'c']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc5()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'b'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k <= array['a', 'b']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc6()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k varchar array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array['a', 'b'])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k < array['a', 'c']");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc7()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName();
- String ddl = "create table " + table + " (k integer array primary key desc)";
- conn.createStatement().execute(ddl);
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- PreparedStatement stmt = conn.prepareStatement("upsert into " + table + " values (array[1, 2])");
- stmt.execute();
- conn.commit();
- conn.close();
-
- conn = DriverManager.getConnection(getUrl(), props);
- ResultSet rs;
- stmt = conn.prepareStatement("select * from " + table + " where k < array[1, 4]");
- rs = stmt.executeQuery();
- assertTrue(rs.next());
- }
-
- @Test
- public void testComparisonOperatorsForDesc8()throws Exception{
-
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String table = generateUniqueName
<TRUNCATED>