Are Vertica CTEs reusable?

Pieter_Sheth-Vo

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)

Bryan_H

This should work. I'm a little concerned that you are linking to v9.3 documentation; we've improved CTE behavior a lot in more recent versions. However, since older versions usually rewrite CTE's as inline expressions (more recent versions materialize them as temp relations automatically), I would run EXPLAIN on each example and see if there is a difference in plan that might cause what you see, and if so, let us know.

Pieter_Sheth-Vo

@Bryan_H Thanks! We're using Vertica 12.0, the docs was just from a Google search. I've opened a tech support case (02684690 ) and sent the EXPLAIN output to that.

VValdar

Hi Pieter,

Check also the impact of the parameter EnableWITHTempRelReuseLimit in this chapter / Processing WITH clauses using EE5 temp relations.

Pieter_Sheth-Vo

@VValdar Thanks! So if I understand it correctly,

• I should be able to reference a WITH clause (e.g. "_target") in this example twice,
• "By default, when WITH clause queries are reused, Vertica saves those WITH clause query outputs in EE5 temp relations."

Sergey_Cherepan_1

Would be nice if life would be so easy (though, on other side, hard life provides for a well paid job).
1) WITH materialisation works only for JDBC and vsql, and docs are quiet about it.
2) WITH materialisation does not work for ODBC and all python drivers that sits on top of ODBC.
In my environment, this creates hard problems when users debug and test performance their queries under Java-JDBC based tools like DbVisualizer or DbWeaver etc, but run in python tools in prod. Queries return same results, but performance can easily differ in order of magnitude.
3) WITH materialisation works only with API calls EXECUTE DIRECT and does not work with prepared statements for all drivers.
4) EXPLAIN PLAN always show plan for inline expansion. I am not aware of a way to produce query plan for WITH materialisation.
5) Queries on EE5 temp relations have non-stable digest, i.e. digest differ between executions of same query. That makes it very hard to find most resource consuming parts of query.
Though, no questions - WITH materialisation is a very useful tool that just hard to correctly use.