This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/druid.git
The following commit(s) were added to refs/heads/master by this push:
new c96b215dd67 SortMerge join support for IS NOT DISTINCT FROM. (#16003)
c96b215dd67 is described below
commit c96b215dd67fad883b1f6651ed568426aae7070f
Author: Gian Merlino <[email protected]>
AuthorDate: Tue Mar 19 12:02:13 2024 -0700
SortMerge join support for IS NOT DISTINCT FROM. (#16003)
* SortMerge join support for IS NOT DISTINCT FROM.
The patch adds a "requiredNonNullKeyParts" field to the sortMerge
processor, which has the list of key parts that must be nonnull for
an equijoin condition to match. Conditions with SQL "=" are present in
the list; conditions with SQL "IS NOT DISTINCT FROM" are absent from
the list.
* Fix test.
* Update javadoc.
---
.../apache/druid/msq/querykit/DataSourcePlan.java | 8 +-
.../common/SortMergeJoinFrameProcessor.java | 22 +-
.../common/SortMergeJoinFrameProcessorFactory.java | 25 +++
.../SortMergeJoinFrameProcessorFactoryTest.java | 241 ++++++++++++++++++++
.../common/SortMergeJoinFrameProcessorTest.java | 242 +++++++++++++++++++++
.../druid/frame/key/FrameComparisonWidget.java | 15 +-
.../druid/frame/key/FrameComparisonWidgetImpl.java | 25 ++-
.../frame/key/FrameComparisonWidgetImplTest.java | 17 +-
.../druid/sql/calcite/planner/JoinAlgorithm.java | 17 --
.../druid/sql/calcite/CalciteJoinQueryTest.java | 22 +-
10 files changed, 576 insertions(+), 58 deletions(-)
diff --git
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/DataSourcePlan.java
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/DataSourcePlan.java
index 566b084ad36..56fae646a4a 100644
---
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/DataSourcePlan.java
+++
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/DataSourcePlan.java
@@ -333,19 +333,15 @@ public class DataSourcePlan
/**
* Checks if the sortMerge algorithm can execute a particular join condition.
*
- * Two checks:
- * (1) join condition on two tables "table1" and "table2" is of the form
+ * One check: join condition on two tables "table1" and "table2" is of the
form
* table1.columnA = table2.columnA && table1.columnB = table2.columnB && ....
- *
- * (2) join condition uses equals, not IS NOT DISTINCT FROM [sortMerge
processor does not currently implement
- * IS NOT DISTINCT FROM]
*/
private static boolean canUseSortMergeJoin(JoinConditionAnalysis
joinConditionAnalysis)
{
return joinConditionAnalysis
.getEquiConditions()
.stream()
- .allMatch(equality -> equality.getLeftExpr().isIdentifier() &&
!equality.isIncludeNull());
+ .allMatch(equality -> equality.getLeftExpr().isIdentifier());
}
/**
diff --git
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessor.java
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessor.java
index 4b3854883a2..0fd85c6d082 100644
---
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessor.java
+++
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessor.java
@@ -138,6 +138,7 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
FrameWriterFactory frameWriterFactory,
String rightPrefix,
List<List<KeyColumn>> keyColumns,
+ int[] requiredNonNullKeyParts,
JoinType joinType,
long maxBufferedBytes
)
@@ -148,8 +149,8 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
this.rightPrefix = rightPrefix;
this.joinType = joinType;
this.trackers = ImmutableList.of(
- new Tracker(left, keyColumns.get(LEFT), maxBufferedBytes),
- new Tracker(right, keyColumns.get(RIGHT), maxBufferedBytes)
+ new Tracker(left, keyColumns.get(LEFT), requiredNonNullKeyParts,
maxBufferedBytes),
+ new Tracker(right, keyColumns.get(RIGHT), requiredNonNullKeyParts,
maxBufferedBytes)
);
this.maxBufferedBytes = maxBufferedBytes;
}
@@ -195,7 +196,7 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
// Two rows match if the keys compare equal _and_ neither key has a null
component. (x JOIN y ON x.a = y.a does
// not match rows where "x.a" is null.)
- final boolean marksMatch = markCmp == 0 &&
trackers.get(LEFT).hasCompletelyNonNullMark();
+ final boolean marksMatch = markCmp == 0 &&
trackers.get(LEFT).markHasRequiredNonNullKeyParts();
// If marked keys are equal on both sides ("marksMatch"), at least one
side needs to have a complete set of rows
// for the marked key. Check if this is true, otherwise call nextAwait
to read more data.
@@ -446,7 +447,7 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
/**
* Compares the marked rows of the two {@link #trackers}. This method
returns 0 if both sides are null, even
* though this is not considered a match by join semantics. Therefore, it is
important to also check
- * {@link Tracker#hasCompletelyNonNullMark()}.
+ * {@link Tracker#markHasRequiredNonNullKeyParts()}.
*
* @return negative if {@link #LEFT} key is earlier, positive if {@link
#RIGHT} key is earlier, zero if the keys
* are the same. Returns zero even if a key component is null, even though
this is not considered a match by
@@ -549,6 +550,7 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
private final List<FrameHolder> holders = new ArrayList<>();
private final ReadableInput input;
private final List<KeyColumn> keyColumns;
+ private final int[] requiredNonNullKeyParts;
private final long maxBytesBuffered;
// markFrame and markRow are the first frame and row with the current key.
@@ -561,10 +563,16 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
// done indicates that no more data is available in the channel.
private boolean done;
- public Tracker(ReadableInput input, List<KeyColumn> keyColumns, long
maxBytesBuffered)
+ public Tracker(
+ final ReadableInput input,
+ final List<KeyColumn> keyColumns,
+ final int[] requiredNonNullKeyParts,
+ final long maxBytesBuffered
+ )
{
this.input = input;
this.keyColumns = keyColumns;
+ this.requiredNonNullKeyParts = requiredNonNullKeyParts;
this.maxBytesBuffered = maxBytesBuffered;
}
@@ -686,9 +694,9 @@ public class SortMergeJoinFrameProcessor implements
FrameProcessor<Object>
/**
* Whether this tracker has a marked row that is completely nonnull.
*/
- public boolean hasCompletelyNonNullMark()
+ public boolean markHasRequiredNonNullKeyParts()
{
- return hasMark() &&
holders.get(markFrame).comparisonWidget.isCompletelyNonNullKey(markRow);
+ return hasMark() &&
holders.get(markFrame).comparisonWidget.hasNonNullKeyParts(markRow,
requiredNonNullKeyParts);
}
/**
diff --git
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactory.java
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactory.java
index ef4d9f280a9..7a81c59cc11 100644
---
a/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactory.java
+++
b/extensions-core/multi-stage-query/src/main/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactory.java
@@ -28,6 +28,8 @@ import com.google.common.collect.ImmutableList;
import com.google.common.collect.Iterables;
import it.unimi.dsi.fastutil.ints.Int2ObjectAVLTreeMap;
import it.unimi.dsi.fastutil.ints.Int2ObjectMap;
+import it.unimi.dsi.fastutil.ints.IntArrayList;
+import it.unimi.dsi.fastutil.ints.IntList;
import org.apache.druid.frame.key.KeyColumn;
import org.apache.druid.frame.key.KeyOrder;
import org.apache.druid.frame.processor.FrameProcessor;
@@ -142,6 +144,7 @@ public class SortMergeJoinFrameProcessorFactory extends
BaseFrameProcessorFactor
// Compute key columns.
final List<List<KeyColumn>> keyColumns = toKeyColumns(condition);
+ final int[] requiredNonNullKeyParts = toRequiredNonNullKeyParts(condition);
// Stitch up the inputs and validate each input channel signature.
// If validateInputFrameSignatures fails, it's a precondition violation:
this class somehow got bad inputs.
@@ -180,6 +183,7 @@ public class SortMergeJoinFrameProcessorFactory extends
BaseFrameProcessorFactor
stageDefinition.createFrameWriterFactory(outputChannel.getFrameMemoryAllocator()),
rightPrefix,
keyColumns,
+ requiredNonNullKeyParts,
joinType,
frameContext.memoryParameters().getSortMergeJoinMemory()
);
@@ -217,6 +221,27 @@ public class SortMergeJoinFrameProcessorFactory extends
BaseFrameProcessorFactor
return retVal;
}
+ /**
+ * Extracts a list of key parts that must be nonnull from a {@link
JoinConditionAnalysis}. These are equality
+ * conditions for which {@link Equality#isIncludeNull()} is false.
+ *
+ * The condition must have been validated by {@link
#validateCondition(JoinConditionAnalysis)}.
+ */
+ public static int[] toRequiredNonNullKeyParts(final JoinConditionAnalysis
condition)
+ {
+ final IntList retVal = new
IntArrayList(condition.getEquiConditions().size());
+
+ final List<Equality> equiConditions = condition.getEquiConditions();
+ for (int i = 0; i < equiConditions.size(); i++) {
+ Equality equiCondition = equiConditions.get(i);
+ if (!equiCondition.isIncludeNull()) {
+ retVal.add(i);
+ }
+ }
+
+ return retVal.toArray(new int[0]);
+ }
+
/**
* Validates that a join condition can be handled by this processor. Returns
the condition if it can be handled.
* Throws {@link IllegalArgumentException} if the condition cannot be
handled.
diff --git
a/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactoryTest.java
b/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactoryTest.java
new file mode 100644
index 00000000000..d1b2730bd0c
--- /dev/null
+++
b/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorFactoryTest.java
@@ -0,0 +1,241 @@
+/*
+ * 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.druid.msq.querykit.common;
+
+import com.google.common.collect.ImmutableList;
+import org.apache.druid.frame.key.KeyColumn;
+import org.apache.druid.frame.key.KeyOrder;
+import org.apache.druid.math.expr.ExprMacroTable;
+import org.apache.druid.segment.join.JoinConditionAnalysis;
+import org.junit.Assert;
+import org.junit.Test;
+
+public class SortMergeJoinFrameProcessorFactoryTest
+{
+ @Test
+ public void test_validateCondition()
+ {
+ Assert.assertNotNull(
+ SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("1", "j.",
ExprMacroTable.nil())
+ )
+ );
+
+ Assert.assertNotNull(
+ SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("x == \"j.y\"", "j.",
ExprMacroTable.nil())
+ )
+ );
+
+ Assert.assertNotNull(
+ SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("1", "j.",
ExprMacroTable.nil())
+ )
+ );
+
+ Assert.assertNotNull(
+ SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("x == \"j.y\" && a ==
\"j.b\"", "j.", ExprMacroTable.nil())
+ )
+ );
+
+ Assert.assertNotNull(
+ SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression(
+ "notdistinctfrom(x, \"j.y\") && a == \"j.b\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertThrows(
+ IllegalArgumentException.class,
+ () -> SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("x == y", "j.",
ExprMacroTable.nil())
+ )
+ );
+
+ Assert.assertThrows(
+ IllegalArgumentException.class,
+ () -> SortMergeJoinFrameProcessorFactory.validateCondition(
+ JoinConditionAnalysis.forExpression("x + 1 == \"j.y\"", "j.",
ExprMacroTable.nil())
+ )
+ );
+ }
+
+ @Test
+ public void test_toKeyColumns()
+ {
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("x", KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("y", KeyOrder.ASCENDING))
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(),
+ ImmutableList.of()
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "1",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("x", KeyOrder.ASCENDING), new
KeyColumn("a", KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("y", KeyOrder.ASCENDING), new
KeyColumn("b", KeyOrder.ASCENDING))
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\" && a == \"j.b\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("x", KeyOrder.ASCENDING), new
KeyColumn("a", KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("y", KeyOrder.ASCENDING), new
KeyColumn("b", KeyOrder.ASCENDING))
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\" && notdistinctfrom(a, \"j.b\")",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("x", KeyOrder.ASCENDING), new
KeyColumn("a", KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("y", KeyOrder.ASCENDING), new
KeyColumn("b", KeyOrder.ASCENDING))
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "notdistinctfrom(x, \"j.y\") && a == \"j.b\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertEquals(
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("x", KeyOrder.ASCENDING), new
KeyColumn("a", KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("y", KeyOrder.ASCENDING), new
KeyColumn("b", KeyOrder.ASCENDING))
+ ),
+ SortMergeJoinFrameProcessorFactory.toKeyColumns(
+ JoinConditionAnalysis.forExpression(
+ "notdistinctfrom(x, \"j.y\") && notdistinctfrom(a, \"j.b\")",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+ }
+
+ @Test
+ public void test_toRequiredNonNullKeyParts()
+ {
+ Assert.assertArrayEquals(
+ new int[0],
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "1",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertArrayEquals(
+ new int[]{0},
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertArrayEquals(
+ new int[]{0, 1},
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\" && a == \"j.b\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertArrayEquals(
+ new int[]{0},
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "x == \"j.y\" && notdistinctfrom(a, \"j.b\")",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertArrayEquals(
+ new int[]{1},
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "notdistinctfrom(x, \"j.y\") && a == \"j.b\"",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+
+ Assert.assertArrayEquals(
+ new int[0],
+ SortMergeJoinFrameProcessorFactory.toRequiredNonNullKeyParts(
+ JoinConditionAnalysis.forExpression(
+ "notdistinctfrom(x, \"j.y\") && notdistinctfrom(a, \"j.b\")",
+ "j.",
+ ExprMacroTable.nil()
+ )
+ )
+ );
+ }
+}
diff --git
a/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorTest.java
b/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorTest.java
index 4b750f167d8..20e4d487107 100644
---
a/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorTest.java
+++
b/extensions-core/multi-stage-query/src/test/java/org/apache/druid/msq/querykit/common/SortMergeJoinFrameProcessorTest.java
@@ -164,6 +164,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.LEFT,
MAX_BUFFERED_BYTES
);
@@ -209,6 +210,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.LEFT,
MAX_BUFFERED_BYTES
);
@@ -285,6 +287,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.INNER,
MAX_BUFFERED_BYTES
);
@@ -326,6 +329,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.LEFT,
MAX_BUFFERED_BYTES
);
@@ -397,6 +401,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
makeFrameWriterFactory(joinSignature),
"j0.",
ImmutableList.of(Collections.emptyList(), Collections.emptyList()),
+ new int[0],
JoinType.INNER,
MAX_BUFFERED_BYTES
);
@@ -510,6 +515,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
new KeyColumn("regionIsoCode", KeyOrder.ASCENDING)
)
),
+ new int[]{0, 1},
JoinType.LEFT,
MAX_BUFFERED_BYTES
);
@@ -589,6 +595,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.RIGHT,
MAX_BUFFERED_BYTES
);
@@ -671,6 +678,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.FULL,
MAX_BUFFERED_BYTES
);
@@ -716,6 +724,165 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
assertResult(processor, outputChannel.readable(), joinSignature,
expectedRows);
}
+ @Test
+ public void testInnerJoinRegionCodeOnly() throws Exception
+ {
+ // This join generates duplicates.
+
+ final ReadableInput factChannel =
+ buildFactInput(
+ ImmutableList.of(
+ new KeyColumn("regionIsoCode", KeyOrder.ASCENDING),
+ new KeyColumn("page", KeyOrder.ASCENDING)
+ )
+ );
+
+ final ReadableInput regionsChannel =
+ buildRegionsInput(
+ ImmutableList.of(
+ new KeyColumn("regionIsoCode", KeyOrder.ASCENDING),
+ new KeyColumn("countryIsoCode", KeyOrder.ASCENDING)
+ )
+ );
+
+ final BlockingQueueFrameChannel outputChannel =
BlockingQueueFrameChannel.minimal();
+
+ final RowSignature joinSignature =
+ RowSignature.builder()
+ .add("j0.page", ColumnType.STRING)
+ .add("regionName", ColumnType.STRING)
+ .add("j0.countryIsoCode", ColumnType.STRING)
+ .build();
+
+ final SortMergeJoinFrameProcessor processor = new
SortMergeJoinFrameProcessor(
+ regionsChannel,
+ factChannel,
+ outputChannel.writable(),
+ makeFrameWriterFactory(joinSignature),
+ "j0.",
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING))
+ ),
+ new int[]{0},
+ JoinType.INNER,
+ MAX_BUFFERED_BYTES
+ );
+
+ final List<List<Object>> expectedRows = Arrays.asList(
+ Arrays.asList("유희왕 GX", "Seoul", "KR"),
+ Arrays.asList("青野武", "Tōkyō", "JP"),
+ Arrays.asList("Алиса в Зазеркалье", "Finnmark Fylke", "NO"),
+ Arrays.asList("Saison 9 de Secret Story", "Val d'Oise", "FR"),
+ Arrays.asList("Cream Soda", "Ainigriv", "SU"),
+ Arrays.asList("Carlo Curti", "California", "US"),
+ Arrays.asList("Otjiwarongo Airport", "California", "US"),
+ Arrays.asList("President of India", "California", "US"),
+ Arrays.asList("Mathis Bolly", "Mexico City", "MX"),
+ Arrays.asList("Gabinete Ministerial de Rafael Correa", "Provincia del
Guayas", "EC"),
+ Arrays.asList("Diskussion:Sebastian Schulz", "Hesse", "DE"),
+ Arrays.asList("Glasgow", "Kingston upon Hull", "GB"),
+ Arrays.asList("History of Fourems", "Fourems Province", "MMMM"),
+ Arrays.asList("DirecTV", "North Carolina", "US"),
+ Arrays.asList("Peremptory norm", "New South Wales", "AU"),
+ Arrays.asList("Didier Leclair", "Ontario", "CA"),
+ Arrays.asList("Sarah Michelle Gellar", "Ontario", "CA"),
+ Arrays.asList("Les Argonautes", "Quebec", "CA"),
+ Arrays.asList("Golpe de Estado en Chile de 1973", "Santiago
Metropolitan", "CL"),
+ Arrays.asList("Wendigo", "Departamento de San Salvador", "SV"),
+ Arrays.asList("Giusy Ferreri discography", "Provincia di Varese",
"IT"),
+ Arrays.asList("Giusy Ferreri discography", "Virginia", "IT"),
+ Arrays.asList("Old Anatolian Turkish", "Provincia di Varese", "US"),
+ Arrays.asList("Old Anatolian Turkish", "Virginia", "US"),
+ Arrays.asList("Roma-Bangkok", "Provincia di Varese", "IT"),
+ Arrays.asList("Roma-Bangkok", "Virginia", "IT")
+ );
+
+ assertResult(processor, outputChannel.readable(), joinSignature,
expectedRows);
+ }
+
+ @Test
+ public void testInnerJoinRegionCodeOnlyIsNotDistinctFrom() throws Exception
+ {
+ // This join generates duplicates.
+
+ final ReadableInput factChannel =
+ buildFactInput(
+ ImmutableList.of(
+ new KeyColumn("regionIsoCode", KeyOrder.ASCENDING),
+ new KeyColumn("page", KeyOrder.ASCENDING)
+ )
+ );
+
+ final ReadableInput regionsChannel =
+ buildRegionsInput(
+ ImmutableList.of(
+ new KeyColumn("regionIsoCode", KeyOrder.ASCENDING),
+ new KeyColumn("countryIsoCode", KeyOrder.ASCENDING)
+ )
+ );
+
+ final BlockingQueueFrameChannel outputChannel =
BlockingQueueFrameChannel.minimal();
+
+ final RowSignature joinSignature =
+ RowSignature.builder()
+ .add("j0.page", ColumnType.STRING)
+ .add("regionName", ColumnType.STRING)
+ .add("j0.countryIsoCode", ColumnType.STRING)
+ .build();
+
+ final SortMergeJoinFrameProcessor processor = new
SortMergeJoinFrameProcessor(
+ regionsChannel,
+ factChannel,
+ outputChannel.writable(),
+ makeFrameWriterFactory(joinSignature),
+ "j0.",
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("regionIsoCode",
KeyOrder.ASCENDING))
+ ),
+ new int[0], // empty array: act as if IS NOT DISTINCT FROM
+ JoinType.INNER,
+ MAX_BUFFERED_BYTES
+ );
+
+ final List<List<Object>> expectedRows = Arrays.asList(
+ Arrays.asList("Agama mossambica", "Nulland", null),
+ Arrays.asList("Apamea abruzzorum", "Nulland", null),
+ Arrays.asList("Atractus flammigerus", "Nulland", null),
+ Arrays.asList("Rallicula", "Nulland", null),
+ Arrays.asList("Talk:Oswald Tilghman", "Nulland", null),
+ Arrays.asList("유희왕 GX", "Seoul", "KR"),
+ Arrays.asList("青野武", "Tōkyō", "JP"),
+ Arrays.asList("Алиса в Зазеркалье", "Finnmark Fylke", "NO"),
+ Arrays.asList("Saison 9 de Secret Story", "Val d'Oise", "FR"),
+ Arrays.asList("Cream Soda", "Ainigriv", "SU"),
+ Arrays.asList("Carlo Curti", "California", "US"),
+ Arrays.asList("Otjiwarongo Airport", "California", "US"),
+ Arrays.asList("President of India", "California", "US"),
+ Arrays.asList("Mathis Bolly", "Mexico City", "MX"),
+ Arrays.asList("Gabinete Ministerial de Rafael Correa", "Provincia del
Guayas", "EC"),
+ Arrays.asList("Diskussion:Sebastian Schulz", "Hesse", "DE"),
+ Arrays.asList("Glasgow", "Kingston upon Hull", "GB"),
+ Arrays.asList("History of Fourems", "Fourems Province", "MMMM"),
+ Arrays.asList("DirecTV", "North Carolina", "US"),
+ Arrays.asList("Peremptory norm", "New South Wales", "AU"),
+ Arrays.asList("Didier Leclair", "Ontario", "CA"),
+ Arrays.asList("Sarah Michelle Gellar", "Ontario", "CA"),
+ Arrays.asList("Les Argonautes", "Quebec", "CA"),
+ Arrays.asList("Golpe de Estado en Chile de 1973", "Santiago
Metropolitan", "CL"),
+ Arrays.asList("Wendigo", "Departamento de San Salvador", "SV"),
+ Arrays.asList("Giusy Ferreri discography", "Provincia di Varese",
"IT"),
+ Arrays.asList("Giusy Ferreri discography", "Virginia", "IT"),
+ Arrays.asList("Old Anatolian Turkish", "Provincia di Varese", "US"),
+ Arrays.asList("Old Anatolian Turkish", "Virginia", "US"),
+ Arrays.asList("Roma-Bangkok", "Provincia di Varese", "IT"),
+ Arrays.asList("Roma-Bangkok", "Virginia", "IT")
+ );
+
+ assertResult(processor, outputChannel.readable(), joinSignature,
expectedRows);
+ }
+
@Test
public void testLeftJoinCountryNumber() throws Exception
{
@@ -750,6 +917,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryNumber",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryNumber",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.LEFT,
MAX_BUFFERED_BYTES
);
@@ -844,6 +1012,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryNumber",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryNumber",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.RIGHT,
MAX_BUFFERED_BYTES
);
@@ -938,6 +1107,75 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
+ JoinType.INNER,
+ MAX_BUFFERED_BYTES
+ );
+
+ final List<List<Object>> expectedRows = Arrays.asList(
+ Arrays.asList("Peremptory norm", "AU", "AU", "Australia", 0L),
+ Arrays.asList("Didier Leclair", "CA", "CA", "Canada", 1L),
+ Arrays.asList("Les Argonautes", "CA", "CA", "Canada", 1L),
+ Arrays.asList("Sarah Michelle Gellar", "CA", "CA", "Canada", 1L),
+ Arrays.asList("Golpe de Estado en Chile de 1973", "CL", "CL", "Chile",
2L),
+ Arrays.asList("Diskussion:Sebastian Schulz", "DE", "DE", "Germany",
3L),
+ Arrays.asList("Gabinete Ministerial de Rafael Correa", "EC", "EC",
"Ecuador", 4L),
+ Arrays.asList("Saison 9 de Secret Story", "FR", "FR", "France", 5L),
+ Arrays.asList("Glasgow", "GB", "GB", "United Kingdom", 6L),
+ Arrays.asList("Giusy Ferreri discography", "IT", "IT", "Italy", 7L),
+ Arrays.asList("Roma-Bangkok", "IT", "IT", "Italy", 7L),
+ Arrays.asList("青野武", "JP", "JP", "Japan", 8L),
+ Arrays.asList("유희왕 GX", "KR", "KR", "Republic of Korea", 9L),
+ Arrays.asList("History of Fourems", "MMMM", "MMMM", "Fourems", 205L),
+ Arrays.asList("Mathis Bolly", "MX", "MX", "Mexico", 10L),
+ Arrays.asList("Алиса в Зазеркалье", "NO", "NO", "Norway", 11L),
+ Arrays.asList("Cream Soda", "SU", "SU", "States United", 15L),
+ Arrays.asList("Wendigo", "SV", "SV", "El Salvador", 12L),
+ Arrays.asList("Carlo Curti", "US", "US", "United States", 13L),
+ Arrays.asList("DirecTV", "US", "US", "United States", 13L),
+ Arrays.asList("Old Anatolian Turkish", "US", "US", "United States",
13L),
+ Arrays.asList("Otjiwarongo Airport", "US", "US", "United States", 13L),
+ Arrays.asList("President of India", "US", "US", "United States", 13L)
+ );
+
+ assertResult(processor, outputChannel.readable(), joinSignature,
expectedRows);
+ }
+
+ @Test
+ public void testInnerJoinCountryIsoCodeNotDistinctFrom() throws Exception
+ {
+ final ReadableInput factChannel = buildFactInput(
+ ImmutableList.of(
+ new KeyColumn("countryIsoCode", KeyOrder.ASCENDING),
+ new KeyColumn("page", KeyOrder.ASCENDING)
+ )
+ );
+
+ final ReadableInput countriesChannel =
+ buildCountriesInput(ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)));
+
+ final BlockingQueueFrameChannel outputChannel =
BlockingQueueFrameChannel.minimal();
+
+ final RowSignature joinSignature =
+ RowSignature.builder()
+ .add("page", ColumnType.STRING)
+ .add("countryIsoCode", ColumnType.STRING)
+ .add("j0.countryIsoCode", ColumnType.STRING)
+ .add("j0.countryName", ColumnType.STRING)
+ .add("j0.countryNumber", ColumnType.LONG)
+ .build();
+
+ final SortMergeJoinFrameProcessor processor = new
SortMergeJoinFrameProcessor(
+ factChannel,
+ countriesChannel,
+ outputChannel.writable(),
+ makeFrameWriterFactory(joinSignature),
+ "j0.",
+ ImmutableList.of(
+ ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
+ ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
+ ),
+ new int[0], // empty array: act as if IS NOT DISTINCT FROM
JoinType.INNER,
MAX_BUFFERED_BYTES
);
@@ -1005,6 +1243,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.INNER,
1
);
@@ -1072,6 +1311,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("countryIsoCode",
KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.INNER,
1
);
@@ -1128,6 +1368,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("channel", KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("channel", KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.INNER,
MAX_BUFFERED_BYTES
);
@@ -1182,6 +1423,7 @@ public class SortMergeJoinFrameProcessorTest extends
InitializedNullHandlingTest
ImmutableList.of(new KeyColumn("channel", KeyOrder.ASCENDING)),
ImmutableList.of(new KeyColumn("channel", KeyOrder.ASCENDING))
),
+ new int[]{0},
JoinType.INNER,
1
);
diff --git
a/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidget.java
b/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidget.java
index 710bfc3b5c9..913d775b80b 100644
---
a/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidget.java
+++
b/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidget.java
@@ -36,18 +36,25 @@ public interface FrameComparisonWidget
RowKey readKey(int row);
/**
- * Whether a particular row has no null fields in its comparison key.
+ * Whether particular key parts in a particular row are non-null.
*
* When {@link
org.apache.druid.common.config.NullHandling#replaceWithDefault()}, default
values (like empty strings
* and numeric zeroes) are considered null for purposes of this method. This
behavior is inherited from
* {@link org.apache.druid.frame.field.FieldReader#isNull(Memory, long)} and
enables join code to behave
* similarly in MSQ and native queries.
+ *
+ * @param row row number
+ * @param keyParts parts to check
*/
- boolean isCompletelyNonNullKey(int row);
+ boolean hasNonNullKeyParts(int row, int[] keyParts);
/**
* Compare a specific row of this frame to the provided key. The key must
have been created with sortColumns
* that match the ones used to create this widget, or else results are
undefined.
+ *
+ * Comparison considers null to be equal to null. Callers that need to
determine if key parts are null
+ * (perhaps because they *don't* want to consider null to be equal to null)
should use
+ * {@link #hasNonNullKeyParts(int, int[])} to check the relevant parts.
*/
int compare(int row, RowKey key);
@@ -55,6 +62,10 @@ public interface FrameComparisonWidget
* Compare a specific row of this frame to a specific row of another frame.
The other frame must have the same
* sort key, or else results are undefined. The other frame may be the same
object as this frame; for example,
* this is used by {@link org.apache.druid.frame.write.FrameSort} to sort
frames in-place.
+ *
+ * Comparison considers null to be equal to null. Callers that need to
determine if key parts are null
+ * (perhaps because they *don't* want to consider null to be equal to null)
should use
+ * {@link #hasNonNullKeyParts(int, int[])} to check the relevant parts.
*/
int compare(int row, FrameComparisonWidget otherWidget, int otherRow);
}
diff --git
a/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidgetImpl.java
b/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidgetImpl.java
index 873c9ef9243..d7008fcab45 100644
---
a/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidgetImpl.java
+++
b/processing/src/main/java/org/apache/druid/frame/key/FrameComparisonWidgetImpl.java
@@ -28,6 +28,7 @@ import org.apache.druid.frame.read.FrameReader;
import org.apache.druid.frame.read.FrameReaderUtils;
import org.apache.druid.frame.write.FrameWriterUtils;
import org.apache.druid.frame.write.RowBasedFrameWriter;
+import org.apache.druid.java.util.common.IAE;
import org.apache.druid.java.util.common.ISE;
import org.apache.druid.segment.column.RowSignature;
@@ -138,21 +139,29 @@ public class FrameComparisonWidgetImpl implements
FrameComparisonWidget
}
@Override
- public boolean isCompletelyNonNullKey(int row)
+ public boolean hasNonNullKeyParts(int row, int[] keyParts)
{
- if (keyFieldCount == 0) {
+ if (keyParts.length == 0) {
return true;
}
final long rowPosition = getRowPositionInDataRegion(row);
- long keyFieldPosition = rowPosition + (long) signature.size() *
Integer.BYTES;
- for (int i = 0; i < keyFieldCount; i++) {
- final boolean isNull = keyFieldReaders.get(i).isNull(dataRegion,
keyFieldPosition);
- if (isNull) {
- return false;
+ for (int i : keyParts) {
+ if (i < 0 || i >= keyFieldCount) {
+ throw new IAE("Invalid key part[%d]", i);
+ }
+
+ final long keyFieldPosition;
+
+ if (i == 0) {
+ keyFieldPosition = rowPosition + (long) signature.size() *
Integer.BYTES;
} else {
- keyFieldPosition = rowPosition + dataRegion.getInt(rowPosition +
(long) i * Integer.BYTES);
+ keyFieldPosition = rowPosition + dataRegion.getInt(rowPosition +
(long) (i - 1) * Integer.BYTES);
+ }
+
+ if (keyFieldReaders.get(i).isNull(dataRegion, keyFieldPosition)) {
+ return false;
}
}
diff --git
a/processing/src/test/java/org/apache/druid/frame/key/FrameComparisonWidgetImplTest.java
b/processing/src/test/java/org/apache/druid/frame/key/FrameComparisonWidgetImplTest.java
index 9dbb718fea2..64556c6775b 100644
---
a/processing/src/test/java/org/apache/druid/frame/key/FrameComparisonWidgetImplTest.java
+++
b/processing/src/test/java/org/apache/druid/frame/key/FrameComparisonWidgetImplTest.java
@@ -83,9 +83,14 @@ public class FrameComparisonWidgetImplTest extends
InitializedNullHandlingTest
final FrameComparisonWidget widget = createComparisonWidget(keyColumns);
for (int i = 0; i < frame.numRows(); i++) {
- final boolean isPartiallyNull =
-
Arrays.stream(RowKeyComparatorTest.ALL_KEY_OBJECTS.get(i)).limit(3).anyMatch(Objects::isNull);
- Assert.assertEquals(isPartiallyNull, !widget.isCompletelyNonNullKey(i));
+ final boolean isAllNonNull =
+
Arrays.stream(RowKeyComparatorTest.ALL_KEY_OBJECTS.get(i)).limit(3).allMatch(Objects::nonNull);
+
+ // null key part, if any, is always the second one (1)
+ Assert.assertTrue(widget.hasNonNullKeyParts(i, new int[0]));
+ Assert.assertTrue(widget.hasNonNullKeyParts(i, new int[]{0, 2}));
+ Assert.assertEquals(isAllNonNull, widget.hasNonNullKeyParts(i, new
int[]{0, 1, 2}));
+ Assert.assertEquals(isAllNonNull, widget.hasNonNullKeyParts(i, new
int[]{1}));
}
}
@@ -102,9 +107,9 @@ public class FrameComparisonWidgetImplTest extends
InitializedNullHandlingTest
final FrameComparisonWidget widget = createComparisonWidget(keyColumns);
for (int i = 0; i < frame.numRows(); i++) {
- final boolean isPartiallyNull =
-
Arrays.stream(RowKeyComparatorTest.ALL_KEY_OBJECTS.get(i)).anyMatch(Objects::isNull);
- Assert.assertEquals(isPartiallyNull, !widget.isCompletelyNonNullKey(i));
+ final boolean isAllNonNull =
+
Arrays.stream(RowKeyComparatorTest.ALL_KEY_OBJECTS.get(i)).allMatch(Objects::nonNull);
+ Assert.assertEquals(isAllNonNull, widget.hasNonNullKeyParts(i, new
int[]{0, 1, 2, 3}));
}
}
diff --git
a/sql/src/main/java/org/apache/druid/sql/calcite/planner/JoinAlgorithm.java
b/sql/src/main/java/org/apache/druid/sql/calcite/planner/JoinAlgorithm.java
index 6e6c872594f..2e53795c4ff 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/planner/JoinAlgorithm.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/planner/JoinAlgorithm.java
@@ -33,12 +33,6 @@ public enum JoinAlgorithm
{
return false;
}
-
- @Override
- public boolean canHandleLeftExpressions()
- {
- return true;
- }
},
SORT_MERGE("sortMerge") {
@Override
@@ -46,12 +40,6 @@ public enum JoinAlgorithm
{
return true;
}
-
- @Override
- public boolean canHandleLeftExpressions()
- {
- return false;
- }
};
private final String id;
@@ -84,11 +72,6 @@ public enum JoinAlgorithm
*/
public abstract boolean requiresSubquery();
- /**
- * Whether this join algorithm is able to handle left-hand side expressions.
- */
- public abstract boolean canHandleLeftExpressions();
-
@Override
public String toString()
{
diff --git
a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteJoinQueryTest.java
b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteJoinQueryTest.java
index 04727d7f86a..93ce1a7d102 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteJoinQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteJoinQueryTest.java
@@ -3863,13 +3863,16 @@ public class CalciteJoinQueryTest extends
BaseCalciteQueryTest
.context(queryContext)
.build()
),
- ImmutableList.of(
- new Object[]{"", ""},
- new Object[]{"10.1", "10.1"},
- new Object[]{"2", "2"},
- new Object[]{"1", "1"},
- new Object[]{"def", "def"},
- new Object[]{"abc", "abc"}
+ sortIfSortBased(
+ ImmutableList.of(
+ new Object[]{"", ""},
+ new Object[]{"10.1", "10.1"},
+ new Object[]{"2", "2"},
+ new Object[]{"1", "1"},
+ new Object[]{"def", "def"},
+ new Object[]{"abc", "abc"}
+ ),
+ 0
)
);
}
@@ -3879,11 +3882,6 @@ public class CalciteJoinQueryTest extends
BaseCalciteQueryTest
@ParameterizedTest(name = "{0}")
public void testInnerJoinSubqueryWithSelectorFilter(Map<String, Object>
queryContext)
{
- if (isSortBasedJoin()) {
- // Cannot handle the [l1.k = 'abc'] condition.
- msqIncompatible();
- }
-
// Cannot vectorize due to 'concat' expression.
cannotVectorize();
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
