Support UNION queries that share a CTE

[jalaziz]

I would like to write queries of the form:

WITH cte AS (SELECT * FROM ....)
(SELECT * FROM cte WHERE ...
UNION
SELECT * FROM cte WHERE ...)

Unfortunately, it seems that when trying to build such a query, the WITH clause is no added to the query. I believe this is due to the first if clause in CTECompiler. generate_sql.

I'm happy to put up a PR to remove the clause and attempt to make it work, but curious if there's a more fundamental not adding the WITH clause to queries with combinations?

[millerdev]

I don't see a test that covers that scenario, and I can't remember if there is a workaround for queries with a combinator. That if clause should have probably raised NotImplementedError rather than returning as_sql(). But maybe that would have caused issues elsewhere?

A PR would be welcome. Please add tests as well.

[gjones-r7]

Oh, I need this too! This is a handy library.