From: | Tatsuo Ishii <ishii(at)postgresql(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: WITH RECURSIVE patches V0.1 TODO items |
Date: | 2008-05-27 07:40:41 |
Message-ID: | 20080527.164041.93382314.t-ishii@sraoss.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Meskes | 2008-05-27 08:03:47 | Re: keyword list/ecpg |
Previous Message | Zdenek Kotala | 2008-05-27 06:45:09 | Re: Packages in oracle Style |