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 08f4ab17e67 Make IDSET and IN_ID_SET work in the multi-stage engine
(#19757)
08f4ab17e67 is described below
commit 08f4ab17e67d6382c848221864739799f7ad55f3
Author: Yash Mayya <[email protected]>
AuthorDate: Tue Oct 6 21:14:51 2026 -0700
Make IDSET and IN_ID_SET work in the multi-stage engine (#19757)
---
.../common/function/TransformFunctionType.java | 5 +-
.../pinot/core/function/scalar/IdSetFunctions.java | 169 ++++++++++
.../function/InIdSetTransformFunction.java | 14 +-
.../core/query/utils/idset/BloomFilterIdSet.java | 29 +-
.../pinot/core/query/utils/idset/IdSets.java | 53 ++-
.../utils/idset/Roaring64NavigableMapIdSet.java | 13 +-
.../pinot/core/function/FunctionRegistryTest.java | 4 +-
.../core/function/scalar/IdSetFunctionsTest.java | 219 +++++++++++++
.../pinot/core/query/utils/idset/IdSetTest.java | 76 +++++
.../org/apache/pinot/queries/IdSetQueriesTest.java | 157 ++++++++-
.../tests/custom/IdSetIntegrationTest.java | 219 +++++++++++++
.../resources/udf-test-results/all-functions.yaml | 2 +-
.../src/test/resources/queries/JoinPlans.json | 14 +
.../resources/queries/PhysicalOptimizerPlans.json | 15 +
.../src/test/resources/queries/IdSet.json | 360 +++++++++++++++++++++
15 files changed, 1318 insertions(+), 31 deletions(-)
diff --git
a/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java
b/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java
index 23c972d2684..793249106f4 100644
---
a/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java
+++
b/pinot-common/src/main/java/org/apache/pinot/common/function/TransformFunctionType.java
@@ -176,7 +176,10 @@ public enum TransformFunctionType {
MAP_VALUE("mapValue",
ReturnTypes.cascade(opBinding -> positionalComponentType(opBinding, 2),
SqlTypeTransforms.FORCE_NULLABLE),
OperandTypes.family(List.of(SqlTypeFamily.ARRAY, SqlTypeFamily.ANY,
SqlTypeFamily.ARRAY))),
- IN_ID_SET("inIdSet", ReturnTypes.BOOLEAN,
OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.CHARACTER)),
+ // Nullable: both the transform and the scalar function return NULL for a
NULL value, unless the IdSet is empty. The
+ // multi-stage planner also uses this operator for the inIdSet scalar
function. This operator is deterministic and
+ // the scalar function's is not, so this one lets the planner push IN_ID_SET
below joins (see IdSetFunctions).
+ IN_ID_SET("inIdSet", ReturnTypes.BOOLEAN_NULLABLE,
OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.CHARACTER)),
LOOKUP("lookUp"),
GROOVY("groovy"),
SCALAR("scalar"),
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/function/scalar/IdSetFunctions.java
b/pinot-core/src/main/java/org/apache/pinot/core/function/scalar/IdSetFunctions.java
new file mode 100644
index 00000000000..e6df10a7977
--- /dev/null
+++
b/pinot-core/src/main/java/org/apache/pinot/core/function/scalar/IdSetFunctions.java
@@ -0,0 +1,169 @@
+/**
+ * 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.core.function.scalar;
+
+import com.google.common.annotations.VisibleForTesting;
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.sql.Timestamp;
+import java.util.UUID;
+import javax.annotation.Nullable;
+import org.apache.pinot.core.query.utils.idset.IdSet;
+import org.apache.pinot.core.query.utils.idset.IdSets;
+import org.apache.pinot.spi.annotations.ScalarFunction;
+import org.apache.pinot.spi.data.FieldSpec.DataType;
+import org.apache.pinot.spi.utils.ByteArray;
+import org.apache.pinot.spi.utils.UuidUtils;
+
+
+/// Scalar form of the `IN_ID_SET` transform function, for queries that cannot
run the transform function:
+/// - In the multi-stage engine, wherever the predicate is not pushed into a
leaf stage, e.g. a filter over a join,
+/// `HAVING` or a join condition. Leaf stages keep using the
`InIdSetTransformFunction`, because transform functions
+/// take precedence over scalar functions.
+/// - In the single-stage engine, after aggregation, e.g. in `HAVING`.
Aggregations such as `MAX` return DOUBLE there,
+/// so cast them to the type the IdSet was built from.
+///
+/// The method is not static, so each call site gets its own instance (see
`FunctionInvoker`), which caches the last
+/// deserialized IdSet:
+/// - A literal IdSet reaches every row as the same String instance, so it is
deserialized once per call site.
+/// - An IdSet read from rows (e.g. a joined `IDSET` subquery result) arrives
as a new but equal String instance with
+/// every data block, which costs one String comparison per block.
+/// - A call site whose IdSet changes from row to row (e.g. a correlated
`IDSET` subquery, with one IdSet per group)
+/// deserializes the IdSet on every change, so it only suits small IdSets.
+///
+/// Not thread-safe: an instance serves one call site, which one thread
evaluates at a time.
+public class IdSetFunctions {
+ private String _serializedIdSet;
+ private IdSet _idSet;
+ // The IdSet and the value class whose combination passed the last value
type validation
+ private IdSet _validatedIdSet;
+ private Class<?> _validatedValueClass;
+
+ /// Returns whether the IdSet contains the value. As with an `IN` subquery,
the result is `false` for an empty or
+ /// `null` IdSet, even when the value is `null`, and `null` for a `null`
value otherwise. `IDSET` returns a `null`
+ /// IdSet over zero rows when null handling is enabled.
+ ///
+ /// Values arrive as their external Java types. BOOLEAN, TIMESTAMP and UUID
values are looked up through their
+ /// stored INT, LONG and BYTES values, as the transform function does.
+ ///
+ /// `isDeterministic = false` keeps all-literal calls out of compile-time
evaluation, which passes literals as Java
+ /// types the lookup cannot map to a value type (e.g. BigDecimal for an INT
literal). The runtime evaluates them
+ /// instead, as before this function existed. The flag also marks the
function VOLATILE, so ingestion transforms
+ /// cannot use it.
+ ///
+ /// The flag does not stop the multi-stage planner from pushing `IN_ID_SET`
below a join into a leaf stage, because
+ /// the planner uses the operator of the `IN_ID_SET` transform function,
which is deterministic. The operator table
+ /// registers transform functions before scalar functions, and skips this
function because its name is taken. If
+ /// the transform function's operator is removed, this function's volatile
operator takes its place, and
+ /// `PinotRuleUtils.isRelocatable` then keeps `IN_ID_SET` above joins. Tests
in `JoinPlans.json` and
+ /// `PhysicalOptimizerPlans.json` pin the push-down.
+ @Nullable
+ @ScalarFunction(nullableParameters = true, isDeterministic = false)
+ public Boolean inIdSet(@Nullable Object value, @Nullable String
serializedIdSet) {
+ if (serializedIdSet == null) {
+ return false;
+ }
+ IdSet idSet = getIdSet(serializedIdSet);
+ if (value == null) {
+ return idSet.getType() == IdSet.Type.EMPTY ? false : null;
+ }
+ if (idSet != _validatedIdSet || value.getClass() != _validatedValueClass) {
+ IdSets.validateValueType(idSet, getStoredType(value));
+ _validatedIdSet = idSet;
+ _validatedValueClass = value.getClass();
+ }
+ if (value instanceof Integer) {
+ return idSet.contains(((Integer) value).intValue());
+ }
+ if (value instanceof Long) {
+ return idSet.contains(((Long) value).longValue());
+ }
+ if (value instanceof Float) {
+ return idSet.contains(((Float) value).floatValue());
+ }
+ if (value instanceof Double) {
+ return idSet.contains(((Double) value).doubleValue());
+ }
+ if (value instanceof String) {
+ return idSet.contains((String) value);
+ }
+ if (value instanceof byte[]) {
+ return idSet.contains((byte[]) value);
+ }
+ if (value instanceof Boolean) {
+ return idSet.contains((Boolean) value ? 1 : 0);
+ }
+ if (value instanceof Timestamp) {
+ return idSet.contains(((Timestamp) value).getTime());
+ }
+ if (value instanceof UUID) {
+ return idSet.contains(UuidUtils.toBytes((UUID) value));
+ }
+ if (value instanceof ByteArray) {
+ return idSet.contains(((ByteArray) value).getBytes());
+ }
+ throw new IllegalStateException("Unsupported value class: " +
value.getClass().getName());
+ }
+
+ @VisibleForTesting
+ IdSet getIdSet(String serializedIdSet) {
+ if (serializedIdSet != _serializedIdSet) {
+ if (!serializedIdSet.equals(_serializedIdSet)) {
+ _idSet = deserialize(serializedIdSet);
+ }
+ // Keep the latest instance: the following rows of a data block share
it, so they match by reference
+ _serializedIdSet = serializedIdSet;
+ }
+ return _idSet;
+ }
+
+ private static IdSet deserialize(String serializedIdSet) {
+ try {
+ return IdSets.fromBase64String(serializedIdSet);
+ } catch (IOException | RuntimeException e) {
+ throw new IllegalArgumentException("Caught exception while deserializing
IdSet: " + e, e);
+ }
+ }
+
+ private static DataType getStoredType(Object value) {
+ if (value instanceof Integer || value instanceof Boolean) {
+ return DataType.INT;
+ }
+ if (value instanceof Long || value instanceof Timestamp) {
+ return DataType.LONG;
+ }
+ if (value instanceof Float) {
+ return DataType.FLOAT;
+ }
+ if (value instanceof Double) {
+ return DataType.DOUBLE;
+ }
+ if (value instanceof String) {
+ return DataType.STRING;
+ }
+ if (value instanceof byte[] || value instanceof UUID || value instanceof
ByteArray) {
+ return DataType.BYTES;
+ }
+ if (value instanceof BigDecimal) {
+ return DataType.BIG_DECIMAL;
+ }
+ throw new IllegalArgumentException(
+ "Cannot look up values of class " + value.getClass().getName() + " in
an IdSet");
+ }
+}
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/operator/transform/function/InIdSetTransformFunction.java
b/pinot-core/src/main/java/org/apache/pinot/core/operator/transform/function/InIdSetTransformFunction.java
index a305ebecb39..ac7594f399a 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/operator/transform/function/InIdSetTransformFunction.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/operator/transform/function/InIdSetTransformFunction.java
@@ -22,6 +22,7 @@ import com.google.common.base.Preconditions;
import java.io.IOException;
import java.util.List;
import java.util.Map;
+import javax.annotation.Nullable;
import org.apache.pinot.common.function.TransformFunctionType;
import org.apache.pinot.core.operator.ColumnContext;
import org.apache.pinot.core.operator.blocks.ValueBlock;
@@ -29,6 +30,7 @@ import
org.apache.pinot.core.operator.transform.TransformResultMetadata;
import org.apache.pinot.core.query.utils.idset.IdSet;
import org.apache.pinot.core.query.utils.idset.IdSets;
import org.apache.pinot.spi.data.FieldSpec.DataType;
+import org.roaringbitmap.RoaringBitmap;
/// The IN_ID_SET transform function takes 2 arguments:
@@ -61,9 +63,10 @@ public class InIdSetTransformFunction extends
BaseTransformFunction {
_transformFunction = arguments.get(0);
try {
_idSet = IdSets.fromBase64String(((LiteralTransformFunction)
arguments.get(1)).getStringLiteral());
- } catch (IOException e) {
- throw new IllegalArgumentException("Caught exception while deserializing
IdSet", e);
+ } catch (IOException | RuntimeException e) {
+ throw new IllegalArgumentException("Caught exception while deserializing
IdSet: " + e, e);
}
+ IdSets.validateValueType(_idSet,
_transformFunction.getResultMetadata().getDataType().getStoredType());
}
@Override
@@ -71,6 +74,13 @@ public class InIdSetTransformFunction extends
BaseTransformFunction {
return BOOLEAN_SV_NO_DICTIONARY_METADATA;
}
+ /// As with an `IN` subquery, a NULL value is in no empty IdSet, so the
result is never NULL for an empty IdSet.
+ @Nullable
+ @Override
+ public RoaringBitmap getNullBitmap(ValueBlock valueBlock) {
+ return _idSet.getType() == IdSet.Type.EMPTY ? null :
super.getNullBitmap(valueBlock);
+ }
+
@Override
public int[] transformToIntValuesSV(ValueBlock valueBlock) {
int length = valueBlock.getNumDocs();
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/BloomFilterIdSet.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/BloomFilterIdSet.java
index 3965c84be1b..a0684852851 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/BloomFilterIdSet.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/BloomFilterIdSet.java
@@ -18,14 +18,14 @@
*/
package org.apache.pinot.core.query.utils.idset;
-import com.google.common.base.Preconditions;
import com.google.common.hash.BloomFilter;
import com.google.common.hash.Funnel;
import com.google.common.hash.Funnels;
-import java.io.ByteArrayInputStream;
+import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.ByteBuffer;
+import java.util.List;
import org.apache.pinot.spi.data.FieldSpec.DataType;
@@ -36,12 +36,16 @@ public class BloomFilterIdSet implements IdSet {
private enum FunnelType {
// DO NOT change the ids as the ser/de relies on them
- INT((byte) 0), LONG((byte) 1), STRING((byte) 2), BYTES((byte) 3);
+ INT((byte) 0, DataType.INT, DataType.FLOAT), LONG((byte) 1, DataType.LONG,
DataType.DOUBLE),
+ STRING((byte) 2, DataType.STRING), BYTES((byte) 3, DataType.BYTES);
private final byte _id;
+ // Stored types whose values hash through this funnel: FLOAT and DOUBLE
values hash as their raw bits
+ private final List<DataType> _valueTypes;
- FunnelType(byte id) {
+ FunnelType(byte id, DataType... valueTypes) {
_id = id;
+ _valueTypes = List.of(valueTypes);
}
public byte getId() {
@@ -93,6 +97,12 @@ public class BloomFilterIdSet implements IdSet {
return _bloomFilter;
}
+ /// Returns the stored types whose values can be looked up in this IdSet.
The serialized form records only the
+ /// funnel, so an IdSet built from INT values also accepts FLOAT values (and
LONG also accepts DOUBLE).
+ List<DataType> getValueTypes() {
+ return _funnelType._valueTypes;
+ }
+
@Override
public Type getType() {
return Type.BLOOM_FILTER;
@@ -174,13 +184,11 @@ public class BloomFilterIdSet implements IdSet {
return byteArrayOutputStream.toByteArray();
}
- /// Deserializes the BloomFilterIdSet from a ByteBuffer.
+ /// Deserializes the BloomFilterIdSet from a ByteBuffer, which may be
read-only or direct.
///
/// NOTE: The ByteBuffer does not include the IdSet.Type byte.
static BloomFilterIdSet fromByteBuffer(ByteBuffer byteBuffer)
throws IOException {
- Preconditions.checkArgument(byteBuffer.hasArray(),
- "Cannot deserialize BloomFilter from ByteBuffer not backed by an
accessible byte array");
// Count the IdSet.Type byte
int serializedSizeInBytes = 1 + byteBuffer.remaining();
byte funnelTypeId = byteBuffer.get();
@@ -204,12 +212,11 @@ public class BloomFilterIdSet implements IdSet {
funnel = Funnels.byteArrayFunnel();
break;
default:
- throw new IllegalStateException();
+ throw new IllegalStateException("Unsupported BloomFilter funnel type
id: " + funnelTypeId);
}
// NOTE: No need to close the stream.
- BloomFilter bloomFilter = BloomFilter.readFrom(
- new ByteArrayInputStream(byteBuffer.array(), byteBuffer.arrayOffset()
+ byteBuffer.position(),
- byteBuffer.remaining()), funnel);
+ // Guava reads the filter 8 bytes at a time, which the Pinot buffer stream
copies byte by byte, so buffer the reads.
+ BloomFilter bloomFilter = BloomFilter.readFrom(new
BufferedInputStream(IdSets.toInputStream(byteBuffer)), funnel);
return new BloomFilterIdSet(funnelType, bloomFilter,
serializedSizeInBytes);
}
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/IdSets.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/IdSets.java
index 721c4787473..4b440c0ee76 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/IdSets.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/IdSets.java
@@ -21,6 +21,11 @@ package org.apache.pinot.core.query.utils.idset;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.util.Base64;
+import java.util.List;
+import java.util.stream.Collectors;
+import org.apache.pinot.segment.spi.memory.DataBufferPinotInputStream;
+import org.apache.pinot.segment.spi.memory.PinotByteBuffer;
+import org.apache.pinot.segment.spi.memory.PinotInputStream;
import org.apache.pinot.spi.data.FieldSpec.DataType;
import org.roaringbitmap.PeekableIntIterator;
import org.roaringbitmap.longlong.LongIterator;
@@ -38,6 +43,10 @@ public class IdSets {
public static final int DEFAULT_EXPECTED_INSERTIONS = 5_000_000;
public static final double DEFAULT_FPP = 0.03;
+ // Stored types whose values can be looked up in an IdSet
+ private static final List<DataType> LOOKUP_VALUE_TYPES =
+ List.of(DataType.INT, DataType.LONG, DataType.FLOAT, DataType.DOUBLE,
DataType.STRING, DataType.BYTES);
+
/// Creates an IdSet.
///
/// @param dataType Data type of the ids
@@ -78,6 +87,40 @@ public class IdSets {
return EmptyIdSet.INSTANCE;
}
+ /// Validates that values of the given stored type can be looked up in the
given IdSet. Rejects e.g. LONG values
+ /// in an IdSet built from INT values, where every lookup would otherwise
throw or hash to the wrong bits. An empty
+ /// IdSet accepts every lookup type, as it records no value type.
+ ///
+ /// @throws IllegalArgumentException if values of the stored type cannot be
looked up in the IdSet
+ public static void validateValueType(IdSet idSet, DataType storedType) {
+ if (!LOOKUP_VALUE_TYPES.contains(storedType)) {
+ throw new IllegalArgumentException(
+ String.format("Cannot look up %s values in an IdSet, supported value
types: %s", storedType,
+ LOOKUP_VALUE_TYPES));
+ }
+ List<DataType> valueTypes;
+ switch (idSet.getType()) {
+ case EMPTY:
+ return;
+ case ROARING_BITMAP:
+ valueTypes = List.of(DataType.INT);
+ break;
+ case ROARING_64_NAVIGABLE_MAP:
+ valueTypes = List.of(DataType.LONG);
+ break;
+ case BLOOM_FILTER:
+ valueTypes = ((BloomFilterIdSet) idSet).getValueTypes();
+ break;
+ default:
+ throw new IllegalStateException("Unsupported IdSet type: " +
idSet.getType());
+ }
+ if (!valueTypes.contains(storedType)) {
+ throw new IllegalArgumentException(
+ String.format("Cannot look up %s values in an IdSet built from %s
values", storedType,
+
valueTypes.stream().map(DataType::name).collect(Collectors.joining(" or "))));
+ }
+ }
+
/// Merges 2 IdSets, converts to BloomFilterIdSet if the size exceeds the
size threshold.
///
/// @param idSet1 First IdSet to merge
@@ -187,7 +230,7 @@ public class IdSets {
return fromByteBuffer(ByteBuffer.wrap(bytes));
}
- /// Deserializes the IdSet from a ByteBuffer.
+ /// Deserializes the IdSet from a ByteBuffer, which may be read-only or
direct (e.g. a multi-stage mailbox payload).
public static IdSet fromByteBuffer(ByteBuffer byteBuffer)
throws IOException {
byte typeId = byteBuffer.get();
@@ -201,7 +244,7 @@ public class IdSets {
case 3:
return BloomFilterIdSet.fromByteBuffer(byteBuffer);
default:
- throw new IllegalStateException();
+ throw new IllegalStateException("Unsupported IdSet type id: " +
typeId);
}
}
@@ -212,4 +255,10 @@ public class IdSets {
throws IOException {
return fromBytes(Base64.getDecoder().decode(base64String));
}
+
+ /// Returns a [PinotInputStream] over the remaining bytes of the given
buffer, without moving its position. It reads
+ /// heap, direct and read-only buffers the same way, and its `DataInput`
methods read big-endian values.
+ static PinotInputStream toInputStream(ByteBuffer byteBuffer) {
+ return new DataBufferPinotInputStream(PinotByteBuffer.slice(byteBuffer));
+ }
}
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/Roaring64NavigableMapIdSet.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/Roaring64NavigableMapIdSet.java
index fc913afae3a..9b0522bae35 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/Roaring64NavigableMapIdSet.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/utils/idset/Roaring64NavigableMapIdSet.java
@@ -18,10 +18,7 @@
*/
package org.apache.pinot.core.query.utils.idset;
-import com.google.common.base.Preconditions;
-import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
-import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.nio.ByteBuffer;
@@ -77,18 +74,14 @@ public class Roaring64NavigableMapIdSet implements IdSet {
return byteArrayOutputStream.toByteArray();
}
- /// Deserializes the Roaring64NavigableMapIdSet from a ByteBuffer.
+ /// Deserializes the Roaring64NavigableMapIdSet from a ByteBuffer, which may
be read-only or direct.
///
/// NOTE: The ByteBuffer does not include the IdSet.Type byte.
static Roaring64NavigableMapIdSet fromByteBuffer(ByteBuffer byteBuffer)
throws IOException {
- Preconditions.checkArgument(byteBuffer.hasArray(),
- "Cannot deserialize Roaring64NavigableMap from ByteBuffer not backed
by an accessible byte array");
Roaring64NavigableMap roaring64NavigableMap = new Roaring64NavigableMap();
- // NOTE: No need to close these streams.
- roaring64NavigableMap.deserialize(new DataInputStream(
- new ByteArrayInputStream(byteBuffer.array(), byteBuffer.arrayOffset()
+ byteBuffer.position(),
- byteBuffer.remaining())));
+ // NOTE: No need to close the stream.
+ roaring64NavigableMap.deserialize(IdSets.toInputStream(byteBuffer));
return new Roaring64NavigableMapIdSet(roaring64NavigableMap);
}
diff --git
a/pinot-core/src/test/java/org/apache/pinot/core/function/FunctionRegistryTest.java
b/pinot-core/src/test/java/org/apache/pinot/core/function/FunctionRegistryTest.java
index d1a0c076d46..0382d5bca0e 100644
---
a/pinot-core/src/test/java/org/apache/pinot/core/function/FunctionRegistryTest.java
+++
b/pinot-core/src/test/java/org/apache/pinot/core/function/FunctionRegistryTest.java
@@ -51,8 +51,8 @@ public class FunctionRegistryTest {
TransformFunctionType.JSON_EXTRACT_KEY,
TransformFunctionType.TIME_CONVERT,
TransformFunctionType.DATE_TIME_CONVERT_WINDOW_HOP,
TransformFunctionType.ARRAY_LENGTH,
TransformFunctionType.ARRAY_AVERAGE, TransformFunctionType.ARRAY_MIN,
TransformFunctionType.ARRAY_MAX,
- TransformFunctionType.ARRAY_SUM, TransformFunctionType.VALUE_IN,
TransformFunctionType.IN_ID_SET,
- TransformFunctionType.GROOVY, TransformFunctionType.CLP_DECODE,
TransformFunctionType.CLP_ENCODED_VARS_MATCH,
+ TransformFunctionType.ARRAY_SUM, TransformFunctionType.VALUE_IN,
TransformFunctionType.GROOVY,
+ TransformFunctionType.CLP_DECODE,
TransformFunctionType.CLP_ENCODED_VARS_MATCH,
TransformFunctionType.ST_POLYGON, TransformFunctionType.ST_AREA,
TransformFunctionType.ITEM,
TransformFunctionType.TIME_SERIES_BUCKET);
private static final EnumSet<FilterKind> IGNORED_FILTER_KINDS = EnumSet.of(
diff --git
a/pinot-core/src/test/java/org/apache/pinot/core/function/scalar/IdSetFunctionsTest.java
b/pinot-core/src/test/java/org/apache/pinot/core/function/scalar/IdSetFunctionsTest.java
new file mode 100644
index 00000000000..1cbfb1ec635
--- /dev/null
+++
b/pinot-core/src/test/java/org/apache/pinot/core/function/scalar/IdSetFunctionsTest.java
@@ -0,0 +1,219 @@
+/**
+ * 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.core.function.scalar;
+
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.sql.Timestamp;
+import java.util.List;
+import java.util.UUID;
+import org.apache.pinot.common.function.FunctionInfo;
+import org.apache.pinot.common.function.FunctionInvoker;
+import org.apache.pinot.common.function.FunctionRegistry;
+import org.apache.pinot.core.query.utils.idset.IdSet;
+import org.apache.pinot.core.query.utils.idset.IdSets;
+import org.apache.pinot.spi.data.FieldSpec.DataType;
+import org.apache.pinot.spi.utils.ByteArray;
+import org.apache.pinot.spi.utils.UuidUtils;
+import org.testng.annotations.Test;
+
+import static org.testng.Assert.assertEquals;
+import static org.testng.Assert.assertFalse;
+import static org.testng.Assert.assertNotNull;
+import static org.testng.Assert.assertNotSame;
+import static org.testng.Assert.assertNull;
+import static org.testng.Assert.assertSame;
+import static org.testng.Assert.assertTrue;
+import static org.testng.Assert.expectThrows;
+
+
+/// Tests the `inIdSet` scalar function on its own; `IdSet.json` in
pinot-query-runtime runs it in queries.
+public class IdSetFunctionsTest {
+
+ @Test
+ public void testLooksUpEveryValueType()
+ throws IOException {
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ intIdSet.add(1);
+ assertInIdSet(intIdSet, 1, 2);
+ // BOOLEAN values are looked up as their stored INT values
+ assertInIdSet(intIdSet, true, false);
+
+ IdSet longIdSet = IdSets.create(DataType.LONG);
+ longIdSet.add(10_000_000_000L);
+ assertInIdSet(longIdSet, 10_000_000_000L, 1L);
+ // TIMESTAMP values are looked up as their stored LONG values
+ assertInIdSet(longIdSet, new Timestamp(10_000_000_000L), new
Timestamp(1L));
+
+ IdSet floatIdSet = createBloomFilterIdSet(DataType.FLOAT);
+ floatIdSet.add(1.5f);
+ assertInIdSet(floatIdSet, 1.5f, 2.5f);
+
+ IdSet doubleIdSet = createBloomFilterIdSet(DataType.DOUBLE);
+ doubleIdSet.add(1.25);
+ assertInIdSet(doubleIdSet, 1.25, 2.25);
+
+ IdSet stringIdSet = createBloomFilterIdSet(DataType.STRING);
+ stringIdSet.add("foo");
+ assertInIdSet(stringIdSet, "foo", "bar");
+
+ IdSet bytesIdSet = createBloomFilterIdSet(DataType.BYTES);
+ bytesIdSet.add(new byte[]{1, 2});
+ assertInIdSet(bytesIdSet, new byte[]{1, 2}, new byte[]{3});
+ // Single-stage post-aggregation passes BYTES group keys as ByteArray
+ assertInIdSet(bytesIdSet, new ByteArray(new byte[]{1, 2}), new
ByteArray(new byte[]{3}));
+
+ // UUID values are looked up as their stored BYTES values
+ UUID uuid = UUID.fromString("123e4567-e89b-12d3-a456-426614174000");
+ IdSet uuidIdSet = createBloomFilterIdSet(DataType.BYTES);
+ uuidIdSet.add(UuidUtils.toBytes(uuid));
+ assertInIdSet(uuidIdSet, uuid,
UUID.fromString("123e4567-e89b-12d3-a456-426614174001"));
+ }
+
+ private static IdSet createBloomFilterIdSet(DataType dataType) {
+ // Sized so that a single id cannot produce a false positive
+ return IdSets.create(dataType, 0, 100, 0.0001);
+ }
+
+ private static void assertInIdSet(IdSet idSet, Object member, Object
nonMember)
+ throws IOException {
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ String serializedIdSet = idSet.toBase64String();
+ assertEquals(idSetFunctions.inIdSet(member, serializedIdSet),
Boolean.TRUE);
+ assertEquals(idSetFunctions.inIdSet(nonMember, serializedIdSet),
Boolean.FALSE);
+ }
+
+ @Test
+ public void testNullArguments()
+ throws IOException {
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ intIdSet.add(1);
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ // Whether NULL is in a non-empty IdSet is unknown, as for an IN subquery
+ assertNull(idSetFunctions.inIdSet(null, intIdSet.toBase64String()));
+ // A null IdSet, which IDSET returns over zero rows with null handling
enabled, holds no ids, so no value is in it,
+ // not even NULL
+ assertEquals(idSetFunctions.inIdSet(1, null), Boolean.FALSE);
+ assertEquals(idSetFunctions.inIdSet(null, null), Boolean.FALSE);
+ // The same holds for an empty IdSet, which IDSET returns over zero rows
with null handling disabled
+ assertEquals(idSetFunctions.inIdSet(null,
IdSets.emptyIdSet().toBase64String()), Boolean.FALSE);
+ }
+
+ @Test
+ public void testEmptyIdSetAcceptsEveryValueType()
+ throws IOException {
+ String serializedEmptyIdSet = IdSets.emptyIdSet().toBase64String();
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ for (Object value : List.of(1, 1L, 1.5f, 1.25, "foo", new byte[]{1}, true,
new Timestamp(1L))) {
+ assertEquals(idSetFunctions.inIdSet(value, serializedEmptyIdSet),
Boolean.FALSE);
+ }
+ }
+
+ @Test
+ public void testRejectsUnsupportedValueTypes()
+ throws IOException {
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ intIdSet.add(1);
+ String serializedIntIdSet = intIdSet.toBase64String();
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ IllegalArgumentException exception =
+ expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(1L, serializedIntIdSet));
+ assertEquals(exception.getMessage(), "Cannot look up LONG values in an
IdSet built from INT values");
+ exception = expectThrows(IllegalArgumentException.class,
+ () -> idSetFunctions.inIdSet(new BigDecimal("1"), serializedIntIdSet));
+ assertTrue(exception.getMessage().startsWith("Cannot look up BIG_DECIMAL
values in an IdSet"),
+ exception.getMessage());
+ exception =
+ expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(new int[]{1}, serializedIntIdSet));
+ assertTrue(exception.getMessage().startsWith("Cannot look up values of
class"), exception.getMessage());
+
+ // Each value class is validated against the IdSet it is looked up in,
also after another class passed
+ assertEquals(idSetFunctions.inIdSet(1, serializedIntIdSet), Boolean.TRUE);
+ expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(1L, serializedIntIdSet));
+ IdSet longIdSet = IdSets.create(DataType.LONG);
+ longIdSet.add(1L);
+ assertEquals(idSetFunctions.inIdSet(1L, longIdSet.toBase64String()),
Boolean.TRUE);
+ expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(1, longIdSet.toBase64String()));
+ }
+
+ @Test
+ public void testFollowsTheIdSetOfEachCall()
+ throws IOException {
+ IdSet idSet1 = IdSets.create(DataType.INT);
+ idSet1.add(1);
+ IdSet idSet2 = IdSets.create(DataType.INT);
+ idSet2.add(2);
+ String serializedIdSet1 = idSet1.toBase64String();
+ String serializedIdSet2 = idSet2.toBase64String();
+ // One instance serves one call site, whose IdSet can change from row to
row when it is read from a column
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ assertEquals(idSetFunctions.inIdSet(1, serializedIdSet1), Boolean.TRUE);
+ assertEquals(idSetFunctions.inIdSet(2, serializedIdSet1), Boolean.FALSE);
+ assertEquals(idSetFunctions.inIdSet(1, serializedIdSet2), Boolean.FALSE);
+ assertEquals(idSetFunctions.inIdSet(2, serializedIdSet2), Boolean.TRUE);
+ // An equal but distinct String instance holds the same IdSet
+ assertEquals(idSetFunctions.inIdSet(2, new String(serializedIdSet2)),
Boolean.TRUE);
+ assertEquals(idSetFunctions.inIdSet(1, new String(serializedIdSet1)),
Boolean.TRUE);
+ }
+
+ @Test
+ public void testReusesTheDeserializedIdSet()
+ throws IOException {
+ IdSet idSet1 = IdSets.create(DataType.INT);
+ idSet1.add(1);
+ String serializedIdSet1 = idSet1.toBase64String();
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ IdSet deserializedIdSet = idSetFunctions.getIdSet(serializedIdSet1);
+ assertSame(idSetFunctions.getIdSet(serializedIdSet1), deserializedIdSet);
+ // Rows of a new data block carry a new but equal String instance
+ assertSame(idSetFunctions.getIdSet(new String(serializedIdSet1)),
deserializedIdSet);
+ // A different IdSet is deserialized
+ IdSet idSet2 = IdSets.create(DataType.INT);
+ idSet2.add(2);
+ assertNotSame(idSetFunctions.getIdSet(idSet2.toBase64String()),
deserializedIdSet);
+ }
+
+ @Test
+ public void testRejectsMalformedIdSet() {
+ IdSetFunctions idSetFunctions = new IdSetFunctions();
+ // Not Base64
+ IllegalArgumentException exception =
+ expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(1, "not an IdSet"));
+ assertTrue(exception.getMessage().startsWith("Caught exception while
deserializing IdSet"), exception.getMessage());
+ // Valid Base64 for an unknown IdSet type id
+ exception = expectThrows(IllegalArgumentException.class, () ->
idSetFunctions.inIdSet(1, "CQ=="));
+ assertTrue(exception.getMessage().contains("Unsupported IdSet type id:
9"), exception.getMessage());
+ }
+
+ @Test
+ public void testRegisteredAsScalarFunction()
+ throws IOException {
+ FunctionInfo functionInfo = FunctionRegistry.lookupFunctionInfo("inidset",
2);
+ assertNotNull(functionInfo);
+ assertEquals(functionInfo.getMethod().getDeclaringClass(),
IdSetFunctions.class);
+ // Kept out of compile-time evaluation, which passes literals as types the
lookup cannot use
+ assertFalse(functionInfo.isDeterministic());
+ // The parameters are nullable, so the invoker passes null arguments
through instead of returning null
+ FunctionInvoker functionInvoker = new FunctionInvoker(functionInfo);
+ assertEquals(functionInvoker.invoke(new Object[]{1, null}), Boolean.FALSE);
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ intIdSet.add(1);
+ assertNull(functionInvoker.invoke(new Object[]{null,
intIdSet.toBase64String()}));
+ }
+}
diff --git
a/pinot-core/src/test/java/org/apache/pinot/core/query/utils/idset/IdSetTest.java
b/pinot-core/src/test/java/org/apache/pinot/core/query/utils/idset/IdSetTest.java
index b75c5c4b741..179a5310b23 100644
---
a/pinot-core/src/test/java/org/apache/pinot/core/query/utils/idset/IdSetTest.java
+++
b/pinot-core/src/test/java/org/apache/pinot/core/query/utils/idset/IdSetTest.java
@@ -21,6 +21,9 @@ package org.apache.pinot.core.query.utils.idset;
import it.unimi.dsi.fastutil.ints.IntOpenHashSet;
import it.unimi.dsi.fastutil.longs.LongOpenHashSet;
import java.io.IOException;
+import java.nio.ByteBuffer;
+import java.nio.charset.StandardCharsets;
+import java.util.List;
import java.util.Random;
import org.apache.pinot.spi.data.FieldSpec.DataType;
import org.testng.annotations.BeforeClass;
@@ -29,6 +32,7 @@ import org.testng.annotations.Test;
import static org.testng.Assert.assertEquals;
import static org.testng.Assert.assertSame;
import static org.testng.Assert.assertTrue;
+import static org.testng.Assert.expectThrows;
public class IdSetTest {
@@ -129,6 +133,78 @@ public class IdSetTest {
assertEquals(IdSets.fromBase64String(_longBloomFilterIdSet.toBase64String()),
_longBloomFilterIdSet);
}
+ @Test
+ public void testDeserializeFromReadOnlyAndDirectBuffers()
+ throws IOException {
+ IdSet stringBloomFilterIdSet = IdSets.create(DataType.STRING, 0,
NUM_VALUES, IdSets.DEFAULT_FPP);
+ IdSet bytesBloomFilterIdSet = IdSets.create(DataType.BYTES, 0, NUM_VALUES,
IdSets.DEFAULT_FPP);
+ for (int intId : _intSet) {
+ String stringId = Integer.toString(intId);
+ stringBloomFilterIdSet.add(stringId);
+ bytesBloomFilterIdSet.add(stringId.getBytes(StandardCharsets.UTF_8));
+ }
+ List<IdSet> idSets =
+ List.of(IdSets.emptyIdSet(), _intIdSet, _longIdSet,
_intBloomFilterIdSet, _longBloomFilterIdSet,
+ stringBloomFilterIdSet, bytesBloomFilterIdSet);
+ for (IdSet idSet : idSets) {
+ byte[] bytes = idSet.toBytes();
+ // The multi-stage engine reads intermediate results from read-only
buffers, which expose no backing array
+ ByteBuffer directBuffer = ByteBuffer.allocateDirect(bytes.length);
+ directBuffer.put(bytes).flip();
+ // Serialized bytes that start inside a larger array, as a positioned
buffer and as a slice
+ byte[] paddedBytes = new byte[bytes.length + 3];
+ System.arraycopy(bytes, 0, paddedBytes, 3, bytes.length);
+ List<ByteBuffer> byteBuffers =
+ List.of(ByteBuffer.wrap(bytes),
ByteBuffer.wrap(bytes).asReadOnlyBuffer(), directBuffer,
+ ByteBuffer.wrap(paddedBytes, 3, bytes.length),
ByteBuffer.wrap(paddedBytes, 3, bytes.length).slice(),
+ ByteBuffer.wrap(paddedBytes, 3,
bytes.length).asReadOnlyBuffer());
+ for (ByteBuffer byteBuffer : byteBuffers) {
+ assertEquals(IdSets.fromByteBuffer(byteBuffer), idSet, idSet.getType()
+ " from " + byteBuffer);
+ }
+ }
+ }
+
+ @Test
+ public void testValidateValueType()
+ throws IOException {
+ assertValueTypes(IdSets.emptyIdSet(), DataType.INT, DataType.LONG,
DataType.FLOAT, DataType.DOUBLE,
+ DataType.STRING, DataType.BYTES);
+ assertValueTypes(_intIdSet, DataType.INT);
+ assertValueTypes(_longIdSet, DataType.LONG);
+ // A Bloom filter funnel serves 2 stored types: FLOAT values hash as int
bits, DOUBLE values as long bits
+ assertValueTypes(_intBloomFilterIdSet, DataType.INT, DataType.FLOAT);
+ assertValueTypes(IdSets.create(DataType.FLOAT, 0, NUM_VALUES,
IdSets.DEFAULT_FPP), DataType.INT, DataType.FLOAT);
+ assertValueTypes(_longBloomFilterIdSet, DataType.LONG, DataType.DOUBLE);
+ assertValueTypes(IdSets.create(DataType.DOUBLE, 0, NUM_VALUES,
IdSets.DEFAULT_FPP), DataType.LONG,
+ DataType.DOUBLE);
+ assertValueTypes(IdSets.create(DataType.STRING, 0, NUM_VALUES,
IdSets.DEFAULT_FPP), DataType.STRING);
+ assertValueTypes(IdSets.create(DataType.BYTES, 0, NUM_VALUES,
IdSets.DEFAULT_FPP), DataType.BYTES);
+ // The funnel survives serialization
+ assertValueTypes(IdSets.fromBytes(_intBloomFilterIdSet.toBytes()),
DataType.INT, DataType.FLOAT);
+ assertValueTypes(IdSets.fromBytes(_longBloomFilterIdSet.toBytes()),
DataType.LONG, DataType.DOUBLE);
+
+ IllegalArgumentException exception =
expectThrows(IllegalArgumentException.class,
+ () -> IdSets.validateValueType(_intBloomFilterIdSet, DataType.STRING));
+ assertEquals(exception.getMessage(), "Cannot look up STRING values in an
IdSet built from INT or FLOAT values");
+ // Even an empty IdSet rejects value types that no IdSet can hold
+ exception = expectThrows(IllegalArgumentException.class,
+ () -> IdSets.validateValueType(IdSets.emptyIdSet(),
DataType.BIG_DECIMAL));
+ assertTrue(exception.getMessage().startsWith("Cannot look up BIG_DECIMAL
values in an IdSet"),
+ exception.getMessage());
+ }
+
+ private static void assertValueTypes(IdSet idSet, DataType... acceptedTypes)
{
+ List<DataType> acceptedTypeList = List.of(acceptedTypes);
+ for (DataType storedType : List.of(DataType.INT, DataType.LONG,
DataType.FLOAT, DataType.DOUBLE, DataType.STRING,
+ DataType.BYTES, DataType.BIG_DECIMAL)) {
+ if (acceptedTypeList.contains(storedType)) {
+ IdSets.validateValueType(idSet, storedType);
+ } else {
+ expectThrows(IllegalArgumentException.class, () ->
IdSets.validateValueType(idSet, storedType));
+ }
+ }
+ }
+
@Test
public void testMerge()
throws IOException {
diff --git
a/pinot-core/src/test/java/org/apache/pinot/queries/IdSetQueriesTest.java
b/pinot-core/src/test/java/org/apache/pinot/queries/IdSetQueriesTest.java
index 4fc6a1c92da..5fd0b32b373 100644
--- a/pinot-core/src/test/java/org/apache/pinot/queries/IdSetQueriesTest.java
+++ b/pinot-core/src/test/java/org/apache/pinot/queries/IdSetQueriesTest.java
@@ -50,6 +50,7 @@ import org.apache.pinot.spi.config.table.TableType;
import org.apache.pinot.spi.data.FieldSpec.DataType;
import org.apache.pinot.spi.data.Schema;
import org.apache.pinot.spi.data.readers.GenericRow;
+import org.apache.pinot.spi.exception.BadQueryRequestException;
import org.apache.pinot.spi.utils.ReadMode;
import org.apache.pinot.spi.utils.builder.TableConfigBuilder;
import org.testng.annotations.AfterClass;
@@ -60,6 +61,7 @@ import static org.testng.Assert.assertEquals;
import static org.testng.Assert.assertFalse;
import static org.testng.Assert.assertNotNull;
import static org.testng.Assert.assertTrue;
+import static org.testng.Assert.expectThrows;
/// Queries test for ID_SET queries.
@@ -83,17 +85,22 @@ public class IdSetQueriesTest extends BaseQueriesTest {
private static final String FLOAT_MV_COLUMN = "floatMVColumn";
private static final String DOUBLE_MV_COLUMN = "doubleMVColumn";
private static final String STRING_MV_COLUMN = "stringMVColumn";
+ // Holds the INT value, except for every 4th record, which holds NULL
+ private static final String NULLABLE_INT_COLUMN = "nullableIntColumn";
+ private static final int NUM_NULL_RECORDS = NUM_RECORDS / 4;
private static final Schema SCHEMA = new
Schema.SchemaBuilder().addSingleValueDimension(INT_COLUMN, DataType.INT)
.addSingleValueDimension(LONG_COLUMN,
DataType.LONG).addSingleValueDimension(FLOAT_COLUMN, DataType.FLOAT)
.addSingleValueDimension(DOUBLE_COLUMN,
DataType.DOUBLE).addSingleValueDimension(STRING_COLUMN, DataType.STRING)
.addSingleValueDimension(BYTES_COLUMN,
DataType.BYTES).addMultiValueDimension(INT_MV_COLUMN, DataType.INT)
.addMultiValueDimension(LONG_MV_COLUMN,
DataType.LONG).addMultiValueDimension(FLOAT_MV_COLUMN, DataType.FLOAT)
.addMultiValueDimension(DOUBLE_MV_COLUMN, DataType.DOUBLE)
- .addMultiValueDimension(STRING_MV_COLUMN, DataType.STRING).build();
+ .addMultiValueDimension(STRING_MV_COLUMN, DataType.STRING)
+ .addSingleValueDimension(NULLABLE_INT_COLUMN, DataType.INT).build();
private static final TableConfig TABLE_CONFIG =
- new
TableConfigBuilder(TableType.OFFLINE).setTableName(RAW_TABLE_NAME).build();
+ new
TableConfigBuilder(TableType.OFFLINE).setTableName(RAW_TABLE_NAME).setNullHandlingEnabled(true).build();
private final int[] _values = new int[NUM_RECORDS];
+ private final List<GenericRow> _records = new ArrayList<>(NUM_RECORDS);
private IndexSegment _indexSegment;
private List<IndexSegment> _indexSegments;
@@ -139,8 +146,10 @@ public class IdSetQueriesTest extends BaseQueriesTest {
record.putValue(FLOAT_MV_COLUMN, new Float[]{floatValue, floatValue +
MAX_VALUE});
record.putValue(DOUBLE_MV_COLUMN, new Double[]{doubleValue, doubleValue
+ MAX_VALUE});
record.putValue(STRING_MV_COLUMN, new String[]{stringValue, stringValue
+ MAX_VALUE});
+ record.putValue(NULLABLE_INT_COLUMN, i % 4 == 0 ? null : intValue);
records.add(record);
}
+ _records.addAll(records);
SegmentGeneratorConfig segmentGeneratorConfig = new
SegmentGeneratorConfig(TABLE_CONFIG, SCHEMA);
segmentGeneratorConfig.setTableName(RAW_TABLE_NAME);
@@ -563,6 +572,150 @@ public class IdSetQueriesTest extends BaseQueriesTest {
}
}
+ @Test
+ public void testInIdSetOnEveryValueType()
+ throws IOException {
+ testInIdSet(INT_COLUMN, IdSets.create(DataType.INT));
+ testInIdSet(LONG_COLUMN, IdSets.create(DataType.LONG));
+ // Bloom filters sized for the records, to keep the literals small
+ testInIdSet(FLOAT_COLUMN, createBloomFilterIdSet(DataType.FLOAT));
+ testInIdSet(DOUBLE_COLUMN, createBloomFilterIdSet(DataType.DOUBLE));
+ testInIdSet(STRING_COLUMN, createBloomFilterIdSet(DataType.STRING));
+ testInIdSet(BYTES_COLUMN, createBloomFilterIdSet(DataType.BYTES));
+ }
+
+ private static IdSet createBloomFilterIdSet(DataType dataType) {
+ return IdSets.create(dataType, IdSets.DEFAULT_SIZE_THRESHOLD_IN_BYTES,
NUM_RECORDS, IdSets.DEFAULT_FPP);
+ }
+
+ /// Fills the IdSet with the column values of the first half of the records,
then checks that IN_ID_SET matches
+ /// the records the IdSet contains, including any Bloom filter false
positives.
+ private void testInIdSet(String column, IdSet idSet)
+ throws IOException {
+ for (int i = 0; i < NUM_RECORDS / 2; i++) {
+ Object value = _records.get(i).getValue(column);
+ if (value instanceof Integer) {
+ idSet.add((int) value);
+ } else if (value instanceof Long) {
+ idSet.add((long) value);
+ } else if (value instanceof Float) {
+ idSet.add((float) value);
+ } else if (value instanceof Double) {
+ idSet.add((double) value);
+ } else if (value instanceof String) {
+ idSet.add((String) value);
+ } else {
+ idSet.add((byte[]) value);
+ }
+ }
+ long expectedNumMatchingRecords = 0;
+ for (GenericRow record : _records) {
+ Object value = record.getValue(column);
+ boolean contains;
+ if (value instanceof Integer) {
+ contains = idSet.contains((int) value);
+ } else if (value instanceof Long) {
+ contains = idSet.contains((long) value);
+ } else if (value instanceof Float) {
+ contains = idSet.contains((float) value);
+ } else if (value instanceof Double) {
+ contains = idSet.contains((double) value);
+ } else if (value instanceof String) {
+ contains = idSet.contains((String) value);
+ } else {
+ contains = idSet.contains((byte[]) value);
+ }
+ if (contains) {
+ expectedNumMatchingRecords++;
+ }
+ }
+ String query =
+ "SELECT COUNT(*) FROM testTable WHERE IN_ID_SET(" + column + ", '" +
idSet.toBase64String() + "') = 1";
+ AggregationOperator aggregationOperator = getOperator(query);
+ List<Object> aggregationResult =
aggregationOperator.nextBlock().getResults();
+ assertNotNull(aggregationResult);
+ assertEquals((long) aggregationResult.get(0), expectedNumMatchingRecords,
column);
+ }
+
+ @Test
+ public void testInIdSetInHaving()
+ throws IOException {
+ // IdSets of the int and long values of the first half of the records
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ IdSet longIdSet = IdSets.create(DataType.LONG);
+ for (int i = 0; i < NUM_RECORDS / 2; i++) {
+ intIdSet.add(_values[i]);
+ longIdSet.add(_values[i] + (long) Integer.MAX_VALUE);
+ }
+ long expectedNumGroups =
Arrays.stream(_values).distinct().filter(intIdSet::contains).count();
+
+ // HAVING on a group key
+ BrokerResponseNative brokerResponse = getBrokerResponse(
+ "SELECT intColumn, COUNT(*) FROM testTable GROUP BY intColumn HAVING
IN_ID_SET(intColumn, '"
+ + intIdSet.toBase64String() + "') = 1 LIMIT " + NUM_RECORDS);
+ assertTrue(brokerResponse.getExceptions().isEmpty(),
brokerResponse.getExceptions().toString());
+ assertEquals(brokerResponse.getResultTable().getRows().size(),
expectedNumGroups);
+
+ // HAVING on an aggregation, which returns DOUBLE here, so it is cast to
the type of the IdSet. Every group holds a
+ // single int value, so its maximum long value is in the IdSet when its
int value is.
+ brokerResponse = getBrokerResponse(
+ "SELECT stringColumn, MAX(longColumn) FROM testTable GROUP BY
stringColumn HAVING IN_ID_SET(CAST(MAX("
+ + "longColumn) AS LONG), '" + longIdSet.toBase64String() + "') = 1
LIMIT " + NUM_RECORDS);
+ assertTrue(brokerResponse.getExceptions().isEmpty(),
brokerResponse.getExceptions().toString());
+ assertEquals(brokerResponse.getResultTable().getRows().size(),
expectedNumGroups);
+
+ // Without the cast, this RoaringBitmap-backed LONG IdSet rejects the
DOUBLE values. A LONG IdSet large enough to be
+ // a Bloom filter would accept them and hash their raw bits, missing every
group, because LONG and DOUBLE values
+ // share a funnel.
+ brokerResponse = getBrokerResponse(
+ "SELECT stringColumn, MAX(longColumn) FROM testTable GROUP BY
stringColumn HAVING IN_ID_SET(MAX(longColumn), '"
+ + longIdSet.toBase64String() + "') = 1 LIMIT " + NUM_RECORDS);
+ assertTrue(brokerResponse.getExceptions().toString()
+ .contains("Cannot look up DOUBLE values in an IdSet built from
LONG values"),
+ brokerResponse.getExceptions().toString());
+ }
+
+ @Test
+ public void testInIdSetRejectsMismatchedValueType()
+ throws IOException {
+ IdSet idSet = IdSets.create(DataType.INT);
+ idSet.add(_values[0]);
+ String query = "SELECT COUNT(*) FROM testTable WHERE IN_ID_SET(longColumn,
'" + idSet.toBase64String() + "') = 1";
+ BadQueryRequestException exception =
expectThrows(BadQueryRequestException.class, () -> getOperator(query));
+ assertTrue(exception.getMessage().contains("Cannot look up LONG values in
an IdSet built from INT values"),
+ exception.getMessage());
+ }
+
+ /// As with an `IN` subquery, no value is in an empty IdSet, not even NULL.
With null handling enabled, `IN_ID_SET` is
+ /// FALSE for a NULL value when the IdSet is empty, and NULL when it is not.
+ @Test
+ public void testInIdSetNullHandling()
+ throws IOException {
+ IdSet idSet = IdSets.create(DataType.INT);
+ for (int value : _values) {
+ idSet.add(value);
+ }
+ // 2 instances with 2 segments each
+ int numRecords = 4 * NUM_RECORDS;
+ int numNullRecords = 4 * NUM_NULL_RECORDS;
+
+ String emptyIdSet = IdSets.emptyIdSet().toBase64String();
+ assertCount(NULLABLE_INT_COLUMN, emptyIdSet, 1, 0);
+ assertCount(NULLABLE_INT_COLUMN, emptyIdSet, 0, numRecords);
+ // Whether a NULL value is in a non-empty IdSet is unknown, so neither
filter keeps the NULL records
+ assertCount(NULLABLE_INT_COLUMN, idSet.toBase64String(), 1, numRecords -
numNullRecords);
+ assertCount(NULLABLE_INT_COLUMN, idSet.toBase64String(), 0, 0);
+ }
+
+ private void assertCount(String column, String serializedIdSet, int inIdSet,
long expectedCount) {
+ BrokerResponseNative brokerResponse = getBrokerResponse(
+ "SET enableNullHandling = true; SELECT COUNT(*) FROM testTable WHERE
IN_ID_SET(" + column + ", '"
+ + serializedIdSet + "') = " + inIdSet);
+ assertTrue(brokerResponse.getExceptions().isEmpty(),
brokerResponse.getExceptions().toString());
+ assertEquals(brokerResponse.getResultTable().getRows().get(0)[0],
expectedCount,
+ "IN_ID_SET = " + inIdSet + " with IdSet " + serializedIdSet);
+ }
+
@AfterClass
public void tearDown()
throws IOException {
diff --git
a/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/IdSetIntegrationTest.java
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/IdSetIntegrationTest.java
new file mode 100644
index 00000000000..1c4b2e43af3
--- /dev/null
+++
b/pinot-integration-tests/src/test/java/org/apache/pinot/integration/tests/custom/IdSetIntegrationTest.java
@@ -0,0 +1,219 @@
+/**
+ * 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.integration.tests.custom;
+
+import com.fasterxml.jackson.databind.JsonNode;
+import java.io.File;
+import java.nio.ByteBuffer;
+import java.nio.charset.StandardCharsets;
+import java.util.List;
+import org.apache.avro.file.DataFileWriter;
+import org.apache.avro.generic.GenericData;
+import org.apache.pinot.core.query.utils.idset.IdSet;
+import org.apache.pinot.core.query.utils.idset.IdSets;
+import org.apache.pinot.spi.data.FieldSpec.DataType;
+import org.apache.pinot.spi.data.Schema;
+import org.testng.annotations.Test;
+
+import static org.testng.Assert.assertEquals;
+import static org.testng.Assert.assertTrue;
+
+
+/// IDSET and IN_ID_SET in both query engines, on a cluster with 2 servers. A
set built by either engine must filter
+/// the same rows in the other one.
+@Test(suiteName = "CustomClusterIntegrationTest")
+public class IdSetIntegrationTest extends
CustomDataQueryClusterIntegrationTest {
+ private static final String TABLE_NAME = "IdSetIntegrationTest";
+ private static final String ID = "id";
+ private static final String INT_COL = "intCol";
+ private static final String LONG_COL = "longCol";
+ private static final String STR_COL = "strCol";
+ private static final String BYTES_COL = "bytesCol";
+ private static final int NUM_ROWS = 1000;
+ // Row i holds value i % NUM_VALUES, so each value appears in NUM_ROWS /
NUM_VALUES rows, and the sets built from the
+ // rows with id < NUM_SET_VALUES hold values 0 to NUM_SET_VALUES - 1
+ private static final int NUM_VALUES = 200;
+ private static final int NUM_SET_VALUES = 60;
+ private static final String SET_FILTER = ID + " < " + NUM_SET_VALUES;
+ // STRING and BYTES sets are Bloom filters; these params keep their literals
short
+ private static final String BLOOM_FILTER_PARAMS =
"expectedInsertions=1000;fpp=0.0001";
+
+ @Override
+ protected long getCountStarResult() {
+ return NUM_ROWS;
+ }
+
+ @Test
+ public void testIdSetIsTheSameInBothEngines()
+ throws Exception {
+ for (String column : List.of(INT_COL, LONG_COL, STR_COL, BYTES_COL)) {
+ String query = "SELECT " + idSetExpression(column) + " FROM " +
getTableName() + " WHERE " + SET_FILTER;
+ setUseMultiStageQueryEngine(false);
+ String singleStageIdSet = getSingleValue(postQuery(query)).asText();
+ setUseMultiStageQueryEngine(true);
+ String multiStageIdSet = getSingleValue(postQuery(query)).asText();
+ assertEquals(multiStageIdSet, singleStageIdSet, column);
+ }
+ }
+
+ @Test(dataProvider = "useBothQueryEngines")
+ public void testFilterBySetFromEitherEngine(boolean
buildSetWithMultiStageEngine)
+ throws Exception {
+ for (String column : List.of(INT_COL, LONG_COL, STR_COL, BYTES_COL)) {
+ setUseMultiStageQueryEngine(buildSetWithMultiStageEngine);
+ String idSet = getSingleValue(
+ postQuery("SELECT " + idSetExpression(column) + " FROM " +
getTableName() + " WHERE " + SET_FILTER)).asText();
+ String inQuery =
+ "SELECT COUNT(*) FROM " + getTableName() + " WHERE IN_ID_SET(" +
column + ", '" + idSet + "') = 1";
+ String notInQuery =
+ "SELECT COUNT(*) FROM " + getTableName() + " WHERE IN_ID_SET(" +
column + ", '" + idSet + "') = 0";
+ setUseMultiStageQueryEngine(false);
+ long singleStageCount = getSingleValue(postQuery(inQuery)).asLong();
+ assertEquals(getSingleValue(postQuery(notInQuery)).asLong(), NUM_ROWS -
singleStageCount, column);
+ setUseMultiStageQueryEngine(true);
+ long multiStageCount = getSingleValue(postQuery(inQuery)).asLong();
+ assertEquals(getSingleValue(postQuery(notInQuery)).asLong(), NUM_ROWS -
multiStageCount, column);
+ assertEquals(multiStageCount, singleStageCount, column);
+ assertEquals(multiStageCount, getExpectedCount(column,
IdSets.fromBase64String(idSet)), column);
+ }
+ }
+
+ @Test
+ public void testScalarSubquery()
+ throws Exception {
+ // The set argument is a scalar subquery, so IN_ID_SET runs as a scalar
function above the join that supplies it
+ setUseMultiStageQueryEngine(true);
+ for (String column : List.of(INT_COL, LONG_COL, STR_COL, BYTES_COL)) {
+ String idSetQuery = "SELECT " + idSetExpression(column) + " FROM " +
getTableName() + " WHERE " + SET_FILTER;
+ String query =
+ "SELECT COUNT(*) FROM " + getTableName() + " WHERE IN_ID_SET(" +
column + ", (" + idSetQuery + ")) = 1";
+ IdSet idSet =
IdSets.fromBase64String(getSingleValue(postQuery(idSetQuery)).asText());
+ assertEquals(getSingleValue(postQuery(query)).asLong(),
getExpectedCount(column, idSet), column);
+ }
+ }
+
+ @Test(dataProvider = "useBothQueryEngines")
+ public void testMismatchedValueType(boolean useMultiStageQueryEngine)
+ throws Exception {
+ setUseMultiStageQueryEngine(useMultiStageQueryEngine);
+ IdSet intIdSet = IdSets.create(DataType.INT);
+ intIdSet.add(1);
+ JsonNode response = postQuery(
+ "SELECT COUNT(*) FROM " + getTableName() + " WHERE IN_ID_SET(" +
LONG_COL + ", '" + intIdSet.toBase64String()
+ + "') = 1");
+ String exceptions = response.get("exceptions").toString();
+ assertTrue(exceptions.contains("Cannot look up LONG values in an IdSet
built from INT values"), exceptions);
+ }
+
+ private static String idSetExpression(String column) {
+ if (column.equals(STR_COL) || column.equals(BYTES_COL)) {
+ return "IDSET(" + column + ", '" + BLOOM_FILTER_PARAMS + "')";
+ }
+ return "IDSET(" + column + ")";
+ }
+
+ /// Returns the number of rows whose value of the column the IdSet contains,
which includes any Bloom filter false
+ /// positives. INT and LONG sets are exact, so they hold exactly the values
of the rows with id < NUM_SET_VALUES.
+ private static long getExpectedCount(String column, IdSet idSet) {
+ long numValuesInSet = 0;
+ for (int value = 0; value < NUM_VALUES; value++) {
+ boolean contains;
+ switch (column) {
+ case INT_COL:
+ contains = idSet.contains(value);
+ break;
+ case LONG_COL:
+ contains = idSet.contains(10_000_000_000L + value);
+ break;
+ case STR_COL:
+ contains = idSet.contains(stringValue(value));
+ break;
+ default:
+ contains =
idSet.contains(stringValue(value).getBytes(StandardCharsets.UTF_8));
+ break;
+ }
+ if (contains) {
+ numValuesInSet++;
+ } else if (value < NUM_SET_VALUES) {
+ throw new AssertionError(column + " set misses value " + value);
+ }
+ }
+ if (column.equals(INT_COL) || column.equals(LONG_COL)) {
+ assertEquals(numValuesInSet, NUM_SET_VALUES, column);
+ }
+ return numValuesInSet * (NUM_ROWS / NUM_VALUES);
+ }
+
+ private static String stringValue(int value) {
+ return "value_" + value;
+ }
+
+ private static JsonNode getSingleValue(JsonNode response) {
+ assertTrue(response.get("exceptions").isEmpty(),
response.get("exceptions").toString());
+ return response.get("resultTable").get("rows").get(0).get(0);
+ }
+
+ @Override
+ public String getTableName() {
+ return TABLE_NAME;
+ }
+
+ @Override
+ public Schema createSchema() {
+ return new Schema.SchemaBuilder().setSchemaName(getTableName())
+ .addSingleValueDimension(ID, DataType.INT)
+ .addSingleValueDimension(INT_COL, DataType.INT)
+ .addSingleValueDimension(LONG_COL, DataType.LONG)
+ .addSingleValueDimension(STR_COL, DataType.STRING)
+ .addSingleValueDimension(BYTES_COL, DataType.BYTES)
+ .build();
+ }
+
+ @Override
+ public List<File> createAvroFiles()
+ throws Exception {
+ org.apache.avro.Schema avroSchema =
org.apache.avro.Schema.createRecord("myRecord", null, null, false);
+ avroSchema.setFields(List.of(
+ new org.apache.avro.Schema.Field(ID,
org.apache.avro.Schema.create(org.apache.avro.Schema.Type.INT), null,
+ null),
+ new org.apache.avro.Schema.Field(INT_COL,
org.apache.avro.Schema.create(org.apache.avro.Schema.Type.INT), null,
+ null),
+ new org.apache.avro.Schema.Field(LONG_COL,
org.apache.avro.Schema.create(org.apache.avro.Schema.Type.LONG),
+ null, null),
+ new org.apache.avro.Schema.Field(STR_COL,
org.apache.avro.Schema.create(org.apache.avro.Schema.Type.STRING),
+ null, null),
+ new org.apache.avro.Schema.Field(BYTES_COL,
org.apache.avro.Schema.create(org.apache.avro.Schema.Type.BYTES),
+ null, null)));
+ try (AvroFilesAndWriters avroFilesAndWriters =
createAvroFilesAndWriters(avroSchema)) {
+ List<DataFileWriter<GenericData.Record>> writers =
avroFilesAndWriters.getWriters();
+ for (int i = 0; i < NUM_ROWS; i++) {
+ int value = i % NUM_VALUES;
+ String stringValue = stringValue(value);
+ GenericData.Record record = new GenericData.Record(avroSchema);
+ record.put(ID, i);
+ record.put(INT_COL, value);
+ record.put(LONG_COL, 10_000_000_000L + value);
+ record.put(STR_COL, stringValue);
+ record.put(BYTES_COL,
ByteBuffer.wrap(stringValue.getBytes(StandardCharsets.UTF_8)));
+ writers.get(i % getNumAvroFiles()).append(record);
+ }
+ return avroFilesAndWriters.getAvroFiles();
+ }
+ }
+}
diff --git
a/pinot-integration-tests/src/test/resources/udf-test-results/all-functions.yaml
b/pinot-integration-tests/src/test/resources/udf-test-results/all-functions.yaml
index 5c44ac2f86b..5fb9cfcb4b6 100644
---
a/pinot-integration-tests/src/test/resources/udf-test-results/all-functions.yaml
+++
b/pinot-integration-tests/src/test/resources/udf-test-results/all-functions.yaml
@@ -866,7 +866,7 @@ in:
transform:
"org.apache.pinot.core.operator.transform.function.InTransformFunction"
udf: null
inidset:
- scalar: null
+ scalar:
"ArgumentCountBasedScalarFunction{org.apache.pinot.core.function.scalar.IdSetFunctions.inIdSet}"
transform:
"org.apache.pinot.core.operator.transform.function.InIdSetTransformFunction"
udf: null
innerproduct:
diff --git a/pinot-query-planner/src/test/resources/queries/JoinPlans.json
b/pinot-query-planner/src/test/resources/queries/JoinPlans.json
index fa6b9c0c892..b68b0d4a0a5 100644
--- a/pinot-query-planner/src/test/resources/queries/JoinPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/JoinPlans.json
@@ -64,6 +64,20 @@
"\n"
]
},
+ {
+ "description": "Inner join with an IN_ID_SET filter on one table,
which is pushed into the leaf stage",
+ "sql": "EXPLAIN PLAN FOR SELECT * FROM a JOIN b ON a.col1 = b.col2
WHERE IN_ID_SET(a.col3, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "output": [
+ "Execution Plan",
+ "\nLogicalJoin(condition=[=($0, $10)], joinType=[inner])",
+ "\n PinotLogicalExchange(distribution=[hash[0]])",
+ "\n LogicalFilter(condition=[INIDSET($2,
_UTF-8'ATowAAABAAAAAAACABAAAAACAAQABgA=')])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n PinotLogicalExchange(distribution=[hash[1]])",
+ "\n PinotLogicalTableScan(table=[[default, b]])",
+ "\n"
+ ]
+ },
{
"description": "SELECT * inner join with filter",
"sql": "EXPLAIN PLAN FOR SELECT * FROM a JOIN b ON a.col1 = b.col2
WHERE a.col3 >= 0 AND a.col3 > b.col3",
diff --git
a/pinot-query-planner/src/test/resources/queries/PhysicalOptimizerPlans.json
b/pinot-query-planner/src/test/resources/queries/PhysicalOptimizerPlans.json
index 771374c9fc5..92de24ed38e 100644
--- a/pinot-query-planner/src/test/resources/queries/PhysicalOptimizerPlans.json
+++ b/pinot-query-planner/src/test/resources/queries/PhysicalOptimizerPlans.json
@@ -263,6 +263,21 @@
},
"physical_opt_join_planning_tests": {
"queries": [
+ {
+ "description": "Inner join with an IN_ID_SET filter on one table,
which is pushed into the leaf stage",
+ "sql": "SET usePhysicalOptimizer=true; EXPLAIN PLAN FOR SELECT * FROM
a JOIN b ON a.col1 = b.col2 WHERE IN_ID_SET(a.col3,
'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "output": [
+ "Execution Plan",
+ "\nPhysicalExchange(exchangeStrategy=[SINGLETON_EXCHANGE])",
+ "\n PhysicalJoin(condition=[=($0, $10)], joinType=[inner])",
+ "\n PhysicalExchange(exchangeStrategy=[PARTITIONING_EXCHANGE],
distKeys=[[0]])",
+ "\n PhysicalFilter(condition=[INIDSET($2,
_UTF-8'ATowAAABAAAAAAACABAAAAACAAQABgA=')])",
+ "\n PhysicalTableScan(table=[[default, a]])",
+ "\n PhysicalExchange(exchangeStrategy=[PARTITIONING_EXCHANGE],
distKeys=[[1]])",
+ "\n PhysicalTableScan(table=[[default, b]])",
+ "\n"
+ ]
+ },
{
"description": "Inner join with order by",
"sql": "SET usePhysicalOptimizer=true; EXPLAIN PLAN FOR SELECT a.col1,
a.ts, b.col3 FROM a JOIN b ON a.col1 = b.col2 ORDER BY a.col1",
diff --git a/pinot-query-runtime/src/test/resources/queries/IdSet.json
b/pinot-query-runtime/src/test/resources/queries/IdSet.json
new file mode 100644
index 00000000000..65b462c35bf
--- /dev/null
+++ b/pinot-query-runtime/src/test/resources/queries/IdSet.json
@@ -0,0 +1,360 @@
+{
+ "idset_filters": {
+ "comments": "IDSET builds the set in the multi-stage engine and IN_ID_SET
consumes it, compared with the equivalent IN subquery in H2. A scalar subquery
as the IdSet argument is joined in, so IN_ID_SET runs as a scalar function
above the join; a literal IdSet on base table columns runs in the leaf stage.
The STRING, BYTES, FLOAT and DOUBLE sets are Bloom filters, sized so that these
few values cannot produce false positives.",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "floatCol", "type": "FLOAT"},
+ {"name": "doubleCol", "type": "DOUBLE"},
+ {"name": "strCol", "type": "STRING"},
+ {"name": "bytesCol", "type": "BYTES"},
+ {"name": "boolCol", "type": "BOOLEAN"},
+ {"name": "tsCol", "type": "TIMESTAMP"},
+ {"name": "grp", "type": "STRING"}
+ ],
+ "inputs": [
+ [1, 10000000001, 1.5, 1.25, "a", "AA01", true, "2020-01-01
00:00:00", "g1"],
+ [2, 10000000002, 2.5, 2.25, "b", "AA02", true, "2020-01-02
00:00:00", "g1"],
+ [3, 10000000003, 3.5, 3.25, "c", "AA03", false, "2020-01-03
00:00:00", "g1"],
+ [4, 10000000004, 4.5, 4.25, "d", "AA04", false, "2020-01-04
00:00:00", "g2"],
+ ["------"],
+ [5, 10000000005, 5.5, 5.25, "e", "AA05", true, "2020-01-05
00:00:00", "g2"],
+ [6, 10000000006, 6.5, 6.25, "f", "AA06", false, "2020-01-06
00:00:00", "g2"]
+ ]
+ },
+ "tbl2": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "floatCol", "type": "FLOAT"},
+ {"name": "doubleCol", "type": "DOUBLE"},
+ {"name": "strCol", "type": "STRING"},
+ {"name": "bytesCol", "type": "BYTES"},
+ {"name": "boolCol", "type": "BOOLEAN"},
+ {"name": "tsCol", "type": "TIMESTAMP"},
+ {"name": "grp", "type": "STRING"}
+ ],
+ "inputs": [
+ [2, 10000000002, 2.5, 2.25, "b", "AA02", true, "2020-01-02
00:00:00", "g1"],
+ [3, 10000000003, 3.5, 3.25, "c", "AA03", false, "2020-01-03
00:00:00", "g1"],
+ [4, 10000000004, 4.5, 4.25, "d", "AA04", true, "2020-01-04
00:00:00", "g2"],
+ ["------"],
+ [7, 10000000007, 7.5, 7.25, "g", "AA07", false, "2020-01-07
00:00:00", "g2"],
+ [8, 10000000008, 8.5, 8.25, "h", "AA08", true, "2020-01-08
00:00:00", "g3"],
+ [2, 10000000002, 2.5, 2.25, "b", "AA02", false, "2020-01-02
00:00:00", "g3"],
+ [9, 10000000009, 9.5, 9.25, "i", "AA09", false, "2020-01-09
00:00:00", "g4"],
+ [10, 10000000010, 10.5, 10.25, "j", "AA10", true, "2020-01-10
00:00:00", "g4"],
+ [11, 10000000011, 11.5, 11.25, "k", "AA11", true, "2020-01-11
00:00:00", "g4"]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "INT set (RoaringBitmap) from a scalar subquery",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(intCol) FROM {tbl} WHERE grp = 'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol IN (SELECT intCol
FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "INT set (RoaringBitmap), negated",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(intCol) FROM {tbl} WHERE grp = 'g1')) = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol NOT IN (SELECT
intCol FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "INT set forced into a Bloom filter",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(intCol, 'sizeThresholdInBytes=0;expectedInsertions=100;fpp=0.0001') FROM
{tbl} WHERE grp = 'g2')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol IN (SELECT intCol
FROM {tbl} WHERE grp = 'g2')"
+ },
+ {
+ "description": "LONG set (Roaring64NavigableMap)",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(longCol, (SELECT
IDSET(longCol) FROM {tbl} WHERE grp = 'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE longCol IN (SELECT longCol
FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "FLOAT set (Bloom filter)",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(floatCol, (SELECT
IDSET(floatCol, 'expectedInsertions=100;fpp=0.0001') FROM {tbl} WHERE grp =
'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE floatCol IN (SELECT
floatCol FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "DOUBLE set (Bloom filter)",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(doubleCol, (SELECT
IDSET(doubleCol, 'expectedInsertions=100;fpp=0.0001') FROM {tbl} WHERE grp =
'g2')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE doubleCol IN (SELECT
doubleCol FROM {tbl} WHERE grp = 'g2')"
+ },
+ {
+ "description": "STRING set (Bloom filter)",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(strCol, (SELECT
IDSET(strCol, 'expectedInsertions=100;fpp=0.0001') FROM {tbl} WHERE grp =
'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE strCol IN (SELECT strCol
FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "A 9 MB STRING set crosses the mailbox in more than one
chunk",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(strCol, (SELECT
IDSET(strCol, 'expectedInsertions=10000000;fpp=0.03') FROM {tbl} WHERE grp =
'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE strCol IN (SELECT strCol
FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "BYTES set (Bloom filter)",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(bytesCol, (SELECT
IDSET(bytesCol, 'expectedInsertions=100;fpp=0.0001') FROM {tbl} WHERE grp =
'g2')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE bytesCol IN (SELECT
bytesCol FROM {tbl} WHERE grp = 'g2')"
+ },
+ {
+ "description": "BOOLEAN set, looked up through the stored INT value",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(boolCol, (SELECT
IDSET(boolCol) FROM {tbl} WHERE intCol = 1)) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE boolCol IN (SELECT boolCol
FROM {tbl} WHERE intCol = 1)"
+ },
+ {
+ "description": "TIMESTAMP set, looked up through the stored LONG
value",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(tsCol, (SELECT
IDSET(tsCol) FROM {tbl} WHERE grp = 'g1')) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE tsCol IN (SELECT tsCol
FROM {tbl} WHERE grp = 'g1')"
+ },
+ {
+ "description": "A subquery that matches no rows yields a NULL set with
null handling on, which holds no ids",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(intCol) FROM {tbl} WHERE intCol < 0)) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol IN (SELECT intCol
FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "A subquery that matches no rows yields a NULL set with
null handling on, negated",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(intCol) FROM {tbl} WHERE intCol < 0)) = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol NOT IN (SELECT
intCol FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "A subquery that matches no rows yields an empty set
with null handling off",
+ "sql": "SET enableNullHandling=false; SELECT COUNT(*) FROM {tbl2}
WHERE IN_ID_SET(intCol, (SELECT IDSET(intCol) FROM {tbl} WHERE intCol < 0)) =
1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol IN (SELECT intCol
FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "A subquery that matches no rows yields an empty set
with null handling off, negated",
+ "sql": "SET enableNullHandling=false; SELECT COUNT(*) FROM {tbl2}
WHERE IN_ID_SET(intCol, (SELECT IDSET(intCol) FROM {tbl} WHERE intCol < 0)) =
0",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol NOT IN (SELECT
intCol FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "Correlated subquery: one set per group, built by a
grouped IDSET",
+ "sql": "SELECT COUNT(*) FROM {tbl2} b WHERE IN_ID_SET(b.intCol,
(SELECT IDSET(a.intCol) FROM {tbl} a WHERE a.grp = b.grp)) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} b WHERE b.intCol IN (SELECT
a.intCol FROM {tbl} a WHERE a.grp = b.grp)"
+ },
+ {
+ "description": "Correlated subquery, negated: groups with no match see
an empty set",
+ "sql": "SELECT COUNT(*) FROM {tbl2} b WHERE IN_ID_SET(b.intCol,
(SELECT IDSET(a.intCol) FROM {tbl} a WHERE a.grp = b.grp)) = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} b WHERE b.intCol NOT IN (SELECT
a.intCol FROM {tbl} a WHERE a.grp = b.grp)"
+ },
+ {
+ "description": "CTE cross join supplies the set as a column",
+ "sql": "WITH s AS (SELECT IDSET(strCol,
'expectedInsertions=100;fpp=0.0001') AS ids FROM {tbl} WHERE grp = 'g2') SELECT
COUNT(*) FROM {tbl2}, s WHERE IN_ID_SET({tbl2}.strCol, s.ids) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE strCol IN (SELECT strCol
FROM {tbl} WHERE grp = 'g2')"
+ },
+ {
+ "description": "HAVING with a set from a scalar subquery",
+ "sql": "SELECT grp, COUNT(*) FROM {tbl2} GROUP BY grp HAVING
IN_ID_SET(MAX(intCol), (SELECT IDSET(intCol) FROM {tbl})) = 1",
+ "h2Sql": "SELECT grp, COUNT(*) FROM {tbl2} GROUP BY grp HAVING
MAX(intCol) IN (SELECT intCol FROM {tbl})"
+ },
+ {
+ "description": "Post-join filter on columns of both sides, set from a
scalar subquery",
+ "sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
WHERE IN_ID_SET(a.intCol + b.intCol, (SELECT IDSET(intCol) FROM {tbl2})) = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
WHERE a.intCol + b.intCol IN (SELECT intCol FROM {tbl2})"
+ },
+ {
+ "description": "Literal set in a leaf filter (the two-query flow); the
set holds 2, 4 and 6",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol IN (2, 4, 6)"
+ },
+ {
+ "description": "Literal set in a leaf filter, bare boolean and
negated",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE NOT IN_ID_SET(intCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA=')",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl2} WHERE intCol NOT IN (2, 4, 6)"
+ },
+ {
+ "description": "Literal set in a post-join filter on columns of both
sides",
+ "sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
WHERE IN_ID_SET(a.intCol + b.intCol, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
WHERE a.intCol + b.intCol IN (2, 4, 6)"
+ },
+ {
+ "description": "Literal set in a join condition",
+ "sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
AND IN_ID_SET(a.intCol + b.intCol, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a JOIN {tbl2} b ON a.grp = b.grp
AND a.intCol + b.intCol IN (2, 4, 6)"
+ },
+ {
+ "description": "Literal set in HAVING",
+ "sql": "SELECT grp, COUNT(*) FROM {tbl2} GROUP BY grp HAVING
IN_ID_SET(MAX(intCol), 'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "h2Sql": "SELECT grp, COUNT(*) FROM {tbl2} GROUP BY grp HAVING
MAX(intCol) IN (2, 4, 6)"
+ },
+ {
+ "description": "Scalar path rejects a set of another value type",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(intCol, (SELECT
IDSET(longCol) FROM {tbl})) = 1",
+ "expectedException": ".*Cannot look up INT values in an IdSet built
from LONG values.*"
+ },
+ {
+ "description": "Leaf path rejects a set of another value type",
+ "sql": "SELECT COUNT(*) FROM {tbl2} WHERE IN_ID_SET(longCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "expectedException": ".*Cannot look up LONG values in an IdSet built
from INT values.*"
+ }
+ ]
+ },
+ "idset_nullable_probe": {
+ "comments": "IN_ID_SET returns NULL for a NULL value, except with an empty
or NULL set, where it is false, as for an IN subquery. The planner must type it
as nullable, or it simplifies IS NOT TRUE and COALESCE away. NULL values come
from outer joins and from nullable columns, which the planner sees only with
column-based null handling.",
+ "extraProps": {
+ "enableColumnBasedNullHandling": true
+ },
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ [1],
+ [2],
+ ["------"],
+ [3],
+ [4]
+ ]
+ },
+ "tbl2": {
+ "schema": [
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ [2],
+ [4]
+ ]
+ },
+ "nullable": {
+ "schema": [
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ [1],
+ [2],
+ [null]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "IS NOT TRUE over the NULL values of a left join; the
set holds 2, 4 and 6",
+ "sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol =
b.intCol WHERE IN_ID_SET(b.intCol, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') IS NOT
TRUE",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol
= b.intCol WHERE (b.intCol IN (2, 4, 6)) IS NOT TRUE"
+ },
+ {
+ "description": "IS FALSE over the NULL values of a left join",
+ "sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol =
b.intCol WHERE IN_ID_SET(b.intCol, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') IS
FALSE",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol
= b.intCol WHERE (b.intCol IN (2, 4, 6)) IS FALSE"
+ },
+ {
+ "description": "NOT COALESCE over the NULL values of a left join",
+ "sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol =
b.intCol WHERE NOT COALESCE(IN_ID_SET(b.intCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA='), FALSE)",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol
= b.intCol WHERE NOT COALESCE(b.intCol IN (2, 4, 6), FALSE)"
+ },
+ {
+ "description": "COALESCE in the SELECT list over the NULL values of a
left join",
+ "sql": "SELECT a.intCol, COALESCE(IN_ID_SET(b.intCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA='), FALSE) FROM {tbl} a LEFT JOIN {tbl2} b ON
a.intCol = b.intCol",
+ "h2Sql": "SELECT a.intCol, COALESCE(b.intCol IN (2, 4, 6), FALSE) FROM
{tbl} a LEFT JOIN {tbl2} b ON a.intCol = b.intCol"
+ },
+ {
+ "description": "= 0 over the NULL values of a left join drops them,
like NOT IN",
+ "sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol =
b.intCol WHERE IN_ID_SET(b.intCol, 'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} a LEFT JOIN {tbl2} b ON a.intCol
= b.intCol WHERE b.intCol NOT IN (2, 4, 6)"
+ },
+ {
+ "description": "IS NOT TRUE on a nullable column in a leaf stage",
+ "sql": "SELECT COUNT(*) FROM {nullable} WHERE IN_ID_SET(intCol,
'ATowAAABAAAAAAACABAAAAACAAQABgA=') IS NOT TRUE",
+ "h2Sql": "SELECT COUNT(*) FROM {nullable} WHERE (intCol IN (2, 4, 6))
IS NOT TRUE"
+ },
+ {
+ "description": "A NULL value is in no empty set, from a subquery that
matches no rows",
+ "sql": "SELECT COUNT(*) FROM {nullable} WHERE IN_ID_SET(intCol,
(SELECT IDSET(intCol) FROM {tbl} WHERE intCol < 0)) = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {nullable} WHERE intCol NOT IN (SELECT
intCol FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "A NULL value is in no empty set, from a literal in a
leaf stage",
+ "sql": "SELECT COUNT(*) FROM {nullable} WHERE IN_ID_SET(intCol,
'AA==') = 0",
+ "h2Sql": "SELECT COUNT(*) FROM {nullable} WHERE intCol NOT IN (SELECT
intCol FROM {tbl} WHERE intCol < 0)"
+ },
+ {
+ "description": "A literal value is left to the runtime instead of
being folded at planning time",
+ "sql": "SELECT COUNT(*) FROM {tbl} WHERE IN_ID_SET(2,
'ATowAAABAAAAAAACABAAAAACAAQABgA=') = 1",
+ "h2Sql": "SELECT COUNT(*) FROM {tbl} WHERE 2 IN (2, 4, 6)"
+ }
+ ]
+ },
+ "idset_serialized_output": {
+ "comments": "IDSET in the multi-stage engine returns the same bytes as an
IdSet built directly from the values, which is what the single-stage engine
returns, so a set built by either engine works in the other.",
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "intCol", "type": "INT"},
+ {"name": "longCol", "type": "LONG"},
+ {"name": "strCol", "type": "STRING"},
+ {"name": "grp", "type": "STRING"}
+ ],
+ "inputs": [
+ [1, 10000000001, "a", "g1"],
+ [2, 10000000002, "b", "g1"],
+ [3, 10000000003, "c", "g1"],
+ ["------"],
+ [4, 10000000004, "d", "g2"],
+ [5, 10000000005, "e", "g2"],
+ [6, 10000000006, "f", "g2"]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "description": "INT values 1 to 6 as a RoaringBitmap",
+ "sql": "SELECT IDSET(intCol) FROM {tbl}",
+ "outputs": [["ATowAAABAAAAAAAFABAAAAABAAIAAwAEAAUABgA="]]
+ },
+ {
+ "description": "LONG values as a Roaring64NavigableMap",
+ "sql": "SELECT IDSET(longCol) FROM {tbl}",
+ "outputs": [["AgAAAAABAAAAAjowAAABAAAAC1QFABAAAAAB5ALkA+QE5AXkBuQ="]]
+ },
+ {
+ "description": "STRING values as a Bloom filter",
+ "sql": "SELECT IDSET(strCol, 'expectedInsertions=100;fpp=0.0001') FROM
{tbl}",
+ "outputs":
[["AwIBDQAAAB4AAoACAAEAAkAAAAAIAACAgCBAABAAAAAAABAAAAAAAASgAAAAAAAAAAAABAAAAAAgAAAEAACAAgAAAAAgAAEAQAAAAABAgAAIAAIAAEAAAAAAAQAAAAAAAAQAAAAAAAgAAEAAAAAAAAAAAAAAAAAAAAAAQCIQAAAAAAAgAAAAAACQAAACAAAAAAAAAAwAAACAAAAEgAAAAAAAAgAAAACABEAAAAIAQAAAAAAgASAAAAAAACAAAIAAIAgEAAAAAEAAEACAAAAAAAAASAAAACAAACAIAEAAAAgQAAAAAAAAAAAAAIQAAgAABAAAAAIEAAA="]]
+ },
+ {
+ "description": "One set per group",
+ "sql": "SELECT grp, IDSET(intCol) FROM {tbl} GROUP BY grp",
+ "outputs": [
+ ["g1", "ATowAAABAAAAAAACABAAAAABAAIAAwA="],
+ ["g2", "ATowAAABAAAAAAACABAAAAAEAAUABgA="]
+ ]
+ }
+ ]
+ },
+ "idset_multi_value": {
+ "comments": "IDSET over a multi-value column adds every element.",
+ "tables": {
+ "mv": {
+ "schema": [
+ {"name": "mvCol", "type": "INT", "isSingleValue": false}
+ ],
+ "inputs": [
+ [[1, 2]],
+ [[3]]
+ ]
+ },
+ "probe": {
+ "schema": [
+ {"name": "intCol", "type": "INT"}
+ ],
+ "inputs": [
+ [1],
+ [2],
+ [3],
+ [4],
+ [5]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "sql": "SELECT COUNT(*) FROM {probe} WHERE IN_ID_SET(intCol, (SELECT
IDSET(mvCol) FROM {mv})) = 1",
+ "outputs": [[3]]
+ },
+ {
+ "sql": "SELECT COUNT(*) FROM {probe} WHERE IN_ID_SET(intCol, (SELECT
IDSET(mvCol) FROM {mv})) = 0",
+ "outputs": [[2]]
+ }
+ ]
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
