Java Code Examples for org.apache.phoenix.util.PropertiesUtil#deepCopy()

@Test
public void testRVCWithNonLeadingPkColsOfTypesIntegerAndString() throws Exception {
    String tenantId = getOrganizationId();
    String tableName = initATableValues(null, tenantId, getDefaultSplits(tenantId), null, null, getUrl(), null);
    String query = "SELECT a_integer, a_string FROM " + tableName + " WHERE ?=organization_id  AND (a_integer, a_string) <= (5, 'a')";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        int count = 0;
        //we have 4 rows present in a table with (a_integer, a_string) <= (5, 'a'). All have a_string set to "a".
        while(rs.next()) {
            assertTrue(rs.getInt(1) <= 5);
            assertTrue(rs.getString(2).compareTo("a") == 0);
            count++;
        }
        assertTrue(count == 4);
    } finally {
        conn.close();
    }
}
 

/**
 * Create the test data
 */
@Before public void setup() throws SQLException {
    tenantId = generateUniqueName();
    tenantViewName = generateUniqueName();
    indexNameTenant = generateUniqueName();
    multiTenantTable = generateUniqueName();
    viewIndexTableName = "_IDX_" + multiTenantTable;

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    connGlobal = DriverManager.getConnection(getUrl(), props);

    props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
    connTenant = DriverManager.getConnection(getUrl(), props);
    String createTblStr = "CREATE TABLE %s (COL1 VARCHAR(15) NOT NULL,ID INTEGER NOT NULL" + ", NAME VARCHAR, CONSTRAINT PK_1 PRIMARY KEY (COL1, ID)) MULTI_TENANT=true";

    createTestTable(getUrl(), String.format(createTblStr, multiTenantTable));

    connTenant.createStatement()
            .execute(String.format(createViewStr, tenantViewName, multiTenantTable));

    String idxStmtTenant = String.format(createIndexStr, indexNameTenant, tenantViewName);
    connTenant.createStatement().execute(idxStmtTenant);
    connTenant.commit();
    connGlobal.commit();
}
 

@Test
public void testAddColumnsUsingNewConnection() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    String ddl = "CREATE TABLE T (\n"
            +"ID1 VARCHAR(15) NOT NULL,\n"
            +"ID2 VARCHAR(15) NOT NULL,\n"
            +"CREATED_DATE DATE,\n"
            +"CREATION_TIME BIGINT,\n"
            +"LAST_USED DATE,\n"
            +"CONSTRAINT PK PRIMARY KEY (ID1, ID2))";
    Connection conn1 = DriverManager.getConnection(getUrl(), props);
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE T ADD STRING VARCHAR, STRING_DATA_TYPES VARCHAR";
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE T DROP COLUMN STRING, STRING_DATA_TYPES";
    conn1.createStatement().execute(ddl);
    ddl = "ALTER TABLE T ADD STRING_ARRAY1 VARCHAR[]";
    conn1.createStatement().execute(ddl);
    conn1.close();
}
 

@Test
public void testNonBooleanWhereExpression() throws Exception {
    try {
        // Select non agg column in aggregate query
        String query = "SELECT a_integer FROM atable WHERE organization_id=? and CASE WHEN a_integer <= 2 THEN 'foo' WHEN a_integer = 3 THEN 'bar' WHEN a_integer <= 5 THEN 'bas' ELSE 'blah' END";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "00D300000000XHP");
            statement.executeQuery();
            fail();
        } finally {
            conn.close();
        }
    } catch (SQLException e) {
        assertTrue(e.getMessage().contains("ERROR 203 (22005): Type mismatch. BOOLEAN and VARCHAR for CASE WHEN A_INTEGER <= 2 THEN 'foo'WHEN A_INTEGER = 3 THEN 'bar'WHEN A_INTEGER <= 5 THEN 'bas' ELSE 'blah' END"));
    }
}
 

@Test
public void testFullyQualifiedRVCInList() throws Exception {
    String query = "SELECT entity_id FROM " + tableName + " WHERE (a_integer,a_string, organization_id,entity_id) IN ((2,'a',:1,:2),(5,'b',:1,:3))";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, ROW2);
        statement.setString(3, ROW5);
        ResultSet rs = statement.executeQuery();
        assertValueEqualsResultSet(rs, Arrays.<Object>asList(ROW2, ROW5));
    } finally {
        conn.close();
    }
}
 

