You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by AsterixDB Code Review <do...@asterix-gerrit.ics.uci.edu> on 2026/08/14 09:55:38 UTC
[XL] Change in asterixdb[master]: [NO ISSUE][COMP] Sample the join key expression, not a column
From Ritik Raj <ri...@couchbase.com>:
Ritik Raj has uploaded this change for review. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21551?usp=email )
Change subject: [NO ISSUE][COMP] Sample the join key expression, not a column
......................................................................
[NO ISSUE][COMP] Sample the join key expression, not a column
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- naiveJoinSelectivity paired the join predicate's used variables
positionally with the two leaf inputs, taking exprUsedVars.get(0) for
idx1 and get(1) for idx2. When one side of the predicate is a computed
expression, the defining ASSIGN is inlined into the condition, so the
predicate carries more than two variables in no particular order and
the positional pick can return a variable the chosen leaf input does
not produce. runSamplingQueryDistinct then aggregated an untyped
variable and typing the sampling plan threw a NullPointerException,
failing compilation of a valid query.
- Derive the join key from the chosen leaf input instead: keep using
idx1/idx2 to select which side's sample to use, then ask that leaf
input which part of the predicate it produces. The side and the key
can no longer disagree.
- runSamplingQueryDistinct takes an ILogicalExpression overload that
inserts an ASSIGN for a computed key. A bare variable reference builds
the same plan as before, so existing estimates are unchanged.
- This also corrects a silent misestimate in the mirrored shape, where
the computed expression is on the sampled side. There all variables
are live, so nothing crashed, but the distinct count came from one
column the expression reads rather than from the expression itself.
Tests:
- runtimets cbo-join/computed-join-predicate reproduces the compilation
failure.
- runtimets cbo-join/computed-join-predicate-sampled-side step 5 pins
the plan for the sampled-side shape, where the estimate changed from
960 to 9600 against a true join cardinality of roughly 31000.
- optimizerts cbo-join/computed-join-predicate.sqlpp covers the same
shape at plan level; no optimizer fixture ran ANALYZE before, so the
sampling path had no plan level coverage at all.
- OptimizerTest and CBOOptimizerTest both 702/702, no other plan moved.
Ext-ref: MB-73256
Change-Id: I604d0ab81473b7be32dc2f098abfa122f3b52523
---
M asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
A asterixdb/asterix-app/src/test/resources/optimizerts/queries/cbo-join/computed-join-predicate.sqlpp
A asterixdb/asterix-app/src/test/resources/optimizerts/results/cbo-join/computed-join-predicate.plan
A asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/cbo-join/computed-join-predicate.plan
M asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.3.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.4.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.5.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.3.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.1.ddl.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.2.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.3.update.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.4.query.sqlpp
A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.4.regex
A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.5.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.adm
A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.plan
A asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate/computed-join-predicate.4.adm
24 files changed, 1,293 insertions(+), 11 deletions(-)
git pull ssh://asterix-gerrit.ics.uci.edu:29418/asterixdb refs/changes/51/21551/1
diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
index bdcbe3c..9ae7fb6 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/rules/cbo/Stats.java
@@ -19,7 +19,8 @@
package org.apache.asterix.optimizer.rules.cbo;
-import static org.apache.asterix.om.functions.BuiltinFunctions.getBuiltinFunctionInfo;
+import static org.apache.hyracks.util.annotations.AiProvenance.Agent.CLAUDE_OPUS_4_8;
+import static org.apache.hyracks.util.annotations.AiProvenance.Tool.CLAUDE_CODE_UI;
import java.util.ArrayList;
import java.util.Arrays;
@@ -83,6 +84,7 @@
import org.apache.hyracks.api.exceptions.ErrorCode;
import org.apache.hyracks.api.exceptions.IWarningCollector;
import org.apache.hyracks.api.exceptions.Warning;
+import org.apache.hyracks.util.annotations.AiProvenance;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
@@ -210,7 +212,7 @@
//At this point, a single join will be treated as an EQ join even if it is not. Not ideal but no other choice
// for example interval-overlap falls into this case. For some reason, joining samples with interval-overlap fails,
// so defaulting to this case.
- double seln = naiveJoinSelectivity(exprUsedVars, card1, card2, idx1, idx2, unnestOp1, unnestOp2);
+ double seln = naiveJoinSelectivity(joinExpr, card1, card2, idx1, idx2, unnestOp1, unnestOp2);
return seln;
}
@@ -291,33 +293,33 @@
return false;
}
- private double naiveJoinSelectivity(List<LogicalVariable> exprUsedVars, double card1, double card2, int idx1,
+ private double naiveJoinSelectivity(AbstractFunctionCallExpression joinExpr, double card1, double card2, int idx1,
int idx2, boolean unnestOp1, boolean unnestOp2) throws AlgebricksException {
AbstractLeafInput leafInput;
- LogicalVariable var;
if (unnestOp1) {// we cannot choose teh side with an array as we need the unnesting scaling factor also.
// have to see if there are other alternatives later
leafInput = joinEnum.leafInputs.get(idx2 - 1);
- var = exprUsedVars.get(1);
} else if (unnestOp2) {
leafInput = joinEnum.leafInputs.get(idx1 - 1);
- var = exprUsedVars.get(0);
} else {
// choose the smaller side sample; better results this way for sure!
if (card1 < card2) {
leafInput = joinEnum.leafInputs.get(idx1 - 1);
- var = exprUsedVars.get(0);
} else {
leafInput = joinEnum.leafInputs.get(idx2 - 1);
- var = exprUsedVars.get(1);
}
}
Index index = findIndex((RealLeafInput) leafInput);
if (index == null) {
return 1.0;
}
- List<List<IAObject>> result = runSamplingQueryDistinct(this.optCtx, leafInput.getOp(), var, index);
+ // the join key of the chosen side is an expression, not necessarily a column; count its distinct values
+ ILogicalExpression joinKeyExpr = findSampledSideJoinKeyExpr(leafInput.getOp(), joinExpr);
+ if (joinKeyExpr == null) {
+ return 1.0;
+ }
+ List<List<IAObject>> result = runSamplingQueryDistinct(this.optCtx, leafInput.getOp(), joinKeyExpr, index);
if (result == null) {
return 1.0;
}
@@ -344,9 +346,66 @@
if (numDistincts > details.getSourceCardinality()) {
numDistincts = details.getSourceCardinality(); // cannot exceed table cardinality
}
+ LOGGER.info("***naive join selectivity: distinct({}) = {} from sample; selectivity = {}***", joinKeyExpr,
+ numDistincts, 1.0 / numDistincts);
return 1.0 / numDistincts; // this is the expected selectivity for joins for Fk-PK and Fk-Fk joins
}
+ /**
+ * Returns the join-key expression of the side of {@code joinExpr} that {@code leafInputOp} produces,
+ * or {@code null} when that side cannot be identified.
+ * <p>
+ * {@link #naiveJoinSelectivity} estimates the join selectivity as {@code 1/distinct(join key)} of one
+ * side, computed from that side's sample. The join key of a side is in general an <em>expression</em>,
+ * not a column: in {@code R.a * R.b mod 100 = S.c} the key of R is {@code R.a * R.b mod 100}. It used
+ * to be assumed that the predicate's used variables pair up positionally with the two leaf inputs of
+ * the join -- the first used variable with the first leaf input, the second with the second one --
+ * which only holds for the shape {@code R.a op S.b}. When one side is computed, that side contributes
+ * more than one used variable (the defining ASSIGN is inlined into the predicate by
+ * {@code JoinEnum.findJoinConditionsAndDoTC}), and the positional pairing then hands back a variable
+ * that the chosen leaf input does not even produce.
+ * <p>
+ * The argument of the binary predicate whose used variables are all live in {@code leafInputOp}
+ * <em>is</em> that side's join key, so it is identified from liveness rather than from position, and
+ * the two arguments are required to partition cleanly across the two sides. {@code null} is returned
+ * when the predicate is not binary, when neither argument's variables are all live in the chosen leaf
+ * input, or when one argument mixes variables of both sides -- in all of those cases the sample of this
+ * leaf input cannot yield the distinct count of a join key, and the caller falls back to its default
+ * selectivity.
+ */
+ @AiProvenance(agent = CLAUDE_OPUS_4_8, tool = CLAUDE_CODE_UI, notes = "Identify the sampled side's join-key expression from liveness instead of position")
+ private static ILogicalExpression findSampledSideJoinKeyExpr(ILogicalOperator leafInputOp,
+ AbstractFunctionCallExpression joinExpr) throws AlgebricksException {
+ List<Mutable<ILogicalExpression>> args = joinExpr.getArguments();
+ if (args.size() != 2) {
+ // not a binary comparison; there is no "the other side" to attribute the remaining variables to
+ return null;
+ }
+ List<LogicalVariable> liveVars = liveVariablesOf(leafInputOp);
+ ILogicalExpression arg0 = args.get(0).getValue(), arg1 = args.get(1).getValue();
+ List<LogicalVariable> argVars0 = usedVariablesOf(arg0), argVars1 = usedVariablesOf(arg1);
+ // an argument belongs to this side only if it has variables and every one of them is live here; it
+ // belongs to the other side only if none of its variables is live here. A constant argument belongs
+ // to neither, and an argument mixing variables of both sides belongs to neither.
+ if (isFromThisSide(argVars0, liveVars) && isFromOtherSide(argVars1, liveVars)) {
+ return arg0;
+ }
+ if (isFromThisSide(argVars1, liveVars) && isFromOtherSide(argVars0, liveVars)) {
+ return arg1;
+ }
+ return null;
+ }
+
+ @AiProvenance(agent = CLAUDE_OPUS_4_8, tool = CLAUDE_CODE_UI, notes = "Side attribution helper for findSampledSideJoinKeyExpr")
+ private static boolean isFromThisSide(List<LogicalVariable> argVars, List<LogicalVariable> liveVars) {
+ return !argVars.isEmpty() && liveVars.containsAll(argVars);
+ }
+
+ @AiProvenance(agent = CLAUDE_OPUS_4_8, tool = CLAUDE_CODE_UI, notes = "Side attribution helper for findSampledSideJoinKeyExpr")
+ private static boolean isFromOtherSide(List<LogicalVariable> argVars, List<LogicalVariable> liveVars) {
+ return !argVars.isEmpty() && argVars.stream().noneMatch(liveVars::contains);
+ }
+
private double findJoinSelFromSamples(RealLeafInput left, RealLeafInput right, Index index1, Index index2,
AbstractFunctionCallExpression joinExpr, JoinOperator join) throws AlgebricksException {
AbstractBinaryJoinOperator abjoin = join.getAbstractJoinOp();
@@ -900,8 +959,31 @@
protected List<List<IAObject>> runSamplingQueryDistinct(IOptimizationContext ctx, ILogicalOperator op,
LogicalVariable var, Index index) throws AlgebricksException {
+ return runSamplingQueryDistinct(ctx, op, new VariableReferenceExpression(var), index);
+ }
+
+ /**
+ * Counts the distinct values of {@code keyExpr} in the sample of the dataset scanned by {@code op}.
+ * {@code keyExpr} is evaluated by an ASSIGN inserted on top of the copied subtree, so it may be any
+ * expression over the variables that subtree produces and not just a bare variable reference. When it
+ * <em>is</em> a bare variable reference no ASSIGN is added, so the plan is exactly the one that was
+ * built before expressions were supported.
+ */
+ @AiProvenance(agent = CLAUDE_OPUS_4_8, tool = CLAUDE_CODE_UI, contributionKind = AiProvenance.ContributionKind.REFACTORED, notes = "Count distinct values of an expression, not only of a variable")
+ protected List<List<IAObject>> runSamplingQueryDistinct(IOptimizationContext ctx, ILogicalOperator op,
+ ILogicalExpression keyExpr, Index index) throws AlgebricksException {
LOGGER.info("***running sample query***");
+ List<LogicalVariable> keyExprVars = usedVariablesOf(keyExpr);
+ if (keyExprVars.isEmpty() || !liveVariablesOf(op).containsAll(keyExprVars)) {
+ // The plan built below counts the distinct values of keyExpr, so every variable it references must
+ // be produced by op. If one is not, keyExpr has no type in the subtree below it and typing the
+ // sampling plan would fail. Callers treat null as "no estimate available from the sample".
+ LOGGER.info("***cannot sample {}: the subtree being sampled does not produce all of {}***", keyExpr,
+ keyExprVars);
+ return null;
+ }
+
IOptimizationContext newCtx = ctx.getOptimizationContextFactory().cloneOptimizationContext(ctx);
ILogicalOperator newLogOp = OperatorManipulationUtil.bottomUpCopyOperators(op);
@@ -931,11 +1013,28 @@
scanOp.setDataSource(sampledatasource);
}
+ // the join key may be a computed expression; evaluate it with an assign so that the distinct count
+ // below is the distinct count of the key itself and not of one of the columns it is computed from.
+ LogicalVariable var;
+ ILogicalOperator distinctInput;
+ if (keyExpr.getExpressionTag() == LogicalExpressionTag.VARIABLE) {
+ var = ((VariableReferenceExpression) keyExpr).getVariableReference();
+ distinctInput = newLogOp;
+ } else {
+ var = newCtx.newVar();
+ // clone: the expression is still installed in the real join condition, which must not be touched
+ AssignOperator keyAssignOp = new AssignOperator(var, new MutableObject<>(keyExpr.cloneExpression()));
+ keyAssignOp.getInputs().add(new MutableObject<>(newLogOp));
+ keyAssignOp.setExecutionMode(newLogOp.getExecutionMode());
+ keyAssignOp.setSourceLocation(newLogOp.getSourceLocation());
+ distinctInput = keyAssignOp;
+ }
+
AbstractLogicalExpression inputVarRef = new VariableReferenceExpression(var, newLogOp.getSourceLocation());
- // add a project operator on top of newLogOp
+ // add a project operator on top of the assign (or of newLogOp when the key is already a variable)
ProjectOperator projOp = new ProjectOperator(var);
projOp.getInputs().add(new MutableObject<>(null)); //add an input
- projOp.getInputs().get(0).setValue(newLogOp);
+ projOp.getInputs().get(0).setValue(distinctInput);
// add a distinct operator on top of the proj.
List<Mutable<ILogicalExpression>> arguments = new ArrayList<>();
VariableReferenceExpression e1 = new VariableReferenceExpression(var);
@@ -1055,6 +1154,18 @@
return helperFunction(newCtx, newAggOp);
}
+ private static List<LogicalVariable> liveVariablesOf(ILogicalOperator op) throws AlgebricksException {
+ List<LogicalVariable> liveVars = new ArrayList<>();
+ VariableUtilities.getLiveVariables(op, liveVars);
+ return liveVars;
+ }
+
+ private static List<LogicalVariable> usedVariablesOf(ILogicalExpression expr) {
+ List<LogicalVariable> usedVars = new ArrayList<>();
+ expr.getUsedVariables(usedVars);
+ return usedVars;
+ }
+
private List<MutableObject> createMutableObjectArray(List<LogicalVariable> vars) {
List<MutableObject> arr = new ArrayList<>();
for (int i = 0; i < vars.size(); i++) {
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/cbo-join/computed-join-predicate.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/cbo-join/computed-join-predicate.sqlpp
new file mode 100644
index 0000000..156b038
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/cbo-join/computed-join-predicate.sqlpp
@@ -0,0 +1,100 @@
+/*
+ * 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.
+ */
+
+
+/*
+ * Description : Plan level guard for a join predicate that is a computed expression over two
+ * : fields of an unnested array, with sample statistics present. The cost based
+ * : optimizer must estimate the selectivity from the samples rather than fail to
+ * : compile or fall back to the default selectivity, which would change the join
+ * : order recorded in the expected plan.
+ * Expected Res : Success
+ */
+
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+-- autogenerated primary keys, so none of the join keys below is a primary key and the cost based
+-- optimizer has to estimate the join selectivities from the samples
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
+
+use test;
+
+INSERT INTO orders (
+ SELECT VALUE {
+ "o_id": i,
+ "o_c_id": i % 300,
+ "o_entry_d": "2016-01-02 00:00:00.000000",
+ "o_orderline": [
+ { "ol_i_id": i % 60, "ol_supply_w_id": i % 9, "ol_quantity": i % 5 },
+ { "ol_i_id": (i + 1) % 60, "ol_supply_w_id": (i + 2) % 9, "ol_quantity": i % 3 }
+ ]
+ }
+ FROM range(1, 2000) i
+);
+
+INSERT INTO item (
+ SELECT VALUE { "i_id": i, "i_data": "xyzab" }
+ FROM range(0, 59) i
+);
+
+INSERT INTO supplier (
+ SELECT VALUE { "su_suppkey": i, "su_nationkey": i % 25 }
+ FROM range(0, 99) i
+);
+
+INSERT INTO nation (
+ SELECT VALUE { "n_nationkey": i, "n_regionkey": i % 5, "n_name": "nation-" || string(i) }
+ FROM range(0, 24) i
+);
+
+INSERT INTO region (
+ SELECT VALUE { "r_regionkey": i, "r_name": "region-" || string(i) }
+ FROM range(0, 4) i
+);
+
+use test;
+
+ANALYZE DATASET orders with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET item with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET supplier with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET nation with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET region with {"sample": "high", "sample-seed": 12345};
+
+use test;
+
+SELECT r.r_name AS region, su.su_nationkey AS supplier_nation,
+ COUNT(DISTINCT ol.ol_i_id) AS unique_items_ordered,
+ COUNT(DISTINCT o.o_c_id) AS unique_customers,
+ SUM(ol.ol_quantity) AS total_quantity_ordered
+FROM orders o, o.o_orderline ol, item i, supplier su, nation n, region r
+WHERE ol.ol_i_id = i.i_id
+ AND i.i_data LIKE '%b'
+ AND ol.ol_supply_w_id * ol.ol_i_id MOD 100 = su.su_suppkey
+ AND su.su_nationkey = n.n_nationkey
+ AND n.n_regionkey = r.r_regionkey
+ AND o.o_entry_d >= '2016-01-01 00:00:00.000000'
+GROUP BY r.r_name, su.su_nationkey
+ORDER BY total_quantity_ordered DESC, region, supplier_nation;
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/cbo-join/computed-join-predicate.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/cbo-join/computed-join-predicate.plan
new file mode 100644
index 0000000..d8c9398
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/cbo-join/computed-join-predicate.plan
@@ -0,0 +1,159 @@
+distribute result [$$188]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$188] <- [{"region": $$r_name, "supplier_nation": $$su_nationkey, "unique_items_ordered": $$201, "unique_customers": $$202, "total_quantity_ordered": $$200}] project: [$$188]
+ -- ASSIGN |PARTITIONED|
+ exchange
+ -- SORT_MERGE_EXCHANGE [$$200(DESC), $$r_name(ASC), $$su_nationkey(ASC) ] |PARTITIONED|
+ order (DESC, $$200) (ASC, $$r_name) (ASC, $$su_nationkey)
+ -- STABLE_SORT [$$200(DESC), $$r_name(ASC), $$su_nationkey(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ group by ([$$r_name := $$189; $$su_nationkey := $$192]) decor ([]) {
+ aggregate [$$200] <- [agg-sql-sum($$175)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$201] <- [agg-sql-count($$191)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$191]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$191) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$191(ASC)] |LOCAL|
+ project ([$$191]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$202] <- [agg-sql-count($$186)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$186]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$186) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$186(ASC)] |LOCAL|
+ project ([$$186]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ -- PRE_CLUSTERED_GROUP_BY[$$189, $$192] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$189) (ASC, $$192)
+ -- STABLE_SORT [$$189(ASC), $$192(ASC)] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$189, $$192] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$189, $$192])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$198, $$199))
+ -- HYBRID_HASH_JOIN [$$198][$$199] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$198] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$192, $$198])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$192, $$203))
+ -- HYBRID_HASH_JOIN [$$192][$$203] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$192] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$192])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$204, $$205))
+ -- HYBRID_HASH_JOIN [$$204][$$205] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$204] |PARTITIONED|
+ assign [$$204] <- [numeric-mod(numeric-multiply($$210, $$191), 100)] project: [$$175, $$191, $$186, $$204]
+ -- ASSIGN |PARTITIONED|
+ project ([$$175, $$191, $$186, $$210])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$191, $$206))
+ -- HYBRID_HASH_JOIN [$$191][$$206] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$191] |PARTITIONED|
+ assign [$$175, $$210, $$191] <- [$$ol.getField("ol_quantity"), $$ol.getField("ol_supply_w_id"), $$ol.getField("ol_i_id")] project: [$$175, $$191, $$186, $$210]
+ -- ASSIGN |PARTITIONED|
+ unnest $$ol <- scan-collection($$208) project: [$$186, $$ol]
+ -- UNNEST |PARTITIONED|
+ select (ge($$o.getField("o_entry_d"), "2016-01-01 00:00:00.000000")) project: [$$208, $$186]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$208, $$186] <- [$$o.getField("o_orderline"), $$o.getField("o_c_id")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$o])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$193, $$o] <- test.orders
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$206] |PARTITIONED|
+ select (like($$i.getField("i_data"), "%b")) project: [$$206]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$206] <- [$$i.getField("i_id")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$i])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$194, $$i] <- test.item
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$205] |PARTITIONED|
+ assign [$$205, $$192] <- [$$su.getField("su_suppkey"), $$su.getField("su_nationkey")] project: [$$192, $$205]
+ -- ASSIGN |PARTITIONED|
+ project ([$$su])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$195, $$su] <- test.supplier
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$203] |PARTITIONED|
+ assign [$$198, $$203] <- [$$n.getField("n_regionkey"), $$n.getField("n_nationkey")] project: [$$198, $$203]
+ -- ASSIGN |PARTITIONED|
+ project ([$$n])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$196, $$n] <- test.nation
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$199] |PARTITIONED|
+ assign [$$189, $$199] <- [$$r.getField("r_name"), $$r.getField("r_regionkey")] project: [$$189, $$199]
+ -- ASSIGN |PARTITIONED|
+ project ([$$r])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$197, $$r] <- test.region
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/cbo-join/computed-join-predicate.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/cbo-join/computed-join-predicate.plan
new file mode 100644
index 0000000..0ec68db
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/cbo-join/computed-join-predicate.plan
@@ -0,0 +1,159 @@
+distribute result [$$188] [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$188] <- [{"region": $$r_name, "supplier_nation": $$su_nationkey, "unique_items_ordered": $$201, "unique_customers": $$202, "total_quantity_ordered": $$200}] project: [$$188] [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (DESC, $$200) (ASC, $$r_name) (ASC, $$su_nationkey) [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$200(DESC), $$r_name(ASC), $$su_nationkey(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- RANGE_PARTITION_EXCHANGE [$$200(DESC), $$r_name(ASC), $$su_nationkey(ASC)] RANGE_MAP:{SPLIT:1} |PARTITIONED|
+ group by ([$$r_name := $$189; $$su_nationkey := $$192]) decor ([]) {
+ aggregate [$$200] <- [agg-sql-sum($$175)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$201] <- [agg-sql-count($$191)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$191]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$191) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$191(ASC)] |LOCAL|
+ project ([$$191]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$202] <- [agg-sql-count($$186)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$186]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$186) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$186(ASC)] |LOCAL|
+ project ([$$186]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- PRE_CLUSTERED_GROUP_BY[$$189, $$192] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$189) (ASC, $$192) [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- STABLE_SORT [$$189(ASC), $$192(ASC)] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$189, $$192] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$189, $$192]) [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$198, $$199)) [cardinality: 0.0, doc-size: -5.0, op-cost: 0.0, total-cost: 0.0]
+ -- HYBRID_HASH_JOIN [$$198][$$199] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -4.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$198] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$192, $$198]) [cardinality: 0.0, doc-size: -4.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -4.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$191, $$206)) [cardinality: 0.0, doc-size: -4.0, op-cost: 0.0, total-cost: 0.0]
+ -- HYBRID_HASH_JOIN [$$191][$$206] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -3.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$191] |PARTITIONED|
+ project ([$$175, $$191, $$186, $$192, $$198]) [cardinality: 0.0, doc-size: -3.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -3.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$214, $$205)) [cardinality: 0.0, doc-size: -3.0, op-cost: 0.0, total-cost: 0.0]
+ -- HYBRID_HASH_JOIN [$$205][$$214] |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -2.0, op-cost: 0.0, total-cost: 0.0]
+ -- HASH_PARTITION_EXCHANGE [$$205] |PARTITIONED|
+ project ([$$192, $$198, $$205]) [cardinality: 0.0, doc-size: -2.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: -2.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$192, $$203)) [cardinality: 0.0, doc-size: -2.0, op-cost: 0.0, total-cost: 0.0]
+ -- HYBRID_HASH_JOIN [$$192][$$203] |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- HASH_PARTITION_EXCHANGE [$$192] |PARTITIONED|
+ assign [$$205, $$192] <- [$$su.getField("su_suppkey"), $$su.getField("su_nationkey")] project: [$$192, $$205] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ project ([$$su]) [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$195, $$su] <- test.supplier [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- HASH_PARTITION_EXCHANGE [$$203] |PARTITIONED|
+ assign [$$198, $$203] <- [$$n.getField("n_regionkey"), $$n.getField("n_nationkey")] project: [$$198, $$203] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ project ([$$n]) [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$196, $$n] <- test.nation [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- HASH_PARTITION_EXCHANGE [$$214] |PARTITIONED|
+ assign [$$214] <- [numeric-mod(numeric-multiply($$ol.getField("ol_supply_w_id"), $$191), 100)] project: [$$175, $$191, $$186, $$214] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ assign [$$175, $$191] <- [$$ol.getField("ol_quantity"), $$ol.getField("ol_i_id")] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ unnest $$ol <- scan-collection($$208) project: [$$186, $$ol] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- UNNEST |PARTITIONED|
+ select (ge($$o.getField("o_entry_d"), "2016-01-01 00:00:00.000000")) project: [$$208, $$186] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$208, $$186] <- [$$o.getField("o_orderline"), $$o.getField("o_c_id")] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ project ([$$o]) [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$193, $$o] <- test.orders [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- HASH_PARTITION_EXCHANGE [$$206] |PARTITIONED|
+ select (like($$i.getField("i_data"), "%b")) project: [$$206] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$206] <- [$$i.getField("i_id")] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ project ([$$i]) [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$194, $$i] <- test.item [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- HASH_PARTITION_EXCHANGE [$$199] |PARTITIONED|
+ assign [$$189, $$199] <- [$$r.getField("r_name"), $$r.getField("r_regionkey")] project: [$$189, $$199] [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ASSIGN |PARTITIONED|
+ project ([$$r]) [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$197, $$r] <- test.region [cardinality: 1000000.0, doc-size: -1.0, op-cost: 0.0, total-cost: 499992.37]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml
index 7c910c9..808c2a6 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/CBOJoinQueries.xml
@@ -61,4 +61,19 @@
<output-dir compare="Text">self-join-advise</output-dir>
</compilation-unit>
</test-case>
+ <test-case FilePath="cbo-join">
+ <compilation-unit name="computed-join-predicate">
+ <output-dir compare="Text">computed-join-predicate</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="cbo-join">
+ <compilation-unit name="computed-join-predicate-sampled-side">
+ <output-dir compare="Text">computed-join-predicate-sampled-side</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="cbo-join">
+ <compilation-unit name="both-unnest-join">
+ <output-dir compare="Text">both-unnest-join</output-dir>
+ </compilation-unit>
+ </test-case>
</test-group>
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.1.ddl.sqlpp
new file mode 100644
index 0000000..f0ef361
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.1.ddl.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+/*
+ * Description : Both sides of a join predicate come from an unnested array. naiveJoinSelectivity
+ * : skips "the side with an array" by taking the other side, but when both sides are
+ * : unnested the other side is an array side too, so the distinct count is measured
+ * : over unnested tuples while the estimator's sample size and cardinality cap are
+ * : both document counts. This pins the estimate that results.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+create dataset collA(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset collB(id uuid not unknown) open type primary key `id` autogenerated;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.2.update.sqlpp
new file mode 100644
index 0000000..33b8b27
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.2.update.sqlpp
@@ -0,0 +1,39 @@
+/*
+ * 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.
+ */
+
+use test;
+
+-- 20000 documents, each holding a 10 element array, so an unnested scan emits 200000 tuples while
+-- the collection holds 20000 documents. The key takes 3000 distinct values across the whole
+-- collection, which is above the 1063 document sample target and far below the document count.
+INSERT INTO collA (
+ SELECT VALUE {
+ "a_id": i,
+ "lines": (SELECT VALUE { "k": (i * 10 + j) % 3000 } FROM range(0, 9) j)
+ }
+ FROM range(1, 20000) i
+);
+
+INSERT INTO collB (
+ SELECT VALUE {
+ "b_id": i,
+ "lines": (SELECT VALUE { "k": (i * 10 + j) % 3000 } FROM range(0, 9) j)
+ }
+ FROM range(1, 20000) i
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.3.update.sqlpp
new file mode 100644
index 0000000..78bf28d
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.3.update.sqlpp
@@ -0,0 +1,23 @@
+/*
+ * 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.
+ */
+
+use test;
+
+ANALYZE DATASET collA with {"sample": "low", "sample-seed": 12345};
+ANALYZE DATASET collB with {"sample": "low", "sample-seed": 12345};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.4.query.sqlpp
new file mode 100644
index 0000000..00ce550
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.4.query.sqlpp
@@ -0,0 +1,35 @@
+/*
+ * 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.
+ */
+
+use test;
+
+-- EXPLAIN only. The join itself is roughly 13 million rows, which is not what this test is about.
+--
+-- The expected result is a regex file. naiveJoinSelectivity samples an unnested leaf input here,
+-- so the distinct count is measured over unnested tuples while the estimator sample size and the
+-- cardinality cap are both document counts. The count ends up clamped to the collection
+-- cardinality of about 20000 rather than the 3000 distinct keys that step 5 measures, which puts
+-- the join cardinality near 2e6 instead of the true 1.3e7. The estimate is matched by order of
+-- magnitude on purpose: the clamp lands on the sample derived source cardinality, which varies by
+-- a fraction of a percent between runs even with a fixed sample-seed, so the exact digits are not
+-- reproducible.
+EXPLAIN
+SELECT COUNT(*) AS c
+FROM collA a, a.lines la, collB b, b.lines lb
+WHERE la.k = lb.k;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.5.query.sqlpp
new file mode 100644
index 0000000..3907bdc
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/both-unnest-join/both-unnest-join.5.query.sqlpp
@@ -0,0 +1,33 @@
+/*
+ * 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.
+ */
+
+
+/*
+ * Description : Ground truth for the estimate pinned in step 4. The unnested scan emits 200000
+ * : tuples from 20000 documents and the join key takes 3000 distinct values, so the
+ * : true join selectivity is 1/3000. Step 4 records what the optimizer estimates
+ * : instead.
+ * Expected Res : Success
+ */
+
+use test;
+
+SELECT COUNT(DISTINCT la.k) AS true_distinct_keys,
+ COUNT(*) AS unnested_tuples
+FROM collA a, a.lines la;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.1.ddl.sqlpp
new file mode 100644
index 0000000..89a7262
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.1.ddl.sqlpp
@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+/*
+ * Description : Mirror of cbo-join/computed-join-predicate: here the computed side of the join
+ * : predicate is the side whose sample the cost based optimizer picks, so the join key
+ * : of the sampled side is an expression over two of its columns rather than a column.
+ * : The distinct count that drives the join selectivity must be the distinct count of
+ * : that expression: supplier holds 100 rows with su_suppkey = i and su_nationkey =
+ * : i % 25, so su_suppkey MOD 10 + su_nationkey MOD 5 takes only 10 distinct values,
+ * : while su_suppkey alone takes 100 and su_nationkey alone takes 25.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+-- autogenerated primary keys, so none of the join keys below is a primary key and the cost based
+-- optimizer has to estimate the join selectivities from the samples
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.2.update.sqlpp
new file mode 100644
index 0000000..95ea6ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.2.update.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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.
+ */
+
+use test;
+
+INSERT INTO orders (
+ SELECT VALUE {
+ "o_id": i,
+ "o_c_id": i % 300,
+ "o_entry_d": "2016-01-02 00:00:00.000000",
+ "o_orderline": [
+ { "ol_i_id": i % 60, "ol_supply_w_id": i % 9, "ol_quantity": i % 5 },
+ { "ol_i_id": (i + 1) % 60, "ol_supply_w_id": (i + 2) % 9, "ol_quantity": i % 3 }
+ ]
+ }
+ FROM range(1, 2000) i
+);
+
+INSERT INTO item (
+ SELECT VALUE { "i_id": i, "i_data": "xyzab" }
+ FROM range(0, 59) i
+);
+
+INSERT INTO supplier (
+ SELECT VALUE { "su_suppkey": i, "su_nationkey": i % 25 }
+ FROM range(0, 99) i
+);
+
+INSERT INTO nation (
+ SELECT VALUE { "n_nationkey": i, "n_regionkey": i % 5, "n_name": "nation-" || string(i) }
+ FROM range(0, 24) i
+);
+
+INSERT INTO region (
+ SELECT VALUE { "r_regionkey": i, "r_name": "region-" || string(i) }
+ FROM range(0, 4) i
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.3.update.sqlpp
new file mode 100644
index 0000000..cf7cf0a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.3.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use test;
+
+ANALYZE DATASET orders with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET item with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET supplier with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET nation with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET region with {"sample": "high", "sample-seed": 12345};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.query.sqlpp
new file mode 100644
index 0000000..5a580ca
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.query.sqlpp
@@ -0,0 +1,42 @@
+/*
+ * 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.
+ */
+
+use test;
+
+-- The unnest of o_orderline makes the cost based optimizer estimate this join from the supplier
+-- sample, and the join key of the supplier side is the computed expression
+-- su_suppkey MOD 10 + su_nationkey MOD 5. Because supplier is joined to nation with an explicit ON
+-- clause, the assign holding that expression cannot be pushed below the join into the supplier leaf
+-- input, so it is inlined into the join predicate and the predicate carries an expression -- not a
+-- variable -- on the side that is sampled. The distinct count that drives the join selectivity must
+-- be the distinct count of that expression (10) rather than of either column it reads (100 and 25).
+SELECT r.r_name AS region, su.su_nationkey AS supplier_nation,
+ COUNT(DISTINCT ol.ol_i_id) AS unique_items_ordered,
+ COUNT(DISTINCT o.o_c_id) AS unique_customers,
+ SUM(ol.ol_quantity) AS total_quantity_ordered
+FROM supplier su
+ JOIN nation n ON su.su_nationkey = n.n_nationkey
+ JOIN region r ON n.n_regionkey = r.r_regionkey,
+ orders o, o.o_orderline ol, item i
+WHERE ol.ol_i_id = i.i_id
+ AND i.i_data LIKE '%b'
+ AND ol.ol_supply_w_id = su.su_suppkey MOD 10 + su.su_nationkey MOD 5
+ AND o.o_entry_d >= '2016-01-01 00:00:00.000000'
+GROUP BY r.r_name, su.su_nationkey
+ORDER BY total_quantity_ordered DESC, region, supplier_nation;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.query.sqlpp
new file mode 100644
index 0000000..3ff8ca5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.query.sqlpp
@@ -0,0 +1,52 @@
+/*
+ * 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.
+ */
+
+
+/*
+ * Description : The plan for the query in step 4. The join selectivity for the computed key
+ * : su_suppkey MOD 10 + su_nationkey MOD 5 must come from the distinct count of that
+ * : expression, not of either column it reads, so the estimated cardinalities below
+ * : pin the behaviour that the rows comparison in step 4 cannot see.
+ * Expected Res : Success
+ */
+
+use test;
+
+-- The unnest of o_orderline makes the cost based optimizer estimate this join from the supplier
+-- sample, and the join key of the supplier side is the computed expression
+-- su_suppkey MOD 10 + su_nationkey MOD 5. Because supplier is joined to nation with an explicit ON
+-- clause, the assign holding that expression cannot be pushed below the join into the supplier leaf
+-- input, so it is inlined into the join predicate and the predicate carries an expression -- not a
+-- variable -- on the side that is sampled. The distinct count that drives the join selectivity must
+-- be the distinct count of that expression (10) rather than of either column it reads (100 and 25).
+EXPLAIN
+SELECT r.r_name AS region, su.su_nationkey AS supplier_nation,
+ COUNT(DISTINCT ol.ol_i_id) AS unique_items_ordered,
+ COUNT(DISTINCT o.o_c_id) AS unique_customers,
+ SUM(ol.ol_quantity) AS total_quantity_ordered
+FROM supplier su
+ JOIN nation n ON su.su_nationkey = n.n_nationkey
+ JOIN region r ON n.n_regionkey = r.r_regionkey,
+ orders o, o.o_orderline ol, item i
+WHERE ol.ol_i_id = i.i_id
+ AND i.i_data LIKE '%b'
+ AND ol.ol_supply_w_id = su.su_suppkey MOD 10 + su.su_nationkey MOD 5
+ AND o.o_entry_d >= '2016-01-01 00:00:00.000000'
+GROUP BY r.r_name, su.su_nationkey
+ORDER BY total_quantity_ordered DESC, region, supplier_nation;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.1.ddl.sqlpp
new file mode 100644
index 0000000..ef1d2e6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.1.ddl.sqlpp
@@ -0,0 +1,38 @@
+/*
+ * 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.
+ */
+
+/*
+ * Description : A join predicate that is a computed expression over two fields of an unnested
+ * : array must compile when sample statistics are present. The variable holding the
+ * : computed expression belongs to a different leaf input than the one whose sample
+ * : the cost based optimizer picks, and the sampling plan must not be built over it.
+ * Expected Res : Success
+ */
+
+drop dataverse test if exists;
+create dataverse test;
+use test;
+
+-- autogenerated primary keys, so none of the join keys below is a primary key and the cost based
+-- optimizer has to estimate the join selectivities from the samples
+create dataset orders(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset item(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset supplier(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset nation(id uuid not unknown) open type primary key `id` autogenerated;
+create dataset region(id uuid not unknown) open type primary key `id` autogenerated;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.2.update.sqlpp
new file mode 100644
index 0000000..95ea6ae
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.2.update.sqlpp
@@ -0,0 +1,53 @@
+/*
+ * 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.
+ */
+
+use test;
+
+INSERT INTO orders (
+ SELECT VALUE {
+ "o_id": i,
+ "o_c_id": i % 300,
+ "o_entry_d": "2016-01-02 00:00:00.000000",
+ "o_orderline": [
+ { "ol_i_id": i % 60, "ol_supply_w_id": i % 9, "ol_quantity": i % 5 },
+ { "ol_i_id": (i + 1) % 60, "ol_supply_w_id": (i + 2) % 9, "ol_quantity": i % 3 }
+ ]
+ }
+ FROM range(1, 2000) i
+);
+
+INSERT INTO item (
+ SELECT VALUE { "i_id": i, "i_data": "xyzab" }
+ FROM range(0, 59) i
+);
+
+INSERT INTO supplier (
+ SELECT VALUE { "su_suppkey": i, "su_nationkey": i % 25 }
+ FROM range(0, 99) i
+);
+
+INSERT INTO nation (
+ SELECT VALUE { "n_nationkey": i, "n_regionkey": i % 5, "n_name": "nation-" || string(i) }
+ FROM range(0, 24) i
+);
+
+INSERT INTO region (
+ SELECT VALUE { "r_regionkey": i, "r_name": "region-" || string(i) }
+ FROM range(0, 4) i
+);
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.3.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.3.update.sqlpp
new file mode 100644
index 0000000..cf7cf0a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.3.update.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+use test;
+
+ANALYZE DATASET orders with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET item with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET supplier with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET nation with {"sample": "high", "sample-seed": 12345};
+ANALYZE DATASET region with {"sample": "high", "sample-seed": 12345};
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.4.query.sqlpp
new file mode 100644
index 0000000..c074c59
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/cbo-join/computed-join-predicate/computed-join-predicate.4.query.sqlpp
@@ -0,0 +1,34 @@
+/*
+ * 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.
+ */
+
+use test;
+
+SELECT r.r_name AS region, su.su_nationkey AS supplier_nation,
+ COUNT(DISTINCT ol.ol_i_id) AS unique_items_ordered,
+ COUNT(DISTINCT o.o_c_id) AS unique_customers,
+ SUM(ol.ol_quantity) AS total_quantity_ordered
+FROM orders o, o.o_orderline ol, item i, supplier su, nation n, region r
+WHERE ol.ol_i_id = i.i_id
+ AND i.i_data LIKE '%b'
+ AND ol.ol_supply_w_id * ol.ol_i_id MOD 100 = su.su_suppkey
+ AND su.su_nationkey = n.n_nationkey
+ AND n.n_regionkey = r.r_regionkey
+ AND o.o_entry_d >= '2016-01-01 00:00:00.000000'
+GROUP BY r.r_name, su.su_nationkey
+ORDER BY total_quantity_ordered DESC, region, supplier_nation;
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.4.regex b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.4.regex
new file mode 100644
index 0000000..a513809
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.4.regex
@@ -0,0 +1,3 @@
+/-- HYBRID_HASH_JOIN/
+/join \(eq\(\$\$[0-9]+, \$\$[0-9]+\)\) \[cardinality: [0-9]{7}\.[0-9]+/
+-/join \(eq\(\$\$[0-9]+, \$\$[0-9]+\)\) \[cardinality: [0-9]{8,}/
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.5.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.5.adm
new file mode 100644
index 0000000..e238f54
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/both-unnest-join/both-unnest-join.5.adm
@@ -0,0 +1 @@
+{ "true_distinct_keys": 3000, "unnested_tuples": 200000 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.adm
new file mode 100644
index 0000000..fe5c1f9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.4.adm
@@ -0,0 +1,25 @@
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-1", "supplier_nation": 1, "total_quantity_ordered": 2660 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-1", "supplier_nation": 6, "total_quantity_ordered": 2660 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-1", "supplier_nation": 11, "total_quantity_ordered": 2660 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-1", "supplier_nation": 16, "total_quantity_ordered": 2660 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-1", "supplier_nation": 21, "total_quantity_ordered": 2660 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-0", "supplier_nation": 0, "total_quantity_ordered": 2226 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-0", "supplier_nation": 5, "total_quantity_ordered": 2226 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-0", "supplier_nation": 10, "total_quantity_ordered": 2226 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-0", "supplier_nation": 15, "total_quantity_ordered": 2226 }
+{ "unique_items_ordered": 60, "unique_customers": 300, "region": "region-0", "supplier_nation": 20, "total_quantity_ordered": 2226 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-2", "supplier_nation": 2, "total_quantity_ordered": 1786 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-2", "supplier_nation": 7, "total_quantity_ordered": 1786 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-2", "supplier_nation": 12, "total_quantity_ordered": 1786 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-2", "supplier_nation": 17, "total_quantity_ordered": 1786 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-2", "supplier_nation": 22, "total_quantity_ordered": 1786 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-3", "supplier_nation": 3, "total_quantity_ordered": 1326 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-3", "supplier_nation": 8, "total_quantity_ordered": 1326 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-3", "supplier_nation": 13, "total_quantity_ordered": 1326 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-3", "supplier_nation": 18, "total_quantity_ordered": 1326 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-3", "supplier_nation": 23, "total_quantity_ordered": 1326 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-4", "supplier_nation": 4, "total_quantity_ordered": 890 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-4", "supplier_nation": 9, "total_quantity_ordered": 890 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-4", "supplier_nation": 14, "total_quantity_ordered": 890 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-4", "supplier_nation": 19, "total_quantity_ordered": 890 }
+{ "unique_items_ordered": 40, "unique_customers": 200, "region": "region-4", "supplier_nation": 24, "total_quantity_ordered": 890 }
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.plan b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.plan
new file mode 100644
index 0000000..c1afc5a
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate-sampled-side/computed-join-predicate-sampled-side.5.plan
@@ -0,0 +1,155 @@
+distribute result [$$190] [cardinality: 9600.0, doc-size: 63.0, op-cost: 0.0, total-cost: 1316653.61]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange [cardinality: 9600.0, doc-size: 63.0, op-cost: 0.0, total-cost: 1316653.61]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$190] <- [{"region": $$r_name, "supplier_nation": $$su_nationkey, "unique_items_ordered": $$205, "unique_customers": $$206, "total_quantity_ordered": $$204}] project: [$$190] [cardinality: 9600.0, doc-size: 63.0, op-cost: 0.0, total-cost: 1316653.61]
+ -- ASSIGN |PARTITIONED|
+ exchange [cardinality: 9600.0, doc-size: 63.0, op-cost: 0.0, total-cost: 1316653.61]
+ -- SORT_MERGE_EXCHANGE [$$204(DESC), $$r_name(ASC), $$su_nationkey(ASC) ] |PARTITIONED|
+ order (DESC, $$204) (ASC, $$r_name) (ASC, $$su_nationkey) [cardinality: 9600.0, doc-size: 63.0, op-cost: 31749.16, total-cost: 1316653.61]
+ -- STABLE_SORT [$$204(DESC), $$r_name(ASC), $$su_nationkey(ASC)] |PARTITIONED|
+ exchange [cardinality: 9600.0, doc-size: 63.0, op-cost: 0.0, total-cost: 1284904.45]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ group by ([$$r_name := $$191; $$su_nationkey := $$193]) decor ([]) {
+ aggregate [$$204] <- [agg-sql-sum($$177)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$205] <- [agg-sql-count($$202)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$202]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$202) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$202(ASC)] |LOCAL|
+ project ([$$202]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ }
+ {
+ aggregate [$$206] <- [agg-sql-count($$188)] [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- AGGREGATE |LOCAL|
+ distinct ([$$188]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_PRE_SORTED_DISTINCT_BY |LOCAL|
+ order (ASC, $$188) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- MICRO_STABLE_SORT [$$188(ASC)] |LOCAL|
+ project ([$$188]) [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- STREAM_PROJECT |LOCAL|
+ nested tuple source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- NESTED_TUPLE_SOURCE |LOCAL|
+ } [cardinality: 9600.0, doc-size: 63.0, op-cost: 632786.64, total-cost: 1284904.45]
+ -- PRE_CLUSTERED_GROUP_BY[$$191, $$193] |PARTITIONED|
+ exchange [cardinality: 40000.0, doc-size: 63.0, op-cost: 0.0, total-cost: 652117.81]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ order (ASC, $$191) (ASC, $$193) [cardinality: 40000.0, doc-size: 63.0, op-cost: 632786.64, total-cost: 652117.81]
+ -- STABLE_SORT [$$191(ASC), $$193(ASC)] |PARTITIONED|
+ exchange [cardinality: 40000.0, doc-size: 63.0, op-cost: 0.0, total-cost: 19331.17]
+ -- HASH_PARTITION_EXCHANGE [$$191, $$193] |PARTITIONED|
+ project ([$$177, $$202, $$188, $$191, $$193]) [cardinality: 40000.0, doc-size: 63.0, op-cost: 0.0, total-cost: 19331.17]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 40000.0, doc-size: 63.0, op-cost: 0.0, total-cost: 19331.17]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (and(eq($$202, $$203), eq($$207, $$216))) [cardinality: 40000.0, doc-size: 63.0, op-cost: 12500.0, total-cost: 19331.17]
+ -- HYBRID_HASH_JOIN [$$203, $$216][$$202, $$207] |PARTITIONED|
+ exchange [cardinality: 6000.0, doc-size: 45.0, op-cost: 0.0, total-cost: 2825.17]
+ -- HASH_PARTITION_EXCHANGE [$$203, $$216] |PARTITIONED|
+ assign [$$216] <- [numeric-add(numeric-mod($$209, 10), numeric-mod($$193, 5))] project: [$$191, $$193, $$203, $$216] [cardinality: 6000.0, doc-size: 45.0, op-cost: 0.0, total-cost: 2825.17]
+ -- ASSIGN |PARTITIONED|
+ project ([$$191, $$203, $$193, $$209]) [cardinality: 6000.0, doc-size: 45.0, op-cost: 0.0, total-cost: 2825.17]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 6000.0, doc-size: 45.0, op-cost: 0.0, total-cost: 2825.17]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$193, $$199)) [cardinality: 6000.0, doc-size: 45.0, op-cost: 1975.0, total-cost: 2825.17]
+ -- HYBRID_HASH_JOIN [$$199][$$193] |PARTITIONED|
+ exchange [cardinality: 1500.0, doc-size: 36.0, op-cost: 0.0, total-cost: 700.08]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$191, $$203, $$199]) [cardinality: 1500.0, doc-size: 36.0, op-cost: 0.0, total-cost: 700.08]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 1500.0, doc-size: 36.0, op-cost: 0.0, total-cost: 700.08]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (eq($$200, $$201)) [cardinality: 1500.0, doc-size: 36.0, op-cost: 475.0, total-cost: 700.08]
+ -- HYBRID_HASH_JOIN [$$201][$$200] |PARTITIONED|
+ exchange [cardinality: 300.0, doc-size: 18.0, op-cost: 0.0, total-cost: 187.55]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ join (true) [cardinality: 300.0, doc-size: 18.0, op-cost: 150.0, total-cost: 187.55]
+ -- NESTED_LOOP |PARTITIONED|
+ exchange [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ select (like($$i.getField("i_data"), "%b")) project: [$$203] [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$203] <- [$$i.getField("i_id")] [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- ASSIGN |PARTITIONED|
+ project ([$$i]) [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$198, $$i] <- test.item [cardinality: 60.0, doc-size: 9.0, op-cost: 0.0, total-cost: 30.05]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 5.0, doc-size: 9.0, op-cost: 0.0, total-cost: 2.5]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$191, $$201] <- [$$r.getField("r_name"), $$r.getField("r_regionkey")] project: [$$191, $$201] [cardinality: 5.0, doc-size: 9.0, op-cost: 0.0, total-cost: 2.5]
+ -- ASSIGN |PARTITIONED|
+ project ([$$r]) [cardinality: 5.0, doc-size: 9.0, op-cost: 0.0, total-cost: 2.5]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 5.0, doc-size: 9.0, op-cost: 0.0, total-cost: 2.5]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$196, $$r] <- test.region [cardinality: 5.0, doc-size: 9.0, op-cost: 0.0, total-cost: 2.5]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 25.0, doc-size: 18.0, op-cost: 0.0, total-cost: 12.53]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$200, $$199] <- [$$n.getField("n_regionkey"), $$n.getField("n_nationkey")] project: [$$199, $$200] [cardinality: 25.0, doc-size: 18.0, op-cost: 0.0, total-cost: 12.53]
+ -- ASSIGN |PARTITIONED|
+ project ([$$n]) [cardinality: 25.0, doc-size: 18.0, op-cost: 0.0, total-cost: 12.53]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 25.0, doc-size: 18.0, op-cost: 0.0, total-cost: 12.53]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$195, $$n] <- test.nation [cardinality: 25.0, doc-size: 18.0, op-cost: 0.0, total-cost: 12.53]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 100.0, doc-size: 9.0, op-cost: 0.0, total-cost: 50.09]
+ -- BROADCAST_EXCHANGE |PARTITIONED|
+ assign [$$209, $$193] <- [$$su.getField("su_suppkey"), $$su.getField("su_nationkey")] project: [$$193, $$209] [cardinality: 100.0, doc-size: 9.0, op-cost: 0.0, total-cost: 50.09]
+ -- ASSIGN |PARTITIONED|
+ project ([$$su]) [cardinality: 100.0, doc-size: 9.0, op-cost: 0.0, total-cost: 50.09]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 100.0, doc-size: 9.0, op-cost: 0.0, total-cost: 50.09]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$194, $$su] <- test.supplier [cardinality: 100.0, doc-size: 9.0, op-cost: 0.0, total-cost: 50.09]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- HASH_PARTITION_EXCHANGE [$$202, $$207] |PARTITIONED|
+ assign [$$177, $$202, $$207] <- [$$ol.getField("ol_quantity"), $$ol.getField("ol_i_id"), $$ol.getField("ol_supply_w_id")] project: [$$177, $$202, $$188, $$207] [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- ASSIGN |PARTITIONED|
+ unnest $$ol <- scan-collection($$211) project: [$$188, $$ol] [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- UNNEST |PARTITIONED|
+ select (ge($$o.getField("o_entry_d"), "2016-01-01 00:00:00.000000")) project: [$$211, $$188] [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- STREAM_SELECT |PARTITIONED|
+ assign [$$211, $$188] <- [$$o.getField("o_orderline"), $$o.getField("o_c_id")] [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- ASSIGN |PARTITIONED|
+ project ([$$o]) [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$197, $$o] <- test.orders [cardinality: 2000.0, doc-size: 18.0, op-cost: 0.0, total-cost: 1506.0]
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source [cardinality: 0.0, doc-size: 0.0, op-cost: 0.0, total-cost: 0.0]
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate/computed-join-predicate.4.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate/computed-join-predicate.4.adm
new file mode 100644
index 0000000..e2ed633
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/cbo-join/computed-join-predicate/computed-join-predicate.4.adm
@@ -0,0 +1,25 @@
+{ "unique_items_ordered": 44, "unique_customers": 225, "region": "region-0", "supplier_nation": 0, "total_quantity_ordered": 757 }
+{ "unique_items_ordered": 12, "unique_customers": 60, "region": "region-4", "supplier_nation": 9, "total_quantity_ordered": 305 }
+{ "unique_items_ordered": 13, "unique_customers": 60, "region": "region-4", "supplier_nation": 24, "total_quantity_ordered": 277 }
+{ "unique_items_ordered": 12, "unique_customers": 60, "region": "region-4", "supplier_nation": 4, "total_quantity_ordered": 266 }
+{ "unique_items_ordered": 12, "unique_customers": 55, "region": "region-2", "supplier_nation": 12, "total_quantity_ordered": 256 }
+{ "unique_items_ordered": 12, "unique_customers": 60, "region": "region-3", "supplier_nation": 18, "total_quantity_ordered": 255 }
+{ "unique_items_ordered": 12, "unique_customers": 55, "region": "region-3", "supplier_nation": 8, "total_quantity_ordered": 254 }
+{ "unique_items_ordered": 12, "unique_customers": 60, "region": "region-3", "supplier_nation": 3, "total_quantity_ordered": 253 }
+{ "unique_items_ordered": 12, "unique_customers": 55, "region": "region-2", "supplier_nation": 2, "total_quantity_ordered": 245 }
+{ "unique_items_ordered": 16, "unique_customers": 80, "region": "region-0", "supplier_nation": 20, "total_quantity_ordered": 235 }
+{ "unique_items_ordered": 12, "unique_customers": 55, "region": "region-1", "supplier_nation": 1, "total_quantity_ordered": 232 }
+{ "unique_items_ordered": 10, "unique_customers": 50, "region": "region-2", "supplier_nation": 22, "total_quantity_ordered": 232 }
+{ "unique_items_ordered": 11, "unique_customers": 50, "region": "region-1", "supplier_nation": 21, "total_quantity_ordered": 231 }
+{ "unique_items_ordered": 10, "unique_customers": 45, "region": "region-3", "supplier_nation": 13, "total_quantity_ordered": 231 }
+{ "unique_items_ordered": 13, "unique_customers": 55, "region": "region-1", "supplier_nation": 6, "total_quantity_ordered": 221 }
+{ "unique_items_ordered": 12, "unique_customers": 60, "region": "region-2", "supplier_nation": 7, "total_quantity_ordered": 209 }
+{ "unique_items_ordered": 11, "unique_customers": 55, "region": "region-1", "supplier_nation": 16, "total_quantity_ordered": 202 }
+{ "unique_items_ordered": 9, "unique_customers": 45, "region": "region-4", "supplier_nation": 19, "total_quantity_ordered": 202 }
+{ "unique_items_ordered": 16, "unique_customers": 90, "region": "region-0", "supplier_nation": 15, "total_quantity_ordered": 187 }
+{ "unique_items_ordered": 9, "unique_customers": 45, "region": "region-1", "supplier_nation": 11, "total_quantity_ordered": 181 }
+{ "unique_items_ordered": 10, "unique_customers": 45, "region": "region-2", "supplier_nation": 17, "total_quantity_ordered": 167 }
+{ "unique_items_ordered": 10, "unique_customers": 50, "region": "region-4", "supplier_nation": 14, "total_quantity_ordered": 157 }
+{ "unique_items_ordered": 10, "unique_customers": 50, "region": "region-3", "supplier_nation": 23, "total_quantity_ordered": 156 }
+{ "unique_items_ordered": 17, "unique_customers": 95, "region": "region-0", "supplier_nation": 10, "total_quantity_ordered": 155 }
+{ "unique_items_ordered": 16, "unique_customers": 90, "region": "region-0", "supplier_nation": 5, "total_quantity_ordered": 135 }
--
To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/21551?usp=email
To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings?usp=email
Gerrit-MessageType: newchange
Gerrit-Project: asterixdb
Gerrit-Branch: master
Gerrit-Change-Id: I604d0ab81473b7be32dc2f098abfa122f3b52523
Gerrit-Change-Number: 21551
Gerrit-PatchSet: 1
Gerrit-Owner: Ritik Raj <ri...@couchbase.com>