Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
This is a subtask about providing parser support in CONNECT BY.
A decision from the meeting on Aug, 7th: we only need to support the CONNECT
BY syntax in the Oracle-compatible parser. There is no need to have it in the
main parser.
Extension for the SELECT syntax
[ START WITH start_cond ] CONNECT BY [NOCYCLE] connect_cond |
Also
- Expressions in the connect_cond above may use PRIOR expr operator
- Expressions other than START WITH may use CONNECT_BY_ROOT operator
- LEVEL, CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF pseudo-columns may be used
- SYS_CONNECT_BY_PATH() function may be used
Adding START WITH / CONNECT BY syntax
START WITH / CONNECT BY syntax does not seem to conflict with anything in the grammar.
Adding the new pseudo-columns
For pseudo-columns, there are two choices:
- include them in the grammar. Will this mean they will be new reserved words? There is generally a pushback against adding new reserved words. What does the standard say?
- Leave the grammar as is. As far as bison is concerned "LEVEL" will be the same as "table_a_column_b". Then, name resolution function should recognize where LEVEL is allowed and change column reference into a pseudo-column reference.
The first one is preferred.
Adding the operators
PRIOR and CONNECT_BY_ROOT are the new unary operators (will have to add
them to the grammar).
These operators can only be used where the context allows it.
Adding ORDER SIBLINGS BY support
In a SELECT with an ORDER BY clause, ORDER BY clause may have the ORDER SIBLINGS BY variant.
Unresolved Questions
Subqueries in CONNECT BY
From Oracle Documentation:
- The CONNECT BY condition cannot contain a subquery.
- Both the CONNECT BY condition and the PRIOR expression can take the form of an uncorrelated subquery. However, the PRIOR expression cannot refer to a sequence. That is, CURRVAL and NEXTVAL are not valid PRIOR expressions.
These 2 statements seem to be contradictory. Which one is true?
See also
- MDEV-13587: CONNECT BY: Post-parse data structures
Attachments
Issue Links
- is part of
-
MDEV-13428 Oracle-compatible recursive queries with CONNECT BY
- Open