/*
 * 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 final String TABLE_WITH_ALL_ARRAY_TYPES = "TABLE_WITH_ALL_ARRAY_TYPES";

    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("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
			assertEquals(rs.getString("B_string"), B_VALUE);
			assertTrue(Floats.compare(rs.getFloat(3), 0.01f) == 0);
			assertFalse(rs.next());
		} finally {
			conn.close();
		}
	}

    private void analyzeTable(Connection conn, String tableWithArray) throws SQLException {
        String analyse = "UPDATE STATISTICS  "+tableWithArray;
		PreparedStatement statement = conn.prepareStatement(analyse);
        statement.execute();
    }

	@Test
	public void testScanWithArrayInWhereClause() throws Exception {
		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("[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 {
		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("[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 {
		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);
            assertEquals("['ABC', 'CEDF', 'XYZWER', 'AB']", rs.getString(1));
			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.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 {
        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 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 {
        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 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 {
        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 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 {
        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 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 {
            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);
            assertEquals("[1, 2, 1]", rs.getString(2));
            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);
            // 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 {
        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);
            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 {
        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);
        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;
        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 testArrayWithVarCharArray() 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 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['a',null])");
        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());
        assertEquals(null, rs.getString(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 testArraySelectGetString() throws Exception {
        Connection conn;
        PreparedStatement stmt;
        long ts = nextTimestamp();
        String tenantId = getOrganizationId();

        // create the table
        createTableWithAllArrayTypes(getUrl(), getDefaultSplits(tenantId), null, ts - 2);

        // populate the table with data
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
        conn = DriverManager.getConnection(getUrl(), props);
        stmt =
                conn.prepareStatement("UPSERT INTO "
                        + TABLE_WITH_ALL_ARRAY_TYPES
                        + "(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();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        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 "
                        + TABLE_WITH_ALL_ARRAY_TYPES);
        analyzeTable(conn, TABLE_WITH_ALL_ARRAY_TYPES);

        ResultSet rs = stmt.executeQuery();
        assertTrue(rs.next());

        assertEquals(tenantId, rs.getString(1));
        assertEquals(ROW1, rs.getString(2));
        
        assertArrayGetString(rs, boolIndex, boolArray, "true, false");
        assertArrayGetString(rs, byteIndex, byteArray, "11, 22");
        assertArrayGetString(rs, doubleIndex, doubleArray, "67.78, 78.89");
        assertArrayGetString(rs, floatIndex, floatArray, "12.23, 45.56");
        assertArrayGetString(rs, intIndex, intArray, "5555, 6666");
        assertArrayGetString(rs, longIndex, longArray, "7777777, 8888888");
        assertArrayGetString(rs, shortIndex, shortArray, "333, 444");
        assertArrayGetString(rs, stringIndex, stringArray, "'a', 'b'");
        conn.close();
    }

    private void assertArrayGetString(ResultSet rs, int arrayIndex, Array expectedArray, String expectedString)
            throws SQLException {
        assertEquals(expectedArray, rs.getArray(arrayIndex));
        assertEquals("[" + expectedString + "]", rs.getString(arrayIndex));
    }
    
    @Test
    public void testArrayWithCast() throws Exception {
        Connection conn;
        PreparedStatement stmt;
        ResultSet rs;
        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);
        assertEquals("[1.0, 2.0]", rs.getString(1));
        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]);
        assertEquals("['1', '2']", rs.getString(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 = CAST(ARRAY [ 25.343, 36.763, 37.56,386.63] AS DOUBLE 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());
			Double[] doubleArr =  new Double[4];
            doubleArr[0] = 25.343;
            doubleArr[1] = 36.763;
            doubleArr[2] = 37.56;
            doubleArr[3] = 386.63;
			Array array = conn.createArrayOf("DOUBLE", doubleArr);
			PhoenixArray resultArray = (PhoenixArray) rs.getArray(1);
			assertEquals(resultArray, array);
            assertEquals("[25.343, 36.763, 37.56, 386.63]", rs.getString(1));
			assertFalse(rs.next());
		} finally {
			conn.close();
		}
	}

	@Test
	public void testArrayIndexUsedInWhereClause() throws Exception {
		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();
            }
        }

    }
    
    private static void createTableWithAllArrayTypes(String url, byte[][] bs, Object object,
            long ts) throws SQLException {
        String ddlStmt = "create table "
                + TABLE_WITH_ALL_ARRAY_TYPES
                + "   (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, ts);
    }
    
    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();
        }
   }

    @Test
    public void testArrayConstructorWithMultipleRows1() throws Exception {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE regions1 (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
        conn.createStatement().execute(ddl);
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions1(region_name, a, b) VALUES('a', 6,3)");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions1(region_name, a, b) VALUES('b', 2,4)");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions1(region_name, a, b) VALUES('c', 6,3)");
        stmt.execute();
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from regions1");
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
    }

    @Test
    public void testArrayConstructorWithMultipleRows2() throws Exception {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE regions2 (region_name VARCHAR PRIMARY KEY, a INTEGER, b INTEGER)";
        conn.createStatement().execute(ddl);
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions2(region_name, a, b) VALUES('a', 6,3)");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions2(region_name, a, b) VALUES('b', 2,4)");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions2(region_name, a, b) VALUES('c', 6,3)");
        stmt.execute();
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from regions2");
        assertTrue(rs.next());
        Array arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
        assertEquals(arr, rs.getArray(1));
        rs.next();
        arr = conn.createArrayOf("INTEGER", new Object[]{2, 4});
        assertEquals(arr, rs.getArray(1));
        rs.next();
        arr = conn.createArrayOf("INTEGER", new Object[]{6, 3});
        assertEquals(arr, rs.getArray(1));
        rs.next();
    }

    @Test
    public void testArrayConstructorWithMultipleRows3() throws Exception {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE regions3 (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
        conn.createStatement().execute(ddl);
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions3(region_name, a, b) VALUES('a', 'foo', 'abc')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions3(region_name, a, b) VALUES('b', 'abc', 'dfg')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions3(region_name, a, b) VALUES('c', 'foo', 'abc')");
        stmt.execute();
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT ARRAY[a,b] from regions3");
        assertTrue(rs.next());
        Array arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc"});
        assertEquals(arr, rs.getArray(1));
        rs.next();
        arr = conn.createArrayOf("VARCHAR", new Object[]{"abc", "dfg"});
        assertEquals(arr, rs.getArray(1));
        rs.next();
        arr = conn.createArrayOf("VARCHAR", new Object[]{"foo", "abc"});
        assertEquals(arr, rs.getArray(1));
        rs.next();
    }

    @Test
    public void testArrayConstructorWithMultipleRows4() throws Exception {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE regions4 (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
        conn.createStatement().execute(ddl);
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions4(region_name, a, b) VALUES('a', 'foo', 'abc')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions4(region_name, a, b) VALUES('b', 'abc', 'dfg')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions4(region_name, a, b) VALUES('c', 'foo', 'abc')");
        stmt.execute();
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT COUNT(DISTINCT ARRAY[a,b]) from regions4");
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
    }

    @Test
    public void testArrayConstructorWithMultipleRows5() throws Exception {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE regions5 (region_name VARCHAR PRIMARY KEY, a VARCHAR, b VARCHAR)";
        conn.createStatement().execute(ddl);
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('a', 'foo', 'abc')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('b', 'abc', 'dfg')");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO regions5(region_name, a, b) VALUES('c', 'foo', 'abc')");
        stmt.execute();
        conn.commit();
        conn.close();
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        rs = conn.createStatement().executeQuery("SELECT ARRAY_APPEND(ARRAY[a,b], 'oo') from regions5");
        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;
        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 ( a VARCHAR ARRAY PRIMARY KEY 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(?)");
        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();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        rs = conn.createStatement().executeQuery("SELECT a FROM t 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();
        
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 50));
        conn = DriverManager.getConnection(getUrl(), props);
        stmt = conn.prepareStatement("UPSERT INTO t VALUES(?)");
        Array a3 = conn.createArrayOf("VARCHAR", new String[] { "a", "b" });
        stmt.setArray(1, a3);
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 60));
        conn = DriverManager.getConnection(getUrl(), props);
        rs = conn.createStatement().executeQuery("SELECT a FROM t 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{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'c'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k >= array['a', 'b']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc2()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'c'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k >= array['a', 'c']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc3()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'c'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k > array['a', 'b']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc4()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'b'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k <= array['a', 'c']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc5()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'b'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k <= array['a', 'b']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc6()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k varchar array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array['a', 'b'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k < array['a', 'c']");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc7()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k integer array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array[1, 2])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k < array[1, 4]");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testComparisonOperatorsForDesc8()throws Exception{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "create table a (k integer array primary key desc)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("upsert into a values (array[1, 2])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("select * from a where k <= array[1, 2]");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
    }

    @Test
    public void testServerArrayElementProjection1() throws SQLException {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1] FROM a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1], arr3[1] from a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1], arr3, arr3[1] from a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 VARCHAR ARRAY, arr3 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY['a', 'b'], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], arr2[1], ARRAY_APPEND(arr3, 4), arr3[1] from a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr3 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], ARRAY_APPEND(arr1, arr3[1]) from a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1, arr1[1], ARRAY_APPEND(arr1, arr2[1]), p from a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER ARRAY PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (ARRAY[5, 6], ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT arr1[1] + 5, arr2[1] FROM a");
        rs = stmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(6, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
    }

    @Test
    public void testServerArrayElementProjection11() throws SQLException {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (2, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (3, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (4, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (2, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (3, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (4, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (1, ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (2, ARRAY[3, 2], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (3, ARRAY[3, 5], ARRAY[2, 3])");
        stmt.execute();
        stmt = conn.prepareStatement("UPSERT INTO a VALUES (4, ARRAY[3, 5], ARRAY[6, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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 {
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE a (p INTEGER ARRAY PRIMARY KEY, arr1 INTEGER ARRAY, arr2 INTEGER ARRAY)";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO a VALUES (ARRAY[5, 6], ARRAY[1, 2], ARRAY[2, 3])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        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 a");
        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{
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        Connection conn = DriverManager.getConnection(getUrl(), props);
        String ddl = "CREATE TABLE TEST5(testCharArray CHAR(3)[], CONSTRAINT test_pk PRIMARY KEY(testCharArray)) DEFAULT_COLUMN_FAMILY='T'";
        conn.createStatement().execute(ddl);
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        conn = DriverManager.getConnection(getUrl(), props);
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO TEST5 VALUES (ARRAY['aaa', 'bbb', 'ccc'])");
        stmt.execute();
        conn.commit();
        conn.close();

        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
        conn = DriverManager.getConnection(getUrl(), props);
        ResultSet rs;
        stmt = conn.prepareStatement("SELECT testCharArray from TEST5");
        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 = "testArrayIndexFunctionForImmutableTable".toUpperCase();
        long ts = nextTimestamp();
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
        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);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
        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);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
        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));
            }
        }
    }

}