You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by tl...@apache.org on 2021/08/31 15:51:35 UTC
[ignite] branch sql-calcite updated: IGNITE-14808 Calcite.
RIGHT|FULL Join operations are lost nulls sort ordering. (#9143)
This is an automated email from the ASF dual-hosted git repository.
tledkov pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new 6383bd5 IGNITE-14808 Calcite. RIGHT|FULL Join operations are lost nulls sort ordering. (#9143)
6383bd5 is described below
commit 6383bd5094a9288367fd0495094a2fa903690794
Author: Evgeniy Stanilovskiy <st...@gmail.com>
AuthorDate: Tue Aug 31 18:51:04 2021 +0300
IGNITE-14808 Calcite. RIGHT|FULL Join operations are lost nulls sort ordering. (#9143)
---
.../query/calcite/CalciteQueryProcessor.java | 2 +
.../query/calcite/metadata/IgniteMdCollation.java | 172 +-
.../query/calcite/rel/AbstractIgniteJoin.java | 11 +-
.../query/calcite/rel/IgniteMergeJoin.java | 175 +-
.../query/calcite/rule/MergeJoinConverterRule.java | 3 +-
.../query/calcite/schema/SchemaHolderImpl.java | 15 +-
.../CalciteBasicSecondaryIndexIntegrationTest.java | 4 +-
.../query/calcite/CalciteQueryProcessorTest.java | 124 +-
.../processors/query/calcite/QueryChecker.java | 2 +-
.../calcite/integration/JoinIntegrationTest.java | 730 +++++
.../calcite/planner/LimitOffsetPlannerTest.java | 23 +-
.../calcite/planner/MergeJoinPlannerTest.java | 2799 ++++++++++++++++++++
.../query/calcite/planner/PlannerTest.java | 57 +-
.../ignite/testsuites/IntegrationTestSuite.java | 2 +
.../apache/ignite/testsuites/PlannerTestSuite.java | 2 +
.../sql/join/full_outer/test_full_outer_join.test | 8 +-
.../src/test/sql/join/inner/test_eq_ineq_join.test | 12 +-
17 files changed, 3778 insertions(+), 363 deletions(-)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
index 4cd3bfa..7b4a909 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessor.java
@@ -20,6 +20,7 @@ package org.apache.ignite.internal.processors.query.calcite;
import java.util.List;
import org.apache.calcite.DataContexts;
import org.apache.calcite.config.Lex;
+import org.apache.calcite.config.NullCollation;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.ConventionTraitDef;
import org.apache.calcite.plan.RelTraitDef;
@@ -100,6 +101,7 @@ public class CalciteQueryProcessor extends GridProcessorAdapter implements Query
.withConformance(IgniteSqlConformance.INSTANCE))
.sqlValidatorConfig(SqlValidator.Config.DEFAULT
.withIdentifierExpansion(true)
+ .withDefaultNullCollation(NullCollation.LOW)
.withSqlConformance(IgniteSqlConformance.INSTANCE))
// Dialects support.
.operatorTable(SqlOperatorTables.chain(
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/metadata/IgniteMdCollation.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/metadata/IgniteMdCollation.java
index 8bc922e..c139c15 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/metadata/IgniteMdCollation.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/metadata/IgniteMdCollation.java
@@ -26,14 +26,11 @@ import java.util.Objects;
import java.util.SortedSet;
import java.util.TreeSet;
import java.util.stream.Collectors;
+
import com.google.common.collect.ImmutableList;
import com.google.common.collect.LinkedListMultimap;
import com.google.common.collect.Multimap;
import com.google.common.collect.Ordering;
-import org.apache.calcite.adapter.enumerable.EnumerableCorrelate;
-import org.apache.calcite.adapter.enumerable.EnumerableHashJoin;
-import org.apache.calcite.adapter.enumerable.EnumerableMergeJoin;
-import org.apache.calcite.adapter.enumerable.EnumerableNestedLoopJoin;
import org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.plan.RelOptTable;
@@ -46,8 +43,6 @@ import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Calc;
import org.apache.calcite.rel.core.Filter;
-import org.apache.calcite.rel.core.Join;
-import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Match;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.Sort;
@@ -73,7 +68,6 @@ import org.apache.calcite.rex.RexProgram;
import org.apache.calcite.sql.validate.SqlMonotonicity;
import org.apache.calcite.util.BuiltInMethod;
import org.apache.calcite.util.ImmutableBitSet;
-import org.apache.calcite.util.ImmutableIntList;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.Util;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteRel;
@@ -164,36 +158,6 @@ public class IgniteMdCollation implements MetadataHandler<BuiltInMetadata.Collat
return ImmutableList.copyOf(table(scan.getTable()));
}
- public ImmutableList<RelCollation> collations(EnumerableMergeJoin join,
- RelMetadataQuery mq) {
- // In general a join is not sorted. But a merge join preserves the sort
- // order of the left and right sides.
- return ImmutableList.copyOf(
- RelMdCollation.mergeJoin(mq, join.getLeft(), join.getRight(),
- join.analyzeCondition().leftKeys, join.analyzeCondition().rightKeys,
- join.getJoinType()));
- }
-
- public ImmutableList<RelCollation> collations(EnumerableHashJoin join,
- RelMetadataQuery mq) {
- return ImmutableList.copyOf(
- RelMdCollation.enumerableHashJoin(mq, join.getLeft(), join.getRight(), join.getJoinType()));
- }
-
- public ImmutableList<RelCollation> collations(EnumerableNestedLoopJoin join,
- RelMetadataQuery mq) {
- return ImmutableList.copyOf(
- RelMdCollation.enumerableNestedLoopJoin(mq, join.getLeft(), join.getRight(),
- join.getJoinType()));
- }
-
- public ImmutableList<RelCollation> collations(EnumerableCorrelate join,
- RelMetadataQuery mq) {
- return ImmutableList.copyOf(
- RelMdCollation.enumerableCorrelate(mq, join.getLeft(), join.getRight(),
- join.getJoinType()));
- }
-
public ImmutableList<RelCollation> collations(Sort sort,
RelMetadataQuery mq) {
return ImmutableList.copyOf(
@@ -249,30 +213,6 @@ public class IgniteMdCollation implements MetadataHandler<BuiltInMetadata.Collat
}
/** Helper method to determine a
- * {@link org.apache.calcite.rel.core.Snapshot}'s collation. */
- public static List<RelCollation> snapshot(RelMetadataQuery mq, RelNode input) {
- return mq.collations(input);
- }
-
- /** Helper method to determine a
- * {@link org.apache.calcite.rel.core.Sort}'s collation. */
- public static List<RelCollation> sort(RelCollation collation) {
- return ImmutableList.of(collation);
- }
-
- /** Helper method to determine a
- * {@link org.apache.calcite.rel.core.Filter}'s collation. */
- public static List<RelCollation> filter(RelMetadataQuery mq, RelNode input) {
- return mq.collations(input);
- }
-
- /** Helper method to determine a
- * limit's collation. */
- public static List<RelCollation> limit(RelMetadataQuery mq, RelNode input) {
- return mq.collations(input);
- }
-
- /** Helper method to determine a
* {@link org.apache.calcite.rel.core.Calc}'s collation. */
public static List<RelCollation> calc(RelMetadataQuery mq, RelNode input,
RexProgram program) {
@@ -447,114 +387,4 @@ public class IgniteMdCollation implements MetadataHandler<BuiltInMetadata.Collat
};
}
}
-
- /** Helper method to determine a {@link Join}'s collation assuming that it
- * uses a merge-join algorithm.
- *
- * <p>If the inputs are sorted on other keys <em>in addition to</em> the join
- * key, the result preserves those collations too.
- * @deprecated Use {@link #mergeJoin(RelMetadataQuery, RelNode, RelNode, ImmutableIntList, ImmutableIntList, JoinRelType)} */
- @Deprecated // to be removed before 2.0
- public static List<RelCollation> mergeJoin(RelMetadataQuery mq,
- RelNode left, RelNode right,
- ImmutableIntList leftKeys, ImmutableIntList rightKeys) {
- return mergeJoin(mq, left, right, leftKeys, rightKeys, JoinRelType.INNER);
- }
-
- /** Helper method to determine a {@link Join}'s collation assuming that it
- * uses a merge-join algorithm.
- *
- * <p>If the inputs are sorted on other keys <em>in addition to</em> the join
- * key, the result preserves those collations too. */
- public static List<RelCollation> mergeJoin(RelMetadataQuery mq,
- RelNode left, RelNode right,
- ImmutableIntList leftKeys, ImmutableIntList rightKeys, JoinRelType joinType) {
- assert EnumerableMergeJoin.isMergeJoinSupported(joinType)
- : "EnumerableMergeJoin unsupported for join type " + joinType;
-
- final ImmutableList<RelCollation> leftCollations = mq.collations(left);
- assert RelCollations.contains(leftCollations, leftKeys)
- : "cannot merge join: left input is not sorted on left keys";
- if (!joinType.projectsRight()) {
- return leftCollations;
- }
-
- final ImmutableList.Builder<RelCollation> builder = ImmutableList.builder();
- builder.addAll(leftCollations);
-
- final ImmutableList<RelCollation> rightCollations = mq.collations(right);
- assert RelCollations.contains(rightCollations, rightKeys)
- : "cannot merge join: right input is not sorted on right keys";
- final int leftFieldCount = left.getRowType().getFieldCount();
- for (RelCollation collation : rightCollations) {
- builder.add(RelCollations.shift(collation, leftFieldCount));
- }
- return builder.build();
- }
-
- /**
- * Returns the collation of {@link EnumerableHashJoin} based on its inputs and the join type.
- */
- public static List<RelCollation> enumerableHashJoin(RelMetadataQuery mq,
- RelNode left, RelNode right, JoinRelType joinType) {
- if (joinType == JoinRelType.SEMI) {
- return enumerableSemiJoin(mq, left, right);
- } else {
- return enumerableJoin0(mq, left, right, joinType);
- }
- }
-
- /**
- * Returns the collation of {@link EnumerableNestedLoopJoin}
- * based on its inputs and the join type.
- */
- public static List<RelCollation> enumerableNestedLoopJoin(RelMetadataQuery mq,
- RelNode left, RelNode right, JoinRelType joinType) {
- return enumerableJoin0(mq, left, right, joinType);
- }
-
- public static List<RelCollation> enumerableCorrelate(RelMetadataQuery mq,
- RelNode left, RelNode right, JoinRelType joinType) {
- // The current implementation always preserve the sort order of the left input
- return mq.collations(left);
- }
-
- public static List<RelCollation> enumerableSemiJoin(RelMetadataQuery mq,
- RelNode left, RelNode right) {
- // The current implementation always preserve the sort order of the left input
- return mq.collations(left);
- }
-
- public static List<RelCollation> enumerableBatchNestedLoopJoin(RelMetadataQuery mq,
- RelNode left, RelNode right, JoinRelType joinType) {
- // The current implementation always preserve the sort order of the left input
- return mq.collations(left);
- }
-
- private static List<RelCollation> enumerableJoin0(RelMetadataQuery mq,
- RelNode left, RelNode right, JoinRelType joinType) {
- // The current implementation can preserve the sort order of the left input if one of the
- // following conditions hold:
- // (i) join type is INNER or LEFT;
- // (ii) RelCollation always orders nulls last.
- final ImmutableList<RelCollation> leftCollations = mq.collations(left);
- switch (joinType) {
- case SEMI:
- case ANTI:
- case INNER:
- case LEFT:
- return leftCollations;
- case RIGHT:
- case FULL:
- for (RelCollation collation : leftCollations) {
- for (RelFieldCollation field : collation.getFieldCollations()) {
- if (!(RelFieldCollation.NullDirection.LAST == field.nullDirection)) {
- return ImmutableList.of();
- }
- }
- }
- return leftCollations;
- }
- return ImmutableList.of();
- }
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/AbstractIgniteJoin.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/AbstractIgniteJoin.java
index 6d48374..c172e0b 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/AbstractIgniteJoin.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/AbstractIgniteJoin.java
@@ -87,10 +87,9 @@ public abstract class AbstractIgniteJoin extends Join implements TraitsAwareIgni
RelCollation collation = TraitUtils.collation(left);
// If nulls are possible at left we has to check whether NullDirection.LAST flag is set on sorted fields.
- // TODO set NullDirection.LAST for insufficient fields instead of erasing collation.
if (joinType == RIGHT || joinType == JoinRelType.FULL) {
for (RelFieldCollation field : collation.getFieldCollations()) {
- if (RelFieldCollation.NullDirection.LAST != field.nullDirection) {
+ if (RelFieldCollation.NullDirection.LAST.nullComparison != field.nullDirection.nullComparison) {
collation = RelCollations.EMPTY;
break;
}
@@ -143,8 +142,8 @@ public abstract class AbstractIgniteJoin extends Join implements TraitsAwareIgni
IgniteDistribution leftDistr = TraitUtils.distribution(left);
IgniteDistribution rightDistr = TraitUtils.distribution(right);
- IgniteDistribution left2rightProjectedDistr = leftDistr.apply(buildProjectionMapping(true));
- IgniteDistribution right2leftProjectedDistr = rightDistr.apply(buildProjectionMapping(false));
+ IgniteDistribution left2rightProjectedDistr = leftDistr.apply(buildTransposeMapping(true));
+ IgniteDistribution right2leftProjectedDistr = rightDistr.apply(buildTransposeMapping(false));
RelTraitSet outTraits;
RelTraitSet leftTraits;
@@ -223,7 +222,7 @@ public abstract class AbstractIgniteJoin extends Join implements TraitsAwareIgni
collation = RelCollations.EMPTY;
else if (joinType == RIGHT || joinType == JoinRelType.FULL) {
for (RelFieldCollation field : collation.getFieldCollations()) {
- if (RelFieldCollation.NullDirection.LAST != field.nullDirection) {
+ if (RelFieldCollation.NullDirection.LAST.nullComparison != field.nullDirection.nullComparison) {
collation = RelCollations.EMPTY;
break;
}
@@ -301,7 +300,7 @@ public abstract class AbstractIgniteJoin extends Join implements TraitsAwareIgni
}
/** Creates mapping from left join keys to the right and vice versa with regards to {@code left2Right}. */
- protected Mappings.TargetMapping buildProjectionMapping(boolean left2Right) {
+ protected Mappings.TargetMapping buildTransposeMapping(boolean left2Right) {
ImmutableIntList sourceKeys = left2Right ? joinInfo.leftKeys : joinInfo.rightKeys;
ImmutableIntList targetKeys = left2Right ? joinInfo.rightKeys : joinInfo.leftKeys;
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/IgniteMergeJoin.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/IgniteMergeJoin.java
index 38b6811..d57c5c3 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/IgniteMergeJoin.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rel/IgniteMergeJoin.java
@@ -18,11 +18,8 @@
package org.apache.ignite.internal.processors.query.calcite.rel;
import java.util.ArrayList;
-import java.util.HashSet;
import java.util.List;
-import java.util.Map;
import java.util.Set;
-import java.util.stream.Collectors;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableSet;
@@ -32,6 +29,7 @@ import org.apache.calcite.plan.RelOptPlanner;
import org.apache.calcite.plan.RelTraitSet;
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.RelInput;
import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.RelWriter;
@@ -40,6 +38,7 @@ import org.apache.calcite.rel.core.Join;
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexNode;
+import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Pair;
import org.apache.ignite.internal.processors.query.calcite.externalize.RelInputEx;
import org.apache.ignite.internal.processors.query.calcite.metadata.cost.IgniteCost;
@@ -47,9 +46,11 @@ import org.apache.ignite.internal.processors.query.calcite.metadata.cost.IgniteC
import org.apache.ignite.internal.processors.query.calcite.trait.TraitUtils;
import org.apache.ignite.internal.processors.query.calcite.util.Commons;
-import static org.apache.ignite.internal.processors.query.calcite.trait.TraitUtils.createCollation;
-import static org.apache.ignite.internal.processors.query.calcite.util.Commons.isPrefix;
-import static org.apache.ignite.internal.processors.query.calcite.util.Commons.maxPrefix;
+import static org.apache.calcite.rel.RelCollations.EMPTY;
+import static org.apache.calcite.rel.RelCollations.containsOrderless;
+import static org.apache.calcite.rel.core.JoinRelType.FULL;
+import static org.apache.calcite.rel.core.JoinRelType.LEFT;
+import static org.apache.calcite.rel.core.JoinRelType.RIGHT;
/** */
public class IgniteMergeJoin extends AbstractIgniteJoin {
@@ -114,7 +115,7 @@ public class IgniteMergeJoin extends AbstractIgniteJoin {
@Override public Join copy(RelTraitSet traitSet, RexNode condition, RelNode left, RelNode right,
JoinRelType joinType, boolean semiJoinDone) {
return new IgniteMergeJoin(getCluster(), traitSet, left, right, condition, variablesSet, joinType,
- leftCollation, rightCollation);
+ left.getTraitSet().getCollation(), right.getTraitSet().getCollation());
}
/** {@inheritDoc} */
@@ -136,22 +137,25 @@ public class IgniteMergeJoin extends AbstractIgniteJoin {
RelTraitSet left = inputTraits.get(0), right = inputTraits.get(1);
RelCollation leftCollation = TraitUtils.collation(left), rightCollation = TraitUtils.collation(right);
- List<Integer> newLeftCollation, newRightCollation;
-
- if (isPrefix(leftCollation.getKeys(), joinInfo.leftKeys)) // preserve left collation
- rightCollation = leftCollation.apply(buildProjectionMapping(true));
+ if (containsOrderless(leftCollation, joinInfo.leftKeys)) // preserve left collation
+ rightCollation = leftCollation.apply(buildTransposeMapping(true));
- else if (isPrefix(rightCollation.getKeys(), joinInfo.rightKeys))// preserve right collation
- leftCollation = rightCollation.apply(buildProjectionMapping(false));
+ else if (containsOrderless(rightCollation, joinInfo.rightKeys)) // preserve right collation
+ leftCollation = rightCollation.apply(buildTransposeMapping(false));
else { // generate new collations
leftCollation = RelCollations.of(joinInfo.leftKeys);
rightCollation = RelCollations.of(joinInfo.rightKeys);
}
+ RelCollation desiredCollation = leftCollation;
+
+ if (joinType == RIGHT || joinType == FULL)
+ desiredCollation = RelCollations.EMPTY;
+
return ImmutableList.of(
Pair.of(
- nodeTraits.replace(leftCollation),
+ nodeTraits.replace(desiredCollation),
ImmutableList.of(
left.replace(leftCollation),
right.replace(rightCollation)
@@ -162,74 +166,79 @@ public class IgniteMergeJoin extends AbstractIgniteJoin {
/** {@inheritDoc} */
@Override public Pair<RelTraitSet, List<RelTraitSet>> passThroughCollation(
- RelTraitSet nodeTraits,
+ RelTraitSet required,
List<RelTraitSet> inputTraits
) {
- RelCollation collation = TraitUtils.collation(nodeTraits);
- RelTraitSet left = inputTraits.get(0), right = inputTraits.get(1);
-
- int rightOff = this.left.getRowType().getFieldCount();
-
- Map<Integer, Integer> rightToLeft = joinInfo.pairs().stream()
- .collect(Collectors.toMap(p -> p.target, p -> p.source));
-
- List<Integer> collationLeftPrj = new ArrayList<>();
+ RelCollation collation = TraitUtils.collation(required);
+ RelTraitSet left = inputTraits.get(0);
+ RelTraitSet right = inputTraits.get(1);
- for (Integer c : collation.getKeys()) {
- collationLeftPrj.add(
- c >= rightOff ? rightToLeft.get(c - rightOff) : c
- );
- }
-
- boolean preserveNodeCollation = false;
-
- List<Integer> newLeftCollation, newRightCollation;
+ if (joinType == FULL)
+ return defaultCollationPair(required, left, right);
- Map<Integer, Integer> leftToRight = joinInfo.pairs().stream()
- .collect(Collectors.toMap(p -> p.source, p -> p.target));
+ int leftInputFieldCount = this.left.getRowType().getFieldCount();
- if (isPrefix(collationLeftPrj, joinInfo.leftKeys)) { // preserve collation
- newLeftCollation = new ArrayList<>();
- newRightCollation = new ArrayList<>();
+ List<Integer> reqKeys = RelCollations.ordinals(collation);
+ List<Integer> leftKeys = joinInfo.leftKeys.toIntegerList();
+ List<Integer> rightKeys = joinInfo.rightKeys.incr(leftInputFieldCount).toIntegerList();
- int ind = 0;
- for (Integer c : collation.getKeys()) {
- if (c < rightOff) {
- newLeftCollation.add(c);
+ ImmutableBitSet reqKeySet = ImmutableBitSet.of(reqKeys);
+ ImmutableBitSet leftKeySet = ImmutableBitSet.of(joinInfo.leftKeys);
+ ImmutableBitSet rightKeySet = ImmutableBitSet.of(rightKeys);
- if (ind < joinInfo.leftKeys.size())
- newRightCollation.add(leftToRight.get(c));
- }
- else {
- c -= rightOff;
- newRightCollation.add(c);
+ RelCollation nodeCollation;
+ RelCollation leftCollation;
+ RelCollation rightCollation;
- if (ind < joinInfo.leftKeys.size())
- newLeftCollation.add(rightToLeft.get(c));
- }
+ if (reqKeySet.equals(leftKeySet)) {
+ if (joinType == RIGHT)
+ return defaultCollationPair(required, left, right);
- ind++;
- }
-
- preserveNodeCollation = true;
+ nodeCollation = collation;
+ leftCollation = collation;
+ rightCollation = collation.apply(buildTransposeMapping(true));
}
- else { // generate new collations
- newLeftCollation = maxPrefix(collationLeftPrj, joinInfo.leftKeys);
-
- Set<Integer> tail = new HashSet<>(joinInfo.leftKeys);
-
- tail.removeAll(newLeftCollation);
-
- newLeftCollation.addAll(tail);
+ else if (containsOrderless(leftKeys, collation)) {
+ if (joinType == RIGHT)
+ return defaultCollationPair(required, left, right);
+
+ // if sort keys are subset of left join keys, we can extend collations to make sure all join
+ // keys are sorted.
+ nodeCollation = collation;
+ leftCollation = extendCollation(collation, leftKeys);
+ rightCollation = leftCollation.apply(buildTransposeMapping(true));
+ }
+ else if (containsOrderless(collation, leftKeys) && reqKeys.stream().allMatch(i -> i < leftInputFieldCount)) {
+ if (joinType == RIGHT)
+ return defaultCollationPair(required, left, right);
+
+ // if sort keys are superset of left join keys, and left join keys is prefix of sort keys
+ // (order not matter), also sort keys are all from left join input.
+ nodeCollation = collation;
+ leftCollation = collation;
+ rightCollation = leftCollation.apply(buildTransposeMapping(true));
+ }
+ else if (reqKeySet.equals(rightKeySet)) {
+ if (joinType == LEFT)
+ return defaultCollationPair(required, left, right);
- newRightCollation = newLeftCollation.stream().map(leftToRight::get).collect(Collectors.toList());
+ nodeCollation = collation;
+ rightCollation = RelCollations.shift(collation, -leftInputFieldCount);
+ leftCollation = rightCollation.apply(buildTransposeMapping(false));
}
+ else if (containsOrderless(rightKeys, collation)) {
+ if (joinType == LEFT)
+ return defaultCollationPair(required, left, right);
- RelCollation leftCollation = createCollation(newLeftCollation);
- RelCollation rightCollation = createCollation(newRightCollation);
+ nodeCollation = collation;
+ rightCollation = RelCollations.shift(extendCollation(collation, rightKeys), -leftInputFieldCount);
+ leftCollation = rightCollation.apply(buildTransposeMapping(false));
+ }
+ else
+ return defaultCollationPair(required, left, right);
return Pair.of(
- nodeTraits.replace(preserveNodeCollation ? collation : leftCollation),
+ required.replace(nodeCollation),
ImmutableList.of(
left.replace(leftCollation),
right.replace(rightCollation)
@@ -277,4 +286,36 @@ public class IgniteMergeJoin extends AbstractIgniteJoin {
public RelCollation rightCollation() {
return rightCollation;
}
+
+ /** Creates pair with default collation for parent and simple yet sufficient collation for children nodes. */
+ private Pair<RelTraitSet, List<RelTraitSet>> defaultCollationPair(
+ RelTraitSet nodeTraits,
+ RelTraitSet leftInputTraits,
+ RelTraitSet rightInputTraits
+ ) {
+ return Pair.of(
+ nodeTraits.replace(EMPTY),
+ ImmutableList.of(
+ leftInputTraits.replace(RelCollations.of(joinInfo.leftKeys)),
+ rightInputTraits.replace(RelCollations.of(joinInfo.rightKeys))
+ )
+ );
+ }
+
+ /**
+ * This function extends collation by appending new collation fields defined on keys.
+ */
+ private static RelCollation extendCollation(RelCollation collation, List<Integer> keys) {
+ List<RelFieldCollation> fieldsForNewCollation = new ArrayList<>(keys.size());
+ fieldsForNewCollation.addAll(collation.getFieldCollations());
+
+ ImmutableBitSet keysBitset = ImmutableBitSet.of(keys);
+ ImmutableBitSet colKeysBitset = ImmutableBitSet.of(collation.getKeys());
+ ImmutableBitSet exceptBitset = keysBitset.except(colKeysBitset);
+
+ for (Integer i : exceptBitset)
+ fieldsForNewCollation.add(new RelFieldCollation(i));
+
+ return RelCollations.of(fieldsForNewCollation);
+ }
}
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/MergeJoinConverterRule.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/MergeJoinConverterRule.java
index 15c0bff..3c8b487 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/MergeJoinConverterRule.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/rule/MergeJoinConverterRule.java
@@ -61,8 +61,7 @@ public class MergeJoinConverterRule extends AbstractIgniteConverterRule<LogicalJ
RelTraitSet leftInTraits = cluster.traitSetOf(IgniteConvention.INSTANCE)
.replace(RelCollations.of(joinInfo.leftKeys));
- RelTraitSet outTraits = cluster.traitSetOf(IgniteConvention.INSTANCE)
- .replace(RelCollations.of(joinInfo.leftKeys)); // preserve collation of the left input
+ RelTraitSet outTraits = cluster.traitSetOf(IgniteConvention.INSTANCE);
RelTraitSet rightInTraits = cluster.traitSetOf(IgniteConvention.INSTANCE)
.replace(RelCollations.of(joinInfo.rightKeys));
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
index a82d7c9..84c597b 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/schema/SchemaHolderImpl.java
@@ -21,6 +21,7 @@ import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+
import org.apache.calcite.rel.RelCollation;
import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.RelFieldCollation;
@@ -247,10 +248,9 @@ public class SchemaHolderImpl extends AbstractService implements SchemaHolder, S
boolean descending = idxDesc.descending(idxField);
int fieldIdx = fieldDesc.fieldIndex();
- RelFieldCollation collation = new RelFieldCollation(fieldIdx,
- descending ? RelFieldCollation.Direction.DESCENDING : RelFieldCollation.Direction.ASCENDING);
-
- collations.add(collation);
+ collations.add(
+ createFieldCollation(fieldIdx, !descending)
+ );
}
return RelCollations.of(collations);
@@ -276,4 +276,11 @@ public class SchemaHolderImpl extends AbstractService implements SchemaHolder, S
igniteSchemas.forEach(newCalciteSchema::add);
calciteSchema = newCalciteSchema;
}
+
+ /** */
+ private static RelFieldCollation createFieldCollation(int fieldIdx, boolean asc) {
+ return asc
+ ? new RelFieldCollation(fieldIdx, RelFieldCollation.Direction.ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ : new RelFieldCollation(fieldIdx, RelFieldCollation.Direction.DESCENDING, RelFieldCollation.NullDirection.LAST);
+ }
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteBasicSecondaryIndexIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteBasicSecondaryIndexIntegrationTest.java
index c3c0b03..ae86dff 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteBasicSecondaryIndexIntegrationTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteBasicSecondaryIndexIntegrationTest.java
@@ -847,7 +847,7 @@ public class CalciteBasicSecondaryIndexIntegrationTest extends GridCommonAbstrac
/** */
@Test
public void testOrderByKeyAlias() {
- assertQuery("SELECT * FROM Developer WHERE id<=4 ORDER BY id")
+ assertQuery("SELECT * FROM Developer WHERE id<=4 ORDER BY id nulls first")
.matches(containsIndexScan("PUBLIC", "DEVELOPER"))
.matches(not(containsSubPlan("IgniteSort")))
.returns(1, "Mozart", 3, "Vienna", 33)
@@ -861,7 +861,7 @@ public class CalciteBasicSecondaryIndexIntegrationTest extends GridCommonAbstrac
/** */
@Test
public void testOrderByDepId() {
- assertQuery("SELECT * FROM Developer ORDER BY depId")
+ assertQuery("SELECT * FROM Developer ORDER BY depId nulls first")
.matches(containsIndexScan("PUBLIC", "DEVELOPER", DEPID_IDX))
.matches(not(containsSubPlan("IgniteSort")))
.returns(3, "Bach", 1, "Leipzig", 55)
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
index 97664de..c4fdbb0 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/CalciteQueryProcessorTest.java
@@ -25,6 +25,7 @@ import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
+
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import org.apache.calcite.util.ImmutableIntList;
@@ -60,8 +61,11 @@ import org.hamcrest.CoreMatchers;
import org.junit.Test;
import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.awaitReservationsRelease;
+import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsIndexScan;
+import static org.apache.ignite.internal.processors.query.calcite.QueryChecker.containsSubPlan;
import static org.apache.ignite.testframework.GridTestUtils.waitForCondition;
import static org.hamcrest.CoreMatchers.equalTo;
+import static org.hamcrest.CoreMatchers.not;
import static org.junit.Assert.assertThat;
/**
@@ -503,49 +507,6 @@ public class CalciteQueryProcessorTest extends GridCommonAbstractTest {
/** */
@Test
- public void testSortNullsDirection() throws Exception {
- IgniteCache<Integer, Employer> orders = client.getOrCreateCache(new CacheConfiguration<Integer, Employer>()
- .setName("orders")
- .setSqlSchema("PUBLIC")
- .setQueryEntities(F.asList(new QueryEntity(Integer.class, Employer.class).setTableName("orders")))
- .setBackups(1)
- );
-
- orders.put(1, new Employer("Igor", 10d));
- orders.put(2, new Employer("Igor", 11d));
- orders.put(3, new Employer("Igor", 12d));
- orders.put(4, new Employer("Igor1", 13d));
- orders.put(5, new Employer("Igor1", 13d));
- orders.put(6, new Employer("Igor1", null));
- orders.put(7, new Employer("Roman", null));
-
- List<List<?>> rows = sql(
- "SELECT salary FROM Orders ORDER BY salary", true);
-
- List<List<?>> rows0 = sql(
- "SELECT salary FROM Orders ORDER BY salary NULLS LAST", true);
-
- assertEquals(7, rows.size());
- assertEquals(rows, rows0);
-
- rows = sql(
- "SELECT _KEY FROM Orders ORDER BY salary", true);
-
- rows0 = sql(
- "SELECT _KEY FROM Orders ORDER BY salary NULLS LAST", true);
-
- assertEquals(7, rows.size());
- assertEquals(rows, rows0);
-
- rows = sql(
- "SELECT salary FROM Orders ORDER BY salary NULLS FIRST LIMIT 1", true);
-
- assertEquals(1, rows.size());
- assertEquals(null, rows.get(0).get(0));
- }
-
- /** */
- @Test
public void testEqConditionWithDistinctSubquery() throws Exception {
populateTables();
@@ -1081,6 +1042,83 @@ public class CalciteQueryProcessorTest extends GridCommonAbstractTest {
.check();
}
+ /**
+ * Test verifies that 1) proper indexes will be chosen for queries with
+ * different kinds of ordering, and 2) result set returned will be
+ * sorted as expected.
+ *
+ * @throws IgniteInterruptedCheckedException If failed.
+ */
+ @Test
+ public void testSelectWithOrdering() throws IgniteInterruptedCheckedException {
+ sql( "drop table if exists test_tbl", true);
+
+ sql( "create table test_tbl (c1 int)", true);
+
+ sql( "insert into test_tbl values (1), (2), (3), (null)", true);
+
+ sql( "create index idx_asc on test_tbl (c1)", true);
+ sql( "create index idx_desc on test_tbl (c1 desc)", true);
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1")
+ .matches(containsIndexScan("PUBLIC", "TEST_TBL", "IDX_ASC"))
+ .matches(not(containsSubPlan("IgniteSort")))
+ .ordered()
+ .returns(new Object[]{null})
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .check();
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1 asc nulls first")
+ .matches(containsIndexScan("PUBLIC", "TEST_TBL", "IDX_ASC"))
+ .matches(not(containsSubPlan("IgniteSort")))
+ .ordered()
+ .returns(new Object[]{null})
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .check();
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1 asc nulls last")
+ .matches(containsSubPlan("IgniteSort"))
+ .ordered()
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .returns(new Object[]{null})
+ .check();
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1 desc")
+ .matches(containsIndexScan("PUBLIC", "TEST_TBL", "IDX_DESC"))
+ .matches(not(containsSubPlan("IgniteSort")))
+ .ordered()
+ .returns(3)
+ .returns(2)
+ .returns(1)
+ .returns(new Object[]{null})
+ .check();
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1 desc nulls first")
+ .matches(containsSubPlan("IgniteSort"))
+ .ordered()
+ .returns(new Object[]{null})
+ .returns(3)
+ .returns(2)
+ .returns(1)
+ .check();
+
+ assertQuery(client, "select c1 from test_tbl ORDER BY c1 desc nulls last")
+ .matches(containsIndexScan("PUBLIC", "TEST_TBL", "IDX_DESC"))
+ .matches(not(containsSubPlan("IgniteSort")))
+ .ordered()
+ .returns(3)
+ .returns(2)
+ .returns(1)
+ .returns(new Object[]{null})
+ .check();
+ }
+
/** */
private static List<String> deriveColumnNamesFromCursor(FieldsQueryCursor cursor) {
List<String> names = new ArrayList<>(cursor.getColumnsCount());
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
index 1da680a..66977b6 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/QueryChecker.java
@@ -356,7 +356,7 @@ public abstract class QueryChecker {
* @param act Actual collection.
*/
private void assertEqualsCollections(Collection<?> exp, Collection<?> act) {
- assertEquals("Collections sizes are not equal:", exp.size(), act.size());
+ assertEquals("Collections sizes are not equal:\nExpected: " + exp + "\nActual: " + act, exp.size(), act.size());
Iterator<?> it1 = exp.iterator();
Iterator<?> it2 = act.iterator();
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java
new file mode 100644
index 0000000..376437f
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/JoinIntegrationTest.java
@@ -0,0 +1,730 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.ignite.internal.processors.query.calcite.integration;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.stream.Collectors;
+
+import org.apache.ignite.cache.query.FieldsQueryCursor;
+import org.apache.ignite.cache.query.QueryCursor;
+import org.apache.ignite.internal.processors.query.calcite.CalciteQueryProcessor;
+import org.apache.ignite.internal.processors.query.calcite.QueryChecker;
+import org.apache.ignite.internal.processors.query.calcite.util.Commons;
+import org.junit.Assume;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+
+/** */
+@RunWith(Parameterized.class)
+public class JoinIntegrationTest extends AbstractBasicIntegrationTest {
+ /** */
+ @Parameterized.Parameter
+ public JoinType joinType;
+
+ /** */
+ @Parameterized.Parameters(name = "joinType={0}")
+ public static List<Object[]> params() {
+ return Arrays.stream(JoinType.values())
+ .map(jt -> new Object[]{jt})
+ .collect(Collectors.toList());
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void beforeTestsStarted() throws Exception {
+ super.beforeTestsStarted();
+
+ executeSql("create table t1 (c1 int, c2 int, c3 int)");
+ executeSql("create table t2 (c1 int, c2 int, c3 int)");
+
+ executeSql("create index t1_idx on t1 (c3, c2, c1)");
+ executeSql("create index t2_idx on t2 (c3, c2, c1)");
+
+ executeSql("insert into t1 values (1, 1, 1), (2, null, 2), (2, 2, 2), (3, 3, null), (3, 3, 3), (4, 4, 4)");
+ executeSql("insert into t2 values (1, 1, 1), (2, 2, null), (2, 2, 2), (3, null, 3), (3, 3, 3), (4, 4, 4)");
+ }
+
+ /** {@inheritDoc} */
+ @Override protected void afterTest() {
+ // NO-OP
+ }
+
+ /**
+ * Test verifies result of inner join with different ordering.
+ */
+ @Test
+ public void testInnerJoin() {
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2, t1.c3"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2, t1.c3 nulls first"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2, t1.c3 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, null, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3 nulls first"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3 nulls last"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, null, 3, 3)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t2.c3 c23, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3, t1.c2"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1)
+ .returns(2, 2, 2, 2)
+ .returns(3, 3, 3, 3)
+ .returns(4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls first, t1.c2 nulls first, t1.c1 nulls first"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls first, t1.c2 nulls first, t1.c1 nulls first"
+ )
+ .ordered()
+ .returns(null, 3, 3, 3, 3)
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls last, t1.c2 nulls last, t1.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, 3, 3, 3, 3)
+ .check();
+ }
+
+ /**
+ * Test verifies result of left join with different ordering.
+ */
+ @Test
+ public void testLeftJoin() {
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2, t1.c3"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2 nulls first, t1.c3 nulls first"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1, t1.c2 nulls last, t1.c3 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, null, 2, null, null)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, null, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3 nulls first"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, null, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t1.c3 c13, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c1 desc, t1.c2, t1.c3 nulls last"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, null, 3, 3)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t2.c3 c23, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3, t1.c2"
+ )
+ .ordered()
+ .returns(null, 3, null, null)
+ .returns(1, 1, 1, 1)
+ .returns(2, null, null, null)
+ .returns(2, 2, 2, 2)
+ .returns(3, 3, 3, 3)
+ .returns(4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls first, t1.c2 nulls first, t1.c1 nulls first"
+ )
+ .ordered()
+ .returns(null, 3, 3, null, null)
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls last, t1.c2 nulls last, t1.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, null, 2, null, null)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, 3, 3, null, null)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls first, t1.c2 nulls first, t1.c1 nulls first"
+ )
+ .ordered()
+ .returns(null, 3, 3, 3, 3)
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, null, 2, null, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22 " +
+ " from t1 " +
+ " left join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t1.c3 nulls last, t1.c2 nulls last, t1.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, null, 2, null, null)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, 3, 3, 3, 3)
+ .check();
+ }
+
+ /**
+ * Test verifies result of right join with different ordering.
+ */
+ @Test
+ public void testRightJoin() {
+ Assume.assumeTrue(joinType != JoinType.CORRELATED);
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1, t2.c2, t2.c3"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1, t2.c2 nulls first, t2.c3 nulls first"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1, t2.c2 nulls last, t2.c3 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, null)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(null, null, 3, null, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1 desc, t2.c2, t2.c3"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, 2, 2, 2, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1 desc, t2.c2, t2.c3 nulls first"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, 2, 2, 2, null)
+ .returns(2, 2, 2, 2, 2)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c1 c11, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c1 desc, t2.c2, t2.c3 nulls last"
+ )
+ .ordered()
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(2, 2, 2, 2, 2)
+ .returns(2, 2, 2, 2, null)
+ .returns(1, 1, 1, 1, 1)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t2.c3 c23, t2.c2 c22 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c3, t2.c2"
+ )
+ .ordered()
+ .returns(null, null, null, 2)
+ .returns(1, 1, 1, 1)
+ .returns(2, 2, 2, 2)
+ .returns(null, null, 3, null)
+ .returns(3, 3, 3, 3)
+ .returns(4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first"
+ )
+ .ordered()
+ .returns(null, null, 2, 2, null)
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c3 = t2.c3 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(null, null, 3, null, 3)
+ .returns(4, 4, 4, 4, 4)
+ .returns(null, null, 2, 2, null)
+ .check();
+
+ assertQuery("" +
+ "select t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first"
+ )
+ .ordered()
+ .returns(2, 2, 2, 2, null)
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3)
+ .returns(3, 3, 3, 3, 3)
+ .returns(null, null, 3, null, 3)
+ .returns(4, 4, 4, 4, 4)
+ .returns(2, 2, 2, 2, null)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " and t1.c3 = t2.c3 " +
+ " order by t2.c3 nulls first, t2.c2 nulls first, t2.c1 nulls first"
+ )
+ .ordered()
+ .returns(null, null, null, 2, 2, null)
+ .returns(1, 1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2, 2)
+ .returns(null, null, null, 3, null, 3)
+ .returns(3, 3, 3, 3, 3, 3)
+ .returns(4, 4, 4, 4, 4, 4)
+ .check();
+
+ assertQuery("" +
+ "select t1.c3 c13, t1.c2 c12, t1.c1 c11, t2.c1 c21, t2.c2 c22, t2.c3 c23 " +
+ " from t1 " +
+ " right join t2 " +
+ " on t1.c1 = t2.c1 " +
+ " and t1.c2 = t2.c2 " +
+ " and t1.c3 = t2.c3 " +
+ " order by t2.c3 nulls last, t2.c2 nulls last, t2.c1 nulls last"
+ )
+ .ordered()
+ .returns(1, 1, 1, 1, 1, 1)
+ .returns(2, 2, 2, 2, 2, 2)
+ .returns(3, 3, 3, 3, 3, 3)
+ .returns(null, null, null, 3, null, 3)
+ .returns(4, 4, 4, 4, 4, 4)
+ .returns(null, null, null, 2, 2, null)
+ .check();
+ }
+
+ /** */
+ private List<List<?>> executeSql(String sql, Object... args) {
+ List<FieldsQueryCursor<List<?>>> cur = queryProcessor().query(null, "PUBLIC", sql, args);
+
+ try (QueryCursor<List<?>> srvCursor = cur.get(0)) {
+ return srvCursor.getAll();
+ }
+ }
+
+ /** {@inheritDoc} */
+ @Override protected QueryChecker assertQuery(String qry) {
+ return super.assertQuery(qry.replace("select", "select "
+ + Arrays.stream(joinType.disabledRules).collect(Collectors.joining("','", "/*+ DISABLE_RULE('", "') */"))));
+ }
+
+ /** */
+ private CalciteQueryProcessor queryProcessor() {
+ return Commons.lookupComponent(client.context(), CalciteQueryProcessor.class);
+ }
+
+ /** */
+ enum JoinType {
+ /** */
+ NESTED_LOOP(
+ "CorrelatedNestedLoopJoin",
+ "JoinCommuteRule",
+ "MergeJoinConverter"
+ ),
+
+ /** */
+ MERGE(
+ "CorrelatedNestedLoopJoin",
+ "JoinCommuteRule",
+ "NestedLoopJoinConverter"
+ ),
+
+ /** */
+ CORRELATED(
+ "MergeJoinConverter",
+ "JoinCommuteRule",
+ "NestedLoopJoinConverter"
+ );
+
+ /** */
+ private final String[] disabledRules;
+
+ /** */
+ JoinType(String... disabledRules) {
+ this.disabledRules = disabledRules;
+ }
+ }
+}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/LimitOffsetPlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/LimitOffsetPlannerTest.java
index 5387669..2528668 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/LimitOffsetPlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/LimitOffsetPlannerTest.java
@@ -17,7 +17,10 @@
package org.apache.ignite.internal.processors.query.calcite.planner;
+import java.util.Arrays;
+
import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.util.ImmutableIntList;
@@ -34,6 +37,8 @@ import org.apache.ignite.internal.processors.query.calcite.type.IgniteTypeSystem
import org.apache.ignite.internal.util.typedef.F;
import org.junit.Test;
+import static java.util.stream.Collectors.toList;
+
/**
* Planner test for LIMIT and OFFSET.
*/
@@ -182,8 +187,22 @@ public class LimitOffsetPlannerTest extends AbstractPlannerTest {
}
};
- if (!F.isEmpty(indexedColumns))
- table.addIndex(RelCollations.of(ImmutableIntList.of(indexedColumns)), "test_idx");
+ if (!F.isEmpty(indexedColumns)) {
+ table.addIndex(
+ RelCollations.of(
+ Arrays.stream(indexedColumns)
+ .mapToObj(
+ idx -> new RelFieldCollation(
+ idx,
+ RelFieldCollation.Direction.ASCENDING,
+ RelFieldCollation.NullDirection.FIRST
+ )
+ )
+ .collect(toList())
+ ),
+ "test_idx"
+ );
+ }
return createSchema(table);
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/MergeJoinPlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/MergeJoinPlannerTest.java
new file mode 100644
index 0000000..1bc501a
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/MergeJoinPlannerTest.java
@@ -0,0 +1,2799 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.planner;
+
+import java.util.List;
+
+import org.apache.calcite.rel.RelCollation;
+import org.apache.calcite.rel.RelCollations;
+import org.apache.calcite.rel.RelFieldCollation;
+import org.apache.calcite.rel.core.Join;
+import org.apache.ignite.internal.processors.query.calcite.rel.IgniteRel;
+import org.apache.ignite.internal.processors.query.calcite.rel.IgniteSort;
+import org.apache.ignite.internal.processors.query.calcite.rel.IgniteTableScan;
+import org.apache.ignite.internal.processors.query.calcite.schema.IgniteSchema;
+import org.apache.ignite.internal.processors.query.calcite.trait.IgniteDistributions;
+import org.junit.Test;
+
+import static org.apache.calcite.rel.RelFieldCollation.Direction.ASCENDING;
+import static org.apache.calcite.rel.RelFieldCollation.Direction.DESCENDING;
+
+/** MergeJoin planner test. */
+public class MergeJoinPlannerTest extends AbstractPlannerTest {
+ /** Only MergeJoin encourage. */
+ private static final String[] DISABLED_RULES = {
+ "NestedLoopJoinConverter",
+ "CorrelatedNestedLoopJoin",
+ "FilterSpoolMergeRule",
+ "JoinCommuteRule"
+ };
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys of left table should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1, LEFT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys of left table should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft2() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys of left table should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft3() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 ASC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys of left table should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft4() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 ASC NULLS LAST, LEFT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys of left table should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft5() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 ASC NULLS FIRST, LEFT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys should be propagated as is,
+ * if it doesn't include fields from right table.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft6() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1, LEFT_T.c2, LEFT_T.c3";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys should be propagated as is,
+ * if it doesn't include fields from right table.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft7() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 DESC, LEFT_T.c3 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(2, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys should be propagated as is,
+ * if it doesn't include fields from right table.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft8() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 ASC, LEFT_T.c3 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys should be propagated as is,
+ * if it doesn't include fields from right table.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft9() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 ASC NULLS LAST, LEFT_T.c2 ASC NULLS LAST, LEFT_T.c3 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys should be propagated as is,
+ * if it doesn't include fields from right table.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft10() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 ASC NULLS FIRST, LEFT_T.c2 ASC NULLS LAST, LEFT_T.c3 ASC NULLS FIRST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is superset of join keys, but its prefix
+ * contains columns outside of join keys, can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft11() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c3, LEFT_T.c2, LEFT_T.c1";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ IgniteSort topSortNode = sortOnTopOfJoin(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ topSortNode.collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortNodes.get(0).collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortNodes.get(1).collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft12() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by LEFT_T.c1, LEFT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft13() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft14() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by LEFT_T.c1 DESC, LEFT_T.c2 ASC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft15() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by LEFT_T.c1 ASC NULLS LAST, LEFT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByLeft16() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by LEFT_T.c1 ASC NULLS FIRST, LEFT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight2() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight3() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 ASC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight4() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 ASC NULLS LAST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight5() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 ASC NULLS FIRST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * Any collation that contains column from right table which is not part
+ * of join keys can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight6() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2, RIGHT_T.c3";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ IgniteSort topSortNode = sortOnTopOfJoin(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(3, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(4, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(5, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ topSortNode.collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expectedBottomCollation = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expectedBottomCollation, sortNodes.get(0).collation());
+ assertEquals(expectedBottomCollation, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight7() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight8() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight9() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 ASC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight10() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1 ASC NULLS LAST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, collation that is subset of join keys should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerPassThroughOrderByRight11() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1 ASC NULLS FIRST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of left keys can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByLeft1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1, LEFT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ IgniteSort topSortNode = sortOnTopOfJoin(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ topSortNode.collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation that is superset of join keys can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByLeft2() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1, LEFT_T.c2, LEFT_T.c3";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ IgniteSort topSortNode = sortOnTopOfJoin(rel);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ topSortNode.collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByRight1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByRight2() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 DESC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByRight3() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 DESC, RIGHT_T.c2 ASC";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByRight4() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 ASC NULLS LAST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of right join, collation consisted of join keys only should be propagated as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testRightPassThroughOrderByRight5() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " right join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1 ASC NULLS FIRST, RIGHT_T.c2 ASC NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of left join, collation consisted of join keys of right table can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testLeftPassThroughOrderByRight1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " left join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(3, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(4, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfJoin(rel).collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of left join, collation consisted of join keys of right table can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testLeftPassThroughOrderByRight2() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " left join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(3, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(4, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfJoin(rel).collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of full join, any collation can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testFullPassThroughOrderByLeft1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " full join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1, LEFT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfJoin(rel).collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of full join, any collation can't be propagated.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testFullPassThroughOrderByRight1() throws Exception {
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " full join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by RIGHT_T.c1, RIGHT_T.c2";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(3, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(4, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfJoin(rel).collation()
+ );
+
+ List<IgniteSort> sortNodes = sortOnTopOfScan(rel);
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortNodes.get(0).collation());
+ assertEquals(expected, sortNodes.get(1).collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft1() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft2() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft3() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft4() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft5() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft6() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft7() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING),
+ new RelFieldCollation(2, DESCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft8() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal, but its prefix contains columns
+ * outside of join keys, can't be derived.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft9() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING),
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset or equal to join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft10() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is subset of join keys, is not suitable.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft11() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset of join keys and has a common prefix could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft12() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c1 NULLS LAST, LEFT_T.c2 NULLS LAST, LEFT_T.c3 NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfJoin(rel));
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation that is superset of join keys, and there is no a common prefix,
+ * could not be derived.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveLeft13() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING),
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ left,
+ createTable("RIGHT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class)
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " order by LEFT_T.c3 NULLS LAST, LEFT_T.c1 NULLS LAST, LEFT_T.c2 NULLS LAST";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING),
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfJoin(rel).collation()
+ );
+
+ RelCollation expected = RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ );
+
+ assertEquals(expected, sortOnTopOfScan(rel, "LEFT_T").collation());
+ assertEquals(expected, sortOnTopOfScan(rel, "RIGHT_T").collation());
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that consists of join keys only could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight1() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that consists of join keys only could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight2() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING),
+ new RelFieldCollation(1, DESCENDING)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that consists of join keys only could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight3() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that consists of join keys only could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight4() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that consists of join keys only could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight5() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that is superset of join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight6() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that is superset of join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight7() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(2, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, DESCENDING, RelFieldCollation.NullDirection.LAST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that is superset of join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight8() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, DESCENDING, RelFieldCollation.NullDirection.LAST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that is superset of join keys could be derived as is.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight9() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, right collation that is subset of join keys can't be derived.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDerivePreserveRight10() throws Exception {
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(
+ createTable("LEFT_T", IgniteDistributions.single(), "C1", Integer.class, "C2", Integer.class, "C3", Integer.class),
+ right
+ );
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(2, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation should be preferred if it fully covers join keys.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDeriveMixed1() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ "idx"
+ );
+
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(1, ASCENDING),
+ new RelFieldCollation(0, ASCENDING)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(left, right);
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertNull(sortOnTopOfScan(rel, "LEFT_T"));
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING),
+ new RelFieldCollation(1, ASCENDING)
+ ),
+ sortOnTopOfScan(rel, "RIGHT_T").collation()
+ );
+ }
+
+ /**
+ * Test verifies the collation propagation from a parent node.
+ *
+ * In case of inner join, left collation should be preferred if it fully covers join keys.
+ *
+ * @throws Exception In case of any unexpected error.
+ */
+ @Test
+ public void testInnerDeriveMixed2() throws Exception {
+ TestTable left = createTable("LEFT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ left.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ TestTable right = createTable("RIGHT_T", IgniteDistributions.single(),
+ "C1", Integer.class, "C2", Integer.class, "C3", Integer.class);
+
+ right.addIndex(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ "idx"
+ );
+
+ IgniteSchema schema = createSchema(left, right);
+
+ String sql = "" +
+ "select * " +
+ " from LEFT_T " +
+ " join RIGHT_T " +
+ " on LEFT_T.c1 = RIGHT_T.c1 " +
+ " and LEFT_T.c2 = RIGHT_T.c2 " +
+ " and LEFT_T.c3 = RIGHT_T.c3 ";
+
+ IgniteRel rel = physicalPlan(sql, schema, DISABLED_RULES);
+
+ assertEquals(
+ RelCollations.of(
+ new RelFieldCollation(2, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(1, ASCENDING, RelFieldCollation.NullDirection.FIRST),
+ new RelFieldCollation(0, ASCENDING, RelFieldCollation.NullDirection.FIRST)
+ ),
+ sortOnTopOfScan(rel, "LEFT_T").collation()
+ );
+ assertNull(sortOnTopOfScan(rel, "RIGHT_T"));
+ }
+
+ /**
+ * Search for sort node whose input is arbitrary scan node.
+ *
+ * @param root The root of the tree to serach.
+ * @return The list of the sort nodes or an empty list, if there is no such nodes.
+ */
+ private List<IgniteSort> sortOnTopOfScan(IgniteRel root) {
+ return findNodes(root, byClass(IgniteSort.class)
+ .and(node -> node.getInputs().size() == 1 && node.getInput(0) instanceof IgniteTableScan));
+ }
+
+ /**
+ * Search for sort node whose input is scan of the given table.
+ *
+ * @param root The root of the tree to serach.
+ * @param tableName Desired table name.
+ * @return The sort node or {@code null}, if there is no such node.
+ */
+ private IgniteSort sortOnTopOfScan(IgniteRel root, String tableName) {
+ List<IgniteSort> sortNodes = findNodes(root, byClass(IgniteSort.class)
+ .and(node -> node.getInputs().size() == 1 && node.getInput(0) instanceof IgniteTableScan
+ && node.getInput(0).getTable().unwrap(TestTable.class).name().equals(tableName)));
+
+ if (sortNodes.size() > 1)
+ throw new AssertionError("Unexpected count of sort nodes: exp<=1, act=" + sortNodes.size());
+
+ return sortNodes.isEmpty() ? null : sortNodes.get(0);
+ }
+
+ /**
+ * Search for sort node whose input is join node.
+ *
+ * @param root The root of the tree to serach.
+ * @return The sort node or {@code null}, if there is no such node.
+ */
+ private IgniteSort sortOnTopOfJoin(IgniteRel root) {
+ List<IgniteSort> sortNodes = findNodes(root, byClass(IgniteSort.class)
+ .and(node -> node.getInputs().size() == 1 && node.getInput(0) instanceof Join));
+
+ if (sortNodes.size() > 1)
+ throw new AssertionError("Unexpected count of sort nodes: exp<=1, act=" + sortNodes.size());
+
+ return sortNodes.isEmpty() ? null : sortNodes.get(0);
+ }
+}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/PlannerTest.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/PlannerTest.java
index 4d8e8c2..d1e40db 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/PlannerTest.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/PlannerTest.java
@@ -1234,61 +1234,6 @@ public class PlannerTest extends AbstractPlannerTest {
/** */
@Test
- public void testMergeJoin() throws Exception {
- IgniteTypeFactory f = new IgniteTypeFactory(IgniteTypeSystem.INSTANCE);
-
- TestTable emp = new TestTable(
- new RelDataTypeFactory.Builder(f)
- .add("ID", f.createJavaType(Integer.class))
- .add("NAME", f.createJavaType(String.class))
- .add("DEPTNO", f.createJavaType(Integer.class))
- .build()) {
-
- @Override public IgniteDistribution distribution() {
- return IgniteDistributions.broadcast();
- }
- };
-
- emp.addIndex(new IgniteIndex(RelCollations.of(ImmutableIntList.of(1, 2)), "emp_idx", null, emp));
-
- TestTable dept = new TestTable(
- new RelDataTypeFactory.Builder(f)
- .add("DEPTNO", f.createJavaType(Integer.class))
- .add("NAME", f.createJavaType(String.class))
- .build()) {
-
- @Override public IgniteDistribution distribution() {
- return IgniteDistributions.broadcast();
- }
- };
-
- dept.addIndex(new IgniteIndex(RelCollations.of(ImmutableIntList.of(1, 0)), "dep_idx", null, dept));
-
- IgniteSchema publicSchema = new IgniteSchema("PUBLIC");
-
- publicSchema.addTable("EMP", emp);
- publicSchema.addTable("DEPT", dept);
-
- SchemaPlus schema = createRootSchema(false)
- .add("PUBLIC", publicSchema);
-
- String sql = "select * from dept d join emp e on d.deptno = e.deptno and e.name = d.name order by e.name, d.deptno";
-
- IgniteRel phys = physicalPlan(sql, publicSchema);
-
- assertNotNull(phys);
- assertEquals("" +
- "IgniteMergeJoin(condition=[AND(=($0, $4), =($3, $1))], joinType=[inner], leftCollation=[[0, 1]], " +
- "rightCollation=[[2, 1]])\n" +
- " IgniteIndexScan(table=[[PUBLIC, DEPT]], index=[dep_idx])\n" +
- " IgniteIndexScan(table=[[PUBLIC, EMP]], index=[emp_idx])\n",
- RelOptUtil.toString(phys));
-
- checkSplitAndSerialization(phys, publicSchema);
- }
-
- /** */
- @Test
public void testMergeJoinIsNotAppliedForNonEquiJoin() throws Exception {
IgniteTypeFactory f = new IgniteTypeFactory(IgniteTypeSystem.INSTANCE);
@@ -1331,7 +1276,7 @@ public class PlannerTest extends AbstractPlannerTest {
assertNotNull(phys);
assertEquals("" +
- "IgniteSort(sort0=[$3], sort1=[$0], dir0=[ASC], dir1=[ASC])\n" +
+ "IgniteSort(sort0=[$3], sort1=[$0], dir0=[ASC-nulls-first], dir1=[ASC-nulls-first])\n" +
" IgniteProject(DEPTNO=[$3], NAME=[$4], ID=[$0], NAME0=[$1])\n" +
" IgniteNestedLoopJoin(condition=[AND(=($3, $2), >=($1, $4))], joinType=[inner])\n" +
" IgniteTableScan(table=[[PUBLIC, EMP]])\n" +
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
index f42a6a2..a09c8d6 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/IntegrationTestSuite.java
@@ -30,6 +30,7 @@ import org.apache.ignite.internal.processors.query.calcite.integration.Aggregate
import org.apache.ignite.internal.processors.query.calcite.integration.CalciteErrorHandlilngIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.IndexDdlIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.IndexSpoolIntegrationTest;
+import org.apache.ignite.internal.processors.query.calcite.integration.JoinIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.MetadataIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.SetOpIntegrationTest;
import org.apache.ignite.internal.processors.query.calcite.integration.SortAggregateIntegrationTest;
@@ -69,6 +70,7 @@ import org.junit.runners.Suite;
SetOpIntegrationTest.class,
UnstableTopologyTest.class,
JoinCommuteRulesTest.class,
+ JoinIntegrationTest.class,
})
public class IntegrationTestSuite {
}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
index 3b0299c..87ef0cd 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/PlannerTestSuite.java
@@ -25,6 +25,7 @@ import org.apache.ignite.internal.processors.query.calcite.planner.HashIndexSpoo
import org.apache.ignite.internal.processors.query.calcite.planner.JoinColocationPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.JoinCommutePlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.LimitOffsetPlannerTest;
+import org.apache.ignite.internal.processors.query.calcite.planner.MergeJoinPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.PlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.SetOpPlannerTest;
import org.apache.ignite.internal.processors.query.calcite.planner.SortAggregatePlannerTest;
@@ -57,6 +58,7 @@ import org.junit.runners.Suite;
UnionPlannerTest.class,
JoinCommutePlannerTest.class,
LimitOffsetPlannerTest.class,
+ MergeJoinPlannerTest.class,
})
public class PlannerTestSuite {
}
diff --git a/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join.test b/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join.test
index cb52aee..9232760 100644
--- a/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join.test
+++ b/modules/calcite/src/test/sql/join/full_outer/test_full_outer_join.test
@@ -19,7 +19,7 @@ INSERT INTO integers2 VALUES (1, 10), (2, 20)
# equality join
query IIII
-SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k ORDER BY i
+SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k ORDER BY i NULLS FIRST
----
NULL NULL 2 20
1 1 1 10
@@ -27,7 +27,8 @@ NULL NULL 2 20
# equality join with additional non-equality predicate
query IIII
-SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k AND integers.j > integers2.l ORDER BY 1, 2, 3, 4
+SELECT i, j, k, l FROM integers FULL OUTER JOIN integers2 ON integers.i=integers2.k AND integers.j > integers2.l ORDER
+BY 1 NULLS FIRST, 2, 3, 4
----
NULL NULL 1 10
NULL NULL 2 20
@@ -36,7 +37,8 @@ NULL NULL 2 20
# equality join with varchar values
query IIIT
-SELECT i, j, k, l FROM integers FULL OUTER JOIN (SELECT k, l::VARCHAR AS l FROM integers2) integers2 ON integers.i=integers2.k ORDER BY 1, 2, 3, 4
+SELECT i, j, k, l FROM integers FULL OUTER JOIN (SELECT k, l::VARCHAR AS l FROM integers2) integers2 ON integers
+.i=integers2.k ORDER BY 1 NULLS FIRST, 2, 3, 4
----
NULL NULL 2 20
1 1 1 10
diff --git a/modules/calcite/src/test/sql/join/inner/test_eq_ineq_join.test b/modules/calcite/src/test/sql/join/inner/test_eq_ineq_join.test
index b63817d..770ee8c 100644
--- a/modules/calcite/src/test/sql/join/inner/test_eq_ineq_join.test
+++ b/modules/calcite/src/test/sql/join/inner/test_eq_ineq_join.test
@@ -65,7 +65,7 @@ statement ok
INSERT INTO test2 VALUES (11, 1, 'd'), (12, 1, 'e'), (13, 4, 'f')
query IIT
-SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c);
+SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) ORDER BY a;
----
12 2 b
13 3 c
@@ -76,7 +76,7 @@ SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND tes
12 2 b
query IIT
-SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c);
+SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c) ORDER BY a;
----
11 1 a
12 2 b
@@ -87,7 +87,7 @@ SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND
11 1 a
query IIT
-SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) AND test.a > 11;
+SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) AND test.a > 11 ORDER BY a;
----
12 2 b
13 3 c
@@ -112,20 +112,20 @@ statement ok
INSERT INTO test2 VALUES (11, 1, 'd'), (12, 1, 'e'), (13, 4, 'f')
query IIT
-SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c AND test2.a>14) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b>test2.c AND test2.a<10);
+SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c AND test2.a>14) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b>test2.c AND test2.a<10) ORDER BY a;
----
11 1 a
12 2 b
13 3 c
query IIT
-SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c AND test2.a=12) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b>test2.c AND test2.a=12);
+SELECT * FROM test WHERE NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<test2.c AND test2.a=12) AND NOT EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b>test2.c AND test2.a=12) ORDER BY a;
----
11 1 a
13 3 c
query IIT
-SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) AND test.a < 13;
+SELECT * FROM test WHERE EXISTS(SELECT * FROM test2 WHERE test.a=test2.a AND test.b<>test2.c) AND test.a < 13 ORDER BY a;
----
12 2 b