recursive query spool space error

General
N/A

recursive query spool space error

I am trying to get absolute parent child mapping for below table, but my query keeps running into spool space error. please advise.

WITH RECURSIVE
ancestor(a,d) AS
(SEL parent AS a, child AS d FROM p_ci_trust_t.parentof WHERE child='Mary'
UNION ALL
SEL ancestor.a, p_ci_trust_t.parentof.child AS d
FROM ancestor JOIN p_ci_trust_t.parentof p
ON ancestor.d=p.parent)
SEL a FROM ancestor WHERE d='mary'
parent  child
alice carol
bob carol
carol dave
carol geouge
dave mary
eve mary
mary frank
1 REPLY
Senior Apprentice

Re: recursive query spool space error

This is not due to recursion :-)

Teradata was implemented before there was Standard SQL, the initial query language was called TEQUEL (TEradata QUEry Language), whose syntax didn't require to list tables within FROM (this was ported to SQL).

A simple RETRIEVE tablename.columnname; carried enough information for the Parser to resolve tablename and columnname.

In your query you mixed TEQUEL and SQL when you aliased p_ci_trust_t.parentof as p. A table-alias is not really an alias, it replaces the tablename within that query. Using the original tablename doesn't result in an error message (as it does within most other DBMSes), but it causes a CROSS join: You got three tables (ancestor, p, p_ci_trust_t.parentof), but only one join-condition.

This is one of the reasons why you should run a quick explain :)

Simply replace p_ci_trust_t.parentof.child AS d with p.child AS d, when you still run out of spool you have circular data.