| /* |
| * 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.assertFalse; |
| import static org.junit.Assert.assertTrue; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.util.Collection; |
| import java.util.List; |
| import java.util.Map; |
| import java.util.Properties; |
| |
| import org.apache.phoenix.util.PropertiesUtil; |
| import org.apache.phoenix.util.QueryUtil; |
| import org.junit.Test; |
| import org.junit.experimental.categories.Category; |
| import org.junit.runner.RunWith; |
| import org.junit.runners.Parameterized; |
| import org.junit.runners.Parameterized.Parameters; |
| import org.apache.phoenix.end2end.ParallelStatsDisabledTest; |
| import org.apache.phoenix.thirdparty.com.google.common.collect.Lists; |
| import org.apache.phoenix.thirdparty.com.google.common.collect.Maps; |
| |
| @Category(ParallelStatsDisabledTest.class) |
| @RunWith(Parameterized.class) |
| public class HashJoinLocalIndexIT extends HashJoinIT { |
| |
| private static final Map<String,String> virtualNameToRealNameMap = Maps.newHashMap(); |
| private static final String schemaName = "S_" + generateUniqueName(); |
| |
| @Override |
| protected String getSchemaName() { |
| // run all tests in a single schema |
| return schemaName; |
| } |
| |
| @Override |
| protected Map<String,String> getTableNameMap() { |
| // cache across tests, so that tables and |
| // indexes are not recreated each time |
| return virtualNameToRealNameMap; |
| } |
| |
| |
| public HashJoinLocalIndexIT(String[] indexDDL, String[] plans) { |
| super(indexDDL, plans); |
| } |
| |
| @Parameters(name="HashJoinLocalIndexIT_{index}") // name is used by failsafe as file name in reports |
| public static synchronized Collection<Object> data() { |
| List<Object> testCases = Lists.newArrayList(); |
| testCases.add(new String[][] { |
| { |
| "CREATE LOCAL INDEX " + JOIN_CUSTOMER_INDEX + " ON " + |
| JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)", |
| "CREATE LOCAL INDEX " + JOIN_ITEM_INDEX + " ON " + |
| JOIN_ITEM_TABLE_FULL_NAME + " (name) " + |
| "INCLUDE (price, discount1, discount2, \"supplier_id\", description)", |
| "CREATE LOCAL INDEX " + JOIN_SUPPLIER_INDEX + " 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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testLeftJoinWithAggregation() |
| * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o |
| * LEFT JOIN joinItemTable i ON o.item_id = i.item_id |
| * GROUP BY i.item_id ORDER BY q DESC" |
| */ |
| "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testLeftJoinWithAggregation() |
| * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i |
| * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id |
| * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid |
| */ |
| "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL LEFT-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_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_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1,'T1'] - [1,'T5']\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL LEFT-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME + |
| "(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1,'S1'] - [1,'S5']\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testJoinPlanWithIndex() |
| * SELECT item.item_id, item.name, supp.supplier_id, supp.name |
| * FROM joinItemTable item INNER JOIN joinSupplierTable supp |
| * ON item.supplier_id = supp.supplier_id |
| * WHERE (item.name = 'T1' OR item.name = 'T5') |
| * AND (supp.name = 'S1' OR supp.name = 'S5') |
| */ |
| "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1,'T1'] - [1,'T5']\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + |
| JOIN_SUPPLIER_INDEX_FULL_NAME + "(" + |
| JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1,'S1'] - [1,'S5']\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testJoinWithSkipMergeOptimization() |
| * SELECT s.name FROM joinItemTable i |
| * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000 |
| * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| "CLIENT MERGE SORT\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 RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME + |
| "(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")", |
| /* |
| * testSelfJoin() |
| * SELECT i2.item_id, i1.name FROM joinItemTable i1 |
| * JOIN joinItemTable i2 ON i1.item_id = i2.item_id |
| * ORDER BY i1.item_id |
| */ |
| "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")", |
| /* |
| * testSelfJoin() |
| * SELECT i1.name, i2.name FROM joinItemTable i1 |
| * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id |
| * ORDER BY i1.name, i2.name |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"I1.:item_id\" IN (\"I2.0:supplier_id\")", |
| /* |
| * testStarJoin() |
| * SELECT order_id, c.name, i.name iname, quantity, o.date |
| * FROM joinOrderTable o |
| * JOIN joinCustomerTable c ON o.customer_id = c.customer_id |
| * JOIN joinItemTable i ON o.item_id = i.item_id |
| * ORDER BY order_id |
| */ |
| "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_INDEX_FULL_NAME + |
| "(" + JOIN_CUSTOMER_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT\n" + |
| " PARALLEL INNER-JOIN TABLE 1\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testStarJoin() |
| * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date |
| * FROM joinOrderTable o |
| * JOIN joinCustomerTable c ON o.customer_id = c.customer_id |
| * JOIN joinItemTable i ON o.item_id = i.item_id |
| * ORDER BY order_id |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\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 RANGE SCAN OVER " + |
| JOIN_CUSTOMER_INDEX_FULL_NAME + "(" + |
| JOIN_CUSTOMER_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")", |
| /* |
| * testSubJoin() |
| * SELECT * FROM joinCustomerTable c |
| * INNER JOIN (joinOrderTable o |
| * INNER JOIN (joinSupplierTable s |
| * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id) |
| * ON o.item_id = i.item_id) |
| * ON c.customer_id = o.customer_id |
| * WHERE c.customer_id <= '0000000005' |
| * AND order_id != '000000000000003' |
| * AND i.name != 'T3' |
| * ORDER BY c.customer_id, i.name |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_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 RANGE SCAN OVER " + |
| JOIN_ITEM_INDEX_FULL_NAME + "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY \"NAME\" != 'T3'\n" + |
| " CLIENT MERGE SORT\n" + |
| " PARALLEL LEFT-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testJoinWithSubqueryAndAggregation() |
| * SELECT o.iid, sum(o.quantity) q |
| * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o |
| * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i |
| * ON o.iid = i.item_id |
| * GROUP BY o.iid ORDER BY q DESC |
| */ |
| "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " CLIENT MERGE SORT", |
| /* |
| * testJoinWithSubqueryAndAggregation() |
| * SELECT i.iid, o.q |
| * FROM (SELECT item_id iid FROM joinItemTable) AS i |
| * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o |
| * ON o.iid = i.iid |
| * ORDER BY o.q DESC NULLS LAST, i.iid |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\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 RANGE SCAN OVER " + |
| JOIN_ITEM_INDEX_FULL_NAME + "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY \"NAME\" != 'T3'\n" + |
| " CLIENT MERGE SORT\n" + |
| " PARALLEL LEFT-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_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 RANGE SCAN OVER "+ JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " CLIENT MERGE SORT\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 RANGE SCAN OVER "+ JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " CLIENT MERGE SORT\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 RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| "CLIENT MERGE SORT\n" + |
| "CLIENT 4 ROW LIMIT\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" + |
| " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")\n" + |
| " JOIN-SCANNER 4 ROW LIMIT", |
| /* |
| * testJoinWithOffset() |
| * 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 RANGE SCAN OVER "+ JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " CLIENT MERGE SORT\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", |
| /* |
| * testJoinWithOffset() |
| * 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 RANGE SCAN OVER "+ JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" + |
| " CLIENT MERGE SORT\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", |
| /* |
| * testJoinWithLocalIndex() |
| * SELECT phone, i.name |
| * FROM joinSupplierTable s |
| * JOIN joinItemTable i ON s.supplier_id = i.supplier_id |
| * WHERE s.name = 'S1' AND i.name < 'T6' |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME + |
| "(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1,'S1']\n" + |
| " SERVER MERGE [0.PHONE]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1,*] - [1,'T6']\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"S.:supplier_id\" IN (\"I.0:supplier_id\")", |
| |
| /* |
| * testJoinWithLocalIndex() |
| * SELECT phone, max(i.name) |
| * FROM joinSupplierTable s |
| * JOIN joinItemTable i ON s."supplier_id" = i."supplier_id" |
| * WHERE s.name = 'S1' AND i.name < 'T6' |
| * GROUP BY phone |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME + |
| "(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1,'S1']\n" + |
| " SERVER MERGE [0.PHONE]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"S.PHONE\"]\n" + |
| "CLIENT MERGE SORT\n" + |
| " PARALLEL INNER-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1,*] - [1,'T6']\n" + |
| " CLIENT MERGE SORT\n" + |
| " DYNAMIC SERVER FILTER BY \"S.:supplier_id\" IN (\"I.0:supplier_id\")", |
| |
| /* |
| * testJoinWithLocalIndex() |
| * SELECT max(phone), max(i.name) |
| * FROM joinSupplierTable s |
| * LEFT JOIN joinItemTable i ON s."supplier_id" = i."supplier_id" AND i.name < 'T6' |
| * WHERE s.name <= 'S3' |
| */ |
| "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME + |
| "(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1,*] - [1,'S3']\n" + |
| " SERVER MERGE [0.PHONE]\n" + |
| " SERVER FILTER BY FIRST KEY ONLY\n" + |
| " SERVER AGGREGATE INTO SINGLE ROW\n" + |
| " PARALLEL LEFT-JOIN TABLE 0\n" + |
| " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME + |
| "(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1,*] - [1,'T6']\n" + |
| " CLIENT MERGE SORT", |
| }}); |
| return testCases; |
| } |
| |
| @Test |
| public void testJoinWithLocalIndex() throws Exception { |
| Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); |
| Connection conn = DriverManager.getConnection(getUrl(), props); |
| try { |
| String query = "select phone, i.name from " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s join " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i on s.\"supplier_id\" = i.\"supplier_id\" where s.name = 'S1' and i.name < 'T6'"; |
| System.out.println("1)\n" + query); |
| PreparedStatement statement = conn.prepareStatement(query); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertEquals(rs.getString(1), "888-888-1111"); |
| assertTrue (rs.next()); |
| assertEquals(rs.getString(1), "888-888-1111"); |
| assertFalse(rs.next()); |
| rs = conn.createStatement().executeQuery("EXPLAIN " + query); |
| assertPlansEqual(plans[24], QueryUtil.getExplainPlan(rs)); |
| |
| query = "select phone, max(i.name) from " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s join " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i on s.\"supplier_id\" = i.\"supplier_id\" where s.name = 'S1' and i.name < 'T6' group by phone"; |
| statement = conn.prepareStatement(query); |
| rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertEquals(rs.getString(1), "888-888-1111"); |
| assertEquals(rs.getString(2), "T2"); |
| assertFalse(rs.next()); |
| rs = conn.createStatement().executeQuery("EXPLAIN " + query); |
| assertPlansEqual(plans[25], QueryUtil.getExplainPlan(rs)); |
| |
| query = "select max(phone), max(i.name) from " + getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME) + " s left join " + getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME) + " i on s.\"supplier_id\" = i.\"supplier_id\" and i.name < 'T6' where s.name <= 'S3'"; |
| statement = conn.prepareStatement(query); |
| rs = statement.executeQuery(); |
| assertTrue (rs.next()); |
| assertEquals(rs.getString(1), "888-888-3333"); |
| assertEquals(rs.getString(2), "T4"); |
| assertFalse(rs.next()); |
| rs = conn.createStatement().executeQuery("EXPLAIN " + query); |
| assertPlansEqual(plans[26], QueryUtil.getExplainPlan(rs)); |
| } finally { |
| conn.close(); |
| } |
| } |
| } |
