bug: pivot query can not support subquery

[cdmikechen]

Search before asking

• I had searched in the issues and found no similar issues.

Version

v1.2.615

What's Wrong?

pivot does not support subquery

How to Reproduce?

referenced document: https://docs.databend.com/sql/sql-commands/query-syntax/query-pivot

error sql:

SELECT * 
FROM (select * from monthly_sales)
PIVOT(SUM(amount) FOR MONTH IN (select distinct month from monthly_sales))
ORDER BY EMPID;

If I use a view, databend can support subquey in from:

create view v_monthly_sales as select * from monthly_sales;
 
SELECT * 
FROM v_monthly_sales
PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
ORDER BY EMPID;

Assuming all months are in the results and need to be used, how can I do the query correctly without writing a for?

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[sundy-li]

pivot doesn't support subquery now.

let pivot = map(
        rule! {
           PIVOT ~ "(" ~ #expr ~ FOR ~ #ident ~ IN ~ "(" ~ #comma_separated_list1(expr) ~ ")" ~ ")"
        },
        |(_pivot, _, aggregate, _for, value_column, _in, _, values, _, _)| Pivot {
            aggregate,
            value_column,
            values,
        },
    );

[cdmikechen]

@sundy-li
If databend have supported from or in statements with pivot, then normal people use the habit of writing regular sql with subquery.
Without supporting it, I think the usability of this feature is really low. In fact, a lot of data processing is based on the data dynamic execution. Or to put it another way, will this subquery be considered for support in the future?