Group by multi-value columns without aggregation can not work

[MeihanLi]

[Pinot SQL] It seems that non-aggregation group by only works for single value columns. Grouping by multi-value columns throws an exception with error code 200. The exception message from Pinot Controller UI is redundant and it repeats the first message over 20 times. The detailed stack tree is not meaningful for us to understand what causes the issue.

Example tags_value (String array): restaurant_professional,restaurant_fast,restaurant_fresh,restaurant_tasty

Example query: select tags_value from myTable GROUP BY tags_value

Error message from Pinot Controller UI: { "errorCode": 200, "message": "QueryExecutionError:\njava.lang.UnsupportedOperationException\n\tat org.apache.pinot.segment.spi.index.reader.ForwardIndexReader.readDictIds(ForwardIndexReader.java:84)\n\tat org.apache.pinot.core.common.DataFetcher$ColumnValueReader.readDictIds(DataFetcher.java:278)\n\tat org.apache.pinot.core.common.DataFetcher.fetchDictIds(DataFetcher.java:88)\n\tat org.apache.pinot.core.common.DataBlockCache.getDictIdsForSVColumn(DataBlockCache.java:99)\n\tat org.apache.pinot.core.operator.docvalsets.ProjectionBlockValSet.getDictionaryIdsSV(ProjectionBlockValSet.java:69)\n\tat org.apache.pinot.core.query.distinct.dictionary.DictionaryBasedSingleColumnDistinctOnlyExecutor.process(DictionaryBasedSingleColumnDistinctOnlyExecutor.java:42)\n\tat org.apache.pinot.core.operator.query.DistinctOperator.getNextBlock(DistinctOperator.java:61)\n\tat org.apache.pinot.core.operator.query.DistinctOperator.getNextBlock(DistinctOperator.java:38)\n\tat org.apache.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:49)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.processSegments(BaseCombineOperator.java:150)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator$1.runJob(BaseCombineOperator.java:105)\n\tat org.apache.pinot.core.util.trace.TraceRunnable.run(TraceRunnable.java:40)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)" },

Error message from the server: 2022-01-10 19:16:24.768 [pqw-10] ERROR org.apache.pinot.core.operator.combine.BaseCombineOperator - Caught exception while executing operator of index: 1 (query: QueryContext{_tableName='re staurant_bi_feedback_OFFLINE', _selectExpressions=[distinct(tags_value)], _aliasList=[null], _filter=rating_value > '0', _groupByExpressions=null, _havingFilter=null, _orderByExpressions=nul l, _limit=10, _offset=0, _queryOptions={responseFormat=sql, trace=true, groupByMode=sql, timeoutMs=16000}, _debugOptions=null, _brokerRequest=BrokerRequest(querySource:QuerySource(tableName: restaurant_bi_feedback_OFFLINE), pinotQuery:PinotQuery(dataSource:DataSource(tableName:restaurant_bi_feedback_OFFLINE), selectList:[Expression(type:FUNCTION, functionCall:Function(operator:D ISTINCT, operands:[Expression(type:IDENTIFIER, identifier:Identifier(name:tags_value))]))], filterExpression:Expression(type:FUNCTION, functionCall:Function(operator:GREATER_THAN, operands:[ Expression(type:IDENTIFIER, identifier:Identifier(name:rating_value)), Expression(type:LITERAL, literal:<Literal longValue:0>)])), groupByList:[], orderByList:[], limit:10, queryOptions:{res ponseFormat=sql, trace=true, groupByMode=sql, timeoutMs=16000}))}) java.lang.UnsupportedOperationException: null at org.apache.pinot.segment.spi.index.reader.ForwardIndexReader.readDictIds(ForwardIndexReader.java:84) at org.apache.pinot.core.common.DataFetcher$ColumnValueReader.readDictIds(DataFetcher.java:278) at org.apache.pinot.core.common.DataFetcher.fetchDictIds(DataFetcher.java:88) at org.apache.pinot.core.common.DataBlockCache.getDictIdsForSVColumn(DataBlockCache.java:99) at org.apache.pinot.core.operator.docvalsets.ProjectionBlockValSet.getDictionaryIdsSV(ProjectionBlockValSet.java:69) at org.apache.pinot.core.query.distinct.dictionary.DictionaryBasedSingleColumnDistinctOnlyExecutor.process(DictionaryBasedSingleColumnDistinctOnlyExecutor.java:42) at org.apache.pinot.core.operator.query.DistinctOperator.getNextBlock(DistinctOperator.java:61) at org.apache.pinot.core.operator.query.DistinctOperator.getNextBlock(DistinctOperator.java:38) at org.apache.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:42) at org.apache.pinot.core.operator.combine.BaseCombineOperator.processSegments(BaseCombineOperator.java:150) at org.apache.pinot.core.operator.combine.BaseCombineOperator$1.runJob(BaseCombineOperator.java:105) at org.apache.pinot.core.util.trace.TraceRunnable.run(TraceRunnable.java:40) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125) at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:57) at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)

Also, It is weired that aggregation group by works for multi-value columns. This query shows more details and if this query can work, the non-aggregation group by should also work for multi-value columns. Workable query: select tags_value, count(*) from myTable GROUP BY tags_value

[mayankshriv]

cc: @atris

[Jackie-Jiang]

This query will be rewritten to select distinct(tags_value) from myTable. Currently there is no MV support for distinct queries. The syntax for MV distinct is unclear: Should we break the MV into multiple individual values when calculating the distinct? Once we decide the syntax, we can add the support.

[atris]

I believe it should be individual values and ORing the result together.

[Jackie-Jiang]

@atris For single column, we can do that. But if there are multiple MV columns in the distinct, should we return all the combinations (the same semantic for group-by right now)?

[atris]

@atris For single column, we can do that. But if there are multiple MV columns in the distinct, should we return all the combinations (the same semantic for group-by right now)?

Yes, IMO. Sorry, when I meant the grouping in my earlier comment, I meant this exact behaviour.

[MeihanLi]

I feel like that we can break the MV into multiple individual values, since that is the behavior for aggregation group by, "select tags_value, count(*) from myTable GROUP BY tags_value" cc: @Jackie-Jiang @yupeng9

[Jackie-Jiang]

@kishoreg What do you think about the distinct on MV column semantic? I don't see a sql standard for this. To follow the semantic of MV group-by, we can return all combinations of the column values, e.g. col1: [1, 2], col2: [3, 4], distinct(col1, col2) -> [1, 3], [1, 4], [2, 3], [2, 4]

[yupeng9]

As @MeihanLi said, why don't we take the same behavior as the query with aggregations, namely select tags_value, count(*) from myTable GROUP BY tags_value?

[atris]

As @MeihanLi said, why don't we take the same behavior as the query with aggregations, namely select tags_value, count(*) from myTable GROUP BY tags_value?

Yes, I believe that is the consensus here

[Jackie-Jiang]

Fixed with #8850