This is an automated email from the ASF dual-hosted git repository.
yashmayya pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/pinot.git
The following commit(s) were added to refs/heads/master by this push:
new 7701cfd9237 Build the lookup join key from the dimension table primary
key in MSE (#19210)
7701cfd9237 is described below
commit 7701cfd92374fd22a0ffe85f5fcc3b49a8f04669
Author: Yash Mayya <[email protected]>
AuthorDate: Tue Aug 18 00:35:14 2026 -0400
Build the lookup join key from the dimension table primary key in MSE
(#19210)
---
.../query/runtime/operator/LookupJoinOperator.java | 232 +++++++++++++++++--
.../runtime/operator/LookupJoinOperatorTest.java | 247 +++++++++++++++++++++
.../runtime/queries/ResourceBasedQueriesTest.java | 58 +++--
.../src/test/resources/queries/LookupJoin.json | 167 ++++++++++++++
4 files changed, 669 insertions(+), 35 deletions(-)
diff --git
a/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/LookupJoinOperator.java
b/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/LookupJoinOperator.java
index 087f8dd08e5..4debec7e08b 100644
---
a/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/LookupJoinOperator.java
+++
b/pinot-query-runtime/src/main/java/org/apache/pinot/query/runtime/operator/LookupJoinOperator.java
@@ -18,14 +18,19 @@
*/
package org.apache.pinot.query.runtime.operator;
+import com.google.common.annotations.VisibleForTesting;
import com.google.common.base.Preconditions;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.List;
import java.util.Set;
import javax.annotation.Nullable;
import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.commons.collections4.CollectionUtils;
import org.apache.pinot.common.datatable.StatMap;
import org.apache.pinot.common.utils.DataSchema;
+import org.apache.pinot.common.utils.DataSchema.ColumnDataType;
import org.apache.pinot.core.data.manager.offline.DimensionTableDataManager;
import org.apache.pinot.core.query.request.ServerQueryRequest;
import org.apache.pinot.core.query.request.context.QueryContext;
@@ -53,17 +58,32 @@ import org.slf4j.LoggerFactory;
///
/// Since right table is a dimension table which is replicated across all
servers, RIGHT and FULL join are not
/// supported to avoid duplication.
+///
+/// The dimension table is a hash map keyed by the primary key values, so the
lookup key must contain one value per
+/// primary key column, in the order the dimension table schema declares them.
The join condition does not provide the
+/// key in that shape:
+///
+/// - The equi-join keys are ordered by the join condition, not by the primary
key.
+/// - A primary key column can be constrained by a constant (`dim.col = 'x'`)
instead of by an equi-join key. Calcite
+/// classifies such a condition as a non-equi condition, so it is absent
from the join keys.
+///
+/// The constructor therefore compiles a key plan that maps every primary key
column to its value source, and rejects
+/// join conditions that cannot produce a complete key. See [#compileKeyPlan].
public class LookupJoinOperator extends MultiStageOperator {
private static final Logger LOGGER =
LoggerFactory.getLogger(LookupJoinOperator.class);
private static final String EXPLAIN_NAME = "LOOKUP_JOIN";
private static final Set<JoinRelType> SUPPORTED_JOIN_TYPES =
Set.of(JoinRelType.INNER, JoinRelType.LEFT, JoinRelType.SEMI,
JoinRelType.ANTI);
+ /// Marks a key position that no join condition binds. It is an error for
one to survive [#compileKeyPlan].
+ private static final int KEY_SOURCE_UNBOUND = -1;
+ /// Marks a key position whose value is a constant held in [#_keyConstants].
+ private static final int KEY_SOURCE_CONSTANT = -2;
+
private final MultiStageOperator _leftInput;
private final int _leftColumnSize;
private final LeafOperator _rightInput;
private final JoinRelType _joinType;
- private final int[] _leftKeyIds;
private final DimensionTableDataManager _rightTable;
private final String[] _rightColumns;
private final DataSchema _resultSchema;
@@ -71,6 +91,16 @@ public class LookupJoinOperator extends MultiStageOperator {
private final List<TransformOperand> _nonEquiEvaluators;
private final StatMap<StatKey> _statMap = new StatMap<>(StatKey.class);
+ /// Number of primary key columns of the dimension table, i.e. the size of
every lookup key.
+ private final int _keySize;
+ /// Value source of each key position: a left row index, or
[#KEY_SOURCE_CONSTANT]. [#compileKeyPlan] rejects the
+ /// plan if any position is still [#KEY_SOURCE_UNBOUND], so that value never
reaches the probe path.
+ private final int[] _keySources;
+ /// Constant value of each key position whose source is
[#KEY_SOURCE_CONSTANT].
+ private final Object[] _keyConstants;
+ /// Set when a constant key value is null. A null never matches a primary
key, so every lookup misses.
+ private final boolean _neverMatches;
+
public LookupJoinOperator(OpChainExecutionContext context,
MultiStageOperator leftInput, DataSchema leftSchema,
MultiStageOperator rightInput, JoinNode node) {
super(context);
@@ -82,11 +112,6 @@ public class LookupJoinOperator extends MultiStageOperator {
Preconditions.checkState(SUPPORTED_JOIN_TYPES.contains(_joinType), "Join
type: % is not supported for lookup join",
_joinType);
- List<Integer> leftKeys = node.getLeftKeys();
- _leftKeyIds = new int[leftKeys.size()];
- for (int i = 0; i < leftKeys.size(); i++) {
- _leftKeyIds[i] = leftKeys.get(i);
- }
List<ServerQueryRequest> leafStageRequests = _rightInput.getRequests();
Preconditions.checkState(leafStageRequests.size() == 1, "Lookup join
cannot be applied to hybrid tables");
QueryContext queryContext = leafStageRequests.get(0).getQueryContext();
@@ -97,10 +122,179 @@ public class LookupJoinOperator extends
MultiStageOperator {
_resultSchema = node.getDataSchema();
_resultColumnSize = _resultSchema.size();
List<RexExpression> nonEquiConditions = node.getNonEquiConditions();
+ // SEMI and ANTI joins project the left columns only, so an evaluator
built over the join result schema cannot
+ // reference a dimension table column. Reject the combination here,
otherwise the loop below fails with an index
+ // error that says nothing about the cause.
+ Preconditions.checkState(nonEquiConditions.isEmpty() ||
_joinType.projectsRight(),
+ "Lookup join type: %s does not support non-equi join conditions, got:
%s", _joinType, nonEquiConditions);
_nonEquiEvaluators = new ArrayList<>(nonEquiConditions.size());
for (RexExpression nonEquiCondition : nonEquiConditions) {
_nonEquiEvaluators.add(TransformOperandFactory.getTransformOperand(nonEquiCondition,
_resultSchema));
}
+
+ KeyPlan keyPlan =
+ compileKeyPlan(node, rightTableName,
_rightTable.getPrimaryKeyColumns(), _rightInput.getDataSchema(),
+ _leftColumnSize);
+ _keySize = keyPlan._sources.length;
+ _keySources = keyPlan._sources;
+ _keyConstants = keyPlan._constants;
+ _neverMatches = keyPlan._neverMatches;
+ }
+
+ /// Works out where each value of the lookup key comes from.
+ ///
+ /// The key has one position per dimension table primary key column, in the
order the dimension table schema declares
+ /// them. Each position is bound in two passes:
+ ///
+ /// 1. Equi-join keys. `rightKeys[i]` names a dimension column, and that
column's position in the primary key decides
+ /// where `leftKeys[i]` lands. This is what makes the key independent of
the order of the join condition.
+ /// 2. Constants. A non-equi condition of the form `dim_column = literal`
binds a position that pass 1 left open.
+ /// A constant never replaces an equi-join key, because the equi-join key
is not kept anywhere else and dropping it
+ /// would silently widen the join. A constant that pass 1 already bound
stays in [#_nonEquiEvaluators] and runs as
+ /// a filter after the lookup, which is what the SQL semantics require.
+ ///
+ /// The method rejects a join condition that cannot produce exactly one
value per primary key column. Every rejected
+ /// case returned no rows or wrong rows before this validation existed, so
an error is the better outcome. This is
+ /// also the contract that the single-stage `lookup` transform function
enforces.
+ @VisibleForTesting
+ static KeyPlan compileKeyPlan(JoinNode node, String tableName, @Nullable
List<String> primaryKeyColumns,
+ DataSchema rightSchema, int leftColumnSize) {
+ Preconditions.checkState(CollectionUtils.isNotEmpty(primaryKeyColumns),
+ "Failed to find primary key columns for dimension table: %s",
tableName);
+ String[] rightColumns = rightSchema.getColumnNames();
+ int keySize = primaryKeyColumns.size();
+ int[] sources = new int[keySize];
+ Arrays.fill(sources, KEY_SOURCE_UNBOUND);
+ Object[] constants = new Object[keySize];
+
+ // Pass 1: bind key positions from the equi-join keys.
+ List<Integer> leftKeys = node.getLeftKeys();
+ List<Integer> rightKeys = node.getRightKeys();
+ int numEquiKeys = leftKeys.size();
+ for (int i = 0; i < numEquiKeys; i++) {
+ String rightColumn = rightColumns[rightKeys.get(i)];
+ int keyPosition = primaryKeyColumns.indexOf(rightColumn);
+ Preconditions.checkState(keyPosition >= 0,
+ "Lookup join on dimension table: %s has a join key on column: %s,
which is not a primary key column. "
+ + "Primary key columns: %s", tableName, rightColumn,
primaryKeyColumns);
+ Preconditions.checkState(sources[keyPosition] == KEY_SOURCE_UNBOUND,
+ "Lookup join on dimension table: %s has multiple join keys on
primary key column: %s", tableName,
+ rightColumn);
+ sources[keyPosition] = leftKeys.get(i);
+ }
+
+ // Pass 2: bind the remaining key positions from constant equality
conditions.
+ boolean neverMatches = false;
+ for (RexExpression nonEquiCondition : node.getNonEquiConditions()) {
+ int rightColumnId = getConstantEqualityColumnId(nonEquiCondition,
leftColumnSize, rightColumns.length);
+ if (rightColumnId < 0) {
+ continue;
+ }
+ int keyPosition = primaryKeyColumns.indexOf(rightColumns[rightColumnId]);
+ if (keyPosition < 0 || sources[keyPosition] != KEY_SOURCE_UNBOUND) {
+ continue;
+ }
+ sources[keyPosition] = KEY_SOURCE_CONSTANT;
+ RexExpression.Literal literal = getConstantLiteral(nonEquiCondition);
+ Object value = literal.getValue();
+ if (value != null) {
+ checkConstantType(literal,
rightSchema.getColumnDataType(rightColumnId), tableName,
+ rightColumns[rightColumnId]);
+ }
+ constants[keyPosition] = value;
+ neverMatches |= value == null;
+ }
+
+ List<String> unboundColumns = new ArrayList<>();
+ for (int i = 0; i < keySize; i++) {
+ if (sources[i] == KEY_SOURCE_UNBOUND) {
+ unboundColumns.add(primaryKeyColumns.get(i));
+ }
+ }
+ Preconditions.checkState(unboundColumns.isEmpty(),
+ "Lookup join on dimension table: %s cannot determine primary key
columns: %s from the join condition. "
+ + "A lookup join reads the dimension table by primary key, so the
join condition must have an equality on "
+ + "every primary key column: %s. Add the missing conditions, or
remove the lookup join hint to use a hash "
+ + "join instead.", tableName, unboundColumns, primaryKeyColumns);
+ return new KeyPlan(sources, constants, neverMatches);
+ }
+
+ /// Returns the dimension table column id of a `dim_column = literal`
condition, or -1 when the condition does not
+ /// have that shape. Only an equality against a single literal can serve as
a key value. A condition such as
+ /// `dim_column IN ('a', 'b')` reaches this method as a disjunction and
returns -1, because a hash lookup cannot read
+ /// a set of keys.
+ ///
+ /// Non-equi conditions index the joined row, so the dimension table columns
start at `leftColumnSize`.
+ private static int getConstantEqualityColumnId(RexExpression condition, int
leftColumnSize, int numRightColumns) {
+ if (!(condition instanceof RexExpression.FunctionCall)) {
+ return -1;
+ }
+ RexExpression.FunctionCall functionCall = (RexExpression.FunctionCall)
condition;
+ if (!functionCall.getFunctionName().equals(SqlKind.EQUALS.name())) {
+ return -1;
+ }
+ List<RexExpression> operands = functionCall.getFunctionOperands();
+ if (operands.size() != 2) {
+ return -1;
+ }
+ RexExpression inputRef = operands.get(0) instanceof RexExpression.InputRef
? operands.get(0) : operands.get(1);
+ RexExpression literal = operands.get(0) instanceof RexExpression.InputRef
? operands.get(1) : operands.get(0);
+ if (!(inputRef instanceof RexExpression.InputRef) || !(literal instanceof
RexExpression.Literal)) {
+ return -1;
+ }
+ int columnId = ((RexExpression.InputRef) inputRef).getIndex() -
leftColumnSize;
+ return columnId >= 0 && columnId < numRightColumns ? columnId : -1;
+ }
+
+ /// Returns the literal of a condition that [#getConstantEqualityColumnId]
accepted.
+ private static RexExpression.Literal getConstantLiteral(RexExpression
condition) {
+ List<RexExpression> operands = ((RexExpression.FunctionCall)
condition).getFunctionOperands();
+ return (RexExpression.Literal) (operands.get(0) instanceof
RexExpression.Literal ? operands.get(0)
+ : operands.get(1));
+ }
+
+ /// Makes sure that a constant can serve as a lookup key value for the given
dimension table column.
+ ///
+ /// The operator does not convert the constant. The planner coerces the
operands of a comparison, so a literal
+ /// compared against a dimension column already carries the type of that
column. This check states that assumption,
+ /// and fails with the table and column named if the planner ever stops
holding it up. A constant of another type
+ /// would miss every row, because [PrimaryKey] compares values with `equals`
and an `Integer` never equals a `Long`.
+ ///
+ /// BYTES is rejected outright. A dimension table reads its key values with
+ ///
[org.apache.pinot.segment.local.segment.readers.PinotSegmentColumnReader],
which returns a raw `byte[]` whose
+ /// `equals` and `hashCode` are identity. No constant of any representation
can match such a key, so an error is
+ /// better than an empty result. Fixing that belongs in
`DimensionTableDataManager`, which would fix the single-stage
+ /// `lookup` transform function at the same time.
+ ///
+ /// BIG_DECIMAL is allowed. `BigDecimal#equals` compares the scale, so a
constant of `1.5` does not match a stored
+ /// `1.50`, but a hash join carries the same hazard through
[org.apache.pinot.query.runtime.operator.join.LookupTable]
+ /// and this operator does not single out one arm of it.
+ private static void checkConstantType(RexExpression.Literal literal,
ColumnDataType columnDataType, String tableName,
+ String column) {
+ ColumnDataType storedType = columnDataType.getStoredType();
+ Preconditions.checkState(storedType != ColumnDataType.BYTES,
+ "Lookup join on dimension table: %s does not support a constant on
primary key column: %s of type BYTES. "
+ + "Remove the lookup join hint to use a hash join instead.",
tableName, column);
+ Preconditions.checkState(literal.getDataType().getStoredType() ==
storedType,
+ "Lookup join on dimension table: %s got a constant of stored type: %s
on primary key column: %s of stored "
+ + "type: %s", tableName, literal.getDataType().getStoredType(),
column, storedType);
+ }
+
+ /// Value sources of the lookup key, one entry per dimension table primary
key column.
+ ///
+ /// `_sources` holds a left row index, or [#KEY_SOURCE_CONSTANT] when the
value is in `_constants` at the same
+ /// position. `_neverMatches` is set when a constant is null, which no
primary key value equals.
+ @VisibleForTesting
+ static class KeyPlan {
+ final int[] _sources;
+ final Object[] _constants;
+ final boolean _neverMatches;
+
+ KeyPlan(int[] sources, Object[] constants, boolean neverMatches) {
+ _sources = sources;
+ _constants = constants;
+ _neverMatches = neverMatches;
+ }
}
@Override
@@ -170,8 +364,7 @@ public class LookupJoinOperator extends MultiStageOperator {
ArrayList<Object[]> rows = new ArrayList<>(container.size());
for (Object[] leftRow : container) {
- PrimaryKey key = getKey(leftRow);
- Object[] rightRow = _rightTable.lookupValues(key, _rightColumns);
+ Object[] rightRow = _neverMatches ? null :
_rightTable.lookupValues(getKey(leftRow), _rightColumns);
if (rightRow != null) {
List<Object> resultRow = JoinedRowView.of(leftRow, rightRow,
_resultColumnSize, _leftColumnSize);
if (_nonEquiEvaluators.isEmpty() || _nonEquiEvaluators.stream()
@@ -191,8 +384,10 @@ public class LookupJoinOperator extends MultiStageOperator
{
private List<Object[]> buildJoinedDataBlockSemi(MseBlock.Data leftBlock) {
List<Object[]> container = leftBlock.asRowHeap().getRows();
+ // A constant key value only comes from a non-equi condition, which the
constructor rejects for this join type, so
+ // there is no null constant to short-circuit on here.
List<Object[]> rows = new ArrayList<>(container.size());
- PrimaryKey key = new PrimaryKey(new Object[_leftKeyIds.length]);
+ PrimaryKey key = new PrimaryKey(new Object[_keySize]);
for (Object[] leftRow : container) {
fillKey(leftRow, key);
@@ -205,8 +400,9 @@ public class LookupJoinOperator extends MultiStageOperator {
private List<Object[]> buildJoinedDataBlockAnti(MseBlock.Data leftBlock) {
List<Object[]> container = leftBlock.asRowHeap().getRows();
+ // See the note in buildJoinedDataBlockSemi on the absence of a null
constant short-circuit.
List<Object[]> rows = new ArrayList<>(container.size());
- PrimaryKey key = new PrimaryKey(new Object[_leftKeyIds.length]);
+ PrimaryKey key = new PrimaryKey(new Object[_keySize]);
for (Object[] leftRow : container) {
fillKey(leftRow, key);
@@ -218,17 +414,19 @@ public class LookupJoinOperator extends
MultiStageOperator {
}
private PrimaryKey getKey(Object[] row) {
- Object[] values = new Object[_leftKeyIds.length];
- for (int i = 0; i < _leftKeyIds.length; i++) {
- values[i] = row[_leftKeyIds[i]];
- }
+ Object[] values = new Object[_keySize];
+ fillKeyValues(row, values);
return new PrimaryKey(values);
}
private void fillKey(Object[] row, PrimaryKey key) {
- Object[] values = key.getValues();
- for (int i = 0; i < _leftKeyIds.length; i++) {
- values[i] = row[_leftKeyIds[i]];
+ fillKeyValues(row, key.getValues());
+ }
+
+ private void fillKeyValues(Object[] row, Object[] values) {
+ for (int i = 0; i < _keySize; i++) {
+ int source = _keySources[i];
+ values[i] = source == KEY_SOURCE_CONSTANT ? _keyConstants[i] :
row[source];
}
}
diff --git
a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/operator/LookupJoinOperatorTest.java
b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/operator/LookupJoinOperatorTest.java
new file mode 100644
index 00000000000..bec6a38f1bb
--- /dev/null
+++
b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/operator/LookupJoinOperatorTest.java
@@ -0,0 +1,247 @@
+/**
+ * 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.pinot.query.runtime.operator;
+
+import java.math.BigDecimal;
+import java.util.ArrayList;
+import java.util.List;
+import javax.annotation.Nullable;
+import org.apache.calcite.rel.core.JoinRelType;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.commons.lang3.tuple.Pair;
+import org.apache.pinot.common.utils.DataSchema;
+import org.apache.pinot.common.utils.DataSchema.ColumnDataType;
+import org.apache.pinot.query.planner.logical.RexExpression;
+import org.apache.pinot.query.planner.plannode.JoinNode;
+import org.apache.pinot.query.planner.plannode.PlanNode;
+import org.apache.pinot.spi.utils.ByteArray;
+import org.testng.annotations.Test;
+
+import static org.testng.Assert.assertEquals;
+import static org.testng.Assert.assertFalse;
+import static org.testng.Assert.assertNull;
+import static org.testng.Assert.assertTrue;
+import static org.testng.Assert.expectThrows;
+
+
+/// Tests [LookupJoinOperator#compileKeyPlan], which decides where each value
of the dimension table lookup key comes
+/// from.
+///
+/// The dimension table is a hash map keyed by the primary key values, so a
key is only usable when it holds one value
+/// per primary key column, in the order the dimension table schema declares
them, and with the type of each column.
+/// These tests cover the cases that a query alone cannot reach, such as a
null constant and a constant of the wrong
+/// type. End-to-end coverage is in `LookupJoin.json`.
+public class LookupJoinOperatorTest {
+ private static final String TABLE_NAME = "dim_tbl_OFFLINE";
+
+ /// Fact table columns. The dimension table columns of the joined row start
after these.
+ private static final int FACT_CURRENCY = 0;
+ private static final int FACT_RATE_START_DATE = 1;
+ private static final int LEFT_COLUMN_SIZE = 2;
+
+ /// Dimension table columns, in the order that the leaf stage reports them.
The order is not the primary key order,
+ /// which is what makes the key positions worth testing.
+ private static final int DIM_CURRENCY = 0;
+ private static final int DIM_RATE = 1;
+ private static final int DIM_RATE_START_DATE = 2;
+ private static final DataSchema DIM_SCHEMA =
+ new DataSchema(new String[]{"currency", "rate", "rate_start_date"}, new
ColumnDataType[]{
+ ColumnDataType.STRING, ColumnDataType.INT, ColumnDataType.LONG
+ });
+ private static final List<String> PRIMARY_KEY_COLUMNS = List.of("currency",
"rate_start_date");
+
+ /// Key positions follow the primary key, not the order of the join
condition.
+ private static final int KEY_CURRENCY = 0;
+ private static final int KEY_RATE_START_DATE = 1;
+
+ @Test
+ public void testKeyPositionsFollowPrimaryKeyOrderNotConditionOrder() {
+ // ON dim.rate_start_date = fact.rate_start_date AND dim.currency =
fact.currency
+ LookupJoinOperator.KeyPlan keyPlan = compileKeyPlan(
+ List.of(joinKey(FACT_RATE_START_DATE, DIM_RATE_START_DATE),
joinKey(FACT_CURRENCY, DIM_CURRENCY)), List.of());
+
+ assertEquals(keyPlan._sources[KEY_CURRENCY], FACT_CURRENCY);
+ assertEquals(keyPlan._sources[KEY_RATE_START_DATE], FACT_RATE_START_DATE);
+ assertFalse(keyPlan._neverMatches);
+ }
+
+ @Test
+ public void testConstantBindsPrimaryKeyColumn() {
+ // ON dim.currency = 'gbp' AND dim.rate_start_date = fact.rate_start_date
+ LookupJoinOperator.KeyPlan keyPlan =
+ compileKeyPlan(List.of(joinKey(FACT_RATE_START_DATE,
DIM_RATE_START_DATE)),
+ List.of(dimEqualsConstant(DIM_CURRENCY, ColumnDataType.STRING,
"gbp")));
+
+ assertEquals(keyPlan._sources[KEY_RATE_START_DATE], FACT_RATE_START_DATE);
+ assertEquals(keyPlan._constants[KEY_CURRENCY], "gbp");
+ assertFalse(keyPlan._neverMatches);
+ }
+
+ @Test
+ public void testConstantDoesNotReplaceJoinKey() {
+ // ON dim.currency = fact.currency AND dim.rate_start_date =
fact.rate_start_date AND dim.currency = 'gbp'
+ // The join key is not kept anywhere else, so replacing it would silently
widen the join. The constant stays a
+ // filter that runs after the lookup.
+ LookupJoinOperator.KeyPlan keyPlan = compileKeyPlan(
+ List.of(joinKey(FACT_CURRENCY, DIM_CURRENCY),
joinKey(FACT_RATE_START_DATE, DIM_RATE_START_DATE)),
+ List.of(dimEqualsConstant(DIM_CURRENCY, ColumnDataType.STRING,
"gbp")));
+
+ assertEquals(keyPlan._sources[KEY_CURRENCY], FACT_CURRENCY);
+ assertEquals(keyPlan._sources[KEY_RATE_START_DATE], FACT_RATE_START_DATE);
+ assertNull(keyPlan._constants[KEY_CURRENCY]);
+ }
+
+ @Test
+ public void testConstantOfTheColumnTypeIsAccepted() {
+ // ON dim.currency = fact.currency AND dim.rate_start_date = 1
+ // The planner coerces the operands of a comparison, so the constant
already carries the type of the column.
+ LookupJoinOperator.KeyPlan keyPlan =
compileKeyPlan(List.of(joinKey(FACT_CURRENCY, DIM_CURRENCY)),
+ List.of(dimEqualsConstant(DIM_RATE_START_DATE, ColumnDataType.LONG,
1L)));
+
+ assertEquals(keyPlan._constants[KEY_RATE_START_DATE], 1L);
+ }
+
+ @Test
+ public void testConstantOfAnotherTypeIsRejected() {
+ // PrimaryKey compares values with equals, where an Integer never equals a
Long, so a constant of the wrong type
+ // misses every row. The planner is expected to have coerced it already.
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(List.of(joinKey(FACT_CURRENCY, DIM_CURRENCY)),
+ List.of(dimEqualsConstant(DIM_RATE_START_DATE, ColumnDataType.INT,
1))));
+ assertTrue(exception.getMessage().contains("got a constant of stored type:
INT"), exception.getMessage());
+ }
+
+ @Test
+ public void testBigDecimalConstantIsAccepted() {
+ // BigDecimal#equals compares the scale, so 1.5 does not match a stored
1.50. A hash join carries the same hazard,
+ // so the lookup join accepts the constant rather than singling out one
arm of it.
+ LookupJoinOperator.KeyPlan keyPlan = compileKeyPlan(DECIMAL_DIM_SCHEMA,
List.of("amount"), List.of(),
+ List.of(dimEqualsConstant(0, ColumnDataType.BIG_DECIMAL, new
BigDecimal("1.5"))));
+
+ assertEquals(keyPlan._constants[0], new BigDecimal("1.5"));
+ }
+
+ @Test
+ public void testBytesConstantIsRejected() {
+ // A dimension table keys on a raw byte[], whose equals and hashCode are
identity, so no constant can match it.
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(BYTES_DIM_SCHEMA, List.of("id"), List.of(),
+ List.of(dimEqualsConstant(0, ColumnDataType.BYTES, new
ByteArray(new byte[]{1, 2})))));
+ assertTrue(exception.getMessage().contains("of type BYTES"),
exception.getMessage());
+ }
+
+ @Test
+ public void testNullConstantMakesEveryLookupMiss() {
+ // A null never matches a primary key value, so the operator must not run
the lookup at all.
+ LookupJoinOperator.KeyPlan keyPlan =
+ compileKeyPlan(List.of(joinKey(FACT_RATE_START_DATE,
DIM_RATE_START_DATE)),
+ List.of(dimEqualsConstant(DIM_CURRENCY, ColumnDataType.STRING,
null)));
+
+ assertTrue(keyPlan._neverMatches);
+ }
+
+ @Test
+ public void testSetPredicateDoesNotBindPrimaryKeyColumn() {
+ // dim.currency IN ('gbp', 'usd') reaches the operator as a disjunction. A
hash lookup cannot read a set of keys,
+ // so the primary key column stays open and the join is rejected.
+ RexExpression inList = new
RexExpression.FunctionCall(ColumnDataType.BOOLEAN, SqlKind.OR.name(),
+ List.of(dimEqualsConstant(DIM_CURRENCY, ColumnDataType.STRING, "gbp"),
+ dimEqualsConstant(DIM_CURRENCY, ColumnDataType.STRING, "usd")));
+
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(List.of(joinKey(FACT_RATE_START_DATE,
DIM_RATE_START_DATE)), List.of(inList)));
+ assertTrue(exception.getMessage().contains("cannot determine primary key
columns: [currency]"),
+ exception.getMessage());
+ }
+
+ @Test
+ public void testOpenPrimaryKeyColumnIsRejected() {
+ // ON dim.rate_start_date = fact.rate_start_date only. Nothing gives a
value for currency.
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(List.of(joinKey(FACT_RATE_START_DATE,
DIM_RATE_START_DATE)), List.of()));
+ assertTrue(exception.getMessage().contains("cannot determine primary key
columns: [currency]"),
+ exception.getMessage());
+ }
+
+ @Test
+ public void testJoinKeyOnNonPrimaryKeyColumnIsRejected() {
+ // The condition on "rate" is a join key, so it is not in the non-equi
conditions and no filter applies it.
+ // Dropping it would return rows that do not match the join condition.
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(List.of(joinKey(FACT_CURRENCY, DIM_CURRENCY),
+ joinKey(FACT_RATE_START_DATE, DIM_RATE_START_DATE),
joinKey(FACT_RATE_START_DATE, DIM_RATE)), List.of()));
+ assertTrue(exception.getMessage().contains("join key on column: rate,
which is not a primary key column"),
+ exception.getMessage());
+ }
+
+ @Test
+ public void testDuplicateJoinKeysOnSamePrimaryKeyColumnAreRejected() {
+ // Only one of the two conditions on currency can build the key, and the
other one has nowhere to run.
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(List.of(joinKey(FACT_CURRENCY, DIM_CURRENCY),
joinKey(FACT_RATE_START_DATE, DIM_CURRENCY),
+ joinKey(FACT_RATE_START_DATE, DIM_RATE_START_DATE)), List.of()));
+ assertTrue(exception.getMessage().contains("multiple join keys on primary
key column: currency"),
+ exception.getMessage());
+ }
+
+ @Test
+ public void testMissingPrimaryKeyColumnsAreRejected() {
+ IllegalStateException exception = expectThrows(IllegalStateException.class,
+ () -> compileKeyPlan(DIM_SCHEMA, List.of(),
List.of(joinKey(FACT_RATE_START_DATE, DIM_RATE_START_DATE)),
+ List.of()));
+ assertTrue(exception.getMessage().contains("Failed to find primary key
columns"), exception.getMessage());
+ }
+
+ private static final DataSchema DECIMAL_DIM_SCHEMA =
+ new DataSchema(new String[]{"amount"}, new
ColumnDataType[]{ColumnDataType.BIG_DECIMAL});
+ private static final DataSchema BYTES_DIM_SCHEMA =
+ new DataSchema(new String[]{"id"}, new
ColumnDataType[]{ColumnDataType.BYTES});
+
+ /// A join key, as the pair of column ids that Calcite splits a `fact_column
= dim_column` condition into.
+ private static Pair<Integer, Integer> joinKey(int factColumnId, int
dimColumnId) {
+ return Pair.of(factColumnId, dimColumnId);
+ }
+
+ private static LookupJoinOperator.KeyPlan compileKeyPlan(List<Pair<Integer,
Integer>> joinKeys,
+ List<RexExpression> nonEquiConditions) {
+ return compileKeyPlan(DIM_SCHEMA, PRIMARY_KEY_COLUMNS, joinKeys,
nonEquiConditions);
+ }
+
+ private static LookupJoinOperator.KeyPlan compileKeyPlan(DataSchema
dimSchema, List<String> primaryKeyColumns,
+ List<Pair<Integer, Integer>> joinKeys, List<RexExpression>
nonEquiConditions) {
+ List<Integer> leftKeys = new ArrayList<>(joinKeys.size());
+ List<Integer> rightKeys = new ArrayList<>(joinKeys.size());
+ for (Pair<Integer, Integer> joinKey : joinKeys) {
+ leftKeys.add(joinKey.getLeft());
+ rightKeys.add(joinKey.getRight());
+ }
+ JoinNode node =
+ new JoinNode(0, dimSchema, PlanNode.NodeHint.EMPTY, List.of(),
JoinRelType.INNER, leftKeys, rightKeys,
+ nonEquiConditions, JoinNode.JoinStrategy.LOOKUP);
+ return LookupJoinOperator.compileKeyPlan(node, TABLE_NAME,
primaryKeyColumns, dimSchema, LEFT_COLUMN_SIZE);
+ }
+
+ /// Builds a `dim_column = constant` condition. Non-equi conditions index
the joined row, so the dimension table
+ /// columns start at [#LEFT_COLUMN_SIZE].
+ private static RexExpression dimEqualsConstant(int dimColumnId,
ColumnDataType dataType, @Nullable Object value) {
+ return new RexExpression.FunctionCall(ColumnDataType.BOOLEAN,
SqlKind.EQUALS.name(),
+ List.of(new RexExpression.InputRef(LEFT_COLUMN_SIZE + dimColumnId),
+ new RexExpression.Literal(dataType, value)));
+ }
+}
diff --git
a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
index 51bc3e987eb..cfc7724ba9c 100644
---
a/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
+++
b/pinot-query-runtime/src/test/java/org/apache/pinot/query/runtime/queries/ResourceBasedQueriesTest.java
@@ -22,6 +22,7 @@ import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
+import com.google.common.collect.Maps;
import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
@@ -52,6 +53,7 @@ import
org.apache.pinot.query.runtime.plan.MultiStageQueryStats;
import org.apache.pinot.query.service.dispatch.QueryDispatcher;
import org.apache.pinot.query.testutils.MockInstanceDataManagerFactory;
import org.apache.pinot.query.testutils.QueryTestUtils;
+import org.apache.pinot.segment.local.segment.readers.PinotSegmentRecordReader;
import org.apache.pinot.segment.spi.ImmutableSegment;
import org.apache.pinot.spi.config.instance.InstanceType;
import org.apache.pinot.spi.config.table.TableType;
@@ -132,9 +134,9 @@ public class ResourceBasedQueriesTest extends
QueryRunnerTestBase {
List<QueryTestCase.ColumnAndType> columnAndTypes = table._schema;
List<GenericRow> genericRows = toRow(columnAndTypes, table._inputs);
if (table._replicated) {
- addSegmentReplicated(factory1, factory2, offlineTableName,
genericRows);
+ ImmutableSegment segment = addSegmentReplicated(factory1, factory2,
offlineTableName, genericRows);
if (table._isDimTable) {
- registerMockDimensionTable(offlineTableName, schema, table,
genericRows);
+ registerMockDimensionTable(offlineTableName, schema, table,
segment);
}
continue;
}
@@ -260,32 +262,52 @@ public class ResourceBasedQueriesTest extends
QueryRunnerTestBase {
}
}
- private void addSegmentReplicated(MockInstanceDataManagerFactory factory1,
MockInstanceDataManagerFactory factory2,
- String offlineTableName, List<GenericRow> rows) {
+ private ImmutableSegment addSegmentReplicated(MockInstanceDataManagerFactory
factory1,
+ MockInstanceDataManagerFactory factory2, String offlineTableName,
List<GenericRow> rows) {
ImmutableSegment segment = factory1.addSegment(offlineTableName, rows);
factory2.addSegment(offlineTableName, segment);
+ return segment;
}
/// Registers a mock DimensionTableDataManager for lookup join testing.
- /// The mock stores all rows in a HashMap keyed by primary key, supporting
lookupValues() and containsKey().
+ /// The mock stores all rows in a HashMap keyed by primary key, supporting
lookupValues(), containsKey() and
+ /// getPrimaryKeyColumns().
+ ///
+ /// The map is built from the segment with [PinotSegmentRecordReader], which
is how a real
+ /// [DimensionTableDataManager] builds its own. Reading the segment is what
keeps every value in the representation
+ /// that the column is stored in, for every data type, without this method
holding a copy of the conversion rules.
+ /// The rows of the test case cannot be used directly: they hold the values
that the JSON parser produced, where an
+ /// `Integer` stands where the query supplies a `Long`, and [PrimaryKey]
compares values with equals, so the lookup
+ /// would miss for reasons that have nothing to do with the code under test.
private void registerMockDimensionTable(String offlineTableName, Schema
schema, QueryTestCase.Table table,
- List<GenericRow> rows) {
+ ImmutableSegment segment) {
List<String> primaryKeyColumns = table._primaryKeyColumns;
if (primaryKeyColumns == null || primaryKeyColumns.isEmpty()) {
throw new IllegalStateException(
"isDimTable=true requires primaryKeyColumns to be set for table: " +
offlineTableName);
}
- // Build an in-memory lookup map: PrimaryKey -> GenericRow
- Map<PrimaryKey, GenericRow> lookupMap = new HashMap<>();
- for (GenericRow row : rows) {
- Object[] pkValues = new Object[primaryKeyColumns.size()];
- for (int i = 0; i < primaryKeyColumns.size(); i++) {
- pkValues[i] = row.getValue(primaryKeyColumns.get(i));
+ // Build an in-memory lookup map: PrimaryKey -> column name to stored value
+ List<String> columns = new ArrayList<>(schema.getColumnNames());
+ Map<PrimaryKey, Map<String, Object>> lookupMap = new HashMap<>();
+ try (PinotSegmentRecordReader recordReader = new
PinotSegmentRecordReader()) {
+ recordReader.init(segment);
+ int[] primaryKeyIndexes =
recordReader.getIndexesForColumns(primaryKeyColumns);
+ int[] columnIndexes = recordReader.getIndexesForColumns(columns);
+ int numDocs = segment.getSegmentMetadata().getTotalDocs();
+ for (int docId = 0; docId < numDocs; docId++) {
+ Object[] values = recordReader.getRecordValues(docId, columnIndexes);
+ Map<String, Object> row =
Maps.newHashMapWithExpectedSize(columns.size());
+ for (int i = 0; i < columns.size(); i++) {
+ row.put(columns.get(i), values[i]);
+ }
+ lookupMap.put(new PrimaryKey(recordReader.getRecordValues(docId,
primaryKeyIndexes)), row);
}
- lookupMap.put(new PrimaryKey(pkValues), row);
+ } catch (Exception e) {
+ throw new RuntimeException("Failed to build the mock dimension table
for: " + offlineTableName, e);
}
// Create and register a mock DimensionTableDataManager
DimensionTableDataManager mockDimManager =
Mockito.mock(DimensionTableDataManager.class);
+
Mockito.when(mockDimManager.getPrimaryKeyColumns()).thenReturn(primaryKeyColumns);
Mockito.when(mockDimManager.containsKey(ArgumentMatchers.any(PrimaryKey.class)))
.thenAnswer(invocation -> {
PrimaryKey pk = invocation.getArgument(0);
@@ -294,14 +316,14 @@ public class ResourceBasedQueriesTest extends
QueryRunnerTestBase {
Mockito.when(mockDimManager.lookupValues(ArgumentMatchers.any(PrimaryKey.class),
ArgumentMatchers.any(String[].class))).thenAnswer(invocation -> {
PrimaryKey pk = invocation.getArgument(0);
- String[] columns = invocation.getArgument(1);
- GenericRow row = lookupMap.get(pk);
+ String[] lookupColumns = invocation.getArgument(1);
+ Map<String, Object> row = lookupMap.get(pk);
if (row == null) {
return null;
}
- Object[] values = new Object[columns.length];
- for (int i = 0; i < columns.length; i++) {
- values[i] = row.getValue(columns[i]);
+ Object[] values = new Object[lookupColumns.length];
+ for (int i = 0; i < lookupColumns.length; i++) {
+ values[i] = row.get(lookupColumns[i]);
}
return values;
});
diff --git a/pinot-query-runtime/src/test/resources/queries/LookupJoin.json
b/pinot-query-runtime/src/test/resources/queries/LookupJoin.json
index 73b0f03f52e..93b0468d7d2 100644
--- a/pinot-query-runtime/src/test/resources/queries/LookupJoin.json
+++ b/pinot-query-runtime/src/test/resources/queries/LookupJoin.json
@@ -75,5 +75,172 @@
"ignoreLiteMode": true
}
]
+ },
+ "lookup_join_literal_key": {
+ "comment": "Regression test for issue 19188. One dimension primary-key
component comes from a literal in the join condition. Calcite classifies that
condition as a non-equi condition, so it is absent from the join keys. Before
the fix the lookup key held one value against a two-column primary key and the
join returned 0 rows.",
+ "tables": {
+ "fact_tbl": {
+ "schema": [
+ {"name": "rate_start_date", "type": "LONG"}
+ ],
+ "inputs": [
+ [1]
+ ]
+ },
+ "dim_tbl": {
+ "schema": [
+ {"name": "currency", "type": "STRING"},
+ {"name": "rate_start_date", "type": "LONG"},
+ {"name": "rate", "type": "INT"}
+ ],
+ "inputs": [
+ ["gbp", 1, 125],
+ ["usd", 1, 100]
+ ],
+ "replicated": true,
+ "isDimTable": true,
+ "primaryKeyColumns": ["currency", "rate_start_date"]
+ }
+ },
+ "queries": [
+ {
+ "description": "Lookup join with a literal dimension primary-key
component",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{dim_tbl}.currency, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency = 'gbp' AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date",
+ "outputs": [
+ ["gbp", 125]
+ ],
+ "ignoreLiteMode": true
+ }
+ ]
+ },
+ "lookup_join_composite_key": {
+ "comment": "Covers lookup joins against a two-column dimension primary
key. The lookup key must hold one value per primary key column, in the order
the dimension table schema declares them. These cases check that the key is
complete, that it is ordered by the primary key instead of by the join
condition, and that a join condition which cannot produce a complete key gives
an error instead of 0 rows.",
+ "tables": {
+ "fact_tbl": {
+ "schema": [
+ {"name": "currency", "type": "STRING"},
+ {"name": "rate_start_date", "type": "LONG"},
+ {"name": "amount", "type": "INT"}
+ ],
+ "inputs": [
+ ["gbp", 1, 10],
+ ["usd", 1, 20],
+ ["gbp", 2, 30],
+ ["eur", 1, 40],
+ ["eur", 9, 50]
+ ]
+ },
+ "dim_tbl": {
+ "schema": [
+ {"name": "currency", "type": "STRING"},
+ {"name": "rate_start_date", "type": "LONG"},
+ {"name": "rate", "type": "INT"}
+ ],
+ "inputs": [
+ ["gbp", 1, 125],
+ ["usd", 1, 100],
+ ["gbp", 2, 130]
+ ],
+ "replicated": true,
+ "isDimTable": true,
+ "primaryKeyColumns": ["currency", "rate_start_date"]
+ }
+ },
+ "queries": [
+ {
+ "description": "Literal primary-key component in the ON clause",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.currency, {dim_tbl}.rate FROM {fact_tbl} JOIN
{dim_tbl} ON {dim_tbl}.currency = 'gbp' AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date",
+ "outputs": [
+ [10, "gbp", 125],
+ [20, "gbp", 125],
+ [30, "gbp", 130],
+ [40, "gbp", 125]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "Same literal primary-key component, written in the
WHERE clause. The planner moves it into the join condition, so the operator
sees the same plan.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.currency, {dim_tbl}.rate FROM {fact_tbl} JOIN
{dim_tbl} ON {dim_tbl}.rate_start_date = {fact_tbl}.rate_start_date WHERE
{dim_tbl}.currency = 'gbp'",
+ "outputs": [
+ [10, "gbp", 125],
+ [20, "gbp", 125],
+ [30, "gbp", 130],
+ [40, "gbp", 125]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "Left join with a literal primary-key component. Before
the fix every row was null-padded, which gave wrong values instead of missing
rows.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} LEFT JOIN {dim_tbl} ON
{dim_tbl}.currency = 'gbp' AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date",
+ "outputs": [
+ [10, 125],
+ [20, 125],
+ [30, 130],
+ [40, 125],
+ [50, null]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "Both primary-key columns equi-joined, conditions
written in primary key order",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency = {fact_tbl}.currency AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date",
+ "outputs": [
+ [10, 125],
+ [20, 100],
+ [30, 130]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "Both primary-key columns equi-joined, conditions
written in reverse primary key order. Before the fix the key values were
swapped and the join returned 0 rows.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.rate_start_date = {fact_tbl}.rate_start_date AND {dim_tbl}.currency =
{fact_tbl}.currency",
+ "outputs": [
+ [10, 125],
+ [20, 100],
+ [30, 130]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "A literal on a primary-key column that an equi-join
key already binds. The equi-join key builds the key and the literal runs as a
filter after the lookup. If the literal replaced the equi-join key, the usd
fact row would read the gbp dimension row and add a wrong row.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency = {fact_tbl}.currency AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date AND {dim_tbl}.currency = 'gbp'",
+ "outputs": [
+ [10, 125],
+ [30, 130]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "A constant binds the LONG primary-key column. The
planner coerces the operands of the comparison, so the constant arrives with
the type of the column, and the operator checks that before it builds the key.
A constant of another type would miss every row.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency = {fact_tbl}.currency AND {dim_tbl}.rate_start_date = 1",
+ "outputs": [
+ [10, 125],
+ [20, 100],
+ [30, 125]
+ ],
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "The join condition leaves a primary-key column open.
Before the fix the join returned 0 rows.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.rate_start_date = {fact_tbl}.rate_start_date",
+ "outputs": [],
+ "expectedException": ".*cannot determine primary key columns:
\\[currency\\].*",
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "A set predicate cannot give a single key value, so the
primary-key column stays open",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency IN ('gbp', 'usd') AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date",
+ "outputs": [],
+ "expectedException": ".*cannot determine primary key columns:
\\[currency\\].*",
+ "ignoreLiteMode": true
+ },
+ {
+ "description": "A join key on a dimension column that is not part of
the primary key. Before the fix this condition was dropped and the join
returned rows that do not match it.",
+ "sql": "SELECT /*+ joinOptions(join_strategy='lookup') */
{fact_tbl}.amount, {dim_tbl}.rate FROM {fact_tbl} JOIN {dim_tbl} ON
{dim_tbl}.currency = {fact_tbl}.currency AND {dim_tbl}.rate_start_date =
{fact_tbl}.rate_start_date AND {dim_tbl}.rate = {fact_tbl}.amount",
+ "outputs": [],
+ "expectedException": ".*join key on column: rate, which is not a
primary key column.*",
+ "ignoreLiteMode": true
+ }
+ ]
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
