SingleValueAggFunction received more than one element error with LISTAGG

[soumoks123]

I receive the following error when trying to use the LISTAGG function in
Table API.


java.lang.RuntimeException: SingleValueAggFunction received more than one
element.
        at GroupAggsHandler$1460.accumulate(Unknown Source)
        at
org.apache.flink.table.runtime.operators.aggregate.GroupAggFunction.processElement(GroupAggFunction.java:158)
        at
org.apache.flink.table.runtime.operators.aggregate.GroupAggFunction.processElement(GroupAggFunction.java:43)
        at
org.apache.flink.streaming.api.operators.KeyedProcessOperator.processElement(KeyedProcessOperator.java:85)
        at
org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processElement(StreamOneInputProcessor.java:164)
        at
org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processInput(StreamOneInputProcessor.java:143)
        at
org.apache.flink.streaming.runtime.tasks.StreamTask.processInput(StreamTask.java:279)
        at
org.apache.flink.streaming.runtime.tasks.StreamTask.run(StreamTask.java:301)
        at
org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:406)
        at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:705)
        at org.apache.flink.runtime.taskmanager.Task.run(Task.java:530)
        at java.lang.Thread.run(Thread.java:748)


This is my query,

SELECT node.nid,
(SELECT LISTAGG(DISTINCT(TDC.name)) FROM M_TermNode TNC JOIN M_TermData TDC
ON TNC.tid = TDC.tid WHERE node.nid = TNC.nid AND TDC.vid = (SELECT
Vocab.vid from M_Vocabulary Vocab where Vocab.`module` = 'extra_fields')) AS
characteristics
FROM node


The above query looks slightly complex but essentially boils down to a group
by on TDC.vid. This may return more than one value for TDC.name which needs
to be concatenated into the same string.


I have tried removing the DISTINCT clause inside LISTAGG but to no avail.

The same query works on a MySQL DB with GROUP_CONCAT instead of LISTAGG.



--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/

[Timo Walther]

Hi,

which Flink version are you using?

Could you also share the resulting plan with us using
`TableEnvironment.explainSql()`?

Thanks,
Timo


On 07.04.21 17:29, soumoks123 wrote:

> I receive the following error when trying to use the LISTAGG function in
> Table API.
>
>
> java.lang.RuntimeException: SingleValueAggFunction received more than one
> element.
>          at GroupAggsHandler$1460.accumulate(Unknown Source)
>          at
> org.apache.flink.table.runtime.operators.aggregate.GroupAggFunction.processElement(GroupAggFunction.java:158)
>          at
> org.apache.flink.table.runtime.operators.aggregate.GroupAggFunction.processElement(GroupAggFunction.java:43)
>          at
> org.apache.flink.streaming.api.operators.KeyedProcessOperator.processElement(KeyedProcessOperator.java:85)
>          at
> org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processElement(StreamOneInputProcessor.java:164)
>          at
> org.apache.flink.streaming.runtime.io.StreamOneInputProcessor.processInput(StreamOneInputProcessor.java:143)
>          at
> org.apache.flink.streaming.runtime.tasks.StreamTask.processInput(StreamTask.java:279)
>          at
> org.apache.flink.streaming.runtime.tasks.StreamTask.run(StreamTask.java:301)
>          at
> org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:406)
>          at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:705)
>          at org.apache.flink.runtime.taskmanager.Task.run(Task.java:530)
>          at java.lang.Thread.run(Thread.java:748)
>
>
> This is my query,
>
> SELECT node.nid,
> (SELECT LISTAGG(DISTINCT(TDC.name)) FROM M_TermNode TNC JOIN M_TermData TDC
> ON TNC.tid = TDC.tid WHERE node.nid = TNC.nid AND TDC.vid = (SELECT
> Vocab.vid from M_Vocabulary Vocab where Vocab.`module` = 'extra_fields')) AS
> characteristics
> FROM node
>
>
> The above query looks slightly complex but essentially boils down to a group
> by on TDC.vid. This may return more than one value for TDC.name which needs
> to be concatenated into the same string.
>
>
> I have tried removing the DISTINCT clause inside LISTAGG but to no avail.
>
> The same query works on a MySQL DB with GROUP_CONCAT instead of LISTAGG.
>
>
>
> --
> Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
>