Github user JamesRTaylor commented on a diff in the pull request: https://github.com/apache/phoenix/pull/294#discussion_r175225898 --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java --- @@ -0,0 +1,425 @@ +/* + * 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.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNull; +import static org.junit.Assert.assertTrue; + +import java.sql.Array; +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; + +import org.apache.phoenix.schema.TypeMismatchException; +import org.junit.Test; + +public class ArrayRemoveFunctionIT extends ParallelStatsDisabledIT { + private String initTables(Connection conn) throws Exception { + String tableName = generateUniqueName(); + String ddl = "CREATE TABLE " + tableName + + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[],integers INTEGER[],doubles DOUBLE[],bigints BIGINT[]," + + "chars CHAR(15)[],double1 DOUBLE,char1 CHAR(17),nullcheck INTEGER,chars2 CHAR(15)[], nullVarchar VARCHAR[], nullBigInt BIGINT[],double2 DOUBLE,integer1 INTEGER)"; + conn.createStatement().execute(ddl); + String dml = "UPSERT INTO " + tableName + "(region_name,varchars,integers,doubles,bigints,chars,double1,char1,nullcheck,chars2,double2,integer1) VALUES('SF Bay Area'," + + "ARRAY['2345','46345','23234']," + + "ARRAY[2345,46345,23234,456]," + + "ARRAY[23.45,46.345,23.234,45.6,5.78]," + + "ARRAY[12,34,56,78,910]," + + "ARRAY['a','bbbb','c','ddd','e']," + + "23.45," + + "'wert'," + + "NULL," + + "ARRAY['a','bbbb','c','ddd','e','foo']," + + "12,"+ + "12"+ + ")"; + PreparedStatement stmt = conn.prepareStatement(dml); + stmt.execute(); + conn.commit(); + return tableName; + } + + @Test + public void testEmptyArrayModification() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(nullVarChar,'34567') FROM " + tableName + " LIMIT 1"); + assertTrue(rs.next()); + + assertNull(rs.getArray(1)); + assertFalse(rs.next()); + } + + @Test + public void testArrayRemoveFunctionVarchar() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(varchars,'23234') FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); + assertTrue(rs.next()); + + String[] strings = new String[]{"2345", "46345"}; + + Array array = conn.createArrayOf("VARCHAR", strings); + + assertEquals(array, rs.getArray(1)); + assertFalse(rs.next()); + } + + @Test + public void testArrayRemoveFunctionInteger() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(integers,456) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); + assertTrue(rs.next()); + + Integer[] integers = new Integer[]{2345, 46345, 23234}; + + Array array = conn.createArrayOf("INTEGER", integers); + + assertEquals(array, rs.getArray(1)); + assertFalse(rs.next()); + } + + @Test + public void testArrayRemoveFunctionDouble() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(doubles,double1) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); + assertTrue(rs.next()); + + Double[] doubles = new Double[]{46.345, 23.234, 45.6, 5.78}; + + Array array = conn.createArrayOf("DOUBLE", doubles); + + assertEquals(array, rs.getArray(1)); + assertFalse(rs.next()); + } + + @Test + public void testArrayRemoveFunctionDoubleWithInt() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(doubles,10) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); + assertTrue(rs.next()); + + Double[] doubles = new Double[]{23.45,46.345,23.234,45.6,5.78}; + + Array array = conn.createArrayOf("DOUBLE", doubles); + + assertEquals(array, rs.getArray(1)); + assertFalse(rs.next()); + } + + @Test + public void testArrayRemoveFunctionBigint() throws Exception { + Connection conn = DriverManager.getConnection(getUrl()); + String tableName = initTables(conn); + ResultSet rs; + rs = conn.createStatement().executeQuery("SELECT ARRAY_REMOVE(bigints,56) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); --- End diff -- Same as above - make sure 56L is in array and is removed.
---