[
https://issues.apache.org/jira/browse/PHOENIX-4643?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16405610#comment-16405610
]
ASF GitHub Bot commented on PHOENIX-4643:
-----------------------------------------
Github user JamesRTaylor commented on a diff in the pull request:
https://github.com/apache/phoenix/pull/294#discussion_r175620626
--- Diff:
phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayRemoveFunctionIT.java
---
@@ -0,0 +1,383 @@
+/*
+ * 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.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import org.apache.phoenix.schema.TypeMismatchException;
+import org.junit.Before;
+import org.junit.Test;
+
+public class ArrayRemoveFunctionIT extends ParallelStatsDisabledIT {
+
+ private Connection conn;
+ private String tableName;
+
+ @Before
+ public void setup() throws Exception {
+ conn = DriverManager.getConnection(getUrl());
+ tableName = initTables(conn);
+ }
+
+ 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,oneItem VARCHAR[],char2
char(15),varchar1 VARCHAR)";
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO " + tableName
+ +
"(region_name,varchars,integers,doubles,bigints,chars,double1,char1,nullcheck,chars2,double2,integer1,oneItem,char2,varchar1)
VALUES('SF Bay Area',"
+ + "ARRAY['2345','46345','23234']," +
"ARRAY[2345,46345,23234,456],"
+ + "ARRAY[10.0,23.45,46.345,23.234,45.6,5.78],"
+ "ARRAY[12,34,56,78,910],"
+ + "ARRAY['a','bbbb','c','ddd','e','c']," +
"23.45," + "'wert'," + "NULL,"
+ + "ARRAY['a','bbbb','c','ddd','e','foo']," +
"12," + "12," + "ARRAY['alone'],'2345','bbbb')";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.execute();
+ conn.commit();
+ return tableName;
+ }
+
+ @Test
+ public void testEmptyArrayModification() throws Exception {
+ ResultSet 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 {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,'23234') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"2345", "46345" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionInteger() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(integers,456) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[] {
2345, 46345, 23234 }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionDouble() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(doubles,double1) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0,
46.345, 23.234, 45.6, 5.78 }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionDoubleWithInt() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(doubles,10),doubles FROM "
+ tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 23.45,
46.345, 23.234, 45.6, 5.78 }), rs.getArray(1));
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0,
23.45, 46.345, 23.234, 45.6, 5.78 }),
+ rs.getArray(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionBigint() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(bigints,56),bigints FROM "
+ tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("BIGINT", new Long[] { 12l,
34l, 78l, 910l }), rs.getArray(1));
+ assertEquals(conn.createArrayOf("BIGINT", new Long[] { 12l,
34l, 56l, 78l, 910l }), rs.getArray(2));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionBigintWithInteger() throws Exception
{
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(bigints,integer1) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("BIGINT", new Long[] { 34l,
56l, 78l, 910l }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test(expected = TypeMismatchException.class)
+ public void testArrayRemoveFunctionBigintWithDouble() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(bigints,double2) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("BIGINT", new Long[] { 34l,
56l, 78l, 910l }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionChar() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(chars,'ddd') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("CHAR", new String[] { "a",
"bbbb", "c", "e", "c" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test(expected = TypeMismatchException.class)
+ public void testArrayRemoveFunctionIntToCharArray() throws Exception {
+ conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,234) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ }
+
+ @Test(expected = TypeMismatchException.class)
+ public void testArrayRemoveFunctionVarcharToIntegerArray() throws
Exception {
+ conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(integers,'234') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithNestedFunctions1() throws
Exception {
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
ARRAY_REMOVE(ARRAY[23,2345],integers[1]) FROM "
+ + tableName + " WHERE region_name = 'SF Bay
Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 23
}), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithNestedFunctions2() throws
Exception {
+ ResultSet rs = conn.createStatement()
+ .executeQuery("SELECT
ARRAY_REMOVE(integers,ARRAY_ELEM(ARRAY[2345,4],1)) FROM " + tableName
+ + " WHERE region_name = 'SF Bay
Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[] {
46345, 23234, 456 }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithUpsert1() throws Exception {
+ String uniqueName = generateUniqueName();
+ String ddl = "CREATE TABLE " + uniqueName + " (region_name
VARCHAR PRIMARY KEY,varchars VARCHAR[])";
+ conn.createStatement().execute(ddl);
+
+ String dml = "UPSERT INTO " + uniqueName
+ + "(region_name,varchars) VALUES('SF Bay
Area',ARRAY_REMOVE(ARRAY['hello','world'],'world'))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement()
+ .executeQuery("SELECT varchars FROM " +
uniqueName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"hello" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithUpsert2() throws Exception {
+ String tableName = generateUniqueName();
+ String ddl = "CREATE TABLE " + tableName + " (region_name
VARCHAR PRIMARY KEY,integers INTEGER[])";
+ conn.createStatement().execute(ddl);
+
+ String dml = "UPSERT INTO " + tableName
+ + "(region_name,integers) VALUES('SF Bay
Area',ARRAY_REMOVE(ARRAY[4,5],5))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement()
+ .executeQuery("SELECT integers FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[] { 4
}), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithUpsertSelect1() throws Exception
{
+ String sourceTableName = generateUniqueName();
+ String targetTableName = generateUniqueName();
+
+ String ddl = "CREATE TABLE " + sourceTableName + " (region_name
VARCHAR PRIMARY KEY,doubles DOUBLE[])";
+ conn.createStatement().execute(ddl);
+
+ ddl = "CREATE TABLE " + targetTableName + " (region_name
VARCHAR PRIMARY KEY,doubles DOUBLE[])";
+ conn.createStatement().execute(ddl);
+
+ String dml = "UPSERT INTO " + sourceTableName
+ + "(region_name,doubles) VALUES('SF Bay
Area',ARRAY_APPEND(ARRAY[5.67,7.87],9))";
+ conn.createStatement().execute(dml);
+
+ dml = "UPSERT INTO " + sourceTableName
+ + "(region_name,doubles) VALUES('SF Bay
Area2',ARRAY_APPEND(ARRAY[56.7,7.87],9))";
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ dml = "UPSERT INTO " + targetTableName
+ + "(region_name, doubles) SELECT region_name,
ARRAY_REMOVE(doubles,9) FROM " + sourceTableName;
+ conn.createStatement().execute(dml);
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("SELECT
doubles FROM " + targetTableName);
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 5.67,
7.87 }), rs.getArray(1));
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 56.7,
7.87 }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionInWhere1() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT region_name FROM " + tableName + "
WHERE ARRAY[2345,46345,23234]=ARRAY_REMOVE(integers,456)");
+ assertTrue(rs.next());
+
+ assertEquals("SF Bay Area", rs.getString(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionVarcharWithNull() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,NULL) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"2345", "46345", "23234" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionDoublesWithNull() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(doubles,NULL) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("DOUBLE", new Double[] { 10.0,
23.45, 46.345, 23.234, 45.6, 5.78 }),
+ rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionCharsWithNull() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(chars,NULL) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("CHAR", new String[] { "a",
"bbbb", "c", "ddd", "e", "c" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionWithNull() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(integers,nullcheck) FROM "
+ tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("INTEGER", new Integer[] {
2345, 46345, 23234, 456 }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionFirstElement() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,'2345') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"46345", "23234" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionMiddleElement() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,'46345') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"2345", "23234" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionLastElement() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,'23234') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"2345", "46345" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionOneElement() throws Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(oneItem,'alone') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[0]),
rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionRepeatingElements() throws Exception
{
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(chars,'c') FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("CHAR", new String[] { "a",
"bbbb", "ddd", "e" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionCharFromVarcharArray() throws
Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(varchars,char2) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("VARCHAR", new String[] {
"46345", "23234" }), rs.getArray(1));
+ assertFalse(rs.next());
+ }
+
+ @Test
+ public void testArrayRemoveFunctionVarcharFromCharArray() throws
Exception {
+ ResultSet rs = conn.createStatement().executeQuery(
+ "SELECT ARRAY_REMOVE(chars,varchar1) FROM " +
tableName + " WHERE region_name = 'SF Bay Area'");
+ assertTrue(rs.next());
+
+ assertEquals(conn.createArrayOf("CHAR", new String[] { "a",
"c", "ddd", "e", "c" }), rs.getArray(1));
--- End diff --
Instead of removing "bbbb" which is the longest element so won't have
trailing spaces, try removing "a" or "c" as it's a better test.
> Implement ARRAY_REMOVE built in function
> -----------------------------------------
>
> Key: PHOENIX-4643
> URL: https://issues.apache.org/jira/browse/PHOENIX-4643
> Project: Phoenix
> Issue Type: New Feature
> Reporter: Xavier Jodoin
> Priority: Minor
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
