phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [27/41] PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (Gabriel Reid)
Date Wed, 12 Mar 2014 21:31:54 GMT
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToNumberFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToNumberFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToNumberFunctionIT.java
new file mode 100644
index 0000000..b617dad
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ToNumberFunctionIT.java
@@ -0,0 +1,301 @@
+/*
+ * 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.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Time;
+import java.sql.Timestamp;
+import java.util.Locale;
+import java.util.Properties;
+
+import org.junit.AfterClass;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.util.PhoenixRuntime;
+
+/**
+ * Tests for the TO_NUMBER built-in function.
+ * 
+ * @see ToNumberFunction
+ * 
+ * @since 0.1
+ */
+public class ToNumberFunctionIT extends BaseClientManagedTimeIT {
+
+    // This test changes to locale to en_US, and saves the previous locale here
+    private static Locale saveLocale;
+
+    public static final String TO_NUMBER_TABLE_NAME = "TO_NUMBER_TABLE";
+    
+    public static final String TO_NUMBER_TABLE_DDL = "create table " + TO_NUMBER_TABLE_NAME +
+        "(a_id integer not null, \n" + 
+        "a_string char(4) not null, \n" +
+        "b_string char(4) not null, \n" + 
+        "a_date date not null, \n" + 
+        "a_time date not null, \n" + 
+        "a_timestamp timestamp not null \n" + 
+        "CONSTRAINT my_pk PRIMARY KEY (a_id, a_string))";
+    
+    private Date row1Date;
+    private Date row2Date;
+    private Date row3Date;
+    private Time row1Time;
+    private Time row2Time;
+    private Time row3Time;
+    private Timestamp row1Timestamp;
+    private Timestamp row2Timestamp;
+    private Timestamp row3Timestamp;
+
+    @BeforeClass
+    public static void setUpBeforeClass() {
+        saveLocale = Locale.getDefault();
+        Locale.setDefault(Locale.US);
+    }
+
+    @AfterClass
+    public static void tearDownAfterClass() {
+        Locale.setDefault(saveLocale);
+    }
+
+    @Before
+    public void initTable() throws Exception {
+        long ts = nextTimestamp();
+        createTestTable(getUrl(), TO_NUMBER_TABLE_DDL, null, ts-2);
+        String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(url, props);
+        conn.setAutoCommit(false);
+        
+        PreparedStatement stmt = conn.prepareStatement(
+                "upsert into " + TO_NUMBER_TABLE_NAME +
+                "    (a_id, " +
+                "    a_string," +
+                "    b_string," +
+                "    a_date," +
+                "    a_time," +
+                "    a_timestamp)" +
+                "VALUES (?, ?, ?, ?, ?, ?)");
+        
+        stmt.setInt(1, 1);
+        stmt.setString(2, "   1");
+        stmt.setString(3, "   1");
+        row1Date = new Date(System.currentTimeMillis() - 1000);
+        row1Time = new Time(System.currentTimeMillis() - 1000);
+        row1Timestamp = new Timestamp(System.currentTimeMillis() + 10000);
+        stmt.setDate(4, row1Date);
+        stmt.setTime(5, row1Time);
+        stmt.setTimestamp(6, row1Timestamp);
+        stmt.execute();
+        
+        stmt.setInt(1, 2);
+        stmt.setString(2, " 2.2");
+        stmt.setString(3, " 2.2");
+        row2Date = new Date(System.currentTimeMillis() - 10000);
+        row2Time = new Time(System.currentTimeMillis() - 1234);
+        row2Timestamp = new Timestamp(System.currentTimeMillis() + 1234567);
+        stmt.setDate(4, row2Date);
+        stmt.setTime(5, row2Time);
+        stmt.setTimestamp(6, row2Timestamp);
+        stmt.execute();
+        
+        stmt.setInt(1, 3);
+        stmt.setString(2, "$3.3");
+        stmt.setString(3, "$3.3");
+        row3Date = new Date(System.currentTimeMillis() - 100);
+        row3Time = new Time(System.currentTimeMillis() - 789);
+        row3Timestamp = new Timestamp(System.currentTimeMillis() + 78901);
+        stmt.setDate(4, row3Date);
+        stmt.setTime(5, row3Time);
+        stmt.setTimestamp(6, row3Timestamp);
+        stmt.execute();
+        
+        conn.commit();
+        conn.close();
+    }
+
+    @Test
+    public void testKeyFilterWithIntegerValue() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_string) = 1";
+        int expectedId = 1;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testKeyFilterWithDoubleValue() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_string) = 2.2";
+        int expectedId = 2;
+        runOneRowQueryTest(query, expectedId);
+    }
+
+    @Test
+    public void testNonKeyFilterWithIntegerValue() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(b_string) = 1";
+        int expectedId = 1;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testNonKeyFilterWithDoubleValue() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(b_string) = 2.2";
+        int expectedId = 2;
+        runOneRowQueryTest(query, expectedId);
+    }
+
+    @Test
+    public void testKeyProjectionWithIntegerValue() throws Exception {
+        String query = "select to_number(a_string) from " + TO_NUMBER_TABLE_NAME + " where a_id = 1";
+        int expectedIntValue = 1;
+        runOneRowQueryTest(query, expectedIntValue);
+    }
+    
+    @Test
+    public void testKeyProjectionWithDecimalValue() throws Exception {
+        String query = "select to_number(a_string) from " + TO_NUMBER_TABLE_NAME + " where a_id = 2";
+        BigDecimal expectedDecimalValue = (BigDecimal)PDataType.DECIMAL.toObject("2.2");
+        runOneRowQueryTest(query, expectedDecimalValue);
+    }
+    
+    @Test
+    public void testNonKeyProjectionWithIntegerValue() throws Exception {
+        String query = "select to_number(b_string) from " + TO_NUMBER_TABLE_NAME + " where a_id = 1";
+        int expectedIntValue = 1;
+        runOneRowQueryTest(query, expectedIntValue);
+    }
+    
+    @Test
+    public void testNonKeyProjectionWithDecimalValue() throws Exception {
+        String query = "select to_number(b_string) from " + TO_NUMBER_TABLE_NAME + " where a_id = 2";
+        BigDecimal expectedDecimalValue = (BigDecimal)PDataType.DECIMAL.toObject("2.2");
+        runOneRowQueryTest(query, expectedDecimalValue);
+    }
+    
+    @Test
+    public void testKeyFilterWithPatternParam() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_string, '\u00A4###.####') = 3.3";
+        int expectedId = 3;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testNonKeyFilterWithPatternParam() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(b_string, '\u00A4#.#') = 3.3";
+        int expectedId = 3;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testDateFilter() throws Exception {
+    	String pattern = "yyyyMMddHHmmssZ";
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_date, '" + pattern + "') = " + row1Date.getTime() ;
+        int expectedId = 1;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    
+    @Test
+    public void testTimeFilter() throws Exception {
+    	String pattern = "HH:mm:ss z";
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_time, '" + pattern + "') = " + row1Time.getTime() ;
+        int expectedId = 1;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testDateFilterWithoutPattern() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_date) = " + row2Date.getTime() ;
+        int expectedId = 2;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    
+    @Test
+    public void testTimeFilterWithoutPattern() throws Exception {
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_time) = " + row2Time.getTime() ;
+        int expectedId = 2;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testTimeStampFilter() throws Exception {
+    	String pattern = "yyMMddHHmmssZ";
+        String query = "SELECT a_id FROM " + TO_NUMBER_TABLE_NAME + " WHERE to_number(a_timestamp, '" + pattern + "') = " + row1Timestamp.getTime() ;
+        int expectedId = 1;
+        runOneRowQueryTest(query, expectedId);
+    }
+    
+    @Test
+    public void testDateProjection() throws Exception {
+        String query = "select to_number(a_date) from " + TO_NUMBER_TABLE_NAME + " where a_id = 1";
+        BigDecimal expectedDecimalValue = new BigDecimal(row1Date.getTime());
+        runOneRowQueryTest(query, expectedDecimalValue);
+    }
+    
+    @Test
+    public void testTimeProjection() throws Exception {
+        String query = "select to_number(a_time) from " + TO_NUMBER_TABLE_NAME + " where a_id = 2";
+        BigDecimal expectedDecimalValue = new BigDecimal(row2Time.getTime());
+        runOneRowQueryTest(query, expectedDecimalValue);
+    }
+    
+    @Test
+    public void testTimeStampProjection() throws Exception {
+        String query = "select to_number(a_timestamp) from " + TO_NUMBER_TABLE_NAME + " where a_id = 3";
+        BigDecimal expectedDecimalValue = new BigDecimal(row3Timestamp.getTime());
+        runOneRowQueryTest(query, expectedDecimalValue);
+    }
+    
+    private void runOneRowQueryTest(String oneRowQuery, BigDecimal expectedDecimalValue) throws Exception {
+    	runOneRowQueryTest(oneRowQuery, false, null, expectedDecimalValue);
+    }
+    
+    private void runOneRowQueryTest(String oneRowQuery, int expectedIntValue) throws Exception {
+    	runOneRowQueryTest(oneRowQuery, true, expectedIntValue, null);
+    }
+    
+    private void runOneRowQueryTest(String oneRowQuery, boolean isIntegerColumn, Integer expectedIntValue, BigDecimal expectedDecimalValue) throws Exception {
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL);
+        try {
+            PreparedStatement statement = conn.prepareStatement(oneRowQuery);
+            ResultSet rs = statement.executeQuery();
+            
+            assertTrue (rs.next());
+            if (isIntegerColumn)
+            	assertEquals(expectedIntValue.intValue(), rs.getInt(1));
+            else
+            	assertTrue(expectedDecimalValue == rs.getBigDecimal(1) || (expectedDecimalValue != null && expectedDecimalValue.compareTo(rs.getBigDecimal(1)) == 0));
+            assertFalse(rs.next());
+        }
+        finally {
+        	conn.close();
+        }
+    }
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/TopNIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TopNIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TopNIT.java
new file mode 100644
index 0000000..be12ad5
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TopNIT.java
@@ -0,0 +1,158 @@
+/*
+ * 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.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.apache.phoenix.util.TestUtil.ROW4;
+import static org.apache.phoenix.util.TestUtil.ROW5;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.junit.Test;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+
+public class TopNIT extends BaseClientManagedTimeIT {
+
+    @Test
+    public void testMultiOrderByExpr() throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+        String query = "SELECT entity_id FROM aTable ORDER BY b_string, entity_id LIMIT 5";
+        Properties props = new Properties(TEST_PROPERTIES);
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        try {
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW1);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW4);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW7);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW2);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW5);
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+    
+
+    @Test
+    public void testDescMultiOrderByExpr() throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+        String query = "SELECT entity_id FROM aTable ORDER BY b_string || entity_id desc LIMIT 5";
+        Properties props = new Properties(TEST_PROPERTIES);
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        try {
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW9);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW6);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW3);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW8);
+            assertTrue (rs.next());
+            assertEquals(rs.getString(1), ROW5);
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+    
+
+    @Test
+    public void testTopNDeleteAutoCommitOn() throws Exception {
+        testTopNDelete(true);
+    }
+    
+    @Test
+    public void testTopNDeleteAutoCommitOff() throws Exception {
+        testTopNDelete(false);
+    }
+    
+    private void testTopNDelete(boolean autoCommit) throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+        String query = "DELETE FROM aTable ORDER BY b_string, entity_id LIMIT 5";
+        Properties props = new Properties(TEST_PROPERTIES);
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(autoCommit);
+        try {
+            PreparedStatement statement = conn.prepareStatement(query);
+            statement.execute();
+            assertEquals(5,statement.getUpdateCount());
+            if (!autoCommit) {
+                conn.commit();
+            }
+        } finally {
+            conn.close();
+        }
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4)); // Execute at timestamp 4
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        query = "SELECT entity_id FROM aTable ORDER BY b_string, x_decimal nulls last, 8-a_integer LIMIT 5";
+        try {
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            assertTrue (rs.next());
+            assertEquals(ROW8, rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW9, rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW6, rs.getString(1));
+            assertTrue (rs.next());
+            assertEquals(ROW3, rs.getString(1));
+
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+    
+
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java
new file mode 100644
index 0000000..b499c32
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TruncateFunctionIT.java
@@ -0,0 +1,121 @@
+/*
+ * 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.apache.phoenix.util.PhoenixRuntime.CURRENT_SCN_ATTRIB;
+import static org.apache.phoenix.util.TestUtil.ATABLE_NAME;
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.Timestamp;
+import java.text.Format;
+import java.text.ParseException;
+import java.util.Properties;
+
+import org.apache.phoenix.query.QueryConstants;
+import org.apache.phoenix.util.DateUtil;
+import org.junit.Test;
+
+public class TruncateFunctionIT extends BaseClientManagedTimeIT {
+    private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_MS_DATE_FORMAT);
+    private static final String DS1 = "1970-01-10 00:58:01.587";
+    private static final String DS2 = "1970-01-20 01:02:45.906";
+    private static final String DS3 = "1970-01-30 01:30:24.353";
+    
+    private static Date toDate(String s) throws ParseException {
+        return (Date) (format.parseObject(s));
+    }
+    
+    private static Timestamp toTimestamp(String s) throws ParseException {
+        return new Timestamp(((Date) (format.parseObject(s))).getTime());
+    }
+    
+    @Test
+    public void testTruncate() throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        ensureTableCreated(getUrl(), ATABLE_NAME, ts-5);
+        Properties props = new Properties();
+        props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts-3));
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        try {
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " +
+                    "ATABLE(" +
+                    "    ORGANIZATION_ID, " +
+                    "    ENTITY_ID, " +
+                    "    A_DATE, " +
+                    "    A_TIMESTAMP)" +
+                    "VALUES (?, ?, ?, ?)");
+            stmt.setString(1, tenantId);
+            stmt.setString(2, ROW1);
+            stmt.setDate(3, toDate(DS1));
+            stmt.setTimestamp(4, toTimestamp(DS1));
+            stmt.execute();
+            stmt.setString(1, tenantId);
+            stmt.setString(2, ROW2);
+            stmt.setDate(3, toDate(DS2));
+            stmt.setTimestamp(4, toTimestamp(DS2));
+            stmt.execute();
+            stmt.setString(1, tenantId);
+            stmt.setString(2, ROW3);
+            stmt.setDate(3, toDate(DS3));
+            stmt.setTimestamp(4, toTimestamp(DS3));
+            stmt.execute();
+            conn.commit();
+            conn.close();
+            
+            props.setProperty(CURRENT_SCN_ATTRIB, Long.toString(ts+1));
+            conn = DriverManager.getConnection(getUrl(), props);
+            String query = "SELECT entity_id, trunc(a_date, 'day', 7), trunc(a_timestamp, 'second', 10) FROM ATABLE WHERE organization_id = ?";
+            PreparedStatement statement = conn.prepareStatement(query);
+            statement.setString(1, tenantId);
+            ResultSet rs = statement.executeQuery();
+            
+            assertTrue (rs.next());
+            assertEquals(ROW1, rs.getString(1));
+            assertEquals(new Date((long) 7 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
+            assertEquals(toTimestamp("1970-01-10 00:58:00.000"), rs.getTimestamp(3));
+            
+            assertTrue (rs.next());
+            assertEquals(ROW2, rs.getString(1));
+            assertEquals(new Date((long) 14 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
+            assertEquals(toTimestamp("1970-01-20 01:02:40.000"), rs.getTimestamp(3));
+            
+            assertTrue (rs.next());
+            assertEquals(ROW3, rs.getString(1));
+            assertEquals(new Date((long) 28 * QueryConstants.MILLIS_IN_DAY), rs.getDate(2));
+            assertEquals(toTimestamp("1970-01-30 01:30:20.000"), rs.getTimestamp(3));
+            
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+}
+

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertBigValuesIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertBigValuesIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertBigValuesIT.java
new file mode 100644
index 0000000..c9bdd2a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertBigValuesIT.java
@@ -0,0 +1,369 @@
+/*
+ * 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.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.junit.Assert.*;
+
+import java.sql.*;
+import java.util.Properties;
+
+import org.junit.Test;
+
+
+public class UpsertBigValuesIT extends BaseHBaseManagedTimeIT {
+
+    private static final long INTEGER_MIN_MINUS_ONE = (long)Integer.MIN_VALUE - 1;
+    private static final long INTEGER_MAX_PLUS_ONE = (long)Integer.MAX_VALUE + 1;
+
+    @Test
+    public void testIntegerPK() throws Exception {
+        int[] testNumbers = {Integer.MIN_VALUE, Integer.MIN_VALUE + 1,
+                -2, -1, 0, 1, 2, Integer.MAX_VALUE - 1, Integer.MAX_VALUE};
+        ensureTableCreated(getUrl(),"PKIntValueTest");
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO PKIntValueTest VALUES(?)";
+        PreparedStatement stmt = conn.prepareStatement(upsert);
+        for (int i = 0; i < testNumbers.length; i++) {
+            stmt.setInt(1, testNumbers[i]);
+            stmt.execute();
+        }
+        conn.commit();
+        conn.close();
+        
+        String select = "SELECT COUNT(*) from PKIntValueTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKIntValueTest where pk >= " + Integer.MIN_VALUE + 
+                " GROUP BY pk ORDER BY pk ASC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        // NOTE: This case currently fails with an error message:
+        // "Overflow trying to get next key for [-1, -1, -1, -1]"
+        select = "SELECT count(*) FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKIntValueTest where pk <= " + Integer.MAX_VALUE + 
+                " GROUP BY pk ORDER BY pk DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length - 1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        // NOTE: This case currently fails since it is not retrieving the negative values.
+        select = "SELECT count(*) FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKIntValueTest where pk >= " + INTEGER_MIN_MINUS_ONE + 
+                " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        // NOTE: This test case fails because it is not retrieving positive values.
+        select = "SELECT count(*) FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKIntValueTest where pk <= " + INTEGER_MAX_PLUS_ONE + 
+                " GROUP BY pk ORDER BY pk DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length - 1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testBigIntPK() throws Exception {
+      // NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of 
+      // bigint is not recognizable in the current version. As a result, we start with 
+      // Long.MIN_VALUE+1 as the smallest value.
+        long[] testNumbers = {Long.MIN_VALUE+1 , Long.MIN_VALUE+2 , 
+                -2L, -1L, 0L, 1L, 2L, Long.MAX_VALUE-1, Long.MAX_VALUE};
+        ensureTableCreated(getUrl(),"PKBigIntValueTest");
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO PKBigIntValueTest VALUES(?)";
+        PreparedStatement stmt = conn.prepareStatement(upsert);
+        for (int i=0; i<testNumbers.length; i++) {
+            stmt.setLong(1, testNumbers[i]);
+            stmt.execute();
+        }
+        conn.commit();
+        conn.close();
+        
+        String select = "SELECT COUNT(*) from PKBigIntValueTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM PKBigIntValueTest where pk >= " + (Long.MIN_VALUE + 1);
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKBigIntValueTest WHERE pk >= " + (Long.MIN_VALUE + 1) +
+                " GROUP BY pk ORDER BY pk ASC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM PKBigIntValueTest where pk <= " + Long.MAX_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKBigIntValueTest WHERE pk <= " + Long.MAX_VALUE + 
+                " GROUP BY pk ORDER BY pk DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length - 1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        
+        /* NOTE: This section currently fails due to the fact that we cannot parse literal values
+           that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
+           before enabling this section of the test.
+        select = "SELECT count(*) FROM PKBigIntValueTest where pk >= " + LONG_MIN_MINUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKBigIntValueTest WHERE pk >= " + LONG_MIN_MINUS_ONE +
+                " GROUP BY pk ORDER BY pk ASC NULLS LAST ";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM PKBigIntValueTest where pk <= " + LONG_MAX_PLUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT pk FROM PKBigIntValueTest WHERE pk <= " + LONG_MAX_PLUS_ONE +
+                " GROUP BY pk ORDER BY pk DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length-1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        */
+    }
+
+    @Test
+    public void testIntegerKV() throws Exception {
+        int[] testNumbers = {Integer.MIN_VALUE, Integer.MIN_VALUE + 1, 
+                -2, -1, 0, 1, 2, Integer.MAX_VALUE - 1, Integer.MAX_VALUE};
+        ensureTableCreated(getUrl(),"KVIntValueTest");
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO KVIntValueTest VALUES(?, ?)";
+        PreparedStatement stmt = conn.prepareStatement(upsert);
+        for (int i=0; i<testNumbers.length; i++) {
+            stmt.setInt(1, i);
+            stmt.setInt(2, testNumbers[i]);
+            stmt.execute();
+        }
+        conn.commit();
+        conn.close();
+        
+        String select = "SELECT COUNT(*) from KVIntValueTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVIntValueTest where kv >= " + Integer.MIN_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVIntValueTest WHERE kv >= " + Integer.MIN_VALUE +
+                " GROUP BY kv ORDER BY kv ASC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i=0; i<testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVIntValueTest where kv <= " + Integer.MAX_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVIntValueTest WHERE kv <= " + Integer.MAX_VALUE +
+                " GROUP BY kv ORDER BY kv DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i=testNumbers.length-1; i>=0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVIntValueTest where kv >= " + INTEGER_MIN_MINUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVIntValueTest WHERE kv >= " + INTEGER_MIN_MINUS_ONE +
+                " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i=0; i<testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVIntValueTest where kv <= " + INTEGER_MAX_PLUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVIntValueTest WHERE kv <= " + INTEGER_MAX_PLUS_ONE +
+                " GROUP BY kv ORDER BY kv DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i=testNumbers.length-1; i>=0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testBigIntKV() throws Exception {
+        // NOTE: Due to how we parse negative long, -9223372036854775808L, the minimum value of 
+        // bigint is not recognizable in the current version. As a result, we start with 
+        // Long.MIN_VALUE+1 as the smallest value.
+        long[] testNumbers = {Long.MIN_VALUE+1, Long.MIN_VALUE+2, 
+                -2L, -1L, 0L, 1L, 2L, Long.MAX_VALUE-1, Long.MAX_VALUE};
+        ensureTableCreated(getUrl(),"KVBigIntValueTest");
+        Properties props = new Properties();
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO KVBigIntValueTest VALUES(?,?)";
+        PreparedStatement stmt = conn.prepareStatement(upsert);
+        for (int i = 0; i < testNumbers.length; i++) {
+            stmt.setLong(1, i);
+            stmt.setLong(2, testNumbers[i]);
+            stmt.execute();
+        }
+        conn.commit();
+        conn.close();
+        
+        String select = "SELECT COUNT(*) from KVBigIntValueTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + (Long.MIN_VALUE+1);
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + (Long.MIN_VALUE+1) + 
+                " GROUP BY kv ORDER BY kv ASC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + Long.MAX_VALUE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + Long.MAX_VALUE +
+                " GROUP BY kv ORDER BY kv DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length-1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getLong(1));
+        }
+        assertFalse(rs.next());
+        
+        /* NOTE: This section currently fails due to the fact that we cannot parse literal values
+           that are bigger than Long.MAX_VALUE and Long.MIN_VALUE. We will need to fix the parse
+           before enabling this section of the test.
+        select = "SELECT count(*) FROM KVBigIntValueTest where kv >= " + LONG_MIN_MINUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVBigIntValueTest WHERE kv >= " + LONG_MIN_MINUS_ONE +
+                " GROUP BY kv ORDER BY kv ASC NULLS LAST ";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = 0; i < testNumbers.length; i++) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        
+        select = "SELECT count(*) FROM KVBigIntValueTest where kv <= " + LONG_MAX_PLUS_ONE;
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(testNumbers.length, rs.getInt(1));
+        assertFalse(rs.next());
+        select = "SELECT kv FROM KVBigIntValueTest WHERE kv <= " + LONG_MAX_PLUS_ONE +
+                " GROUP BY kv ORDER BY kv DESC NULLS LAST";
+        rs = conn.createStatement().executeQuery(select);
+        for (int i = testNumbers.length-1; i >= 0; i--) {
+            assertTrue(rs.next());
+            assertEquals(testNumbers[i], rs.getInt(1));
+        }
+        assertFalse(rs.next());
+        */
+    }
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java
new file mode 100644
index 0000000..feb3fbc
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectAutoCommitIT.java
@@ -0,0 +1,135 @@
+/*
+ * 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.apache.phoenix.util.TestUtil.A_VALUE;
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.junit.Test;
+
+public class UpsertSelectAutoCommitIT extends BaseHBaseManagedTimeIT {
+
+    public UpsertSelectAutoCommitIT() {
+    }
+
+    @Test
+    public void testAutoCommitUpsertSelect() throws Exception {
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        conn.setAutoCommit(true);
+        conn.createStatement().execute("CREATE TABLE atable (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" +
+        "CONSTRAINT pk PRIMARY KEY (organization_id, entity_id))");
+        
+        String tenantId = getOrganizationId();
+       // Insert all rows at ts
+        PreparedStatement stmt = conn.prepareStatement(
+                "upsert into " +
+                "ATABLE(" +
+                "    ORGANIZATION_ID, " +
+                "    ENTITY_ID, " +
+                "    A_STRING " +
+                "    )" +
+                "VALUES (?, ?, ?)");
+        stmt.setString(1, tenantId);
+        stmt.setString(2, ROW1);
+        stmt.setString(3, A_VALUE);
+        stmt.execute();
+        
+        String query = "SELECT entity_id, a_string FROM ATABLE";
+        PreparedStatement statement = conn.prepareStatement(query);
+        ResultSet rs = statement.executeQuery();
+        
+        assertTrue(rs.next());
+        assertEquals(ROW1, rs.getString(1));
+        assertEquals(A_VALUE, rs.getString(2));
+        assertFalse(rs.next());
+        
+        conn.createStatement().execute("CREATE TABLE atable2 (ORGANIZATION_ID CHAR(15) NOT NULL, ENTITY_ID CHAR(15) NOT NULL, A_STRING VARCHAR\n" +
+        "CONSTRAINT pk PRIMARY KEY (organization_id, entity_id DESC))");
+        
+        conn.createStatement().execute("UPSERT INTO atable2 SELECT * FROM ATABLE");
+        query = "SELECT entity_id, a_string FROM ATABLE2";
+        statement = conn.prepareStatement(query);
+        rs = statement.executeQuery();
+        
+        assertTrue(rs.next());
+        assertEquals(ROW1, rs.getString(1));
+        assertEquals(A_VALUE, rs.getString(2));
+        assertFalse(rs.next());
+        
+    }
+
+    @Test
+    public void testDynamicUpsertSelect() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        String cursorDDL = " CREATE TABLE IF NOT EXISTS CURSOR (ORGANIZATION_ID VARCHAR(15) NOT NULL, \n"
+                + "QUERY_ID VARCHAR(15) NOT NULL, \n"
+                + "CURSOR_ORDER UNSIGNED_LONG NOT NULL, \n"
+                + "CONSTRAINT API_HBASE_CURSOR_STORAGE_PK PRIMARY KEY (ORGANIZATION_ID, QUERY_ID, CURSOR_ORDER))\n"
+                + "SALT_BUCKETS = 4";
+        conn.createStatement().execute(cursorDDL);
+        
+        String dataTableDDL = "CREATE TABLE IF NOT EXISTS PLINYTEST" +
+                "(" +
+                "ORGANIZATION_ID CHAR(15) NOT NULL, " +
+                "PLINY_ID CHAR(15) NOT NULL, " +
+                "CREATED_DATE DATE NOT NULL, " + 
+                "TEXT VARCHAR, " +
+                "CONSTRAINT PK PRIMARY KEY " +
+                "(" +
+                "ORGANIZATION_ID, " +
+                "PLINY_ID, "  +
+                "CREATED_DATE" +
+                ")" +
+                ")";
+        
+        conn.createStatement().execute(dataTableDDL);
+        PreparedStatement stmt = null;
+        String upsert = "UPSERT INTO PLINYTEST VALUES (?, ?, ?, ?)";
+        stmt = conn.prepareStatement(upsert);
+        stmt.setString(1, getOrganizationId());
+        stmt.setString(2, "aaaaaaaaaaaaaaa");
+        stmt.setDate(3, new Date(System.currentTimeMillis()));
+        stmt.setString(4, "text");
+        stmt.executeUpdate();
+        conn.commit();
+        
+        String upsertSelect = "UPSERT INTO CURSOR (ORGANIZATION_ID, QUERY_ID, CURSOR_ORDER, PLINY_ID CHAR(15),CREATED_DATE DATE) SELECT ?, ?, ?, PLINY_ID, CREATED_DATE FROM PLINYTEST WHERE ORGANIZATION_ID = ?";
+        stmt = conn.prepareStatement(upsertSelect);
+        String orgId = getOrganizationId();
+        stmt.setString(1, orgId);
+        stmt.setString(2, "queryqueryquery");
+
+        stmt.setInt(3, 1);
+        stmt.setString(4, orgId);
+        stmt.executeUpdate();
+        conn.commit();
+    }
+    
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectIT.java
new file mode 100644
index 0000000..893fdb9
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UpsertSelectIT.java
@@ -0,0 +1,729 @@
+/*
+ * 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.apache.phoenix.util.PhoenixRuntime.UPSERT_BATCH_SIZE_ATTRIB;
+import static org.apache.phoenix.util.TestUtil.A_VALUE;
+import static org.apache.phoenix.util.TestUtil.B_VALUE;
+import static org.apache.phoenix.util.TestUtil.CUSTOM_ENTITY_DATA_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.C_VALUE;
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.PTSDB_NAME;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.apache.phoenix.query.QueryConstants;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Test;
+
+
+public class UpsertSelectIT extends BaseClientManagedTimeIT {
+    
+    @Test
+    public void testUpsertSelectWithNoIndex() throws Exception {
+        testUpsertSelect(false);
+    }
+    
+    @Test
+    public void testUpsertSelecWithIndex() throws Exception {
+        testUpsertSelect(true);
+    }
+    
+    private void testUpsertSelect(boolean createIndex) throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts-1);
+        ensureTableCreated(PHOENIX_JDBC_URL, CUSTOM_ENTITY_DATA_FULL_NAME, ts-1);
+        String indexName = "IDX1";
+        if (createIndex) {
+            Properties props = new Properties();
+            props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts)); // Execute at timestamp 1
+            Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+            conn.createStatement().execute("CREATE INDEX IF NOT EXISTS " + indexName + " ON " + TestUtil.ATABLE_NAME + "(a_string)" );
+            conn.close();
+        }
+        PreparedStatement upsertStmt;
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+        props.setProperty(UPSERT_BATCH_SIZE_ATTRIB, Integer.toString(3)); // Trigger multiple batches
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true);
+        String upsert = "UPSERT INTO " + CUSTOM_ENTITY_DATA_FULL_NAME + "(custom_entity_data_id, key_prefix, organization_id, created_by) " +
+            "SELECT substr(entity_id, 4), substr(entity_id, 1, 3), organization_id, a_string  FROM ATABLE WHERE ?=a_string";
+        if (createIndex) { // Confirm index is used
+            upsertStmt = conn.prepareStatement("EXPLAIN " + upsert);
+            upsertStmt.setString(1, tenantId);
+            ResultSet ers = upsertStmt.executeQuery();
+            assertTrue(ers.next());
+            String explainPlan = QueryUtil.getExplainPlan(ers);
+            assertTrue(explainPlan.contains(" SCAN OVER " + indexName));
+        }
+        
+        upsertStmt = conn.prepareStatement(upsert);
+        upsertStmt.setString(1, A_VALUE);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(4, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        String query = "SELECT key_prefix, substr(custom_entity_data_id, 1, 1), created_by FROM " + CUSTOM_ENTITY_DATA_FULL_NAME + " WHERE organization_id = ? ";
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 3)); // Execute at timestamp 3
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        PreparedStatement statement = conn.prepareStatement(query);
+        statement.setString(1, tenantId);
+        ResultSet rs = statement.executeQuery();
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("1", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("2", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("3", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("4", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+
+        assertFalse(rs.next());
+        conn.close();
+
+        // Test UPSERT through coprocessor
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true);
+        upsert = "UPSERT INTO " + CUSTOM_ENTITY_DATA_FULL_NAME + "(custom_entity_data_id, key_prefix, organization_id, last_update_by, division) " +
+            "SELECT custom_entity_data_id, key_prefix, organization_id, created_by, 1.0  FROM " + CUSTOM_ENTITY_DATA_FULL_NAME + " WHERE organization_id = ? and created_by >= 'a'";
+        
+        upsertStmt = conn.prepareStatement(upsert);
+        upsertStmt.setString(1, tenantId);
+        assertEquals(4, upsertStmt.executeUpdate());
+        conn.commit();
+
+        query = "SELECT key_prefix, substr(custom_entity_data_id, 1, 1), created_by, last_update_by, division FROM " + CUSTOM_ENTITY_DATA_FULL_NAME + " WHERE organization_id = ?";
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5)); 
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        statement = conn.prepareStatement(query);
+        statement.setString(1, tenantId);
+        rs = statement.executeQuery();
+       
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("1", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        assertEquals(A_VALUE, rs.getString(4));
+        assertTrue(BigDecimal.valueOf(1.0).compareTo(rs.getBigDecimal(5)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("2", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        assertEquals(A_VALUE, rs.getString(4));
+        assertTrue(BigDecimal.valueOf(1.0).compareTo(rs.getBigDecimal(5)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("3", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        assertEquals(A_VALUE, rs.getString(4));
+        assertTrue(BigDecimal.valueOf(1.0).compareTo(rs.getBigDecimal(5)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals("00A", rs.getString(1));
+        assertEquals("4", rs.getString(2));
+        assertEquals(A_VALUE, rs.getString(3));
+        assertEquals(A_VALUE, rs.getString(4));
+        assertTrue(BigDecimal.valueOf(1.0).compareTo(rs.getBigDecimal(5)) == 0);
+
+        assertFalse(rs.next());
+        conn.close();
+    }
+
+    // TODO: more tests - nullable fixed length last PK column
+    @Test
+    public void testUpsertSelectEmptyPKColumn() throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts-1);
+        ensureTableCreated(PHOENIX_JDBC_URL, PTSDB_NAME, ts-1);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(false);
+        String upsert = "UPSERT INTO " + PTSDB_NAME + "(date, val, host) " +
+            "SELECT current_date(), x_integer+2, entity_id FROM ATABLE WHERE a_integer >= ?";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        upsertStmt.setInt(1, 6);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(4, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        String query = "SELECT inst,host,date,val FROM " + PTSDB_NAME;
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        PreparedStatement statement = conn.prepareStatement(query);
+        ResultSet rs = statement.executeQuery();
+        
+        Date now = new Date(System.currentTimeMillis());
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW6, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertEquals(null, rs.getBigDecimal(4));
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW7, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(7).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW8, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(6).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW9, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(4)) == 0);
+
+        assertFalse(rs.next());
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 3));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true);
+        upsert = "UPSERT INTO " + PTSDB_NAME + "(date, val, inst) " +
+            "SELECT date+1, val*10, host FROM " + PTSDB_NAME;
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(4, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        Date then = new Date(now.getTime() + QueryConstants.MILLIS_IN_DAY);
+        query = "SELECT host,inst, date,val FROM " + PTSDB_NAME + " where inst is not null";
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4)); // Execute at timestamp 2
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        statement = conn.prepareStatement(query);
+        
+        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());
+        conn.close();
+        
+        // Should just update all values with the same value, essentially just updating the timestamp
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true);
+        upsert = "UPSERT INTO " + PTSDB_NAME + " SELECT * FROM " + PTSDB_NAME;
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(8, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        query = "SELECT * FROM " + PTSDB_NAME ;
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4)); // Execute at timestamp 2
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        statement = conn.prepareStatement(query);
+        
+        rs = statement.executeQuery();
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW6, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertEquals(null, rs.getBigDecimal(4));
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW7, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(7).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW8, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(6).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(ROW9, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(4)) == 0);
+
+        assertTrue (rs.next());
+        assertEquals(ROW6, rs.getString(1));
+        assertEquals(null, rs.getString(2));
+        assertTrue(rs.getDate(3).after(now) && rs.getDate(3).before(then));
+        assertEquals(null, rs.getBigDecimal(4));
+        
+        assertTrue (rs.next());
+        assertEquals(ROW7, rs.getString(1));
+        assertEquals(null, 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(ROW8, rs.getString(1));
+        assertEquals(null, 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(ROW9, rs.getString(1));
+        assertEquals(null, 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());
+        conn.close();
+    }
+
+    @Test
+    public void testUpsertSelectForAggAutoCommit() throws Exception {
+        testUpsertSelectForAgg(true);
+    }
+    
+    @Test
+    public void testUpsertSelectForAgg() throws Exception {
+        testUpsertSelectForAgg(false);
+    }
+    
+    private void testUpsertSelectForAgg(boolean autoCommit) throws Exception {
+        long ts = nextTimestamp();
+        String tenantId = getOrganizationId();
+        initATableValues(tenantId, getDefaultSplits(tenantId), null, ts-1);
+        ensureTableCreated(PHOENIX_JDBC_URL, PTSDB_NAME, ts-1);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(autoCommit);
+        String upsert = "UPSERT INTO " + PTSDB_NAME + "(date, val, host) " +
+            "SELECT current_date(), sum(a_integer), a_string FROM ATABLE GROUP BY a_string";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(3, rowsInserted);
+        if (!autoCommit) {
+            conn.commit();
+        }
+        conn.close();
+        
+        String query = "SELECT inst,host,date,val FROM " + PTSDB_NAME;
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        PreparedStatement statement = conn.prepareStatement(query);
+        ResultSet rs = statement.executeQuery();
+        Date now = new Date(System.currentTimeMillis());
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(A_VALUE, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(10).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(B_VALUE, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(26).compareTo(rs.getBigDecimal(4)) == 0);
+        
+        assertTrue (rs.next());
+        assertEquals(null, rs.getString(1));
+        assertEquals(C_VALUE, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(9).compareTo(rs.getBigDecimal(4)) == 0);
+        assertFalse(rs.next());
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 3));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true);
+        upsert = "UPSERT INTO " + PTSDB_NAME + "(date, val, host, inst) " +
+            "SELECT current_date(), max(val), max(host), 'x' FROM " + PTSDB_NAME;
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        if (!autoCommit) {
+            conn.commit();
+        }
+        conn.close();
+        
+        query = "SELECT inst,host,date,val FROM " + PTSDB_NAME + " WHERE inst='x'";
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 4));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        statement = conn.prepareStatement(query);
+        rs = statement.executeQuery();
+        now = new Date(System.currentTimeMillis());
+        
+        assertTrue (rs.next());
+        assertEquals("x", rs.getString(1));
+        assertEquals(C_VALUE, rs.getString(2));
+        assertTrue(rs.getDate(3).before(now) );
+        assertTrue(BigDecimal.valueOf(26).compareTo(rs.getBigDecimal(4)) == 0);
+        assertFalse(rs.next());
+        
+    }
+
+    @Test
+    public void testUpsertSelectLongToInt() throws Exception {
+        byte[][] splits = new byte[][] {PDataType.INTEGER.toBytes(1), PDataType.INTEGER.toBytes(2),
+                PDataType.INTEGER.toBytes(3), PDataType.INTEGER.toBytes(4)};
+        long ts = nextTimestamp();
+        ensureTableCreated(getUrl(),"IntKeyTest",splits, ts-2);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO IntKeyTest VALUES(1)";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        upsert = "UPSERT INTO IntKeyTest select i+1 from IntKeyTest";
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String select = "SELECT i FROM IntKeyTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(1,rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(2,rs.getInt(1));
+        assertFalse(rs.next());
+        conn.close();
+    }
+
+    @Test
+    public void testUpsertSelectRunOnServer() throws Exception {
+        byte[][] splits = new byte[][] {PDataType.INTEGER.toBytes(1), PDataType.INTEGER.toBytes(2),
+                PDataType.INTEGER.toBytes(3), PDataType.INTEGER.toBytes(4)};
+        long ts = nextTimestamp();
+        createTestTable(getUrl(), "create table IntKeyTest (i integer not null primary key desc, j integer)" ,splits, ts-2);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO IntKeyTest VALUES(1, 1)";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 3));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String select = "SELECT i,j+1 FROM IntKeyTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(1,rs.getInt(1));
+        assertEquals(2,rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true); // Force to run on server side.
+        upsert = "UPSERT INTO IntKeyTest(i,j) select i, j+1 from IntKeyTest";
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        select = "SELECT j FROM IntKeyTest";
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(2,rs.getInt(1));
+        assertFalse(rs.next());
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 15));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true); // Force to run on server side.
+        upsert = "UPSERT INTO IntKeyTest(i,j) select i, i from IntKeyTest";
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        select = "SELECT j FROM IntKeyTest";
+        rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(1,rs.getInt(1));
+        assertFalse(rs.next());
+        conn.close();
+    }
+
+    @Test
+    public void testUpsertSelectOnDescToAsc() throws Exception {
+        byte[][] splits = new byte[][] {PDataType.INTEGER.toBytes(1), PDataType.INTEGER.toBytes(2),
+                PDataType.INTEGER.toBytes(3), PDataType.INTEGER.toBytes(4)};
+        long ts = nextTimestamp();
+        createTestTable(getUrl(), "create table IntKeyTest (i integer not null primary key desc, j integer)" ,splits, ts-2);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO IntKeyTest VALUES(1, 1)";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.setAutoCommit(true); // Force to run on server side.
+        upsert = "UPSERT INTO IntKeyTest(i,j) select i+1, j+1 from IntKeyTest";
+        upsertStmt = conn.prepareStatement(upsert);
+        rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(1, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String select = "SELECT i,j FROM IntKeyTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(2,rs.getInt(1));
+        assertEquals(2,rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(1,rs.getInt(1));
+        assertEquals(1,rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+    }
+
+    @Test
+    public void testUpsertSelectRowKeyMutationOnSplitedTable() throws Exception {
+        byte[][] splits = new byte[][] {PDataType.INTEGER.toBytes(1), PDataType.INTEGER.toBytes(2),
+                PDataType.INTEGER.toBytes(3), PDataType.INTEGER.toBytes(4)};
+        long ts = nextTimestamp();
+        ensureTableCreated(getUrl(),"IntKeyTest",splits,ts-2);
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String upsert = "UPSERT INTO IntKeyTest VALUES(?)";
+        PreparedStatement upsertStmt = conn.prepareStatement(upsert);
+        upsertStmt.setInt(1, 1);
+        upsertStmt.executeUpdate();
+        upsertStmt.setInt(1, 3);
+        upsertStmt.executeUpdate();
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        // Normally this would force a server side update. But since this changes the PK column, it would
+        // for to run on the client side.
+        conn.setAutoCommit(true);
+        upsert = "UPSERT INTO IntKeyTest(i) SELECT i+1 from IntKeyTest";
+        upsertStmt = conn.prepareStatement(upsert);
+        int rowsInserted = upsertStmt.executeUpdate();
+        assertEquals(2, rowsInserted);
+        conn.commit();
+        conn.close();
+        
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        String select = "SELECT i FROM IntKeyTest";
+        ResultSet rs = conn.createStatement().executeQuery(select);
+        assertTrue(rs.next());
+        assertEquals(1,rs.getInt(1));
+        assertTrue(rs.next());
+        assertTrue(rs.next());
+        assertTrue(rs.next());
+        assertEquals(4,rs.getInt(1));
+        assertFalse(rs.next());
+        conn.close();
+    }
+    
+    @Test
+    public void testUpsertSelectWithLimit() throws Exception {
+        long ts = nextTimestamp();
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("create table phoenix_test (id varchar(10) not null primary key, val varchar(10), ts timestamp)");
+        conn.close();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("upsert into phoenix_test values ('aaa', 'abc', current_date())");
+        conn.createStatement().execute("upsert into phoenix_test values ('bbb', 'bcd', current_date())");
+        conn.createStatement().execute("upsert into phoenix_test values ('ccc', 'cde', current_date())");
+        conn.commit();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        ResultSet rs = conn.createStatement().executeQuery("select * from phoenix_test");
+        
+        assertTrue(rs.next());
+        assertEquals("aaa",rs.getString(1));
+        assertEquals("abc",rs.getString(2));
+        assertNotNull(rs.getDate(3));
+        
+        assertTrue(rs.next());
+        assertEquals("bbb",rs.getString(1));
+        assertEquals("bcd",rs.getString(2));
+        assertNotNull(rs.getDate(3));
+        
+        assertTrue(rs.next());
+        assertEquals("ccc",rs.getString(1));
+        assertEquals("cde",rs.getString(2));
+        assertNotNull(rs.getDate(3));
+        
+        assertFalse(rs.next());
+        conn.close();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 30));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("upsert into phoenix_test (id, ts) select id, null from phoenix_test where id <= 'bbb' limit 1");
+        conn.commit();
+        conn.close();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 40));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        rs = conn.createStatement().executeQuery("select * from phoenix_test");
+        
+        assertTrue(rs.next());
+        assertEquals("aaa",rs.getString(1));
+        assertEquals("abc",rs.getString(2));
+        assertNull(rs.getDate(3));
+        
+        assertTrue(rs.next());
+        assertEquals("bbb",rs.getString(1));
+        assertEquals("bcd",rs.getString(2));
+        assertNotNull(rs.getDate(3));
+        
+        assertTrue(rs.next());
+        assertEquals("ccc",rs.getString(1));
+        assertEquals("cde",rs.getString(2));
+        assertNotNull(rs.getDate(3));
+        
+        assertFalse(rs.next());
+        conn.close();
+
+    }
+    
+    @Test
+    public void testUpsertSelectWithSequence() throws Exception {
+        long ts = nextTimestamp();
+        Properties props = new Properties();
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+        Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("create table t1 (id bigint not null primary key, v varchar)");
+        conn.createStatement().execute("create table t2 (k varchar primary key)");
+        conn.createStatement().execute("create sequence s");
+        conn.close();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("upsert into t2 values ('a')");
+        conn.createStatement().execute("upsert into t2 values ('b')");
+        conn.createStatement().execute("upsert into t2 values ('c')");
+        conn.commit();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 15));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        conn.createStatement().execute("upsert into t1 select next value for s, k from t2");
+        conn.commit();
+
+        props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 20));
+        conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+        ResultSet rs = conn.createStatement().executeQuery("select * from t1");
+        
+        assertTrue(rs.next());
+        assertEquals(1,rs.getLong(1));
+        assertEquals("a",rs.getString(2));
+        
+        assertTrue(rs.next());
+        assertEquals(2,rs.getLong(1));
+        assertEquals("b",rs.getString(2));
+        
+        assertTrue(rs.next());
+        assertEquals(3,rs.getLong(1));
+        assertEquals("c",rs.getString(2));
+        
+        assertFalse(rs.next());
+        conn.close();
+    }
+}


Mime
View raw message