Optimizing Pivot Possible Bug

[nezorepla]

Hi,

While using the optimize function, I have notice that it removes pivot structures in queries. Is it known or a bug?

For ex. the following query is parsed correctly.
a=parse_one("""
select *
from(
select a, b, c
from sc.tb
) pivot (
sum(c)
for b in ('x','y','z')
)
""")
print(a.sql(pretty=True))

opt:
SELECT
*
FROM (
SELECT
a,
b,
c
FROM sc.tb
) PIVOT( SUM(c) FOR b IN ('x', 'y', 'z'))

However if we apply optimize the output has no pivot structure:
a=sqlglot.optimizer.optimize(a,{'sc':{'tb':{'a':'INT','b':'INT','c':'INT'}}})
print(a.sql(pretty=True))

opt:
SELECT
"tb"."a" AS "a",
"tb"."b" AS "b",
"tb"."c" AS "c"
FROM "sc"."tb" AS "tb"

[tobymao]

pivot optimization is being actively worked on. hopefully will be ready next week

[georgesittas]

FYI I'm currently working on this, will post an update in this thread when I make some good progress (there are several changes that need to be made).

[georgesittas]

Created a PR for this: #1617