How to overcome grouping() function not supported by HANA Table UDF?

Question

I have written some custom logic in HANA anonymous block. I used grouping function somewhere in the middle as a core. It worked well and the result was satisfying.

Once I tried to industrialize it by moving to HANA Table Function, the activation went well however function started throwing an error like below on attempt to select from it.

Could not execute 'udf_foo( )' SAP DBTech JDBC: [7]: feature not supported: grouping_id() or grouping() functions should be used with grouping sets

Could you please tell if grouping( ) operation is officially not supported by Table UDF on HANA?
I am using HANA 1.0 SPS 12. So far as a workaround I used case statements.

Way to reproduce:

do begin sequential execution
    create column table t1 ( id int primary key, customer varchar(5), year int, product varchar(5), sales int );
    insert into t1 values(1, 'C1', 2009, 'P1', 100);
    insert into t1 values(2, 'C1', 2009, 'P2', 200);
    insert into t1 values(3, 'C1', 2010, 'P1', 50);
    insert into t1 values(4, 'C1', 2010, 'P2', 150);
    insert into t1 values(5, 'C2', 2009, 'P1', 200);
    insert into t1 values(6, 'C2', 2009, 'P2', 300);
    insert into t1 values(7, 'C2', 2010, 'P1', 100);
    insert into t1 values(8, 'C2', 2010, 'P2', 150);
end;

CREATE FUNCTION udf_grp_test ( ) 
    RETURNS TABLE(
        CUSTOMER        NVARCHAR(2) ,
        YEAR            INT,
        PRODUCT         NVARCHAR(2) ,
        GRP_YEAR        NVARCHAR(1) ,
        SALES           DEC(3,0)
)
AS BEGIN

    RETURN
        SELECT
            CUSTOMER,
            YEAR,
            PRODUCT,
            GROUPING( YEAR ) AS GRP_YEAR,
            SUM(SALES) AS SALES
        FROM
            T1
        GROUP BY GROUPING SETS(
            ( CUSTOMER, YEAR),
            ( CUSTOMER, PRODUCT)
        );

END

-- Works well
SELECT
    CUSTOMER,
    YEAR,
    PRODUCT,
    GROUPING( YEAR ) AS GRP_YEAR,
    SUM(SALES)
FROM
    T1
GROUP BY GROUPING SETS(
    ( CUSTOMER, YEAR),
    ( CUSTOMER, PRODUCT)
)

-- Throws error     
SELECT * FROM udf_grp_test ( ) 

ps. I found out all the bugs reported in https://answers.sap.com/questions/11570257/sap-hana-usage-of-grouping.html are still not fixed in SPS 12.

Answer 1

Thanks a lot for the really good reproduction example. This is how it should be done and it enables the easy reproduction of the issue. Well done!

Now, I don't have a HANA 1 SPS 12 anymore (and haven't used one for a long time). So, I tried it with the current HANA Express Edition HANA 2 SPS04 and had no issues at all.

The UDF works correctly and the other mentioned bugs from 2015 are not present anymore.

While HANA 1 SPS 12 is still supported, the question here is from when the build you're using is. HANA 1 won't see any newer SPs, so all bug-fixes are done in the SPS 12 branch. That means, maybe you can install the current build and the problems you encounter will be fixed.

Other than that, I'd recommend upgrading to HANA 2 anyhow, if the hardware/OS you're using allows for that.

Two remarks about the example code:

• the SEQUENTIAL EXECUTION keywords are not necessary. As soon as data changing DML is specified in a block, the execution defaults to sequential.
• the return table structure of the table function differs from the table definition (CUSTOMER is NVARCHAR(5) in the table, but NVARCHAR(2) in the table function. In HANA 2 this leads to a compiler warning:
  java.sql.SQLWarning: general warning: Type mismatch for table variable "_SYS_SS2_RETURN_VAR_": Target type "NVARCHAR(2)" of attribute "CUSTOMER" not same as assigned type "VARCHAR(5)": line 11 col 5 (at pos 245)