Description
Description
I encountered an issue when using the PIVOT operator together with subqueries or CTEs. The PIVOT operation only works directly on a table, but not when the same data is selected via a subquery or CTE.
Steps to reproduce the behavior (Required)
CREATE TABLE sandbox.test_pivot (
id int,
category varchar(64),
val1 int,
val2 int
);
INSERT INTO sandbox.test_pivot VALUES
(1, 'fruit', 2, 3),
(2, 'veggies', 4, 5),
(3, 'other', 6, 7);
-- This works as expected:
SELECT * FROM sandbox.test_pivot
PIVOT (
sum(val1) AS sum_orders,
avg(val2) AS avg_price
FOR category IN ('fruit', 'veggies')
);
-- This does NOT work (using a CTE):
WITH test_cte AS (
SELECT * FROM sandbox.test_pivot
)
SELECT * FROM test_cte
PIVOT (
sum(val1) AS sum_orders,
avg(val2) AS avg_price
FOR category IN ('fruit', 'veggies')
);
/*
SQL Error [1064] [42000]: Getting analyzing error. Detail message: Column 'sandbox.test_pivot.id' cannot be resolved.
*/
-- This also does NOT work (using a subquery):
SELECT * FROM (
SELECT * FROM sandbox.test_pivot
) t
PIVOT (
sum(val1) AS sum_orders,
avg(val2) AS avg_price
FOR category IN ('fruit', 'veggies')
);
/*
SQL Error [1064] [42000]: Getting analyzing error. Detail message: Column 'sandbox.test_pivot.id' cannot be resolved.
*/
Expected behavior (Required)
PIVOT should work the same way on a subquery or CTE as it does on a base table.
Real behavior (Required)
When using PIVOT on a subquery or CTE I get an error:
SQL Error [1064] [42000]: Getting analyzing error. Detail message: Column 'sandbox.test_pivot.id' cannot be resolved.
StarRocks version (Required)
3.4.0
Additional context:
It would be very helpful to support PIVOT on subqueries and CTEs for more flexible data transformations.