Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
This task will explain how a select query containing a CONNECT BY clause can be remapped to a CTE expression.
We start with a generic SELECT. This can be part of a subquery as well. The whole mapping must go within that subquery.
SELECT select_list |
FROM table_expr |
WHERE where_cond |
START WITH start_cond |
CONNECT BY connect_expr |
[GROUP BY group_by_expr] |
[ORDER BY order_by_clause] |
a) First we must perform a splitting of the select_list
Based on experiments with Oracle's code, aggregation happens post CONNECT BY. All select items containing aggregation or window functions (SUM, MIN, MAX, OVER() etc.) must be separated from regular columns or pseudo-columns. From splitting, we generate a factored_select_list that does not contain aggregate functions and keep the original select_list for the final result set generation.
b) We must split the WHERE clause into Join Conditions and Filtering Conditions. See MDEV-13664 as to what these represent.
We can now define the CTE expression to perform the computation as follows:
WITH data_set AS |
(
|
SELECT factored_select_list -- As defined at point a) |
FROM table_expr -- This includes JOIN conditions in the FROM clause |
WHERE join_conditions_expr -- See MDEV-13664 |
), hierarchy_set AS |
(
|
(
|
SELECT factored_select_list -- Here we need special handling for CONNECT BY pseudo-columns |
-- For example, LEVEL starts as 1, SYS_CONNECT_BY_PATH starts with the connecting string + column etc. |
FROM data_set |
WHERE start_cond -- The conditions from START WITH to generate the Base Set (MDEV-13664) |
)
|
UNION ALL |
(
|
SELECT factored_select_list -- This needs special definitions for CYCLE and PATH but it is outside the scope of this MDEV. |
FROM data_set, hierarchy_set |
WHERE connect_expr |
-- For all expressions in the CONNECT BY clause, we transform the expressions containing PRIOR |
-- keyword to reference the hierarchy_set CTE recursively: |
-- For all expressions in the CONNECT BY clause, not containing the PRIOR keyword we reference the base_set table. |
-- Ex: PRIOR id = manager becomes hierarchy_set.id = base_set.id |
-- This requires special care when dealing with functions. |
-- This expression must also prevent cycles. (To Be Decided based on analysis how oracle Cycle detection actually works) |
)
|
), result_set AS |
(
|
SELECT factored_select_list |
FROM hierarchy_set |
WHERE filtering_conditions_expr -- See MDEV-13664 |
-- Here we need to perform any ORDER BY SIBLINGS (if present) |
-- If no ORDER BY SIBLINGS, we order by PATH Lexicographically. |
)
|
SELECT select_list -- This includes SUM functions. |
FROM result_set |
[ GROUP BY group_by_expr ] |
[ ORDER BY order_by_expr ] |
Note that this mapping does not include the details for getting CONNECT BY Pseudo-columns to work, nor does it include the details required for CYCLE detection, nor does it include the details required for ORDER SIBLINGS BY
Attachments
Issue Links
- is part of
-
-
- Open
-