@Test
public void testDateSubtract() throws Exception {
    String query = "SELECT entity_id, b_string FROM ATABLE WHERE a_date - 0.5d > ?";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setDate(1, new Date(System.currentTimeMillis() + MILLIS_IN_DAY));
        ResultSet rs = statement.executeQuery();
        @SuppressWarnings("unchecked")
        List<List<Object>> expectedResults = Lists.newArrayList(
                Arrays.<Object>asList(ROW3, E_VALUE),
                Arrays.<Object>asList( ROW6, E_VALUE), 
                Arrays.<Object>asList(ROW9, E_VALUE));
        assertValuesEqualsResultSet(rs, expectedResults);
    } finally {
        conn.close();
    }
}
 

@Test
public void testPartiallyEvaluableOr() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    String query = "SELECT date FROM PRODUCT_METRICS WHERE organization_id=? AND (transactions = 10000 OR unset_column = 5 OR io_time = 4000)";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    try {
        initTableValues(tenantId, getSplits(tenantId), ts);
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(D4, rs.getDate(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testDistinctLimitScan() throws Exception {
    String query = "SELECT DISTINCT a_string FROM aTable WHERE organization_id=? LIMIT 1";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), A_VALUE);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testSelectSpecificIndexOfAnArrayAsArrayFunction()
        throws Exception {
    String tenantId = getOrganizationId();
    String tableName = createTableWithArray(getUrl(),
            getDefaultSplits(tenantId), null);
    initTablesWithArrays(tableName, tenantId, null, false, getUrl());
    String query = "SELECT ARRAY_ELEM(a_double_array,2) FROM  " + tableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        // Need to support primitive
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 36.763;
        conn.createArrayOf("DOUBLE", doubleArr);
        Double result =  rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testUpsertSelectDoesntSeeUpsertedData() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(QueryServices.MUTATE_BATCH_SIZE_BYTES_ATTRIB, Integer.toString(512));
    props.setProperty(QueryServices.SCAN_CACHE_SIZE_ATTRIB, Integer.toString(3));
    props.setProperty(QueryServices.SCAN_RESULT_CHUNK_SIZE, Integer.toString(3));
    props.setProperty(QueryServices.ENABLE_SERVER_SIDE_UPSERT_MUTATIONS,
        allowServerSideMutations);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    conn.setAutoCommit(true);
    String tableName = generateUniqueName();
    conn.createStatement().execute("CREATE SEQUENCE "+ tableName + "_seq CACHE 1000");
    conn.createStatement().execute("CREATE TABLE " + tableName
            + " (pk INTEGER PRIMARY KEY, val INTEGER) UPDATE_CACHE_FREQUENCY=3600000");

    conn.createStatement().execute(
        "UPSERT INTO " + tableName + " VALUES (NEXT VALUE FOR "+ tableName + "_seq, 1)");
    PreparedStatement stmt =
            conn.prepareStatement("UPSERT INTO " + tableName
                    + " SELECT NEXT VALUE FOR "+ tableName + "_seq, val FROM " + tableName);
    Admin admin =
            driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin();
    for (int i=0; i<12; i++) {
        try {
            admin.split(TableName.valueOf(tableName));
        } catch (IOException ignore) {
            // we don't care if the split sometime cannot be executed
        }
        int upsertCount = stmt.executeUpdate();
        assertEquals((int)Math.pow(2, i), upsertCount);
    }
    admin.close();
    conn.close();
}
 

@Test
public void testArraySizeRoundtrip() throws Exception {

    String tenantId = getOrganizationId();
    String table = createTableWithArray(getUrl(),
            getDefaultSplits(tenantId), null);
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        ResultSet rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("x_long_array")));
        assertTrue(rs.next());
        assertEquals(5, rs.getInt("ARRAY_SIZE"));
        assertFalse(rs.next());

        rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("a_string_array")));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt("ARRAY_SIZE"));
        assertFalse(rs.next());

        rs = conn.getMetaData().getColumns(null, null, StringUtil.escapeLike(table), StringUtil.escapeLike(SchemaUtil.normalizeIdentifier("a_double_array")));
        assertTrue(rs.next());
        assertEquals(0, rs.getInt("ARRAY_SIZE"));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Override
