Re: WITH RECURSIVE patches V0.1 TODO items

> Hi,
> 
> Thanks to all who respnoded to the WITH RECURSIVE patches V0.1. Here
> are TODO items so far. Lines starting with "*" are my comments and
> questions.
> 
> - SEARCH clause not supported
> 
>   * do weed this for 8.4?
> 
> - CYCLE clause not supported
> 
>   * do weed this for 8.4?
> 
> - the number of "partition" is limited to up to 1
> 
>   * do weed this for 8.4?
> 
> - "non_recursive_term UNION recursive_term" is not supported.  Always
>   UNION ALL" is requried. (i.e. "non_recursive_term UNION ALL
>   recursive_term" is supported)
> 
>   * do weed this for 8.4?
> 
> - mutually recursive queries are not supported
> 
>   * do weed this for 8.4?
> 
> - mutually recursive queries are not detected
> 
>   * do weed this for 8.4?
> 
> - cost of Recursive Scan is always 0
> 
> - infinit recursion is not detected
> 
>   * Tom suggested let query cancel and statement_timeout handle it.
> 
> - only the last SELECT of UNION ALL can include self recursion name
> 
> - outer joins for recursive name and tables does not work

Further investigations showed that it seems the standard does not
allow some cases including above. I found these in a Japanese book
which was written by someone who are one of the SQL standard
committees. Note that the book was written for SQL:1999. May be some
of the restrictions are removed in SQL:2008(still in a draft phase)
but not sure. I guess most of these will be carried in SQL:2008 since
these are required to ensure that the recursive query has a fixed
point however.

In query expressions in the WITH clause:

- EXCEPT which has a recursive query name in the right hand operator is not allowed

- function which has recursive query name as an operator is not allowed

- subquery which includes a recursive query name is not allowed. Note that in the most outer query in the WITH clause
subquerywhich includes a recursive query name is allowed
 

- query which has a selection list including recursive query name and aggregate function is not allowed

- query which has a selection list including recursive query name and HAVING clause

- query including recursive query name and INTERSECT ALL or EXCEPT ALL is not allowed

- query including recursive query name and FULL OUTER JOIN is not allowed

- outer join query is not allowed if the right hand side of LEFT OUTER JOIN has recursive query name

- outer join query is not allowed if the left hand side of RIGHT OUTER JOIN has recursive query name

> - need regression tests
> 
> - need docs (at least SELECT reference manual)
> 
> - some queries crash. Examples are following:
> 
> --non recursive term only case: crashed with V0.1 patches
> WITH RECURSIVE subdepartment AS
> (
>   -- non recursive term
>   SELECT * FROM department WHERE name = 'A'
> )
> SELECT * FROM subdepartment ORDER BY name;
> 
> -- recursive term only case: crashed with V0.1 patches
> WITH RECURSIVE subdepartment AS
> (
>   -- recursive term
>   SELECT sd.level + 1, d.* FROM department AS d, subdepartment AS sd
>     WHERE d.parent_department = sd.id
> )
> SELECT * FROM subdepartment ORDER BY name;
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan