Sanity check, am I allowed to refer to a CTE multiple times in subsequent CTEs within the same query?
I'm finding a case where if I define a CTE under one alias and refer to it twice I get results that very and unexplainably different than expected.
If I understand the docs, they should be reusable, "Vertica can execute the CTE on each reference (inline expansion), or materialize the result set as a temporary table that it reuses for all references. "
WITH
foo AS ( ...),
bar AS ( state, COUNT(*) AS x FROM foo GROUP BY state),
qix AS ( state, county, COUNT(*) AS y FROM foo GROUP BY state, county)
SELECT *, y/x FROM bar LEFT JOIN qix USING (state)
But the problem goes away if I do any of the following:
materialize the CTE using /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */
WITH
foo /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ AS ( ...),
bar AS ( state, COUNT(*) AS x FROM foo GROUP BY state),
qix AS ( state, county, COUNT(*) AS y FROM foo GROUP BY state, county)
SELECT *, y/x FROM FROM bar LEFT JOIN qix USING (state)
redefine the same CTE twice under two different aliases and refer to each once
WITH
foo AS ( ...),
foo2 AS ( ...),
bar AS ( state, COUNT(*) AS x FROM foo GROUP BY state),
qix AS ( state, county, COUNT(*) AS y FROM foo2 GROUP BY state, county)
SELECT *, y/x FROM bar LEFT JOIN qix USING (state)
not use CTEs but write it twice as embedded subqueries
WITH
bar AS ( state, COUNT(*) AS x FROM (...foo...) GROUP BY state),
qix AS ( state, county, COUNT(*) AS y FROM (...foo...) GROUP BY state, county)
SELECT *, y/x FROM bar LEFT JOIN qix USING (state)