public Exception call() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
        String ddl =
                "CREATE VIEW " + fullViewName + " (v2 VARCHAR) AS SELECT * FROM "
                        + fullTableName + " WHERE k = 5";
        conn.createStatement().execute(ddl);
    } catch (SQLException e) {
        return e;
    }
    return null;
}
 

@Test
public void testOffsetSerialQueryExecutedOnServer() throws SQLException {
    Connection conn;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    conn = DriverManager.getConnection(getUrl(), props);
    int offset = 10;
    createTestTable(getUrl(), ddl);
    initTableValues(conn);
    updateStatistics(conn);
    String query = "SELECT t_id from " + tableName + " offset " + offset;
    ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query);
    if(!isSalted){
        assertEquals("CLIENT SERIAL 1-WAY FULL SCAN OVER " + tableName + "\n"
                + "    SERVER FILTER BY FIRST KEY ONLY\n"
                + "    SERVER OFFSET " + offset, QueryUtil.getExplainPlan(rs));
    }else{
        assertEquals("CLIENT PARALLEL 10-WAY FULL SCAN OVER " + tableName + "\n"
                + "    SERVER FILTER BY FIRST KEY ONLY\n"
                + "CLIENT MERGE SORT\n" + "CLIENT OFFSET " + offset, QueryUtil.getExplainPlan(rs));
    }
    rs = conn.createStatement().executeQuery(query);
    int i = 0;
    while (i++ < STRINGS.length - offset) {
        assertTrue(rs.next());
        assertEquals(STRINGS[offset + i - 1], rs.getString(1));
    }
    query = "SELECT t_id from " + tableName + " ORDER BY v1 offset " + offset;
    rs = conn.createStatement().executeQuery("EXPLAIN " + query);
    if (!isSalted) {
        assertEquals("CLIENT PARALLEL 5-WAY FULL SCAN OVER " + tableName + "\n" + "    SERVER SORTED BY [C2.V1]\n"
                + "CLIENT MERGE SORT\n" + "CLIENT OFFSET " + offset, QueryUtil.getExplainPlan(rs));
    } else {
        assertEquals("CLIENT PARALLEL 10-WAY FULL SCAN OVER " + tableName + "\n" + "    SERVER SORTED BY [C2.V1]\n"
                + "CLIENT MERGE SORT\n" + "CLIENT OFFSET " + offset, QueryUtil.getExplainPlan(rs));
    }
    conn.close();
}
 

@Test
public void testUpsertValuesWithNull() throws Exception {
    long ts = nextTimestamp();
    String tenantId = getOrganizationId();
    createTableWithArray(getUrl(), getDefaultSplits(tenantId), null, ts - 2);
    String query = "upsert into table_with_array(ORGANIZATION_ID,ENTITY_ID,a_double_array) values('" + tenantId
            + "','00A123122312312',null)";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); // Execute
                                                                             // at
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        int executeUpdate = statement.executeUpdate();
        assertEquals(1, executeUpdate);
        conn.commit();
        statement.close();
        conn.close();
        // create another connection
        props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
        conn = DriverManager.getConnection(getUrl(), props);
        query = "SELECT ARRAY_ELEM(a_double_array,2) FROM table_with_array";
        statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        // Need to support primitive
        Double[] doubleArr = new Double[1];
        doubleArr[0] = 0.0d;
        conn.createArrayOf("DOUBLE", doubleArr);
        Double result = rs.getDouble(1);
        assertEquals(doubleArr[0], result);
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

protected void helpTestSelectAliasAndOrderByWithIndex(boolean mutable, boolean localIndex) throws Exception {
    String dataTableName = generateUniqueName();
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    String indexName = generateUniqueName();

    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        createDataTable(conn, fullDataTableName, mutable ? "" : "IMMUTABLE_ROWS=true");
        populateDataTable(conn, fullDataTableName);
        String ddl = "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexName + " ON " + fullDataTableName
                + " (int_col1+1)";

        conn.createStatement().execute(ddl);
        String sql = "SELECT int_col1+1 AS foo FROM " + fullDataTableName + " ORDER BY foo";
        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + sql);
        assertEquals("CLIENT PARALLEL 1-WAY "
                + (localIndex ? "RANGE SCAN OVER " + fullDataTableName
                        + " [1]\n    SERVER FILTER BY FIRST KEY ONLY\nCLIENT MERGE SORT"
                        : "FULL SCAN OVER INDEX_TEST." + indexName + "\n    SERVER FILTER BY FIRST KEY ONLY"),
                QueryUtil.getExplainPlan(rs));
        rs = conn.createStatement().executeQuery(sql);
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

