This is an automated email from the ASF dual-hosted git repository.
vitalii pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/drill.git
commit 93a1c5aba6f17b93a52ab4367f625103a904f1ad
Author: Hanumath Rao Maduri <hanu....@gmail.com>
AuthorDate: Fri Aug 3 13:56:44 2018 -0700
DRILL-6671: Multi level lateral unnest join is throwing an exception during
materializing the plan.
closes #1426
---
.../drill/exec/planner/fragment/Materializer.java | 19 ++--
.../drill/exec/planner/physical/UnnestPrel.java | 10 ++
.../visitor/LateralUnnestRowIDVisitor.java | 54 ++++++++-
.../impl/lateraljoin/TestE2EUnnestAndLateral.java | 81 ++++++++++----
.../impl/lateraljoin/TestLateralPlans.java | 124 ++++++++++-----------
5 files changed, 189 insertions(+), 99 deletions(-)
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/fragment/Materializer.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/fragment/Materializer.java
index 987e65c..2f7fdce 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/fragment/Materializer.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/fragment/Materializer.java
@@ -17,6 +17,8 @@
*/
package org.apache.drill.exec.planner.fragment;
+import java.util.ArrayDeque;
+import java.util.Deque;
import java.util.List;
import org.apache.drill.common.exceptions.ExecutionSetupException;
@@ -116,29 +118,28 @@ public class Materializer extends
AbstractPhysicalVisitor<PhysicalOperator, Mate
children.add(op.getLeft().accept(this, iNode));
children.add(op.getRight().accept(this, iNode));
- UnnestPOP unnestInLeftInput = iNode.getUnnest();
+ UnnestPOP unnestForThisLateral = iNode.getUnnest();
PhysicalOperator newOp = op.getNewWithChildren(children);
newOp.setCost(op.getCost());
newOp.setOperatorId(Short.MAX_VALUE & op.getOperatorId());
- ((LateralJoinPOP)newOp).setUnnestForLateralJoin(unnestInLeftInput);
-
+ ((LateralJoinPOP) newOp).setUnnestForLateralJoin(unnestForThisLateral);
return newOp;
}
@Override
public PhysicalOperator visitUnnest(UnnestPOP unnest, IndexedFragmentNode
value) throws ExecutionSetupException {
PhysicalOperator newOp = visitOp(unnest, value);
- value.addUnnest((UnnestPOP)newOp);
+ value.addUnnest((UnnestPOP) newOp);
return newOp;
}
public static class IndexedFragmentNode{
- final Wrapper info;
- final int minorFragmentId;
+ private final Wrapper info;
+ private final int minorFragmentId;
- UnnestPOP unnest = null;
+ private final Deque<UnnestPOP> unnest = new ArrayDeque<>();
public IndexedFragmentNode(int minorFragmentId, Wrapper info) {
super();
@@ -163,11 +164,11 @@ public class Materializer extends
AbstractPhysicalVisitor<PhysicalOperator, Mate
}
public void addUnnest(UnnestPOP unnest) {
- this.unnest = unnest;
+ this.unnest.addFirst(unnest);
}
public UnnestPOP getUnnest() {
- return this.unnest;
+ return this.unnest.removeFirst();
}
}
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/UnnestPrel.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/UnnestPrel.java
index 274f27a..a344915 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/UnnestPrel.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/UnnestPrel.java
@@ -25,6 +25,7 @@ import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.drill.common.expression.SchemaPath;
import org.apache.drill.exec.physical.base.PhysicalOperator;
@@ -86,6 +87,15 @@ public class UnnestPrel extends DrillUnnestRelBase
implements Prel {
}
@Override
+ public RelNode accept(RexShuttle shuttle) {
+ RexNode ref = shuttle.apply(this.ref);
+ if (this.ref == ref) {
+ return this;
+ }
+ return new UnnestPrel(getCluster(), traitSet, rowType, ref);
+ }
+
+ @Override
public Prel prepareForLateralUnnestPipeline(List<RelNode> children) {
RelDataTypeFactory typeFactory = this.getCluster().getTypeFactory();
List<String> fieldNames = new ArrayList<>();
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/visitor/LateralUnnestRowIDVisitor.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/visitor/LateralUnnestRowIDVisitor.java
index dc4af5b..7734d90 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/visitor/LateralUnnestRowIDVisitor.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/physical/visitor/LateralUnnestRowIDVisitor.java
@@ -19,11 +19,18 @@ package org.apache.drill.exec.planner.physical.visitor;
import com.google.common.collect.Lists;
import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.rel.core.CorrelationId;
+import org.apache.calcite.rel.rules.ProjectCorrelateTransposeRule;
+import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexShuttle;
+import org.apache.calcite.util.ImmutableBitSet;
import org.apache.drill.exec.planner.physical.LateralJoinPrel;
import org.apache.drill.exec.planner.physical.Prel;
import org.apache.drill.exec.planner.physical.UnnestPrel;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
/**
* LateralUnnestRowIDVisitor traverses the physical plan and modifies all the
operators in the
@@ -59,16 +66,55 @@ public class LateralUnnestRowIDVisitor extends
BasePrelVisitor<Prel, Boolean, Ru
}
@Override
- public Prel visitLateral(LateralJoinPrel prel, Boolean value) throws
RuntimeException {
+ public Prel visitLateral(LateralJoinPrel prel, Boolean isRightOfLateral)
throws RuntimeException {
List<RelNode> children = Lists.newArrayList();
- children.add(((Prel)prel.getInput(0)).accept(this, value));
+ children.add(((Prel) prel.getInput(0)).accept(this, isRightOfLateral));
children.add(((Prel) prel.getInput(1)).accept(this, true));
- return (Prel) prel.copy(prel.getTraitSet(), children);
+ if (!isRightOfLateral) {
+ return (Prel) prel.copy(prel.getTraitSet(), children);
+ } else {
+ //Adjust the column numbering due to an additional column
"$drill_implicit_field$" is added to the inputs.
+ Map<Integer, Integer> requiredColsMap = new HashMap<>();
+ for (Integer corrColIndex : prel.getRequiredColumns()) {
+ requiredColsMap.put(corrColIndex, corrColIndex + 1);
+ }
+ ImmutableBitSet requiredColumns = prel.getRequiredColumns().shift(1);
+
+ CorrelationId corrId = prel.getCluster().createCorrel();
+ RexCorrelVariable updatedCorrel =
+ (RexCorrelVariable) prel.getCluster().getRexBuilder().makeCorrel(
+ children.get(0).getRowType(),
+ corrId);
+ RelNode rightChild = children.get(1).accept(
+ new CorrelateVarReplacer(
+ new
ProjectCorrelateTransposeRule.RexFieldAccessReplacer(prel.getCorrelationId(),
+ updatedCorrel,
prel.getCluster().getRexBuilder(), requiredColsMap)));
+ return (Prel) prel.copy(prel.getTraitSet(), children.get(0), rightChild,
+ corrId, requiredColumns, prel.getJoinType());
+ }
}
@Override
- public Prel visitUnnest(UnnestPrel prel, Boolean value) throws
RuntimeException {
+ public Prel visitUnnest(UnnestPrel prel, Boolean isRightOfLateral) throws
RuntimeException {
return prel.prepareForLateralUnnestPipeline(null);
}
+
+ /**
+ * Visitor for RelNodes which applies specified {@link RexShuttle} visitor
+ * for every node in the tree.
+ */
+ public static class CorrelateVarReplacer extends
ProjectCorrelateTransposeRule.RelNodesExprsHandler {
+ protected final RexShuttle rexVisitor;
+
+ public CorrelateVarReplacer(RexShuttle rexVisitor) {
+ super(rexVisitor);
+ this.rexVisitor = rexVisitor;
+ }
+
+ @Override
+ public RelNode visit(RelNode other) {
+ return super.visit(other.accept(rexVisitor));
+ }
+ }
}
diff --git
a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestE2EUnnestAndLateral.java
b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestE2EUnnestAndLateral.java
index 0d752a2..0283ade 100644
---
a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestE2EUnnestAndLateral.java
+++
b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestE2EUnnestAndLateral.java
@@ -58,40 +58,40 @@ public class TestE2EUnnestAndLateral extends ClusterTest {
@Test
public void testLateral_WithLimitInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
+ String sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) LIMIT 1) orders";
- runAndLog(Sql);
+ runAndLog(sql);
}
@Test
public void testLateral_WithFilterInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
+ String sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) WHERE t.ord.o_amount > 10) orders";
- runAndLog(Sql);
+ runAndLog(sql);
}
@Test
public void testLateral_WithFilterAndLimitInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
+ String sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) WHERE t.ord.o_amount > 10 LIMIT 1) orders";
- runAndLog(Sql);
+ runAndLog(sql);
}
@Test
public void testLateral_WithTopNInSubQuery() throws Exception {
runAndLog("alter session set `planner.enable_topn`=false");
- String Sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
+ String sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) ORDER BY " +
"o_amount DESC LIMIT 1) orders";
try {
testBuilder()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.unOrdered()
.baselineColumns("c_name", "o_id", "o_amount")
.baselineValues("customer1", 3.0, 294.5)
@@ -113,14 +113,14 @@ public class TestE2EUnnestAndLateral extends ClusterTest {
runAndLog("alter session set `planner.enable_topn`=false");
- String Sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
+ String sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) ORDER BY " +
"o_amount DESC LIMIT 1) orders";
try {
testBuilder()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.unOrdered()
.baselineColumns("c_name", "o_id", "o_amount")
.baselineValues("customer1", 3.0, 294.5)
@@ -135,13 +135,13 @@ public class TestE2EUnnestAndLateral extends ClusterTest {
@Test
public void testLateral_WithSortInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
+ String sql = "SELECT customer.c_name, orders.o_id, orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) ORDER BY " +
"o_amount DESC) orders WHERE customer.c_id = 1.0";
testBuilder()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.ordered()
.baselineColumns("c_name", "o_id", "o_amount")
.baselineValues("customer1", 3.0, 294.5)
@@ -152,28 +152,61 @@ public class TestE2EUnnestAndLateral extends ClusterTest {
@Test
public void testOuterApply_WithFilterAndLimitInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
+ String sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer OUTER APPLY " +
"(SELECT t.ord.o_id as o_id , t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) WHERE t.ord.o_amount > 10 LIMIT 1) orders";
- runAndLog(Sql);
+ runAndLog(sql);
}
@Test
public void testLeftLateral_WithFilterAndLimitInSubQuery() throws Exception {
- String Sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
+ String sql = "SELECT customer.c_name, customer.c_address, orders.o_id,
orders.o_amount " +
"FROM cp.`lateraljoin/nested-customer.parquet` customer LEFT JOIN
LATERAL " +
"(SELECT t.ord.o_id as o_id, t.ord.o_amount as o_amount FROM
UNNEST(customer.orders) t(ord) WHERE t.ord.o_amount > 10 LIMIT 1) orders ON
TRUE";
- runAndLog(Sql);
+ runAndLog(sql);
}
@Test
public void testMultiUnnestAtSameLevel() throws Exception {
- String Sql = "EXPLAIN PLAN FOR SELECT customer.c_name, customer.c_address,
U1.order_id, U1.order_amt," +
- " U1.itemName, U1.itemNum" + " FROM
cp.`lateraljoin/nested-customer.parquet` customer, LATERAL" +
- " (SELECT t.ord.o_id AS order_id, t.ord.o_amount AS order_amt,
U2.item_name AS itemName, U2.item_num AS " +
- "itemNum FROM UNNEST(customer.orders) t(ord) , LATERAL" +
- " (SELECT t1.ord.i_name AS item_name, t1.ord.i_number AS item_num FROM
UNNEST(t.ord) AS t1(ord)) AS U2) AS U1";
- runAndLog(Sql);
+ String sql = "EXPLAIN PLAN FOR SELECT customer.c_name, customer.c_address,
U1.order_id, U1.order_amt," +
+ " U1.itemName, U1.itemNum" + " FROM
cp.`lateraljoin/nested-customer.parquet` customer, LATERAL" +
+ " (SELECT t.ord.o_id AS order_id, t.ord.o_amount AS order_amt,
U2.item_name AS itemName, U2.item_num AS " +
+ "itemNum FROM UNNEST(customer.orders) t(ord) , LATERAL" +
+ " (SELECT t1.ord.i_name AS item_name, t1.ord.i_number AS item_num
FROM UNNEST(t.ord) AS t1(ord)) AS U2) AS U1";
+ runAndLog(sql);
+ }
+
+ @Test
+ public void testMultiUnnestAtSameLevelExec() throws Exception {
+ String sql = "SELECT customer.c_name, customer.c_address, U1.order_id,
U1.order_amt," +
+ " U1.itemName, U1.itemNum FROM cp.`lateraljoin/nested-customer.parquet`
customer, LATERAL" +
+ " (SELECT dt.order_id, dt.order_amt, U2.item_name AS itemName,
U2.item_num AS itemNum from" +
+ "(select t.ord.items as items, t.ord.o_id AS order_id,
t.ord.o_amount AS order_amt FROM UNNEST(customer.orders) t(ord)) dt , LATERAL" +
+ " (SELECT t1.items.i_name AS item_name, t1.items.i_number AS item_num
FROM UNNEST(dt.items) AS t1(items)) AS U2) AS U1";
+ String baseline = "SELECT customer.c_name, customer.c_address,
U1.order_id, U1.order_amount as order_amt, U2.item_name as itemName,
U2.item_num as itemNum" +
+ " FROM cp.`lateraljoin/nested-customer.parquet` customer, LATERAL
" +
+ "(SELECT t.ord.items as items, t.ord.o_id as order_id,
t.ord.o_amount as order_amount from UNNEST(customer.orders) t(ord)) U1,
LATERAL" +
+ "(SELECT t1.items.i_name as item_name, t1.items.i_number as
item_num from UNNEST(U1.items) t1(items)) U2";
+ testBuilder()
+ .unOrdered()
+ .sqlQuery(sql)
+ .sqlBaselineQuery(baseline)
+ .go();
+ }
+
+ @Test
+ public void testMultiUnnestAtSameLevelExecExplicitResult() throws Exception {
+ String sql = "SELECT customer.c_name, customer.c_address, U1.order_id,
U1.order_amt," +
+ " U1.itemName, U1.itemNum FROM
cp.`lateraljoin/nested-customer.parquet` customer, LATERAL" +
+ " (SELECT dt.order_id, dt.order_amt, U2.item_name AS itemName,
U2.item_num AS itemNum from" +
+ "(select t.ord.items as items, t.ord.o_id AS order_id,
t.ord.o_amount AS order_amt FROM UNNEST(customer.orders) t(ord)) dt , LATERAL" +
+ " (SELECT t1.items.i_name AS item_name, t1.items.i_number AS
item_num FROM UNNEST(dt.items) AS t1(items)) AS U2) AS U1 order by 1,2,3,4,5,6
limit 1";
+ testBuilder()
+ .unOrdered()
+ .sqlQuery(sql)
+ .baselineColumns("c_name", "c_address", "order_id", "order_amt",
"itemName", "itemNum")
+ .baselineValues("customer1","bay area, CA",1.0,4.5,"cheese",9.0)
+ .go();
}
@Test
@@ -265,9 +298,9 @@ public class TestE2EUnnestAndLateral extends ClusterTest {
@Test
public void testNestedUnnest() throws Exception {
- String Sql = "select * from (select customer.orders as orders from
cp.`lateraljoin/nested-customer.parquet` customer ) t1," +
+ String sql = "select * from (select customer.orders as orders from
cp.`lateraljoin/nested-customer.parquet` customer ) t1," +
" lateral ( select t.ord.items as items from unnest(t1.orders) t(ord)
) t2, unnest(t2.items) t3(item) ";
- runAndLog(Sql);
+ runAndLog(sql);
}
/***********************************************************************************************
diff --git
a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestLateralPlans.java
b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestLateralPlans.java
index 9cb5b6d..ef52f8e 100644
---
a/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestLateralPlans.java
+++
b/exec/java-exec/src/test/java/org/apache/drill/exec/physical/impl/lateraljoin/TestLateralPlans.java
@@ -51,15 +51,15 @@ public class TestLateralPlans extends BaseTestQuery {
@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," +
+ 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\\`\\]"},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"},
new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.baselineColumns("c_name", "o_shop")
.baselineValues("customer1", "Meno Park 1st")
.go();
@@ -70,10 +70,10 @@ public class TestLateralPlans extends BaseTestQuery {
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," +
+ 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\\`\\]"},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"},
new String[]{});
test(explainSql);
@@ -82,18 +82,18 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testFilterPushCorrelate() throws Exception {
test("alter session set `planner.slice_target`=1");
- String query = "select t.c_name, t2.ord.o_shop as o_shop from
cp.`lateraljoin/nested-customer.json` t,"
+ 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(query, new
String[]{"LateralJoin(.*[\n\r])+.*Filter(.*[\n\r])+.*Scan(.*[\n\r])+.*Filter"},
+ PlanTestBase.testPlanMatchingPatterns(sql, new
String[]{"LateralJoin(.*[\n\r])+.*Filter(.*[\n\r])+.*Scan(.*[\n\r])+.*Filter"},
new String[]{});
- PlanTestBase.testPlanMatchingPatterns(query, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"},
new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(query)
+ .sqlQuery(sql)
.baselineColumns("c_name", "o_shop")
.baselineValues("customer1", "Meno Park 1st")
.go();
@@ -101,15 +101,15 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testLateralSqlPlainCol() throws Exception {
- String Sql = "select t.c_name, t2.phone as c_phone from
cp.`lateraljoin/nested-customer.json` t,"
+ 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\\`\\]"},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`c_phone\\`\\]"},
new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.baselineColumns("c_name", "c_phone")
.baselineValues("customer1", "6505200001")
.go();
@@ -117,14 +117,14 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testLateralSqlStar() throws Exception {
- String Sql = "select * from cp.`lateraljoin/nested-customer.json` t,
unnest(t.orders) Orders(ord) limit 0";
+ String sql = "select * from cp.`lateraljoin/nested-customer.json` t,
unnest(t.orders) Orders(ord) limit 0";
- PlanTestBase.testPlanMatchingPatterns(Sql, new String[]{},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{},
new String[]{"column excluded from output: =\\[\\`orders\\`\\]"});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.baselineColumns("c_name", "c_id", "c_phone", "orders", "c_address",
"ord")
.expectsEmptyResultSet()
.go();
@@ -147,14 +147,14 @@ public class TestLateralPlans extends BaseTestQuery {
@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";
+ String sql = "select Orders.*, c.* from
cp.`lateraljoin/nested-customer.json` c, unnest(c.orders) Orders(ord) limit 0";
- PlanTestBase.testPlanMatchingPatterns(Sql, new String[]{},
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{},
new String[]{"column excluded from output: =\\[\\`orders\\`\\]"});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.baselineColumns("ord","c_name", "c_id", "c_phone", "orders",
"c_address")
.expectsEmptyResultSet()
.go();
@@ -162,13 +162,13 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testLateralSqlStar4() throws Exception {
- String Sql = "select Orders.* from cp.`lateraljoin/nested-customer.json`
c, unnest(c.orders) Orders(ord) limit 0";
+ 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[]{});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"}, new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.baselineColumns("ord")
.expectsEmptyResultSet()
.go();
@@ -176,17 +176,17 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testLateralSqlWithAS() throws Exception {
- String Sql = "select t.c_name, t2.orders from
cp.`lateraljoin/nested-customer.parquet` t,"
+ 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[]{});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"}, new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.sqlBaselineQuery(baselineQuery)
.go();
@@ -194,25 +194,25 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testMultiUnnestLateralAtSameLevel() throws Exception {
- String Sql = "select t.c_name, t2.orders, t3.orders from
cp.`lateraljoin/nested-customer.parquet` t," +
+ 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[]{});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"}, new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .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," +
+ 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)";
@@ -220,18 +220,18 @@ public class TestLateralPlans extends BaseTestQuery {
" 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\\`\\]"});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"}, new String[]{"column excluded from output:
=\\[\\`items\\`\\]"});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .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," +
+ 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";
@@ -239,18 +239,18 @@ public class TestLateralPlans extends BaseTestQuery {
" 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\\`\\]"});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]"}, new String[]{"column excluded from output:
=\\[\\`items\\`\\]"});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .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," +
+ 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";
@@ -267,14 +267,14 @@ public class TestLateralPlans extends BaseTestQuery {
.testBuilder()
.ordered()
.sqlBaselineQuery(baselineQuery)
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.go();
}
}
@Test
public void testUnnestWithAggOnOuterTable() throws Exception {
- String Sql = "select avg(d2.inum) from
cp.`lateraljoin/nested-customer.parquet` t," +
+ 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";
@@ -282,11 +282,11 @@ public class TestLateralPlans extends BaseTestQuery {
" 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[]{});
+ PlanTestBase.testPlanMatchingPatterns(sql, new String[]{"column excluded
from output: =\\[\\`orders\\`\\]", "column excluded from output:
=\\[\\`items\\`\\]"}, new String[]{});
testBuilder()
.unOrdered()
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.sqlBaselineQuery(baselineQuery)
.go();
@@ -294,7 +294,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testUnnestTableAndColumnAlias() throws Exception {
- String Sql = "select t.c_name from cp.`lateraljoin/nested-customer.json`
t, unnest(t.orders) ";
+ String sql = "select t.c_name from cp.`lateraljoin/nested-customer.json`
t, unnest(t.orders) ";
ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher)
.setOptionDefault(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true);
@@ -302,7 +302,7 @@ public class TestLateralPlans extends BaseTestQuery {
ClientFixture client = cluster.clientFixture()) {
client
.queryBuilder()
- .sql(Sql)
+ .sql(sql)
.run();
} catch (UserRemoteException ex) {
assertTrue(ex.getMessage().contains("Alias table and column name are
required for UNNEST"));
@@ -311,7 +311,7 @@ public class TestLateralPlans extends BaseTestQuery {
@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";
+ 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(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true);
@@ -319,7 +319,7 @@ public class TestLateralPlans extends BaseTestQuery {
ClientFixture client = cluster.clientFixture()) {
client
.queryBuilder()
- .sql(Sql)
+ .sql(sql)
.run();
} catch (UserRemoteException ex) {
assertTrue(ex.getMessage().contains("Alias table and column name are
required for UNNEST"));
@@ -333,7 +333,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithAggWithoutGrpBy() throws Exception {
- String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles`
t," +
+ 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(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true)
@@ -341,7 +341,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -349,7 +349,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithStreamAggWithGrpBy() throws Exception {
- String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles`
t," +
+ 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)
@@ -360,7 +360,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -368,7 +368,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithHashAggWithGrpBy() throws Exception {
- String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles`
t," +
+ 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(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true)
@@ -378,7 +378,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -402,7 +402,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithOrderByLimit() throws Exception {
- String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles`
t," +
+ 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(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true)
@@ -410,7 +410,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -419,7 +419,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithLateralsDownStreamJoin() throws Exception {
- String Sql = "select d1.totalprice from dfs.`lateraljoin/multipleFiles` t,
dfs.`lateraljoin/multipleFiles` t2, " +
+ 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)
@@ -428,7 +428,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -436,7 +436,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithLateralsDownStreamUnion() throws Exception {
- String Sql = "select t.c_name from dfs.`lateraljoin/multipleFiles` t union
all " +
+ 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)
@@ -445,7 +445,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -453,7 +453,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testNoExchangeWithLateralsDownStreamAgg() throws Exception {
- String Sql = "select sum(d1.totalprice) from
dfs.`lateraljoin/multipleFiles` t, " +
+ 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(ExecConstants.ENABLE_UNNEST_LATERAL_KEY, true)
@@ -463,7 +463,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
- String explain = client.queryBuilder().sql(Sql).explainText();
+ String explain = client.queryBuilder().sql(sql).explainText();
String rightChild = getRightChildOfLateral(explain);
assertFalse(rightChild.contains("Exchange"));
}
@@ -480,14 +480,14 @@ public class TestLateralPlans extends BaseTestQuery {
//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)";
+ String sql = "select c.* from cp.`lateraljoin/nested-customer.json` c,
unnest(c.orders) Orders(ord)";
ClusterFixtureBuilder builder = ClusterFixture.builder(dirTestWatcher)
.setOptionDefault(ExecConstants.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 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("]"));
@@ -499,7 +499,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testUnnestTopN() throws Exception {
- String query =
+ String sql =
"select customer.c_custkey," +
"customer.c_name," +
"t.o.o_orderkey," +
@@ -517,7 +517,7 @@ public class TestLateralPlans extends BaseTestQuery {
try (ClusterFixture cluster = builder.build();
ClientFixture client = cluster.clientFixture()) {
String plan = client.queryBuilder()
- .sql(query)
+ .sql(sql)
.explainText();
assertThat("Query plan doesn't contain TopN operator",
@@ -529,7 +529,7 @@ public class TestLateralPlans extends BaseTestQuery {
@Test
public void testMultiUnnestQuery() throws Exception {
- String Sql = "SELECT t5.l_quantity FROM dfs.`lateraljoin/multipleFiles` t,
" +
+ 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";
@@ -545,7 +545,7 @@ public class TestLateralPlans extends BaseTestQuery {
client.testBuilder()
.ordered()
.sqlBaselineQuery(baselineQuery)
- .sqlQuery(Sql)
+ .sqlQuery(sql)
.go();
}
}
