You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/09/29 02:22:11 UTC
[11/27] phoenix git commit: PHOENIX-4246 Breakup join related tests
into several integration tests so as not to create too many tables in one
test
http://git-wip-us.apache.org/repos/asf/phoenix/blob/bfcd57c2/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinIT.java
deleted file mode 100644
index 2ecc404..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinIT.java
+++ /dev/null
@@ -1,2563 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package org.apache.phoenix.end2end;
-
-import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
-import static org.junit.Assert.assertEquals;
-import 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.util.Collection;
-import java.util.List;
-import java.util.Properties;
-
-import org.apache.phoenix.exception.SQLExceptionCode;
-import org.apache.phoenix.query.QueryServices;
-import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.QueryUtil;
-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;
-
-@RunWith(Parameterized.class)
-public class SortMergeJoinIT extends BaseJoinIT {
-
- @Parameters
- public static Collection<Object> data() {
- List<Object> testCases = Lists.newArrayList();
- testCases.add(new String[][] {
- {}, {
- "SORT-MERGE-JOIN (LEFT) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- "AND\n" +
- " SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " AND (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY QUANTITY < 5000\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- " CLIENT SORTED BY [\"I.supplier_id\"]",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "CLIENT 4 ROW LIMIT",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY FIRST KEY ONLY"
- }});
- 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)"
- }, {
- "SORT-MERGE-JOIN (LEFT) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"S.:supplier_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER SORTED BY [\"I.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- " AND (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY QUANTITY < 5000\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- " CLIENT SORTED BY [\"I.0:supplier_id\"]",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "CLIENT 4 ROW LIMIT",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER Join.idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I1.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER Join.idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I2.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [\"I1.:item_id\"]"
- }});
- 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)"
- }, {
- "SORT-MERGE-JOIN (LEFT) TABLES\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +JOIN_SUPPLIER_TABLE_FULL_NAME + " [1]\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"S.:supplier_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " [1]\n" +
- " SERVER SORTED BY [\"I.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- " AND (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY QUANTITY < 5000\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- " CLIENT SORTED BY [\"I.0:supplier_id\"]",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " [1]\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER SORTED BY [\"O.item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "CLIENT 4 ROW LIMIT",
-
- "SORT-MERGE-JOIN (INNER) TABLES\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " [1]\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I1.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "AND\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + " [1]\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I2.:item_id\"]\n" +
- " CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [\"I1.:item_id\"]"
- }});
- return testCases;
- }
-
-
- public SortMergeJoinIT(String[] indexDDL, String[] plans) {
- super(indexDDL, plans);
- }
-
- @Test
- public void testDefaultJoin() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for " + seqName + " FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query[] = new String[3];
- query[0] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for " + seqName + " FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- query[1] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ " + tableName1 + ".\"item_id\", " + tableName1 + ".name, " + tableName2 + ".\"supplier_id\", " + tableName2 + ".name, next value for " + seqName + " FROM " + tableName1 + " LEFT JOIN " + tableName2 + " ON " + tableName1 + ".\"supplier_id\" = " + tableName2 + ".\"supplier_id\" ORDER BY \"item_id\"";
- query[2] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", " + tableName1 + ".name, supp.\"supplier_id\", " + tableName2 + ".name, next value for " + seqName + " FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON " + tableName1 + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName2 + " supp RIGHT JOIN " + tableName1 + " item ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND supp.\"supplier_id\" BETWEEN '0000000001' AND '0000000005' ORDER BY \"item_id\"";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005') ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005') ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName2 + " supp RIGHT JOIN " + tableName1 + " item ON item.\"supplier_id\" = supp.\"supplier_id\" WHERE supp.\"supplier_id\" BETWEEN '0000000001' AND '0000000005' ORDER BY \"item_id\"";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" WHERE supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005' ORDER BY \"item_id\"";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName3 = getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String[] query = new String[5];
- query[0] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.\"DATE\" FROM " + tableName4 + " o JOIN "
- + tableName3 + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
- query[1] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.\"DATE\" FROM " + tableName4 + " o, "
- + tableName3 + " c, "
- + tableName1 + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
- query[2] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.\"DATE\" FROM " + tableName4 + " o JOIN "
- + tableName3 + " c ON o.\"customer_id\" = c.\"customer_id\" JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\"";
- query[3] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.\"DATE\" FROM (" + tableName4 + " o, "
- + tableName3 + " c), "
- + tableName1 + " i WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
- query[4] = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, i.name iname, quantity, o.\"DATE\" FROM " + tableName4 + " o, ("
- + tableName3 + " c, "
- + tableName1 + " i) WHERE o.\"item_id\" = i.\"item_id\" AND o.\"customer_id\" = c.\"customer_id\" ORDER BY \"order_id\"";
- 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());
- }
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testLeftJoinWithAggregation() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.name, sum(quantity) FROM " + tableName4 + " o LEFT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\" iid, sum(quantity) q FROM " + tableName4 + " o LEFT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC";
- String query3 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\" iid, sum(quantity) q FROM " + tableName1 + " i LEFT JOIN "
- + tableName4 + " o ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
- 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());
-
- 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());
-
- 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());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testRightJoinWithAggregation() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.name, sum(quantity) FROM " + tableName4 + " o RIGHT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.name ORDER BY i.name";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ i.\"item_id\" iid, sum(quantity) q FROM " + tableName4 + " o RIGHT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" GROUP BY i.\"item_id\" ORDER BY q DESC NULLS LAST, iid";
- 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());
-
- 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());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testLeftRightJoin() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o LEFT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o LEFT JOIN "
- + "(" + tableName1 + " i RIGHT JOIN " + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\")"
- + " ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- try {
- PreparedStatement statement = conn.prepareStatement(query1);
- 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());
-
- statement = conn.prepareStatement(query2);
- 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 testRightLeftJoin() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query1 = "SELECT \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName1 + " i RIGHT JOIN "
- + tableName4 + " o ON o.\"item_id\" = i.\"item_id\" LEFT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\"";
- String query2 = "SELECT \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o RIGHT JOIN "
- + "(" + tableName1 + " i LEFT JOIN " + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\")"
- + " ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- try {
- PreparedStatement statement = conn.prepareStatement(query1);
- 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());
-
- statement = conn.prepareStatement(query2);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertNull(rs.getString(1));
- assertEquals(rs.getString(2), "INVALID-1");
- assertNull(rs.getString(3));
- assertEquals(rs.getInt(4), 0);
- assertNull(rs.getDate(5));
- 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));
- 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 testMultiLeftJoin() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String[] queries = {
- "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o LEFT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" LEFT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\"",
- "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o LEFT JOIN "
- + "(" + tableName1 + " i LEFT JOIN " + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\") "
- + "ON o.\"item_id\" = i.\"item_id\" ORDER BY \"order_id\""};
- try {
- for (String query : queries) {
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o RIGHT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- 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();
- }
- }
-
- // Basically a copy of testMultiRightJoin, but with a very small result scan chunk size
- // to test that repeated row keys within a single chunk are handled properly
- @Test
- public void testMultiRightJoin_SmallChunkSize() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- props.setProperty(QueryServices.SCAN_RESULT_CHUNK_SIZE, "1");
- Connection conn = DriverManager.getConnection(getUrl(), props);
-
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, s.name, quantity, \"DATE\" FROM " + tableName4 + " o RIGHT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- 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 {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ * FROM " + tableName1 + " LEFT JOIN " + tableName2 + " supp ON " + tableName1 + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000001");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T1");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 100);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 5);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 10);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000001");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000002");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T2");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 200);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 5);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 8);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000001");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000003");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T3");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 300);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 8);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 12);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000002");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000004");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T4");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 400);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 6);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 10);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000002");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000005");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T5");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 500);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 8);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 15);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000005");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "0000000006");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "T6");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 600);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 8);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 15);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000006");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".item_id"), "invalid001");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".NAME"), "INVALID-1");
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".PRICE"), 0);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT1"), 0);
- assertEquals(rs.getInt(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".DISCOUNT2"), 0);
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + ".supplier_id"), "0000000000");
- assertEquals(rs.getString(getDisplayTableName(conn, JOIN_ITEM_TABLE_FULL_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());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinWithTableWildcard() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ s.*, "+ tableName1 + ".*, \"order_id\" FROM " + tableName4 + " o RIGHT JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" RIGHT JOIN "
- + tableName2 + " s ON i.\"supplier_id\" = s.\"supplier_id\" ORDER BY \"order_id\", s.\"supplier_id\" DESC";
- 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");
- assertEquals(rs.getString(6), "0000000006");
- assertEquals(rs.getString(7), "T6");
- assertEquals(rs.getInt(8), 600);
- assertEquals(rs.getInt(9), 8);
- assertEquals(rs.getInt(10), 15);
- assertEquals(rs.getString(11), "0000000006");
- assertEquals(rs.getString(12), "Item T6");
- assertEquals(rs.getString(13), "000000000000002");
- 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), "0000000002");
- assertEquals(rs.getString(7), "T2");
- assertEquals(rs.getInt(8), 200);
- assertEquals(rs.getInt(9), 5);
- assertEquals(rs.getInt(10), 8);
- assertEquals(rs.getString(11), "0000000001");
- assertEquals(rs.getString(12), "Item T2");
- assertEquals(rs.getString(13), "000000000000003");
- 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");
- assertEquals(rs.getString(6), "0000000006");
- assertEquals(rs.getString(7), "T6");
- assertEquals(rs.getInt(8), 600);
- assertEquals(rs.getInt(9), 8);
- assertEquals(rs.getInt(10), 15);
- assertEquals(rs.getString(11), "0000000006");
- assertEquals(rs.getString(12), "Item T6");
- assertEquals(rs.getString(13), "000000000000004");
- 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), "0000000003");
- assertEquals(rs.getString(7), "T3");
- assertEquals(rs.getInt(8), 300);
- assertEquals(rs.getInt(9), 8);
- assertEquals(rs.getInt(10), 12);
- assertEquals(rs.getString(11), "0000000002");
- assertEquals(rs.getString(12), "Item T3");
- assertEquals(rs.getString(13), "000000000000005");
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinMultiJoinKeys() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName3 = getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ c.name, s.name FROM " + tableName3 + " c LEFT JOIN " + tableName2 + " s ON \"customer_id\" = \"supplier_id\" AND c.loc_id = s.loc_id AND substr(s.name, 2, 1) = substr(c.name, 2, 1) ORDER BY \"customer_id\"";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C1");
- assertEquals(rs.getString(2), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C2");
- assertNull(rs.getString(2));
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C3");
- assertEquals(rs.getString(2), "S3");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C4");
- assertNull(rs.getString(2));
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C5");
- assertEquals(rs.getString(2), "S5");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "C6");
- assertNull(rs.getString(2));
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinWithDifferentNumericJoinKeyTypes() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, i.price, discount2, quantity FROM " + tableName4 + " o INNER JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" AND o.price = (i.price * (100 - discount2)) / 100.0 WHERE quantity < 5000";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "000000000000004");
- assertEquals(rs.getString(2), "T6");
- assertEquals(rs.getInt(3), 600);
- assertEquals(rs.getInt(4), 15);
- assertEquals(rs.getInt(5), 4000);
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinWithDifferentDateJoinKeyTypes() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName3 = getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", c.name, o.\"DATE\" FROM " + tableName4 + " o INNER JOIN "
- + tableName3 + " c ON o.\"customer_id\" = c.\"customer_id\" AND o.\"DATE\" = c.\"DATE\" ORDER BY \"order_id\"";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "000000000000001");
- assertEquals(rs.getString(2), "C4");
- assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "000000000000002");
- assertEquals(rs.getString(2), "C3");
- assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "000000000000003");
- assertEquals(rs.getString(2), "C2");
- assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "000000000000005");
- assertEquals(rs.getString(2), "C5");
- assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinWithIncomparableJoinKeyTypes() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE_SORT_MERGE_JOIN*/ \"order_id\", i.name, i.price, discount2, quantity FROM " + tableName4 + " o INNER JOIN "
- + tableName1 + " i ON o.\"item_id\" = i.\"item_id\" AND o.price / 100 = substr(i.name, 2, 1)";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- statement.executeQuery();
- fail("Should have got SQLException.");
- } catch (SQLException e) {
- assertEquals(e.getErrorCode(), SQLExceptionCode.TYPE_MISMATCH.getErrorCode());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testJoinPlanWithIndex() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " 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' ORDER BY \"item_id\"";
- String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" WHERE (item.name = 'T1' OR item.name = 'T5') AND (supp.name = 'S1' OR supp.name = 'S5') ORDER BY \"item_id\"";
- 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), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertEquals(rs.getString(3), "0000000003");
- assertEquals(rs.getString(4), "S3");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000004");
- assertEquals(rs.getString(4), "S4");
- 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), "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 testJoinWithSkipMergeOptimization() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- Connection conn = DriverManager.getConnection(getUrl(), props);
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName4 = getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME);
- String query = "SELECT /*+ USE
<TRUNCATED>