[Sqlalchemy-tickets] Issue #3220: Several small issues with Oracle recursive queries (zzzeek/sqlalchemy)

[Mariano M. <iss...@bi...>]

New issue 3220: Several small issues with Oracle recursive queries
https://bitbucket.org/zzzeek/sqlalchemy/issue/3220/several-small-issues-with-oracle-recursive

Mariano Mara:

Hi Mike, I'm trying to use the same CTE I have working for PG with Oracle (since Oracle now supports CTE), however there are several small issues that prevents the use of SQLAlchemy's CTE with Oracle as transparently as it is possible with PG (I need to make it work with SQL Server too but so far I have not tested it). 

1- The first issue is regarding the **RECURSIVE** keyword. Here is a simple script that includes everything required to reproduce the problem.


```
#!python

from sqlalchemy.orm import aliased
from sqlalchemy import String, Integer, Column, func, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('oracle://scott:tiger@localhost:1521/xe', echo=True)
Base = declarative_base()
class Part(Base):
    __tablename__ = 'part'
    part = Column(String(200), primary_key=True)
    sub_part = Column(String(200), primary_key=True)
    quantity = Column(Integer)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)

incl_alias = aliased(included_parts, name="pr1")
parts_alias = aliased(Part, name="p")
included_parts = included_parts.union_all(
    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\
            filter(parts_alias.part==included_parts.c.sub_part)
    )

q = session.query(
        included_parts.c.sub_part,
        func.sum(included_parts.c.quantity).
            label('total_quantity')
    ).\
    group_by(included_parts.c.sub_part)
session.execute(q)
```
if you try to execute this version, you will get the following error (because Oracle does not like the word RECURSIVE):

```
#!python

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00905: missing keyword
 'WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT part.sub_part AS sub_part, part.part AS part, part.quantity AS quantity \nFROM part \nWHERE part.part = :part_1 UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM part p, included_parts \nWHERE p.part = included_parts.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part' {'part_1': 'our part'}
```

so, of course, we change the CTE (even if we are doing a recursive query) from


```
#!python

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)
```

to


```
#!python

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=False)
```

but now we get


```
#!python

sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-32039: recursive WITH clause must have column alias list
 'WITH included_parts AS \n(SELECT part.sub_part AS sub_part, part.part AS part, part.quantity AS quantity \nFROM part \nWHERE part.part = :part_1 UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM part p, included_parts \nWHERE p.part = included_parts.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part' {'part_1': 'our part'}
```
because we are now missing the list of columns required.

2- the second issue deals with some special keywords oracle has in order to provide ordering and to prevent cycle issues within a recursive query. The complete spec is available [here](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#BCEDDGGE) and I found these [two](http://rwijk.blogspot.jp/2009/11/recursive-subquery-factoring.html) [examples](http://rajeshwaranbtech.blogspot.com.ar/2012/12/recursive-with-clause-to-implement.html) floating around. The **cycle** option is specially useful since it can prevent circular errors in the recursive relation.  In PG you can do some magic with *arrays*, *rows* and *any* but in Oracle it is really hard to prevent circular issues unless you use the suggested features.

To tell you the thruth it looks like a pretty troublesome extension to add to sqlalchemy but I guess it could be great if at least we can input some text() condition with this .

Thanks for your patience reading this and I am available for anything you need me to test in case you think this issue is worth your time.

Mariano