Oracle CONNECT BY clause after GROUP BY clause

Question

I just came across this interesting article here , showing how to mimic wm_concat() or group_concat() in Oracle using hierarchical query and window functions:

 SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees FROM (SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp) GROUP BY deptno CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1; 

Although, I think this is not a very readable solution, it is quite interesting, especially because the CONNECT BY .. STARTS WITH appears after the GROUP BY . According to the specification , this should not be possible. I tried this using a simple query, but it works! The following two queries return the same results:

 -- wrong according to the specification: select level from dual group by level connect by level <= 2; -- correct according to the specification: select level from dual connect by level <= 2 group by level; 

Is this an undocumented function? Or just syntactic indifference for convenience? Or are these two statements subtly behaving differently?

Answer 1

I think this is just a minor syntax difference.

In particular, I think this is a mistake in the documentation. The syntax diagram for 8i implies that any order is supported. Nothing in reference 8i implies that order matters. But this diagram also implies that you can have multiple group_by_clause or hierarchical_query , which is wrong:

 --You can't group twice: ORA-01787: only one clause allowed per query block select level from dual connect by level <= 2 group by level group by level; 

My assumption is that when Oracle corrected the syntax diagram for 9i, they also forgot that the order may be different. Or maybe they intentionally left it, because it seems more logical to first perform the hierarchical part.

There are a few minor syntax changes that are undocumented. I do not think this means that they are not supported. Oracle probably regrets having so many weird options and wants it to at least look simple. For example, HAVING may appear before GROUP BY , many of the old parallel functions still work (but are ignored), etc. (That's why I always laugh when people say they are going to quickly "parse SQL" - good luck finding that out!)

Oracle 8i Syntax:

Oracle 9i Syntax:

Answer 2

Look at the execution plans. In my environment, they are identical, with the CONNECT BY operation being submitted to the HASH GROUP BY. So it seems that placing GROUP BY at first is just an odd syntax that gives the same result as a more natural order.

Technically, this is probably a mistake in the parser, because, as you say, the specification indicates that the hierarchical query clause should appear before the group-by clause. But it doesn't seem to have anything to do with how the request is executed.