GitHub user traflm opened a pull request:
https://github.com/apache/trafodion/pull/1688
[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.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/traflm/trafodion TRAFODION-3034
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1688.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1688
----
commit 5fb8f21f20c397e077b2f7a8f96a7be9819f4883
Author: Liu Ming <ovis_poly@...>
Date: 2018-06-01T22:44:58Z
[TRAFODION-3034] add parser changes
commit b29f4ddb417b087a3c888b17e9fae2ee6b060828
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-17T09:21:17Z
basic work finish
commit 88e8b20d91ed7093ea06448a64bcdc3090d91ba9
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-21T09:56:44Z
fix parser, add loop detection
commit 9fb3a5943f6440540b4894bc554d94b317fd9ece
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-23T05:28:08Z
refactor the code, next need to add CQD and where clause support
commit b875ec3cdaef2c33cc3d127c24e62be478a78781
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-25T04:22:21Z
support where clause, next add CQD to control memory usage
commit 1b853c5da8210d77bd8d84dac374e57a67e38bef
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-26T05:00:23Z
support where clause, next add regression test
commit f20b63c875f3a56ba3da1a83c2f1e0f255b29b71
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-27T12:03:44Z
support both where clause and order by, next add test cases
commit 5757f25e9c2fa91b25751e24f28b04fbf903df4e
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-28T02:54:56Z
add test case, next to rebase and try to support is_cycle
commit 73a175a19c4f77e2af761ecc2a0b01779f9b7175
Author: Liu Ming <ovis_poly@...>
Date: 2018-07-28T05:15:14Z
Merge branch 'master' of git://git.apache.org/trafodion into TRAFODION-3034
Conflicts:
core/sql/generator/GenRelExeUtil.cpp
core/sql/optimizer/RelExeUtil.h
core/sql/sqlcomp/DefaultConstants.h
commit 1b32b6b27b7ba4686b174c41a349f320807221fe
Author: Liu Ming <ovis_poly@...>
Date: 2018-08-04T10:44:33Z
support PATH, next try to support is leaf
commit 81ef30d0e97797e033522b711f1aeb5ba359cd94
Author: Liu Ming <ovis_poly@...>
Date: 2018-08-05T09:43:03Z
add ISLEAF support, but need to wait for another enhancement
commit d781d881ad400e75fcd765d1856bdb509e2c6adb
Author: Liu Ming <ovis_poly@...>
Date: 2018-08-07T20:48:56Z
finish is leaf
commit cc9057d27aec9d0e5413330f72cc8df932103e7d
Author: Liu Ming <ovis_poly@...>
Date: 2018-08-07T20:49:08Z
Merge branch 'master' of git://git.apache.org/trafodion into TRAFODION-3034
commit 8ae2ed9c5fcbb1ccb47f6b952438dedc06b0d90e
Author: Liu Ming <ovis_poly@...>
Date: 2018-08-10T05:27:13Z
first phase finished
----
---
