WITH RECURSIVE patches 0818

Hi,

Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides
syncing to CVS HEAD, followings are main differences from previous
one:

1) Enhance sgml docs. Patches contributed by Jeff Davis.

2) Fix bug with plans using HashAggregate. Hash tables should be
recreated if recursive scan is used. Otherwise goes into infinite
recursion.

Also I include an implementation doc slightly enhanced previous one.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

On Mon, Aug 18, 2008 at 04:38:52PM +0900, Tatsuo Ishii wrote:
> Hi,
>
> Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides
> syncing to CVS HEAD, followings are main differences from previous
> one:

Thanks for the new patch :)

I think I may have found another bug:

WITH RECURSIVE t(i,j) AS (
VALUES (1,2)
UNION ALL
SELECT t2.i, t.j
FROM (
SELECT 2 AS i
UNION ALL /* Wrongly getting detected, I think */
SELECT 3 AS i
) AS t2
JOIN
t
ON (t2.i = t.i)
)
SELECT * FROM t;
ERROR: attribute number 2 exceeds number of columns 1

Is there some way to ensure that in the case of WITH RECURSIVE, the
query to the right of UNION ALL follows only the SQL:2008 rules about
not having outer JOINs, etc. in it, but otherwise make it opaque to
the error-checking code?

I know I didn't explain that well, but the above SQL should work and
the error appears to stem from the parser's looking at the innermost
UNION ALL instead of the outermost.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> I think I may have found another bug:
>
> WITH RECURSIVE t(i,j) AS (
> VALUES (1,2)
> UNION ALL
> SELECT t2.i, t.j
> FROM (
> SELECT 2 AS i
> UNION ALL /* Wrongly getting detected, I think */
> SELECT 3 AS i
> ) AS t2
> JOIN
> t
> ON (t2.i = t.i)
> )
> SELECT * FROM t;
> ERROR: attribute number 2 exceeds number of columns 1
>
> Is there some way to ensure that in the case of WITH RECURSIVE, the
> query to the right of UNION ALL follows only the SQL:2008 rules about
> not having outer JOINs, etc. in it, but otherwise make it opaque to
> the error-checking code?
>
> I know I didn't explain that well, but the above SQL should work and
> the error appears to stem from the parser's looking at the innermost
> UNION ALL instead of the outermost.

Thanks for the report. I will look into this.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> I think I may have found another bug:
>
> WITH RECURSIVE t(i,j) AS (
> VALUES (1,2)
> UNION ALL
> SELECT t2.i, t.j
> FROM (
> SELECT 2 AS i
> UNION ALL /* Wrongly getting detected, I think */
> SELECT 3 AS i
> ) AS t2
> JOIN
> t
> ON (t2.i = t.i)
> )
> SELECT * FROM t;
> ERROR: attribute number 2 exceeds number of columns 1
>
> Is there some way to ensure that in the case of WITH RECURSIVE, the
> query to the right of UNION ALL follows only the SQL:2008 rules about
> not having outer JOINs, etc. in it, but otherwise make it opaque to
> the error-checking code?
>
> I know I didn't explain that well, but the above SQL should work and
> the error appears to stem from the parser's looking at the innermost
> UNION ALL instead of the outermost.

Here is new patches fixing the bug you pointed out (patches was
created by Yoshiyuki). Also I added your SQL to the regression test,
and now the patches is against CVS HEAD. For your convenience I also
include patches against the previous version.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

On Sat, Aug 23, 2008 at 11:33:13AM +0900, Tatsuo Ishii wrote:
> > I think I may have found another bug:
> >
> > WITH RECURSIVE t(i,j) AS (
> > VALUES (1,2)
> > UNION ALL
> > SELECT t2.i, t.j
> > FROM (
> > SELECT 2 AS i
> > UNION ALL /* Wrongly getting detected, I think */
> > SELECT 3 AS i
> > ) AS t2
> > JOIN
> > t
> > ON (t2.i = t.i)
> > )
> > SELECT * FROM t;
> > ERROR: attribute number 2 exceeds number of columns 1
> >
> > Is there some way to ensure that in the case of WITH RECURSIVE,
> > the query to the right of UNION ALL follows only the SQL:2008
> > rules about not having outer JOINs, etc. in it, but otherwise make
> > it opaque to the error-checking code?
> >
> > I know I didn't explain that well, but the above SQL should work
> > and the error appears to stem from the parser's looking at the
> > innermost UNION ALL instead of the outermost.
>
> Here is new patches fixing the bug you pointed out (patches was
> created by Yoshiyuki). Also I added your SQL to the regression test,
> and now the patches is against CVS HEAD. For your convenience I also
> include patches against the previous version.

Thanks :)

Any progress on the READMEs for this?

Also, now that we are into August, would Asaba-san and whomever else
like to try out the git repository? To do so, I just need a login
name and a public key.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

> > Here is new patches fixing the bug you pointed out (patches was
> > created by Yoshiyuki). Also I added your SQL to the regression test,
> > and now the patches is against CVS HEAD. For your convenience I also
> > include patches against the previous version.
>
> Thanks :)
>
> Any progress on the READMEs for this?

I have posted kind of README (implementation.txt) along with patches
on Aug 18. Have you read it?

> Also, now that we are into August, would Asaba-san and whomever else
> like to try out the git repository? To do so, I just need a login
> name and a public key.

I will send you later.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

On Sat, Aug 23, 2008 at 03:35:52PM +0900, Tatsuo Ishii wrote:
> > > Here is new patches fixing the bug you pointed out (patches was
> > > created by Yoshiyuki). Also I added your SQL to the regression
> > > test, and now the patches is against CVS HEAD. For your
> > > convenience I also include patches against the previous version.
> >
> > Thanks :)
> >
> > Any progress on the READMEs for this?
>
> I have posted kind of README (implementation.txt) along with patches
> on Aug 18. Have you read it?

Oops. I've now put it up with some minor edits and formatting at
<http://wiki.postgresql.org/wiki/CTEReadme>. It is linked from the
Commitfest page.

> > Also, now that we are into August, would Asaba-san and whomever else
> > like to try out the git repository? To do so, I just need a login
> > name and a public key.
>
> I will send you later.

Thanks :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate