| /* |
| * 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.drill.exec.physical.impl.lateraljoin; |
| |
| import static org.hamcrest.CoreMatchers.containsString; |
| import static org.hamcrest.core.IsNot.not; |
| import static org.junit.Assert.assertFalse; |
| import static org.hamcrest.MatcherAssert.assertThat; |
| import static org.junit.Assert.assertTrue; |
| |
| import java.nio.file.Paths; |
| import java.util.regex.Matcher; |
| import java.util.regex.Pattern; |
| |
| import org.apache.drill.PlanTestBase; |
| import org.apache.drill.common.exceptions.UserRemoteException; |
| import org.apache.drill.exec.ExecConstants; |
| import org.apache.drill.exec.planner.physical.PlannerSettings; |
| import org.apache.drill.test.BaseTestQuery; |
| import org.apache.drill.test.ClientFixture; |
| import org.apache.drill.test.ClusterFixture; |
| import org.apache.drill.test.ClusterFixtureBuilder; |
| import org.junit.BeforeClass; |
| import org.junit.Test; |
| |
| public class TestLateralPlans extends BaseTestQuery { |
| private static final String regularTestFile_1 = "cust_order_10_1.json"; |
| private static final String regularTestFile_2 = "cust_order_10_2.json"; |
| |
| @BeforeClass |
| public static void enableUnnestLateral() throws Exception { |
| dirTestWatcher.copyResourceToRoot(Paths.get("lateraljoin", "multipleFiles", regularTestFile_1)); |
| dirTestWatcher.copyResourceToRoot(Paths.get("lateraljoin", "multipleFiles", regularTestFile_2)); |
| test("alter session set `planner.enable_unnest_lateral`=true"); |
| } |
| |
| @Test |
| public void testLateralSql() throws Exception { |
| String sql = "select t.c_name, t2.ord.o_shop as o_shop from cp.`lateraljoin/nested-customer.json` t," + |
| " unnest(t.orders) t2(ord) limit 1"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, |
| new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("c_name", "o_shop") |
| .baselineValues("customer1", "Meno Park 1st") |
| .go(); |
| } |
| |
| @Test |
| public void testExplainLateralSql() throws Exception { |
| String explainSql = "explain plan without implementation for select t.c_name, t2.ord.o_shop as o_shop from cp.`lateraljoin/nested-customer.json` t," + |
| " unnest(t.orders) t2(ord) limit 1"; |
| |
| String sql = "select t.c_name, t2.ord.o_shop as o_shop from cp.`lateraljoin/nested-customer.json` t," + |
| " unnest(t.orders) t2(ord) limit 1"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, |
| new String[]{}); |
| |
| test(explainSql); |
| } |
| |
| @Test |
| public void testFilterPushCorrelate() throws Exception { |
| test("alter session set `planner.slice_target`=1"); |
| String sql = "select t.c_name, t2.ord.o_shop as o_shop from cp.`lateraljoin/nested-customer.json` t," |
| + " unnest(t.orders) t2(ord) where t.c_name='customer1' AND t2.ord.o_shop='Meno Park 1st' "; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"LateralJoin(.*[\n\r])+.*Filter(.*[\n\r])+.*Scan(.*[\n\r])+.*Filter"}, |
| new String[]{}); |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, |
| new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("c_name", "o_shop") |
| .baselineValues("customer1", "Meno Park 1st") |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlPlainCol() throws Exception { |
| String sql = "select t.c_name, t2.phone as c_phone from cp.`lateraljoin/nested-customer.json` t," |
| + " unnest(t.c_phone) t2(phone) limit 1"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`c_phone\\`\\]"}, |
| new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("c_name", "c_phone") |
| .baselineValues("customer1", "6505200001") |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlStar() throws Exception { |
| String sql = "select * from cp.`lateraljoin/nested-customer.json` t, unnest(t.orders) Orders(ord) limit 0"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{}, |
| new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("c_name", "c_id", "c_phone", "orders", "c_address", "ord") |
| .expectsEmptyResultSet() |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlStar2() throws Exception { |
| String Sql = "select c.* from cp.`lateraljoin/nested-customer.json` c, unnest(c.orders) Orders(ord) limit 0"; |
| |
| PlanTestBase.testPlanMatchingPatterns(Sql, new String[]{}, |
| new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(Sql) |
| .baselineColumns("c_name", "c_id", "c_phone", "orders", "c_address") |
| .expectsEmptyResultSet() |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlStar3() throws Exception { |
| String sql = "select Orders.*, c.* from cp.`lateraljoin/nested-customer.json` c, unnest(c.orders) Orders(ord) limit 0"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{}, |
| new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("ord","c_name", "c_id", "c_phone", "orders", "c_address") |
| .expectsEmptyResultSet() |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlStar4() throws Exception { |
| String sql = "select Orders.* from cp.`lateraljoin/nested-customer.json` c, unnest(c.orders) Orders(ord) limit 0"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .baselineColumns("ord") |
| .expectsEmptyResultSet() |
| .go(); |
| } |
| |
| @Test |
| public void testLateralSqlWithAS() throws Exception { |
| String sql = "select t.c_name, t2.orders from cp.`lateraljoin/nested-customer.parquet` t," |
| + " unnest(t.orders) as t2(orders)"; |
| String baselineQuery = "select t.c_name, t2.orders from cp.`lateraljoin/nested-customer.parquet` t inner join" + |
| " (select c_name, flatten(orders) from cp" + |
| ".`lateraljoin/nested-customer.parquet` ) as t2(name, orders) on t.c_name = t2.name"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .sqlBaselineQuery(baselineQuery) |
| .go(); |
| } |
| |
| @Test |
| public void testMultiUnnestLateralAtSameLevel() throws Exception { |
| String sql = "select t.c_name, t2.orders, t3.orders from cp.`lateraljoin/nested-customer.parquet` t," + |
| " LATERAL ( select t2.orders from unnest(t.orders) as t2(orders)) as t2, LATERAL " + |
| "(select t3.orders from unnest(t.orders) as t3(orders)) as t3"; |
| String baselineQuery = "select t.c_name, t2.orders, t3.orders from cp.`lateraljoin/nested-customer.parquet` t inner join" + |
| " (select c_name, flatten(orders) from cp.`lateraljoin/nested-customer.parquet` ) as t2 (name, orders) on t.c_name = t2.name " + |
| " inner join (select c_name, flatten(orders) from cp.`lateraljoin/nested-customer.parquet` ) as t3(name, orders) on t.c_name = t3.name"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .sqlBaselineQuery(baselineQuery) |
| .go(); |
| } |
| |
| @Test |
| public void testSubQuerySql() throws Exception { |
| String sql = "select t.c_name, d1.items as items0 , t3.items as items1 from cp.`lateraljoin/nested-customer.parquet` t," + |
| " lateral (select t2.ord.items as items from unnest(t.orders) t2(ord)) d1," + |
| " unnest(d1.items) t3(items)"; |
| |
| String baselineQuery = "select t.c_name, t3.orders.items as items0, t3.items as items1 from cp.`lateraljoin/nested-customer.parquet` t " + |
| " inner join (select c_name, f, flatten(t1.f.items) from (select c_name, flatten(orders) as f from cp.`lateraljoin/nested-customer.parquet`) as t1 ) " + |
| "t3(name, orders, items) on t.c_name = t3.name "; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, new String[]{"column excluded from output: =\\[\\`items\\`\\]"}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .sqlBaselineQuery(baselineQuery) |
| .go(); |
| } |
| |
| @Test |
| public void testUnnestWithFilter() throws Exception { |
| String sql = "select t.c_name, d1.items as items0, t3.items as items1 from cp.`lateraljoin/nested-customer.parquet` t," + |
| " lateral (select t2.ord.items as items from unnest(t.orders) t2(ord)) d1," + |
| " unnest(d1.items) t3(items) where t.c_id > 1"; |
| |
| String baselineQuery = "select t.c_name, t3.orders.items as items0, t3.items as items1 from cp.`lateraljoin/nested-customer.parquet` t " + |
| " inner join (select c_name, f, flatten(t1.f.items) from (select c_name, flatten(orders) as f from cp.`lateraljoin/nested-customer.parquet`) as t1 ) " + |
| "t3(name, orders, items) on t.c_name = t3.name where t.c_id > 1"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]"}, new String[]{"column excluded from output: =\\[\\`items\\`\\]"}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .sqlBaselineQuery(baselineQuery) |
| .go(); |
| } |
| |
| @Test |
| public void testUnnestWithAggInSubquery() throws Exception { |
| String sql = "select t.c_name, sum(t4.items) from cp.`lateraljoin/nested-customer.parquet` t," + |
| " lateral (select t2.ord.items as items from unnest(t.orders) t2(ord)) d1," + |
| " lateral (select sum(t3.items.i_number) from unnest(d1.items) t3(items)) t4(items) where t.c_id > 1 group by t.c_name"; |
| |
| String baselineQuery = "select t.c_name, sum(t3.items.i_number) from cp.`lateraljoin/nested-customer.parquet` t " + |
| " inner join (select c_name, f, flatten(t1.f.items) from (select c_name, flatten(orders) as f from cp.`lateraljoin/nested-customer.parquet`) as t1 ) " + |
| "t3(name, orders, items) on t.c_name = t3.name where t.c_id > 1 group by t.c_name"; |
| |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| client |
| .testBuilder() |
| .ordered() |
| .sqlBaselineQuery(baselineQuery) |
| .sqlQuery(sql) |
| .go(); |
| } |
| } |
| |
| @Test |
| public void testUnnestWithAggOnOuterTable() throws Exception { |
| String sql = "select avg(d2.inum) from cp.`lateraljoin/nested-customer.parquet` t," + |
| " lateral (select t2.ord.items as items from unnest(t.orders) t2(ord)) d1," + |
| " lateral (select t3.items.i_number as inum from unnest(d1.items) t3(items)) d2 where t.c_id > 1 group by t.c_id"; |
| |
| String baselineQuery = "select avg(t3.items.i_number) from cp.`lateraljoin/nested-customer.parquet` t " + |
| " inner join (select c_name, f, flatten(t1.f.items) from (select c_name, flatten(orders) as f from cp.`lateraljoin/nested-customer.parquet`) as t1 ) " + |
| "t3(name, orders, items) on t.c_name = t3.name where t.c_id > 1 group by t.c_id"; |
| |
| PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded from output: =\\[\\`orders\\`\\]", "column excluded from output: =\\[\\`items\\`\\]"}, new String[]{}); |
| |
| testBuilder() |
| .unOrdered() |
| .sqlQuery(sql) |
| .sqlBaselineQuery(baselineQuery) |
| .go(); |
| } |
| |
| @Test |
| public void testUnnestTableAndColumnAlias() throws Exception { |
| String sql = "select t.c_name from cp.`lateraljoin/nested-customer.json` t, unnest(t.orders) "; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| client |
| .queryBuilder() |
| .sql(sql) |
| .run(); |
| } catch (UserRemoteException ex) { |
| assertThat(ex.getMessage(), containsString("Alias table and column name are required for UNNEST")); |
| } |
| } |
| |
| @Test |
| public void testUnnestColumnAlias() throws Exception { |
| String sql = "select t.c_name, t2.orders from cp.`lateraljoin/nested-customer.json` t, unnest(t.orders) t2"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| client |
| .queryBuilder() |
| .sql(sql) |
| .run(); |
| } catch (UserRemoteException ex) { |
| assertThat(ex.getMessage(), containsString("Column 'orders' not found in table 't2'")); |
| } |
| } |
| |
| /*********************************************************************************************** |
| Following test cases are introduced to make sure no exchanges are present on right side of |
| Lateral join. |
| **********************************************************************************************/ |
| |
| @Test |
| public void testNoExchangeWithAggWithoutGrpBy() throws Exception { |
| String sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t," + |
| " lateral ( select sum(t2.ord.o_totalprice) as totalprice from unnest(t.c_orders) t2(ord)) d1"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithStreamAggWithGrpBy() throws Exception { |
| String sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t," + |
| " lateral ( select sum(t2.ord.o_totalprice) as totalprice from unnest(t.c_orders) t2(ord) group by t2.ord.o_orderkey) d1"; |
| |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1) |
| .setOptionDefault(PlannerSettings.HASHAGG.getOptionName(), false) |
| .setOptionDefault(PlannerSettings.STREAMAGG.getOptionName(), true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithHashAggWithGrpBy() throws Exception { |
| String sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t," + |
| " lateral ( select sum(t2.ord.o_totalprice) as totalprice from unnest(t.c_orders) t2(ord) group by t2.ord.o_orderkey) d1"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1) |
| .setOptionDefault(PlannerSettings.HASHAGG.getOptionName(), true) |
| .setOptionDefault(PlannerSettings.STREAMAGG.getOptionName(), false); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithOrderByWithoutLimit() throws Exception { |
| String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t," + |
| " lateral ( select t2.ord.o_totalprice as totalprice from unnest(t.c_orders) t2(ord) order by t2.ord.o_orderkey) d1"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(Sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithOrderByLimit() throws Exception { |
| String sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t," + |
| " lateral ( select t2.ord.o_totalprice as totalprice from unnest(t.c_orders) t2(ord) order by t2.ord.o_orderkey limit 10) d1"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithLateralsDownStreamJoin() throws Exception { |
| String sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t, dfs.`lateraljoin/multipleFiles` t2, " + |
| " lateral ( select t2.ord.o_totalprice as totalprice from unnest(t.c_orders) t2(ord) order by t2.ord.o_orderkey limit 10) d1" + |
| " where t.c_name = t2.c_name"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithLateralsDownStreamUnion() throws Exception { |
| String sql = "select t.c_name from dfs.`lateraljoin/multipleFiles` t union all " + |
| " select t.c_name from dfs.`lateraljoin/multipleFiles` t, " + |
| " lateral ( select t2.ord.o_totalprice as totalprice from unnest(t.c_orders) t2(ord) order by t2.ord.o_orderkey limit 10) d1"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| @Test |
| public void testNoExchangeWithLateralsDownStreamAgg() throws Exception { |
| String sql = "select sum(d1.totalprice) from dfs.`lateraljoin/multipleFiles` t, " + |
| " lateral ( select t2.ord.o_totalprice as totalprice from unnest(t.c_orders) t2(ord) order by t2.ord.o_orderkey limit 10) d1 group by t.c_custkey"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1) |
| .setOptionDefault(PlannerSettings.HASHAGG.getOptionName(), false) |
| .setOptionDefault(PlannerSettings.STREAMAGG.getOptionName(), true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String rightChild = getRightChildOfLateral(explain); |
| assertFalse(rightChild.contains("Exchange")); |
| } |
| } |
| |
| private String getRightChildOfLateral(String explain) throws Exception { |
| Matcher matcher = Pattern.compile("LateralJoin.*Unnest", Pattern.MULTILINE | Pattern.DOTALL).matcher(explain); |
| assertTrue (matcher.find()); |
| String CorrelateUnnest = matcher.group(0); |
| return CorrelateUnnest.substring(CorrelateUnnest.lastIndexOf("Scan")); |
| } |
| |
| //The following test is for testing the explain plan contains relation between lateral and corresponding unnest. |
| @Test |
| public void testLateralAndUnnestExplainPlan() throws Exception { |
| String sql = "select c.* from cp.`lateraljoin/nested-customer.json` c, unnest(c.orders) Orders(ord)"; |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String explain = client.queryBuilder().sql(sql).explainText(); |
| String srcOp = explain.substring(explain.indexOf("srcOp")); |
| assertTrue(srcOp != null && srcOp.length() > 0); |
| String correlateFragmentPattern = srcOp.substring(srcOp.indexOf("=")+1, srcOp.indexOf("]")); |
| assertTrue(correlateFragmentPattern != null && correlateFragmentPattern.length() > 0); |
| Matcher matcher = Pattern.compile(correlateFragmentPattern + ".*LateralJoin", Pattern.MULTILINE | Pattern.DOTALL).matcher(explain); |
| assertTrue(matcher.find()); |
| } |
| } |
| |
| @Test |
| public void testUnnestTopN() throws Exception { |
| String sql = |
| "select customer.c_custkey," + |
| "customer.c_name," + |
| "t.o.o_orderkey," + |
| "t.o.o_totalprice\n" + |
| "from dfs.`lateraljoin/multipleFiles` customer," + |
| "unnest(customer.c_orders) t(o)\n" + |
| "order by customer.c_custkey," + |
| "t.o.o_orderkey," + |
| "t.o.o_totalprice\n" + |
| "limit 50"; |
| |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| String plan = client.queryBuilder() |
| .sql(sql) |
| .explainText(); |
| |
| assertThat("Query plan doesn't contain TopN operator", |
| plan, containsString("TopN(limit=[50])")); |
| assertThat("Query plan shouldn't contain Sort operator", |
| plan, not(containsString("Sort"))); |
| } |
| } |
| |
| @Test |
| public void testMultiUnnestQuery() throws Exception { |
| String sql = "SELECT t5.l_quantity FROM dfs.`lateraljoin/multipleFiles` t, " + |
| "LATERAL (SELECT t2.ordrs.o_lineitems FROM UNNEST(t.c_orders) t2(ordrs)) t3(lineitems), " + |
| "LATERAL (SELECT t4.lineitems.l_quantity FROM UNNEST(t3.lineitems) t4(lineitems)) t5(l_quantity) order by 1"; |
| |
| String baselineQuery = "select dt.lineitems.l_quantity as l_quantity from (select flatten(dt.orders.o_lineitems) as lineitems " + |
| "from (select flatten(c_orders) as orders from dfs.`lateraljoin/multipleFiles` t) dt)dt order by 1"; |
| |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true) |
| .setOptionDefault(ExecConstants.SLICE_TARGET, 1); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| client.testBuilder() |
| .ordered() |
| .sqlBaselineQuery(baselineQuery) |
| .sqlQuery(sql) |
| .go(); |
| } |
| } |
| |
| @Test |
| public void testNestedColumnQuery() throws Exception { |
| String sql = "select dt.area_code as area_code, dt.ph as ph from cp.`lateraljoin/nested-customer-map.json` t," + |
| " lateral (select t2.ord.area_code as area_code , t2.ord.phone as ph from unnest(t.c_address.c_phone) t2(ord)) dt"; |
| |
| String baselineQuery = "select dt.c_ph.area_code as area_code, dt.c_ph.phone as ph from (select flatten(t.c_address.c_phone) as c_ph from cp.`lateraljoin/nested-customer-map.json` t) dt"; |
| |
| ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher) |
| .setOptionDefault(PlannerSettings.ENABLE_UNNEST_LATERAL_KEY, true); |
| |
| try (ClusterFixture cluster = builder.build(); |
| ClientFixture client = cluster.clientFixture()) { |
| client.testBuilder() |
| .ordered() |
| .sqlBaselineQuery(baselineQuery) |
| .sqlQuery(sql) |
| .go(); |
| } |
| } |
| } |