/**
 * Test an upsert of a full row with dynamic Columns
 */
@Test
public void testFullUpsert() throws Exception {
    String upsertquery = "UPSERT INTO "
            + TABLE
            + " (a.DynColA VARCHAR,b.DynColB varchar) VALUES('dynEntry','aValue','bValue','DynColValuea','DynColValueb')";
    String selectquery = "SELECT entry,DynColA,a.dummy,DynColB,b.dummy FROM " + TABLE
            + " (a.DynColA VARCHAR,b.DynColB VARCHAR) where entry='dynEntry'";

    String url = getUrl() + ";";
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    conn.setAutoCommit(true);
    try {
        PreparedStatement statement = conn.prepareStatement(upsertquery);
        int rowsInserted = statement.executeUpdate();
        assertEquals(1, rowsInserted);

        // since the upsert does not alter the schema check with a dynamicolumn
        statement = conn.prepareStatement(selectquery);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals("dynEntry", rs.getString(1));
        assertEquals("DynColValuea", rs.getString(2));
        assertEquals("aValue", rs.getString(3));
        assertEquals("DynColValueb", rs.getString(4));
        assertEquals("bValue", rs.getString(5));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

private static Connection getConnection() throws SQLException {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    props.setProperty(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(1));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    return conn;
}
 

@Test
public void testGroupByOrderPreservingDescSort() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String tableName = generateUniqueName();
    conn.createStatement().execute("CREATE TABLE " + tableName + " (k1 char(1) not null, k2 char(1) not null," +
            " constraint pk primary key (k1,k2)) split on ('ac','jc','nc')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'a')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'b')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'c')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('a', 'd')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'a')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'b')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'c')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('j', 'd')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'a')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'b')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'c')");
    conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES('n', 'd')");
    conn.commit();
    QueryBuilder queryBuilder = new QueryBuilder()
        .setSelectExpression("K1,COUNT(*)")
        .setSelectColumns(Lists.newArrayList("K1"))
        .setFullTableName(tableName)
        .setGroupByClause("K1")
        .setOrderByClause("K1 DESC");
    ResultSet rs = executeQuery(conn, queryBuilder);
    assertTrue(rs.next());
    assertEquals("n", rs.getString(1));
    assertEquals(4, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals("j", rs.getString(1));
    assertEquals(4, rs.getLong(2));
    assertTrue(rs.next());
    assertEquals("a", rs.getString(1));
    assertEquals(4, rs.getLong(2));
    assertFalse(rs.next());
    String expectedPhoenixPlan = "CLIENT PARALLEL 1-WAY REVERSE FULL SCAN OVER " + tableName + "\n" +
            "    SERVER FILTER BY FIRST KEY ONLY\n" +
            "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [K1]";
    validateQueryPlan(conn, queryBuilder, expectedPhoenixPlan, null);
}
 

@Test
public void testDiffDataTypes() throws Exception {
    String tableName1 = generateUniqueName();
    String tableName2 = generateUniqueName();
    String tableName3 = generateUniqueName();
    String tableName4 = generateUniqueName();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);

    String ddl = "create table " + tableName1 + " ( id bigint not null primary key, " +
            "firstname varchar(10), lastname varchar(10) )";
    createTestTable(getUrl(), ddl);
    String dml = "upsert into " + tableName1 + " values (?, ?, ?)";
    PreparedStatement stmt = conn.prepareStatement(dml);
    stmt.setInt(1, 1);
    stmt.setString(2, "john");
    stmt.setString(3, "doe");
    stmt.execute();
    stmt.setInt(1, 2);
    stmt.setString(2, "jane");
    stmt.setString(3, "doe");
    stmt.execute();
    conn.commit();

    ddl = "create table " + tableName2 + " ( id integer not null primary key, firstname char(12)," +
            " lastname varchar(12) )";
    createTestTable(getUrl(), ddl);
    dml = "upsert into " + tableName2 + " values (?, ?, ?)";
    stmt = conn.prepareStatement(dml);
    stmt.setInt(1, 1);
    stmt.setString(2, "sam");
    stmt.setString(3, "johnson");
    stmt.execute();
    stmt.setInt(1, 2);
    stmt.setString(2, "ann");
    stmt.setString(3, "wiely");
    stmt.execute();
    conn.commit();

    ddl = "create table " + tableName3 + " ( id varchar(20) not null primary key)";
    createTestTable(getUrl(), ddl);
    dml = "upsert into " + tableName3 + " values ('abcd')";
    stmt = conn.prepareStatement(dml);
    stmt.execute();
    conn.commit();
    ddl = "create table " + tableName4 + " ( id char(50) not null primary key)";
    createTestTable(getUrl(), ddl);
    dml = "upsert into " + tableName4 + " values ('xyz')";
    stmt = conn.prepareStatement(dml);
    stmt.execute();
    conn.commit();
    String query = "select id, 'foo' firstname, lastname from " + tableName1 + " union all" +
            " select * from " + tableName2;
    try {
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("foo", rs.getString(2));
        assertEquals("doe", rs.getString(3));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals("foo", rs.getString(2));
        assertEquals("doe", rs.getString(3));
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals("sam", rs.getString(2).trim());
        assertEquals("johnson", rs.getString(3));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals("ann", rs.getString(2).trim());
        assertEquals("wiely", rs.getString(3));
        assertFalse(rs.next());

        pstmt = conn.prepareStatement("select * from " + tableName3 + " union all select * from " + tableName4);
        rs = pstmt.executeQuery();
        assertTrue(rs.next());
        assertEquals("abcd", rs.getString(1));
        assertTrue(rs.next());
        assertEquals("xyz", rs.getString(1).trim());
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 

@Test
public void testMappingHbaseTableToPhoenixTable() throws Exception {
    String mtest = generateUniqueName();
    final TableName tableName = TableName.valueOf(mtest);
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    PhoenixConnection conn = DriverManager.getConnection(getUrl(), props).unwrap(PhoenixConnection.class);
    
    Admin admin = conn.getQueryServices().getAdmin();
    try {
        // Create table then get the single region for our new table.
        TableDescriptorBuilder builder = TableDescriptorBuilder.newBuilder(tableName);
        builder.addColumnFamily(ColumnFamilyDescriptorBuilder.of(Bytes.toBytes("cf1")))
                .addColumnFamily(ColumnFamilyDescriptorBuilder.of(Bytes.toBytes("cf2")));
        admin.createTable(builder.build());
        Table t = conn.getQueryServices().getTable(Bytes.toBytes(mtest));
        insertData(tableName.getName(), admin, t);
        t.close();
        // create phoenix table that maps to existing HBase table
        createPhoenixTable(mtest);
        
        String selectSql = "SELECT * FROM " + mtest;
        ResultSet rs = conn.createStatement().executeQuery(selectSql);
        ResultSetMetaData rsMetaData = rs.getMetaData();
        assertTrue("Expected single row", rs.next());
        // verify values from cf2 is not returned
        assertEquals("Number of columns", 2, rsMetaData.getColumnCount());
        assertEquals("Column Value", "value1", rs.getString(2));
        assertFalse("Expected single row ", rs.next());
        
        // delete the row
        String deleteSql = "DELETE FROM " + mtest + " WHERE id = 'row'";
        conn.createStatement().executeUpdate(deleteSql);
        conn.commit();
        
        // verify that no rows are returned when querying through phoenix
        rs = conn.createStatement().executeQuery(selectSql);
        assertFalse("Expected no row` ", rs.next());
        
        // verify that row with value for cf2 still exists when using hbase apis
        Scan scan = new Scan();
        ResultScanner results = t.getScanner(scan);
        Result result = results.next();
        assertNotNull("Expected single row", result);
        List<Cell> kvs = result.getColumnCells(Bytes.toBytes("cf2"), Bytes.toBytes("q2"));
        assertEquals("Expected single value ", 1, kvs.size());
        assertEquals("Column Value", "value2", Bytes.toString(kvs.get(0).getValueArray(), kvs.get(0).getValueOffset(), kvs.get(0).getValueLength()));
        assertNull("Expected single row", results.next());
    } finally {
        admin.close();
    }
}