PHOENIX-4244 Breakup ArrayIT into several integration tests so as not to create too many tables in one test
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/527f786a Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/527f786a Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/527f786a Branch: refs/heads/master Commit: 527f786ac4b4883677fa77d0e31c71ea7d518872 Parents: 06f58d5 Author: James Taylor <[email protected]> Authored: Thu Sep 28 13:18:15 2017 -0700 Committer: James Taylor <[email protected]> Committed: Thu Sep 28 17:51:57 2017 -0700 ---------------------------------------------------------------------- .../org/apache/phoenix/end2end/Array1IT.java | 1004 +++++++ .../org/apache/phoenix/end2end/Array2IT.java | 815 ++++++ .../org/apache/phoenix/end2end/Array3IT.java | 770 ++++++ .../org/apache/phoenix/end2end/ArrayIT.java | 2543 +----------------- 4 files changed, 2617 insertions(+), 2515 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/527f786a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array1IT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array1IT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array1IT.java new file mode 100644 index 0000000..20f4422 --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array1IT.java @@ -0,0 +1,1004 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package 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.assertTrue; + +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.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.TestUtil; +import org.junit.Test; + +import com.google.common.primitives.Floats; + +public class Array1IT extends ArrayIT { + private void assertArrayGetString(ResultSet rs, int arrayIndex, Array expectedArray, String expectedString) + throws SQLException { + assertEquals(expectedArray, rs.getArray(arrayIndex)); + assertEquals("[" + expectedString + "]", rs.getString(arrayIndex)); + } + + 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 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 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); + TestUtil.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(); + } + } + + @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); + TestUtil.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); + TestUtil.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(); + } + + @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(); + } + +} http://git-wip-us.apache.org/repos/asf/phoenix/blob/527f786a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array2IT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array2IT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array2IT.java new file mode 100644 index 0000000..52bfb86 --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array2IT.java @@ -0,0 +1,815 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.end2end; + +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.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.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; + +public class Array2IT extends ArrayIT { + @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(); + } + } + + } + + @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)); + } + +}
