Unable to use calculated column in grouping sets

[mcvejic]

When trying to use calculated column in grouping set error showing that query is not valid is thrown.

examples:

select transaction_id, term_start, 'test' as test, sum(quantity) from transaction group by grouping sets ((transaction_id, 'test'), (transaction_id, term_start));

throws

Query 20170801_083703_00110_2mfvk failed: line 1:124: mismatched input ''test'' expecting {'ADD', 'ALL', 'SOME', 'ANY', 'AT', 'NO', 'SUBSTRING', 'POSITION', 'TINYINT', 'SMALLINT', 'INTEGER', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL', 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'ZONE', 'FILTER', 'OVER', 'PARTITION', 'RANGE', 'ROWS', 'PRECEDING', 'FOLLOWING', 'CURRENT', 'ROW', 'SCHEMA', 'COMMENT', 'VIEW', 'REPLACE', 'GRANT', 'REVOKE', 'PRIVILEGES', 'PUBLIC', 'OPTION', 'GRANTS', 'EXPLAIN', 'ANALYZE', 'FORMAT', 'TYPE', 'TEXT', 'GRAPHVIZ', 'LOGICAL', 'DISTRIBUTED', 'VALIDATE', 'VERBOSE', 'SHOW', 'TABLES', 'SCHEMAS', 'CATALOGS', 'COLUMNS', 'COLUMN', 'USE', 'PARTITIONS', 'FUNCTIONS', 'TO', 'SYSTEM', 'BERNOULLI', 'POISSONIZED', 'TABLESAMPLE', 'ARRAY', 'MAP', 'SET', 'RESET', 'SESSION', 'DATA', 'START', 'TRANSACTION', 'COMMIT', 'ROLLBACK', 'WORK', 'ISOLATION', 'LEVEL', 'SERIALIZABLE', 'REPEATABLE', 'COMMITTED', 'UNCOMMITTED', 'READ', 'WRITE', 'ONLY', 'CALL', 'INPUT', 'OUTPUT', 'CASCADE', 'RESTRICT', 'INCLUDING', 'EXCLUDING', 'PROPERTIES', 'NFD', 'NFC', 'NFKD', 'NFKC', 'IF', 'NULLIF', 'COALESCE', IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}
select transaction_id, term_start, 'test' as test, sum(quantity) from transaction group by grouping sets ((transaction_id, 'test'), (transaction_id, term_start))

at the same time it is not possible to use calculated column alias (expected).

This works when using just group by without grouping sets.

[cploonker]

Facing the same issue. The only workaround is to calculate the derived column using a join with a lookup table which maps the original column to derived column and then do grouping set on the derived column. Since it work in GROUP BY, it should work in GROUPING SETS also. Syntactically the best would be if we could use the alias from select statement in group by and grouping sets.

[martint]

Syntactically the best would be if we could use the alias from select statement in group by and grouping sets.

It's not clear what that would mean, since the expressions in the SELECT clause are "evaluated" on the results of the aggregation (group by/grouping sets) phase.

[210grams]

You also can't use column index in grouping sets.

These are successful:
SELECT CAST(column AS string) as StringColumn,
count(*)
FROM table
GROUP BY 1 /OR/
GROUP BY CAST(column AS string) /OR/
GROUP BY GROUPING SETS ((column), ())

These fail:
SELECT CAST(column AS string) as StringColumn,
count(*)
FROM table
GROUP BY StringColumn /OR/
GROUP BY GROUPING SETS ((1), ()) /OR/
GROUP BY GROUPING SETS ((CAST(column AS string)), ()) /OR/
GROUP BY GROUPING SETS ((StringColumn), ())

All of these failed cases work intuitively in Vertica SQL, some details here: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/SELECT/GROUPBYClause.htm?zoom_highlight=grouping%20sets

Choosing some/all of these to support would be great.

[stale]

This issue has been automatically marked as stale because it has not had any activity in the last 2 years. If you feel that this issue is important, just comment and the stale tag will be removed; otherwise it will be closed in 7 days. This is an attempt to ensure that our open issues remain valuable and relevant so that we can keep track of what needs to be done and prioritize the right things.

[jborchma]

Just wanted to add that I am missing this functionality a lot as well. Grouping sets are incredibly useful, but being able to use either number references for column or use the name of a derived column would be absolutely great!