You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ja...@apache.org on 2017/09/29 02:31:14 UTC
[16/26] phoenix git commit: PHOENIX-4246 Breakup join related tests
into several integration tests so as not to create too many tables in one
test
http://git-wip-us.apache.org/repos/asf/phoenix/blob/d5d8378d/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
----------------------------------------------------------------------
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
deleted file mode 100644
index edab319..0000000
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java
+++ /dev/null
@@ -1,3448 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one
- * or more contributor license agreements. See the NOTICE file
- * distributed with this work for additional information
- * regarding copyright ownership. The ASF licenses this file
- * to you under the Apache License, Version 2.0 (the
- * "License"); you may not use this file except in compliance
- * with the License. You may obtain a copy of the License at
- *
- * http://www.apache.org/licenses/LICENSE-2.0
- *
- * Unless required by applicable law or agreed to in writing, software
- * distributed under the License is distributed on an "AS IS" BASIS,
- * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
- * See the License for the specific language governing permissions and
- * limitations under the License.
- */
-package org.apache.phoenix.end2end;
-
-import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertNotNull;
-import static org.junit.Assert.assertNull;
-import static org.junit.Assert.assertTrue;
-import static org.junit.Assert.fail;
-
-import java.io.IOException;
-import java.sql.Connection;
-import java.sql.Date;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Timestamp;
-import java.util.ArrayList;
-import java.util.Collection;
-import java.util.Collections;
-import java.util.List;
-import java.util.Map;
-import java.util.Properties;
-import java.util.Random;
-
-import org.apache.hadoop.hbase.HConstants;
-import org.apache.hadoop.hbase.client.Scan;
-import org.apache.hadoop.hbase.coprocessor.ObserverContext;
-import org.apache.hadoop.hbase.coprocessor.RegionCoprocessorEnvironment;
-import org.apache.hadoop.hbase.coprocessor.SimpleRegionObserver;
-import org.apache.hadoop.hbase.regionserver.RegionScanner;
-import org.apache.phoenix.cache.GlobalCache;
-import org.apache.phoenix.cache.ServerCacheClient;
-import org.apache.phoenix.cache.TenantCache;
-import org.apache.phoenix.exception.SQLExceptionCode;
-import org.apache.phoenix.hbase.index.util.ImmutableBytesPtr;
-import org.apache.phoenix.join.HashJoinInfo;
-import org.apache.phoenix.query.QueryServices;
-import org.apache.phoenix.util.ByteUtil;
-import org.apache.phoenix.util.PropertiesUtil;
-import org.apache.phoenix.util.QueryUtil;
-import org.apache.phoenix.util.ReadOnlyProps;
-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 BaseJoinIT {
-
-
- public HashJoinIT(String[] indexDDL, String[] plans) {
- super(indexDDL, plans);
- }
-
- @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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.item_id\"]\n" +
- "CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_NAME + "\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME,
- /*
- * testRightJoinWithAggregation()
- * SELECT i.name, sum(quantity) FROM joinOrderTable o
- * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
- * GROUP BY i.name ORDER BY i.name
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_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 FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_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_FULL_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_FULL_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_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY QUANTITY < 5000\n" +
- " PARALLEL INNER-JOIN TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_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_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_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_FULL_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_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_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_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_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_FULL_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_FULL_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_FULL_NAME + "\n" +
- " SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT i.name, sum(quantity) FROM joinOrderTable o
- * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i
- * ON o.item_id = i.iid
- * GROUP BY i.name ORDER BY i.name
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
- "CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
- " CLIENT MERGE SORT",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT i.iid, o.q
- * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
- * JOIN (SELECT item_id iid FROM joinItemTable) AS i
- * ON o.iid = i.iid
- * ORDER BY o.q DESC, i.iid
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
- " CLIENT MERGE SORT",
- /*
- * testNestedSubqueries()
- * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c
- * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate,
- * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription,
- * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id
- * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o
- * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription,
- * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id
- * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s
- * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i
- * ON i.sid = s.sid) as qi
- * ON o.iid = qi.iiid) as qo
- * ON c.cid = qo.ocid
- * WHERE c.cid <= '0000000005'
- * AND qo.ooid != '000000000000003'
- * AND qo.iname != 'T3'
- * ORDER BY c.cid, qo.iname
- */
- "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + " [*] - ['0000000005']\n" +
- " SERVER SORTED BY [C.CID, QO.INAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY NAME != 'T3'\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME,
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
- */
- "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " SERVER 4 ROW LIMIT\n" +
- "CLIENT 4 ROW LIMIT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " JOIN-SCANNER 4 ROW LIMIT",
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- "CLIENT 4 ROW LIMIT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ITEM_TABLE_FULL_NAME + "\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.supplier_id\")\n" +
- " JOIN-SCANNER 4 ROW LIMIT",
- /*
- * testJoinWithSetMaxRows()
- * statement.setMaxRows(4);
- * SELECT order_id, i.name, quantity FROM joinItemTable i
- * JOIN joinOrderTable o ON o.item_id = i.item_id;
- * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i
- * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o
- * ON o.item_id = i.item_id;
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\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 FULL SCAN OVER "+ JOIN_ITEM_TABLE_FULL_NAME + "\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 FULL SCAN OVER "+ JOIN_ITEM_TABLE_FULL_NAME + "\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.supplier_id\")\n" +
- " JOIN-SCANNER 3 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_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testLeftJoinWithAggregation()
- * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
- * LEFT JOIN joinItemTable i ON o.item_id = i.item_id
- * GROUP BY i.item_id ORDER BY q DESC"
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"I.:item_id\"]\n" +
- "CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testLeftJoinWithAggregation()
- * SELECT i.item_id iid, sum(quantity) q FROM joinItemTable i
- * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id
- * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME,
- /*
- * testRightJoinWithAggregation()
- * SELECT i.name, sum(quantity) FROM joinOrderTable o
- * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
- * GROUP BY i.name ORDER BY i.name
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.0:NAME\"]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME,
- /*
- * testRightJoinWithAggregation()
- * SELECT i.item_id iid, sum(quantity) q FROM joinOrderTable o
- * RIGHT JOIN joinItemTable i ON o.item_id = i.item_id
- * GROUP BY i.item_id ORDER BY q DESC NULLS LAST, iid
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"I.item_id\"]\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC NULLS LAST, \"I.item_id\"]\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME,
- /*
- * testJoinWithWildcard()
- * SELECT * FROM joinItemTable LEFT JOIN joinSupplierTable supp
- * ON joinItemTable.supplier_id = supp.supplier_id
- * ORDER BY item_id
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME,
- /*
- * testJoinPlanWithIndex()
- * SELECT item.item_id, item.name, supp.supplier_id, supp.name
- * FROM joinItemTable item LEFT JOIN joinSupplierTable supp
- * ON substr(item.name, 2, 1) = substr(supp.name, 2, 1)
- * AND (supp.name BETWEEN 'S1' AND 'S5')
- * WHERE item.name BETWEEN 'T1' AND 'T5'
- */
- "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testJoinPlanWithIndex()
- * SELECT item.item_id, item.name, supp.supplier_id, supp.name
- * FROM joinItemTable item INNER JOIN joinSupplierTable supp
- * ON item.supplier_id = supp.supplier_id
- * WHERE (item.name = 'T1' OR item.name = 'T5')
- * AND (supp.name = 'S1' OR supp.name = 'S5')
- */
- "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_item ['T1'] - ['T5']\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER " + JOIN_SCHEMA + ".idx_supplier ['S1'] - ['S5']\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testJoinWithSkipMergeOptimization()
- * SELECT s.name FROM joinItemTable i
- * JOIN joinOrderTable o ON o.item_id = i.item_id AND quantity < 5000
- * JOIN joinSupplierTable s ON i.supplier_id = s.supplier_id
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY QUANTITY < 5000\n" +
- " PARALLEL INNER-JOIN TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testSelfJoin()
- * SELECT i2.item_id, i1.name FROM joinItemTable i1
- * JOIN joinItemTable i2 ON i1.item_id = i2.item_id
- * ORDER BY i1.item_id
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " DYNAMIC SERVER FILTER BY \"I1.item_id\" IN (\"I2.:item_id\")",
- /*
- * testSelfJoin()
- * SELECT i1.name, i2.name FROM joinItemTable i1
- * JOIN joinItemTable i2 ON i1.item_id = i2.supplier_id
- * ORDER BY i1.name, i2.name
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"I1.0:NAME\", \"I2.0:NAME\"]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item",
- /*
- * testStarJoin()
- * SELECT order_id, c.name, i.name iname, quantity, o.date
- * FROM joinOrderTable o
- * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
- * JOIN joinItemTable i ON o.item_id = i.item_id
- * ORDER BY order_id
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " PARALLEL INNER-JOIN TABLE 1\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testStarJoin()
- * SELECT (*NO_STAR_JOIN*) order_id, c.name, i.name iname, quantity, o.date
- * FROM joinOrderTable o
- * JOIN joinCustomerTable c ON o.customer_id = c.customer_id
- * JOIN joinItemTable i ON o.item_id = i.item_id
- * ORDER BY order_id
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [\"O.order_id\"]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testSubJoin()
- * SELECT * FROM joinCustomerTable c
- * INNER JOIN (joinOrderTable o
- * INNER JOIN (joinSupplierTable s
- * RIGHT JOIN joinItemTable i ON i.supplier_id = s.supplier_id)
- * ON o.item_id = i.item_id)
- * ON c.customer_id = o.customer_id
- * WHERE c.customer_id <= '0000000005'
- * AND order_id != '000000000000003'
- * AND i.name != 'T3'
- * ORDER BY c.customer_id, i.name
- */
- "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + " [*] - ['0000000005']\n" +
- " SERVER SORTED BY [\"C.customer_id\", \"I.0:NAME\"]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY \"NAME\" != 'T3'\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " DYNAMIC SERVER FILTER BY \"C.customer_id\" IN (\"O.customer_id\")",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT i.name, sum(quantity) FROM joinOrderTable o
- * LEFT JOIN (SELECT name, item_id iid FROM joinItemTable) AS i
- * ON o.item_id = i.iid
- * GROUP BY i.name ORDER BY i.name
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [I.NAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT o.iid, sum(o.quantity) q
- * FROM (SELECT item_id iid, quantity FROM joinOrderTable) AS o
- * LEFT JOIN (SELECT item_id FROM joinItemTable) AS i
- * ON o.iid = i.item_id
- * GROUP BY o.iid ORDER BY q DESC
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [O.IID]\n" +
- "CLIENT MERGE SORT\n" +
- "CLIENT SORTED BY [SUM(O.QUANTITY) DESC]\n" +
- " PARALLEL LEFT-JOIN TABLE 0 (SKIP MERGE)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT i.iid, o.q
- * FROM (SELECT item_id iid FROM joinItemTable) AS i
- * LEFT JOIN (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
- * ON o.iid = i.iid
- * ORDER BY o.q DESC NULLS LAST, i.iid
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [O.Q DESC NULLS LAST, I.IID]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
- " CLIENT MERGE SORT",
- /*
- * testJoinWithSubqueryAndAggregation()
- * SELECT i.iid, o.q
- * FROM (SELECT item_id iid, sum(quantity) q FROM joinOrderTable GROUP BY item_id) AS o
- * JOIN (SELECT item_id iid FROM joinItemTable) AS i
- * ON o.iid = i.iid
- * ORDER BY o.q DESC, i.iid
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- " SERVER SORTED BY [O.Q DESC, I.IID]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
- " CLIENT MERGE SORT",
- /*
- * testNestedSubqueries()
- * SELECT * FROM (SELECT customer_id cid, name, phone, address, loc_id, date FROM joinCustomerTable) AS c
- * INNER JOIN (SELECT o.oid ooid, o.cid ocid, o.iid oiid, o.price * o.quantity, o.date odate,
- * qi.iiid iiid, qi.iname iname, qi.iprice iprice, qi.idiscount1 idiscount1, qi.idiscount2 idiscount2, qi.isid isid, qi.idescription idescription,
- * qi.ssid ssid, qi.sname sname, qi.sphone sphone, qi.saddress saddress, qi.sloc_id sloc_id
- * FROM (SELECT item_id iid, customer_id cid, order_id oid, price, quantity, date FROM joinOrderTable) AS o
- * INNER JOIN (SELECT i.iid iiid, i.name iname, i.price iprice, i.discount1 idiscount1, i.discount2 idiscount2, i.sid isid, i.description idescription,
- * s.sid ssid, s.name sname, s.phone sphone, s.address saddress, s.loc_id sloc_id
- * FROM (SELECT supplier_id sid, name, phone, address, loc_id FROM joinSupplierTable) AS s
- * RIGHT JOIN (SELECT item_id iid, name, price, discount1, discount2, supplier_id sid, description FROM joinItemTable) AS i
- * ON i.sid = s.sid) as qi
- * ON o.iid = qi.iiid) as qo
- * ON c.cid = qo.ocid
- * WHERE c.cid <= '0000000005'
- * AND qo.ooid != '000000000000003'
- * AND qo.iname != 'T3'
- * ORDER BY c.cid, qo.iname
- */
- "CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + " [*] - ['0000000005']\n" +
- " SERVER SORTED BY [C.CID, QO.INAME]\n" +
- "CLIENT MERGE SORT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " SERVER FILTER BY \"order_id\" != '000000000000003'\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY \"NAME\" != 'T3'\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME,
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
- */
- "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " SERVER 4 ROW LIMIT\n" +
- "CLIENT 4 ROW LIMIT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " JOIN-SCANNER 4 ROW LIMIT",
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 4
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- "CLIENT 4 ROW LIMIT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
- " JOIN-SCANNER 4 ROW LIMIT",
- /*
- * testJoinWithSetMaxRows()
- * statement.setMaxRows(4);
- * SELECT order_id, i.name, quantity FROM joinItemTable i
- * JOIN joinOrderTable o ON o.item_id = i.item_id;
- * SELECT o.order_id, i.name, o.quantity FROM joinItemTable i
- * JOIN (SELECT order_id, item_id, quantity FROM joinOrderTable) o
- * ON o.item_id = i.item_id;
- */
- "CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " SERVER FILTER BY FIRST KEY ONLY\n" +
- "CLIENT 4 ROW LIMIT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " JOIN-SCANNER 4 ROW LIMIT",
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * LEFT JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * LEFT JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 1 OFFSET 2
- */
- "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " SERVER OFFSET 2\n" +
- " SERVER 3 ROW LIMIT\n" +
- "CLIENT 1 ROW LIMIT\n" +
- " PARALLEL LEFT-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL LEFT-JOIN TABLE 1(DELAYED EVALUATION)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " JOIN-SCANNER 3 ROW LIMIT",
- /*
- * testJoinWithLimit()
- * SELECT order_id, i.name, s.name, s.address, quantity
- * FROM joinSupplierTable s
- * JOIN joinItemTable i ON i.supplier_id = s.supplier_id
- * JOIN joinOrderTable o ON o.item_id = i.item_id LIMIT 1 OFFSET 2
- */
- "CLIENT SERIAL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
- " SERVER OFFSET 2\n" +
- "CLIENT 1 ROW LIMIT\n" +
- " PARALLEL INNER-JOIN TABLE 0\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_SCHEMA + ".idx_item\n" +
- " PARALLEL INNER-JOIN TABLE 1(DELAYED EVALUATION)\n" +
- " CLIENT PARALLEL 1-WAY FULL SCAN OVER "+ JOIN_ORDER_TABLE_FULL_NAME + "\n" +
- " DYNAMIC SERVER FILTER BY \"S.supplier_id\" IN (\"I.0:supplier_id\")\n" +
- " JOIN-SCANNER 3 ROW LIMIT",
- }});
- 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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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_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",
- }});
- return testCases;
- }
-
-
- @Test
- public void testDefaultJoin() throws Exception {
- Connection conn = getConnection();
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertEquals(rs.getString(3), "0000000005");
- assertEquals(rs.getString(4), "S5");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000006");
- assertEquals(rs.getString(2), "T6");
- assertEquals(rs.getString(3), "0000000006");
- assertEquals(rs.getString(4), "S6");
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testInnerJoin() throws Exception {
- Connection conn = getConnection();
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for " + seqName + " FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\"";
- 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 {
- Connection conn = getConnection();
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query[] = new String[3];
- query[0] = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name, next value for " + seqName + " FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- query[1] = "SELECT " + tableName1 + ".\"item_id\", " + tableName1 + ".name, " + tableName2 + ".\"supplier_id\", " + tableName2 + ".name, next value for " + seqName + " FROM " + tableName1 + " LEFT JOIN " + tableName2 + " ON " + tableName1 + ".\"supplier_id\" = " + tableName2 + ".\"supplier_id\" ORDER BY \"item_id\"";
- query[2] = "SELECT item.\"item_id\", " + tableName1 + ".name, supp.\"supplier_id\", " + tableName2 + ".name, next value for " + seqName + " FROM " + tableName1 + " item LEFT JOIN " + tableName2 + " supp ON " + tableName1 + ".\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- try {
- for (int i = 0; i < query.length; i++) {
- PreparedStatement statement = conn.prepareStatement(query[i]);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertEquals(rs.getString(3), "0000000005");
- assertEquals(rs.getString(4), "S5");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000006");
- assertEquals(rs.getString(2), "T6");
- assertEquals(rs.getString(3), "0000000006");
- assertEquals(rs.getString(4), "S6");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "invalid001");
- assertEquals(rs.getString(2), "INVALID-1");
- assertNull(rs.getString(3));
- assertNull(rs.getString(4));
-
- assertFalse(rs.next());
- rs.close();
- statement.close();
- }
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testRightJoin() throws Exception {
- Connection conn = getConnection();
- String tableName1 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String query = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " supp RIGHT JOIN " + tableName2 + " item ON item.\"supplier_id\" = supp.\"supplier_id\" ORDER BY \"item_id\"";
- try {
- PreparedStatement statement = conn.prepareStatement(query);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertEquals(rs.getString(3), "0000000005");
- assertEquals(rs.getString(4), "S5");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000006");
- assertEquals(rs.getString(2), "T6");
- assertEquals(rs.getString(3), "0000000006");
- assertEquals(rs.getString(4), "S6");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "invalid001");
- assertEquals(rs.getString(2), "INVALID-1");
- assertNull(rs.getString(3));
- assertNull(rs.getString(4));
-
- assertFalse(rs.next());
- } finally {
- conn.close();
- }
- }
-
- @Test
- public void testInnerJoinWithPreFilters() throws Exception {
- Connection conn = getConnection();
- String tableName1 = getTableName(conn, JOIN_ITEM_TABLE_FULL_NAME);
- String tableName2 = getTableName(conn, JOIN_SUPPLIER_TABLE_FULL_NAME);
- String query1 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND supp.\"supplier_id\" BETWEEN '0000000001' AND '0000000005'";
- String query2 = "SELECT item.\"item_id\", item.name, supp.\"supplier_id\", supp.name FROM " + tableName1 + " item INNER JOIN " + tableName2 + " supp ON item.\"supplier_id\" = supp.\"supplier_id\" AND (supp.\"supplier_id\" = '0000000001' OR supp.\"supplier_id\" = '0000000005')";
- try {
- PreparedStatement statement = conn.prepareStatement(query1);
- ResultSet rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000003");
- assertEquals(rs.getString(2), "T3");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000004");
- assertEquals(rs.getString(2), "T4");
- assertEquals(rs.getString(3), "0000000002");
- assertEquals(rs.getString(4), "S2");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertEquals(rs.getString(3), "0000000005");
- assertEquals(rs.getString(4), "S5");
-
- assertFalse(rs.next());
-
-
- statement = conn.prepareStatement(query2);
- rs = statement.executeQuery();
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000001");
- assertEquals(rs.getString(2), "T1");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000002");
- assertEquals(rs.getString(2), "T2");
- assertEquals(rs.getString(3), "0000000001");
- assertEquals(rs.getString(4), "S1");
- assertTrue (rs.next());
- assertEquals(rs.getString(1), "0000000005");
- assertEquals(rs.getString(2), "T5");
- assertEquals(rs.getString(3), "0000000005");
-
<TRUNCATED>