Hans Zeller created TRAFODION-2248:
--------------------------------------
Summary: WITH clause with CTE used in subquery gives error
Key: TRAFODION-2248
URL: https://issues.apache.org/jira/browse/TRAFODION-2248
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.0-incubating
Reporter: Hans Zeller
Here is a simple example for a failing query:
{noformat}
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.
{noformat}
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 error 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:
{noformat}
// 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
{noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
