You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2014/03/12 22:31:56 UTC
[29/41] PHOENIX-130 Separate execution of slow (integration) tests
from fast unit tests (Gabriel Reid)
http://git-wip-us.apache.org/repos/asf/incubator-phoenix/blob/8d6e2a58/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/8d6e2a58/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/8d6e2a58/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/8d6e2a58/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/8d6e2a58/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;
+ }
+ }
+}