http://git-wip-us.apache.org/repos/asf/phoenix/blob/e409c0e7/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD new file mode 100644 index 0000000..a03204a --- /dev/null +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java~HEAD @@ -0,0 +1,3818 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.phoenix.end2end; + +import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_DISPLAY_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_CUSTOMER_TABLE_FULL_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_DISPLAY_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_ITEM_TABLE_FULL_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_DISPLAY_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_ORDER_TABLE_FULL_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_SCHEMA; +import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_DISPLAY_NAME; +import static org.apache.phoenix.util.TestUtil.JOIN_SUPPLIER_TABLE_FULL_NAME; +import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.assertNotNull; +import static org.junit.Assert.assertNull; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + +import java.sql.Connection; +import java.sql.Date; +import java.sql.DriverManager; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Timestamp; +import java.text.SimpleDateFormat; +import java.util.Collection; +import java.util.List; +import java.util.Map; +import java.util.Properties; + +import org.apache.phoenix.exception.SQLExceptionCode; +import org.apache.phoenix.query.QueryServices; +import org.apache.phoenix.schema.TableAlreadyExistsException; +import org.apache.phoenix.util.MetaDataUtil; +import org.apache.phoenix.util.PropertiesUtil; +import org.apache.phoenix.util.QueryUtil; +import org.apache.phoenix.util.ReadOnlyProps; +import org.junit.Before; +import org.junit.BeforeClass; +import org.junit.Test; +import org.junit.runner.RunWith; +import org.junit.runners.Parameterized; +import org.junit.runners.Parameterized.Parameters; + +import com.google.common.collect.Lists; +import com.google.common.collect.Maps; + +@RunWith(Parameterized.class) +public class HashJoinIT extends BaseHBaseManagedTimeIT { + + private SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); + private String[] indexDDL; + private String[] plans; + + public HashJoinIT(String[] indexDDL, String[] plans) { + this.indexDDL = indexDDL; + this.plans = plans; + } + + @BeforeClass + @Shadower(classBeingShadowed = BaseHBaseManagedTimeIT.class) + public static void doSetup() throws Exception { + Map<String,String> props = Maps.newHashMapWithExpectedSize(3); + // Forces server cache to be used + props.put(QueryServices.INDEX_MUTATE_BATCH_SIZE_THRESHOLD_ATTRIB, Integer.toString(2)); + // Must update config before starting server + setUpTestDriver(new ReadOnlyProps(props.entrySet().iterator())); + } + + @Before + public void initTable() throws Exception { + initJoinTableValues(getUrl(), null, null); + if (indexDDL != null && indexDDL.length > 0) { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + for (String ddl : indexDDL) { + try { + conn.createStatement().execute(ddl); + } catch (TableAlreadyExistsException e) { + } + } + conn.close(); + } + } + + @Parameters + public static Collection<Object> data() { + List<Object> testCases = Lists.newArrayList(); + testCases.add(new String[][] { + {}, { + /* + * testLeftJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME, + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC" + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testJoinWithWildcard() + * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp + * ON joinItemTable.supplier_id = supp.supplier_id + * ORDER BY item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item LEFT JOIN joinSupplierTable supp + * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1) + * AND (supp.name BETWEEN 'S1' AND 'S5') + * WHERE item.name BETWEEN 'T1' AND 'T5' + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY (NAME >= 'T1' AND NAME <= 'T5')\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY (NAME >= 'S1' AND NAME <= 'S5')", + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item INNER JOIN joinSupplierTable supp + * ON item.supplier_id = supp.supplier_id + * WHERE (item.name = 'T1' OR item.name = 'T5') + * AND (supp.name = 'S1' OR supp.name = 'S5') + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY (NAME = 'T1' OR NAME = 'T5')\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY (NAME = 'S1' OR NAME = 'S5')", + /* + * testJoinWithSkipMergeOptimization() + * SELECT s.name FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000 + * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY QUANTITY < 5000\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")", + /* + * testSelfJoin() + * SELECT i2.item_id, i1.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.item_id + * ORDER BY i1.item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.item_id\")", + /* + * testSelfJoin() + * SELECT i1.name, i2.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id + * ORDER BY i1.name, i2.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER SORTED BY [I1.NAME, I2.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.supplier_id\")", + /* + * testStarJoin() + * SELECT order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME, + /* + * testStarJoin() + * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER SORTED BY [\"O.order_id\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")", + /* + * testSubJoin() + * SELECT * FROM joinCustomerTable c + * INNER JOIN (joinOrderTable o + * INNER JOIN (joinSupplierTable s + * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id) + * ON o.item_id = i.item_id) + * ON c.customer_id = o.customer_id + * WHERE c.customer_id <= '0000000005' + * AND order_id != '000000000000003' + * AND i.name != 'T3' + * ORDER BY c.customer_id, i.name + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [\"C.customer_id\", I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY NAME != 'T3'\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i + * ON o.item_id = i.iid + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME, + /* + * testJoinWithSubqueryAndAggregation() + * SELECT o.iid, sum(o.quantity) q + * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o + * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i + * ON o.iid = i.item_id + * GROUP BY o.iid ORDER BY q DESC + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid FROM joinItemTable) AS i + * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * ON o.iid = i.iid + * ORDER BY o.q DESC NULLS LAST, i.iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * JOIN (SELECT item_id iid FROM joinItemTable) AS i + * ON o.iid = i.iid + * ORDER BY o.q DESC, i.iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC, I.IID]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testNestedSubqueries() + * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c + * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate, + * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription, + * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id + * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o + * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription, + * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id + * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s + * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i + * ON i.sid = s.sid) as qi + * ON o.iid = qi.iiid) as qo + * ON c.cid = qo.ocid + * WHERE c.cid <= '0000000005' + * AND qo.ooid != '000000000000003' + * AND qo.iname != 'T3' + * ORDER BY c.cid, qo.iname + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [C.CID, QO.INAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY NAME != 'T3'\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " SERVER 4 ROW LIMIT\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.supplier_id\")\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))", + /* + * testJoinWithSetMaxRows() + * statement.setMaxRows(4); + * SELECT order_id, i.name, quantity FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id; + * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i + * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o + * ON o.item_id = i.item_id; + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"I.item_id\" IN (\"O.item_id\")\n" + + " JOIN-SCANNER 4 ROW LIMIT", + }}); + testCases.add(new String[][] { + { + "CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)", + "CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)", + "CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)" + }, { + /* + * testLeftJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC" + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testJoinWithWildcard() + * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp + * ON joinItemTable.supplier_id = supp.supplier_id + * ORDER BY item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item LEFT JOIN joinSupplierTable supp + * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1) + * AND (supp.name BETWEEN 'S1' AND 'S5') + * WHERE item.name BETWEEN 'T1' AND 'T5' + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item INNER JOIN joinSupplierTable supp + * ON item.supplier_id = supp.supplier_id + * WHERE (item.name = 'T1' OR item.name = 'T5') + * AND (supp.name = 'S1' OR supp.name = 'S5') + */ + "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testJoinWithSkipMergeOptimization() + * SELECT s.name FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000 + * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY QUANTITY < 5000\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testSelfJoin() + * SELECT i2.item_id, i1.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.item_id + * ORDER BY i1.item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")", + /* + * testSelfJoin() + * SELECT i1.name, i2.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id + * ORDER BY i1.name, i2.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item", + /* + * testStarJoin() + * SELECT order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testStarJoin() + * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [\"O.order_id\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testSubJoin() + * SELECT * FROM joinCustomerTable c + * INNER JOIN (joinOrderTable o + * INNER JOIN (joinSupplierTable s + * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id) + * ON o.item_id = i.item_id) + * ON c.customer_id = o.customer_id + * WHERE c.customer_id <= '0000000005' + * AND order_id != '000000000000003' + * AND i.name != 'T3' + * ORDER BY c.customer_id, i.name + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [\"C.customer_id\", \"I.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY \"NAME\" != 'T3'\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i + * ON o.item_id = i.iid + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT o.iid, sum(o.quantity) q + * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o + * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i + * ON o.iid = i.item_id + * GROUP BY o.iid ORDER BY q DESC + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid FROM joinItemTable) AS i + * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * ON o.iid = i.iid + * ORDER BY o.q DESC NULLS LAST, i.iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * JOIN (SELECT item_id iid FROM joinItemTable) AS i + * ON o.iid = i.iid + * ORDER BY o.q DESC, i.iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC, I.IID]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testNestedSubqueries() + * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c + * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate, + * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription, + * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id + * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o + * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription, + * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id + * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s + * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i + * ON i.sid = s.sid) as qi + * ON o.iid = qi.iiid) as qo + * ON c.cid = qo.ocid + * WHERE c.cid <= '0000000005' + * AND qo.ooid != '000000000000003' + * AND qo.iname != 'T3' + * ORDER BY c.cid, qo.iname + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [C.CID, QO.INAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY \"NAME\" != 'T3'\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " SERVER 4 ROW LIMIT\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" + + " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" + + " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))", + /* + * testJoinWithSetMaxRows() + * statement.setMaxRows(4); + * SELECT order_id, i.name, quantity FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id; + * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i + * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o + * ON o.item_id = i.item_id; + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".OrderTable\n" + + " JOIN-SCANNER 4 ROW LIMIT", + }}); + testCases.add(new String[][] { + { + "CREATE LOCAL INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)", + "CREATE LOCAL INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)", + "CREATE LOCAL INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)" + }, { + /* + * testLeftJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * LEFT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC" + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testLeftJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testRightJoinWithAggregation() + * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o + * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id + * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME, + /* + * testJoinWithWildcard() + * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp + * ON joinItemTable.supplier_id = supp.supplier_id + * ORDER BY item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item LEFT JOIN joinSupplierTable supp + * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1) + * AND (supp.name BETWEEN 'S1' AND 'S5') + * WHERE item.name BETWEEN 'T1' AND 'T5' + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testJoinPlanWithIndex() + * SELECT item.item_id, item.name, supp.supplier_id, supp.name + * FROM joinItemTable item INNER JOIN joinSupplierTable supp + * ON item.supplier_id = supp.supplier_id + * WHERE (item.name = 'T1' OR item.name = 'T5') + * AND (supp.name = 'S1' OR supp.name = 'S5') + */ + "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768,'T1'] - [-32768,'T5']\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME +" [-32768,'S1'] - [-32768,'S5']\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSkipMergeOptimization() + * SELECT s.name FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000 + * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY QUANTITY < 5000\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")", + /* + * testSelfJoin() + * SELECT i2.item_id, i1.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.item_id + * ORDER BY i1.item_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")", + /* + * testSelfJoin() + * SELECT i1.name, i2.name FROM joinItemTable i1 + * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id + * ORDER BY i1.name, i2.name + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY \"I1.:item_id\" IN (\"I2.0:supplier_id\")", + /* + * testStarJoin() + * SELECT order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 1\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testStarJoin() + * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date + * FROM joinOrderTable o + * JOIN joinCustomerTable c ON o.customer_id = c.customer_id + * JOIN joinItemTable i ON o.item_id = i.item_id + * ORDER BY order_id + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [\"O.order_id\"]\n"+ + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + "" + JOIN_CUSTOMER_TABLE_DISPLAY_NAME+" [-32768]\n"+ + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")", + /* + * testSubJoin() + * SELECT * FROM joinCustomerTable c + * INNER JOIN (joinOrderTable o + * INNER JOIN (joinSupplierTable s + * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id) + * ON o.item_id = i.item_id) + * ON c.customer_id = o.customer_id + * WHERE c.customer_id <= '0000000005' + * AND order_id != '000000000000003' + * AND i.name != 'T3' + * ORDER BY c.customer_id, i.name + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [\"C.customer_id\", \"I.0:NAME\"]\n"+ + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+ JOIN_ITEM_TABLE_DISPLAY_NAME +" [-32768]\n" + + " SERVER FILTER BY \"NAME\" != 'T3'\n" + + " CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.name, sum(quantity) FROM joinOrderTable o + * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i + * ON o.item_id = i.iid + * GROUP BY i.name ORDER BY i.name + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME+" [-32768]\n"+ + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT o.iid, sum(o.quantity) q + * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o + * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i + * ON o.iid = i.item_id + * GROUP BY o.iid ORDER BY q DESC + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" + + "CLIENT MERGE SORT\n" + + "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" + + " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid FROM joinItemTable) AS i + * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * ON o.iid = i.iid + * ORDER BY o.q DESC NULLS LAST, i.iid + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n"+ + "CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithSubqueryAndAggregation() + * SELECT i.iid, o.q + * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o + * JOIN (SELECT item_id iid FROM joinItemTable) AS i + * ON o.iid = i.iid + * ORDER BY o.q DESC, i.iid + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + " SERVER SORTED BY [O.Q DESC, I.IID]\n"+ + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" + + " CLIENT MERGE SORT", + /* + * testNestedSubqueries() + * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c + * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate, + * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription, + * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id + * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o + * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription, + * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id + * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s + * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i + * ON i.sid = s.sid) as qi + * ON o.iid = qi.iiid) as qo + * ON c.cid = qo.ocid + * WHERE c.cid <= '0000000005' + * AND qo.ooid != '000000000000003' + * AND qo.iname != 'T3' + * ORDER BY c.cid, qo.iname + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_DISPLAY_NAME + " [*] - ['0000000005']\n" + + " SERVER SORTED BY [C.CID, QO.INAME]\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " SERVER FILTER BY \"order_id\" != '000000000000003'\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY \"NAME\" != 'T3'\n" + + " CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME, + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + " SERVER 4 ROW LIMIT\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL LEFT-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " CLIENT MERGE SORT\n" + + " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithLimit() + * SELECT order_id, i.name, s.name, s.address, quantity + * FROM joinSupplierTable s + * JOIN joinItemTable i ON i.supplier_id = s.supplier_id + * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4 + */ + "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_DISPLAY_NAME + "\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY RANGE SCAN OVER "+ MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX +""+JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" + + " JOIN-SCANNER 4 ROW LIMIT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY LHS.COL0 IN (RHS.COL2)", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1) IN ((RHS.COL1, RHS.COL2))", + /* + * testJoinWithKeyRangeOptimization() + * SELECT lhs.col0, lhs.col1, lhs.col2, rhs.col0, rhs.col1, rhs.col2 + * FROM TEMP_TABLE_COMPOSITE_PK lhs + * JOIN TEMP_TABLE_COMPOSITE_PK rhs ON lhs.col0 = rhs.col1 AND lhs.col2 = rhs.col3 - 1 AND lhs.col1 = rhs.col2 + */ + "CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + "CLIENT MERGE SORT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 4-WAY FULL SCAN OVER TEMP_TABLE_COMPOSITE_PK\n" + + " CLIENT MERGE SORT\n" + + " DYNAMIC SERVER FILTER BY (LHS.COL0, LHS.COL1, LHS.COL2) IN ((RHS.COL1, RHS.COL2, TO_INTEGER((RHS.COL3 - 1))))", + /* + * testJoinWithSetMaxRows() + * statement.setMaxRows(4); + * SELECT order_id, i.name, quantity FROM joinItemTable i + * JOIN joinOrderTable o ON o.item_id = i.item_id; + * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i + * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o + * ON o.item_id = i.item_id; + */ + "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + MetaDataUtil.LOCAL_INDEX_TABLE_PREFIX + JOIN_ITEM_TABLE_DISPLAY_NAME + " [-32768]\n" + + " SERVER FILTER BY FIRST KEY ONLY\n" + + "CLIENT MERGE SORT\n" + + "CLIENT 4 ROW LIMIT\n" + + " PARALLEL INNER-JOIN TABLE 0\n" + + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_DISPLAY_NAME + "\n" + + " DYNAMIC SERVER FILTER BY \"I.:item_id\" IN (\"O.item_id\")\n" + + " JOIN-SCANNER 4 ROW LIMIT", + }}); + return testCases; + } + + + @Test + public void testDefaultJoin() throws Exception { + String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\""; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000001"); + assertEquals(rs.getString(2), "T1"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000002"); + assertEquals(rs.getString(2), "T2"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000003"); + assertEquals(rs.getString(2), "T3"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000004"); + assertEquals(rs.getString(2), "T4"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000005"); + assertEquals(rs.getString(2), "T5"); + assertEquals(rs.getString(3), "0000000005"); + assertEquals(rs.getString(4), "S5"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000006"); + assertEquals(rs.getString(2), "T6"); + assertEquals(rs.getString(3), "0000000006"); + assertEquals(rs.getString(4), "S6"); + + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + @Test + public void testInnerJoin() throws Exception { + String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item INNER JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\""; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + PreparedStatement statement = conn.prepareStatement(query); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000001"); + assertEquals(rs.getString(2), "T1"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertEquals(1, rs.getInt(5)); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000002"); + assertEquals(rs.getString(2), "T2"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertEquals(2, rs.getInt(5)); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000003"); + assertEquals(rs.getString(2), "T3"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertEquals(3, rs.getInt(5)); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000004"); + assertEquals(rs.getString(2), "T4"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertEquals(4, rs.getInt(5)); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000005"); + assertEquals(rs.getString(2), "T5"); + assertEquals(rs.getString(3), "0000000005"); + assertEquals(rs.getString(4), "S5"); + assertEquals(5, rs.getInt(5)); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000006"); + assertEquals(rs.getString(2), "T6"); + assertEquals(rs.getString(3), "0000000006"); + assertEquals(rs.getString(4), "S6"); + assertEquals(6, rs.getInt(5)); + + assertFalse(rs.next()); + } finally { + conn.close(); + } + } + + @Test + public void testLeftJoin() throws Exception { + String query[] = new String[3]; + query[0] = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\""; + query[1] = "SELECT " + JOIN_ITEM_TABLE_FULL_NAME + ".\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".\"supplier_id\" ORDER BY \"item_id\""; + query[2] = "SELECT item.\"item_id\", " + JOIN_ITEM_TABLE_FULL_NAME + ".name, supp.\"supplier_id\", " + JOIN_SUPPLIER_TABLE_FULL_NAME + ".name, next value for my.seq FROM " + JOIN_ITEM_TABLE_FULL_NAME + " item LEFT JOIN " + JOIN_SUPPLIER_TABLE_FULL_NAME + " supp ON " + JOIN_ITEM_TABLE_FULL_NAME + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\""; + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + try { + for (int i = 0; i < query.length; i++) { + PreparedStatement statement = conn.prepareStatement(query[i]); + ResultSet rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000001"); + assertEquals(rs.getString(2), "T1"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000002"); + assertEquals(rs.getString(2), "T2"); + assertEquals(rs.getString(3), "0000000001"); + assertEquals(rs.getString(4), "S1"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000003"); + assertEquals(rs.getString(2), "T3"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000004"); + assertEquals(rs.getString(2), "T4"); + assertEquals(rs.getString(3), "0000000002"); + assertEquals(rs.getString(4), "S2"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000005"); + assertEquals(rs.getString(2), "T5"); + assertEquals(rs.getString(3), "0000000005"); + assertEquals(rs.getString(4), "S5"); + assertTrue (rs.next()); + assertEquals(rs.getString(1), "0000000006"); + assertEquals(rs.getString(2), "T6"); + assertEquals(rs.getString(3), "0000000006"); + a
<TRUNCATED>
