Thread: Re: [PATCHES] WITH RECUSIVE patches 0717

Re: [PATCHES] WITH RECUSIVE patches 0717

From

"Erik"

Date:

18 July 2008, 17:30:48

On Fri, July 18, 2008 03:41, Tatsuo Ishii wrote:
>> > Here is the lastest WITH RECURSIVE patches against CVS HEAD created by
>> > Yoshiyuki Asaba and minor corrections by Tatsuo Ishii.
>>
>> I tried this patch vs. CVS HEAD used my usual configure option with
>> only --with-prefix set, then tried to make, and got:
>>
>> make[3]: *** No rule to make target `parse_cte.o', needed by `objfiles.txt'.
>>  Stop.
>> make[3]: Leaving directory `/home/shackle/pgsql/src/backend/parser'
>> make[2]: *** [parser-recursive] Error 2
>> make[2]: Leaving directory `/home/shackle/pgsql/src/backend'
>> make[1]: *** [all] Error 2
>> make[1]: Leaving directory `/home/shackle/pgsql/src'
>> make: *** [all] Error 2
>>
>> Is there something missing?
>
> Oops. I forgot to include patches against newly added files. Please
> try included patches.
>


This crashes the backend:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 5 ORDER BY 1
)
SELECT n FROM t;

apparently because of the  ORDER BY 1

( ORDER BY t.n  will just error out )


Compiled with:

    ./configure \
     --prefix=${install_dir} \
     --with-pgport=${pgport} \
     --quiet          \
     --enable-depend  \
     --enable-cassert \
     --enable-debug   \
     --with-openssl


hth

Erik Rijkers

Re: [PATCHES] WITH RECUSIVE patches 0717

From

Tatsuo Ishii

Date:

20 July 2008, 15:42:26

> This crashes the backend:
>
> WITH RECURSIVE t(n) AS (
>     VALUES (1)
>   UNION ALL
>     SELECT n+1 FROM t WHERE n < 5 ORDER BY 1
> )
> SELECT n FROM t;
>
> apparently because of the  ORDER BY 1

Thanks for the report. I think ORDER BY in this case is useless
anyway. ORDER BY affects (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE
n < 5). Since this is a recursive query, value for (VALUES (1) UNION
ALL SELECT n+1 FROM t WHERE n < 5) will not be determined until the
recursion stops. So the meaning of ORDER BY is vague. If caller wants
to get the sorted result of the recursion, he could always write:

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 5
)
SELECT n FROM t ORDER BY 1;

Thus I think we should avoid this kind of ORDER BY. Probably we should
avoid LIMIT/OFFSET and FOR UPDATE as well. Included patches add the
checking plus minor error messages clarifications. Also I include new
error cases sql.

> ( ORDER BY t.n  will just error out )
>
> Compiled with:
>
>     ./configure \
>      --prefix=${install_dir} \
>      --with-pgport=${pgport} \
>      --quiet          \
>      --enable-depend  \
>      --enable-cassert \
>      --enable-debug   \
>      --with-openssl
>
>
> hth
>
> Erik Rijkers
>
>
>
>
>
-- UNION
WITH RECURSIVE x(n) AS (SELECT 1 UNION SELECT n+1 FROM x)
  SELECT * FROM x;

-- INTERSECT
WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT SELECT n+1 FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 INTERSECT ALL SELECT n+1 FROM x)
  SELECT * FROM x;

-- EXCEPT
WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT SELECT n+1 FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 EXCEPT ALL SELECT n+1 FROM x)
  SELECT * FROM x;

-- no non-recursive term
WITH RECURSIVE x(n) AS (SELECT n FROM x)
  SELECT * FROM x;

-- recursive term in the left hand side
WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
  SELECT * FROM x;

CREATE TEMP TABLE y (a int);
INSERT INTO y SELECT generate_series(1, 10);
-- LEFT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM y LEFT JOIN x ON x.n = y.a where n <
10)
  SELECT * FROM x;

-- RIGHT JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM x RIGHT JOIN y ON x.n = y.a where n <
10)
  SELECT * FROM x;

-- FULL JOIN
WITH RECURSIVE x(n) AS (SELECT a FROM y WHERE a = 1 UNION ALL SELECT x.n+1 FROM x FULL JOIN y ON x.n = y.a where n <
10)
  SELECT * FROM x;

-- subquery
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
                          WHERE n IN (SELECT * FROM x))
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x
                          WHERE n = 1 AND n IN (SELECT * FROM x))
  SELECT * FROM x;

-- GROUP BY
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x GROUP BY n)
  SELECT * FROM x;

-- HAVING
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x HAVING n < 10)
  SELECT * FROM x;

-- aggregate functions
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT count(*) FROM x)
  SELECT * FROM x;

WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT sum(*) FROM x)
  SELECT * FROM x;

-- ORDER BY
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x ORDER BY 1)
  SELECT * FROM x;

-- LIMIT/OFFSET
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x LIMIT 10 OFFSET 1)
  SELECT * FROM x;

-- FOR UPDATE
WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM x FOR UPDATE)
  SELECT * FROM x;

Re: [PATCHES] WITH RECUSIVE patches 0717

From

Tom Lane

Date:

20 July 2008, 16:22:39

Tatsuo Ishii <ishii@postgresql.org> writes:
> Thus I think we should avoid this kind of ORDER BY. Probably we should
> avoid LIMIT/OFFSET and FOR UPDATE as well.

What of index-optimized SELECT max(...) ?

            regards, tom lane

Re: [PATCHES] WITH RECUSIVE patches 0717

From

Tatsuo Ishii

Date:

20 July 2008, 23:19:45

> > Thus I think we should avoid this kind of ORDER BY. Probably we should
> > avoid LIMIT/OFFSET and FOR UPDATE as well.
>
> What of index-optimized SELECT max(...) ?

Aggregate functions in a recursive term is prohibited by the
standard. For example,

WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT max(n) FROM x)
  SELECT * FROM x;

produces an error.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

Re: [PATCHES] WITH RECUSIVE patches 0717

From

David Fetter

Date:

21 July 2008, 00:00:54

On Mon, Jul 21, 2008 at 08:19:35AM +0900, Tatsuo Ishii wrote:
> > > Thus I think we should avoid this kind of ORDER BY. Probably we should
> > > avoid LIMIT/OFFSET and FOR UPDATE as well.
> >
> > What of index-optimized SELECT max(...) ?
>
> Aggregate functions in a recursive term is prohibited by the
> standard. For example,
>
> WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT max(n) FROM x)
>   SELECT * FROM x;
>
> produces an error.

On the other side of UNION ALL, it's OK, right?  For example,

WITH RECURSIVE x(n) AS (
    SELECT max(i) FROM t
UNION ALL
    SELECT n+1 FROM x WHERE n < 20
)

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

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

Re: [PATCHES] WITH RECUSIVE patches 0717

From

Tatsuo Ishii

Date:

21 July 2008, 01:14:08

> On Mon, Jul 21, 2008 at 08:19:35AM +0900, Tatsuo Ishii wrote:
> > > > Thus I think we should avoid this kind of ORDER BY. Probably we should
> > > > avoid LIMIT/OFFSET and FOR UPDATE as well.
> > >
> > > What of index-optimized SELECT max(...) ?
> >
> > Aggregate functions in a recursive term is prohibited by the
> > standard. For example,
> >
> > WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT max(n) FROM x)
> >   SELECT * FROM x;
> >
> > produces an error.
>
> On the other side of UNION ALL, it's OK, right?  For example,
>
> WITH RECURSIVE x(n) AS (
>     SELECT max(i) FROM t
> UNION ALL
>     SELECT n+1 FROM x WHERE n < 20
> )

Yes, aggregate functions in the non-recursive term is allowed by the
standard.
--
Tatsuo Ishii
SRA OSS, Inc. Japan