[MDEV-13479] CONNECT BY: Parser support Created: 2017-08-09  Updated: 2018-12-03

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: Compatibility

Issue Links:
PartOf
is part of MDEV-13428 Oracle-compatible recursive queries w... Open

 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

 Comments   
Comment by Sergei Petrunia [ 2017-08-09 ]

Re the question of "Subqueries in CONNECT BY". I took the dataset from MDEV-13473 and tried this:

create table lookup_table (a NUMBER);
 
insert into lookup_table select distinct employee_id from employees;


SQL> SELECT 
  employee_id, last_name, manager_id
 
FROM employees
 
START WITH employee_id IN (201,202)
 
CONNECT BY 
  PRIOR manager_id = employee_id AND 
  employee_id IN (select a from lookup_table);

uncorrelated subquery is accepted.

SQL> SELECT 
  employee_id, last_name, manager_id
 
FROM employees
 
START WITH employee_id IN (201,202)
 
CONNECT BY 
  PRIOR manager_id = employee_id AND 
  exists(select 1 from lookup_table where a=employee_id);

correlated subquery is accepted, too.

Subquery may have outside references but they may not use the "PRIOR" operator
(which actually makes name resolution easier)

SQL> SELECT 
  employee_id, last_name, manager_id
 
FROM employees
 
START WITH employee_id IN (201,202)
 
CONNECT BY 
  PRIOR manager_id = employee_id AND 
  exists(select 1 from lookup_table where a=prior manager_id);
 
  2    3    4    5    6    7    exists(select 1 from lookup_table where a=prior manager_id)
 
         *
 
ERROR at line 7:
 
ORA-01788: CONNECT BY clause required in this query block

So, I wasn't able to figure out what is the limitation in question.

Comment by zhangyuan [ 2017-08-11 ]

I think the keyword LEVEL and START should be change from Non-reserved to reserved, otherwise, there will be many reduce/reduce conflicts.

Comment by Igor Babaev [ 2017-08-18 ]

The problem with the conflict of START is because START can be considered as an alias
name for the last table reference in the FROM list. Yet if we parse START WITH on lexical level and use one token for this sequence we don't have a conflict. The same is applied to CONNECT BY when START... is not used.

Generated at Thu Feb 08 08:05:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.