[
https://issues.apache.org/jira/browse/TRAFODION-3034?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16576293#comment-16576293
]
ASF GitHub Bot commented on TRAFODION-3034:
-------------------------------------------
Github user traflm commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1688#discussion_r209260745
--- Diff: core/sql/optimizer/BindRelExpr.cpp ---
@@ -2505,7 +2505,62 @@ RelExpr *RelExpr::bindSelf(BindWA *bindWA)
if (bindWA->inViewWithCheckOption())
bindWA->predsOfViewWithCheckOption() += selectionPred();
}
+#if 0
+ ItemExpr *startWithTree = removeStartWithTree();
+ if (startWithTree) {
+ bindWA->getCurrentScope()->context()->inWhereClause() = TRUE;
+ startWithTree->convertToValueIdSet(getStartWith(), bindWA, ITM_AND);
+ bindWA->getCurrentScope()->context()->inWhereClause() = FALSE;
+
+ if (bindWA->errStatus()) return this;
+ // If this is an embedded insert, then subquery predicates are not
+ // allowed.
+ // For example: To handle this query and issue an error stating
+ // subqueries are not allowed in embedded inserts
+ //
+ // select a from (insert into t901t01 values(22,22,222))t(a,b,c)
+ // where t.a IN (select m from t901t03 where t901t03.m = 77);
+
+ if (getGroupAttr()->isEmbeddedInsert())
+ {
+ if (!getStartWith().isEmpty() && getStartWith().containsSubquery())
+ {
+ *CmpCommon::diags() << DgSqlCode(-4337);
+ bindWA->setErrStatus();
+ return this;
+ }
+ }
+ }
+
+ ItemExpr *connectByTree = removeConnectByTree();
+ if (connectByTree) {
+
+ bindWA->getCurrentScope()->context()->inWhereClause() = TRUE;
+ connectByTree->convertToValueIdSet(getConnectBy(), bindWA, ITM_AND);
+ bindWA->getCurrentScope()->context()->inWhereClause() = FALSE;
+
+ if (bindWA->errStatus()) return this;
+
+ // If this is an embedded insert, then subquery predicates are not
+ // allowed.
+ // For example: To handle this query and issue an error stating
+ // subqueries are not allowed in embedded inserts
+ //
+ // select a from (insert into t901t01 values(22,22,222))t(a,b,c)
+ // where t.a IN (select m from t901t03 where t901t03.m = 77);
+
+ if (getGroupAttr()->isEmbeddedInsert())
+ {
+ if (!getConnectBy().isEmpty() && getConnectBy().containsSubquery())
+ {
+ *CmpCommon::diags() << DgSqlCode(-4337);
+ bindWA->setErrStatus();
+ return this;
+ }
+ }
+ }
+#endif
--- End diff --
oops, I will remove these dead code.
> support Oracle 'Start with connect by' feature
> ----------------------------------------------
>
> Key: TRAFODION-3034
> URL: https://issues.apache.org/jira/browse/TRAFODION-3034
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: liu ming
> Assignee: liu ming
> Priority: Major
>
> Oracle support hierarchy search , or recursive query. Syntax is 'connect by,
> start with'
>
> If a table contains hierarchical data, then you can select rows in a
> hierarchical order using the hierarchical query clause (START WITH
> condition1) CONNECT BY condition2
> The START WITH clause is optional and specifies the rows athat are the
> root(s) of the hierarchical query. If you omit this clause, then Oracle uses
> all rows in the table as root rows. The START WITH condition can contain a
> subquery, but it cannot contain a scalar subquery expression.
> The CONNECT BY clause specifies the relationship between parent rows and
> child rows of the hierarchy. The connect_by_condition can be any condition,
> however, it must use the PRIOR operator to refer to the parent row.
> Restriction on the CONNECT BY clause: The connect_by_condition cannot contain
> a regular subquery or a scalar subquery expression.
> The PRIOR operator to refer to the parent row can be used as following:
> Sample query on the employees table:
> SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY
> PRIOR employee_id = manager_id;
> shows employee_id, last_name, manager_id and level in the tree for the
> employee hierarchy.
> In Oracle 9i a new feature for hierarchical queries is added:
> sys_connect_by_path It returns the path of a column value from root to node,
> with column values separated by char for each row returned by CONNECT BY
> condition. Both column and char can be any of the datatypes CHAR, VARCHAR2,
> NCHAR, or NVARCHAR2. The string returned is of VARCHAR2 datatype and is in
> the same character set as column.
> Examples
> The following example returns the path of employee names from employee
> Kochhar to all employees of Kochhar (and their employees):
> SELECT LPAD(' ', 2*level-1)|| SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM
> employees CONNECT BY PRIOR employee_id = manager_id;
> In Oracle 10 3 new features for hierarchical queries are added:
> h3. connect_by_root
> CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical
> queries. When you qualify a column with this operator, Oracle returns the
> column value using data from the root row. This operator extends the
> functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.
> Restriction on CONNECT_BY_ROOT: You cannot specify this operator in the START
> WITH condition or the CONNECT BY condition. Example query:
> SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1
> "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE
> LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id;
> h3. connect_by_isleaf
> The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of
> the tree defined by the CONNECT BY condition. Otherwise it returns 0. This
> information indicates whether a given row can be further expanded to show
> more of the hierarchy. Example
> SELECT employee_id, last_name, manager_id, connect_by_isleaf "IsLeaf" FROM
> employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id =
> manager_id;
> h3. connect_by_iscycle
> The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child
> which is also its ancestor. Otherwise it returns 0.
> You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE
> parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the
> results of a query that would otherwise fail because of a CONNECT BY loop in
> the data.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
