Hello.
Sorry for the long delay. I finally had enough time to produce a minimal
self-contained regression. The attached file produces the following SQL:
WITH RECURSIVE
q_cte(partner_id, max_depth) AS (
SELECT
partner.id AS partner_id,
1 AS max_depth
FROM partner
WHERE partner.sponsor_id IS NULL
UNION ALL
SELECT
partner_alias.id AS partner_id,
max_depth + 1 AS max_depth
FROM
partner AS partner_alias,
q_cte AS q_cte_alias
WHERE partner_alias.sponsor_id = q_cte_alias.partner_id
)
SELECT
q_cte.partner_id AS q_cte_partner_id, -- suspicious
partner.sponsor_id AS partner_sponsor_id
FROM
q_cte
JOIN partner ON q_cte.partner_id = q_cte.partner_id -- BUG
I would expect this (q_cte part is correct):
WITH RECURSIVE
q_cte(partner_id, max_depth) AS (
...
)
SELECT
partner.id AS partner_id,
partner.sponsor_id AS partner_sponsor_id
FROM
q_cte
JOIN partner ON q_cte.partner_id = partner.id
The bug is somehow related to the use of select_from().
Hope this helps,
Ladislav Lenart
On 10.5.2013 22:04, Michael Bayer wrote:
>
> On May 10, 2013, at 3:03 PM, Ladislav Lenart <[email protected]> wrote:
>
>> Hello.
>>
>> My main concern was that the query creates a cartesian product and I thought
>> the
>> warning might have something to do with it. It haven't. The problem is
>> related
>> to the use of select_from():
>>
>> q = session.query(cls, PersonalContact).select_from(q_cte_union)
>> q = q.join(cls, cls.id == q_cte_union.c.partner_id)
>>
>> This part renders the following SQL:
>>
>> SELECT *
>> FROM
>> q_cte
>> JOIN partner ON q_cte.partner_id = q_cte.partner_id
>>
>> As you can see, the JOIN condition is wrong though I have no idea why. The
>> following works, however:
>>
>> q = session.query(cls)
>> q = q.add_entity(PersonalContact)
>> q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
>>
>> Is my usage of select_from() bad for some reason? Do you know what's going
>> on? I
>> can provide you a fully runnable test case if you still need it (on Monday).
>> Note also that I use SA 0.7.9. Is this fixed in later versions?
>
> I've no idea why you're getting it without more context - if everything is
> generated from a query(), typically the "labels" are already applied, with
> the exception of some operations such as subquery() (maybe cte? not sure, I'd
> need to try it, hence an already existing example saves me lots of time
> tinkering around trying to guess what you're doing).
>
> Things have been improved in 0.8 regarding same-named columns but that only
> applies when the labels are already present.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
# coding=utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import literal_column
from sqlalchemy.orm.util import aliased
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.orm import relationship
Base = declarative_base()
session = None
class Partner(Base):
__tablename__ = 'partner'
id = Column(Integer(), primary_key=True)
sponsor_id = Column(Integer(), ForeignKey('partner.id', ondelete='SET NULL'))
sponsor = relationship('Partner', primaryjoin='Partner.sponsor_id == Partner.id', remote_side='Partner.id', uselist=False)
def find_subtree(self, max_depth=None):
cls = self.__class__
i0 = literal_column('1').label('max_depth')
q_base = session.query(cls.id.label('partner_id'), i0).filter(cls.sponsor_id == self.id)
q_cte = q_base.cte(name='q_cte', recursive=True)
q_cte_alias = aliased(q_cte, name='q_cte_alias')
partner_alias = aliased(cls, name='partner_alias')
i1 = literal_column('max_depth + 1').label('max_depth')
q_rec = session.query(partner_alias.id.label('partner_id'), i1)
q_rec = q_rec.filter(partner_alias.sponsor_id == q_cte_alias.c.partner_id)
if max_depth is not None:
q_rec = q_rec.filter(q_cte_alias.c.max_depth < max_depth)
q_cte_union = q_cte.union_all(q_rec)
q = session.query(cls).select_from(q_cte_union)
q = q.join(cls, cls.id == q_cte_union.c.partner_id)
# q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id)
return q
def main():
global session
conn_string = 'postgresql+psycopg2://lada:heslo@localhost:5433/zfp_xxx'
engine = create_engine(conn_string, echo=True)
session = sessionmaker(bind=engine, autoflush=False)()
Base.metadata.bind = engine
Base.metadata.create_all()
root = Partner()
session.add(root)
q = root.find_subtree()
print q
if __name__ == '__main__':
main()
