PIVOT does not work with subqueries or CTEs: "Column 'column_name' cannot be resolved" error

[vitalyDE]

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.

[artemivchatov]

Still the case in version 3.4.1. Pivot is quite useless if you can't use it with CTEs.