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 subquery which
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