Re: [PATCHES] WITH RECUSIVE patches 0717 - Mailing list pgsql-hackers

> 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;