[TRAFODION-2248] WITH clause with CTE used in subquery gives error 3288

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0-incubating
• Fix Version/s: 2.2.0
• Component/s: sql-cmp
• Assignee: Hans Zeller
• Reporter: Hans Zeller
• Created: 27/Sep/16 23:59
• Updated: 26/Mar/18 16:54
• Resolved: 31/Oct/16 23:18

Description

Here is a simple example for a failing query:

with cse1 as (select * from (values (1)) T(a))
select count(*)
from cse1
where a > (select avg(a) from cse1);

*** ERROR[3288] A syntax error occurred. WITH clause redefined. WITH name CSE1 .

*** ERROR[8822] The statement was not prepared.

There are really two things going on here:

• We hit an assert in RelRoot::transformNode() that assumes there are no subqueries above the RelRoot.
• The error we see is different, however. That's because the initial assert triggers AQR (auto-query retry). When we compile the query a second time, it still has the CTE from the first time, and that causes the error 3288. So, we also need to initialize the parser list of CTEs before a recompilation.

This is the original assert we hit:

  // We are currently assuming that no subqueries have been introduced above me;
  // any new subquery parent would just silently be ignored!
  CMPASSERT( this == locationOfPointerToMe );    // Genesis 10-970828-6025