Strange behaviour in optimize function

[rorynormaness]

Hello guys, I have been using the optimizer function, and I think I have found a strange behaviour which is causing the optimize function to output an invalid query.

Whilst I found this isse on a trickier query, I have tried to create a simple example which illustrates the issue for reproducibility. Here it is:

WITH cte1
            AS (SELECT col1,
                       col2
                  FROM c1.d1.t1
                ),
     cte2
            AS (
                SELECT col1
                        , ARRAY_AGG(DISTINCT col3) AS col3_arr
                  FROM c1.d1.t2
                  WHERE col4 = 1
                  GROUP BY col1
                )
SELECT IF(cte1.col1 IN UNNEST(cte2.col3_arr), 1, 0) AS col5
FROM cte1
LEFT OUTER JOIN cte2 
USING (col1)

When I call optimize on this as follows:

ast = parse_one(sql, dialect='bigquery')
print(optimize(ast,dialect='bigquery').sql(dialect='bigquery',pretty=True))

I get this:

SELECT
  IF(`t1`.`col1` IN UNNEST(`cte2`.`col3_arr`), 1, 0) AS `col5`
FROM `c1`.`d1`.`t1` AS `t1`

However this is not valid sql - the CTE which is being referenced in the optimized query - cte2 - is no longer defined, and the join relation has also been completely lost.

Is this an issue, or am I doing something wrong here?

Thanks guys!

[georgesittas]

This seems like a bug, can you please file an issue to track it properly?

[rorynormaness]

Sure thing @georgesittas - Just filed issue #3209.

Let me know if you need anything else from me on this.