This is an automated email from the ASF dual-hosted git repository.
xiangfu0 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 6e0a0a1b35a Support ARRAY_AGG without a data type (#19988)
6e0a0a1b35a is described below
commit 6e0a0a1b35a6e9b80595487e0a1e5412691d1480
Author: Xiang Fu <[email protected]>
AuthorDate: Wed Oct 28 18:36:31 2026 -0700
Support ARRAY_AGG without a data type (#19988)
---
.../apache/pinot/sql/parsers/CalciteSqlParser.java | 11 +++
.../pinot/sql/parsers/CalciteSqlParserTest.java | 14 +++
.../AggregateFunctionRewriteOptimizer.java | 44 +++++++++
.../AggregateFunctionRewriteOptimizerTest.java | 44 +++++++++
.../pinot/queries/ArrayAggMvQueriesTest.java | 19 ++++
.../apache/pinot/queries/ArrayAggQueriesTest.java | 43 +++++++++
.../pinot/queries/ArrayAggStarTreeQueriesTest.java | 42 +++++++--
.../PinotAggregateExchangeNodeInsertRule.java | 9 ++
.../PinotAggregateTypeArgumentRewriteRule.java | 85 ++++++++++-------
.../PinotApproximateAggregateRewriteRule.java | 22 ++---
.../pinot/calcite/rel/rules/PinotRuleUtils.java | 21 +++++
.../org/apache/pinot/query/QueryEnvironment.java | 8 +-
.../v2/opt/rules/AggregatePushdownRule.java | 9 ++
.../query/ApproximateAggregateRewriteTest.java | 32 +++++++
.../src/test/resources/queries/AggregatePlans.json | 105 +++++++++++++++++++++
.../src/test/resources/queries/UDFAggregates.json | 95 ++++++++++++++++++-
16 files changed, 536 insertions(+), 67 deletions(-)
diff --git
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
index 274b6be95cf..1312b2ac3a1 100644
---
a/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
+++
b/pinot-common/src/main/java/org/apache/pinot/sql/parsers/CalciteSqlParser.java
@@ -1308,6 +1308,17 @@ public class CalciteSqlParser {
operands.add(toExpression(childNode));
}
}
+ if (functionKind == SqlKind.ARRAY_AGG &&
functionNode.getFunctionQuantifier() != null
+ && "DISTINCT".equals(functionNode.getFunctionQuantifier().toString()))
{
+ Expression distinct = RequestUtils.getLiteralExpression(true);
+ int lastIndex = operands.size() - 1;
+ if ((operands.size() == 2 || operands.size() == 3) &&
operands.get(lastIndex).isSetLiteral()
+ && operands.get(lastIndex).getLiteral().isSetBoolValue()) {
+ operands.set(lastIndex, distinct);
+ } else if (operands.size() == 1 || operands.size() == 2) {
+ operands.add(distinct);
+ }
+ }
ParserUtils.validateFunction(canonicalName, operands);
Expression functionExpression =
RequestUtils.getFunctionExpression(canonicalName, operands);
if (negated) {
diff --git
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
index 2e3d93f30d5..0845d127e8f 100644
---
a/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
+++
b/pinot-common/src/test/java/org/apache/pinot/sql/parsers/CalciteSqlParserTest.java
@@ -64,6 +64,20 @@ public class CalciteSqlParserTest {
private static final String SINGLE_CHAR = "a";
private static final String QUERY_TEMPLATE = "SELECT %s FROM %s";
+ @Test
+ public void testArrayAggDistinctPreservesNonBooleanOperands() {
+ for (String[] arguments : new String[][]{
+ {"intCol, UPPER('long')", "intCol, UPPER('long'), true"},
+ {"intCol, longCol", "intCol, longCol, true"},
+ {"intCol, 'INT', strCol", "intCol, 'INT', strCol"},
+ {"intCol ORDER BY intCol", "intCol, intCol, true"}
+ }) {
+ assertEquals(CalciteSqlParser.compileToPinotQuery("SELECT
ARRAY_AGG(DISTINCT " + arguments[0] + ") FROM t")
+ .getSelectList(),
+ CalciteSqlParser.compileToPinotQuery("SELECT ARRAY_AGG(" +
arguments[1] + ") FROM t").getSelectList());
+ }
+ }
+
@AfterMethod
public void resetLegacyUnescaping() {
// Reset to default (false) after each test
diff --git
a/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
index 398e5d53b9b..997bac50e18 100644
---
a/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
+++
b/pinot-core/src/main/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizer.java
@@ -37,6 +37,7 @@ import org.apache.pinot.spi.utils.CommonConstants;
/// Always applied:
/// - FIRST_WITH_TIME(dataColumn, timeColumn) -> FIRST_WITH_TIME(dataColumn,
timeColumn, 'dataType')
/// - LAST_WITH_TIME(dataColumn, timeColumn) -> LAST_WITH_TIME(dataColumn,
timeColumn, 'dataType')
+/// - ARRAY_AGG(column[, isDistinct]) -> ARRAY_AGG(column, 'dataType'[,
isDistinct])
/// - MODE(stringColumn[, 'MIN'|'MAX']) -> MODE(stringColumn, 'MIN'|'MAX',
'STRING')
///
/// Applied only when enabled through the `inferAnyValueType` query option:
@@ -112,6 +113,10 @@ public class AggregateFunctionRewriteOptimizer implements
StatementOptimizer {
}
return;
}
+ if (normalizedName.equals(AggregationFunctionType.ARRAYAGG.name()) &&
isArrayAggWithoutDataType(function)) {
+ addArrayAggDataTypeArgument(function, normalizedName, fieldSpec);
+ return;
+ }
if (normalizedName.equals(AggregationFunctionType.MODE.name()) &&
function.getOperandsSize() <= 2) {
addModeDataTypeArgument(function, fieldSpec);
return;
@@ -175,6 +180,45 @@ public class AggregateFunctionRewriteOptimizer implements
StatementOptimizer {
function.addToOperands(RequestUtils.getLiteralExpression(dataType));
}
+ /// Matches ARRAY_AGG(column) and ARRAY_AGG(column, isDistinct); a string
second argument is the explicit data type.
+ private static boolean isArrayAggWithoutDataType(Function function) {
+ int numOperands = function.getOperandsSize();
+ if (numOperands == 1) {
+ return true;
+ }
+ if (numOperands == 2) {
+ Expression secondOperand = function.getOperands().get(1);
+ return secondOperand.isSetLiteral() &&
secondOperand.getLiteral().isSetBoolValue();
+ }
+ return false;
+ }
+
+ /// Inserts the column's logical type, the element type for a multi-value
column, as the second argument. The
+ /// supported types match `PinotAggregateTypeArgumentRewriteRule` in the
multi-stage engine.
+ private static void addArrayAggDataTypeArgument(Function function, String
functionName, FieldSpec fieldSpec) {
+ switch (fieldSpec.getDataType()) {
+ case BOOLEAN:
+ case INT:
+ case LONG:
+ case TIMESTAMP:
+ case FLOAT:
+ case DOUBLE:
+ case BIG_DECIMAL:
+ case STRING:
+ case BYTES:
+ case UUID:
+ function.getOperands().add(1,
RequestUtils.getLiteralExpression(fieldSpec.getDataType().name()));
+ return;
+ case JSON:
+ // Same as the multi-stage engine, which types a JSON column as
VARCHAR.
+ function.getOperands().add(1,
RequestUtils.getLiteralExpression(FieldSpec.DataType.STRING.name()));
+ return;
+ default:
+ throw new BadQueryRequestException(
+ String.format("Unsupported data type for %s(expression): %s",
functionName, fieldSpec.getDataType()));
+ }
+ }
+
private static void rewriteToTypedVariant(Function function, String
functionName, FieldSpec.DataType operandType) {
// Rewrite MIN(stringCol) and MAX(stringCol) to MINSTRING / MAXSTRING
// Rewrite MIN(longCol) and MAX(longCol) to MINLONG / MAXLONG
diff --git
a/pinot-core/src/test/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizerTest.java
b/pinot-core/src/test/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizerTest.java
index dc58ca7bac4..1cc042a0677 100644
---
a/pinot-core/src/test/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizerTest.java
+++
b/pinot-core/src/test/java/org/apache/pinot/core/query/optimizer/statement/AggregateFunctionRewriteOptimizerTest.java
@@ -18,7 +18,9 @@
*/
package org.apache.pinot.core.query.optimizer.statement;
+import java.util.Map;
import org.apache.pinot.common.request.PinotQuery;
+import org.apache.pinot.spi.data.DimensionFieldSpec;
import org.apache.pinot.spi.data.FieldSpec.DataType;
import org.apache.pinot.spi.data.Schema;
import org.apache.pinot.spi.exception.BadQueryRequestException;
@@ -48,6 +50,9 @@ public class AggregateFunctionRewriteOptimizerTest {
.addMultiValueDimension("mvCol", DataType.INT)
.addMultiValueDimension("mvStringCol", DataType.STRING)
.addSingleValueDimension("timeCol", DataType.LONG)
+ .addComplex("mapCol", DataType.MAP, Map.of(
+ "key", new DimensionFieldSpec("key", DataType.STRING, true),
+ "value", new DimensionFieldSpec("value", DataType.INT, true)))
.build();
private static final String AUTO_REWRITE = "SET
autoRewriteAggregationType=true; ";
private static final String INFER_ANY_VALUE_TYPE = "SET
inferAnyValueType=true; ";
@@ -88,6 +93,45 @@ public class AggregateFunctionRewriteOptimizerTest {
+ "FROM testTable");
}
+ @DataProvider
+ public Object[][] arrayAggDataTypes() {
+ return new Object[][]{
+ {"intCol", "INT"}, {"longCol", "LONG"}, {"floatCol", "FLOAT"},
{"doubleCol", "DOUBLE"},
+ {"bigDecimalCol", "BIG_DECIMAL"}, {"stringCol", "STRING"}, {"jsonCol",
"STRING"}, {"boolCol", "BOOLEAN"},
+ {"timestampCol", "TIMESTAMP"}, {"bytesCol", "BYTES"}, {"uuidCol",
"UUID"}, {"mvCol", "INT"}
+ };
+ }
+
+ @Test(dataProvider = "arrayAggDataTypes")
+ public void testArrayAggInfersDataType(String column, String dataType) {
+ assertRewrite("SELECT ARRAY_AGG(" + column + "), arrayAgg(" + column + ",
true) FROM testTable",
+ "SELECT ARRAY_AGG(" + column + ", '" + dataType + "'), arrayAgg(" +
column + ", '" + dataType + "', true) "
+ + "FROM testTable");
+ }
+
+ @Test
+ public void testArrayAggDistinctKeyword() {
+ for (String arguments : new String[]{"intCol", "intCol, false", "intCol,
'INT'", "intCol, 'INT', true",
+ "intCol, 'INT', false"}) {
+ assertRewrite("SELECT ARRAY_AGG(DISTINCT " + arguments + ") FROM
testTable",
+ "SELECT ARRAY_AGG(intCol, 'INT', true) FROM testTable");
+ }
+ }
+
+ @Test
+ public void testArrayAggRejectsUnsupportedColumn() {
+ PinotQuery query = CalciteSqlParser.compileToPinotQuery("SELECT
ARRAY_AGG(mapCol) FROM testTable");
+ BadQueryRequestException exception =
expectThrows(BadQueryRequestException.class,
+ () -> new AggregateFunctionRewriteOptimizer().optimize(query, SCHEMA));
+ assertEquals(exception.getMessage(), "Unsupported data type for
ARRAYAGG(expression): MAP");
+ }
+
+ @Test
+ public void testArrayAggKeepsExplicitFormsAndColumnsWithoutSchemaType() {
+ assertUnchanged("SELECT ARRAY_AGG(intCol, 'LONG'), ARRAY_AGG(intCol,
'LONG', true), ARRAY_AGG(intCol, 1), "
+ + "ARRAY_AGG(missingCol), ARRAY_AGG(trim(stringCol), true) FROM
testTable");
+ }
+
@Test
public void testRewritesNestedAggregationsConsistently() {
// Aliases, transforms, FILTER, HAVING and ORDER BY wrap the aggregation
in another function.
diff --git
a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggMvQueriesTest.java
b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggMvQueriesTest.java
index 118d27d143c..d21125a17e7 100644
---
a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggMvQueriesTest.java
+++
b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggMvQueriesTest.java
@@ -26,6 +26,7 @@ import java.util.List;
import java.util.Set;
import org.apache.commons.io.FileUtils;
import org.apache.pinot.common.response.broker.ResultTable;
+import org.apache.pinot.common.utils.DataSchema.ColumnDataType;
import org.apache.pinot.core.operator.blocks.results.AggregationResultsBlock;
import org.apache.pinot.core.operator.query.AggregationOperator;
import
org.apache.pinot.segment.local.indexsegment.immutable.ImmutableSegmentLoader;
@@ -162,6 +163,24 @@ public class ArrayAggMvQueriesTest extends BaseQueriesTest
{
assertEquals(((String[]) resultTable.getRows().get(0)[4]).length, 2 *
NUM_RECORDS);
}
+ /// The data type argument can be omitted for a multi-value column; the
optimizer supplies its element type.
+ @Test
+ public void testArrayAggMvWithInferredDataType() {
+ String query = "SELECT ArrayAgg(intMV), ArrayAgg(longMV, true),
ArrayAgg(floatMV), ArrayAgg(doubleMV, true), "
+ + "ArrayAgg(stringMV) FROM testTableMv";
+ ResultTable resultTable = getBrokerResponseForOptimizedQuery(query,
SCHEMA).getResultTable();
+ assertEquals(resultTable.getDataSchema().getColumnDataTypes(), new
ColumnDataType[]{
+ ColumnDataType.INT_ARRAY, ColumnDataType.LONG_ARRAY,
ColumnDataType.FLOAT_ARRAY, ColumnDataType.DOUBLE_ARRAY,
+ ColumnDataType.STRING_ARRAY
+ });
+ Object[] row = resultTable.getRows().get(0);
+ assertEquals(((int[]) row[0]).length, 8 * NUM_RECORDS);
+ assertEquals(((long[]) row[1]).length, 2 * NUM_RECORDS);
+ assertEquals(((float[]) row[2]).length, 8 * NUM_RECORDS);
+ assertEquals(((double[]) row[3]).length, 2 * NUM_RECORDS);
+ assertEquals(((String[]) row[4]).length, 8 * NUM_RECORDS);
+ }
+
@AfterClass
public void tearDown()
throws IOException {
diff --git
a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggQueriesTest.java
b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggQueriesTest.java
index a1749adeeea..674203533e9 100644
--- a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggQueriesTest.java
+++ b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggQueriesTest.java
@@ -20,6 +20,7 @@ package org.apache.pinot.queries;
import java.io.File;
import java.io.IOException;
+import java.lang.reflect.Array;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
@@ -29,6 +30,7 @@ import java.util.Set;
import java.util.UUID;
import org.apache.commons.io.FileUtils;
import org.apache.pinot.common.response.broker.ResultTable;
+import org.apache.pinot.common.utils.DataSchema.ColumnDataType;
import org.apache.pinot.common.utils.HashUtil;
import org.apache.pinot.core.operator.blocks.results.AggregationResultsBlock;
import org.apache.pinot.core.operator.query.AggregationOperator;
@@ -83,6 +85,11 @@ public class ArrayAggQueriesTest extends BaseQueriesTest {
.addSingleValueDimension(BYTES_COLUMN, DataType.BYTES)
.addSingleValueDimension(UUID_COLUMN, DataType.UUID)
.build();
+ // The JSON column aggregates to strings
+ private static final ColumnDataType[] EXPECTED_COLUMN_DATA_TYPES = {
+ ColumnDataType.INT_ARRAY, ColumnDataType.LONG_ARRAY,
ColumnDataType.FLOAT_ARRAY, ColumnDataType.DOUBLE_ARRAY,
+ ColumnDataType.STRING_ARRAY, ColumnDataType.STRING_ARRAY,
ColumnDataType.BYTES_ARRAY, ColumnDataType.UUID_ARRAY
+ };
private Set<Integer> _values;
private int[] _expectedCardinalityResults;
@@ -226,6 +233,42 @@ public class ArrayAggQueriesTest extends BaseQueriesTest {
assertEquals(((String[]) resultTable.getRows().get(0)[7]).length,
_expectedCardinalityResults[7]);
}
+ /// The data type argument can be omitted for a column; the optimizer
supplies it from the schema.
+ @Test
+ public void testArrayAggWithInferredDataType() {
+ String query = "SELECT ArrayAgg(intColumn), ArrayAgg(longColumn),
ArrayAgg(floatColumn), ArrayAgg(doubleColumn), "
+ + "ArrayAgg(stringColumn), ArrayAgg(jsonColumn),
ArrayAgg(bytesColumn), ArrayAgg(uuidColumn) FROM testTable";
+ ResultTable resultTable = getBrokerResponseForOptimizedQuery(query,
SCHEMA).getResultTable();
+ assertEquals(resultTable.getDataSchema().getColumnDataTypes(),
EXPECTED_COLUMN_DATA_TYPES);
+ Object[] row = resultTable.getRows().get(0);
+ for (int i = 0; i < 8; i++) {
+ assertEquals(Array.getLength(row[i]), 4 * NUM_RECORDS);
+ }
+ }
+
+ @Test
+ public void testArrayAggDistinctWithInferredDataType() {
+ String query = "SELECT ArrayAgg(intColumn, true), ArrayAgg(longColumn,
true), ArrayAgg(floatColumn, true), "
+ + "ArrayAgg(doubleColumn, true), ArrayAgg(stringColumn, true),
ArrayAgg(jsonColumn, true), "
+ + "ArrayAgg(bytesColumn, true), ArrayAgg(uuidColumn, true) FROM
testTable";
+ ResultTable resultTable = getBrokerResponseForOptimizedQuery(query,
SCHEMA).getResultTable();
+ assertEquals(resultTable.getDataSchema().getColumnDataTypes(),
EXPECTED_COLUMN_DATA_TYPES);
+ Object[] row = resultTable.getRows().get(0);
+ for (int i = 0; i < 8; i++) {
+ assertEquals(Array.getLength(row[i]), _expectedCardinalityResults[i]);
+ }
+ }
+
+ @Test
+ public void testArrayAggDistinctKeyword() {
+ for (String arguments : new String[]{"intColumn", "intColumn, false",
"intColumn, 'INT'",
+ "intColumn, 'INT', true", "intColumn, 'INT', false"}) {
+ ResultTable result = getBrokerResponseForOptimizedQuery(
+ "SELECT ARRAY_AGG(DISTINCT " + arguments + ") FROM testTable",
SCHEMA).getResultTable();
+ assertEquals(((int[]) result.getRows().get(0)[0]).length,
_expectedCardinalityResults[0]);
+ }
+ }
+
@AfterClass
public void tearDown()
throws IOException {
diff --git
a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggStarTreeQueriesTest.java
b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggStarTreeQueriesTest.java
index ac3b9c4565a..6d64c2d1d10 100644
---
a/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggStarTreeQueriesTest.java
+++
b/pinot-core/src/test/java/org/apache/pinot/queries/ArrayAggStarTreeQueriesTest.java
@@ -64,6 +64,13 @@ public class ArrayAggStarTreeQueriesTest extends
BaseQueriesTest {
private static final String LONG_METRIC = "lm";
private static final String STRING_METRIC = "sm";
private static final String BYTES_METRIC = "bm";
+ private static final Schema SCHEMA = new
Schema.SchemaBuilder().setSchemaName(RAW_TABLE_NAME)
+ .addSingleValueDimension(DIMENSION, DataType.INT)
+ .addSingleValueDimension(INT_METRIC, DataType.INT)
+ .addSingleValueDimension(LONG_METRIC, DataType.LONG)
+ .addSingleValueDimension(STRING_METRIC, DataType.STRING)
+ .addSingleValueDimension(BYTES_METRIC, DataType.BYTES)
+ .build();
private static final Map<String, String> STAR_TREE_ON =
Map.of("useStarTree", "true");
private static final Map<String, String> STAR_TREE_OFF =
Map.of("useStarTree", "false");
@@ -95,13 +102,6 @@ public class ArrayAggStarTreeQueriesTest extends
BaseQueriesTest {
throws Exception {
FileUtils.deleteDirectory(INDEX_DIR);
- Schema schema = new Schema.SchemaBuilder().setSchemaName(RAW_TABLE_NAME)
- .addSingleValueDimension(DIMENSION, DataType.INT)
- .addSingleValueDimension(INT_METRIC, DataType.INT)
- .addSingleValueDimension(LONG_METRIC, DataType.LONG)
- .addSingleValueDimension(STRING_METRIC, DataType.STRING)
- .addSingleValueDimension(BYTES_METRIC, DataType.BYTES)
- .build();
// The LONG metric is raw-encoded: arrayAgg star-tree aggregation supports
both dictionary-encoded and raw
// single-value source columns, so the LONG tests double as raw-metric
build/query coverage.
TableConfig tableConfig = new
TableConfigBuilder(TableType.OFFLINE).setTableName(RAW_TABLE_NAME)
@@ -119,7 +119,7 @@ public class ArrayAggStarTreeQueriesTest extends
BaseQueriesTest {
rows.add(row);
}
- SegmentGeneratorConfig segmentGeneratorConfig = new
SegmentGeneratorConfig(tableConfig, schema);
+ SegmentGeneratorConfig segmentGeneratorConfig = new
SegmentGeneratorConfig(tableConfig, SCHEMA);
segmentGeneratorConfig.setSegmentName(SEGMENT_NAME);
segmentGeneratorConfig.setOutDir(INDEX_DIR.getPath());
SegmentIndexCreationDriverImpl driver = new
SegmentIndexCreationDriverImpl();
@@ -271,11 +271,33 @@ public class ArrayAggStarTreeQueriesTest extends
BaseQueriesTest {
"Non-distinct arrayAgg must not use the star-tree, so it scans the
same docs with the option on or off");
}
+ /// Without the data type argument, distinct arrayAgg is rewritten to the
explicit form with each column's type, which
+ /// the star-tree still serves.
+ @Test
+ public void testDistinctArrayAggWithInferredDataTypeUsesStarTree() {
+ String query = String.format(
+ "SELECT %s, arrayAgg(%s, true), arrayAgg(%s, true), arrayAgg(%s,
true), arrayAgg(%s, true) FROM %s "
+ + "GROUP BY %s ORDER BY %s", DIMENSION, INT_METRIC, LONG_METRIC,
STRING_METRIC, BYTES_METRIC,
+ RAW_TABLE_NAME, DIMENSION, DIMENSION);
+ String explicitQuery = String.format(
+ "SELECT %s, arrayAgg(%s, 'INT', true), arrayAgg(%s, 'LONG', true),
arrayAgg(%s, 'STRING', true), "
+ + "arrayAgg(%s, 'BYTES', true) FROM %s GROUP BY %s ORDER BY %s",
DIMENSION, INT_METRIC, LONG_METRIC,
+ STRING_METRIC, BYTES_METRIC, RAW_TABLE_NAME, DIMENSION, DIMENSION);
+ BrokerResponseNative starTree = getBrokerResponseForOptimizedQuery("SET
useStarTree=true; " + query, SCHEMA);
+ BrokerResponseNative rawScan = getBrokerResponse(explicitQuery,
STAR_TREE_OFF);
+ assertTrue(starTree.getNumDocsScanned() < rawScan.getNumDocsScanned(),
+ "Expected the star-tree to serve the distinct arrayAgg query without a
data type argument");
+ assertSameDistinctValues(starTree.getResultTable(),
rawScan.getResultTable());
+ }
+
/// Runs the query with the star-tree enabled and disabled and asserts each
arrayAgg cell (an unordered MV of
/// distinct values) is set-equal between the two, per group.
private void assertStarTreeMatchesRawScan(String query) {
- ResultTable starTree = getBrokerResponse(query,
STAR_TREE_ON).getResultTable();
- ResultTable rawScan = getBrokerResponse(query,
STAR_TREE_OFF).getResultTable();
+ assertSameDistinctValues(getBrokerResponse(query,
STAR_TREE_ON).getResultTable(),
+ getBrokerResponse(query, STAR_TREE_OFF).getResultTable());
+ }
+
+ private static void assertSameDistinctValues(ResultTable starTree,
ResultTable rawScan) {
List<Object[]> starTreeRows = starTree.getRows();
List<Object[]> rawScanRows = rawScan.getRows();
assertEquals(starTreeRows.size(), rawScanRows.size());
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateExchangeNodeInsertRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateExchangeNodeInsertRule.java
index 2411867f5e1..31aa9619630 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateExchangeNodeInsertRule.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateExchangeNodeInsertRule.java
@@ -578,6 +578,15 @@ public class PinotAggregateExchangeNodeInsertRule {
functionCategory = SqlFunctionCategory.USER_DEFINED_FUNCTION;
} else if (kind == SqlKind.LISTAGG) {
rexList.add(input.getCluster().getRexBuilder().makeLiteral(true));
+ } else if
(AggregationFunctionType.getNormalizedAggregationFunctionName(functionName)
+ .equals(AggregationFunctionType.ARRAYAGG.name())) {
+ // ARRAY_AGG(expression, 'dataType'[, isDistinct]) takes DISTINCT as
its isDistinct argument
+ RexNode isDistinct =
input.getCluster().getRexBuilder().makeLiteral(true);
+ if (rexList.size() == 3) {
+ rexList.set(2, isDistinct);
+ } else {
+ rexList.add(isDistinct);
+ }
}
}
SqlReturnTypeInference returnTypeInference = null;
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateTypeArgumentRewriteRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateTypeArgumentRewriteRule.java
index edbc553d07f..324d10fb5c9 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateTypeArgumentRewriteRule.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotAggregateTypeArgumentRewriteRule.java
@@ -31,9 +31,7 @@ import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.core.Project;
-import org.apache.calcite.rel.hint.RelHint;
import org.apache.calcite.rel.logical.LogicalAggregate;
-import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexLiteral;
@@ -48,16 +46,17 @@ import org.apache.pinot.segment.spi.AggregationFunctionType;
/// Supplies the explicit data type argument from the type of the data
argument:
/// - FIRST_WITH_TIME(dataColumn, timeColumn) -> FIRST_WITH_TIME(dataColumn,
timeColumn, 'dataType')
/// - LAST_WITH_TIME(dataColumn, timeColumn) -> LAST_WITH_TIME(dataColumn,
timeColumn, 'dataType')
+/// - ARRAY_AGG(expression[, isDistinct]) -> ARRAY_AGG(expression,
'dataType'[, isDistinct])
/// - MODE(stringColumn) -> MODE(stringColumn, 'MIN', 'STRING')
/// - ANY_VALUE(expr) -> ANY_VALUE(expr, 'dataType'), except by
[#WITHOUT_ANY_VALUE]
///
/// The result type of the call is unchanged. The literals are projected
directly beneath the aggregate, appended to
/// the existing project when there is one, because
[PinotAggregateExchangeNodeInsertRule] and `AggregatePushdownRule`
-/// inline literal arguments only from the immediate project.
+/// inline literal arguments from the immediate project or the inputs of a
union.
///
-/// The servers require the data type argument of FIRST_WITH_TIME /
LAST_WITH_TIME, so the rule is required rather than
-/// an optimization: `QueryEnvironment` always applies [#WITHOUT_ANY_VALUE],
or [#INSTANCE] when the `inferAnyValueType`
-/// query option is true, and `skipPlannerRules` cannot turn it off.
+/// The servers require type arguments for FIRST_WITH_TIME, LAST_WITH_TIME and
ARRAY_AGG. `QueryEnvironment` always
+/// applies [#WITHOUT_ANY_VALUE], or [#INSTANCE] when `inferAnyValueType` is
true, regardless of `skipPlannerRules`.
+
public class PinotAggregateTypeArgumentRewriteRule extends RelOptRule {
private static final Set<ColumnDataType> ANY_VALUE_DATA_TYPES =
EnumSet.of(ColumnDataType.INT, ColumnDataType.LONG,
ColumnDataType.FLOAT, ColumnDataType.DOUBLE,
@@ -65,20 +64,27 @@ public class PinotAggregateTypeArgumentRewriteRule extends
RelOptRule {
ColumnDataType.BYTES, ColumnDataType.UUID);
public static final PinotAggregateTypeArgumentRewriteRule INSTANCE =
- new PinotAggregateTypeArgumentRewriteRule(true,
PinotRuleUtils.PINOT_REL_FACTORY, null);
+ new PinotAggregateTypeArgumentRewriteRule(true, false,
PinotRuleUtils.PINOT_REL_FACTORY, null);
/// Leaves ANY_VALUE without the data type argument.
public static final PinotAggregateTypeArgumentRewriteRule WITHOUT_ANY_VALUE =
- new PinotAggregateTypeArgumentRewriteRule(false,
PinotRuleUtils.PINOT_REL_FACTORY,
+ new PinotAggregateTypeArgumentRewriteRule(false, false,
PinotRuleUtils.PINOT_REL_FACTORY,
"PinotAggregateTypeArgumentRewriteRuleWithoutAnyValue");
+ /// Runs before CASE-to-FILTER without changing other aggregation functions.
+ public static final PinotAggregateTypeArgumentRewriteRule ARRAY_AGG_ONLY =
+ new PinotAggregateTypeArgumentRewriteRule(false, true,
PinotRuleUtils.PINOT_REL_FACTORY,
+ "PinotAggregateTypeArgumentRewriteRuleArrayAggOnly");
+
+ private final boolean _arrayAggOnly;
private final boolean _rewriteAnyValue;
// Matches with RelOptRule operands, like the other aggregate rewrite rules,
instead of a RelRule config.
@SuppressWarnings("deprecation")
- private PinotAggregateTypeArgumentRewriteRule(boolean rewriteAnyValue,
RelBuilderFactory factory,
- @Nullable String description) {
+ private PinotAggregateTypeArgumentRewriteRule(boolean rewriteAnyValue,
boolean arrayAggOnly,
+ RelBuilderFactory factory, @Nullable String description) {
super(operand(LogicalAggregate.class, any()), factory, description);
_rewriteAnyValue = rewriteAnyValue;
+ _arrayAggOnly = arrayAggOnly;
}
@Override
@@ -93,26 +99,9 @@ public class PinotAggregateTypeArgumentRewriteRule extends
RelOptRule {
public void onMatch(RelOptRuleCall call) {
Aggregate aggRel = call.rel(0);
RelNode input = aggRel.getInput();
- RelNode unboxedInput = PinotRuleUtils.unboxRel(input);
RexBuilder rexBuilder = aggRel.getCluster().getRexBuilder();
-
- List<RexNode> projects;
- RelNode projectInput;
- List<RelHint> hints;
- if (unboxedInput instanceof Project) {
- Project project = (Project) unboxedInput;
- projects = new ArrayList<>(project.getProjects());
- projectInput = project.getInput();
- hints = project.getHints();
- } else {
- int numFields = input.getRowType().getFieldCount();
- projects = new ArrayList<>(numFields + 1);
- for (int i = 0; i < numFields; i++) {
- projects.add(rexBuilder.makeInputRef(input, i));
- }
- projectInput = input;
- hints = List.of();
- }
+ int numInputFields = input.getRowType().getFieldCount();
+ List<RexNode> literals = new ArrayList<>();
// Appending keeps the existing field indexes, so the group set and the
other calls are unaffected.
List<AggregateCall> orgAggCalls = aggRel.getAggCallList();
@@ -125,16 +114,18 @@ public class PinotAggregateTypeArgumentRewriteRule
extends RelOptRule {
continue;
}
List<Integer> argList = new ArrayList<>(aggCall.getArgList());
+ int argumentPosition = isArrayAgg(aggCall) ? 1 : argList.size();
for (String literalArgument : literalArguments) {
- argList.add(literalIndexes.computeIfAbsent(literalArgument, literal ->
{
- projects.add(rexBuilder.makeLiteral(literal));
- return projects.size() - 1;
- }));
+ int literalIndex = literalIndexes.computeIfAbsent(literalArgument,
literal -> {
+ literals.add(rexBuilder.makeLiteral(literal));
+ return numInputFields + literals.size() - 1;
+ });
+ argList.add(argumentPosition++, literalIndex);
}
// Keeps the original result type, so the rewrite does not change the
result schema.
aggCalls.add(aggCall.withArgList(argList));
}
- RelNode newInput = LogicalProject.create(projectInput, hints, projects,
(List<String>) null, Set.of());
+ RelNode newInput = PinotRuleUtils.appendAggregateLiterals(input, literals);
call.transformTo(
aggRel.copy(aggRel.getTraitSet(), newInput, aggRel.getGroupSet(),
aggRel.getGroupSets(), aggCalls));
}
@@ -144,6 +135,23 @@ public class PinotAggregateTypeArgumentRewriteRule extends
RelOptRule {
private List<String> getLiteralArguments(AggregateCall aggCall, RelNode
input) {
List<Integer> argList = aggCall.getArgList();
String name =
AggregationFunctionType.getNormalizedAggregationFunctionName(aggCall.getAggregation().getName());
+ if (isArrayAgg(aggCall)) {
+ if (argList.size() != 1 && (argList.size() != 2
+ || getSqlTypeName(input, argList.get(1)) != SqlTypeName.BOOLEAN)) {
+ return null;
+ }
+ RelDataType dataType =
input.getRowType().getFieldList().get(argList.get(0)).getType();
+ if (dataType.getComponentType() != null) {
+ dataType = dataType.getComponentType();
+ }
+ if (dataType.getSqlTypeName() == SqlTypeName.DECIMAL) {
+ return
List.of(RelToPlanNodeConverter.convertToColumnDataType(aggCall.getType()).toDataType().name());
+ }
+ return List.of(getDataTypeArgument(name, dataType.getSqlTypeName()));
+ }
+ if (_arrayAggOnly) {
+ return null;
+ }
if (name.equals(AggregationFunctionType.MODE.name())) {
// BOOLEAN and TIMESTAMP already return their own type, cast from the
DOUBLE result.
return argList.size() == 1 &&
SqlTypeName.CHAR_TYPES.contains(getSqlTypeName(input, argList.get(0)))
@@ -202,7 +210,9 @@ public class PinotAggregateTypeArgumentRewriteRule extends
RelOptRule {
return "UUID";
default:
throw new IllegalArgumentException(
- String.format("Unsupported data type for %s(dataColumn,
timeColumn): %s", name, dataType));
+ String.format("Unsupported data type for %s%s: %s", name,
+ name.equals(AggregationFunctionType.ARRAYAGG.name()) ?
"(expression)" : "(dataColumn, timeColumn)",
+ dataType));
}
}
@@ -236,4 +246,9 @@ public class PinotAggregateTypeArgumentRewriteRule extends
RelOptRule {
relDataType.getSqlTypeName(), explicitDataType != null ?
explicitDataType : dataTypeArgument));
}
}
+
+ private static boolean isArrayAgg(AggregateCall aggCall) {
+ return
AggregationFunctionType.getNormalizedAggregationFunctionName(aggCall.getAggregation().getName())
+ .equals(AggregationFunctionType.ARRAYAGG.name());
+ }
}
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotApproximateAggregateRewriteRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotApproximateAggregateRewriteRule.java
index 21a23a5dc76..f0db5ca3b65 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotApproximateAggregateRewriteRule.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotApproximateAggregateRewriteRule.java
@@ -29,7 +29,6 @@ import org.apache.calcite.rel.RelNode;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
import org.apache.calcite.rel.logical.LogicalAggregate;
-import org.apache.calcite.rel.logical.LogicalProject;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.sql.SqlAggFunction;
@@ -101,29 +100,22 @@ public class PinotApproximateAggregateRewriteRule extends
RelOptRule {
// indices of every call stay valid. With no parameters configured the
plan keeps the shape it had before, with no
// extra project.
//
- // Invariant: this project has to stay directly beneath the aggregate.
Both PinotAggregateExchangeNodeInsertRule
- // and AggregatePushdownRule inline the literal only through
findImmediateProjects(input), so anything that puts a
- // node between them would leave the leaf with a column reference where
the function expects a parameter string.
- // Nothing in the later phases does: AggregateProjectMergeRule needs an
all-input-ref project and the literals
- // block it, and ProjectMergeRule collapses into a single project that
stays adjacent.
+ // Keep literal arguments in the immediate project visible to the
aggregate split rules.
RelNode newInput = input;
int distinctCountParamsIndex = -1;
int percentileParamsIndex = -1;
if (!distinctCountParams.isEmpty() || !percentileParams.isEmpty()) {
RexBuilder rexBuilder = aggRel.getCluster().getRexBuilder();
- List<RexNode> projects = new ArrayList<>(numInputFields + 2);
- for (int i = 0; i < numInputFields; i++) {
- projects.add(rexBuilder.makeInputRef(input, i));
- }
+ List<RexNode> literals = new ArrayList<>(2);
if (!distinctCountParams.isEmpty()) {
- distinctCountParamsIndex = projects.size();
- projects.add(rexBuilder.makeLiteral(distinctCountParams));
+ distinctCountParamsIndex = numInputFields + literals.size();
+ literals.add(rexBuilder.makeLiteral(distinctCountParams));
}
if (!percentileParams.isEmpty()) {
- percentileParamsIndex = projects.size();
- projects.add(rexBuilder.makeLiteral(percentileParams));
+ percentileParamsIndex = numInputFields + literals.size();
+ literals.add(rexBuilder.makeLiteral(percentileParams));
}
- newInput = LogicalProject.create(input, List.of(), projects,
(List<String>) null);
+ newInput = PinotRuleUtils.appendAggregateLiterals(input, literals);
}
List<AggregateCall> rewrittenAggCalls = new
ArrayList<>(aggRel.getAggCallList().size());
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotRuleUtils.java
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotRuleUtils.java
index 6bbb888ebab..b71130b9c72 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotRuleUtils.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/calcite/rel/rules/PinotRuleUtils.java
@@ -23,6 +23,7 @@ import com.google.common.collect.ImmutableList;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.List;
+import java.util.Set;
import javax.annotation.Nullable;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.RelTraitSet;
@@ -38,6 +39,8 @@ import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Window;
import org.apache.calcite.rel.logical.LogicalAsofJoin;
import org.apache.calcite.rel.logical.LogicalJoin;
+import org.apache.calcite.rel.logical.LogicalProject;
+import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
@@ -85,6 +88,24 @@ public class PinotRuleUtils {
}
}
+ /// Appends literal arguments to the immediate project, preserving existing
column references.
+ public static RelNode appendAggregateLiterals(RelNode input, List<RexNode>
literals) {
+ RelNode rel = unboxRel(input);
+ if (rel instanceof Project) {
+ Project project = (Project) rel;
+ List<RexNode> projects = new ArrayList<>(project.getProjects());
+ projects.addAll(literals);
+ return LogicalProject.create(project.getInput(), project.getHints(),
projects, (List<String>) null, Set.of());
+ }
+ RexBuilder rexBuilder = input.getCluster().getRexBuilder();
+ List<RexNode> projects = new
ArrayList<>(input.getRowType().getFieldCount() + literals.size());
+ for (int i = 0; i < input.getRowType().getFieldCount(); i++) {
+ projects.add(rexBuilder.makeInputRef(input, i));
+ }
+ projects.addAll(literals);
+ return LogicalProject.create(input, List.of(), projects, (List<String>)
null, Set.of());
+ }
+
public static boolean isExchange(RelNode rel) {
return unboxRel(rel) instanceof Exchange;
}
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/query/QueryEnvironment.java
b/pinot-query-planner/src/main/java/org/apache/pinot/query/QueryEnvironment.java
index 8b50aea94f6..95e9b4f102e 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/query/QueryEnvironment.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/query/QueryEnvironment.java
@@ -610,10 +610,14 @@ public class QueryEnvironment {
// Run the Calcite CORE rules using 1 HepInstruction per rule. We use 1
HepInstruction per rule for simplicity:
// the rules used here can rest assured that they are the only ones
evaluated in a dedicated graph-traversal.
for (RelOptRule relOptRule : basicRules) {
+ // Supplies the data type argument before AggregateCaseToFilter, which
rewrites only one-argument calls: its
+ // ARRAY_AGG(expression) FILTER (WHERE condition) form of ARRAY_AGG(CASE
WHEN ...) fails at runtime.
+ if
(relOptRule.toString().equals(CommonConstants.Broker.PlannerRuleNames.AGGREGATE_CASE_TO_FILTER))
{
+
hepProgramBuilder.addRuleInstance(PinotAggregateTypeArgumentRewriteRule.ARRAY_AGG_ONLY);
+ }
hepProgramBuilder.addRuleInstance(relOptRule);
}
- // Required for FIRST_WITH_TIME / LAST_WITH_TIME without a data type and
MODE on strings, so the rule options
- // do not filter it.
+ // Required for FIRST_WITH_TIME, LAST_WITH_TIME, ARRAY_AGG and string
MODE, so the rule options do not filter it.
hepProgramBuilder.addRuleInstance(inferAnyValueType ?
PinotAggregateTypeArgumentRewriteRule.INSTANCE
: PinotAggregateTypeArgumentRewriteRule.WITHOUT_ANY_VALUE);
diff --git
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/physical/v2/opt/rules/AggregatePushdownRule.java
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/physical/v2/opt/rules/AggregatePushdownRule.java
index ca30c851a01..a40402cd4e8 100644
---
a/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/physical/v2/opt/rules/AggregatePushdownRule.java
+++
b/pinot-query-planner/src/main/java/org/apache/pinot/query/planner/physical/v2/opt/rules/AggregatePushdownRule.java
@@ -391,6 +391,15 @@ public class AggregatePushdownRule extends PRelOptRule {
functionCategory = SqlFunctionCategory.USER_DEFINED_FUNCTION;
} else if (kind == SqlKind.LISTAGG) {
rexList.add(input.getCluster().getRexBuilder().makeLiteral(true));
+ } else if
(AggregationFunctionType.getNormalizedAggregationFunctionName(functionName)
+ .equals(AggregationFunctionType.ARRAYAGG.name())) {
+ // ARRAY_AGG(expression, 'dataType'[, isDistinct]) takes DISTINCT as
its isDistinct argument
+ RexNode isDistinct =
input.getCluster().getRexBuilder().makeLiteral(true);
+ if (rexList.size() == 3) {
+ rexList.set(2, isDistinct);
+ } else {
+ rexList.add(isDistinct);
+ }
}
}
SqlReturnTypeInference returnTypeInference = null;
diff --git
a/pinot-query-planner/src/test/java/org/apache/pinot/query/ApproximateAggregateRewriteTest.java
b/pinot-query-planner/src/test/java/org/apache/pinot/query/ApproximateAggregateRewriteTest.java
index a220377a1a9..2dbaa1a864d 100644
---
a/pinot-query-planner/src/test/java/org/apache/pinot/query/ApproximateAggregateRewriteTest.java
+++
b/pinot-query-planner/src/test/java/org/apache/pinot/query/ApproximateAggregateRewriteTest.java
@@ -25,6 +25,7 @@ import org.apache.pinot.core.routing.RoutingManager;
import org.apache.pinot.query.routing.WorkerManager;
import org.apache.pinot.spi.data.Schema;
import org.apache.pinot.spi.utils.CommonConstants;
+import org.apache.pinot.sql.parsers.CalciteSqlParser;
import org.testng.annotations.Test;
import static org.testng.Assert.assertEquals;
@@ -110,6 +111,37 @@ public class ApproximateAggregateRewriteTest extends
QueryEnvironmentTestBase {
assertTrue(subquery.contains("threshold=23"), subquery);
}
+ /// The parameters are appended to the project beneath the aggregate, so the
literals already in it, here the data
+ /// type argument of FIRST_WITH_TIME, are still inlined when ProjectMerge is
skipped.
+ @Test
+ public void testKeepsOtherLiteralsInlinedWithoutProjectMerge() {
+ for (boolean usePhysicalOptimizer : List.of(false, true)) {
+ QueryEnvironment env = newQueryEnvironment(true, "threshold=17",
"threshold=23", usePhysicalOptimizer);
+ for (String input : List.of("a", "(SELECT * FROM a UNION ALL SELECT *
FROM b UNION ALL SELECT * FROM c)")) {
+ String query = "SET skipPlannerRules='ProjectMerge'; EXPLAIN PLAN FOR
SELECT col2, "
+ + "FIRST_WITH_TIME(col3, ts), DISTINCTCOUNT(col1) FROM " + input +
" GROUP BY col2";
+ String plan = env.compile(query,
CalciteSqlParser.compileToSqlNodeAndOptions(query))
+ .explain(RANDOM_REQUEST_ID_GEN.nextLong(), null).getExplainPlan();
+ String leafAggregate = plan.lines().filter(line ->
line.contains("aggType=[LEAF]")).findFirst().orElse("");
+ for (String literal : List.of("_UTF-8'INT'", "_UTF-8'threshold=17'")) {
+ assertTrue(leafAggregate.contains(literal), "Expected " + literal +
" in the leaf aggregate of:\n" + plan);
+ }
+ }
+ }
+ }
+
+ @Test
+ public void testApproximateParamsKeepBranchSpecificUnionColumns() {
+ QueryEnvironment env = newQueryEnvironment(true, "threshold=17",
"threshold=23");
+ String query = "EXPLAIN PLAN FOR SELECT DISTINCTCOUNT(x), COVARPOP(x, y)
FROM "
+ + "(SELECT col3 x, 1.0 y FROM a UNION ALL SELECT col3, CAST(col7 AS
DOUBLE) FROM b)";
+ String plan = env.compile(query,
CalciteSqlParser.compileToSqlNodeAndOptions(query))
+ .explain(RANDOM_REQUEST_ID_GEN.nextLong(), null).getExplainPlan();
+ String leaf = plan.lines().filter(line ->
line.contains("aggType=[LEAF]")).findFirst().orElse("");
+ assertTrue(leaf.contains("_UTF-8'threshold=17'"), plan);
+ assertFalse(leaf.contains("1.0"), plan);
+ }
+
/// `AggregateCaseToFilterRule` turns `COUNT(DISTINCT CASE WHEN c THEN x
END)` into `COUNT(DISTINCT x) FILTER (WHERE
/// c)`, so the rows that fail `c` add nothing to the count. It only matches
a single-argument call, so the rewrite
/// has to run after it: with the parameters appended first, the CASE stays
and its NULL counts as one more value.
diff --git a/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
b/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
index d025fa29406..9e470212e4f 100644
--- a/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
+++ b/pinot-query-planner/src/test/resources/queries/AggregatePlans.json
@@ -295,6 +295,111 @@
"\n"
]
},
+ {
+ "description": "ARRAY_AGG without a data type infers it from the
expression as the second argument",
+ "sql": "EXPLAIN PLAN FOR SELECT ARRAY_AGG(a.col1), ARRAY_AGG(a.col3,
true), ARRAY_AGG(a.col4), ARRAY_AGG(a.ts_timestamp, true), ARRAY_AGG(a.col5),
ARRAY_AGG(a.col7 + 1) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'STRING')], agg#1=[ARRAYAGG($1, _UTF-8'INT', true)], agg#2=[ARRAYAGG($2,
_UTF-8'DOUBLE')], agg#3=[ARRAYAGG($3, _UTF-8'TIMESTAMP', true)],
agg#4=[ARRAYAGG($4, _UTF-8'BOOLEAN')], agg#5=[ARRAYAGG($5, _UTF-8'LONG')],
aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'STRING')], agg#1=[ARRAYAGG($1, _UTF-8'INT', true)], agg#2=[ARRAYAGG($3,
_UTF-8'DOUBLE')], agg#3=[ARRAYAGG($4, _UTF-8'TIMESTAMP', true)],
agg#4=[ARRAYAGG($5, _UTF-8'BOOLEAN')], agg#5=[ARRAYAGG($6, _UTF-8'LONG')],
aggType=[LEAF])",
+ "\n LogicalProject($f0=[$0], $f1=[$2], $f2=[true], $f3=[$3],
$f4=[$8], $f5=[$4], $f6=[+($6, 1)], $f7=[_UTF-8'STRING'], $f8=[_UTF-8'INT'],
$f9=[_UTF-8'DOUBLE'], $f10=[_UTF-8'TIMESTAMP'], $f11=[_UTF-8'BOOLEAN'],
$f12=[_UTF-8'LONG'])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG without a data type next to the explicit
forms in a grouped aggregate",
+ "sql": "EXPLAIN PLAN FOR SELECT a.col2, ARRAY_AGG(a.col3, true),
ARRAY_AGG(a.col3, 'LONG', true), ARRAY_AGG(a.col1, 'STRING') FROM a GROUP BY
a.col2",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{0}], agg#0=[ARRAYAGG($1,
_UTF-8'INT', true)], agg#1=[ARRAYAGG($2, _UTF-8'LONG', true)],
agg#2=[ARRAYAGG($3, _UTF-8'STRING')], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash[0]])",
+ "\n PinotLogicalAggregate(group=[{0}], agg#0=[ARRAYAGG($1,
_UTF-8'INT', true)], agg#1=[ARRAYAGG($1, _UTF-8'LONG', true)],
agg#2=[ARRAYAGG($4, _UTF-8'STRING')], aggType=[LEAF])",
+ "\n LogicalProject($f0=[$1], $f1=[$2], $f2=[true],
$f3=[_UTF-8'LONG'], $f4=[$0], $f5=[_UTF-8'STRING'], $f6=[_UTF-8'INT'])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG without a data type uses the element type of
a multi-value column",
+ "sql": "EXPLAIN PLAN FOR SELECT ARRAY_AGG(e.mcol1), ARRAY_AGG(e.mcol2,
true) FROM e",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'STRING')], agg#1=[ARRAYAGG($1, _UTF-8'LONG', true)], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'STRING')], agg#1=[ARRAYAGG($1, _UTF-8'LONG', true)], aggType=[LEAF])",
+ "\n LogicalProject($f0=[$7], $f1=[$8], $f2=[true],
$f3=[_UTF-8'STRING'], $f4=[_UTF-8'LONG'])",
+ "\n PinotLogicalTableScan(table=[[default, e]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG without a data type follows the planner's
array type for a DECIMAL",
+ "sql": "EXPLAIN PLAN FOR SELECT ARRAY_AGG(CAST(a.col3 AS DECIMAL(5,
0))), ARRAY_AGG(CAST(a.col7 AS DECIMAL(20, 0)), true), ARRAY_AGG(CAST(a.col7 AS
DECIMAL(39, 0))) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'INT')], agg#1=[ARRAYAGG($1, _UTF-8'LONG', true)], agg#2=[ARRAYAGG($2,
_UTF-8'DOUBLE')], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'INT')], agg#1=[ARRAYAGG($1, _UTF-8'LONG', true)], agg#2=[ARRAYAGG($3,
_UTF-8'DOUBLE')], aggType=[LEAF])",
+ "\n LogicalProject($f0=[CAST($2):DECIMAL(5, 0) NOT NULL],
$f1=[CAST($6):DECIMAL(20, 0) NOT NULL], $f2=[true], $f3=[CAST($6):DECIMAL(39,
0) NOT NULL], $f4=[_UTF-8'INT'], $f5=[_UTF-8'LONG'], $f6=[_UTF-8'DOUBLE'])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG(DISTINCT ...) passes DISTINCT as a true
isDistinct argument, with or without a data type",
+ "sql": "EXPLAIN PLAN FOR SELECT a.col2, ARRAY_AGG(DISTINCT a.col3),
ARRAY_AGG(DISTINCT a.col3, false), ARRAY_AGG(DISTINCT a.col1, 'STRING') FROM a
GROUP BY a.col2",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{0}], agg#0=[ARRAYAGG($1,
_UTF-8'INT', true)], agg#1=[ARRAYAGG($2, _UTF-8'INT', true)],
agg#2=[ARRAYAGG($3, _UTF-8'STRING', true)], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash[0]])",
+ "\n PinotLogicalAggregate(group=[{0}], agg#0=[ARRAYAGG($1,
_UTF-8'INT', true)], agg#1=[ARRAYAGG($1, _UTF-8'INT', true)],
agg#2=[ARRAYAGG($3, _UTF-8'STRING', true)], aggType=[LEAF])",
+ "\n LogicalProject($f0=[$1], $f1=[$2], $f2=[false], $f3=[$0],
$f4=[_UTF-8'STRING'], $f5=[_UTF-8'INT'])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG(CASE ...) without a data type keeps the CASE
expression instead of a FILTER",
+ "sql": "EXPLAIN PLAN FOR SELECT ARRAY_AGG(CASE WHEN a.col3 > 1 THEN
a.col3 END) FROM a",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'INT')], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[ARRAYAGG($0,
_UTF-8'INT')], aggType=[LEAF])",
+ "\n LogicalProject($f0=[CASE(>($2, 1), $2, null:INTEGER)],
$f1=[_UTF-8'INT'])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "Inferred types above nested unions keep one union and
one literal project",
+ "sql": "EXPLAIN PLAN FOR SELECT FIRST_WITH_TIME(col3, ts),
ARRAY_AGG(col3) FROM (SELECT col3, ts FROM a UNION ALL SELECT col3, ts FROM b
UNION ALL SELECT col3, ts FROM c)",
+ "output": [
+ "Execution Plan",
+ "\nPinotLogicalAggregate(group=[{}], agg#0=[FIRSTWITHTIME($0, $0,
_UTF-8'INT')], agg#1=[ARRAYAGG($1, _UTF-8'INT')], aggType=[FINAL])",
+ "\n PinotLogicalExchange(distribution=[hash])",
+ "\n PinotLogicalAggregate(group=[{}], agg#0=[FIRSTWITHTIME($0,
$1, _UTF-8'INT')], agg#1=[ARRAYAGG($0, _UTF-8'INT')], aggType=[LEAF])",
+ "\n LogicalProject($f0=[$0], $f1=[$1], $f2=[_UTF-8'INT'],
$f3=[_UTF-8'INT'])",
+ "\n LogicalUnion(all=[true])",
+ "\n PinotLogicalExchange(distribution=[single])",
+ "\n LogicalProject(col3=[$2], ts=[$7])",
+ "\n PinotLogicalTableScan(table=[[default, a]])",
+ "\n PinotLogicalExchange(distribution=[single])",
+ "\n LogicalProject(col3=[$2], ts=[$7])",
+ "\n PinotLogicalTableScan(table=[[default, b]])",
+ "\n PinotLogicalExchange(distribution=[single])",
+ "\n LogicalProject(col3=[$2], ts=[$7])",
+ "\n PinotLogicalTableScan(table=[[default, c]])",
+ "\n"
+ ]
+ },
+ {
+ "description": "ARRAY_AGG without a data type rejects an unsupported
expression type",
+ "sql": "EXPLAIN PLAN FOR SELECT ARRAY_AGG(CAST(a.col1 AS DATE), true)
FROM a",
+ "expectedException": ".*Unsupported data type for .*\\(expression\\):
DATE.*"
+ },
{
"description": "MIN on STRING column rewritten to MINSTRING",
"sql": "SET autoRewriteAggregationType=true; EXPLAIN PLAN FOR SELECT
MIN(a.col1) FROM a",
diff --git a/pinot-query-runtime/src/test/resources/queries/UDFAggregates.json
b/pinot-query-runtime/src/test/resources/queries/UDFAggregates.json
index 68813c3d637..c76c7b4e17c 100644
--- a/pinot-query-runtime/src/test/resources/queries/UDFAggregates.json
+++ b/pinot-query-runtime/src/test/resources/queries/UDFAggregates.json
@@ -45,6 +45,14 @@
}
},
"queries": [
+ {"sql": "SELECT FIRSTWITHTIME(x, c) FROM (SELECT int_col x, 5 c FROM
{tbl} WHERE int_col = 2 UNION ALL SELECT int_col, 5 FROM {tbl} WHERE int_col =
2)", "outputs": [[2]]},
+ {"sql": "SELECT COVARPOP(x, c), MODE(s) FROM (SELECT int_col x, 50 c,
string_col s FROM {tbl} UNION ALL SELECT int_col, 50, string_col FROM {tbl})",
"outputs": [[0, "c"]]},
+ {"sql": "SELECT CARDINALITY(ARRAY_AGG(x)), DISTINCTCOUNT(x), COVARPOP(x,
y) FROM (SELECT int_col x, 1.0 y FROM {tbl} UNION ALL SELECT int_col,
CAST(long_col AS DOUBLE) FROM {tbl})", "outputs": [[14, 6, 2339.918367346939]]},
+ {"sql": "SET enableNullHandling=false; SELECT MODE(CASE WHEN int_col > 3
THEN string_col END) FROM {tbl}", "outputs": [["c"]]},
+ {"sql": "SET enableNullHandling=false; SELECT /*+
aggOptions(is_skip_leaf_stage_group_by='true') */ bool_col, MODE(CASE WHEN
int_col > 3 THEN string_col END) FROM {tbl} GROUP BY bool_col", "outputs":
[[true, "c"], [false, "c"]]},
+ {"sql": "SET inferAnyValueType=true; SELECT src, ANY_VALUE(x) FROM
(SELECT 'a' src, 1 x FROM {tbl} UNION ALL SELECT 'b', 2 FROM {tbl}) GROUP BY
src", "outputs": [["a", 1], ["b", 2]]},
+ {"sql": "SELECT LASTWITHTIME(x, t) FROM (SELECT int_col x,
CAST('2019-01-01 00:00:00' AS TIMESTAMP) t FROM {tbl} UNION ALL SELECT int_col,
timestamp_col FROM {tbl})", "outputs": [[101]]},
+ {"sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
src, k, MODE(s) FROM (SELECT 'a' src, int_col k, string_col s FROM {tbl} UNION
ALL SELECT 'b', int_col, string_col FROM {tbl}) WHERE k = 2 GROUP BY src, k",
"outputs": [["a", 2, "a"], ["b", 2, "a"]]},
{
"sql": "SELECT FIRSTWITHTIME(int_col, timestamp_col, 'INT'),
FIRSTWITHTIME(string_col, timestamp_col, 'STRING'), first_with_time(bool_col,
CAST(long_col AS TIMESTAMP), 'BOOLEAN') FROM {tbl}",
"outputs": [[2, "a", true]]
@@ -54,7 +62,7 @@
"outputs": [[false, 100, 100, 3], [true, 300, 300, 2]]
},
{
- "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_aggregate='true') */
string_col, FIRSTWITHTIME(float_col, timestamp_col, 'FLOAT'),
FIRSTWITHTIME(double_col, timestamp_col, 'DOUBLE'), first_with_time(long_col,
CAST(long_col AS TIMESTAMP), 'LONG') FROM {tbl} GROUP BY string_col",
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, FIRSTWITHTIME(float_col, timestamp_col, 'FLOAT'),
FIRSTWITHTIME(double_col, timestamp_col, 'DOUBLE'), first_with_time(long_col,
CAST(long_col AS TIMESTAMP), 'LONG') FROM {tbl} GROUP BY string_col",
"outputs": [["a", 300, 300, 2], ["b", 100, 100, 3], ["c", 1.75, 1.75,
101]]
},
{
@@ -66,7 +74,7 @@
"outputs": [[false, 1.01, 1.01, 150], [true, 1.75, 1.75, 175]]
},
{
- "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_aggregate='true') */
string_col, LASTWITHTIME(float_col, timestamp_col, 'FLOAT'),
LASTWITHTIME(double_col, timestamp_col, 'DOUBLE'), last_with_time(long_col,
CAST(long_col AS TIMESTAMP), 'LONG') FROM {tbl} GROUP BY string_col",
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, LASTWITHTIME(float_col, timestamp_col, 'FLOAT'),
LASTWITHTIME(double_col, timestamp_col, 'DOUBLE'), last_with_time(long_col,
CAST(long_col AS TIMESTAMP), 'LONG') FROM {tbl} GROUP BY string_col",
"outputs": [["a", 400, 400, 2], ["b", 1, 1, 100], ["c", 1.01, 1.01,
175]]
},
{
@@ -79,7 +87,7 @@
"outputs": [[false, 100, 100, 3], [true, 300, 300, 2]]
},
{
- "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_aggregate='true') */
string_col, FIRSTWITHTIME(float_col, timestamp_col), FIRSTWITHTIME(double_col,
timestamp_col), first_with_time(long_col, CAST(long_col AS TIMESTAMP)) FROM
{tbl} GROUP BY string_col",
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, FIRSTWITHTIME(float_col, timestamp_col), FIRSTWITHTIME(double_col,
timestamp_col), first_with_time(long_col, CAST(long_col AS TIMESTAMP)) FROM
{tbl} GROUP BY string_col",
"outputs": [["a", 300, 300, 2], ["b", 100, 100, 3], ["c", 1.75, 1.75,
101]]
},
{
@@ -91,7 +99,7 @@
"outputs": [[false, 1.01, 1.01, 150], [true, 1.75, 1.75, 175]]
},
{
- "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_aggregate='true') */
string_col, LASTWITHTIME(float_col, timestamp_col), LASTWITHTIME(double_col,
timestamp_col), last_with_time(long_col, CAST(long_col AS TIMESTAMP)) FROM
{tbl} GROUP BY string_col",
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, LASTWITHTIME(float_col, timestamp_col), LASTWITHTIME(double_col,
timestamp_col), last_with_time(long_col, CAST(long_col AS TIMESTAMP)) FROM
{tbl} GROUP BY string_col",
"outputs": [["a", 400, 400, 2], ["b", 1, 1, 100], ["c", 1.01, 1.01,
175]]
},
{
@@ -124,7 +132,7 @@
"outputs": [["a", "1E-307", "1E+307", "deadbeef", "fdeadbee"], ["b",
"1E-308", "1E+308", "efdeadbe", "beefdead"], ["c", "1E+328", "1E-317",
"deadbe02", "adbeefde"]]
},
{
- "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_aggregate='true') */
string_col, FIRST_WITH_TIME(decimal_col, timestamp_col),
LAST_WITH_TIME(decimal_col, timestamp_col), FIRST_WITH_TIME(bytes_col,
timestamp_col), LAST_WITH_TIME(bytes_col, timestamp_col) FROM {tbl} GROUP BY
string_col",
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, FIRST_WITH_TIME(decimal_col, timestamp_col),
LAST_WITH_TIME(decimal_col, timestamp_col), FIRST_WITH_TIME(bytes_col,
timestamp_col), LAST_WITH_TIME(bytes_col, timestamp_col) FROM {tbl} GROUP BY
string_col",
"outputs": [["a", "1E-307", "1E+307", "deadbeef", "fdeadbee"], ["b",
"1E-308", "1E+308", "efdeadbe", "beefdead"], ["c", "1E+328", "1E-317",
"deadbe02", "adbeefde"]]
},
{
@@ -392,5 +400,82 @@
"outputs": [["a", 1, 1, 2, 2], ["b", 2, 2, 2, 2], ["c", 3, 3, 3, 3]]
}
]
+ },
+ "pinot_array_agg_tests": {
+ "tables": {
+ "tbl": {
+ "schema": [
+ {"name": "int_col", "type": "INT"},
+ {"name": "long_col", "type": "LONG"},
+ {"name": "double_col", "type": "DOUBLE"},
+ {"name": "decimal_col", "type": "BIG_DECIMAL"},
+ {"name": "string_col", "type": "STRING"},
+ {"name": "json_col", "type": "JSON"},
+ {"name": "int_mv_col", "type": "INT", "isSingleValue": false},
+ {"name": "string_mv_col", "type": "STRING", "isSingleValue": false}
+ ],
+ "inputs": [
+ [1, 10, 1.5, "1.5", "a", "{\"k\":1}", [1, 2], ["x", "y"]],
+ [2, 20, 2.5, "2.5", "a", "{\"k\":2}", [2, 3], ["y"]],
+ [2, 20, 2.5, "2.5", "b", "{\"k\":2}", [3], ["z", "x"]],
+ [3, 30, 3.5, "3.5", "b", "{\"k\":3}", [4, 5], ["z"]]
+ ]
+ }
+ },
+ "queries": [
+ {
+ "comment": "The data type argument of ARRAY_AGG is inferred when
omitted",
+ "sql": "SELECT arraySortInt(ARRAY_AGG(int_col)),
arraySortInt(ARRAY_AGG(int_col, true)), arraySortString(ARRAY_AGG(string_col,
true)), arraySortString(ARRAY_AGG(json_col)), CARDINALITY(ARRAY_AGG(long_col,
true)), CARDINALITY(ARRAY_AGG(double_col)), CARDINALITY(ARRAY_AGG(decimal_col,
true)) FROM {tbl}",
+ "outputs": [[[1, 2, 2, 3], [1, 2, 3], ["a", "b"], ["{\"k\":1}",
"{\"k\":2}", "{\"k\":2}", "{\"k\":3}"], 3, 4, 3]]
+ },
+ {
+ "sql": "SELECT string_col, arraySortInt(ARRAY_AGG(int_col + 1, true)),
arraySortInt(ARRAY_AGG(int_mv_col, true)),
arraySortString(ARRAY_AGG(string_mv_col)) FROM {tbl} GROUP BY string_col",
+ "outputs": [["a", [2, 3], [1, 2, 3], ["x", "y", "y"]], ["b", [3, 4],
[3, 4, 5], ["x", "z", "z"]]]
+ },
+ {
+ "sql": "SELECT /*+ aggOptions(is_skip_leaf_stage_group_by='true') */
string_col, arraySortInt(ARRAY_AGG(int_col + 1, true)),
CARDINALITY(ARRAY_AGG(long_col)) FROM {tbl} GROUP BY string_col",
+ "outputs": [["a", [2, 3], 2], ["b", [3, 4], 2]]
+ },
+ {
+ "sql": "SELECT arraySortInt(ARRAY_AGG(CAST(int_col AS DECIMAL(10,
0)))), CARDINALITY(ARRAY_AGG(CAST(long_col AS DECIMAL(20, 0)), true)) FROM
{tbl}",
+ "outputs": [[[1, 2, 2, 3], 3]]
+ },
+ {
+ "sql": "SELECT arraySortInt(ARRAY_AGG(DISTINCT int_col)),
CARDINALITY(ARRAY_AGG(DISTINCT string_col)), CARDINALITY(ARRAY_AGG(DISTINCT
int_col, false)), CARDINALITY(ARRAY_AGG(DISTINCT int_col, 'INT')) FROM {tbl}",
+ "outputs": [[[1, 2, 3], 2, 3, 3]]
+ },
+ {
+ "sql": "SELECT string_col, arraySortString(ARRAY_AGG(DISTINCT
string_mv_col)) FROM {tbl} GROUP BY string_col",
+ "outputs": [["a", ["x", "y"]], ["b", ["x", "z"]]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(DISTINCT x, 'INT', true)),
CARDINALITY(ARRAY_AGG(DISTINCT x, 'INT')) FROM (SELECT int_col x FROM {tbl}
UNION ALL SELECT int_col FROM {tbl})",
+ "outputs": [[3, 3]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(x)), FIRSTWITHTIME(x, CAST(t AS
TIMESTAMP)) FROM (SELECT int_col x, long_col t FROM {tbl} UNION ALL SELECT
int_col, long_col FROM {tbl} UNION ALL SELECT int_col, long_col FROM {tbl})",
+ "outputs": [[12, 1]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(x)), FIRSTWITHTIME(x, CAST(t AS
TIMESTAMP)) FROM (SELECT int_col x, long_col t FROM {tbl} INTERSECT ALL SELECT
int_col, long_col FROM {tbl})",
+ "outputs": [[4, 1]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(x)) FROM (SELECT int_col x FROM
{tbl} EXCEPT ALL SELECT int_col FROM {tbl} WHERE int_col > 2)",
+ "outputs": [[3]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(long_col / ((int_col - 2) *
1.0))) FROM {tbl}",
+ "outputs": [[4]]
+ },
+ {
+ "sql": "SELECT CARDINALITY(ARRAY_AGG(CASE WHEN int_col > 1 THEN
int_col END)), CARDINALITY(ARRAY_AGG(CASE WHEN int_col > 1 THEN int_col END,
'INT')) FROM {tbl}",
+ "outputs": [[3, 3]]
+ },
+ {
+ "sql": "SELECT arraySortInt(ARRAY_AGG(int_col, 'INT')),
arraySortInt(ARRAY_AGG(int_col, 'INT', true)) FROM {tbl}",
+ "outputs": [[[1, 2, 2, 3], [1, 2, 3]]]
+ }
+ ]
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
