| /* |
| * 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(); |
| } |
| |
| } |