phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [29/41] PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
Date Wed, 12 Mar 2014 22:40:10 GMT
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
new file mode 100644
index 0000000..17f2308
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SequenceIT.java
@@ -0,0 +1,594 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.phoenix.end2end;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Map;
+import java.util.Properties;
+
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.jdbc.PhoenixStatement;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.SequenceAlreadyExistsException;
+import org.apache.phoenix.schema.SequenceNotFoundException;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import com.google.common.collect.Maps;
+
+public class SequenceIT extends BaseClientManagedTimeIT {
+    private static final long BATCH_SIZE = 3;
+    
+    private Connection conn;
+    
+    @BeforeClass 
+    public static void doSetup() throws Exception {
+        
+        Map<String,String> props = Maps.newHashMapWithExpectedSize(1);
+        // Make a small batch size to test multiple calls to reserve sequences
+        props.put(QueryServices.SEQUENCE_CACHE_SIZE_ATTRIB, Long.toString(BATCH_SIZE));
+        // Must update config before starting server
+        startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));
+    }
+    
+
+	@Test
+	public void testSystemTable() throws Exception {		
+		nextConnection();
+		String query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\"";
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertFalse(rs.next());
+	}
+
+	@Test
+	public void testDuplicateSequences() throws Exception {
+        nextConnection();
+		conn.createStatement().execute("CREATE SEQUENCE alpha.beta START WITH 2 INCREMENT BY 4\n");
+
+		try {
+	        nextConnection();
+			conn.createStatement().execute("CREATE SEQUENCE alpha.beta START WITH 2 INCREMENT BY 4\n");
+			Assert.fail("Duplicate sequences");
+		} catch (SequenceAlreadyExistsException e){
+
+		}
+	}
+
+	@Test
+	public void testSequenceNotFound() throws Exception {
+        nextConnection();
+		String query = "SELECT NEXT value FOR qwert.asdf FROM SYSTEM.\"SEQUENCE\"";
+		try {
+			conn.prepareStatement(query).executeQuery();
+			fail("Sequence not found");
+		}catch(SequenceNotFoundException e){
+
+		}
+	}
+
+	@Test
+	public void testCreateSequence() throws Exception {	
+        nextConnection();
+		conn.createStatement().execute("CREATE SEQUENCE alpha.omega START WITH 2 INCREMENT BY 4");
+        nextConnection();
+		String query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='OMEGA'";
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals("ALPHA", rs.getString("sequence_schema"));
+		assertEquals("OMEGA", rs.getString("sequence_name"));
+		assertEquals(2, rs.getInt("current_value"));
+		assertEquals(4, rs.getInt("increment_by"));
+		assertFalse(rs.next());
+	}
+		
+    @Test
+    public void testCurrentValueFor() throws Exception {
+        ResultSet rs;
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE used.nowhere START WITH 2 INCREMENT BY 4");
+        nextConnection();
+        try {
+            rs = conn.createStatement().executeQuery("SELECT CURRENT VALUE FOR used.nowhere FROM SYSTEM.\"SEQUENCE\"");
+            rs.next();
+            fail();
+        } catch (SQLException e) {
+            assertEquals(SQLExceptionCode.CANNOT_CALL_CURRENT_BEFORE_NEXT_VALUE.getErrorCode(), e.getErrorCode());
+        }
+        
+        rs = conn.createStatement().executeQuery("SELECT NEXT VALUE FOR used.nowhere FROM SYSTEM.\"SEQUENCE\"");
+        assertTrue(rs.next());
+        assertEquals(2, rs.getInt(1));
+        rs = conn.createStatement().executeQuery("SELECT CURRENT VALUE FOR used.nowhere FROM SYSTEM.\"SEQUENCE\"");
+        assertTrue(rs.next());
+        assertEquals(2, rs.getInt(1));
+	}
+
+    @Test
+    public void testDropSequence() throws Exception { 
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE alpha.omega START WITH 2 INCREMENT BY 4");
+        nextConnection();
+        String query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='OMEGA'";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals("ALPHA", rs.getString("sequence_schema"));
+        assertEquals("OMEGA", rs.getString("sequence_name"));
+        assertEquals(2, rs.getInt("current_value"));
+        assertEquals(4, rs.getInt("increment_by"));
+        assertFalse(rs.next());
+
+        conn.createStatement().execute("DROP SEQUENCE alpha.omega");
+        nextConnection();
+        query = "SELECT sequence_schema, sequence_name, current_value, increment_by FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='OMEGA'";
+        rs = conn.prepareStatement(query).executeQuery();
+        assertFalse(rs.next());
+
+        try {
+            conn.createStatement().execute("DROP SEQUENCE alpha.omega");
+            fail();
+        } catch (SequenceNotFoundException ignore) {
+        }
+    }
+
+	@Test
+	public void testSelectNextValueFor() throws Exception {
+        nextConnection();
+		conn.createStatement().execute("CREATE SEQUENCE foo.bar START WITH 3 INCREMENT BY 2");
+        nextConnection();
+		String query = "SELECT NEXT VALUE FOR foo.bar FROM SYSTEM.\"SEQUENCE\"";
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(3, rs.getInt(1));
+
+		rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(5, rs.getInt(1));
+
+		rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(7, rs.getInt(1));
+	}
+
+	@Test
+	public void testInsertNextValueFor() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE alpha.tau START WITH 2 INCREMENT BY 1");
+		conn.createStatement().execute("CREATE TABLE test.sequence_number ( id INTEGER NOT NULL PRIMARY KEY)");
+        nextConnection();
+		conn.createStatement().execute("UPSERT INTO test.sequence_number (id) VALUES (NEXT VALUE FOR alpha.tau)");
+        conn.createStatement().execute("UPSERT INTO test.sequence_number (id) VALUES (NEXT VALUE FOR alpha.tau)");
+		conn.commit();
+        nextConnection();
+		String query = "SELECT id FROM test.sequence_number";		
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(2, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(3, rs.getInt(1));
+	}
+
+	@Test
+	public void testSequenceCreation() throws Exception {		
+        nextConnection();
+		conn.createStatement().execute("CREATE SEQUENCE alpha.gamma START WITH 2 INCREMENT BY 3 CACHE 5");
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT start_with, increment_by, cache_size, sequence_schema, sequence_name FROM SYSTEM.\"SEQUENCE\"");
+        assertTrue(rs.next());
+        assertEquals(2, rs.getLong(1));
+        assertEquals(3, rs.getLong(2));
+        assertEquals(5, rs.getLong(3));
+        assertEquals("ALPHA", rs.getString(4));
+        assertEquals("GAMMA", rs.getString(5));
+        assertFalse(rs.next());
+		rs = conn.createStatement().executeQuery("SELECT NEXT VALUE FOR alpha.gamma, CURRENT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
+        assertTrue(rs.next());
+        assertEquals(2, rs.getLong(1));
+        assertEquals(2, rs.getLong(2));
+        assertFalse(rs.next());
+        rs = conn.createStatement().executeQuery("SELECT CURRENT VALUE FOR alpha.gamma, NEXT VALUE FOR alpha.gamma FROM SYSTEM.\"SEQUENCE\"");
+        assertTrue(rs.next());
+        assertEquals(5, rs.getLong(1));
+        assertEquals(5, rs.getLong(2));
+        assertFalse(rs.next());
+	}
+
+    @Test
+    public void testSameMultipleSequenceValues() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7");
+        nextConnection();
+        String query = "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.zeta FROM SYSTEM.\"SEQUENCE\"";
+        ResultSet rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertEquals(4, rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+    }
+
+    @Test
+	public void testMultipleSequenceValues() throws Exception {
+        nextConnection();
+		conn.createStatement().execute("CREATE SEQUENCE alpha.zeta START WITH 4 INCREMENT BY 7");
+		conn.createStatement().execute("CREATE SEQUENCE alpha.kappa START WITH 9 INCREMENT BY 2");
+        nextConnection();
+		String query = "SELECT NEXT VALUE FOR alpha.zeta, NEXT VALUE FOR alpha.kappa FROM SYSTEM.\"SEQUENCE\"";
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(4, rs.getInt(1));
+		assertEquals(9, rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(4+7, rs.getInt(1));
+        assertEquals(9+2, rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+        // Test that sequences don't have gaps (if no other client request the same sequence before we close it)
+        nextConnection();
+        rs = conn.prepareStatement(query).executeQuery();
+        assertTrue(rs.next());
+        assertEquals(4+7*2, rs.getInt(1));
+        assertEquals(9+2*2, rs.getInt(2));
+        assertTrue(rs.next());
+        assertEquals(4+7*3, rs.getInt(1));
+        assertEquals(9+2*3, rs.getInt(2));
+        assertFalse(rs.next());
+        conn.close();
+	}
+	
+	@Test
+	public void testCompilerOptimization() throws Exception {
+		nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE seq.perf START WITH 3 INCREMENT BY 2");        
+		conn.createStatement().execute("CREATE TABLE t (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true");
+        nextConnection();
+        conn.createStatement().execute("CREATE INDEX idx ON t(v1) INCLUDE (v2)");
+        nextConnection();
+        PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
+        stmt.optimizeQuery("SELECT k, NEXT VALUE FOR seq.perf FROM t WHERE v1 = 'bar'");
+	}
+	
+	@Test
+	public void testSelectRowAndSequence() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE alpha.epsilon START WITH 1 INCREMENT BY 4");
+		conn.createStatement().execute("CREATE TABLE test.foo ( id INTEGER NOT NULL PRIMARY KEY)");
+        nextConnection();
+		conn.createStatement().execute("UPSERT INTO test.foo (id) VALUES (NEXT VALUE FOR alpha.epsilon)");
+		conn.commit();
+        nextConnection();
+		String query = "SELECT NEXT VALUE FOR alpha.epsilon, id FROM test.foo";
+		ResultSet rs = conn.prepareStatement(query).executeQuery();
+		assertTrue(rs.next());
+		assertEquals(5, rs.getInt(1));
+		assertEquals(1, rs.getInt(2));
+        assertFalse(rs.next());
+	}
+
+    @Test
+    public void testSelectNextValueForOverMultipleBatches() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+        
+        nextConnection();
+        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE  * 2 + 1; i++) {
+            stmt.execute();
+        }
+        conn.commit();
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT count(*),max(k) FROM foo");
+        assertTrue(rs.next());
+        assertEquals(BATCH_SIZE * 2 + 1, rs.getInt(1));
+        assertEquals(BATCH_SIZE * 2 + 1, rs.getInt(2));
+    }
+
+    @Test
+    public void testSelectNextValueForGroupBy() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY, v VARCHAR)");
+        conn.createStatement().execute("CREATE TABLE bar (k BIGINT NOT NULL PRIMARY KEY, v VARCHAR)");
+        
+        nextConnection();
+        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar, ?)");
+        stmt.setString(1, "a");
+        stmt.execute();
+        stmt.setString(1, "a");
+        stmt.execute();
+        stmt.setString(1, "b");
+        stmt.execute();
+        stmt.setString(1, "b");
+        stmt.execute();
+        stmt.setString(1, "c");
+        stmt.execute();
+        conn.commit();
+        
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT k from foo");
+        assertTrue(rs.next());
+        assertEquals(1, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(2, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(3, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(4, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(5, rs.getInt(1));
+        assertFalse(rs.next());
+
+        nextConnection();
+        conn.setAutoCommit(true);;
+        conn.createStatement().execute("UPSERT INTO bar SELECT NEXT VALUE FOR foo.bar,v FROM foo GROUP BY v");
+        nextConnection();
+        rs = conn.createStatement().executeQuery("SELECT * from bar");
+        assertTrue(rs.next());
+        assertEquals(6, rs.getInt(1));
+        assertEquals("a", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals(7, rs.getInt(1));
+        assertEquals("b", rs.getString(2));
+        assertTrue(rs.next());
+        assertEquals(8, rs.getInt(1));
+        assertEquals("c", rs.getString(2));
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testSelectNextValueForMultipleConn() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+
+        nextConnection();
+        Connection conn1 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt1 = conn1.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE+ 1; i++) {
+            stmt1.execute();
+        }
+        conn1.commit();
+        
+        nextConnection();
+        Connection conn2 = conn;
+        PreparedStatement stmt2 = conn2.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        stmt2.execute();
+        stmt1.close(); // Should still continue with next value, even on separate connection
+        for (int i = 0; i < BATCH_SIZE; i++) {
+            stmt2.execute();
+        }
+        conn2.commit();
+        conn2.close();
+        conn1.close();
+        
+        nextConnection();
+        // No gaps exist even when sequences were generated from different connections
+        ResultSet rs = conn.createStatement().executeQuery("SELECT k FROM foo");
+        for (int i = 0; i < (BATCH_SIZE+ 1)*2; i++) {
+            assertTrue(rs.next());
+            assertEquals(i+1, rs.getInt(1));
+        }
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testSelectNextValueForMultipleConnWithStmtClose() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+        
+        nextConnection();
+        Connection conn1 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt1 = conn1.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE+ 1; i++) {
+            stmt1.execute();
+        }
+        conn1.commit();
+        stmt1.close();
+        
+        nextConnection();
+        Connection conn2 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt2 = conn2.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE + 1; i++) {
+            stmt2.execute();
+        }
+        conn2.commit();
+        conn2.close();
+        conn1.close();
+        
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT k FROM foo");
+        for (int i = 0; i < 2*(BATCH_SIZE + 1); i++) {
+            assertTrue(rs.next());
+            assertEquals(i+1, rs.getInt(1));
+        }
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testSelectNextValueForMultipleConnWithConnClose() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+        
+        nextConnection();
+        Connection conn1 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt1 = conn1.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE+ 1; i++) {
+            stmt1.execute();
+        }
+        conn1.commit();
+        conn1.close(); // will return unused sequences, so no gaps now
+        
+        nextConnection();
+        Connection conn2 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt2 = conn2.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)");
+        for (int i = 0; i < BATCH_SIZE + 1; i++) {
+            stmt2.execute();
+        }
+        conn2.commit();
+        conn1.close();
+        
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT k FROM foo");
+        for (int i = 0; i < 2*(BATCH_SIZE + 1); i++) {
+            assertTrue(rs.next());
+            assertEquals(i+1, rs.getInt(1));
+        }
+        assertFalse(rs.next());
+    }
+
+    @Test
+    public void testDropCachedSeq1() throws Exception {
+        testDropCachedSeq(false);
+    }
+    
+    @Test
+    public void testDropCachedSeq2() throws Exception {
+        testDropCachedSeq(true);
+    }
+    
+    private void testDropCachedSeq(boolean detectDeleteSeqInEval) throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE foo.bar");
+        conn.createStatement().execute("CREATE SEQUENCE bar.bas START WITH 101");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+        
+        nextConnection();
+        Connection conn1 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        String stmtStr1a = "UPSERT INTO foo VALUES(NEXT VALUE FOR foo.bar)";
+        PreparedStatement stmt1a = conn1.prepareStatement(stmtStr1a);
+        stmt1a.execute();
+        stmt1a.execute();
+        String stmtStr1b = "UPSERT INTO foo VALUES(NEXT VALUE FOR bar.bas)";
+        PreparedStatement stmt1b = conn1.prepareStatement(stmtStr1b);
+        stmt1b.execute();
+        stmt1b.execute();
+        stmt1b.execute();
+        conn1.commit();
+        
+        nextConnection();
+        Connection conn2 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        
+        PreparedStatement stmt2 = conn2.prepareStatement("UPSERT INTO foo VALUES(NEXT VALUE FOR bar.bas)");
+        stmt2.execute();
+        conn2.commit();
+        
+        nextConnection();
+        ResultSet rs = conn.createStatement().executeQuery("SELECT k FROM foo");
+        assertTrue(rs.next());
+        assertEquals(1, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(2, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(101, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(102, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(103, rs.getInt(1));
+        assertTrue(rs.next());
+        assertEquals(104, rs.getInt(1));
+        assertFalse(rs.next());
+        
+        nextConnection();
+        conn.createStatement().execute("DROP SEQUENCE bar.bas");
+
+        nextConnection();
+        stmt1a = conn.prepareStatement(stmtStr1a);
+        stmt1a.execute();
+        if (!detectDeleteSeqInEval) {
+            stmt1a.execute(); // Will allocate new batch for foo.bar and get error for bar.bas, but ignore it
+        }
+        
+        stmt1b = conn.prepareStatement(stmtStr1b);
+        try {
+            stmt1b.execute(); // Will try to get new batch, but fail b/c sequence has been dropped
+            fail();
+        } catch (SequenceNotFoundException e) {
+        }
+        conn1.close();
+        conn2.close();
+    }
+
+    @Test
+    public void testExplainPlanValidatesSequences() throws Exception {
+        nextConnection();
+        conn.createStatement().execute("CREATE SEQUENCE bar");
+        conn.createStatement().execute("CREATE TABLE foo (k BIGINT NOT NULL PRIMARY KEY)");
+        
+        nextConnection();
+        Connection conn2 = conn;
+        conn = null; // So that call to nextConnection doesn't close it
+        ResultSet rs = conn2.createStatement().executeQuery("EXPLAIN SELECT NEXT VALUE FOR bar FROM foo");
+        assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER FOO\n" + 
+        		"CLIENT RESERVE VALUES FROM 1 SEQUENCE", QueryUtil.getExplainPlan(rs));
+        
+        nextConnection();
+        rs = conn.createStatement().executeQuery("SELECT sequence_name, current_value FROM SYSTEM.\"SEQUENCE\" WHERE sequence_name='BAR'");
+        assertTrue(rs.next());
+        assertEquals("BAR", rs.getString(1));
+        assertEquals(1, rs.getLong(2));
+        conn.close();
+        conn2.close();
+
+        nextConnection();
+        try {
+            conn.createStatement().executeQuery("EXPLAIN SELECT NEXT VALUE FOR zzz FROM foo");
+            fail();
+        } catch (SequenceNotFoundException e) {
+            // expected
+        }
+        conn.close();
+    }
+    
+	private void nextConnection() throws Exception {
+	    if (conn != null) conn.close();
+	    long ts = nextTimestamp();
+		Properties props = new Properties(TestUtil.TEST_PROPERTIES);
+		props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
+		conn = DriverManager.getConnection(getUrl(), props);
+	}	
+}
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/ServerExceptionIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/ServerExceptionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ServerExceptionIT.java
new file mode 100644
index 0000000..ab9bb28
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/ServerExceptionIT.java
@@ -0,0 +1,67 @@
+/*
+ * 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.TEST_PROPERTIES;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.junit.Test;
+
+
+public class ServerExceptionIT extends BaseHBaseManagedTimeIT {
+
+    @Test
+    public void testServerExceptionBackToClient() throws Exception {
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        conn.setAutoCommit(false);
+        try {
+            String ddl = "CREATE TABLE IF NOT EXISTS t1(pk VARCHAR NOT NULL PRIMARY KEY, " +
+                    "col1 INTEGER, col2 INTEGER)";
+            createTestTable(getUrl(), ddl);
+            
+            String query = "UPSERT INTO t1 VALUES(?,?,?)";
+            PreparedStatement stmt = conn.prepareStatement(query);
+            stmt.setString(1, "1");
+            stmt.setInt(2, 1);
+            stmt.setInt(3, 0);
+            stmt.execute();
+            conn.commit();
+            
+            query = "SELECT * FROM t1 where col1/col2 > 0";
+            stmt = conn.prepareStatement(query);
+            ResultSet rs = stmt.executeQuery();
+            rs.next();
+            rs.getInt(1);
+            fail("Should have caught exception.");
+        } catch (SQLException e) {
+            assertTrue(e.getMessage().contains("ERROR 212 (22012): Arithmetic error on server. / by zero"));
+        } finally {
+            conn.close();
+        }
+    }
+
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipRangeParallelIteratorRegionSplitterIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipRangeParallelIteratorRegionSplitterIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipRangeParallelIteratorRegionSplitterIT.java
new file mode 100644
index 0000000..e3869d9
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipRangeParallelIteratorRegionSplitterIT.java
@@ -0,0 +1,368 @@
+/*
+ * 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.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+
+import org.apache.hadoop.hbase.HRegionLocation;
+import org.apache.hadoop.hbase.client.Scan;
+import org.apache.hadoop.hbase.util.Bytes;
+import org.apache.phoenix.compile.ColumnResolver;
+import org.apache.phoenix.compile.ScanRanges;
+import org.apache.phoenix.compile.StatementContext;
+import org.apache.phoenix.filter.SkipScanFilter;
+import org.apache.phoenix.iterate.SkipRangeParallelIteratorRegionSplitter;
+import org.apache.phoenix.jdbc.PhoenixConnection;
+import org.apache.phoenix.jdbc.PhoenixStatement;
+import org.apache.phoenix.parse.HintNode;
+import org.apache.phoenix.query.KeyRange;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.ColumnRef;
+import org.apache.phoenix.schema.PDataType;
+import org.apache.phoenix.schema.PDatum;
+import org.apache.phoenix.schema.PTableKey;
+import org.apache.phoenix.schema.RowKeySchema;
+import org.apache.phoenix.schema.RowKeySchema.RowKeySchemaBuilder;
+import org.apache.phoenix.schema.SortOrder;
+import org.apache.phoenix.schema.TableRef;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.base.Function;
+import com.google.common.collect.Lists;
+import com.google.common.collect.Maps;
+
+
+/**
+ * Tests for {@link SkipRangeParallelIteratorRegionSplitter}.
+ */
+@RunWith(Parameterized.class)
+public class SkipRangeParallelIteratorRegionSplitterIT extends BaseClientManagedTimeIT {
+
+    private static final String TABLE_NAME = "TEST_SKIP_RANGE_PARALLEL_ITERATOR";
+    private static final String DDL = "CREATE TABLE " + TABLE_NAME + " (id char(3) NOT NULL PRIMARY KEY, \"value\" integer)";
+    private static final byte[] Ka1A = Bytes.toBytes("a1A");
+    private static final byte[] Ka1B = Bytes.toBytes("a1B");
+    private static final byte[] Ka1C = Bytes.toBytes("a1C");
+    private static final byte[] Ka1D = Bytes.toBytes("a1D");
+    private static final byte[] Ka1E = Bytes.toBytes("a1E");
+    private static final byte[] Ka1F = Bytes.toBytes("a1F");
+    private static final byte[] Ka1G = Bytes.toBytes("a1G");
+    private static final byte[] Ka1H = Bytes.toBytes("a1H");
+    private static final byte[] Ka1I = Bytes.toBytes("a1I");
+    private static final byte[] Ka2A = Bytes.toBytes("a2A");
+
+    private final Scan scan;
+    private final ScanRanges scanRanges;
+    private final List<KeyRange> expectedSplits;
+
+    public SkipRangeParallelIteratorRegionSplitterIT(Scan scan, ScanRanges scanRanges, List<KeyRange> expectedSplits) {
+        this.scan = scan;
+        this.scanRanges = scanRanges;
+        this.expectedSplits = expectedSplits;
+    }
+
+    @Test
+    public void testGetSplitsWithSkipScanFilter() throws Exception {
+        byte[][] splits = new byte[][] {Ka1A, Ka1B, Ka1E, Ka1G, Ka1I, Ka2A};
+        long ts = nextTimestamp();
+        createTestTable(getUrl(),DDL,splits, ts-2);
+        String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + ts;
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(url, props);
+        PhoenixConnection pconn = conn.unwrap(PhoenixConnection.class);
+        TableRef tableRef = new TableRef(null,pconn.getMetaDataCache().getTable(new PTableKey(pconn.getTenantId(), TABLE_NAME)),ts, false);
+        List<HRegionLocation> regions = pconn.getQueryServices().getAllTableRegions(tableRef.getTable().getPhysicalName().getBytes());
+        
+        conn.close();
+        initTableValues();
+        List<KeyRange> ranges = getSplits(tableRef, scan, regions, scanRanges);
+        assertEquals("Unexpected number of splits: " + ranges.size(), expectedSplits.size(), ranges.size());
+        for (int i=0; i<expectedSplits.size(); i++) {
+            assertEquals(expectedSplits.get(i), ranges.get(i));
+        }
+    }
+
+    private static KeyRange getKeyRange(byte[] lowerRange, boolean lowerInclusive, byte[] upperRange, boolean upperInclusive) {
+        return PDataType.CHAR.getKeyRange(lowerRange, lowerInclusive, upperRange, upperInclusive);
+    }
+
+    @Parameters(name="{1} {2}")
+    public static Collection<Object> data() {
+        List<Object> testCases = Lists.newArrayList();
+        // Scan range is empty.
+        testCases.addAll(
+                foreach(ScanRanges.NOTHING,
+                        new int[] {1,1,1},
+                        new KeyRange[] { }));
+        // Scan range is everything.
+        testCases.addAll(
+                foreach(ScanRanges.EVERYTHING,
+                        new int[] {1,1,1},
+                        new KeyRange[] {
+                            getKeyRange(KeyRange.UNBOUND, true, Ka1A, false),
+                            getKeyRange(Ka1A, true, Ka1B, false),
+                            getKeyRange(Ka1B, true, Ka1E, false),
+                            getKeyRange(Ka1E, true, Ka1G, false),
+                            getKeyRange(Ka1G, true, Ka1I, false),
+                            getKeyRange(Ka1I, true, Ka2A, false),
+                            getKeyRange(Ka2A, true, KeyRange.UNBOUND, false)
+                }));
+        // Scan range lies inside first region.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("0"), true, Bytes.toBytes("0"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("A"), true, Bytes.toBytes("Z"), true)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(KeyRange.UNBOUND, true, Ka1A, false)
+                }));
+        // Scan range lies in between first and second, intersecting bound on second.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("0"), true, Bytes.toBytes("0"), true),
+                            getKeyRange(Bytes.toBytes("1"), true, Bytes.toBytes("1"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("A"), true, Bytes.toBytes("A"), true)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(KeyRange.UNBOUND, true, Ka1A, false),
+                        getKeyRange(Ka1A, true, Ka1B, false),
+                }));
+        // Scan range spans third, split into 3 due to concurrency config.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("1"), true, Bytes.toBytes("1"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("B"), true, Bytes.toBytes("E"), false)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(Ka1B, true, Ka1C, false),
+                        getKeyRange(Ka1C, true, Ka1D, false),
+                        getKeyRange(Ka1D, true, Ka1E, false),
+                }));
+        // Scan range spans third, split into 3 due to concurrency config.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("1"), true, Bytes.toBytes("1"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("B"), true, Bytes.toBytes("E"), false)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(Ka1B, true, Ka1C, false),
+                        getKeyRange(Ka1C, true, Ka1D, false),
+                        getKeyRange(Ka1D, true, Ka1E, false),
+                }));
+        // Scan range spans 2 ranges, split into 4 due to concurrency config.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("1"), true, Bytes.toBytes("1"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("F"), true, Bytes.toBytes("H"), false)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(Ka1E, true, Ka1F, false),
+                        getKeyRange(Ka1F, true, Ka1G, false),
+                        getKeyRange(Ka1G, true, Ka1H, false),
+                        getKeyRange(Ka1H, true, Ka1I, false),
+                }));
+        // Scan range spans more than 3 range, no split.
+        testCases.addAll(
+                foreach(new KeyRange[][]{
+                        {
+                            getKeyRange(Bytes.toBytes("a"), true, Bytes.toBytes("a"), true),
+                            getKeyRange(Bytes.toBytes("b"), true, Bytes.toBytes("b"), true)
+                        },{
+                            getKeyRange(Bytes.toBytes("1"), true, Bytes.toBytes("1"), true),
+                            getKeyRange(Bytes.toBytes("2"), true, Bytes.toBytes("2"), true),
+                        },{
+                            getKeyRange(Bytes.toBytes("A"), true, Bytes.toBytes("A"), true),
+                            getKeyRange(Bytes.toBytes("C"), true, Bytes.toBytes("D"), true),
+                            getKeyRange(Bytes.toBytes("G"), true, Bytes.toBytes("G"), true)
+                        }},
+                    new int[] {1,1,1},
+                    new KeyRange[] {
+                        getKeyRange(Ka1A, true, Ka1B, false),
+                        getKeyRange(Ka1B, true, Ka1E, false),
+                        getKeyRange(Ka1G, true, Ka1I, false),
+                        getKeyRange(Ka2A, true, KeyRange.UNBOUND, false)
+                }));
+        return testCases;
+    }
+
+    private static RowKeySchema buildSchema(int[] widths) {
+        RowKeySchemaBuilder builder = new RowKeySchemaBuilder(10);
+        for (final int width : widths) {
+            builder.addField(new PDatum() {
+                @Override
+                public boolean isNullable() {
+                    return false;
+                }
+                @Override
+                public PDataType getDataType() {
+                    return PDataType.CHAR;
+                }
+                @Override
+                public Integer getMaxLength() {
+                    return width;
+                }
+                @Override
+                public Integer getScale() {
+                    return null;
+                }
+                @Override
+                public SortOrder getSortOrder() {
+                    return SortOrder.getDefault();
+                }
+            }, false, SortOrder.getDefault());
+        }
+        return builder.build();
+    }
+    
+    private static Collection<?> foreach(ScanRanges scanRanges, int[] widths, KeyRange[] expectedSplits) {
+         SkipScanFilter filter = new SkipScanFilter(scanRanges.getRanges(), buildSchema(widths));
+        Scan scan = new Scan().setFilter(filter).setStartRow(KeyRange.UNBOUND).setStopRow(KeyRange.UNBOUND);
+        List<Object> ret = Lists.newArrayList();
+        ret.add(new Object[] {scan, scanRanges, Arrays.<KeyRange>asList(expectedSplits)});
+        return ret;
+    }
+
+    private static Collection<?> foreach(KeyRange[][] ranges, int[] widths, KeyRange[] expectedSplits) {
+        RowKeySchema schema = buildSchema(widths);
+        List<List<KeyRange>> slots = Lists.transform(Lists.newArrayList(ranges), ARRAY_TO_LIST);
+        SkipScanFilter filter = new SkipScanFilter(slots, schema);
+        // Always set start and stop key to max to verify we are using the information in skipscan
+        // filter over the scan's KMIN and KMAX.
+        Scan scan = new Scan().setFilter(filter).setStartRow(KeyRange.UNBOUND).setStopRow(KeyRange.UNBOUND);
+        ScanRanges scanRanges = ScanRanges.create(slots, schema);
+        List<Object> ret = Lists.newArrayList();
+        ret.add(new Object[] {scan, scanRanges, Arrays.<KeyRange>asList(expectedSplits)});
+        return ret;
+    }
+
+    private static final Function<KeyRange[], List<KeyRange>> ARRAY_TO_LIST = 
+            new Function<KeyRange[], List<KeyRange>>() {
+                @Override 
+                public List<KeyRange> apply(KeyRange[] input) {
+                    return Lists.newArrayList(input);
+                }
+    };
+
+    private void initTableValues() throws SQLException {
+        String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + nextTimestamp();
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(url, props);
+        PreparedStatement stmt = conn.prepareStatement(
+                "upsert into " + TABLE_NAME + " VALUES (?, ?)");
+        stmt.setString(1, new String("a1A"));
+        stmt.setInt(2, 1);
+        stmt.execute();
+        stmt.setString(1, new String("a1E"));
+        stmt.setInt(2, 2);
+        stmt.execute();
+        conn.commit();
+        conn.close();
+     }
+    
+    @BeforeClass
+    public static void doSetup() throws Exception {
+        int targetQueryConcurrency = 3;
+        int maxQueryConcurrency = 5;
+        Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+        props.put(QueryServices.MAX_QUERY_CONCURRENCY_ATTRIB, Integer.toString(maxQueryConcurrency));
+        props.put(QueryServices.TARGET_QUERY_CONCURRENCY_ATTRIB, Integer.toString(targetQueryConcurrency));
+        props.put(QueryServices.MAX_INTRA_REGION_PARALLELIZATION_ATTRIB, Integer.toString(Integer.MAX_VALUE));
+        // Must update config before starting server
+        startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));
+    }
+
+    private static List<KeyRange> getSplits(TableRef tableRef, final Scan scan, final List<HRegionLocation> regions,
+            final ScanRanges scanRanges) throws SQLException {
+        final List<TableRef> tableRefs = Collections.singletonList(tableRef);
+        ColumnResolver resolver = new ColumnResolver() {
+
+            @Override
+            public List<TableRef> getTables() {
+                return tableRefs;
+            }
+
+            @Override
+            public TableRef resolveTable(String schemaName, String tableName)
+                    throws SQLException {
+                throw new UnsupportedOperationException();
+            }
+
+            @Override
+            public ColumnRef resolveColumn(String schemaName, String tableName, String colName) throws SQLException {
+                throw new UnsupportedOperationException();
+            }
+            
+        };
+        PhoenixConnection connection = DriverManager.getConnection(getUrl(), TEST_PROPERTIES).unwrap(PhoenixConnection.class);
+        StatementContext context = new StatementContext(new PhoenixStatement(connection), resolver, scan);
+        context.setScanRanges(scanRanges);
+        SkipRangeParallelIteratorRegionSplitter splitter = SkipRangeParallelIteratorRegionSplitter.getInstance(context, tableRef, HintNode.EMPTY_HINT_NODE);
+        List<KeyRange> keyRanges = splitter.getSplits();
+        Collections.sort(keyRanges, new Comparator<KeyRange>() {
+            @Override
+            public int compare(KeyRange o1, KeyRange o2) {
+                return Bytes.compareTo(o1.getLowerRange(),o2.getLowerRange());
+            }
+        });
+        return keyRanges;
+    }
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
new file mode 100644
index 0000000..8d9f81d
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SkipScanQueryIT.java
@@ -0,0 +1,275 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.io.StringReader;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Arrays;
+import java.util.Collections;
+import java.util.List;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.junit.Test;
+
+public class SkipScanQueryIT extends BaseHBaseManagedTimeIT {
+    
+    private void initIntInTable(Connection conn, List<Integer> data) throws SQLException {
+        String ddl = "CREATE TABLE IF NOT EXISTS inTest (" + 
+                     "  i INTEGER NOT NULL PRIMARY KEY)";
+        conn.createStatement().executeUpdate(ddl);
+        
+        // Test upsert correct values 
+        String query = "UPSERT INTO inTest VALUES(?)";
+        PreparedStatement stmt = conn.prepareStatement(query);
+        for (Integer i : data) {
+            stmt.setInt(1, i);
+            stmt.execute();
+        }
+        conn.commit();
+    }
+    
+    private void initVarCharCrossProductInTable(Connection conn, List<String> c1, List<String> c2) throws SQLException {
+        String ddl = "CREATE TABLE IF NOT EXISTS inVarTest (" + 
+                     "  s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (s1,s2))";
+        conn.createStatement().executeUpdate(ddl);
+        
+        // Test upsert correct values 
+        String query = "UPSERT INTO inVarTest VALUES(?,?)";
+        PreparedStatement stmt = conn.prepareStatement(query);
+        for (String s1 : c1) {
+            for (String s2 : c2) {
+                stmt.setString(1, s1);
+                stmt.setString(2, s2);
+                stmt.execute();
+            }
+        }
+        conn.commit();
+    }
+    
+    private void initVarCharParallelListInTable(Connection conn, List<String> c1, List<String> c2) throws SQLException {
+        String ddl = "CREATE TABLE IF NOT EXISTS inVarTest (" + 
+                     "  s1 VARCHAR, s2 VARCHAR CONSTRAINT pk PRIMARY KEY (s1,s2))";
+        conn.createStatement().executeUpdate(ddl);
+        
+        // Test upsert correct values 
+        String query = "UPSERT INTO inVarTest VALUES(?,?)";
+        PreparedStatement stmt = conn.prepareStatement(query);
+        for (int i = 0; i < c1.size(); i++) {
+            stmt.setString(1, c1.get(i));
+            stmt.setString(2, i < c2.size() ? c2.get(i) : null);
+            stmt.execute();
+        }
+        conn.commit();
+    }
+    
+    private static final String UPSERT_SELECT_AFTER_UPSERT_STATEMENTS = 
+    		"upsert into table1(c1, c2, c3, c4, v1, v2) values('1001', '91', 's1', '2013-09-26', 28397, 23541);\n" + 
+    		"upsert into table1(c1, c2, c3, c4, v1, v2) values('1001', '91', 's2', '2013-09-23', 3369, null);\n";
+    private void initSelectAfterUpsertTable(Connection conn) throws Exception {
+        String ddl = "create table if not exists table1("
+                + "c1 VARCHAR NOT NULL," + "c2 VARCHAR NOT NULL,"
+                + "c3 VARCHAR NOT NULL," + "c4 VARCHAR NOT NULL,"
+                + "v1 integer," + "v2 integer "
+                + "CONSTRAINT PK PRIMARY KEY (c1, c2, c3, c4)" + ")";
+        conn.createStatement().execute(ddl);
+
+        // Test upsert correct values
+        StringReader reader = new StringReader(UPSERT_SELECT_AFTER_UPSERT_STATEMENTS);
+        PhoenixRuntime.executeStatements(conn, reader, Collections.emptyList());
+        reader.close();
+        conn.commit();
+    }
+    
+    @Test
+    public void testSelectAfterUpsertInQuery() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        initSelectAfterUpsertTable(conn);
+        try {
+            String query;
+            query = "SELECT case when sum(v2)*1.0/sum(v1) is null then 0 else sum(v2)*1.0/sum(v1) END AS val FROM table1 " +
+            		"WHERE c1='1001' AND c2 = '91' " +
+            		"AND c3 IN ('s1','s2') AND c4='2013-09-24'";
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(0, rs.getInt(1));
+        } finally {
+            conn.close();
+        }
+    }
+    @Test
+    public void testInQuery() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.setAutoCommit(false);
+        initIntInTable(conn,Arrays.asList(2,7,10));
+        try {
+            String query;
+            query = "SELECT i FROM inTest WHERE i IN (1,2,4,5,7,8,10)";
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals(2, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(7, rs.getInt(1));
+            assertTrue(rs.next());
+            assertEquals(10, rs.getInt(1));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testVarCharParallelListInQuery() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.setAutoCommit(false);
+        initVarCharParallelListInTable(conn,Arrays.asList("d","da","db"),Arrays.asList("m","mc","tt"));
+        try {
+            String query;
+            query = "SELECT s1,s2 FROM inVarTest WHERE s1 IN ('a','b','da','db') AND s2 IN ('c','ma','m','mc','ttt','z')";
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("da", rs.getString(1));
+            assertEquals("mc", rs.getString(2));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+    
+    @Test
+    public void testVarCharXInQuery() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.setAutoCommit(false);
+        initVarCharCrossProductInTable(conn,Arrays.asList("d","da","db"),Arrays.asList("m","mc","tt"));
+        try {
+            String query;
+            query = "SELECT s1,s2 FROM inVarTest WHERE s1 IN ('a','b','da','db') AND s2 IN ('c','ma','m','mc','ttt','z')";
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("da", rs.getString(1));
+            assertEquals("m", rs.getString(2));
+            assertTrue(rs.next());
+            assertEquals("da", rs.getString(1));
+            assertEquals("mc", rs.getString(2));
+            assertTrue(rs.next());
+            assertEquals("db", rs.getString(1));
+            assertEquals("m", rs.getString(2));
+            assertTrue(rs.next());
+            assertEquals("db", rs.getString(1));
+            assertEquals("mc", rs.getString(2));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    @Test
+    public void testVarCharXIntInQuery() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        conn.setAutoCommit(false);
+        initVarCharCrossProductInTable(conn,Arrays.asList("d","da","db"),Arrays.asList("m","mc","tt"));
+        try {
+            String query;
+            query = "SELECT s1,s2 FROM inVarTest " +
+                    "WHERE s1 IN ('a','b','da','db') AND s2 IN ('c','ma','m','mc','ttt','z') " +
+                    "AND s1 > 'd' AND s1 < 'db' AND s2 > 'm'";
+            ResultSet rs = conn.createStatement().executeQuery(query);
+            assertTrue(rs.next());
+            assertEquals("da", rs.getString(1));
+            assertEquals("mc", rs.getString(2));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+    
+    @Test
+    public void testPreSplitCompositeFixedKey() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        try {
+            conn.createStatement().execute("create table test(key_1 char(3) not null, key_2 char(4) not null, v varchar(8)  CONSTRAINT pk PRIMARY KEY (key_1,key_2)) split on('000','100','200')");
+            conn.setAutoCommit(true);
+            conn.createStatement().execute("upsert into test values('000','aaaa','value_1')");
+            conn.createStatement().execute("upsert into test values('000','aabb','value_2')");
+            conn.createStatement().execute("upsert into test values('100','aacc','value_3')");
+            conn.createStatement().execute("upsert into test values('100','aadd','value_4')");
+            conn.createStatement().execute("upsert into test values('200','aaee','value_5')");
+            conn.createStatement().execute("upsert into test values('201','aaff','value_6')");
+            ResultSet rs = conn.createStatement().executeQuery("select * from test where key_1>='000' and key_1<'200' and key_2>='aabb' and key_2<'aadd'");
+            assertTrue(rs.next());
+            assertEquals("000", rs.getString(1));
+            assertEquals("aabb", rs.getString(2));
+            assertEquals("value_2", rs.getString(3));
+            assertTrue(rs.next());
+            assertEquals("100", rs.getString(1));
+            assertEquals("aacc", rs.getString(2));
+            assertEquals("value_3", rs.getString(3));
+            assertFalse(rs.next());
+        } finally {
+            conn.close();
+        }
+    }
+
+    
+    @Test
+    public void testInWithDescKey() throws Exception {
+        Connection conn = DriverManager.getConnection(getUrl());
+        try {
+            conn.createStatement().execute("create table test(key_1 char(3) not null, key_2 char(4) not null, v varchar(8)  CONSTRAINT pk PRIMARY KEY (key_1,key_2 desc))");
+            conn.setAutoCommit(true);
+            conn.createStatement().execute("upsert into test values('000','aaaa','value_1')");
+            conn.createStatement().execute("upsert into test values('000','aabb','value_2')");
+            conn.createStatement().execute("upsert into test values('100','aacc','value_3')");
+            conn.createStatement().execute("upsert into test values('100','aadd','value_4')");
+            conn.createStatement().execute("upsert into test values('200','aaee','value_5')");
+            conn.createStatement().execute("upsert into test values('201','aaff','value_6')");
+            ResultSet rs = conn.createStatement().executeQuery("select * from test where key_1>='000' and key_1<'200' and key_2>='aabb' and key_2<'aadd'");
+            assertTrue(rs.next());
+            assertEquals("000", rs.getString(1));
+            assertEquals("aabb", rs.getString(2));
+            assertEquals("value_2", rs.getString(3));
+            assertTrue(rs.next());
+            assertEquals("100", rs.getString(1));
+            assertEquals("aacc", rs.getString(2));
+            assertEquals("value_3", rs.getString(3));
+            assertFalse(rs.next());
+
+            rs = conn.createStatement().executeQuery("select * from test where (key_1,key_2) in (('100','aacc'),('100','aadd'))");
+            assertTrue(rs.next());
+            assertEquals("100", rs.getString(1));
+            assertEquals("aadd", rs.getString(2));
+            assertEquals("value_4", rs.getString(3));
+            assertTrue(rs.next());
+            assertEquals("100", rs.getString(1));
+            assertEquals("aacc", rs.getString(2));
+            assertEquals("value_3", rs.getString(3));
+            assertFalse(rs.next());
+            
+        } finally {
+            conn.close();
+        }
+    }
+    
+}

http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/df23cf97/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderFIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderFIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderFIT.java
new file mode 100644
index 0000000..679e0bf
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortOrderFIT.java
@@ -0,0 +1,552 @@
+/*
+ * 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.TEST_PROPERTIES;
+
+import java.math.BigDecimal;
+import java.math.BigInteger;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Calendar;
+import java.util.GregorianCalendar;
+import java.util.List;
+import java.util.Properties;
+
+import org.apache.commons.lang.ArrayUtils;
+import org.junit.Assert;
+import org.junit.Test;
+
+import com.google.common.collect.Lists;
+
+/**
+ * @since 1.2
+ */
+public class SortOrderFIT extends BaseHBaseManagedTimeIT {
+    
+    private static final String TABLE = "DescColumnSortOrderTest";
+
+    @Test
+    public void noOrder() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY)";
+        runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}});
+    }                                                           
+
+    @Test
+    public void noOrderCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid, code))";
+        Object[][] rows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), rows, rows);
+    }
+    
+    @Test
+    public void ascOrderInlinePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY ASC)";
+        runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}});
+    }
+    
+    @Test
+    public void ascOrderCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid ASC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
+        Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);        
+    }
+
+    @Test
+    public void descOrderInlinePK() throws Exception {
+        for (String type : new String[]{"CHAR(2)", "VARCHAR"}) {
+            String ddl = "CREATE TABLE " + TABLE + " (pk ${type} NOT NULL PRIMARY KEY DESC)".replace("${type}", type);
+            runQueryTest(ddl, "pk", new Object[][]{{"aa"}, {"bb"}, {"cc"}}, new Object[][]{{"cc"}, {"bb"}, {"aa"}});
+        }
+    }
+    
+    @Test
+    public void descOrderCompositePK1() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
+        Object[][] expectedRows = new Object[][]{{"o3", 3}, {"o2", 2}, {"o1", 1}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);        
+    }
+    
+    @Test
+    public void descOrderCompositePK2() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
+        Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);        
+    }    
+
+    @Test
+    public void equalityDescInlinePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY DESC)";
+        runQueryTest(ddl, upsert("pk"), new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"b"}}, new WhereCondition("pk", "=", "'b'"));
+    }
+    
+    @Test
+    public void equalityDescCompositePK1() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "=", "'o2'"));        
+    }
+    
+    @Test
+    public void equalityDescCompositePK2() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "=", "2"));        
+    }
+    
+    @Test
+    public void inDescCompositePK1() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "IN", "(2)"));        
+    }
+    
+    @Test
+    public void inDescCompositePK2() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "IN", "('o2')"));        
+    }
+    
+    @Test
+    public void likeDescCompositePK1() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"a1", 1}, {"b2", 2}, {"c3", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", 2}}, new WhereCondition("oid", "LIKE", "('b%')"));        
+    }
+    
+    @Test
+    public void likeDescCompositePK2() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code CHAR(2) NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"a1", "11"}, {"b2", "22"}, {"c3", "33"}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", "22"}}, new WhereCondition("code", "LIKE", "('2%')"));        
+    }
+    
+    @Test
+    public void greaterThanDescCompositePK3() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
+        Object[][] expectedRows = new Object[][]{{"o1", 2}, {"o1", 1}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("code", "<", "3"));        
+    }
+    
+    @Test
+    public void substrDescCompositePK1() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(3) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
+        Object[][] insertedRows = new Object[][]{{"ao1", 1}, {"bo2", 2}, {"co3", 3}};
+        Object[][] expectedRows = new Object[][]{{"co3", 3}, {"bo2", 2}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 3, 1)", ">", "'1'"));
+    }
+        
+    @Test
+    public void substrDescCompositePK2() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(4) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
+        Object[][] insertedRows = new Object[][]{{"aaaa", 1}, {"bbbb", 2}, {"cccd", 3}};
+        Object[][] expectedRows = new Object[][]{{"cccd", 3}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 4, 1)", "=", "'d'"));
+    }    
+    
+    @Test
+    public void lTrimDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{" o1 ", 1}, {"  o2", 2}, {"  o3", 3}};
+        Object[][] expectedRows = new Object[][]{{"  o2", 2}};
+        runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LTRIM(oid)", "=", "'o2'"));
+    }
+
+    @Test
+    public void countDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
+        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
+        Object[][] expectedRows = new Object[][]{{3l}};
+        runQueryTest(ddl, upsert("oid", "code"), select("COUNT(oid)"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void sumDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
+        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
+        Object[][] expectedRows = new Object[][]{{60l, bdec(60.6), 96l}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("SUM(n1), SUM(n2), SUM(n3)"), insertedRows, expectedRows);
+    }    
+    
+    @Test
+    public void avgDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
+        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
+        Object[][] expectedRows = new Object[][]{{new BigDecimal(bint(2), -1), bdec(20.2), BigDecimal.valueOf(32)}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("AVG(n1), AVG(n2), AVG(n3)"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void minDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
+        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
+        Object[][] expectedRows = new Object[][]{{10, bdec(10.2), 21l}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MIN(n1), MIN(n2), MIN(n3)"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void maxDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
+        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
+        Object[][] expectedRows = new Object[][]{{30, bdec(30.2), 43l}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MAX(n1), MAX(n2), MAX(n3)"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void havingSumDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (name CHAR(1) NOT NULL, code INTEGER NOT NULL " + 
+            "constraint pk primary key (name DESC, code DESC))";
+        Object[][] insertedRows = new Object[][]{{"a", 10}, {"a", 20}, {"b", 100}}; 
+        Object[][] expectedRows = new Object[][]{{"a", 30l}};
+        runQueryTest(ddl, upsert("name", "code"), select("name", "SUM(code)"), insertedRows, expectedRows, 
+            new HavingCondition("name", "SUM(code) = 30"));
+    }
+    
+    @Test
+    public void queryDescDateWithExplicitOrderBy() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (c1 CHAR(1) NOT NULL, c2 CHAR(1) NOT NULL, d1 DATE NOT NULL, c3 CHAR(1) NOT NULL " + 
+            "constraint pk primary key (c1, c2, d1 DESC, c3))";
+        Object[] row1 = {"1", "2", date(10, 11, 2001), "3"};
+        Object[] row2 = {"1", "2", date(10, 11, 2003), "3"};
+        Object[][] insertedRows = new Object[][]{row1, row2};
+        runQueryTest(ddl, upsert("c1", "c2", "d1", "c3"), select("c1, c2, d1", "c3"), insertedRows, new Object[][]{row2, row1},
+            null, null, new OrderBy("d1", OrderBy.Direction.DESC));
+    }    
+    
+    @Test
+    public void additionOnDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";
+        Object[][] insertedRows = new Object[][]{
+            {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 1, 2001)}};
+        Object[][] expectedRows = new Object[][]{
+            {31l, bdec(32.2), 46l, date(3, 5, 2001)}, {21l, bdec(22.2), 35l, date(2, 10, 2001)}, {11l, bdec(12.2), 24l, date(1, 14, 2001)}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1+1, n2+2, n3+3", "d1+4"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void subtractionOnDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + 
+            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";
+        Object[][] insertedRows = new Object[][]{
+            {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 10, 2001)}};
+        Object[][] expectedRows = new Object[][]{
+            {29l, bdec(28.2), 40l, date(3, 6, 2001)}, {19l, bdec(18.2), 29l, date(2, 2, 2001)}, {9l, bdec(8.2), 18l, date(1, 6, 2001)}};
+        runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1-1, n2-2, n3-3", "d1-4"), insertedRows, expectedRows);
+    }
+    
+    @Test
+    public void lessThanLeadingDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))";
+        Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2012)}, {3, date(1, 1, 2013)}, {2, date(1, 1, 2011)}};
+        Object[][] expectedRows = new Object[][]{{1, date(1, 1, 2012)}};
+        runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("id", "<", "2"));
+    }
+    
+    @Test
+    public void lessThanTrailingDescCompositePK() throws Exception {
+        String ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))";
+        Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2002)}, {3, date(1, 1, 2003)}, {2, date(1, 1, 2001)}};
+        Object[][] expectedRows = new Object[][]{{2, date(1, 1, 2001)}};
+        runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("date", "<", "TO_DATE('02-02-2001','mm-dd-yyyy')"));
+    }
+    
+    private void runQueryTest(String ddl, String columnName, Object[][] rows, Object[][] expectedRows) throws Exception {
+        runQueryTest(ddl, new String[]{columnName}, rows, expectedRows, null);
+    }
+    
+    private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows) throws Exception {
+        runQueryTest(ddl, columnNames, rows, expectedRows, null);
+    }
+    
+    private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows, WhereCondition condition) throws Exception {
+        runQueryTest(ddl, columnNames, columnNames, rows, expectedRows, condition, null, null);
+    }
+    
+    private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows) throws Exception {
+        runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, null, null);
+    }
+    
+    private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, HavingCondition havingCondition) throws Exception {
+        runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, havingCondition, null);
+    }
+    
+
+    private void runQueryTest(
+        String ddl, 
+        String[] columnNames, 
+        String[] projections, 
+        Object[][] rows, Object[][] expectedRows, 
+        WhereCondition whereCondition, 
+        HavingCondition havingCondition,
+        OrderBy orderBy) 
+        throws Exception 
+    {
+        Properties props = new Properties(TEST_PROPERTIES);
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+
+        try {
+
+            conn.setAutoCommit(false);
+
+            createTestTable(getUrl(), ddl);
+
+            String columns = appendColumns(columnNames);
+            String placeholders = appendPlaceholders(columnNames);
+            String dml = "UPSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders +")";
+            PreparedStatement stmt = conn.prepareStatement(dml);
+
+            for (int row = 0; row < rows.length; row++) {
+                for (int col = 0; col < rows[row].length; col++) {
+                    Object value = rows[row][col];
+                    stmt.setObject(col + 1, value);
+                }
+                stmt.execute();
+            }
+            conn.commit();
+            
+            String selectClause = "SELECT " + appendColumns(projections) + " FROM " + TABLE;
+            
+            for (WhereCondition whereConditionClause : new WhereCondition[]{whereCondition, WhereCondition.reverse(whereCondition)}) {
+                String query = WhereCondition.appendWhere(whereConditionClause, selectClause);
+                query = HavingCondition.appendHaving(havingCondition, query);
+                query = OrderBy.appendOrderBy(orderBy, query);
+                runQuery(conn, query, expectedRows);
+            }
+            
+            if (orderBy != null) {
+                orderBy = OrderBy.reverse(orderBy);
+                String query = WhereCondition.appendWhere(whereCondition, selectClause);
+                query = HavingCondition.appendHaving(havingCondition, query);
+                query = OrderBy.appendOrderBy(orderBy, query);
+                runQuery(conn, query, reverse(expectedRows));
+            }
+            
+        } finally {
+            conn.close();
+        }
+    }
+    
+    private String appendColumns(String[] columnNames) {
+        String appendedColumns = "";
+        for (int i = 0; i < columnNames.length; i++) {                
+            appendedColumns += columnNames[i];
+            if (i < columnNames.length - 1) {
+                appendedColumns += ",";
+            }
+        }
+        return appendedColumns;
+    }
+    
+    private String appendPlaceholders(String[] columnNames) {
+        String placeholderList = "";
+        for (int i = 0; i < columnNames.length; i++) {                
+            placeholderList += "?";
+            if (i < columnNames.length - 1) {
+                placeholderList += ",";
+            }
+        }
+        return placeholderList;        
+    }
+    
+    private static void runQuery(Connection connection, String query, Object[][] expectedValues) throws Exception {
+        PreparedStatement stmt = connection.prepareStatement(query);
+
+        ResultSet rs = stmt.executeQuery();
+        int rowCounter = 0;
+        while (rs.next()) {
+            if (rowCounter == expectedValues.length) {
+                Assert.assertEquals("Exceeded number of expected rows for query" + query, expectedValues.length, rowCounter+1);
+            }
+            Object[] cols = new Object[expectedValues[rowCounter].length];
+            for (int colCounter = 0; colCounter < expectedValues[rowCounter].length; colCounter++) {
+                cols[colCounter] = rs.getObject(colCounter+1);
+            }
+            Assert.assertArrayEquals("Unexpected result for query " + query, expectedValues[rowCounter], cols);
+            rowCounter++;
+        }
+        Assert.assertEquals("Unexpected number of rows for query " + query, expectedValues.length, rowCounter);
+    }
+    
+    private static Object[][] reverse(Object[][] rows) {
+        Object[][] reversedArray = new Object[rows.length][];
+        System.arraycopy(rows, 0, reversedArray, 0, rows.length);
+        ArrayUtils.reverse(reversedArray);
+        return reversedArray;
+    }
+    
+    private static Date date(int month, int day, int year) {
+        Calendar cal = new GregorianCalendar();
+        cal.set(Calendar.MONTH, month-1);
+        cal.set(Calendar.DAY_OF_MONTH, day);
+        cal.set(Calendar.YEAR, year);
+        cal.set(Calendar.HOUR_OF_DAY, 10);
+        cal.set(Calendar.MINUTE, 2);
+        cal.set(Calendar.SECOND, 5);
+        cal.set(Calendar.MILLISECOND, 101);
+        Date d = new Date(cal.getTimeInMillis()); 
+        return d;
+    }
+        
+    private static String[] upsert(String...args) {
+        return args;
+    }
+    
+    private static String[] select(String...args) {
+        return args;
+    }
+    
+    private static BigDecimal bdec(double d) {
+        return BigDecimal.valueOf(d);
+    }
+    
+    private static BigInteger bint(long l) {
+        return BigInteger.valueOf(l);
+    }    
+    
+    private static class WhereCondition {
+        final String lhs;
+        final String operator;
+        final String rhs;
+    
+        WhereCondition(String lhs, String operator, String rhs) {
+            this.lhs = lhs;
+            this.operator = operator;
+            this.rhs = rhs;
+        }
+        
+        static WhereCondition reverse(WhereCondition whereCondition) {
+            
+            if (whereCondition == null) {
+                return null; 
+            }
+            
+            if (whereCondition.operator.equalsIgnoreCase("IN") || whereCondition.operator.equalsIgnoreCase("LIKE")) {
+                return whereCondition;
+            } else {
+                return new WhereCondition(whereCondition.rhs, whereCondition.getReversedOperator(), whereCondition.lhs);
+            }
+        }
+        
+         static String appendWhere(WhereCondition whereCondition, String query) {
+             if (whereCondition == null) {
+                 return query;
+             }
+            return query + " WHERE " + whereCondition.lhs + " " + whereCondition.operator + " " + whereCondition.rhs;
+        }
+        
+        private String getReversedOperator() {
+            if (operator.equals("<")) {
+                return ">";
+            } else if (operator.equals(">")) {
+                return "<";
+            }
+            return operator;
+        }
+    }
+    
+    private static class HavingCondition {
+        
+        private String groupby;
+        private String having;
+        
+        HavingCondition(String groupby, String having) {
+            this.groupby = groupby;
+            this.having = having;
+        }
+        
+        static String appendHaving(HavingCondition havingCondition, String query) {
+            if (havingCondition == null) {
+                return query;
+            }
+            return query + " GROUP BY " + havingCondition.groupby + " HAVING " + havingCondition.having + " ";
+        }
+    }
+    
+    private static class OrderBy {
+        
+        enum Direction {
+            
+            ASC, DESC;
+            
+            Direction reverse() {
+                if (this == ASC) {
+                    return DESC;
+                }
+                return ASC;
+            }
+        }
+        
+        private List<String> columnNames = Lists.newArrayList();
+        private List<Direction> directions = Lists.newArrayList();
+        
+        OrderBy() {            
+        }
+        
+        OrderBy(String columnName, Direction orderBy) {
+            add(columnName, orderBy);
+        }
+        
+        void add(String columnName, Direction direction) {
+            columnNames.add(columnName);
+            directions.add(direction);
+        }
+        
+        static OrderBy reverse(OrderBy orderBy) {
+            
+            if (orderBy == null) {
+                return null;
+            }
+            
+            List<Direction> reversedDirections = Lists.newArrayList();
+            for (Direction dir : orderBy.directions) {
+                reversedDirections.add(dir.reverse());
+            }
+            OrderBy reversedOrderBy = new OrderBy();
+            reversedOrderBy.columnNames = orderBy.columnNames;
+            reversedOrderBy.directions = reversedDirections;
+            return reversedOrderBy;
+        }
+        
+        static String appendOrderBy(OrderBy orderBy, String query) {
+            if (orderBy == null || orderBy.columnNames.isEmpty()) {
+                return query;
+            }
+            query += " ORDER BY ";
+            for (int i = 0; i < orderBy.columnNames.size(); i++) {
+                query += orderBy.columnNames.get(i) + " " + orderBy.directions.get(i).toString() + " ";
+            }
+            
+            query += " LIMIT 1000 ";
+            
+            return query;
+        }        
+    }
+}


Mime
View raw message