cancel
Showing results for 
Search instead for 
Did you mean: 

Arithmetic expression in AVG not supported with GROUPING SETS on column table

patricksteffens
Participant
0 Kudos

I constructed the following query:

SELECT L_ORDERKEY as orderkey,
       L_LINENUMBER as number,
       L_TAX as tax,
       AVG(L_EXTENDEDPRICE * L_PARTKEY) as avg_price
   FROM lineitem <br>   GROUP BY GROUPING SETS(L_ORDERKEY, L_LINENUMBER, L_TAX);

Since I constructed it such that it consumes quite a bit of time, the result is obviously not really expressive.

Anyway, if table lineitem is on row store, the query is processed without any errors. If it is on columnar store, I get the error:

feature not supported: aggregation has unsupported expressions

After either removing the arithmetic expression in AVG or removing the "GROUPING SETS" and the parentheses, it executes fine again. So does it if I use SUM instead of AVG.

So what's the matter with GROUPING SETS in connection with arithemtic expressions in AVG and why is this only a problem for column tables?

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor

That looks like an undocumented limitation (bug!) to me. I've opened a support message for this to get an official statement about the issue.

patricksteffens
Participant
0 Kudos

Thanks! Please keep me updated if there is any progress.

lbreddemann
Active Contributor
0 Kudos

SAP HANA development reported back that the fix for this issue is going to be included in SAP HANA revisions 122.16, 2.00.012.05 and 2.00.024.

lbreddemann
Active Contributor

Just checked: the fix works on 122.16. (no time to test the other versions, but I assume these work fine, too).