column_property() does not properly alias columns inside func.count() in eagerloads

[Former user]

According to the documentation, a correct column_property to calculate a count uses a select(func.count(table.c.somecol), <clause>).correlate(other_table).

If the mapper in which we define the column_property is eagerloaded, the full select is aliased (table goes usually to "table_1") in all places but inside the func.count(), so the SQL is invalid.

Tested on PostgreSQL and SQLite.

Attached is a test case which exercises the bug.

Regards,

Raul Garcia.

[Former user]

Test case which exercises the bug

[Mike Bayer]

haven't checked yet but probably a dupe of #949. see the child tickets there for current methods of using correlations with eagerloads. they are doable but have some quirks that need to be worked around.

[Mike Bayer]

not a dupe. this patch:

Index: lib/sqlalchemy/sql/expression.py
===================================================================
--- lib/sqlalchemy/sql/expression.py    (revision 4720)
+++ lib/sqlalchemy/sql/expression.py    (working copy)
@@ -3066,7 +3066,7 @@
         """return child elements as per the ClauseElement specification."""

         return (column_collections and list(self.columns) or [+ \
-            list(self._froms) + \
+            self._raw_columns + list(self._froms) + \
             [x for x in (self._whereclause, self._having, self._order_by_clause, self._group_by_clause) if x is not None](]))

     def column(self, column):

repairs the error message, but column is still not targeted in the rowset.

[Mike Bayer]

after more research, it still might be related to #949. bug is fixed in e02114ce4975714454feacc953cb955aaedb6596 and I don't think I'm going to backport this into 0.4 since it makes a significant change in eagerload's workings. we dont want the addresses.id column to be aliased here; the subquery on User should be contained to just the user table; the alias of addresses is something separate.

[Former user]

zzzeek wrote:

bug is fixed in e02114ce4975714454feacc953cb955aaedb6596 and I don't think I'm going to backport this into 0.4 since it makes a significant change in eagerload's workings.

The bug is only present in the 0.5 version (in 0.4 it worked like a charm, in fact, in f865f0a88da54707a9b33dfb121025dfab686009 the bug was not present). So the fix must be only for 0.5 (I think I set the 'milestone' attribute of the ticket to indicate it).

BTW, e02114ce4975714454feacc953cb955aaedb6596 works ok. Thanks a lot :)

Regards,

Raul Garcia.

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)