All Recursive View Definitions Must Be Linear - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

SQL does not support non-linear recursion.

When recursion is linear, invoking a recursive view produces at most 1 direct invocation of that recursive view. To enforce linearity, SQL restricts table references to a single occurrence in the recursive view definition, which can be specified either in the FROM clause of the recursive statement or in a subquery in the recursive statement, but not in both.

A recursion is said to be linear if it obeys the following rules.

  • For every joined table in the recursive view definition, the recursively-defined table is referenced only once.
  • The recursively-defined table is not referenced in both the FROM clause and in a subquery of the same query specification.
  • The recursively-defined table is not referenced more than once in the FROM clause of the query specification.

If any of these conditions is not met, then the recursion is non-linear.

The following create text is an example of a non-valid attempt to create a recursive view definition. The definition is not valid because the recursive view Fibonacci is referenced more than once (using the column alias names p and pp, respectively) in the FROM clause of the recursively-defined relation in the view definition. As a result, it violates the rule stated in the third bullet.

     CREATE RECURSIVE VIEW fibonacci (n, f, mycount) AS
       SELECT a, b, 0 AS mycount
       FROM t
       WHERE (a=0
       AND    b=0)
       OR    (a=1
       AND    b=1)
     UNION ALL
       SELECT n+1, p.f + pp.f, p.mycount + 1
       FROM fibonacci AS p, fibonacci AS pp
       WHERE (p.n - 1) = pp.n
       AND   p.mycount <= 100;

The following similarly conceived query that uses derived tables has the same problem.

     CREATE RECURSIVE VIEW fibonacci (n, f, mycount) AS
       SELECT  n, f, mycount
       FROM (SELECT 0,0,0) AS a (n,f,mycount)
     UNION ALL
       SELECT n, f, mycount
       FROM (SELECT 1,1,0) AS b (n, f, mycount)
     UNION ALL
       SELECT n+1, p.f + pp.f, p.mycount + 1
       FROM fibonacci AS p, fibonacci AS pp
       WHERE (p.n - 1) = pp.n
       AND   p.mycount <= 100;