Remove restrictions in recursive query

Hi,

I'm confused about what we should allow in a recursive query. For example,
in the following query:

WITH RECURSIVE t1 AS ( SELECT 1 UNION SELECT generate_series(2,3) FROM t1
ORDER BY 1 DESC) SELECT * FROM t1 ;

The parser attaches the "order by" clause to the "union" operator, and then
we error out with the following message: "ORDER BY in a recursive query is
not implemented"

The comment in the code (parser_cte.c:900) says "Disallow ORDER BY and
similar decoration atop the UNION". Then, if we wrap the recursive clause
around parentheses:

WITH RECURSIVE t1 AS ( SELECT 1 UNION (SELECT generate_series(2,3) FROM t1
ORDER BY 1 DESC)) SELECT * FROM t1 ;

It works as expected. So, do we support the ORDER BY in a recursive query
or not? If the answer is yes, I suggest one of the following modifications:
1. Change the error message to something like "ORDER BY at the top level of
a recursive query is not implemented. HINT: wrap the respective statement
around ()"
2. (preferred) Modify the parser or simply remove these checks to allow the
first query.

If the answer is no, then there is a minor bug that allows us to bypass the
check. Even though the ORDER BY happens inside the working table, I think
it can be a useful feature combined with LIMIT and OFFSET.

There is a similar restriction regarding GROUP BY. But in this case, the
error message is very clear and it is consistent with the comment in the
code. I suggest removing this restriction as well in order to improve
PostgreSQL's capabilities to process graph data. For example, counting the
number of paths in a DAG can be computed more efficiently using an
aggregation in each step.

I don't know what the standard says about this, but it certainly does not
allow DISTINCT ON in the recursive query, while PostgreSQL does support it.
So, we could eventually skip the specs in this case to be more consistent
since a DISTINCT ON has many similarities with a GROUP BY.

I did some tests, and it is enough to remove the check regarding the GROUP
BY. The machinery to perform the GROUP BY in a recursive clause is already
there.

Of course, if it is the case, I would be glad to send a patch.

Best regards,
Renan Fonseca