phoenix-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jamestay...@apache.org
Subject [08/25] phoenix git commit: PHOENIX-4246 Breakup join related tests into several integration tests so as not to create too many tables in one test
Date Fri, 29 Sep 2017 01:24:42 GMT
http://git-wip-us.apache.org/repos/asf/phoenix/blob/ee20a8c9/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/BaseJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/BaseJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/BaseJoinIT.java
new file mode 100644
index 0000000..6e03a37
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/BaseJoinIT.java
@@ -0,0 +1,473 @@
+/*
+ * 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.join;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.assertEquals;
+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.SQLException;
+import java.sql.Timestamp;
+import java.text.SimpleDateFormat;
+import java.util.Map;
+import java.util.Properties;
+import java.util.regex.Pattern;
+
+import org.apache.phoenix.cache.ServerCacheClient;
+import org.apache.phoenix.end2end.ParallelStatsDisabledIT;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.StringUtil;
+import org.junit.Before;
+
+import com.google.common.collect.ImmutableMap;
+import com.google.common.collect.Maps;
+
+public abstract class BaseJoinIT extends ParallelStatsDisabledIT {
+	
+    protected static final String JOIN_SCHEMA = "Join";
+    protected static final String JOIN_ORDER_TABLE = "OrderTable";
+    protected static final String JOIN_CUSTOMER_TABLE = "CustomerTable";
+    protected static final String JOIN_ITEM_TABLE = "ItemTable";
+    protected static final String JOIN_SUPPLIER_TABLE = "SupplierTable";
+    protected static final String JOIN_COITEM_TABLE = "CoitemTable";
+    protected static final String JOIN_ORDER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_ORDER_TABLE + '"';
+    protected static final String JOIN_CUSTOMER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_CUSTOMER_TABLE + '"';
+    protected static final String JOIN_ITEM_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_ITEM_TABLE + '"';
+    protected static final String JOIN_SUPPLIER_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_SUPPLIER_TABLE + '"';
+    protected static final String JOIN_COITEM_TABLE_FULL_NAME = '"' + JOIN_SCHEMA + "\".\"" + JOIN_COITEM_TABLE + '"';
+
+    private static final Map<String,String> tableDDLMap;
+    
+    static {
+        ImmutableMap.Builder<String,String> builder = ImmutableMap.builder();
+        builder.put(JOIN_ORDER_TABLE_FULL_NAME, "create table " + JOIN_ORDER_TABLE_FULL_NAME +
+                "   (\"order_id\" varchar(15) not null primary key, " +
+                "    \"customer_id\" varchar(10), " +
+                "    \"item_id\" varchar(10), " +
+                "    price integer, " +
+                "    quantity integer, " +
+                "    date timestamp) IMMUTABLE_ROWS=true");
+        builder.put(JOIN_CUSTOMER_TABLE_FULL_NAME, "create table " + JOIN_CUSTOMER_TABLE_FULL_NAME +
+                "   (\"customer_id\" varchar(10) not null primary key, " +
+                "    name varchar, " +
+                "    phone varchar(12), " +
+                "    address varchar, " +
+                "    loc_id varchar(5), " +
+                "    date date) IMMUTABLE_ROWS=true");
+        builder.put(JOIN_ITEM_TABLE_FULL_NAME, "create table " + JOIN_ITEM_TABLE_FULL_NAME +
+                "   (\"item_id\" varchar(10) not null primary key, " +
+                "    name varchar, " +
+                "    price integer, " +
+                "    discount1 integer, " +
+                "    discount2 integer, " +
+                "    \"supplier_id\" varchar(10), " +
+                "    description varchar)");
+        builder.put(JOIN_SUPPLIER_TABLE_FULL_NAME, "create table " + JOIN_SUPPLIER_TABLE_FULL_NAME +
+                "   (\"supplier_id\" varchar(10) not null primary key, " +
+                "    name varchar, " +
+                "    phone varchar(12), " +
+                "    address varchar, " +
+                "    loc_id varchar(5))");
+        builder.put(JOIN_COITEM_TABLE_FULL_NAME, "create table " + JOIN_COITEM_TABLE_FULL_NAME +
+                "   (item_id varchar(10) NOT NULL, " +
+                "    item_name varchar NOT NULL, " +
+                "    co_item_id varchar(10), " +
+                "    co_item_name varchar " +
+                "   CONSTRAINT pk PRIMARY KEY (item_id, item_name)) " +
+                "   SALT_BUCKETS=4");
+        tableDDLMap = builder.build();
+    }
+    
+    protected String seqName;
+    protected String schemaName;
+    protected final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+    protected final String[] plans;
+    private final String[] indexDDL;
+    private final Map<String,String> virtualNameToRealNameMap = Maps.newHashMap();
+    
+    public BaseJoinIT(String[] indexDDL, String[] plans) {
+        this.indexDDL = indexDDL;
+        this.plans = plans;
+    }
+    
+    public BaseJoinIT() {
+        this.indexDDL = new String[0];
+        this.plans = new String[0];
+    }
+
+    protected String getTableName(Connection conn, String virtualName) throws Exception {
+        String realName = virtualNameToRealNameMap.get(virtualName);
+        if (realName == null) {
+            realName = SchemaUtil.getTableName(schemaName, generateUniqueName());
+            virtualNameToRealNameMap.put(virtualName, realName);
+            createTable(conn, virtualName, realName);
+            initValues(conn, virtualName, realName);
+            createIndexes(conn, virtualName, realName);
+        }
+        return realName;
+    }
+    
+    protected String getDisplayTableName(Connection conn, String virtualName) throws Exception {
+        return getTableName(conn, virtualName);
+    }
+
+    private void createTable(Connection conn, String virtualName, String realName) throws SQLException {
+        String ddl = tableDDLMap.get(virtualName);
+        if (ddl == null) {
+            throw new IllegalStateException("Expected to find " + virtualName + " in " + tableDDLMap);
+        }
+        ddl =  ddl.replace(virtualName, realName);
+        conn.createStatement().execute(ddl);
+    }
+
+    @Before
+    public void createSchema() throws SQLException {
+        Connection conn = DriverManager.getConnection(getUrl());
+        try {
+            schemaName = "S_" + generateUniqueName();
+            seqName = "SEQ_" + generateUniqueName();
+            conn.createStatement().execute("CREATE SEQUENCE " + seqName);
+        } finally {
+            conn.close();
+        }
+    }
+    
+    private String translateToVirtualPlan(String actualPlan) {
+        int size = virtualNameToRealNameMap.size();
+        String[] virtualNames = new String[size+1];
+        String[] realNames = new String[size+1];
+        int count = 0;
+        for (Map.Entry<String, String>entry : virtualNameToRealNameMap.entrySet()) {
+            virtualNames[count] = entry.getKey();
+            realNames[count] = entry.getValue();
+            count++;
+        }
+        realNames[count] = schemaName;
+        virtualNames[count]= JOIN_SCHEMA;
+        String convertedPlan =  StringUtil.replace(actualPlan, realNames, virtualNames);
+        return convertedPlan;
+    }
+    
+    protected void assertPlansMatch(String virtualPlanRegEx, String actualPlan) {
+        String convertedPlan = translateToVirtualPlan(actualPlan);
+        assertTrue("\"" + convertedPlan + "\" does not match \"" + virtualPlanRegEx + "\"", Pattern.matches(virtualPlanRegEx, convertedPlan));
+    }
+    
+    protected void assertPlansEqual(String virtualPlan, String actualPlan) {
+        String convertedPlan = translateToVirtualPlan(actualPlan);
+        assertEquals(virtualPlan, convertedPlan);
+    }
+    
+    private static void initValues(Connection conn, String virtualName, String realName) throws Exception {
+        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+        if (virtualName.equals(JOIN_CUSTOMER_TABLE_FULL_NAME)) {
+            // Insert into customer table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + realName +
+                    "   (\"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();
+        } else if (virtualName.equals(JOIN_ITEM_TABLE_FULL_NAME)) {
+        
+            // Insert into item table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + realName +
+                    "   (\"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();
+        } else if (virtualName.equals(JOIN_SUPPLIER_TABLE_FULL_NAME)) {
+
+            // Insert into supplier table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + realName +
+                    "   (\"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();
+        } else if (virtualName.equals(JOIN_ORDER_TABLE_FULL_NAME)) {
+
+            // Insert into order table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + realName +
+                    "   (\"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();
+        } else if (virtualName.equals(JOIN_COITEM_TABLE_FULL_NAME)) {
+            // Insert into coitem table
+            PreparedStatement stmt = conn.prepareStatement(
+                    "upsert into " + realName + 
+                    "   (item_id, " + 
+                    "    item_name, " + 
+                    "    co_item_id, " + 
+                    "    co_item_name) " + 
+                    "values (?, ?, ?, ?)");
+            stmt.setString(1, "0000000001");
+            stmt.setString(2, "T1");
+            stmt.setString(3, "0000000002");
+            stmt.setString(4, "T3");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000004");
+            stmt.setString(2, "T4");
+            stmt.setString(3, "0000000003");
+            stmt.setString(4, "T3");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000003");
+            stmt.setString(2, "T4");
+            stmt.setString(3, "0000000005");
+            stmt.setString(4, "T5");
+            stmt.execute();
+            
+            stmt.setString(1, "0000000006");
+            stmt.setString(2, "T6");
+            stmt.setString(3, "0000000001");
+            stmt.setString(4, "T1");
+            stmt.execute();
+        }
+
+        conn.commit();
+    }
+
+	protected Connection getConnection() throws SQLException {
+		Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+		props.put(ServerCacheClient.HASH_JOIN_SERVER_CACHE_RESEND_PER_SERVER, "true");
+		return DriverManager.getConnection(getUrl(), props);
+	}
+	
+    protected void createIndexes(Connection conn, String virtualName, String realName) throws Exception {
+        if (indexDDL != null && indexDDL.length > 0) {
+            for (String ddl : indexDDL) {
+                String newDDL =  ddl.replace(virtualName, realName);
+                if (!newDDL.equals(ddl)) {
+                    conn.createStatement().execute(newDDL);
+                }
+            }
+        }
+    }
+    
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/ee20a8c9/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinCacheIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinCacheIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinCacheIT.java
new file mode 100644
index 0000000..c49c61f
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinCacheIT.java
@@ -0,0 +1,101 @@
+/*
+ * 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.join;
+
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.fail;
+
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Properties;
+import java.util.Random;
+
+import org.apache.hadoop.hbase.client.Scan;
+import org.apache.hadoop.hbase.coprocessor.ObserverContext;
+import org.apache.hadoop.hbase.coprocessor.RegionCoprocessorEnvironment;
+import org.apache.hadoop.hbase.coprocessor.SimpleRegionObserver;
+import org.apache.hadoop.hbase.regionserver.RegionScanner;
+import org.apache.phoenix.cache.GlobalCache;
+import org.apache.phoenix.cache.TenantCache;
+import org.apache.phoenix.coprocessor.HashJoinCacheNotFoundException;
+import org.apache.phoenix.hbase.index.util.ImmutableBytesPtr;
+import org.apache.phoenix.join.HashJoinInfo;
+import org.apache.phoenix.query.QueryServices;
+import org.apache.phoenix.util.ByteUtil;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.SchemaUtil;
+import org.apache.phoenix.util.TestUtil;
+import org.junit.Test;
+
+public class HashJoinCacheIT extends BaseJoinIT {
+    
+    @Override
+    protected String getTableName(Connection conn, String virtualName) throws Exception {
+        String realName = super.getTableName(conn, virtualName);
+        TestUtil.addCoprocessor(conn, SchemaUtil.normalizeFullTableName(realName), InvalidateHashCache.class);
+        return realName;
+    }
+                
+    @Test
+    public void testExpiredCache() throws Exception {
+        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+        props.setProperty(QueryServices.MAX_SERVER_CACHE_TIME_TO_LIVE_MS_ATTRIB, "1");
+        Connection conn = DriverManager.getConnection(getUrl(), props);
+        String tableName1 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
+        String tableName2 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
+        String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " +
+                tableName1 + " supp RIGHT JOIN " + tableName2 +
+                " item ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
+        try {
+            PreparedStatement statement = conn.prepareStatement(query);
+            ResultSet rs = statement.executeQuery();
+            rs.next();
+            fail("HashJoinCacheNotFoundException was not thrown or incorrectly handled");
+        } catch (HashJoinCacheNotFoundException e) {
+            //Expected exception
+        }
+    }
+
+    public static class InvalidateHashCache extends SimpleRegionObserver {
+        public static Random rand= new Random();
+        public static List<ImmutableBytesPtr> lastRemovedJoinIds=new ArrayList<ImmutableBytesPtr>();
+        @Override
+        public RegionScanner preScannerOpen(final ObserverContext<RegionCoprocessorEnvironment> c, final Scan scan,
+                final RegionScanner s) throws IOException {
+            final HashJoinInfo joinInfo = HashJoinInfo.deserializeHashJoinFromScan(scan);
+            if (joinInfo != null) {
+                TenantCache cache = GlobalCache.getTenantCache(c.getEnvironment(), null);
+                int count = joinInfo.getJoinIds().length;
+                for (int i = 0; i < count; i++) {
+                    ImmutableBytesPtr joinId = joinInfo.getJoinIds()[i];
+                    if (!ByteUtil.contains(lastRemovedJoinIds,joinId)) {
+                        lastRemovedJoinIds.add(joinId);
+                        cache.removeServerCache(joinId);
+                    }
+                }
+            }
+            return s;
+        }
+        
+    }
+}

http://git-wip-us.apache.org/repos/asf/phoenix/blob/ee20a8c9/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
new file mode 100644
index 0000000..76944a6
--- /dev/null
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/join/HashJoinGlobalIndexIT.java
@@ -0,0 +1,399 @@
+/*
+ * 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.join;
+
+import java.util.Collection;
+import java.util.List;
+
+import org.junit.runners.Parameterized.Parameters;
+
+import com.google.common.collect.Lists;
+
+public class HashJoinGlobalIndexIT extends HashJoinIT {
+
+    public HashJoinGlobalIndexIT(String[] indexDDL, String[] plans) {
+        super(indexDDL, plans);
+    }
+
+    @Parameters
+    public static Collection<Object> data() {
+        List<Object> testCases = Lists.newArrayList();
+        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_FULL_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_FULL_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_FULL_NAME + "\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\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_FULL_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" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_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_FULL_NAME + "\n" +
+                "    SERVER FILTER BY FIRST KEY ONLY\n" +
+                "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\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_FULL_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_FULL_NAME + "\n" +
+                "    PARALLEL LEFT-JOIN TABLE 0\n" +
+                "        CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_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_FULL_NAME + "\n" +
+                "    DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
+                "    JOIN-SCANNER 4 ROW LIMIT",
+                /*
+                 * 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_ORDER_TABLE_FULL_NAME + "\n" +
+                "    JOIN-SCANNER 4 ROW LIMIT",
+                /*
+                 * 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 1 OFFSET 2
+                 */
+                "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
+                "    SERVER OFFSET 2\n" +
+                "    SERVER 3 ROW LIMIT\n" +
+                "CLIENT 1 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_FULL_NAME + "\n" +
+                "    JOIN-SCANNER 3 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 1 OFFSET 2
+                 */
+                "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
+                "    SERVER OFFSET 2\n" +
+                "CLIENT 1 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_FULL_NAME + "\n" +
+                "    DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
+                "    JOIN-SCANNER 3 ROW LIMIT",
+                }});
+        return testCases;
+    }
+}


Mime
View raw message