trafodion
trafodion copied to clipboard
apache
[TRAFODION-3034] Support Oracle Hierarchy Query (Connect By)
This PR is the first patch to support Oracle Hierarchy query feature (CONNECT BY). In this PR, the feature is implemented as a new SQL utility, It is standalone, rather clear isolated with all other SQL functions, so the impact is minimal. In the long run, we should finish the ANSI recursive feature (recursive WITH) , and at that time, this feature can be considered to refactor to use that infrastructure. This is just the first phase of this feature.
The basic logic is simple: the utility will run a query to get all start values (specified by the START WITH clause), then it will construct queries to search for children of the root, and loop until no children can be found.
Oracle has 3 pseudo columns, to support the ISLEAF and CONNECT_BY_PATH, the utility will have to run a query for each parent, it will be rather slow. If a query doesn't have those two pseudo columns required, the utility will run in batch mode, for each iteration, get all children in one query. That will be much faster.
One can check the executor/TEST021 for how this feature works first.
This will be a long review process, there must be many places to be modified and enhanced, thank you all for help in advance.
Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2935/
Previous Test Aborted. New Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2936/
@anoopsharma00 @zellerh @sureshsubbiah @selvaganesang could you help to take a review?
I need a big rework of this patch. Please hold until I finish. The current implemention takes assumption there is only one table in the from_clause, which is not correct.
Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2961/
New Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2962/
New Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2965/
New Check Test Started: https://jenkins.esgyn.com/job/Check-PR-master/2966/
What are the security considerations for this features? Do standard grant privileges work find without changes? Could one possible example for using CONNECT BY it to traverse the privilege tree. Start with the grantor being the system and proceed to through the children (where grantee == grantor).
