[jira] [Commented] (CALCITE-5100) Incorrect syntax in sub-query, unsupported LISTAGG

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-5100?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17525167#comment-17525167 ] 

Julian Hyde commented on CALCITE-5100:
--------------------------------------

The problem is probably the character literal argument ', '. Calcite requires arguments to aggregate functions to be field references. So I guess that Calcite creates a Project with a literal as one field. Then RelToSqlConverter inlines that field in the GROUP BY. And Postgres doesn't like literals in its GROUP BY (see CALCITE-4702).

So the solution is probably to not inline the literal in the {{GROUP BY}}. Also make sure that the field that references the literal isn't part of {{groupKey}} in the {{Aggregate}}.

Can you reproduce using a simpler query, such as the following?
{code}SELECT deptno, MIN(', ') FROM emp GROUP BY deptno{code}

> Incorrect syntax in sub-query, unsupported LISTAGG
> --------------------------------------------------
>
>                 Key: CALCITE-5100
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5100
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.30.0
>            Reporter: Roman Churganov
>            Priority: Blocker
>
> Execute query like: 
> {code:sql}
>   select C11, LISTAGG( distinct C12, ', ' )  
>          from FOO  
>          group by C11
> {code}
> In case of JDBC driver doesn't support LISTAGG function (Postresql),  JDBC adapter creates subquery:
> {code:sql}
> SELECT "C11", "C12", ', ' AS "$f2"
>      FROM "FOO"
>      GROUP BY "C11", "C12", ', ' 
> {code}
> which cannot be executed on Postresql and gives error  :
> {noformat}
>    [42601] ERROR: non-integer constant in GROUP BY
> {noformat}
>  



--
This message was sent by Atlassian Jira
(v8.20.7#820007)