Error when using CUBE function: ERROR 3457: Function cube does not exist, or permission is denied
Has anyone seen this before and even better doe anyone know how we can use the Vertica CUBE function
We use “Vertica Analytic Database v7.1.1-0”
I started vsql on the linux databaseserver to be sure the client tool is not the reason of failure.:
When I execute the following statement:
vertica=> select grantor, object_type, count(1), grouping_id()
vertica-> from grants
vertica-> group by CUBE (grantor, object_type);
I got the following error:
ERROR 3457: Function CUBE(varchar, varchar) does not exist, or permission is denied for CUBE(varchar, varchar)
HINT: No function matches the given name and argument types. You may need to add explicit type casts
Vertica user has root permisions.
When I replace CUBE with ROLLUP everything is working perfectly
select grantor, object_type, count(1), grouping_id()
from grants
group by ROLLUP (grantor, object_type)
grantor | object_type | count | grouping_id
-------------------------+--------------+-------+-------------
vertica | RESOURCEPOOL | 23 | 0
vertica | PROCEDURE | 92 | 0
vertica | | 152 | 1
lijn_user | | 4 | 1
pentaho_operations_mart | TABLE | 66 | 0
lijn_user | SCHEMA | 4 | 0
| | 1113 | 3
vertica | SCHEMA | 8 | 0
etl_user | | 882 | 1
vertica | ROLE | 29 | 0
ml | | 6 | 1
etl_user | TABLE | 875 | 0
pentaho_operations_mart | SCHEMA | 3 | 0
etl_user | SCHEMA | 7 | 0
pentaho_operations_mart | | 69 | 1
ml | SCHEMA | 6 | 0
(16 rows)
We are just starting doing the Vertica DBA tasks and this is the first issue we have to solve.
I whould by happy with any suggstion to solve this problem,
Regards,
Tom Kooi
ONVZ Health ensurance
The Netherlands
Comments
Note the following response from Vertica Technical Support.
Thanks for the reply!
Support for CUBE in the GROUP BY aggregation was added 7.1 SP2 (i.e. version 7.1.2-0). Based on the details you've provided, it looks like you're using 7.1.1-0, so the error would be correct: the function doesn't exist in that version. Details can be found in the "Improvements to GROUP BY Aggregation" section of https://my.vertica.com/docs/7.1.x/PDF/HP_Vertica_7.1.x_New_Features.pdf
Thanks, Geoff Krause | Technical Support | HPE Vertica
Tom