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?

asked 27 Apr, 05:20

Ilia63's gravatar image

Ilia63
1.1k404468
accept rate: 44%

1

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".

(27 Apr, 05:54) Volker Barth
2

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.

(29 Apr, 08:34) Chris Keating
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×164

question asked: 27 Apr, 05:20

question was seen: 101 times

last updated: 30 Apr, 14:10