Optimize Function does not seem to deal with ORDER BY in CTEs

[rorynormaness]

Hello guys - me again.

I seem to be encountering strange behaviour of the optimize function on BigQuery SQL, when there is an ORDER BY clause within a CTE, which results in invalid SQL output.

Let's take the following as an example to be optimized:

WITH 
     CTE1 AS
              (
              SELECT   col1,
                       col2,
              FROM     `my_catalog.my_dataset.my_table` AS A
              ORDER BY col1 # Note the ORDER BY clause here which is valid for BigQuery
              )
  , CTE2 AS
              (
                    SELECT col1,
                           col2,
                    FROM   `my_catalog.my_dataset.my_table`  AS A
              )
  , CTE3 AS
            (
                  SELECT col1,
                         col2,
                  FROM   CTE1
                  UNION ALL
                  SELECT col1,
                         col2,
                  FROM   CTE2
            )

SELECT col1,
       col2,

FROM   CTE3

Then when I put this through the optimize function as follows:

ast = maybe_parse(ddl, dialect='bigquery')
optimized_query = optimize(ast, dialect='bigquery').sql(dialect='bigquery',pretty=True)

I get the following:

WITH `cte3` AS
(
         SELECT   `a`.`col1`                       AS `col1`,
                  `a`.`col2`                       AS `col2`
         FROM     `my_catalog.my_dataset.my_table` AS `a`
         ORDER BY `col1`
         /* This is not valid in BigQuery - the ORDER BY must go at the end, or not be there at all*/
         UNION ALL
         SELECT `a`.`col1`                       AS `col1`,
                `a`.`col2`                       AS `col2`
         FROM   `my_catalog.my_dataset.my_table` AS `a` )
SELECT `cte3`.`col1` AS `col1`,
       `cte3`.`col2` AS `col2`
FROM   `cte3`        AS `cte3`

I know it would be a judgment call on whether to put it at the end or not at all, but for what it's worth, I would make the case that it should be omitted altogether in the optimizer rules because:

1. The integrity of the first order cannot be maintained with BigQuery's syntax when optimizing
2. Ordering the full final result-set will never make the query faster in the context of a query because, whichever way you look at it, it's an additional operation.

[georgesittas]

Hey @rorynormaness, thanks for trying out the optimizer and reporting these problems. Can you just file an issue for this and any other bug you find? Thanks!

[rorynormaness]

Hey @georgesittas, yes sure will do. The reason I didn't file these two as issues is because I'd asked a couple of questions and it turned out to be me that was the problem rather than sqlglot ha!

I'll just file as issues going forwards.

Thanks again for your help and I'll let you know if I find anything else.

Filed this as #3211