|
Take a simple select that contains two LIST functions, inside each of which we use the COALESCE function. In 17.0.10.6057, this select causes an error: |
|
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.
|
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".
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.