| /* |
| * 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','a')"; |
| 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[] { "bbbb", "c", "ddd", "e", "c" }), rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| } |