| /* |
| * 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.assertFalse; |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertTrue; |
| |
| import java.sql.*; |
| |
| import org.apache.phoenix.schema.TypeMismatchException; |
| import org.apache.phoenix.schema.types.PhoenixArray; |
| import org.junit.Test; |
| |
| public class ArrayPrependFunctionIT extends ParallelStatsDisabledIT { |
| |
| private void initTableWithVarArray(Connection conn, String tableName, String type, Object[] objectArray, String value) throws SQLException { |
| conn.createStatement().execute("CREATE TABLE " + tableName + " ( k VARCHAR PRIMARY KEY, a " + type + "[],b " + type + ")"); |
| conn.commit(); |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES(?,?," + value + ")"); |
| PhoenixArray array = (PhoenixArray) conn.createArrayOf(type, objectArray); |
| stmt.setString(1, "a"); |
| stmt.setArray(2, array); |
| stmt.execute(); |
| conn.commit(); |
| |
| } |
| |
| private void initTables(Connection conn, String tableName) throws Exception { |
| 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)[])"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " (region_name,varchars,integers,doubles,bigints,chars,double1,char1,nullcheck,chars2) 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['foo','a','bbbb','c','ddd','e']" + |
| ")"; |
| PreparedStatement stmt = conn.prepareStatement(dml); |
| stmt.execute(); |
| conn.commit(); |
| } |
| |
| private void initTablesDesc(Connection conn, String tableName, String type, String val) throws Exception { |
| String ddl = "CREATE TABLE " + tableName + " (pk " + type + " PRIMARY KEY DESC,varchars VARCHAR[],integers INTEGER[],doubles DOUBLE[],bigints BIGINT[],chars CHAR(15)[],chars2 CHAR(15)[], bools BOOLEAN[])"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + "(pk,varchars,integers,doubles,bigints,chars,chars2,bools) VALUES(" + val + "," + |
| "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']," + |
| "ARRAY['a','bbbb','c','ddd','e','foo']," + |
| "ARRAY[true,false]" + |
| ")"; |
| PreparedStatement stmt = conn.prepareStatement(dml); |
| stmt.execute(); |
| conn.commit(); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInteger() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(1234,integers) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Integer[] integers = new Integer[]{1234, 2345, 46345, 23234, 456}; |
| |
| Array array = conn.createArrayOf("INTEGER", integers); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionVarchar() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND('34567',varchars) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{"34567", "2345", "46345", "23234"}; |
| |
| Array array = conn.createArrayOf("VARCHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionNulls1() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String[] s = new String[]{null, null, "1", "2"}; |
| String tableName = generateUniqueName(); |
| initTableWithVarArray(conn, tableName, "VARCHAR", s, null); |
| String[] s2 = new String[]{null, null, null, "1", "2"}; |
| PhoenixArray array2 = (PhoenixArray) conn.createArrayOf("VARCHAR", s2); |
| conn = DriverManager.getConnection(getUrl()); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(b,a) FROM " + tableName + " WHERE k = 'a'"); |
| assertTrue(rs.next()); |
| assertEquals(array2, rs.getArray(1)); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionNulls2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String[] s = new String[]{"1", "2"}; |
| String tableName = generateUniqueName(); |
| initTableWithVarArray(conn, tableName, "VARCHAR", s, null); |
| String[] s2 = new String[]{null, "1", "2"}; |
| PhoenixArray array2 = (PhoenixArray) conn.createArrayOf("VARCHAR", s2); |
| conn = DriverManager.getConnection(getUrl()); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(b,a) FROM " + tableName + " WHERE k = 'a'"); |
| assertTrue(rs.next()); |
| assertEquals(array2, rs.getArray(1)); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionNulls3() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String[] s = new String[]{"176", null, "212"}; |
| String tableName = generateUniqueName(); |
| initTableWithVarArray(conn, tableName, "VARCHAR", s, null); |
| String[] s2 = new String[]{null, "176", null, "212"}; |
| PhoenixArray array2 = (PhoenixArray) conn.createArrayOf("VARCHAR", s2); |
| conn = DriverManager.getConnection(getUrl()); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(b,a) FROM " + tableName + " WHERE k = 'a'"); |
| assertTrue(rs.next()); |
| assertEquals(array2, rs.getArray(1)); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionNulls4() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String[] s = new String[]{"176", null, "212"}; |
| String tableName = generateUniqueName(); |
| initTableWithVarArray(conn, tableName, "VARCHAR", s, "'foo'"); |
| String[] s2 = new String[]{"foo", "176", null, "212"}; |
| PhoenixArray array2 = (PhoenixArray) conn.createArrayOf("VARCHAR", s2); |
| conn = DriverManager.getConnection(getUrl()); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(b,a) FROM " + tableName + " WHERE k = 'a'"); |
| assertTrue(rs.next()); |
| assertEquals(array2, rs.getArray(1)); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionDouble() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(double1,doubles) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{23.45, 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 testArrayPrependFunctionDouble2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(23,doubles) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{new Double(23), 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 testArrayPrependFunctionBigint() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(1112,bigints) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Long[] longs = new Long[]{1112l, 12l, 34l, 56l, 78l, 910l}; |
| |
| Array array = conn.createArrayOf("BIGINT", longs); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionChar() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND('fac',chars) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{"fac", "a", "bbbb", "c", "ddd", "e"}; |
| |
| Array array = conn.createArrayOf("CHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test(expected = TypeMismatchException.class) |
| public void testArrayPrependFunctionIntToCharArray() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(234,varchars) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| } |
| |
| @Test(expected = TypeMismatchException.class) |
| public void testArrayPrependFunctionVarcharToIntegerArray() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND('234',integers) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| |
| } |
| |
| @Test(expected = SQLException.class) |
| public void testArrayPrependFunctionChar2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND('facfacfacfacfacfacfac',chars) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| rs.next(); |
| rs.getArray(1); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionIntegerToDoubleArray() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(45,doubles) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{45.0, 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 testArrayPrependFunctionWithNestedFunctions1() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(integers[1],ARRAY[23,45]) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Integer[] integers = new Integer[]{2345, 23, 45}; |
| |
| Array array = conn.createArrayOf("INTEGER", integers); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithNestedFunctions2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(ARRAY_ELEM(ARRAY[2,4],1),integers) FROM " + tableName+ " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Integer[] integers = new Integer[]{2, 2345, 46345, 23234, 456}; |
| |
| Array array = conn.createArrayOf("INTEGER", integers); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithNestedFunctions3() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(ARRAY_ELEM(doubles,2),doubles) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{46.345, 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 testArrayPrependFunctionWithUpsert1() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| String ddl = "CREATE TABLE " + tableName + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; |
| conn.createStatement().execute(ddl); |
| |
| String dml = "UPSERT INTO " + tableName + " (region_name,varchars) VALUES('SF Bay Area',ARRAY_PREPEND(':-)',ARRAY['hello','world']))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT varchars FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{":-)", "hello", "world"}; |
| |
| Array array = conn.createArrayOf("VARCHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithUpsert2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| 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_PREPEND(6,ARRAY[4,5]))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT integers FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Integer[] integers = new Integer[]{6, 4, 5}; |
| |
| Array array = conn.createArrayOf("INTEGER", integers); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithUpsert3() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| String ddl = "CREATE TABLE " + tableName + "(region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])"; |
| conn.createStatement().execute(ddl); |
| |
| String dml = "UPSERT INTO " + tableName + "(region_name,doubles) VALUES('SF Bay Area',ARRAY_PREPEND(9.0,ARRAY[5.67,7.87]))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT doubles FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{new Double(9), 5.67, 7.87}; |
| |
| Array array = conn.createArrayOf("DOUBLE", doubles); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithUpsertSelect1() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String baseTable = generateUniqueName(); |
| String source = baseTable + "_SOURCE"; |
| String target = baseTable + "_TARGET"; |
| String ddl = "CREATE TABLE " + source + " (region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])"; |
| conn.createStatement().execute(ddl); |
| |
| ddl = "CREATE TABLE " + target + "(region_name VARCHAR PRIMARY KEY,doubles DOUBLE[])"; |
| conn.createStatement().execute(ddl); |
| |
| String dml = "UPSERT INTO " + source + "(region_name,doubles) VALUES('SF Bay Area',ARRAY_PREPEND(9.0,ARRAY[5.67,7.87]))"; |
| conn.createStatement().execute(dml); |
| |
| dml = "UPSERT INTO " + source + "(region_name,doubles) VALUES('SF Bay Area2',ARRAY_PREPEND(9.2,ARRAY[56.7,7.87]))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| dml = "UPSERT INTO " + target + "(region_name, doubles) SELECT region_name, ARRAY_PREPEND(5,doubles) FROM " + source; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT doubles FROM " + target); |
| assertTrue(rs.next()); |
| |
| Double[] doubles = new Double[]{new Double(5), new Double(9), 5.67, 7.87}; |
| Array array = conn.createArrayOf("DOUBLE", doubles); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertTrue(rs.next()); |
| |
| doubles = new Double[]{new Double(5), new Double(9.2), 56.7, 7.87}; |
| array = conn.createArrayOf("DOUBLE", doubles); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionWithUpsertSelect2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String baseTable = generateUniqueName(); |
| String source = baseTable + "_SOURCE"; |
| String target = baseTable + "_TARGET"; |
| String ddl = "CREATE TABLE " + source + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; |
| conn.createStatement().execute(ddl); |
| |
| ddl = "CREATE TABLE " + target + " (region_name VARCHAR PRIMARY KEY,varchars VARCHAR[])"; |
| conn.createStatement().execute(ddl); |
| |
| String dml = "UPSERT INTO " + source + "(region_name,varchars) VALUES('SF Bay Area',ARRAY_PREPEND('c',ARRAY['abcd','b']))"; |
| conn.createStatement().execute(dml); |
| |
| dml = "UPSERT INTO " + source + "(region_name,varchars) VALUES('SF Bay Area2',ARRAY_PREPEND('something',ARRAY['d','fgh']))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| dml = "UPSERT INTO " + target + "(region_name, varchars) SELECT region_name, ARRAY_PREPEND('stu',varchars) FROM " + source; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT varchars FROM " + target); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{"stu", "c", "abcd", "b"}; |
| Array array = conn.createArrayOf("VARCHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertTrue(rs.next()); |
| |
| strings = new String[]{"stu", "something", "d", "fgh"}; |
| array = conn.createArrayOf("VARCHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere1() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE ARRAY[123,2345,46345,23234,456]=ARRAY_PREPEND(123,integers)"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere2() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE varchars[1]=ANY(ARRAY_PREPEND('1234',ARRAY['2345','46345','23234']))"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere3() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE ARRAY['1234','2345','46345','23234']=ARRAY_PREPEND('1234',ARRAY['2345','46345','23234'])"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere4() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE ARRAY[123.4,23.45,4634.5,2.3234]=ARRAY_PREPEND(123.4,ARRAY[23.45,4634.5,2.3234])"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere5() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE ARRAY['foo','2345','46345','23234']=ARRAY_PREPEND('foo',varchars)"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere6() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE chars2=ARRAY_PREPEND('foo',chars)"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionInWhere7() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT region_name FROM " + tableName + " WHERE ARRAY[4,2,3]=ARRAY_PREPEND(4,ARRAY[2,3])"); |
| assertTrue(rs.next()); |
| |
| assertEquals("SF Bay Area", rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test(expected = SQLException.class) |
| public void testArrayPrependFunctionCharLimitCheck() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTables(conn, tableName); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(char1,chars) FROM " + tableName + " WHERE region_name = 'SF Bay Area'"); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{"wert", "a", "bbbb", "c", "ddd", "e"}; |
| |
| Array array = conn.createArrayOf("CHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionIntegerDesc() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTablesDesc(conn, tableName, "INTEGER", "23"); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(pk,integers) FROM " + tableName); |
| assertTrue(rs.next()); |
| |
| Integer[] integers = new Integer[]{23, 2345, 46345, 23234, 456}; |
| |
| Array array = conn.createArrayOf("INTEGER", integers); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| |
| } |
| |
| @Test |
| public void testArrayPrependFunctionVarcharDesc() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTablesDesc(conn, tableName, "VARCHAR", "'e'"); |
| |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(pk,varchars) FROM " + tableName); |
| assertTrue(rs.next()); |
| |
| String[] strings = new String[]{"e", "2345", "46345", "23234"}; |
| |
| Array array = conn.createArrayOf("VARCHAR", strings); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionBigIntDesc() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTablesDesc(conn, tableName, "BIGINT", "1112"); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(pk,bigints) FROM " + tableName); |
| assertTrue(rs.next()); |
| |
| Long[] longs = new Long[]{1112l, 12l, 34l, 56l, 78l, 910l}; |
| |
| Array array = conn.createArrayOf("BIGINT", longs); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testArrayPrependFunctionBooleanDesc() throws Exception { |
| Connection conn = DriverManager.getConnection(getUrl()); |
| String tableName = generateUniqueName(); |
| initTablesDesc(conn, tableName, "BOOLEAN", "false"); |
| ResultSet rs; |
| rs = conn.createStatement().executeQuery("SELECT ARRAY_PREPEND(pk,bools) FROM " + tableName); |
| assertTrue(rs.next()); |
| |
| Boolean[] booleans = new Boolean[]{false, true, false}; |
| |
| Array array = conn.createArrayOf("BOOLEAN", booleans); |
| |
| assertEquals(array, rs.getArray(1)); |
| assertFalse(rs.next()); |
| } |
| } |