Derby
  1. Derby
  2. DERBY-11

Recursive SQL and WITH A(col list) as (Select col list From Table List) Support.

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      ANY.
    • Urgency:
      Low

      Description

      Right now, in Derby, there is no way to define a temporary Result Set to use in subsequent statements. This makes complicated concepts to be expressed in SQL either very very complicated and lengthy or simply impossible.

      DB2 has a simple and useful syntax using a "WITH" statement. It would be nice if Derby can support this. An example is as below:

      WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 WHERE condition)
      SELECT T2.COL3 FROM T2, A WHERE condition2

      It can be extended to include more WITH clauses:

      WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 WHERE condition)
      WITH B(COL3) as (SELECT COL3 FROM T1,A WHERE condition2)
      SELECT T2.COL5, B.COL3 FROM T2, A, B WHERE condition3

      and so on.

      Note that as the following example shows, the use of table correlation name in another subselect is NOT supported and cannot be a workaround:

      SELECT cols FROM (SELECT cols FROM T1) as A, (SELECT cols FROM T2,A where A relates to T2) as B where condition

      Another interesting aspect of these WITH clauses is their ability to make RECURSIVE SQL possible. In below example, definition of A includes a select from ITSELF:

      WITH A(COL1, COL2) as (SELECT COL1, COL2 FROM T1 UNION ALL SELECT COL1, COL2 FROM T2, A where A.COL1=T2.COLN)
      SELECT COL1, COL2 FROM A WHERE condition2

      Recursion with a WITH clause relies on a specific syntax. Consult DB2 documentation for more info about Recursion and WITH clause.

      Recursion is an important facility and it would be very very useful to have it in Derby.

      Recursion comes in very handy when a single table holds a hierarchy of rows that are related to each other with parent-child relationships of N-Levels where N is large or unknown in which case non-recursive solutions are either impossible or require complicated code at the Client side. With recursion possible at the SQL level, many problems can be reduced to single SQL statements instead of lengthy application code.

      Regards,
      Suavi Demir

        Activity

        Hide
        Rick Hillegas added a comment -

        Some overviews of recursive queries can be found here:

        http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL

        http://walkingoncoals.blogspot.com/2009/12/fun-with-recursive-sql-part-1.html

        http://gennick.com/database/understanding-the-with-clause

        The 2011 SQL Standard provides language for recursive queries as follows:

        Recursive queries are defined by

        T121) WITH (excluding RECURSIVE ) in query expression

        T122) WITH (excluding RECURSIVE) in subquery

        T131) Recursive query

        T132) Recursive query in subquery

        Relevant sections of part 2 include

        1) The withClause as defined in section 7.13 (query expression).

        2) The searchOrCycleClause as defined in section 7.14

        I believe that both Oracle (as of 11g) and DB2 (as of some version) support the SQL Standard syntax. Or at least claim to support it.

        The following looks to me like it might be a legal, example SQL Standard recursive query:

        WITH RECURSIVE emp_hier (emp_id, mgr_id, level) AS
        (
        SELECT a.emp_id, a.mgr_id, 0 
        FROM   employee a
        WHERE  a.emp_id = 123
        UNION ALL
        SELECT b.emp_id, b.mgr_id, c.level+1 
        FROM   employee b,
               emp_hier c
        WHERE  b.mgr_id = c.emp_id
        )
        SELECT e.emp_title, e.emp_id, e.mgr_id, h.level
        FROM   employee e,
               emp_hier h
        WHERE  e.emp_id = h.emp_id
          AND  e.emp_id <> 123;
        

        My gut feeling is that this feature would not entail more work than has been involved in implementing deferrable constraints (DERBY-532) or the MERGE statement (DERBY-3155).

        Show
        Rick Hillegas added a comment - Some overviews of recursive queries can be found here: http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL http://walkingoncoals.blogspot.com/2009/12/fun-with-recursive-sql-part-1.html http://gennick.com/database/understanding-the-with-clause The 2011 SQL Standard provides language for recursive queries as follows: Recursive queries are defined by T121) WITH (excluding RECURSIVE ) in query expression T122) WITH (excluding RECURSIVE) in subquery T131) Recursive query T132) Recursive query in subquery Relevant sections of part 2 include 1) The withClause as defined in section 7.13 (query expression). 2) The searchOrCycleClause as defined in section 7.14 I believe that both Oracle (as of 11g) and DB2 (as of some version) support the SQL Standard syntax. Or at least claim to support it. The following looks to me like it might be a legal, example SQL Standard recursive query: WITH RECURSIVE emp_hier (emp_id, mgr_id, level) AS ( SELECT a.emp_id, a.mgr_id, 0 FROM employee a WHERE a.emp_id = 123 UNION ALL SELECT b.emp_id, b.mgr_id, c.level+1 FROM employee b, emp_hier c WHERE b.mgr_id = c.emp_id ) SELECT e.emp_title, e.emp_id, e.mgr_id, h.level FROM employee e, emp_hier h WHERE e.emp_id = h.emp_id AND e.emp_id <> 123; My gut feeling is that this feature would not entail more work than has been involved in implementing deferrable constraints ( DERBY-532 ) or the MERGE statement ( DERBY-3155 ).
        Hide
        Kathey Marsden added a comment -

        I think the first step would be to research the SQL Standard to determine if there is a SQL standard syntax for this. Rick indicated that DB2 and Oracle have different approaches. Hopefully there is a standard approach so that we can implement it in Derby.

        Show
        Kathey Marsden added a comment - I think the first step would be to research the SQL Standard to determine if there is a SQL standard syntax for this. Rick indicated that DB2 and Oracle have different approaches. Hopefully there is a standard approach so that we can implement it in Derby.
        Hide
        Dileepa Sisila Chandrasekera added a comment -

        I like to contribute and fix this issue as a final year student in Computer Science and Engineering Department in University of Moratuwa, Sri Lanka. Could you provide information on which packages should I refer regarding to this issue. Up to now, I have successfully built the source package of derby.

        best regards,
        Dileepa Sisila Chandrasekera,
        Department of Computer Science,
        University of Moratuwa.

        Show
        Dileepa Sisila Chandrasekera added a comment - I like to contribute and fix this issue as a final year student in Computer Science and Engineering Department in University of Moratuwa, Sri Lanka. Could you provide information on which packages should I refer regarding to this issue. Up to now, I have successfully built the source package of derby. best regards, Dileepa Sisila Chandrasekera, Department of Computer Science, University of Moratuwa.
        Hide
        Rick Hillegas added a comment -

        Oracle and DB2 both provide recursive SQL support. However, the approaches are different. See http://www.ibm.com/developerworks/data/library/techarticle/0307steinbach/0307steinbach.html

        Show
        Rick Hillegas added a comment - Oracle and DB2 both provide recursive SQL support. However, the approaches are different. See http://www.ibm.com/developerworks/data/library/techarticle/0307steinbach/0307steinbach.html

          People

          • Assignee:
            Unassigned
            Reporter:
            Ali Demir
          • Votes:
            5 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

            • Created:
              Updated:

              Development