You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by co...@apache.org on 2015/05/27 22:54:27 UTC
[1/4] phoenix git commit: PHOENIX-1964 - porting from master
Repository: phoenix
Updated Branches:
refs/heads/4.4-HBase-1.0 41037dd4b -> e409c0e7a
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~HEAD
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~HEAD
new file mode 100644
index 0000000..9fc3003
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~HEAD
@@ -0,0 +1,515 @@
+/*
+ * 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.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.apache.phoenix.util.TestUtil.ROW4;
+import static org.apache.phoenix.util.TestUtil.ROW5;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.junit.Test;
+
+
+public class OrderByIT extends BaseClientManagedTimeIT {
+
+ @Test
+ public void testMultiOrderByExpr() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT entity_id FROM aTable ORDER BY b_string, entity_id";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(ROW1,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW4,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW7,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW2,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW5,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW8,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW3,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW6,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW9,rs.getString(1));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+
+ @Test
+ public void testDescMultiOrderByExpr() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT entity_id FROM aTable ORDER BY b_string || entity_id desc";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(ROW9,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW6,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW3,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW8,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW5,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW2,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW7,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW4,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW1,rs.getString(1));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testOrderByWithPosition() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+
+ try {
+ String ddl = "CREATE TABLE t_table " +
+ " (a_string varchar not null, col1 integer" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+
+ String dml = "UPSERT INTO t_table VALUES(?, ?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 20);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.execute();
+ conn.commit();
+
+ String query = "select count(*), col1 from t_table group by col1 order by 2";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals(1,rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(1,rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(1,rs.getInt(1));
+ assertFalse(rs.next());
+
+ query = "select a_string x, col1 y from t_table order by x";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(20,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertFalse(rs.next());
+
+ query = "select * from t_table order by 2";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(20,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+
+ @Test
+ public void testColumnFamily() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+
+ try {
+ String ddl = "CREATE TABLE x_table " +
+ " (a_string varchar not null, cf1.a integer, cf1.b varchar, col1 integer, cf2.c varchar, cf2.d integer, col2 integer" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+ String dml = "UPSERT INTO x_table VALUES(?,?,?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "aa");
+ stmt.setInt(4, 10);
+ stmt.setString(5, "bb");
+ stmt.setInt(6, 20);
+ stmt.setInt(7, 1);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.setString(3, "cc");
+ stmt.setInt(4, 50);
+ stmt.setString(5, "dd");
+ stmt.setInt(6, 60);
+ stmt.setInt(7, 3);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "bb");
+ stmt.setInt(4, 5);
+ stmt.setString(5, "aa");
+ stmt.setInt(6, 80);
+ stmt.setInt(7, 2);
+ stmt.execute();
+ conn.commit();
+
+ String query = "select * from x_table order by 2, 5";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertEquals("cc",rs.getString(3));
+ assertEquals(50,rs.getInt(4));
+ assertEquals("dd",rs.getString(5));
+ assertEquals(60,rs.getInt(6));
+ assertEquals(3,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("bb",rs.getString(3));
+ assertEquals(5,rs.getInt(4));
+ assertEquals("aa",rs.getString(5));
+ assertEquals(80,rs.getInt(6));
+ assertEquals(2,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("aa",rs.getString(3));
+ assertEquals(10,rs.getInt(4));
+ assertEquals("bb",rs.getString(5));
+ assertEquals(20,rs.getInt(6));
+ assertEquals(1,rs.getInt(7));
+ assertFalse(rs.next());
+
+ query = "select * from x_table order by 7";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("aa",rs.getString(3));
+ assertEquals(10,rs.getInt(4));
+ assertEquals("bb",rs.getString(5));
+ assertEquals(20,rs.getInt(6));
+ assertEquals(1,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("bb",rs.getString(3));
+ assertEquals(5,rs.getInt(4));
+ assertEquals("aa",rs.getString(5));
+ assertEquals(80,rs.getInt(6));
+ assertEquals(2,rs.getInt(7));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertEquals("cc",rs.getString(3));
+ assertEquals(50,rs.getInt(4));
+ assertEquals("dd",rs.getString(5));
+ assertEquals(60,rs.getInt(6));
+ assertEquals(3,rs.getInt(7));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testOrderByWithJoin() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+
+ try {
+ String ddl = "CREATE TABLE s_table " +
+ " (a_string varchar not null, cf1.a integer, cf1.b varchar, col1 integer, cf2.c varchar, cf2.d integer " +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+ String dml = "UPSERT INTO s_table VALUES(?,?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "aa");
+ stmt.setInt(4, 10);
+ stmt.setString(5, "bb");
+ stmt.setInt(6, 20);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.setString(3, "cc");
+ stmt.setInt(4, 50);
+ stmt.setString(5, "dd");
+ stmt.setInt(6, 60);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "bb");
+ stmt.setInt(4, 5);
+ stmt.setString(5, "aa");
+ stmt.setInt(6, 80);
+ stmt.execute();
+ conn.commit();
+
+ ddl = "CREATE TABLE t_table " +
+ " (a_string varchar not null, col1 integer" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+
+ dml = "UPSERT INTO t_table VALUES(?, ?)";
+ stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 20);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.execute();
+ conn.commit();
+
+ String query = "select t1.* from s_table t1 join t_table t2 on t1.a_string = t2.a_string order by 3";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("aa",rs.getString(3));
+ assertEquals(10,rs.getInt(4));
+ assertEquals("bb",rs.getString(5));
+ assertEquals(20,rs.getInt(6));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertEquals("bb",rs.getString(3));
+ assertEquals(5,rs.getInt(4));
+ assertEquals("aa",rs.getString(5));
+ assertEquals(80,rs.getInt(6));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertEquals("cc",rs.getString(3));
+ assertEquals(50,rs.getInt(4));
+ assertEquals("dd",rs.getString(5));
+ assertEquals(60,rs.getInt(6));
+ assertFalse(rs.next());
+
+ query = "select t1.a_string, t2.col1 from s_table t1 join t_table t2 on t1.a_string = t2.a_string order by 2";
+ rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(20,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertFalse(rs.next());
+ } catch (SQLException e) {
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testOrderByWithUnionAll() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+
+ try {
+ String ddl = "CREATE TABLE x_table " +
+ " (a_string varchar not null, cf1.a integer, cf1.b varchar, col1 integer, cf2.c varchar, cf2.d integer " +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+ String dml = "UPSERT INTO x_table VALUES(?,?,?,?,?,?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "aa");
+ stmt.setInt(4, 10);
+ stmt.setString(5, "bb");
+ stmt.setInt(6, 20);
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 30);
+ stmt.setString(3, "cc");
+ stmt.setInt(4, 50);
+ stmt.setString(5, "dd");
+ stmt.setInt(6, 60);
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 40);
+ stmt.setString(3, "bb");
+ stmt.setInt(4, 5);
+ stmt.setString(5, "aa");
+ stmt.setInt(6, 80);
+ stmt.execute();
+ conn.commit();
+
+ ddl = "CREATE TABLE y_table " +
+ " (a_string varchar not null, col1 integer" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+
+ dml = "UPSERT INTO y_table VALUES(?, ?)";
+ stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "aa");
+ stmt.setInt(2, 40);
+ stmt.execute();
+ stmt.setString(1, "bb");
+ stmt.setInt(2, 10);
+ stmt.execute();
+ stmt.setString(1, "cc");
+ stmt.setInt(2, 30);
+ stmt.execute();
+ conn.commit();
+
+ String query = "select a_string, cf2.d from x_table union all select * from y_table order by 2";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("bb",rs.getString(1));
+ assertEquals(10,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals(20,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("cc",rs.getString(1));
+ assertEquals(30,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("aa",rs.getString(1));
+ assertEquals(40,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("c",rs.getString(1));
+ assertEquals(60,rs.getInt(2));
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals(80,rs.getInt(2));
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testOrderByWithExpression() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ conn.setAutoCommit(false);
+
+ try {
+ String ddl = "CREATE TABLE e_table " +
+ " (a_string varchar not null, col1 integer, col2 integer, col3 timestamp, col4 varchar" +
+ " CONSTRAINT pk PRIMARY KEY (a_string))\n";
+ createTestTable(getUrl(), ddl);
+
+ Date date = new Date(System.currentTimeMillis());
+ String dml = "UPSERT INTO e_table VALUES(?, ?, ?, ?, ?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+ stmt.setString(1, "a");
+ stmt.setInt(2, 40);
+ stmt.setInt(3, 20);
+ stmt.setDate(4, new Date(date.getTime()));
+ stmt.setString(5, "xxyy");
+ stmt.execute();
+ stmt.setString(1, "b");
+ stmt.setInt(2, 50);
+ stmt.setInt(3, 30);
+ stmt.setDate(4, new Date(date.getTime()-500));
+ stmt.setString(5, "yyzz");
+ stmt.execute();
+ stmt.setString(1, "c");
+ stmt.setInt(2, 60);
+ stmt.setInt(3, 20);
+ stmt.setDate(4, new Date(date.getTime()-300));
+ stmt.setString(5, "ddee");
+ stmt.execute();
+ conn.commit();
+
+ String query = "SELECT col1+col2, col4, a_string FROM e_table ORDER BY 1, 2";
+ ResultSet rs = conn.createStatement().executeQuery(query);
+ assertTrue(rs.next());
+ assertEquals("a", rs.getString(3));
+ assertTrue(rs.next());
+ assertEquals("c", rs.getString(3));
+ assertTrue(rs.next());
+ assertEquals("b", rs.getString(3));
+ assertFalse(rs.next());
+ } catch (SQLException e) {
+ } finally {
+ conn.close();
+ }
+ }
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid) b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
new file mode 100644
index 0000000..45be69d
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/OrderByIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
@@ -0,0 +1,153 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.phoenix.end2end;
+
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.ROW1;
+import static org.apache.phoenix.util.TestUtil.ROW2;
+import static org.apache.phoenix.util.TestUtil.ROW3;
+import static org.apache.phoenix.util.TestUtil.ROW4;
+import static org.apache.phoenix.util.TestUtil.ROW5;
+import static org.apache.phoenix.util.TestUtil.ROW6;
+import static org.apache.phoenix.util.TestUtil.ROW7;
+import static org.apache.phoenix.util.TestUtil.ROW8;
+import static org.apache.phoenix.util.TestUtil.ROW9;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.Properties;
+
+import org.junit.Ignore;
+import org.junit.Test;
+
+import org.apache.phoenix.util.PhoenixRuntime;
+
+public class OrderByIT extends BaseClientManagedTimeIT {
+
+ @Test
+ public void testMultiOrderByExpr() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT entity_id FROM aTable ORDER BY b_string, entity_id";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(ROW1,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW4,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW7,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW2,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW5,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW8,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW3,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW6,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW9,rs.getString(1));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+
+ @Test
+ public void testDescMultiOrderByExpr() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT entity_id FROM aTable ORDER BY b_string || entity_id desc";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(ROW9,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW6,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW3,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW8,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW5,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW2,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW7,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW4,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW1,rs.getString(1));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+
+ @Ignore("When reverse scan is in HBase re-enable this test")
+ @Test
+ public void testReverseScan() throws Exception {
+ long ts = nextTimestamp();
+ String tenantId = getOrganizationId();
+ initATableValues(tenantId, getDefaultSplits(tenantId), null, ts);
+ String query = "SELECT entity_id FROM aTable WHERE entity_id >= ? ORDER BY organization_id DESC, entity_id DESC";
+ Properties props = new Properties(TEST_PROPERTIES);
+ props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ statement.setString(1, ROW7);
+ ResultSet rs = statement.executeQuery();
+
+ assertTrue (rs.next());
+ assertEquals(ROW9,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW8,rs.getString(1));
+ assertTrue (rs.next());
+ assertEquals(ROW7,rs.getString(1));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+
+}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~HEAD
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~HEAD
new file mode 100644
index 0000000..e02a473
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~HEAD
@@ -0,0 +1,197 @@
+/*
+ * 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.util.Properties;
+
+import org.apache.phoenix.schema.ColumnNotFoundException;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.QueryUtil;
+import org.junit.Test;
+
+
+public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
+
+ @Test
+ public void testUpdatableView() throws Exception {
+ testUpdatableView(null);
+ }
+
+ @Test
+ public void testUpdatableViewLocalIndex() throws Exception {
+ testUpdatableView(null, true);
+ }
+
+ @Test
+ public void testUpdatableViewsWithSameNameDifferentTenants() throws Exception {
+ testUpdatableViewsWithSameNameDifferentTenants(null);
+ }
+
+ @Test
+ public void testUpdatableViewsWithSameNameDifferentTenantsWithLocalIndex() throws Exception {
+ testUpdatableViewsWithSameNameDifferentTenants(null, true);
+ }
+
+ @Test
+ public void testMultiCFViewIndex() throws Exception {
+ testMultiCFViewIndex(false);
+ }
+
+ @Test
+ public void testMultiCFViewLocalIndex() throws Exception {
+ testMultiCFViewIndex(true);
+ }
+
+ private void testMultiCFViewIndex(boolean localIndex) throws Exception {
+ Connection conn = DriverManager.getConnection(getUrl());
+ String ddl = "CREATE TABLE MT_BASE (PK1 VARCHAR not null, PK2 VARCHAR not null, "
+ + "MYCF1.COL1 varchar,MYCF2.COL2 varchar "
+ + "CONSTRAINT pk PRIMARY KEY(PK1,PK2)) MULTI_TENANT=true";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute("UPSERT INTO MT_BASE values ('a','b','c','d')");
+ conn.commit();
+
+ ResultSet rs = conn.createStatement().executeQuery("select * from mt_base where (pk1,pk2) IN (('a','b'),('b','b'))");
+ assertTrue(rs.next());
+ assertEquals("a",rs.getString(1));
+ assertEquals("b",rs.getString(2));
+ assertFalse(rs.next());
+
+ conn.close();
+ String tenantId = "a";
+ Properties props = new Properties();
+ props.setProperty(PhoenixRuntime.TENANT_ID_ATTRIB, tenantId);
+ conn = DriverManager.getConnection(getUrl(),props);
+ conn.createStatement().execute("CREATE VIEW acme AS SELECT * FROM MT_BASE");
+ rs = conn.createStatement().executeQuery("select * from acme");
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals("c",rs.getString(2));
+ assertEquals("d",rs.getString(3));
+ assertFalse(rs.next());
+ conn.createStatement().execute("UPSERT INTO acme VALUES ('e','f','g')");
+ conn.commit();
+ if(localIndex){
+ conn.createStatement().execute("create local index idx_acme on acme (COL1)");
+ } else {
+ conn.createStatement().execute("create index idx_acme on acme (COL1)");
+ }
+ rs = conn.createStatement().executeQuery("select * from acme");
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertEquals("c",rs.getString(2));
+ assertEquals("d",rs.getString(3));
+ assertTrue(rs.next());
+ assertEquals("e",rs.getString(1));
+ assertEquals("f",rs.getString(2));
+ assertEquals("g",rs.getString(3));
+ assertFalse(rs.next());
+ rs = conn.createStatement().executeQuery("explain select * from acme");
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER MT_BASE ['a']",QueryUtil.getExplainPlan(rs));
+
+ rs = conn.createStatement().executeQuery("select pk2,col1 from acme where col1='f'");
+ assertTrue(rs.next());
+ assertEquals("e",rs.getString(1));
+ assertEquals("f",rs.getString(2));
+ assertFalse(rs.next());
+ rs = conn.createStatement().executeQuery("explain select pk2,col1 from acme where col1='f'");
+ if(localIndex){
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _LOCAL_IDX_MT_BASE ['a',-32768,'f']\n"
+ + " SERVER FILTER BY FIRST KEY ONLY\n"
+ + "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs));
+ } else {
+ assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER _IDX_MT_BASE ['a',-32768,'f']\n"
+ + " SERVER FILTER BY FIRST KEY ONLY",QueryUtil.getExplainPlan(rs));
+ }
+
+ try {
+ // Cannot reference tenant_id column in tenant specific connection
+ conn.createStatement().executeQuery("select * from mt_base where (pk1,pk2) IN (('a','b'),('b','b'))");
+ fail();
+ } catch (ColumnNotFoundException e) {
+ }
+
+ // This is ok, though
+ rs = conn.createStatement().executeQuery("select * from mt_base where pk2 IN ('b','e')");
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("e",rs.getString(1));
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("select * from acme where pk2 IN ('b','e')");
+ assertTrue(rs.next());
+ assertEquals("b",rs.getString(1));
+ assertTrue(rs.next());
+ assertEquals("e",rs.getString(1));
+ assertFalse(rs.next());
+
+ }
+
+ @Test
+ public void testNonPaddedTenantId() throws Exception {
+ String tenantId1 = "org1";
+ String tenantId2 = "org2";
+ String ddl = "CREATE TABLE T (tenantId char(15) NOT NULL, pk1 varchar NOT NULL, pk2 INTEGER NOT NULL, val1 VARCHAR CONSTRAINT pk primary key (tenantId,pk1,pk2)) MULTI_TENANT = true";
+ Connection conn = DriverManager.getConnection(getUrl());
+ conn.createStatement().execute(ddl);
+ String dml = "UPSERT INTO T (tenantId, pk1, pk2, val1) VALUES (?, ?, ?, ?)";
+ PreparedStatement stmt = conn.prepareStatement(dml);
+
+ String pk = "pk1b";
+ // insert two rows in table T. One for tenantId1 and other for tenantId2.
+ stmt.setString(1, tenantId1);
+ stmt.setString(2, pk);
+ stmt.setInt(3, 100);
+ stmt.setString(4, "value1");
+ stmt.executeUpdate();
+
+ stmt.setString(1, tenantId2);
+ stmt.setString(2, pk);
+ stmt.setInt(3, 200);
+ stmt.setString(4, "value2");
+ stmt.executeUpdate();
+ conn.commit();
+ conn.close();
+
+ // get a tenant specific url.
+ String tenantUrl = getUrl() + ';' + PhoenixRuntime.TENANT_ID_ATTRIB + '=' + tenantId1;
+ Connection tenantConn = DriverManager.getConnection(tenantUrl);
+
+ // create a tenant specific view.
+ tenantConn.createStatement().execute("CREATE VIEW V AS select * from T");
+ String query = "SELECT val1 FROM V WHERE pk1 = ?";
+
+ // using the tenant connection query the view.
+ PreparedStatement stmt2 = tenantConn.prepareStatement(query);
+ stmt2.setString(1, pk); // for tenantId1 the row inserted has pk1 = "pk1b"
+ ResultSet rs = stmt2.executeQuery();
+ assertTrue(rs.next());
+ assertEquals("value1", rs.getString(1));
+ assertFalse("No other rows should have been returned for the tenant", rs.next()); // should have just returned one record since for org1 we have only one row.
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid) b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
new file mode 100644
index 0000000..2f4a1d1
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/TenantSpecificViewIndexIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
@@ -0,0 +1,33 @@
+/*
+ * 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 org.junit.Test;
+
+public class TenantSpecificViewIndexIT extends BaseTenantSpecificViewIndexIT {
+
+ @Test
+ public void testUpdatableView() throws Exception {
+ testUpdatableView(null);
+ }
+
+ @Test
+ public void testUpdatableViewsWithSameNameDifferentTenants() throws Exception {
+ testUpdatableViewsWithSameNameDifferentTenants(null);
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/config/pherf.properties
----------------------------------------------------------------------
diff --git a/phoenix-pherf/config/pherf.properties b/phoenix-pherf/config/pherf.properties
index 354707a..1142f9b5 100644
--- a/phoenix-pherf/config/pherf.properties
+++ b/phoenix-pherf/config/pherf.properties
@@ -29,3 +29,6 @@ pherf.default.dataloader.threadpool=0
# When upserting, this is the max # of rows that will be inserted in a single commit
pherf.default.dataloader.batchsize=1000
+# Directory where results from a scenario run will be written
+pherf.default.results.dir=RESULTS
+
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/DataIngestIT.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/DataIngestIT.java b/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/DataIngestIT.java
index b29656d..2b56f43 100644
--- a/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/DataIngestIT.java
+++ b/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/DataIngestIT.java
@@ -18,7 +18,6 @@
package org.apache.phoenix.pherf;
-import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
import org.apache.phoenix.pherf.configuration.Column;
import org.apache.phoenix.pherf.configuration.DataTypeMapping;
import org.apache.phoenix.pherf.configuration.Scenario;
@@ -39,7 +38,7 @@ import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
-public class DataIngestIT extends BaseHBaseManagedTimeIT {
+public class DataIngestIT extends ResultBaseTestIT {
protected static PhoenixUtil util = new PhoenixUtil(true);
static final String matcherScenario = ".*scenario/.*test.*xml";
static final String matcherSchema = ".*datamodel/.*test.*sql";
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/ResultBaseTestIT.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/ResultBaseTestIT.java b/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/ResultBaseTestIT.java
new file mode 100644
index 0000000..6e103b8
--- /dev/null
+++ b/phoenix-pherf/src/it/java/org/apache/phoenix/pherf/ResultBaseTestIT.java
@@ -0,0 +1,45 @@
+/*
+ * 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.pherf;
+
+import org.apache.phoenix.end2end.BaseHBaseManagedTimeIT;
+import org.apache.phoenix.pherf.result.ResultUtil;
+import org.junit.BeforeClass;
+
+import java.util.Properties;
+
+public class ResultBaseTestIT extends BaseHBaseManagedTimeIT {
+ private static boolean isSetUpDone = false;
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ if (isSetUpDone) {
+ return;
+ }
+
+ ResultUtil util = new ResultUtil();
+ PherfConstants constants = PherfConstants.create();
+ Properties properties = constants.getProperties(PherfConstants.PHERF_PROPERTIES);
+ String dir = properties.getProperty("pherf.default.results.dir");
+ String targetDir = "target/" + dir;
+ properties.setProperty("pherf.default.results.dir", targetDir);
+ util.ensureBaseDirExists(targetDir);
+ isSetUpDone = true;
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/Pherf.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/Pherf.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/Pherf.java
index 0ccc49a..073c661 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/Pherf.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/Pherf.java
@@ -84,7 +84,7 @@ public class Pherf {
System.exit(1);
}
- properties = getProperties();
+ properties = PherfConstants.create().getProperties(PherfConstants.PHERF_PROPERTIES);
dropPherfTablesRegEx = command.getOptionValue("drop", null);
monitor = command.hasOption("m");
String monitorFrequency = (command.hasOption("m") && command.hasOption("monitorFrequency"))
@@ -192,9 +192,4 @@ public class Pherf {
}
}
}
-
- private static Properties getProperties() throws Exception {
- ResourceList list = new ResourceList();
- return list.getProperties();
- }
}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/PherfConstants.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/PherfConstants.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/PherfConstants.java
index 22d18f6..493f5a8 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/PherfConstants.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/PherfConstants.java
@@ -18,7 +18,13 @@
package org.apache.phoenix.pherf;
+import java.io.InputStream;
+import java.util.Properties;
+
public class PherfConstants {
+ private static PherfConstants instance = null;
+ private Properties properties = null;
+
public static final int DEFAULT_THREAD_POOL_SIZE = 10;
public static final int DEFAULT_BATCH_SIZE = 1000;
public static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss.SSS";
@@ -29,7 +35,7 @@ public class PherfConstants {
".*" + PherfConstants.RESOURCE_SCENARIO.substring(1) + ".*";
public static final String SCHEMA_ROOT_PATTERN = ".*";
public static final String PHERF_PROPERTIES = "pherf.properties";
- public static final String RESULT_DIR = "RESULTS";
+// public static final String RESULT_DIR = "RESULTS";
public static final String EXPORT_DIR = "CSV_EXPORT";
public static final String RESULT_PREFIX = "RESULT_";
public static final String PATH_SEPARATOR = "/";
@@ -60,4 +66,46 @@ public class PherfConstants {
PERFORMANCE,
FUNCTIONAL
}
+
+ private PherfConstants() {
+ }
+
+ public static PherfConstants create() {
+ if (instance == null) {
+ instance = new PherfConstants();
+ }
+ return instance;
+ }
+
+ public Properties getProperties(final String fileName) throws Exception {
+ if (properties != null) {
+ return properties;
+ }
+
+ properties = new Properties();
+ InputStream is = null;
+ try {
+ is = getClass().getClassLoader().getResourceAsStream(fileName);
+ properties.load(is);
+ } finally {
+ if (is != null) {
+ is.close();
+ }
+ }
+ return properties;
+ }
+
+ public String getProperty(String property) {
+ return getProperty(PherfConstants.PHERF_PROPERTIES, property);
+ }
+
+ public String getProperty(final String fileName, String property) {
+ String value = null;
+ try {
+ value = getProperties(fileName).getProperty(property);
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ return value;
+ }
}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/loaddata/DataLoader.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/loaddata/DataLoader.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/loaddata/DataLoader.java
index abec1b4..c521822 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/loaddata/DataLoader.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/loaddata/DataLoader.java
@@ -67,7 +67,7 @@ public class DataLoader {
}
public DataLoader(PhoenixUtil phoenixUtil, XMLConfigParser parser) throws Exception{
- this(phoenixUtil, new ResourceList().getProperties(), parser);
+ this(phoenixUtil, PherfConstants.create().getProperties(PherfConstants.PHERF_PROPERTIES), parser);
}
/**
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/ResultUtil.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/ResultUtil.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/ResultUtil.java
index 360cb49..fd960d1 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/ResultUtil.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/ResultUtil.java
@@ -115,7 +115,9 @@ public class ResultUtil {
}
public void ensureBaseResultDirExists() {
- ensureBaseDirExists(PherfConstants.RESULT_DIR);
+ PherfConstants constants = PherfConstants.create();
+ String resultDir = constants.getProperty("pherf.default.results.dir");
+ ensureBaseDirExists(resultDir);
}
/**
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/CSVResultHandler.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/CSVResultHandler.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/CSVResultHandler.java
index ca470de..e7fbb48 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/CSVResultHandler.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/CSVResultHandler.java
@@ -53,8 +53,11 @@ public class CSVResultHandler implements ResultHandler {
public CSVResultHandler(String resultFileName, ResultFileDetails resultFileDetails, boolean generateFullFileName) {
this.util = new ResultUtil();
+ PherfConstants constants = PherfConstants.create();
+ String resultDir = constants.getProperty("pherf.default.results.dir");
+
this.resultFileName = generateFullFileName ?
- PherfConstants.RESULT_DIR + PherfConstants.PATH_SEPARATOR
+ resultDir + PherfConstants.PATH_SEPARATOR
+ PherfConstants.RESULT_PREFIX
+ resultFileName + util.getSuffix()
+ resultFileDetails.getExtension().toString()
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/ImageResultHandler.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/ImageResultHandler.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/ImageResultHandler.java
index 6e66cf6..ad3c8fb 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/ImageResultHandler.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/ImageResultHandler.java
@@ -44,8 +44,11 @@ public class ImageResultHandler implements ResultHandler {
public ImageResultHandler(String resultFileName, ResultFileDetails resultFileDetails, boolean generateFullFileName) {
ResultUtil util = new ResultUtil();
+ PherfConstants constants = PherfConstants.create();
+ String resultDir = constants.getProperty("pherf.default.results.dir");
+
this.resultFileName = generateFullFileName ?
- PherfConstants.RESULT_DIR + PherfConstants.PATH_SEPARATOR
+ resultDir + PherfConstants.PATH_SEPARATOR
+ PherfConstants.RESULT_PREFIX
+ resultFileName + util.getSuffix()
+ resultFileDetails.getExtension().toString()
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/XMLResultHandler.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/XMLResultHandler.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/XMLResultHandler.java
index a1d0930..8a913ed 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/XMLResultHandler.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/result/impl/XMLResultHandler.java
@@ -30,6 +30,7 @@ import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
+import java.util.Properties;
public class XMLResultHandler implements ResultHandler {
private final String resultFileName;
@@ -41,8 +42,11 @@ public class XMLResultHandler implements ResultHandler {
public XMLResultHandler(String resultFileName, ResultFileDetails resultFileDetails, boolean generateFullFileName) {
ResultUtil util = new ResultUtil();
+ PherfConstants constants = PherfConstants.create();
+ String resultDir = constants.getProperty("pherf.default.results.dir");
+
this.resultFileName = generateFullFileName ?
- PherfConstants.RESULT_DIR + PherfConstants.PATH_SEPARATOR
+ resultDir + PherfConstants.PATH_SEPARATOR
+ PherfConstants.RESULT_PREFIX
+ resultFileName + util.getSuffix()
+ resultFileDetails.getExtension().toString()
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/util/ResourceList.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/util/ResourceList.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/util/ResourceList.java
index ac1e74d..5359c35 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/util/ResourceList.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/util/ResourceList.java
@@ -18,14 +18,12 @@
package org.apache.phoenix.pherf.util;
-import org.apache.phoenix.pherf.PherfConstants;
import org.apache.phoenix.pherf.exception.PherfException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.IOException;
-import java.io.InputStream;
import java.net.URI;
import java.net.URL;
import java.nio.file.Path;
@@ -43,17 +41,11 @@ public class ResourceList {
private static final Logger logger = LoggerFactory.getLogger(ResourceList.class);
private final String rootResourceDir;
- public ResourceList() {
- this("/");
- }
-
public ResourceList(String rootResourceDir) {
this.rootResourceDir = rootResourceDir;
}
public Collection<Path> getResourceList(final String pattern) throws Exception {
- Properties properties = getProperties();
-
// Include files from config directory
Collection<Path> paths = getResourcesPaths(Pattern.compile(pattern));
@@ -112,24 +104,6 @@ public class ResourceList {
return paths;
}
- public Properties getProperties() throws Exception {
- return getProperties(PherfConstants.PHERF_PROPERTIES);
- }
-
- public Properties getProperties(final String fileName) throws Exception {
- Properties pherfProps = new Properties();
- InputStream is = null;
- try {
- is = getClass().getClassLoader().getResourceAsStream(fileName);
- pherfProps.load(is);
- } finally {
- if (is != null) {
- is.close();
- }
- }
- return pherfProps;
- }
-
private Collection<String> getResources(
final String element,
final Pattern pattern) {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/workload/WorkloadExecutor.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/workload/WorkloadExecutor.java b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/workload/WorkloadExecutor.java
index 0509bdc..cf2f038 100644
--- a/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/workload/WorkloadExecutor.java
+++ b/phoenix-pherf/src/main/java/org/apache/phoenix/pherf/workload/WorkloadExecutor.java
@@ -45,7 +45,7 @@ public class WorkloadExecutor {
public WorkloadExecutor() throws Exception {
- this(new ResourceList().getProperties());
+ this(PherfConstants.create().getProperties(PherfConstants.PHERF_PROPERTIES));
}
public WorkloadExecutor(Properties properties) throws Exception{
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ConfigurationParserTest.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ConfigurationParserTest.java b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ConfigurationParserTest.java
index fcc353e..f362842 100644
--- a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ConfigurationParserTest.java
+++ b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ConfigurationParserTest.java
@@ -38,7 +38,7 @@ import javax.xml.bind.Marshaller;
import static org.junit.Assert.*;
-public class ConfigurationParserTest {
+public class ConfigurationParserTest extends ResultBaseTest{
private static final Logger logger = LoggerFactory.getLogger(ConfigurationParserTest.class);
@Test
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResourceTest.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResourceTest.java b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResourceTest.java
index 042f9c3..cd567cb 100644
--- a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResourceTest.java
+++ b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResourceTest.java
@@ -48,11 +48,9 @@ public class ResourceTest {
@Test
public void testResourceListPropertyDirectory() throws Exception {
-
- ResourceList list = new ResourceList();
- Properties properties = list.getProperties();
- assertTrue("Property file list was empty", properties.size() > 0);
- assertNotNull(properties.getProperty("pherf.default.dataloader.threadpool"));
+ PherfConstants constants = PherfConstants.create();
+ assertNotNull(constants.getProperty("pherf.default.dataloader.threadpool"));
+ assertNotNull(constants.getProperty("pherf.default.results.dir"));
}
private Collection<Path> assertResources(String pattern, String rootDir, String assertStr) throws Exception {
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultBaseTest.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultBaseTest.java b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultBaseTest.java
new file mode 100644
index 0000000..5c455fc
--- /dev/null
+++ b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultBaseTest.java
@@ -0,0 +1,44 @@
+/*
+ * 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.pherf;
+
+import org.apache.phoenix.pherf.result.ResultUtil;
+import org.junit.BeforeClass;
+
+import java.util.Properties;
+
+public class ResultBaseTest {
+ private static boolean isSetUpDone = false;
+
+ @BeforeClass
+ public static void setUp() throws Exception {
+ if (isSetUpDone) {
+ return;
+ }
+
+ ResultUtil util = new ResultUtil();
+ PherfConstants constants = PherfConstants.create();
+ Properties properties = constants.getProperties(PherfConstants.PHERF_PROPERTIES);
+ String dir = properties.getProperty("pherf.default.results.dir");
+ String targetDir = "target/" + dir;
+ properties.setProperty("pherf.default.results.dir", targetDir);
+ util.ensureBaseDirExists(targetDir);
+ isSetUpDone = true;
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultTest.java
----------------------------------------------------------------------
diff --git a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultTest.java b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultTest.java
index 0f4dfd1..4ab36fb 100644
--- a/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultTest.java
+++ b/phoenix-pherf/src/test/java/org/apache/phoenix/pherf/ResultTest.java
@@ -33,11 +33,12 @@ import org.apache.phoenix.pherf.result.file.ResultFileDetails;
import org.apache.phoenix.pherf.result.impl.CSVResultHandler;
import org.apache.phoenix.pherf.result.impl.XMLResultHandler;
import org.apache.phoenix.pherf.result.*;
+import org.junit.BeforeClass;
import org.junit.Test;
import org.apache.phoenix.pherf.configuration.Query;
-public class ResultTest {
+public class ResultTest extends ResultBaseTest {
@Test
public void testMonitorWriter() throws Exception {
@@ -96,7 +97,7 @@ public class ResultTest {
records = monitor.readResults();
assertNotNull("Could not retrieve records", records);
- assertEquals("Failed to get correct amount of CSV records.", records.size(), monitor.getRowCount());
+ assertTrue("Failed to get correct CSV records.", records.size() > 0);
assertFalse("Monitor was not stopped correctly.", monitor.isRunning());
}
[4/4] phoenix git commit: PHOENIX-1964 - porting from master
Posted by co...@apache.org.
PHOENIX-1964 - porting from master
Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo
Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/e409c0e7
Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/e409c0e7
Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/e409c0e7
Branch: refs/heads/4.4-HBase-1.0
Commit: e409c0e7ae2ae67379e29fced1dee2045be25e15
Parents: 41037dd
Author: cmarcel <cm...@salesforce.com>
Authored: Wed May 27 13:53:51 2015 -0700
Committer: cmarcel <cm...@salesforce.com>
Committed: Wed May 27 13:53:51 2015 -0700
----------------------------------------------------------------------
.../end2end/BaseClientManagedTimeIT.java~HEAD | 79 +
...on) tests from fast unit tests (GabrielReid) | 41 +
.../end2end/BaseHBaseManagedTimeIT.java~HEAD | 66 +
...on) tests from fast unit tests (GabrielReid) | 41 +
.../apache/phoenix/end2end/HashJoinIT.java~HEAD | 3818 ++++++++++++++++++
...on) tests from fast unit tests (GabrielReid) | 2242 ++++++++++
.../apache/phoenix/end2end/OrderByIT.java~HEAD | 515 +++
...on) tests from fast unit tests (GabrielReid) | 153 +
.../end2end/TenantSpecificViewIndexIT.java~HEAD | 197 +
...on) tests from fast unit tests (GabrielReid) | 33 +
phoenix-pherf/config/pherf.properties | 3 +
.../org/apache/phoenix/pherf/DataIngestIT.java | 3 +-
.../apache/phoenix/pherf/ResultBaseTestIT.java | 45 +
.../java/org/apache/phoenix/pherf/Pherf.java | 7 +-
.../apache/phoenix/pherf/PherfConstants.java | 50 +-
.../phoenix/pherf/loaddata/DataLoader.java | 2 +-
.../apache/phoenix/pherf/result/ResultUtil.java | 4 +-
.../pherf/result/impl/CSVResultHandler.java | 5 +-
.../pherf/result/impl/ImageResultHandler.java | 5 +-
.../pherf/result/impl/XMLResultHandler.java | 6 +-
.../apache/phoenix/pherf/util/ResourceList.java | 26 -
.../pherf/workload/WorkloadExecutor.java | 2 +-
.../phoenix/pherf/ConfigurationParserTest.java | 2 +-
.../org/apache/phoenix/pherf/ResourceTest.java | 8 +-
.../apache/phoenix/pherf/ResultBaseTest.java | 44 +
.../org/apache/phoenix/pherf/ResultTest.java | 5 +-
26 files changed, 7353 insertions(+), 49 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~HEAD
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~HEAD
new file mode 100644
index 0000000..1acd5b3
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~HEAD
@@ -0,0 +1,79 @@
+/*
+ * 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 java.util.Map;
+
+import javax.annotation.concurrent.NotThreadSafe;
+
+import org.apache.hadoop.conf.Configuration;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+
+import com.google.common.collect.Maps;
+
+/**
+ * Base class for tests that manage their own time stamps
+ * We need to separate these from tests that rely on hbase to set
+ * timestamps, because we create/destroy the Phoenix tables
+ * between tests and only allow a table time stamp to increase.
+ * Without this separation table deletion/creation would fail.
+ *
+ * All tests extending this class use the mini cluster that is
+ * different from the mini cluster used by test classes extending
+ * {@link BaseHBaseManagedTimeIT}.
+ *
+ * @since 0.1
+ */
+@NotThreadSafe
+@Category(ClientManagedTimeTest.class)
+public abstract class BaseClientManagedTimeIT extends BaseTest {
+ protected static Configuration getTestClusterConfig() {
+ // don't want callers to modify config.
+ return new Configuration(config);
+ }
+
+ @After
+ public void cleanUpAfterTest() throws Exception {
+ long ts = nextTimestamp();
+ deletePriorTables(ts - 1, getUrl());
+ }
+
+ public static Map<String,String> getDefaultProps() {
+ Map<String,String> props = Maps.newHashMapWithExpectedSize(5);
+ // Must update config before starting server
+ props.put(QueryServices.STATS_USE_CURRENT_TIME_ATTRIB, Boolean.FALSE.toString());
+ return props;
+ }
+
+ @BeforeClass
+ public static void doSetup() throws Exception {
+ Map<String,String> props = getDefaultProps();
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @AfterClass
+ public static void doTeardown() throws Exception {
+ dropNonSystemTables();
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid) b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
new file mode 100644
index 0000000..2c5e400
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseClientManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
@@ -0,0 +1,41 @@
+/*
+ * 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 org.junit.Before;
+
+/**
+ *
+ * Base class for tests that manage their own time stamps
+ * We need to separate these from tests that manged the time stamp
+ * themselves, because we create/destroy the Phoenix tables
+ * between tests and only allow a table time stamp to increase.
+ * If we let HBase set the time stamps, then our client time stamps
+ * will usually be smaller than these time stamps and the table
+ * deletion/creation would fail.
+ *
+ *
+ * @since 0.1
+ */
+public abstract class BaseClientManagedTimeIT extends BaseConnectedQueryIT {
+ @Before
+ public void doTestSetup() throws Exception {
+ long ts = nextTimestamp();
+ deletePriorTables(ts-1);
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~HEAD
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~HEAD
new file mode 100644
index 0000000..679f29d
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~HEAD
@@ -0,0 +1,66 @@
+/*
+ * 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 javax.annotation.concurrent.NotThreadSafe;
+
+import org.apache.hadoop.conf.Configuration;
+import org.apache.hadoop.hbase.HConstants;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.experimental.categories.Category;
+
+/**
+ * Base class for tests that let HBase set timestamps.
+ * We need to separate these from tests that rely on clients
+ * to set timestamps, because we create/destroy the Phoenix tables
+ * between tests and only allow a table time stamp to increase.
+ * Without this separation table deletion/creation would fail.
+ *
+ * All tests extending this class use the mini cluster that is
+ * different from the mini cluster used by test classes extending
+ * {@link BaseClientManagedTimeIT}.
+ *
+ * @since 0.1
+ */
+@NotThreadSafe
+@Category(HBaseManagedTimeTest.class)
+public abstract class BaseHBaseManagedTimeIT extends BaseTest {
+ protected static Configuration getTestClusterConfig() {
+ // don't want callers to modify config.
+ return new Configuration(config);
+ }
+
+ @BeforeClass
+ public static void doSetup() throws Exception {
+ setUpTestDriver(ReadOnlyProps.EMPTY_PROPS);
+ }
+
+ @AfterClass
+ public static void doTeardown() throws Exception {
+ dropNonSystemTables();
+ }
+
+ @After
+ public void cleanUpAfterTest() throws Exception {
+ deletePriorTables(HConstants.LATEST_TIMESTAMP, getUrl());
+ }
+}
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid) b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
new file mode 100644
index 0000000..c00b74c
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/BaseHBaseManagedTimeIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
@@ -0,0 +1,41 @@
+/*
+ * 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 org.apache.hadoop.hbase.HConstants;
+import org.junit.Before;
+
+/**
+ *
+ * Base class for tests that have HBase manage the time stamps.
+ * We need to separate these from tests that manged the time stamp
+ * themselves, because we create/destroy the Phoenix tables
+ * between tests and only allow a table time stamp to increase.
+ * If we let HBase set the time stamps, then our client time stamps
+ * will usually be smaller than these time stamps and the table
+ * deletion/creation would fail.
+ *
+ *
+ * @since 0.1
+ */
+public abstract class BaseHBaseManagedTimeIT extends BaseConnectedQueryIT {
+ @Before
+ public void doTestSetup() throws Exception {
+ deletePriorTables(HConstants.LATEST_TIMESTAMP);
+ }
+}
[2/4] phoenix git commit: PHOENIX-1964 - porting from master
Posted by co...@apache.org.
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid) b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
new file mode 100644
index 0000000..123ff35
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~PHOENIX-130 Separate execution of slow (integration) tests from fast unit tests (GabrielReid)
@@ -0,0 +1,2242 @@
+/*
+ * 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.JOIN_CUSTOMER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SCHEMA;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.PHOENIX_JDBC_URL;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.Before;
+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.collect.Lists;
+import com.google.common.collect.Maps;
+
+@RunWith(Parameterized.class)
+public class HashJoinIT extends BaseHBaseManagedTimeIT {
+
+ private String[] indexDDL;
+ private String[] plans;
+
+ public HashJoinIT(String[] indexDDL, String[] plans) {
+ this.indexDDL = indexDDL;
+ this.plans = plans;
+ }
+
+ @BeforeClass
+ public static void doSetup() throws Exception {
+ Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+ // Don't split intra region so we can more easily know that the n-way parallelization is for the explain plan
+ props.put(QueryServices.MAX_INTRA_REGION_PARALLELIZATION_ATTRIB, Integer.toString(1));
+ // Forces server cache to be used
+ props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2));
+ // Must update config before starting server
+ startServer(getUrl(), new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Before
+ public void initTable() throws Exception {
+ initTableValues();
+ if (indexDDL != null && indexDDL.length > 0) {
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ for (String ddl : indexDDL) {
+ try {
+ conn.createStatement().execute(ddl);
+ } catch (TableAlreadyExistsException e) {
+ }
+ }
+ conn.close();
+ }
+ }
+
+ @Parameters(name="{0}")
+ public static Collection<Object> data() {
+ List<Object> testCases = Lists.newArrayList();
+ testCases.add(new String[][] {
+ {}, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY QUANTITY < 5000\n" +
+ " BUILD HASH TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testSelfJoin
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSelfJoin
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testStarJoin
+ * SELECT order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
+ " BUILD HASH TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testStarJoin
+ * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [O.order_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME,
+ }});
+ testCases.add(new String[][] {
+ {
+ "CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+ "CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+ "CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+ }, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.0:NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [I.0:NAME]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.:item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.0:NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [I.item_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, I.item_id]\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY QUANTITY < 5000\n" +
+ " BUILD HASH TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier",
+ /*
+ * testSelfJoin
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSelfJoin
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [I1.0:NAME, I2.0:NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
+ /*
+ * testStarJoin
+ * SELECT order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
+ " BUILD HASH TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testStarJoin
+ * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.order_id]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" +
+ " BUILD HASH TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer",
+ }});
+ return testCases;
+ }
+
+
+ protected void initTableValues() throws Exception {
+ ensureTableCreated(getUrl(), JOIN_CUSTOMER_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_ITEM_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_SUPPLIER_TABLE_FULL_NAME);
+ ensureTableCreated(getUrl(), JOIN_ORDER_TABLE_FULL_NAME);
+
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ conn.createStatement().execute("CREATE SEQUENCE my.seq");
+ SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ // Insert into customer table
+ PreparedStatement stmt = conn.prepareStatement(
+ "upsert into " + JOIN_CUSTOMER_TABLE_FULL_NAME +
+ " (\"customer_id\", " +
+ " NAME, " +
+ " PHONE, " +
+ " ADDRESS, " +
+ " LOC_ID, " +
+ " DATE) " +
+ "values (?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "C1");
+ stmt.setString(3, "999-999-1111");
+ stmt.setString(4, "101 XXX Street");
+ stmt.setString(5, "10001");
+ stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "C2");
+ stmt.setString(3, "999-999-2222");
+ stmt.setString(4, "202 XXX Street");
+ stmt.setString(5, null);
+ stmt.setDate(6, new Date(format.parse("2013-11-25 16:45:07").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "C3");
+ stmt.setString(3, "999-999-3333");
+ stmt.setString(4, "303 XXX Street");
+ stmt.setString(5, null);
+ stmt.setDate(6, new Date(format.parse("2013-11-25 10:06:29").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "C4");
+ stmt.setString(3, "999-999-4444");
+ stmt.setString(4, "404 XXX Street");
+ stmt.setString(5, "10004");
+ stmt.setDate(6, new Date(format.parse("2013-11-22 14:22:56").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "C5");
+ stmt.setString(3, "999-999-5555");
+ stmt.setString(4, "505 XXX Street");
+ stmt.setString(5, "10005");
+ stmt.setDate(6, new Date(format.parse("2013-11-27 09:37:50").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "C6");
+ stmt.setString(3, "999-999-6666");
+ stmt.setString(4, "606 XXX Street");
+ stmt.setString(5, "10001");
+ stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime()));
+ stmt.execute();
+
+ // Insert into item table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_ITEM_TABLE_FULL_NAME +
+ " (\"item_id\", " +
+ " NAME, " +
+ " PRICE, " +
+ " DISCOUNT1, " +
+ " DISCOUNT2, " +
+ " \"supplier_id\", " +
+ " DESCRIPTION) " +
+ "values (?, ?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "T1");
+ stmt.setInt(3, 100);
+ stmt.setInt(4, 5);
+ stmt.setInt(5, 10);
+ stmt.setString(6, "0000000001");
+ stmt.setString(7, "Item T1");
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "T2");
+ stmt.setInt(3, 200);
+ stmt.setInt(4, 5);
+ stmt.setInt(5, 8);
+ stmt.setString(6, "0000000001");
+ stmt.setString(7, "Item T2");
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "T3");
+ stmt.setInt(3, 300);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 12);
+ stmt.setString(6, "0000000002");
+ stmt.setString(7, "Item T3");
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "T4");
+ stmt.setInt(3, 400);
+ stmt.setInt(4, 6);
+ stmt.setInt(5, 10);
+ stmt.setString(6, "0000000002");
+ stmt.setString(7, "Item T4");
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "T5");
+ stmt.setInt(3, 500);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 15);
+ stmt.setString(6, "0000000005");
+ stmt.setString(7, "Item T5");
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "T6");
+ stmt.setInt(3, 600);
+ stmt.setInt(4, 8);
+ stmt.setInt(5, 15);
+ stmt.setString(6, "0000000006");
+ stmt.setString(7, "Item T6");
+ stmt.execute();
+
+ stmt.setString(1, "invalid001");
+ stmt.setString(2, "INVALID-1");
+ stmt.setInt(3, 0);
+ stmt.setInt(4, 0);
+ stmt.setInt(5, 0);
+ stmt.setString(6, "0000000000");
+ stmt.setString(7, "Invalid item for join test");
+ stmt.execute();
+
+ // Insert into supplier table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_SUPPLIER_TABLE_FULL_NAME +
+ " (\"supplier_id\", " +
+ " NAME, " +
+ " PHONE, " +
+ " ADDRESS, " +
+ " LOC_ID) " +
+ "values (?, ?, ?, ?, ?)");
+ stmt.setString(1, "0000000001");
+ stmt.setString(2, "S1");
+ stmt.setString(3, "888-888-1111");
+ stmt.setString(4, "101 YYY Street");
+ stmt.setString(5, "10001");
+ stmt.execute();
+
+ stmt.setString(1, "0000000002");
+ stmt.setString(2, "S2");
+ stmt.setString(3, "888-888-2222");
+ stmt.setString(4, "202 YYY Street");
+ stmt.setString(5, "10002");
+ stmt.execute();
+
+ stmt.setString(1, "0000000003");
+ stmt.setString(2, "S3");
+ stmt.setString(3, "888-888-3333");
+ stmt.setString(4, "303 YYY Street");
+ stmt.setString(5, null);
+ stmt.execute();
+
+ stmt.setString(1, "0000000004");
+ stmt.setString(2, "S4");
+ stmt.setString(3, "888-888-4444");
+ stmt.setString(4, "404 YYY Street");
+ stmt.setString(5, null);
+ stmt.execute();
+
+ stmt.setString(1, "0000000005");
+ stmt.setString(2, "S5");
+ stmt.setString(3, "888-888-5555");
+ stmt.setString(4, "505 YYY Street");
+ stmt.setString(5, "10005");
+ stmt.execute();
+
+ stmt.setString(1, "0000000006");
+ stmt.setString(2, "S6");
+ stmt.setString(3, "888-888-6666");
+ stmt.setString(4, "606 YYY Street");
+ stmt.setString(5, "10006");
+ stmt.execute();
+
+ // Insert into order table
+ stmt = conn.prepareStatement(
+ "upsert into " + JOIN_ORDER_TABLE_FULL_NAME +
+ " (\"order_id\", " +
+ " \"customer_id\", " +
+ " \"item_id\", " +
+ " PRICE, " +
+ " QUANTITY," +
+ " DATE) " +
+ "values (?, ?, ?, ?, ?, ?)");
+ stmt.setString(1, "000000000000001");
+ stmt.setString(2, "0000000004");
+ stmt.setString(3, "0000000001");
+ stmt.setInt(4, 100);
+ stmt.setInt(5, 1000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000002");
+ stmt.setString(2, "0000000003");
+ stmt.setString(3, "0000000006");
+ stmt.setInt(4, 552);
+ stmt.setInt(5, 2000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000003");
+ stmt.setString(2, "0000000002");
+ stmt.setString(3, "0000000002");
+ stmt.setInt(4, 190);
+ stmt.setInt(5, 3000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000004");
+ stmt.setString(2, "0000000004");
+ stmt.setString(3, "0000000006");
+ stmt.setInt(4, 510);
+ stmt.setInt(5, 4000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-26 13:26:04").getTime()));
+ stmt.execute();
+
+ stmt.setString(1, "000000000000005");
+ stmt.setString(2, "0000000005");
+ stmt.setString(3, "0000000003");
+ stmt.setInt(4, 264);
+ stmt.setInt(5, 5000);
+ stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));
+ stmt.execute();
+
+ conn.commit();
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testDefaultJoin() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testInnerJoin() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertEquals(1, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertEquals(2, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertEquals(3, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertEquals(4, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertEquals(5, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+ assertEquals(6, rs.getInt(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLeftJoin() throws Exception {
+ String query[] = new String[3];
+ query[0] = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ query[1] = "SELECT " + JOIN_ITEM_TABLE_FULL_NAME + ".\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\" ORDER BY \"item_id\"";
+ query[2] = "SELECT item.\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, supp.\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ for (int i = 0; i < query.length; i++) {
+ PreparedStatement statement = conn.prepareStatement(query[i]);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+
+ assertFalse(rs.next());
+ rs.close();
+ statement.close();
+ }
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRightJoin() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp RIGHT JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " item ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testInnerJoinWithPreFilters() throws Exception {
+ String query1 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND supp.\"supplier_id\" BETWEEN '0000000001' AND '0000000005'";
+ String query2 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005')";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLeftJoinWithPreFilters() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005') ORDER BY \"item_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "invalid001");
+ assertEquals(rs.getString(2), "INVALID-1");
+ assertNull(rs.getString(3));
+ assertNull(rs.getString(4));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJoinWithPostFilters() throws Exception {
+ String query1 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp RIGHT JOIN " + JOIN_ITEM_TABLE_FULL_NAME + " item ON item.\"supplier_id\" = supp.\"supplier_id\" WHERE supp.\"supplier_id\" BETWEEN '0000000001' AND '0000000005'";
+ String query2 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" WHERE supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005'";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testStarJoin() throws Exception {
+ String[] query = new String[2];
+ query[0] = "SELECT \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN "
+ + JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
+ query[1] = "SELECT /*+ NO_STAR_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o JOIN "
+ + JOIN_CUSTOMER_TABLE_FULL_NAME + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ for (int i = 0; i < query.length; i++) {
+ PreparedStatement statement = conn.prepareStatement(query[i]);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000001");
+ assertEquals(rs.getString("\"order_id\""), "000000000000001");
+ assertEquals(rs.getString(2), "C4");
+ assertEquals(rs.getString("C.name"), "C4");
+ assertEquals(rs.getString(3), "T1");
+ assertEquals(rs.getString("iName"), "T1");
+ assertEquals(rs.getInt(4), 1000);
+ assertEquals(rs.getInt("Quantity"), 1000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000002");
+ assertEquals(rs.getString(2), "C3");
+ assertEquals(rs.getString(3), "T6");
+ assertEquals(rs.getInt(4), 2000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000003");
+ assertEquals(rs.getString(2), "C2");
+ assertEquals(rs.getString(3), "T2");
+ assertEquals(rs.getInt(4), 3000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000004");
+ assertEquals(rs.getString(2), "C4");
+ assertEquals(rs.getString(3), "T6");
+ assertEquals(rs.getInt(4), 4000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000005");
+ assertEquals(rs.getString(2), "C5");
+ assertEquals(rs.getString(3), "T3");
+ assertEquals(rs.getInt(4), 5000);
+ assertNotNull(rs.getDate(5));
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query[i]);
+ assertEquals(plans[11 + i], QueryUtil.getExplainPlan(rs));
+ }
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLeftJoinWithAggregation() throws Exception {
+ String query1 = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
+ String query2 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC";
+ String query3 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ITEM_TABLE_FULL_NAME + " i LEFT JOIN "
+ + JOIN_ORDER_TABLE_FULL_NAME + " o ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T1");
+ assertEquals(rs.getInt(2), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T2");
+ assertEquals(rs.getInt(2), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T3");
+ assertEquals(rs.getInt(2), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T6");
+ assertEquals(rs.getInt(2), 6000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[0], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[1], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query3);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000004");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000005");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "invalid001");
+ assertEquals(rs.getInt("q"), 0);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query3);
+ assertEquals(plans[2], QueryUtil.getExplainPlan(rs));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testRightJoinWithAggregation() throws Exception {
+ String query1 = "SELECT i.name, sum(quantity) FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o RIGHT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
+ String query2 = "SELECT i.\"item_id\" iid, sum(quantity) q FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o RIGHT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query1);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "INVALID-1");
+ assertEquals(rs.getInt(2), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T1");
+ assertEquals(rs.getInt(2), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T2");
+ assertEquals(rs.getInt(2), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T3");
+ assertEquals(rs.getInt(2), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T4");
+ assertEquals(rs.getInt(2), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T5");
+ assertEquals(rs.getInt(2), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "T6");
+ assertEquals(rs.getInt(2), 6000);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query1);
+ assertEquals(plans[3], QueryUtil.getExplainPlan(rs));
+
+ statement = conn.prepareStatement(query2);
+ rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000006");
+ assertEquals(rs.getInt("q"), 6000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000003");
+ assertEquals(rs.getInt("q"), 5000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000002");
+ assertEquals(rs.getInt("q"), 3000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000001");
+ assertEquals(rs.getInt("q"), 1000);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000004");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "0000000005");
+ assertEquals(rs.getInt("q"), 0);
+ assertTrue (rs.next());
+ assertEquals(rs.getString("iid"), "invalid001");
+ assertEquals(rs.getInt("q"), 0);
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query2);
+ assertEquals(plans[4], QueryUtil.getExplainPlan(rs));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLeftRightJoin() throws Exception {
+ String query = "SELECT \"order_id\", i.name, s.name, quantity, date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
+ + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertNull(rs.getString(2));
+ assertEquals(rs.getString(3), "S5");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertNull(rs.getString(2));
+ assertEquals(rs.getString(3), "S4");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertNull(rs.getString(2));
+ assertEquals(rs.getString(3), "S3");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 1000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000002");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 2000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000003");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 3000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000004");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 4000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000005");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "S2");
+ assertEquals(rs.getInt(4), 5000);
+ assertNotNull(rs.getDate(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testMultiLeftJoin() throws Exception {
+ String query = "SELECT \"order_id\", i.name, s.name, quantity, date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o LEFT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" LEFT JOIN "
+ + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 1000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000002");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 2000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000003");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 3000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000004");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 4000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000005");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "S2");
+ assertEquals(rs.getInt(4), 5000);
+ assertNotNull(rs.getDate(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testMultiRightJoin() throws Exception {
+ String query = "SELECT \"order_id\", i.name, s.name, quantity, date FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o RIGHT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
+ + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "S5");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertNull(rs.getString(2));
+ assertEquals(rs.getString(3), "S4");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertNull(rs.getString(2));
+ assertEquals(rs.getString(3), "S3");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertNull(rs.getString(1));
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "S2");
+ assertEquals(rs.getInt(4), 0);
+ assertNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 1000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000002");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 2000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000003");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "S1");
+ assertEquals(rs.getInt(4), 3000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000004");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "S6");
+ assertEquals(rs.getInt(4), 4000);
+ assertNotNull(rs.getDate(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "000000000000005");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "S2");
+ assertEquals(rs.getInt(4), 5000);
+ assertNotNull(rs.getDate(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJoinWithWildcard() throws Exception {
+ String query = "SELECT * FROM " + JOIN_ITEM_TABLE_FULL_NAME + " LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000001");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T1");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 100);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 5);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 10);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000001");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T1");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000001");
+ assertEquals(rs.getString("supp.name"), "S1");
+ assertEquals(rs.getString("supp.phone"), "888-888-1111");
+ assertEquals(rs.getString("supp.address"), "101 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10001");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000002");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T2");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 200);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 5);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 8);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000001");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T2");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000001");
+ assertEquals(rs.getString("supp.name"), "S1");
+ assertEquals(rs.getString("supp.phone"), "888-888-1111");
+ assertEquals(rs.getString("supp.address"), "101 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10001");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000003");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T3");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 300);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 8);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 12);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000002");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T3");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000002");
+ assertEquals(rs.getString("supp.name"), "S2");
+ assertEquals(rs.getString("supp.phone"), "888-888-2222");
+ assertEquals(rs.getString("supp.address"), "202 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10002");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000004");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T4");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 400);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 6);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 10);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000002");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T4");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000002");
+ assertEquals(rs.getString("supp.name"), "S2");
+ assertEquals(rs.getString("supp.phone"), "888-888-2222");
+ assertEquals(rs.getString("supp.address"), "202 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10002");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000005");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T5");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 500);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 8);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 15);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000005");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T5");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000005");
+ assertEquals(rs.getString("supp.name"), "S5");
+ assertEquals(rs.getString("supp.phone"), "888-888-5555");
+ assertEquals(rs.getString("supp.address"), "505 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10005");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "0000000006");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "T6");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 600);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 8);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 15);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000006");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Item T6");
+ assertEquals(rs.getString("SUPP.supplier_id"), "0000000006");
+ assertEquals(rs.getString("supp.name"), "S6");
+ assertEquals(rs.getString("supp.phone"), "888-888-6666");
+ assertEquals(rs.getString("supp.address"), "606 YYY Street");
+ assertEquals(rs.getString("supp.loc_id"), "10006");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".item_id"), "invalid001");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".NAME"), "INVALID-1");
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".PRICE"), 0);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT1"), 0);
+ assertEquals(rs.getInt(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DISCOUNT2"), 0);
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".supplier_id"), "0000000000");
+ assertEquals(rs.getString(JOIN_ITEM_TABLE_DISPLAY_NAME + ".DESCRIPTION"), "Invalid item for join test");
+ assertNull(rs.getString("SUPP.supplier_id"));
+ assertNull(rs.getString("supp.name"));
+ assertNull(rs.getString("supp.phone"));
+ assertNull(rs.getString("supp.address"));
+ assertNull(rs.getString("supp.loc_id"));
+
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("EXPLAIN " + query);
+ assertEquals(plans[5], QueryUtil.getExplainPlan(rs));
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testJoinWithTableWildcard() throws Exception {
+ String query = "SELECT s.*, "+ JOIN_ITEM_TABLE_FULL_NAME + ".*, \"order_id\" FROM " + JOIN_ORDER_TABLE_FULL_NAME + " o RIGHT JOIN "
+ + JOIN_ITEM_TABLE_FULL_NAME + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
+ + JOIN_SUPPLIER_TABLE_FULL_NAME + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
+ Properties props = new Properties(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ ResultSetMetaData md = rs.getMetaData();
+ assertEquals(md.getColumnCount(), 13);
+
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "S5");
+ assertEquals(rs.getString(3), "888-888-5555");
+ assertEquals(rs.getString(4), "505 YYY Street");
+ assertEquals(rs.getString(5), "10005");
+ assertEquals(rs.getString(6), "0000000005");
+ assertEquals(rs.getString(7), "T5");
+ assertEquals(rs.getInt(8), 500);
+ assertEquals(rs.getInt(9), 8);
+ assertEquals(rs.getInt(10), 15);
+ assertEquals(rs.getString(11), "0000000005");
+ assertEquals(rs.getString(12), "Item T5");
+ assertNull(rs.getString(13));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "S4");
+ assertEquals(rs.getString(3), "888-888-4444");
+ assertEquals(rs.getString(4), "404 YYY Street");
+ assertNull(rs.getString(5));
+ assertNull(rs.getString(6));
+ assertNull(rs.getString(7));
+ assertEquals(rs.getInt(8), 0);
+ assertEquals(rs.getInt(9), 0);
+ assertEquals(rs.getInt(10), 0);
+ assertNull(rs.getString(11));
+ assertNull(rs.getString(12));
+ assertNull(rs.getString(13));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "S3");
+ assertEquals(rs.getString(3), "888-888-3333");
+ assertEquals(rs.getString(4), "303 YYY Street");
+ assertNull(rs.getString(5));
+ assertNull(rs.getString(6));
+ assertNull(rs.getString(7));
+ assertEquals(rs.getInt(8), 0);
+ assertEquals(rs.getInt(9), 0);
+ assertEquals(rs.getInt(10), 0);
+ assertNull(rs.getString(11));
+ assertNull(rs.getString(12));
+ assertNull(rs.getString(13));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "S2");
+ assertEquals(rs.getString(3), "888-888-2222");
+ assertEquals(rs.getString(4), "202 YYY Street");
+ assertEquals(rs.getString(5), "10002");
+ assertEquals(rs.getString(6), "0000000004");
+ assertEquals(rs.getString(7), "T4");
+ assertEquals(rs.getInt(8), 400);
+ assertEquals(rs.getInt(9), 6);
+ assertEquals(rs.getInt(10), 10);
+ assertEquals(rs.getString(11), "0000000002");
+ assertEquals(rs.getString(12), "Item T4");
+ assertNull(rs.getString(13));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "S1");
+ assertEquals(rs.getString(3), "888-888-1111");
+ assertEquals(rs.getString(4), "101 YYY Street");
+ assertEquals(rs.getString(5), "10001");
+ assertEquals(rs.getString(6), "0000000001");
+ assertEquals(rs.getString(7), "T1");
+ assertEquals(rs.getInt(8), 100);
+ assertEquals(rs.getInt(9), 5);
+ assertEquals(rs.getInt(10), 10);
+ assertEquals(rs.getString(11), "0000000001");
+ assertEquals(rs.getString(12), "Item T1");
+ assertEquals(rs.getString(13), "000000000000001");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "S6");
+ assertEquals(rs.getString(3), "888-888-6666");
+ assertEquals(rs.getString(4), "606 YYY Street");
+ assertEquals(rs.getString(5), "10006");
+
<TRUNCATED>
[3/4] phoenix git commit: PHOENIX-1964 - porting from master
Posted by co...@apache.org.
http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD
new file mode 100644
index 0000000..a03204a
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD
@@ -0,0 +1,3818 @@
+/*
+ * 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.JOIN_CUSTOMER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SCHEMA;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_DISPLAY_NAME;
+import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertNotNull;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Collection;
+import java.util.List;
+import java.util.Map;
+import java.util.Properties;
+
+import org.apache.phoenix.exception.SQLExceptionCode;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.schema.TableAlreadyExistsException;
+import org.apache.phoenix.util.MetaDataUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.QueryUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
+import org.junit.Before;
+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.collect.Lists;
+import com.google.common.collect.Maps;
+
+@RunWith(Parameterized.class)
+public class HashJoinIT extends BaseHBaseManagedTimeIT {
+
+ private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ private String[] indexDDL;
+ private String[] plans;
+
+ public HashJoinIT(String[] indexDDL, String[] plans) {
+ this.indexDDL = indexDDL;
+ this.plans = plans;
+ }
+
+ @BeforeClass
+ @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class)
+ public static void doSetup() throws Exception {
+ Map<String,String> props = Maps.newHashMapWithExpectedSize(3);
+ // Forces server cache to be used
+ props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2));
+ // Must update config before starting server
+ setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator()));
+ }
+
+ @Before
+ public void initTable() throws Exception {
+ initJoinTableValues(getUrl(), null, null);
+ if (indexDDL != null && indexDDL.length > 0) {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ for (String ddl : indexDDL) {
+ try {
+ conn.createStatement().execute(ddl);
+ } catch (TableAlreadyExistsException e) {
+ }
+ }
+ conn.close();
+ }
+ }
+
+ @Parameters
+ public static Collection<Object> data() {
+ List<Object> testCases = Lists.newArrayList();
+ testCases.add(new String[][] {
+ {}, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY QUANTITY < 5000\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")",
+ /*
+ * testSelfJoin()
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.item_id\")",
+ /*
+ * testSelfJoin()
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [I1.NAME, I2.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.supplier_id\")",
+ /*
+ * testStarJoin()
+ * SELECT order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testStarJoin()
+ * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER SORTED BY [\"O.order_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")",
+ /*
+ * testSubJoin()
+ * SELECT * FROM joinCustomerTable c
+ * INNER JOIN (joinOrderTable o
+ * INNER JOIN (joinSupplierTable s
+ * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id)
+ * ON o.item_id = i.item_id)
+ * ON c.customer_id = o.customer_id
+ * WHERE c.customer_id <= '0000000005'
+ * AND order_id != '000000000000003'
+ * AND i.name != 'T3'
+ * ORDER BY c.customer_id, i.name
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [\"C.customer_id\", I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY NAME != 'T3'\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i
+ * ON o.item_id = i.iid
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT o.iid, sum(o.quantity) q
+ * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o
+ * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i
+ * ON o.iid = i.item_id
+ * GROUP BY o.iid ORDER BY q DESC
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid FROM joinItemTable) AS i
+ * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC NULLS LAST, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * JOIN (SELECT item_id iid FROM joinItemTable) AS i
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testNestedSubqueries()
+ * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c
+ * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate,
+ * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription,
+ * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id
+ * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o
+ * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription,
+ * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id
+ * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s
+ * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i
+ * ON i.sid = s.sid) as qi
+ * ON o.iid = qi.iiid) as qo
+ * ON c.cid = qo.ocid
+ * WHERE c.cid <= '0000000005'
+ * AND qo.ooid != '000000000000003'
+ * AND qo.iname != 'T3'
+ * ORDER BY c.cid, qo.iname
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [C.CID, QO.INAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY NAME != 'T3'\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER 4 ROW LIMIT\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.supplier_id\")\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))",
+ /*
+ * testJoinWithSetMaxRows()
+ * statement.setMaxRows(4);
+ * SELECT order_id, i.name, quantity FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id;
+ * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i
+ * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o
+ * ON o.item_id = i.item_id;
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ }});
+ testCases.add(new String[][] {
+ {
+ "CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+ "CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+ "CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+ }, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY QUANTITY < 5000\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSelfJoin()
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")",
+ /*
+ * testSelfJoin()
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
+ /*
+ * testStarJoin()
+ * SELECT order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testStarJoin()
+ * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [\"O.order_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testSubJoin()
+ * SELECT * FROM joinCustomerTable c
+ * INNER JOIN (joinOrderTable o
+ * INNER JOIN (joinSupplierTable s
+ * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id)
+ * ON o.item_id = i.item_id)
+ * ON c.customer_id = o.customer_id
+ * WHERE c.customer_id <= '0000000005'
+ * AND order_id != '000000000000003'
+ * AND i.name != 'T3'
+ * ORDER BY c.customer_id, i.name
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [\"C.customer_id\", \"I.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY \"NAME\" != 'T3'\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i
+ * ON o.item_id = i.iid
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT o.iid, sum(o.quantity) q
+ * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o
+ * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i
+ * ON o.iid = i.item_id
+ * GROUP BY o.iid ORDER BY q DESC
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid FROM joinItemTable) AS i
+ * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC NULLS LAST, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * JOIN (SELECT item_id iid FROM joinItemTable) AS i
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC, I.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testNestedSubqueries()
+ * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c
+ * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate,
+ * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription,
+ * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id
+ * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o
+ * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription,
+ * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id
+ * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s
+ * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i
+ * ON i.sid = s.sid) as qi
+ * ON o.iid = qi.iiid) as qo
+ * ON c.cid = qo.ocid
+ * WHERE c.cid <= '0000000005'
+ * AND qo.ooid != '000000000000003'
+ * AND qo.iname != 'T3'
+ * ORDER BY c.cid, qo.iname
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [C.CID, QO.INAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY \"NAME\" != 'T3'\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER 4 ROW LIMIT\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))",
+ /*
+ * testJoinWithSetMaxRows()
+ * statement.setMaxRows(4);
+ * SELECT order_id, i.name, quantity FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id;
+ * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i
+ * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o
+ * ON o.item_id = i.item_id;
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".OrderTable\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ }});
+ testCases.add(new String[][] {
+ {
+ "CREATE LOCAL INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
+ "CREATE LOCAL INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
+ "CREATE LOCAL INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
+ }, {
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC"
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testLeftJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testRightJoinWithAggregation()
+ * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
+ * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
+ * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithWildcard()
+ * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
+ * ON joinItemTable.supplier_id = supp.supplier_id
+ * ORDER BY item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
+ * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
+ * AND (supp.name BETWEEN 'S1' AND 'S5')
+ * WHERE item.name BETWEEN 'T1' AND 'T5'
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinPlanWithIndex()
+ * SELECT item.item_id, item.name, supp.supplier_id, supp.name
+ * FROM joinItemTable item INNER JOIN joinSupplierTable supp
+ * ON item.supplier_id = supp.supplier_id
+ * WHERE (item.name = 'T1' OR item.name = 'T5')
+ * AND (supp.name = 'S1' OR supp.name = 'S5')
+ */
+ "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSkipMergeOptimization()
+ * SELECT s.name FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
+ * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY QUANTITY < 5000\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")",
+ /*
+ * testSelfJoin()
+ * SELECT i2.item_id, i1.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
+ * ORDER BY i1.item_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")",
+ /*
+ * testSelfJoin()
+ * SELECT i1.name, i2.name FROM joinItemTable i1
+ * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
+ * ORDER BY i1.name, i2.name
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY \"I1.:item_id\" IN (\"I2.0:supplier_id\")",
+ /*
+ * testStarJoin()
+ * SELECT order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 1\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testStarJoin()
+ * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
+ * FROM joinOrderTable o
+ * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
+ * JOIN joinItemTable i ON o.item_id = i.item_id
+ * ORDER BY order_id
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [\"O.order_id\"]\n"+
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME+" [-32768]\n"+
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")",
+ /*
+ * testSubJoin()
+ * SELECT * FROM joinCustomerTable c
+ * INNER JOIN (joinOrderTable o
+ * INNER JOIN (joinSupplierTable s
+ * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id)
+ * ON o.item_id = i.item_id)
+ * ON c.customer_id = o.customer_id
+ * WHERE c.customer_id <= '0000000005'
+ * AND order_id != '000000000000003'
+ * AND i.name != 'T3'
+ * ORDER BY c.customer_id, i.name
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [\"C.customer_id\", \"I.0:NAME\"]\n"+
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" +
+ " SERVER FILTER BY \"NAME\" != 'T3'\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.name, sum(quantity) FROM joinOrderTable o
+ * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i
+ * ON o.item_id = i.iid
+ * GROUP BY i.name ORDER BY i.name
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n"+
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT o.iid, sum(o.quantity) q
+ * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o
+ * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i
+ * ON o.iid = i.item_id
+ * GROUP BY o.iid ORDER BY q DESC
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
+ " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid FROM joinItemTable) AS i
+ * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC NULLS LAST, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n"+
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithSubqueryAndAggregation()
+ * SELECT i.iid, o.q
+ * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
+ * JOIN (SELECT item_id iid FROM joinItemTable) AS i
+ * ON o.iid = i.iid
+ * ORDER BY o.q DESC, i.iid
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ " SERVER SORTED BY [O.Q DESC, I.IID]\n"+
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testNestedSubqueries()
+ * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c
+ * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate,
+ * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription,
+ * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id
+ * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o
+ * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription,
+ * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id
+ * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s
+ * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i
+ * ON i.sid = s.sid) as qi
+ * ON o.iid = qi.iiid) as qo
+ * ON c.cid = qo.ocid
+ * WHERE c.cid <= '0000000005'
+ * AND qo.ooid != '000000000000003'
+ * AND qo.iname != 'T3'
+ * ORDER BY c.cid, qo.iname
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" +
+ " SERVER SORTED BY [C.CID, QO.INAME]\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY \"NAME\" != 'T3'\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME,
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ " SERVER 4 ROW LIMIT\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL LEFT-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithLimit()
+ * SELECT order_id, i.name, s.name, s.address, quantity
+ * FROM joinSupplierTable s
+ * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
+ * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
+ */
+ "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))",
+ /*
+ * testJoinWithKeyRangeOptimization()
+ * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2
+ * FROM TEMP_TABLE_COMPOSITE_PK lhs
+ * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2
+ */
+ "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ "CLIENT MERGE SORT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" +
+ " CLIENT MERGE SORT\n" +
+ " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))",
+ /*
+ * testJoinWithSetMaxRows()
+ * statement.setMaxRows(4);
+ * SELECT order_id, i.name, quantity FROM joinItemTable i
+ * JOIN joinOrderTable o ON o.item_id = i.item_id;
+ * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i
+ * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o
+ * ON o.item_id = i.item_id;
+ */
+ "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" +
+ " SERVER FILTER BY FIRST KEY ONLY\n" +
+ "CLIENT MERGE SORT\n" +
+ "CLIENT 4 ROW LIMIT\n" +
+ " PARALLEL INNER-JOIN TABLE 0\n" +
+ " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" +
+ " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")\n" +
+ " JOIN-SCANNER 4 ROW LIMIT",
+ }});
+ return testCases;
+ }
+
+
+ @Test
+ public void testDefaultJoin() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testInnerJoin() throws Exception {
+ String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ PreparedStatement statement = conn.prepareStatement(query);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertEquals(1, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertEquals(2, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertEquals(3, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertEquals(4, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertEquals(5, rs.getInt(5));
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ assertEquals(rs.getString(4), "S6");
+ assertEquals(6, rs.getInt(5));
+
+ assertFalse(rs.next());
+ } finally {
+ conn.close();
+ }
+ }
+
+ @Test
+ public void testLeftJoin() throws Exception {
+ String query[] = new String[3];
+ query[0] = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ query[1] = "SELECT " + JOIN_ITEM_TABLE_FULL_NAME + ".\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\" ORDER BY \"item_id\"";
+ query[2] = "SELECT item.\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, supp.\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ Connection conn = DriverManager.getConnection(getUrl(), props);
+ try {
+ for (int i = 0; i < query.length; i++) {
+ PreparedStatement statement = conn.prepareStatement(query[i]);
+ ResultSet rs = statement.executeQuery();
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000001");
+ assertEquals(rs.getString(2), "T1");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000002");
+ assertEquals(rs.getString(2), "T2");
+ assertEquals(rs.getString(3), "0000000001");
+ assertEquals(rs.getString(4), "S1");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000003");
+ assertEquals(rs.getString(2), "T3");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000004");
+ assertEquals(rs.getString(2), "T4");
+ assertEquals(rs.getString(3), "0000000002");
+ assertEquals(rs.getString(4), "S2");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000005");
+ assertEquals(rs.getString(2), "T5");
+ assertEquals(rs.getString(3), "0000000005");
+ assertEquals(rs.getString(4), "S5");
+ assertTrue (rs.next());
+ assertEquals(rs.getString(1), "0000000006");
+ assertEquals(rs.getString(2), "T6");
+ assertEquals(rs.getString(3), "0000000006");
+ a
<TRUNCATED>