Bug #2341 CONNECT BY PRIOR
Submitted: 9 Jan 2004 18:00 Modified: 7 Jan 2020 9:33
Reporter: [ name withheld ] Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:Next One OS:Any (any)
Assigned to: CPU Architecture:Any

[9 Jan 2004 18:00] [ name withheld ]
Description:
Hi -

I see that "Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures." is the top feature in your "1.6.4 New Features Planned for the Near Future".

But this has been the top feature in this list for quite a while.  When will this be incorporated?  I need it desperately!

Thanks,

Dylan MacDonald

How to repeat:
Hi -

I see that "Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures." is the top feature in your "1.6.4 New Features Planned for the Near Future".

But this has been the top feature in this list for quite a while.  When will this be incorporated?  I need it desperately!

Thanks,

Dylan MacDonald

Suggested fix:
Hi -

I see that "Oracle-like CONNECT BY PRIOR ... to search tree-like (hierarchical) structures." is the top feature in your "1.6.4 New Features Planned for the Near Future".

But this has been the top feature in this list for quite a while.  When will this be incorporated?  I need it desperately!

Thanks,

Dylan MacDonald
[4 Oct 2005 17:19] Hartmut Holzgraefe
see also worlog entry 576
[12 Jan 2006 5:27] Rene Pitayataratorn
Hi,

In support of your feature request I would like to mention that CONNECT BY PRIOR / Oracle style or SELECT RECURSIVE / DB2 style recursive query support has been promised as a feature since 4.1 (s. also http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/TODO_future.html). 

Numerous authors have contributed theory and work arounds (incl. Celkos. http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=697912) ranging from Nested Sets (http://www.klempert.de/php/nested_sets/) to PHP classes. However, the performance aspect of the approach - nothing can be better than including that feature than a SQL syntax support with a clause limiting the recursion depth incl. as standard feature of the distribution.

All major RDBMS have support for this meanwhile.

Cheers,

Rene
[1 Nov 2009 13:22] bill barsch
the solution to order by depth on recursive querys with hierarchy data
is stored function like this:
obs: it´s not recursive but do the work!
***********************************************************************
DELIMITER $$

CREATE FUNCTION `depth_of_node`(v_nodeID integer) RETURNS INT
    READS SQL DATA
BEGIN

  DECLARE v_depth int;
  DECLARE v_parentNodeID int;
  DECLARE v_currentNode int;
  declare v_The_End BOOL default FALSE;
  declare continue handler for not found set v_The_End := TRUE;

  SET v_currentNode = v_nodeID;
  SET v_depth = -1;

  WHILE (v_The_End = false) DO

    SELECT table_name.PARENTNODE into v_parentNode
    FROM table_name
    WHERE table_name.nodeID = v_currentNode;

    SET v_currentNode = v_parentNode;
    SET v_depth = v_depth + 1;

  end while;

  return v_depth;

END $$
DELIMITER ;
**************************************************************************

use it as:

SELECT table_name.NodeName,
depth_of_node(table_name.nodeID) as depth
FROM table_name
LEFT JOIN table_name t2 ON (table_name.parentID = t2.nodeID)
WHERE table_name.COL_NAME = "xxxxxx"
AND table_name.COL_NAME2 = "xxxxxx"
ORDER BY depth_of_node(table_name.nodeID)

It will result some like this:
**************************************************************
Node Name|             Depth
*************|***********************************************
A              |               1
B              |               2
C              |               3
D              |               4
*************************************************************

hope it help.
[20 Dec 2016 11:52] yvs ppt
Is it possible to get informations about roadmap of the development of this old request ?

As "connect by" is not standard syntax defined by SQL:1999/SQL:2003, I think  the statement "WITH RECURSIVE" could be used.
[7 Jan 2020 9:33] Norvald Ryeng
Posted by developer:
 
Common table expressions (CTEs), including recursive CTEs, were implemented in MySQL 8.0.1 and covers the same use case (https://dev.mysql.com/doc/refman/8.0/en/with.html). We're not going to implement CONNECT BY PRIOR.