SELECT pr.name as name, MAX(pr.age) as age, MAX(city) as city
FROM (
SELECT CAST(pr AS ROW(name VARCHAR, age int)) as pr, city
FROM(
VALUES
(ROW('John Doe', 30), 'New York'),
(ROW('Jane Doe', 25), 'Los Angeles')
) AS t(pr, city)
)
GROUP BY GROUPING SETS (pr.name)
Current Behavior
Following query works
SELECT pr.name as name, MAX(pr.age) as age, MAX(city) as city
FROM (
SELECT CAST(pr AS ROW(name VARCHAR, age int)) as pr, city
FROM(
VALUES
(ROW('John Doe', 30), 'New York'),
(ROW('Jane Doe', 25), 'Los Angeles')
) AS t(pr, city)
)
GROUP BY pr.name
However following query fails
SELECT pr.name as name, MAX(pr.age) as age, MAX(city) as city
FROM (
SELECT CAST(pr AS ROW(name VARCHAR, age int)) as pr, city
FROM(
VALUES
(ROW('John Doe', 30), 'New York'),
(ROW('Jane Doe', 25), 'Los Angeles')
) AS t(pr, city)
)
GROUP BY GROUPING SETS (pr.name)
Error
Error(type=USER_ERROR, name=INVALID_COLUMN_REFERENCE, message="line 11:26: GROUP BY expression must be a column reference: pr.name
Your Environment
Expected Behavior
Following query to work without any errors
Current Behavior
Following query works
However following query fails
Error
Shouldn't both work?
Possible Solution
Steps to Reproduce
1. 2. 3. 4.
Screenshots (if appropriate)
Context
Unable to use grouping sets with struct columns