LIST, LIST & COALESCE: Illegal ORDER BY in aggregate function

[former_SQLA_member326303]

Take a simple select that contains two LIST functions, inside each of which we use the COALESCE function.

select 
 "list"(if "coalesce"("row_num",0) > 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc),
 "list"(if "coalesce"("row_num",0) >= 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc) 
FROM sa_rowgenerator(1,10);

In 16.0.0.2546, this select is performed fine.
In 17.0.10.6057, this select causes an error:

Illegal ORDER BY in aggregate function
SQLCODE=-904, ODBC 3 State="42000"

There is a bug 17th version?

[VolkerBarth]

Funnily enough is does work with 17.0.10.6057 when using isnull instead of coalesce in the list aggregate (when using coalesce in the order by):

select 
 "list"(if "isnull"("row_num",0) > 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc),
 "list"(if "isnull"("row_num",0) >= 5 then ' (p)' else '' endif, ',' order by "coalesce"("row_num",0) desc) 
FROM sa_rowgenerator(1,10);

or when using isnull only in the order by - but it also fails when replacing all 4 appearances of coalesce with isnull... So I guess the answer to your question is "yes".

[chris_keating]

This appears to be a bug that was introduced between 17.0 Build 4935 (PL39) and 17.0 Build 5745 (PL41). It is being investigated as Engineering Case# 821576.

[VolkerBarth · Accepted solution]

This is solved in 17.0.0.6175, cf. the note on Engineering Case #821576 in the newest readme file:

    The server may incorrectly return the SQL code -904 "Illegal ORDER BY 
    in aggregate function" if a query contains two or more LIST functions 
    in the same query block, the LIST functions have the same or subsumed ORDER 
    BY clauses, and an order by expression is used in an IF expressions in the 
    first parameter of the LIST 
    function.

    For example: In the following query the expression "coalesce(a1,0)" 
    is in both LIST functions as part of the IF
    expressions and the ORDER BY.

    select 
    list( if coalesce(a1,0) < 1 then b1 else b1*(c1/a1) endif, ',' order 
    by coalesce(a1,0) desc ) as col1,
    list( if coalesce(a1,0) <= 1 then b1 else b1*(c1/a1) endif, ',' order 
    by coalesce(a1,0) desc ) as col2
    from T1

    This has been fixed.