/*
* 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.PhoenixRuntime.CURRENT_SCN_ATTRIB;
import static org.apache.phoenix.util.TestUtil.B_VALUE;
import static org.apache.phoenix.util.TestUtil.ROW1;
import static org.apache.phoenix.util.TestUtil.TABLE_WITH_ARRAY;
import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Properties;
import org.apache.phoenix.query.BaseTest;
import org.apache.phoenix.schema.types.PhoenixArray;
import org.apache.phoenix.util.PhoenixRuntime;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.SchemaUtil;
import org.apache.phoenix.util.StringUtil;
import org.junit.Test;
import com.google.common.primitives.Floats;
public class ArrayIT extends BaseClientManagedTimeIT {
private static final String SIMPLE_TABLE_WITH_ARRAY = "SIMPLE_TABLE_WITH_ARRAY";
private static void initTablesWithArrays(String tenantId, Date date, Long ts, boolean useNull, String url) throws Exception {
Properties props = new Properties();
if (ts != null) {
props.setProperty(CURRENT_SCN_ATTRIB, ts.toString());
}
Connection conn = DriverManager.getConnection(url, props);
try {
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " +
"TABLE_WITH_ARRAY(" +
" ORGANIZATION_ID, " +
" ENTITY_ID, " +
" a_string_array, " +
" B_STRING, " +
" A_INTEGER, " +
" A_DATE, " +
" X_DECIMAL, " +
" x_long_array, " +
" X_INTEGER," +
" a_byte_array," +
" A_SHORT," +
" A_FLOAT," +
" a_double_array," +
" A_UNSIGNED_FLOAT," +
" A_UNSIGNED_DOUBLE)" +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, tenantId);
stmt.setString(2, ROW1);
// Need to support primitive
String[] strArr = new String[4];
strArr[0] = "ABC";
if (useNull) {
strArr[1] = null;
} else {
strArr[1] = "CEDF";
}
strArr[2] = "XYZWER";
strArr[3] = "AB";
Array array = conn.createArrayOf("VARCHAR", strArr);
stmt.setArray(3, array);
stmt.setString(4, B_VALUE);
stmt.setInt(5, 1);
stmt.setDate(6, date);
stmt.setBigDecimal(7, null);
// Need to support primitive
Long[] longArr = new Long[2];
longArr[0] = 25l;
longArr[1] = 36l;
array = conn.createArrayOf("BIGINT", longArr);
stmt.setArray(8, array);
stmt.setNull(9, Types.INTEGER);
// Need to support primitive
Byte[] byteArr = new Byte[2];
byteArr[0] = 25;
byteArr[1] = 36;
array = conn.createArrayOf("TINYINT", byteArr);
stmt.setArray(10, array);
stmt.setShort(11, (short) 128);
stmt.setFloat(12, 0.01f);
// 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);
stmt.setArray(13, array);
stmt.setFloat(14, 0.01f);
stmt.setDouble(15, 0.0001);
stmt.execute();
conn.commit();
} finally {
conn.close();
}
}
@Test
public void testScanByArrayValue() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM table_with_array WHERE ?=organization_id and ?=a_float";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(getUrl(), props);
analyzeTable(conn, TABLE_WITH_ARRAY);
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(rs.getString("B_string"), B_VALUE);
assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
assertFalse(rs.next());
} finally {
conn.close();
}
}
private void analyzeTable(Connection conn, String tableWithArray) throws SQLException {
String analyse = "UPDATE STATISTICS "+tableWithArray;
PreparedStatement statement = conn.prepareStatement(analyse);
statement.execute();
}
@Test
public void testScanWithArrayInWhereClause() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM table_with_array WHERE ?=organization_id and ?=a_byte_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(getUrl(), props);
analyzeTable(conn, TABLE_WITH_ARRAY);
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(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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array, /* comment ok? */ b_string, a_float FROM table_with_array WHERE ?=organization_id and ?=a_string_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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(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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_string_array FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSelectSpecificIndexOfAnArrayAsArrayFunction()
throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array[3] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT CASE WHEN A_INTEGER = 1 THEN a_double_array ELSE null END [3] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
String query = "upsert into table_with_array(ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
+ "','00A123122312312',ARRAY[2.0,345.8])";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); // Execute
// 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);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "upsert into table_with_array(ORGANIZATION_ID,ENTITY_ID,a_double_array) "
+ "SELECT organization_id, entity_id, a_double_array FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " 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);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
conn = DriverManager.getConnection(getUrl(), props);
query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_double_array[1] FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " WHERE a_double_array[2] = 89.96d 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_double_array[1] FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " WHERE 89.96d = 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_double_array[1] FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " WHERE 64.87d = 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_double_array[1] FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " WHERE a_char_array[0] = 'f' or 89.96d > 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_double_array[1], a_double_array[2] FROM " + SIMPLE_TABLE_WITH_ARRAY
+ " WHERE a_char_array[0] = 'f' or 100.0d > 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "SELECT a_string_array[1] FROM " + TABLE_WITH_ARRAY
+ " 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_WITH_ARRAY + " 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_integer,ARRAY[1,2,a_integer] FROM table_with_array where organization_id = '"
+ tenantId + "'";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
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);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSelectWithArrayWithColumnRefWithVarLengthArray() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT b_string,ARRAY['abc','defgh',b_string] FROM table_with_array where organization_id = '"
+ tenantId + "'";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
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);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testSelectWithArrayWithColumnRefWithVarLengthArrayWithNullValue() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT b_string,ARRAY['abc',null,'bcd',null,null,b_string] FROM table_with_array where organization_id = '"
+ tenantId + "'";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
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);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testUpsertSelectWithColumnRef() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
Connection conn = null;
try {
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
String query = "upsert into table_with_array(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 " + SIMPLE_TABLE_WITH_ARRAY
+ " 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);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
conn = DriverManager.getConnection(getUrl(), props);
query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createSimpleTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
initSimpleArrayTable(tenantId, null, ts, false);
String query = "SELECT a_char_array[2] FROM SIMPLE_TABLE_WITH_ARRAY";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute(
"CREATE TABLE t ( 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT b_string_array FROM t");
assertTrue(rs.next());
PhoenixArray strArr = (PhoenixArray)rs.getArray(1);
assertEquals(array, strArr);
conn.close();
}
@Test
public void testArrayWithFloatArray() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a Float ARRAY[])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t VALUES('a',ARRAY[2.0,3.0])");
int res = stmt.executeUpdate();
assertEquals(1, res);
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT ARRAY_ELEM(a,2) FROM t");
assertTrue(rs.next());
Float f = new Float(3.0);
assertEquals(f, (Float)rs.getFloat(1));
conn.close();
}
@Test
public void testArraySelectSingleArrayElemWithCast() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT k, CAST(a[2] AS DOUBLE) FROM t");
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 testArrayWithCast() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a bigint ARRAY[])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT CAST(a AS DOUBLE []) FROM t");
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);
conn.close();
}
@Test
public void testArrayWithCastForVarLengthArr() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a VARCHAR(5) ARRAY)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT CAST(a AS CHAR ARRAY) FROM t");
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]);
conn.close();
}
@Test
public void testFixedWidthCharArray() throws Exception {
Connection conn;
PreparedStatement stmt;
ResultSet rs;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement().execute("CREATE TABLE t ( k VARCHAR PRIMARY KEY, a CHAR(5) ARRAY)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.getMetaData().getColumns(null, null, "T", "A");
assertTrue(rs.next());
assertEquals(5, rs.getInt("COLUMN_SIZE"));
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT k, a[2] FROM t");
assertTrue(rs.next());
assertEquals("a",rs.getString(1));
assertEquals("2",rs.getString(2));
conn.close();
}
@Test
public void testSelectArrayUsingUpsertLikeSyntax() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array FROM TABLE_WITH_ARRAY WHERE a_double_array = ARRAY [ 25.343d, 36.763d, 37.56d,386.63d]";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Test
public void testArrayIndexUsedInWhereClause() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
int a_index = 0;
String query = "SELECT a_double_array[2] FROM table_with_array where a_double_array["+a_index+"2]<?";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array[2] FROM table_with_array GROUP BY a_double_array[2]";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, true, getUrl());
String query = "SELECT a_string_array[2] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_string_array[3],A_INTEGER FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT A_INTEGER, a_string_array[3] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_string_array[3], a_double_array[2] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, 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_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_string_array[3], a_string_array[3] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_string_array[3] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT a_double_array[100] FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT ARRAY_LENGTH(a_string_array) FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
initTablesWithArrays(tenantId, null, ts, false, getUrl());
String query = "SELECT ARRAY_LENGTH(a_double_array) FROM table_with_array";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(),
getDefaultSplits(tenantId), null, ts - 2);
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
ResultSet rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(TABLE_WITH_ARRAY), 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_WITH_ARRAY), 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_WITH_ARRAY), 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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"CREATE TABLE t_same_size ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t_same_size 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery("SELECT k, a_string_array[2] FROM t_same_size 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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"CREATE TABLE t ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery(
"SELECT k, a_string_array[2] FROM t 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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"CREATE TABLE t ( k VARCHAR PRIMARY KEY, a_string_array VARCHAR(100) ARRAY[4], b_string_array VARCHAR(100) ARRAY[4])");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery(
"SELECT k, a_string_array[2] FROM t 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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
String query = "upsert into table_with_array(ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
+ "','00A123122312312',null)";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); // Execute
// 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);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
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 {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
String query = "upsert into table_with_array(ORGANIZATION_ID,ENTITY_ID,a_string_array) values(?, ?, ?)";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); // Execute
// 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);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
conn = DriverManager.getConnection(getUrl(), props);
query = "SELECT ARRAY_ELEM(a_string_array,1) FROM table_with_array";
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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute(
"CREATE TABLE t ( 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
conn = DriverManager.getConnection(getUrl(), props);
stmt = conn.prepareStatement("UPSERT INTO t 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();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
conn = DriverManager.getConnection(getUrl(), props);
rs = conn.createStatement().executeQuery(
"SELECT k, a_string_array[2] FROM t 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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
try {
conn.createStatement().execute(
"CREATE TABLE t ( 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;
long ts = nextTimestamp();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
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();
}
}
}
static void createTableWithArray(String url, byte[][] bs, Object object,
long ts) throws SQLException {
String ddlStmt = "create table "
+ TABLE_WITH_ARRAY
+ " (organization_id char(15) not null, \n"
+ " entity_id char(15) not null,\n"
+ " a_string_array varchar(100) array[3],\n"
+ " b_string varchar(100),\n"
+ " a_integer integer,\n"
+ " a_date date,\n"
+ " a_time time,\n"
+ " a_timestamp timestamp,\n"
+ " x_decimal decimal(31,10),\n"
+ " x_long_array bigint[5],\n"
+ " x_integer integer,\n"
+ " a_byte_array tinyint array,\n"
+ " a_short smallint,\n"
+ " a_float float,\n"
+ " a_double_array double array[],\n"
+ " a_unsigned_float unsigned_float,\n"
+ " a_unsigned_double unsigned_double \n"
+ " CONSTRAINT pk PRIMARY KEY (organization_id, entity_id)\n"
+ ")";
BaseTest.createTestTable(url, ddlStmt, bs, ts);
}
static void createSimpleTableWithArray(String url, byte[][] bs, Object object,
long ts) throws SQLException {
String ddlStmt = "create table "
+ SIMPLE_TABLE_WITH_ARRAY
+ " (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, ts);
}
protected static void initSimpleArrayTable(String tenantId, Date date, Long ts, boolean useNull) throws Exception {
Properties props = new Properties();
if (ts != null) {
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, ts.toString());
}
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
// Insert all rows at ts
PreparedStatement stmt = conn.prepareStatement(
"upsert into " +SIMPLE_TABLE_WITH_ARRAY+
"(" +
" 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();
}
}
}
