"CompileError: Multiple, unrelated CTEs found with the same name" when using column property

[Adrian]

I'm trying to add a `deep_children_count` column property to one of my models.
As a regular property it works perfectly fine but I'd like to make it a column property so I don't have to spam extra queries if I need the counts for multiple objects.

So I tried this:

    cat_alias = db.aliased(Category)
    cte_query = (select([cat_alias.id, db.cast(array([]), ARRAY(db.Integer)).label('parents')])
                 .where(cat_alias.parent_id.is_(None) & ~cat_alias.is_deleted)
                 .cte('chains', recursive=True))
    parent_query = (select([cat_alias.id, cte_query.c.parents.op('||')(cat_alias.parent_id)])
                    .where((cat_alias.parent_id == cte_query.c.id) & ~cat_alias.is_deleted))
    cte_query = cte_query.union_all(parent_query)
    query = select([db.func.count()]).where(cte_query.c.parents.contains(array([Category.id])))
    Category.deep_children_count = column_property(query)

Unfortunately this fails with an exception when loading one of the objects:
CompileError: Multiple, unrelated CTEs found with the same name: u'chains'

I'm not sure why I end up with *multiple* CTEs and since it's a compile error I cannot look at the SQL it
tried to generate either...

[Mike Bayer]

On 06/09/2016 05:39 AM, Adrian wrote:
> I'm trying to add a `deep_children_count` column property to one of my
> models.
> As a regular property it works perfectly fine but I'd like to make it a
> column property so I don't have to spam extra queries if I need the
> counts for multiple objects.
>
> So I tried this:
>
> |
> cat_alias =db.aliased(Category)
> cte_query
> =(select([cat_alias.id,db.cast(array([]),ARRAY(db.Integer)).label('parents')])
> .where(cat_alias.parent_id.is_(None)&~cat_alias.is_deleted)
> .cte('chains',recursive=True))
> parent_query
> =(select([cat_alias.id,cte_query.c.parents.op('||')(cat_alias.parent_id)])
> .where((cat_alias.parent_id
> ==cte_query.c.id)&~cat_alias.is_deleted))
> cte_query =cte_query.union_all(parent_query)
> query

> =select([db.func.count()]).where(cte_query.c.parents.contains(array([Category.id])))

> Category.deep_children_count =column_property(query)
> |
>
>
> Unfortunately this fails with an exception when loading one of the objects:
> CompileError: Multiple, unrelated CTEs found with the same name: u'chains'
>
> I'm not sure why I end up with *multiple* CTEs and since it's a compile
> error I cannot look at the SQL it
> tried to generate either...

"name" is optional, it will generate an anoymous name if omitted. I'm
not sure what the problem is here but perhaps it will get a little
further that way and you'd be able to see what the problem is.

otherwise I'd need an MCVE for this in order to play with it. CTEs are
painful.



>
> --
> 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 sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Adrian]

I've already tried not specifying a name - in that case it's `anon_2` in the error.

Here's an MCVE: https://gist.github.com/ThiefMaster/593e5a78f08d6323eb1b88270256baa7

[Mike Bayer]

the deannotation step is making a clone of the CTE and not maintaining
all the linkages properly. Likely a bug but a really deep one I could
use some help on.

if you can confirm the query is correct with this patch:

diff --git a/lib/sqlalchemy/orm/properties.py
b/lib/sqlalchemy/orm/properties.py
index f3dce75..45b9e83 100644
--- a/lib/sqlalchemy/orm/properties.py
+++ b/lib/sqlalchemy/orm/properties.py
@@ -118,8 +118,9 @@ class ColumnProperty(StrategizedProperty):
"""
super(ColumnProperty, self).__init__()
self._orig_columns = [expression._labeled(c) for c in columns]
- self.columns = [expression._labeled(_orm_full_deannotate(c))
- for c in columns]
+ #self.columns = [expression._labeled(_orm_full_deannotate(c))
+ # for c in columns]
+ self.columns = [expression._labeled(c) for c in columns]
self.group = kwargs.pop('group', None)
self.deferred = kwargs.pop('deferred', False)
self.instrument = kwargs.pop('_instrument', True)

then we know it's strictly the visit-clone step. isolated test case
would need to be constructed, the style we're looking for would be along
the lines of what we see in
https://bitbucket.org/zzzeek/sqlalchemy/src/31a0da32a8af2503c6b94123a0e869816d83c707/test/sql/test_generative.py?at=master&fileviewer=file-view-default#test_generative.py-299
though perhaps the CTE specific tests would be in test/sql/test_cte.py.

[Adrian]

Yes, works fine with this change.

[Adrian]

Unless there'll be a release fixing this soon-ish: Is there any workaround that doesn't require patching the sqlalchemy to avoid the issue?
Otherwise I'd probably go for a hack like this:

@contextmanager
def dirty_hack():
    orig = sqlalchemy.orm.properties._orm_full_deannotate
    sqlalchemy.orm.properties._orm_full_deannotate = lambda x: x
    try:
        yield
    finally:
        sqlalchemy.orm.properties._orm_full_deannotate = orig

and then wrap the creation of the column property in this contextmanager. But I don't know whether skipping the deannotate step might cause other issues...

[Mike Bayer]

It's probably something small.   A bb issue would be targeted at 1.0.x if you want to put one up

--

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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.