http://git-wip-us.apache.org/repos/asf/phoenix/blob/527f786a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array3IT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array3IT.java
b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array3IT.java
new file mode 100644
index 0000000..73c7e63
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/Array3IT.java
@@ -0,0 +1,770 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Array;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+public class Array3IT extends ArrayIT {
+
+ @Test
+ public void testArrayConstructorWithMultipleRows5() throws Exception {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (region_name VARCHAR
PRIMARY KEY, a VARCHAR, b VARCHAR)";
+ conn.createStatement().execute(ddl);
+ conn.commit();
+ conn.close();
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " (region_name, a, b) VALUES('a', 'foo', 'abc')");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + "
(region_name, a, b) VALUES('b', 'abc', 'dfg')");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + "
(region_name, a, b) VALUES('c', 'foo', 'abc')");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ rs = conn.createStatement().executeQuery("SELECT
ARRAY_APPEND(ARRAY[a,b], 'oo') from " + table);
+ assertTrue(rs.next());
+ Array arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc",
"oo"});
+ assertEquals(arr, rs.getArray(1));
+ rs.next();
+ arr = conn.createArrayOf("VARCHAR", new Object[]{"abc", "dfg", "oo"});
+ assertEquals(arr, rs.getArray(1));
+ rs.next();
+ arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc", "oo"});
+ assertEquals(arr, rs.getArray(1));
+ rs.next();
+ }
+
+ @Test
+ public void testPKWithDescArray() throws Exception {
+ Connection conn;
+ PreparedStatement stmt;
+ ResultSet rs;
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ conn.createStatement()
+ .execute(
+ "CREATE TABLE " + table + " ( a VARCHAR ARRAY
PRIMARY KEY DESC)\n");
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?)");
+ Array a1 = conn.createArrayOf("VARCHAR", new String[] { "a", "ba" });
+ stmt.setArray(1, a1);
+ stmt.execute();
+ Array a2 = conn.createArrayOf("VARCHAR", new String[] { "a", "c" });
+ stmt.setArray(1, a2);
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ rs = conn.createStatement().executeQuery("SELECT a FROM " + table +
" ORDER BY a DESC");
+ assertTrue(rs.next());
+ assertEquals(a2, rs.getArray(1));
+ assertTrue(rs.next());
+ assertEquals(a1, rs.getArray(1));
+ assertFalse(rs.next());
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES(?)");
+ Array a3 = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
+ stmt.setArray(1, a3);
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ rs = conn.createStatement().executeQuery("SELECT a FROM " + table +
" ORDER BY a DESC");
+ assertTrue(rs.next());
+ assertEquals(a2, rs.getArray(1));
+ assertTrue(rs.next());
+ assertEquals(a1, rs.getArray(1));
+ assertTrue(rs.next());
+ assertEquals(a3, rs.getArray(1));
+ assertFalse(rs.next());
+ conn.close();
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc1()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'c'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k
>= array['a', 'b']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc2()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'c'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k
>= array['a', 'c']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc3()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'c'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k >
array['a', 'b']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc4()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'b'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k
<= array['a', 'c']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc5()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'b'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k
<= array['a', 'b']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc6()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k varchar array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array['a', 'b'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k <
array['a', 'c']");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc7()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k integer array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array[1, 2])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k <
array[1, 4]");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testComparisonOperatorsForDesc8()throws Exception{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "create table " + table + " (k integer array primary
key desc)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("upsert into " +
table + " values (array[1, 2])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("select * from " + table + " where k
<= array[1, 2]");
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ }
+
+ @Test
+ public void testServerArrayElementProjection1() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1] FROM " +
table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals("a", rs.getString(3));
+ }
+
+ @Test
+ public void testServerArrayElementProjection2() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1], arr3[1]
from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals("a", rs.getString(3));
+ assertEquals(2, rs.getInt(4));
+ }
+
+ @Test
+ public void testServerArrayElementProjection3() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1], arr3,
arr3[1] from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals("a", rs.getString(3));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{2, 3}),
rs.getArray(4));
+ assertEquals(2, rs.getInt(5));
+ }
+
+ @Test
+ public void testServerArrayElementProjection4() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1],
ARRAY_APPEND(arr3, 4), arr3[1] from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals("a", rs.getString(3));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{2, 3, 4}),
rs.getArray(4));
+ assertEquals(2, rs.getInt(5));
+ }
+
+ @Test
+ public void testServerArrayElementProjection5() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr3 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], ARRAY_APPEND(arr1,
arr3[1]) from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 2}),
rs.getArray(3));
+ }
+
+ @Test
+ public void testServerArrayElementProjection6() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + table
+ " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1], ARRAY_APPEND(arr1,
arr2[1]), p from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 2}),
rs.getArray(3));
+ assertEquals(1, rs.getInt(4));
+ }
+
+ @Test
+ public void testServerArrayElementProjection7() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1],
ARRAY_APPEND(ARRAY_APPEND(arr1, arr2[2]), arr2[1]), p from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 3, 2}),
rs.getArray(3));
+ assertEquals(1, rs.getInt(4));
+ }
+
+ @Test
+ public void testServerArrayElementProjection8() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1],
ARRAY_ELEM(ARRAY_APPEND(arr1, arr2[1]), 1), p, arr2[2] from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(1, rs.getInt(4));
+ assertEquals(3, rs.getInt(5));
+ }
+
+ @Test
+ public void testServerArrayElementProjection9() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER ARRAY PRIMARY
KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (ARRAY[5, 6], ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1, arr1[1],
ARRAY_ELEM(ARRAY_APPEND(arr1, arr2[1]), 1), p, arr2[2] from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2}),
rs.getArray(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{5, 6}),
rs.getArray(4));
+ assertEquals(3, rs.getInt(5));
+ }
+
+ @Test
+ public void testServerArrayElementProjection10() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT arr1[1] + 5, arr2[1] FROM " +
table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(6, rs.getInt(1));
+ assertEquals(2, rs.getInt(2));
+ }
+
+ @Test
+ public void testServerArrayElementProjection11() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (2,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (3,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (4,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT CASE WHEN p = 1 THEN arr1[1] WHEN
p = 2 THEN arr1[2] WHEN p = 3 THEN arr2[1] ELSE arr2[2] END FROM " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testServerArrayElementProjection12() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (2,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (3,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (4,
ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT CASE WHEN p = 1 THEN arr1[1] WHEN
p = 2 THEN arr1[2] WHEN p = 3 THEN arr2[1] ELSE arr2[2] END, ARRAY_APPEND(arr1,
arr1[1]) FROM " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 1}),
rs.getArray(2));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 1}),
rs.getArray(2));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 1}),
rs.getArray(2));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{1, 2, 1}),
rs.getArray(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testServerArrayElementProjection13() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER PRIMARY KEY,
arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (2,
ARRAY[3, 2], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (3,
ARRAY[3, 5], ARRAY[2, 3])");
+ stmt.execute();
+ stmt = conn.prepareStatement("UPSERT INTO " + table + " VALUES (4,
ARRAY[3, 5], ARRAY[6, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT CASE WHEN arr1[1] = 1 THEN 1 WHEN
arr1[2] = 2 THEN 2 WHEN arr2[1] = 2 THEN 2 ELSE arr2[2] END FROM " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(3, rs.getInt(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testServerArrayElementProjection14() throws SQLException {
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (p INTEGER ARRAY PRIMARY
KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (ARRAY[5, 6], ARRAY[1, 2], ARRAY[2, 3])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT ARRAY_ELEM(ARRAY_PREPEND(arr2[1],
ARRAY_CAT(arr1, ARRAY[arr2[2],3])), 1), arr1[1], ARRAY_ELEM(ARRAY_APPEND(arr1,
arr2[1]), 1), p, arr2[2] from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(2, rs.getInt(1));
+ assertEquals(1, rs.getInt(2));
+ assertEquals(1, rs.getInt(3));
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[]{5, 6}),
rs.getArray(4));
+ assertEquals(3, rs.getInt(5));
+ }
+
+ @Test
+ public void testCharPrimaryKey() throws SQLException{
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ String table = generateUniqueName();
+ String ddl = "CREATE TABLE " + table + " (testCharArray CHAR(3)[],
CONSTRAINT test_pk PRIMARY KEY(testCharArray)) DEFAULT_COLUMN_FAMILY='T'";
+ conn.createStatement().execute(ddl);
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " +
table + " VALUES (ARRAY['aaa', 'bbb', 'ccc'])");
+ stmt.execute();
+ conn.commit();
+ conn.close();
+
+ conn = DriverManager.getConnection(getUrl(), props);
+ ResultSet rs;
+ stmt = conn.prepareStatement("SELECT testCharArray from " + table);
+ rs = stmt.executeQuery();
+ assertTrue(rs.next());
+ assertEquals(conn.createArrayOf("CHAR", new String[]{"aaa", "bbb",
"ccc"}), rs.getArray(1));
+ }
+
+ @Test
+ public void testArrayIndexFunctionForImmutableTable() throws Exception {
+ String tableName = generateUniqueName();
+
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String ddl = "CREATE IMMUTABLE TABLE " + tableName + "
(region_name VARCHAR PRIMARY KEY, ZIP VARCHAR ARRAY[10])";
+ conn.createStatement().execute(ddl);
+ }
+ props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.createStatement().executeUpdate("UPSERT INTO " + tableName +
" (region_name,zip) VALUES('SF Bay Area',ARRAY['94115','94030','94125'])");
+ conn.commit();
+ }
+ props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ String sql = "SELECT ZIP[2] FROM " + tableName;
+ try (ResultSet rs = conn.createStatement().executeQuery(sql)) {
+ assertTrue(rs.next());
+ assertEquals("94030", rs.getString(1));
+ }
+ }
+ }
+}
