| /******************************************************************************* |
| * Copyright (c) 2013, Salesforce.com, Inc. |
| * All rights reserved. |
| * |
| * Redistribution and use in source and binary forms, with or without |
| * modification, are permitted provided that the following conditions are met: |
| * |
| * Redistributions of source code must retain the above copyright notice, |
| * this list of conditions and the following disclaimer. |
| * Redistributions in binary form must reproduce the above copyright notice, |
| * this list of conditions and the following disclaimer in the documentation |
| * and/or other materials provided with the distribution. |
| * Neither the name of Salesforce.com nor the names of its contributors may |
| * be used to endorse or promote products derived from this software without |
| * specific prior written permission. |
| * |
| * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND |
| * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED |
| * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE |
| * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE |
| * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL |
| * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR |
| * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER |
| * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, |
| * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
| * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
| ******************************************************************************/ |
| /********************************** |
| * |
| * Later modifications to test Trafodion instead of Phoenix were granted to ASF. |
| * |
| * 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 test.java.org.trafodion.phoenix.end2end; |
| |
| import static org.junit.Assert.*; |
| import org.junit.*; |
| import java.math.*; |
| import java.sql.*; |
| import java.sql.Date; |
| import java.util.*; |
| |
| public class ExecuteStatementsTest extends BaseTest { |
| |
| @BeforeClass |
| public static void doTestSuiteSetup() throws Exception { |
| /* List all of the object names being used in this entire class. |
| * The objects are dropped with errors ignored, so it is OK if the |
| * object does not exist for a particular test. |
| */ |
| objDropList = new ArrayList<String>( |
| Arrays.asList("table " + ATABLE_NAME, "table " + BTABLE_NAME, "table " + PTSDB_NAME, "table foo")); |
| doBaseTestSuiteSetup(); |
| } |
| /* @AfterClass, @Before, @After are defined in BaseTest */ |
| |
| @Test |
| public void testExecuteStatements() throws Exception { |
| printTestDescription(); |
| |
| initATableValues(); |
| |
| String statements[] = null; |
| if (tgtPH()) statements = new String[] { |
| "create table IF NOT EXISTS" + ATABLE_NAME + // Shouldn't error out b/c of if not exists clause |
| " (organization_id char(15) not null, \n" + |
| " entity_id char(15) not null,\n" + |
| " a_string varchar(100),\n" + |
| " b_string varchar(100)\n" + |
| " CONSTRAINT pk PRIMARY KEY (organization_id,entity_id));\n", |
| |
| "create table " + PTSDB_NAME + |
| " (inst varchar null,\n" + |
| " host varchar null,\n" + |
| " date date not null,\n" + |
| " val decimal\n" + |
| " CONSTRAINT pk PRIMARY KEY (inst,host,date))\n" + |
| " split on (?,?,?);\n", |
| |
| "alter table " + PTSDB_NAME + " add IF NOT EXISTS val decimal;\n", // Shouldn't error out b/c of if not exists clause |
| |
| "alter table " + PTSDB_NAME + " drop column IF EXISTS blah;\n", // Shouldn't error out b/c of if exists clause |
| |
| "drop table IF EXISTS FOO.BAR;\n", // Shouldn't error out b/c of if exists clause |
| |
| "UPSERT INTO " + PTSDB_NAME + "(date, val, host) " + |
| " SELECT current_date(), x_integer+2, entity_id FROM ATABLE WHERE a_integer >= ?;", |
| |
| "UPSERT INTO " + PTSDB_NAME + "(date, val, inst)\n" + |
| " SELECT date+1, val*10, host FROM " + PTSDB_NAME + ";"}; |
| else if (tgtTR()) statements = new String[]{ |
| "create table IF NOT EXISTS " + ATABLE_NAME + // Shouldn't error out b/c of if not exists clause |
| " (organization_id char(15) not null, \n" + |
| " entity_id char(15) not null,\n" + |
| " a_string varchar(100),\n" + |
| " b_string varchar(100)\n" + |
| " , CONSTRAINT pk_btable PRIMARY KEY (organization_id,entity_id));\n", |
| |
| "create table " + PTSDB_NAME + |
| " (inst varchar(100) not null not droppable,\n" + |
| " host1 varchar(100) not null not droppable,\n" + |
| " date1 date not null,\n" + |
| " val decimal\n" + |
| " , CONSTRAINT pk_ptsdb PRIMARY KEY (inst,host1,date1))\n" + |
| // TRAF " split on (?,?,?);\n", |
| /* TRAF */ ";\n", |
| |
| "alter table " + PTSDB_NAME + " add IF NOT EXISTS val1 decimal;\n", // Shouldn't error out b/c of if not exists clause |
| |
| "alter table " + PTSDB_NAME + " drop column if exists blah;\n", // Shouldn't error out b/c of if exists clause |
| |
| "drop table IF EXISTS FOO.BAR;\n", // Shouldn't error out b/c of if exists clause |
| |
| "UPSERT INTO " + PTSDB_NAME + "(inst, date1, val, host1) " + |
| " SELECT 'abcd', CURRENT_DATE, x_integer+2, entity_id FROM ATABLE WHERE a_integer >= 6;", |
| |
| "UPSERT INTO " + PTSDB_NAME + "(date1, val, inst, host1)\n" + |
| " SELECT date1+1, val*10, host1, host1 FROM " + PTSDB_NAME + ";"}; |
| else if (tgtSQ()) statements = new String[]{ |
| "create table " + BTABLE_NAME + |
| " (organization_id char(15) not null, \n" + |
| " entity_id char(15) not null,\n" + |
| " a_string varchar(100),\n" + |
| " b_string varchar(100)\n" + |
| " , CONSTRAINT pk_btable PRIMARY KEY (organization_id,entity_id));\n", |
| |
| "create table " + PTSDB_NAME + |
| " (inst varchar(100) not null not droppable,\n" + |
| " host1 varchar(100) not null not droppable,\n" + |
| " date1 date not null,\n" + |
| " val decimal\n" + |
| " , CONSTRAINT pk_ptsdb PRIMARY KEY (inst,host1,date1))\n" + |
| ";\n", |
| |
| "alter table " + PTSDB_NAME + " add val1 decimal;\n", |
| |
| // TRAF: take the following one out completely. No replacement in SQ. |
| // TRAF "alter table " + PTSDB_NAME + " drop column if exists blah;\n", // Shouldn't error out b/c of if exists clause |
| |
| "drop table " + BTABLE_NAME + ";\n", |
| |
| "INSERT INTO " + PTSDB_NAME + "(inst, date1, val, host1) " + |
| " SELECT 'abcd', CURRENT_DATE, x_integer+2, entity_id FROM ATABLE WHERE a_integer >= 6;", |
| |
| "INSERT INTO " + PTSDB_NAME + "(date1, val, inst, host1)\n" + |
| " SELECT date1+1, val*10, host1, host1 FROM " + PTSDB_NAME + ";"}; |
| Date now = new Date(System.currentTimeMillis()); |
| conn.setAutoCommit(true); |
| List<Object> binds = Arrays.<Object>asList("a","j","s", 6); |
| for (int i = 0; i < statements.length; i++) { |
| String stmt = statements[i]; |
| conn.createStatement().execute(stmt); |
| } |
| |
| Date then = new Date(System.currentTimeMillis() + MILLIS_IN_DAY); |
| |
| String query = null; |
| if (tgtPH()) query = "SELECT host,inst, date,val FROM " + PTSDB_NAME + " where inst is not null"; |
| else if (tgtSQ()||tgtTR()) query = "SELECT host1,inst, date1,val FROM " + PTSDB_NAME + " where inst is not null order by 1, 2, 3, 4"; |
| PreparedStatement statement = conn.prepareStatement(query); |
| |
| // TRAF, the table column values are all different by now. There is |
| // not much point to verify the data the same way. |
| if (tgtSQ()||tgtTR()) return; |
| |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertEquals(null, rs.getString(1)); |
| assertEquals(ROW6, rs.getString(2)); |
| assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then)); |
| assertEquals(null, rs.getBigDecimal(4)); |
| |
| assertTrue (rs.next()); |
| assertEquals(null, rs.getString(1)); |
| assertEquals(ROW7, rs.getString(2)); |
| assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then)); |
| assertTrue(BigDecimal.valueOf(70).compareTo(rs.getBigDecimal(4)) == 0); |
| |
| assertTrue (rs.next()); |
| assertEquals(null, rs.getString(1)); |
| assertEquals(ROW8, rs.getString(2)); |
| assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then)); |
| assertTrue(BigDecimal.valueOf(60).compareTo(rs.getBigDecimal(4)) == 0); |
| |
| assertTrue (rs.next()); |
| assertEquals(null, rs.getString(1)); |
| assertEquals(ROW9, rs.getString(2)); |
| assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then)); |
| assertTrue(BigDecimal.valueOf(50).compareTo(rs.getBigDecimal(4)) == 0); |
| |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testCharPadding() throws Exception { |
| printTestDescription(); |
| |
| String tableName = "foo"; |
| String rowKey = "hello"; |
| String testString = "world"; |
| String query = null; |
| if (tgtPH()) query = "create table " + tableName + |
| "(a_id integer not null, \n" + |
| "a_string char(10) not null, \n" + |
| "b_string char(8) not null \n" + |
| "CONSTRAINT my_pk PRIMARY KEY (a_id, a_string))"; |
| else if (tgtSQ()||tgtTR()) query = "create table " + tableName + |
| "(a_id integer not null, \n" + |
| "a_string char(10) not null, \n" + |
| "b_string char(8) not null \n" + |
| ", CONSTRAINT pk_foo PRIMARY KEY (a_id, a_string))"; |
| |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.execute(); |
| if (tgtPH()||tgtTR()) statement = conn.prepareStatement( |
| "upsert into " + tableName + |
| " (a_id, " + |
| " a_string, " + |
| " b_string)" + |
| "VALUES (?, ?, ?)"); |
| else if (tgtSQ()) statement = conn.prepareStatement( |
| "insert into " + tableName + |
| " (a_id, " + |
| " a_string, " + |
| " b_string)" + |
| "VALUES (?, ?, ?)"); |
| statement.setInt(1, 1); |
| statement.setString(2, rowKey); |
| statement.setString(3, testString); |
| statement.execute(); |
| |
| createTestTable(BTABLE_NAME); |
| if (tgtPH()||tgtTR()) statement = conn.prepareStatement( |
| "upsert into BTABLE VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| else if (tgtSQ()) statement = conn.prepareStatement( |
| "insert into BTABLE VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| statement.setString(1, "abc"); |
| statement.setString(2, "xyz"); |
| statement.setString(3, "x"); |
| statement.setInt(4, 9); |
| statement.setString(5, "ab"); |
| statement.setInt(6, 1); |
| statement.setInt(7, 1); |
| statement.setString(8, "ab"); |
| statement.setString(9, "morning1"); |
| statement.execute(); |
| try { |
| // test rowkey and non-rowkey values in select statement |
| query = "select a_string, b_string from " + tableName; |
| assertCharacterPadding(conn.prepareStatement(query), rowKey, testString); |
| |
| // test with rowkey in where clause |
| query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + "'"; |
| assertCharacterPadding(conn.prepareStatement(query), rowKey, testString); |
| |
| // test with non-rowkey in where clause |
| query = "select a_string, b_string from " + tableName + " where b_string = '" + testString + "'"; |
| assertCharacterPadding(conn.prepareStatement(query), rowKey, testString); |
| |
| // test with rowkey and id in where clause |
| query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + "'"; |
| assertCharacterPadding(conn.prepareStatement(query), rowKey, testString); |
| |
| // test with rowkey and id in where clause where rowkey is greater than the length of the char.len |
| query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = '" + rowKey + testString + "'"; |
| statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| |
| // test with rowkey and id in where clause where rowkey is lesser than the length of the char.len |
| query = "select a_string, b_string from " + tableName + " where a_id = 1 and a_string = 'he'"; |
| statement = conn.prepareStatement(query); |
| rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| |
| String rowKey2 = "good"; |
| String testString2 = "morning"; |
| String testString8Char = "morning1"; |
| String testString10Char = "morning123"; |
| String upsert = null; |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + " values (2, '" + rowKey2 + "', '" + testString2+ "') "; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + " values (2, '" + rowKey2 + "', '" + testString2+ "') "; |
| statement = conn.prepareStatement(upsert); |
| statement.execute(); |
| |
| // test upsert statement with padding |
| initATableValues(); |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT A_INTEGER, A_STRING, B_STRING FROM ATABLE WHERE a_string = ?"; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT A_INTEGER, A_STRING, B_STRING FROM ATABLE WHERE a_string = ?"; |
| |
| statement = conn.prepareStatement(upsert); |
| statement.setString(1, A_VALUE); |
| int rowsInserted = statement.executeUpdate(); |
| assertEquals(4, rowsInserted); |
| |
| if (tgtPH()) query = "select a_string, b_string from " + tableName + " where a_string = '" + A_VALUE+"'"; |
| else if (tgtSQ()||tgtTR()) query = "select a_string, b_string from " + tableName + " where a_string = '" + A_VALUE+"'" + "order by 2"; |
| assertCharacterPadding(conn.prepareStatement(query), A_VALUE, B_VALUE); |
| |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + " values (3, '" + testString2 + "', '" + testString2+ "') "; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + " values (3, '" + testString2 + "', '" + testString2+ "') "; |
| statement = conn.prepareStatement(upsert); |
| statement.execute(); |
| query = "select a_string, b_string from " + tableName + " where a_id = 3 and a_string = b_string"; |
| assertCharacterPadding(conn.prepareStatement(query), testString2, testString2); |
| |
| // compare a higher length col with lower length : a_string(10), b_string(8) |
| query = "select a_string, b_string from " + tableName + " where a_id = 3 and b_string = a_string"; |
| statement = conn.prepareStatement(query); |
| rs = statement.executeQuery(); |
| assertCharacterPadding(conn.prepareStatement(query), testString2, testString2); |
| |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + " values (4, '" + rowKey2 + "', '" + rowKey2 + "') "; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + " values (4, '" + rowKey2 + "', '" + rowKey2 + "') "; |
| statement = conn.prepareStatement(upsert); |
| statement.execute(); |
| |
| // where both the columns have same value with different paddings |
| query = "select a_string, b_string from " + tableName + " where a_id = 4 and b_string = a_string"; |
| assertCharacterPadding(conn.prepareStatement(query), rowKey2, rowKey2); |
| |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + " values (5, '" + testString10Char + "', '" + testString8Char + "') "; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + " values (5, '" + testString10Char + "', '" + testString8Char + "') "; |
| statement = conn.prepareStatement(upsert); |
| statement.execute(); |
| |
| // where smaller column is the subset of larger string |
| query = "select a_string, b_string from " + tableName + " where a_id = 5 and b_string = a_string"; |
| statement = conn.prepareStatement(query); |
| rs = statement.executeQuery(); |
| assertFalse(rs.next()); |
| |
| //where selecting from a CHAR(x) and upserting into a CHAR(y) where x>y |
| // upsert rowkey value greater than rowkey limit |
| try { |
| |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT x_integer, organization_id, b_string FROM ATABLE WHERE a_string = ?"; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT x_integer, organization_id, b_string FROM ATABLE WHERE a_string = ?"; |
| |
| statement = conn.prepareStatement(upsert); |
| statement.setString(1, A_VALUE); |
| statement.executeUpdate(); |
| fail("Should fail when bigger than expected character is inserted"); |
| } catch (SQLException ex) { |
| if (tgtPH()) assertTrue(ex.getMessage().contains("The value is outside the range for the data type. columnName=A_STRING")); |
| // TRAF should see ERROR 8402 A string overflow occurred during the evaluation.. but we are seeing null assigning to non-null... alas, the columns have been changed. |
| else if (tgtSQ()||tgtTR()) assertTrue(ex.getMessage().contains("ERROR")); |
| } |
| |
| // upsert non-rowkey value greater than its limit |
| try { |
| |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT y_integer, a_string, entity_id FROM ATABLE WHERE a_string = ?"; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT y_integer, a_string, entity_id FROM ATABLE WHERE a_string = ?"; |
| |
| statement = conn.prepareStatement(upsert); |
| statement.setString(1, A_VALUE); |
| statement.executeUpdate(); |
| fail("Should fail when bigger than expected character is inserted"); |
| } |
| catch (SQLException ex) { |
| if (tgtPH()) assertTrue(ex.getMessage().contains("The value is outside the range for the data type. columnName=B_STRING")); |
| else if (tgtTR()) assertTrue(ex.getMessage().contains("*** ERROR[8421] NULL cannot be assigned to a NOT NULL column.")); |
| else if (tgtSQ()) assertTrue(ex.getMessage().contains("*** ERROR[8402] A string overflow occurred during the evaluation of a character expression.")); |
| } |
| |
| //where selecting from a CHAR(x) and upserting into a CHAR(y) where x<=y. |
| if (tgtPH()||tgtTR()) upsert = "UPSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT a_integer, e_string, a_id FROM BTABLE"; |
| else if (tgtSQ()) upsert = "INSERT INTO " + tableName + "(a_id, a_string, b_string) " + |
| "SELECT a_integer, e_string, a_id FROM BTABLE"; |
| |
| statement = conn.prepareStatement(upsert); |
| rowsInserted = statement.executeUpdate(); |
| assertEquals(1, rowsInserted); |
| |
| query = "select a_string, b_string from " + tableName + " where a_string = 'morning1'"; |
| assertCharacterPadding(conn.prepareStatement(query), "morning1", "xyz"); |
| } finally { |
| } |
| } |
| |
| |
| private void assertCharacterPadding(PreparedStatement statement, String rowKey, String testString) throws SQLException { |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| if (tgtPH()) { |
| assertEquals(rowKey, rs.getString(1)); |
| assertEquals(testString, rs.getString(2)); |
| } else if (tgtSQ()||tgtTR()) { |
| assertEquals(rowKey, rs.getString(1).trim()); |
| assertEquals(testString, rs.getString(2).trim()); |
| } |
| } |
| |
| } |
