<p dir="auto"><strong>Migrated issue, originally created by Andrey Semenov</strong></p>
<p dir="auto"><a href="https://gist.github.com/SantjagoCorkez/db207a7b533d1d6f05ae">https://gist.github.com/SantjagoCorkez/db207a7b533d1d6f05ae</a></p>
<p dir="auto">When providing a ColumnElement itself into .group_by()/.order_by() the query compiler does not automatically quote the reference to that producing a query that in some circumstances becomes invalid (for example in case there are columns with the same name as the ColumnElement's label within the query).</p>
<p dir="auto">This became an issue at 1.0.0 (and not resolved in 1.0.1)</p>
<p dir="auto"><strong>Migrated issue, originally created by petergrace (<a href="https://github.com/petergrace">@petergrace</a>)</strong></p>
<p dir="auto">I posted a stackoverflow question about this and someone mentioned I should also post the issue here since 1.0.0 is still in beta.  You can see the nicely-formatted post here: <a href="http://stackoverflow.com/questions/29212205/in-sqlalchemy-group-by-on-column-property-no-longer-works-in-1-0-0b1" rel="nofollow">http://stackoverflow.com/questions/29212205/in-sqlalchemy-group-by-on-column-property-no-longer-works-in-1-0-0b1</a></p>
<p dir="auto">Here's a dump of the text of that question:</p>
<p dir="auto">A change from 0.9.9 to 1.0.0b1 in the query functionality is causing me some heartburn. I have a group_by clause that uses a column_property.</p>
<p dir="auto">In 0.9.9, the generated query reproduces the calculated value in GROUP BY by actually calculating the value again. In 1.0.0b1, the calculation is wrapped in an anon_1, and MSSQL won't let you group_by a named value for a calculated field.</p>
<p dir="auto">Is there some way to revert to the old behavior without requiring a specific version?</p>
<p dir="auto">The below code generates the following SQL in 0.9.9:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT 
    count(cdr_extended.[UniqueID]) AS [CallCount],
    sum(cdr_extended.[Duration]) AS [TotalSeconds], 
    ext_map.[FName] + ' ' + ext_map.[LName] AS anon_1 
FROM cdr_extended, ext_map 
WHERE 
    (ext_map.exten = cdr_extended.[Extension] 
        OR ext_map.prev_exten = cdr_extended.[Extension]) 
    AND cdr_extended.[StartTime] &gt; '2015-01-01' 
    AND cdr_extended.[Extension] IN ('8297') 
GROUP BY ext_map.[FName] + ' ' + ext_map.[LName]
DESC
"><pre class="notranslate"><code class="notranslate">SELECT 
    count(cdr_extended.[UniqueID]) AS [CallCount],
    sum(cdr_extended.[Duration]) AS [TotalSeconds], 
    ext_map.[FName] + ' ' + ext_map.[LName] AS anon_1 
FROM cdr_extended, ext_map 
WHERE 
    (ext_map.exten = cdr_extended.[Extension] 
        OR ext_map.prev_exten = cdr_extended.[Extension]) 
    AND cdr_extended.[StartTime] &gt; '2015-01-01' 
    AND cdr_extended.[Extension] IN ('8297') 
GROUP BY ext_map.[FName] + ' ' + ext_map.[LName]
DESC

</code></pre></div>
<p dir="auto">However, in 1.0.0, it produces this code:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT 
    count(cdr_extended.[UniqueID]) AS [CallCount],
    sum(cdr_extended.[Duration]) AS [TotalSeconds], 
    ext_map.[FName] + ' ' + ext_map.[LName] AS anon_1 
FROM cdr_extended, ext_map 
WHERE 
    (ext_map.exten = cdr_extended.[Extension] 
        OR ext_map.prev_exten = cdr_extended.[Extension]) 
    AND cdr_extended.[StartTime] &gt; '2015-01-01' 
    AND cdr_extended.[Extension] IN ('8297') 
GROUP BY anon_1 
DESC
"><pre class="notranslate"><code class="notranslate">SELECT 
    count(cdr_extended.[UniqueID]) AS [CallCount],
    sum(cdr_extended.[Duration]) AS [TotalSeconds], 
    ext_map.[FName] + ' ' + ext_map.[LName] AS anon_1 
FROM cdr_extended, ext_map 
WHERE 
    (ext_map.exten = cdr_extended.[Extension] 
        OR ext_map.prev_exten = cdr_extended.[Extension]) 
    AND cdr_extended.[StartTime] &gt; '2015-01-01' 
    AND cdr_extended.[Extension] IN ('8297') 
GROUP BY anon_1 
DESC

</code></pre></div>
<p dir="auto">Here's the model:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class EMap(Base):
    FName = Column(String(length=45))
    LName = Column(String(length=45))
    AssociateName = column_property(FName + &quot; &quot; + LName)

   DBSession.query(func.count(ExtendedCDR.UniqueID)
.label(&quot;CallCount&quot;),func.sum(ExtendedCDR.Duration)
.label(&quot;TotalSeconds&quot;))
.filter(or_(ExtensionMap.exten == ExtendedCDR.Extension,ExtensionMap.prev_exten == ExtendedCDR.Extension))
.filter(ExtendedCDR.StartTime&gt;jan1)
.filter(ExtendedCDR.Extension.in_(extensions))
.group_by(ExtensionMap.AssociateName)
.order_by(func.count(ExtendedCDR.UniqueID).desc())
"><pre class="notranslate"><code class="notranslate">class EMap(Base):
    FName = Column(String(length=45))
    LName = Column(String(length=45))
    AssociateName = column_property(FName + " " + LName)

   DBSession.query(func.count(ExtendedCDR.UniqueID)
.label("CallCount"),func.sum(ExtendedCDR.Duration)
.label("TotalSeconds"))
.filter(or_(ExtensionMap.exten == ExtendedCDR.Extension,ExtensionMap.prev_exten == ExtendedCDR.Extension))
.filter(ExtendedCDR.StartTime&gt;jan1)
.filter(ExtendedCDR.Extension.in_(extensions))
.group_by(ExtensionMap.AssociateName)
.order_by(func.count(ExtendedCDR.UniqueID).desc())

</code></pre></div>
<p dir="auto">And finally, here's the actual stack trace when the group_by fails:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#!

Traceback (most recent call last):
  File &quot;/usr/local/lib/python2.7/site-packages/pyramid_exclog-0.7-py2.7.egg/pyramid_exclog/__init__.py&quot;, line 111, in exclog_tween
    return handler(request)
  File &quot;/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/router.py&quot;, line 163, in handle_request
    response = view_callable(context, request)
  File &quot;/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py&quot;, line 245, in _secured_view
    return view(context, request)
  File &quot;/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py&quot;, line 355, in rendered_view
    result = view(context, request)
  File &quot;/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py&quot;, line 501, in _requestonly_view
    response = view(request)
  File &quot;/opt/cedar/cedar/views/ViewMyDashboard.py&quot;, line 51, in MyDashboardView
    YearList = ObstinateDatabaseQueryAll(DBSession.query(func.count(ExtendedCDR.UniqueID).label(&quot;CallCount&quot;),func.sum(ExtendedCDR.Duration).label(&quot;TotalSeconds&quot;),ExtensionMap.AssociateName).filter(or_(ExtensionMap.exten == ExtendedCDR.Extension,ExtensionMap.prev_exten == ExtendedCDR.Extension)).filter(ExtendedCDR.StartTime&gt;year_today).filter(ExtendedCDR.Extension.in_(extensions)).group_by(ExtensionMap.AssociateName).order_by(func.count(ExtendedCDR.UniqueID).desc()))
  File &quot;/opt/cedar/cedar/controllers/db.py&quot;, line 40, in ObstinateDatabaseQueryAll
    ret=query.all()
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py&quot;, line 2408, in all
    return list(self)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py&quot;, line 2525, in __iter__
    return self._execute_and_instances(context)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py&quot;, line 2540, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py&quot;, line 914, in execute
    return meth(self, multiparams, params)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py&quot;, line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py&quot;, line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py&quot;, line 1146, in _execute_context
    context)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py&quot;, line 1332, in _handle_dbapi_exception
    exc_info
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py&quot;, line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py&quot;, line 1139, in _execute_context
    context)
  File &quot;build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py&quot;, line 442, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (pyodbc.ProgrammingError) ('42S22', &quot;[42S22] [FreeTDS][SQL Server]Invalid column name 'anon_1'. (207) (SQLExecDirectW)&quot;) [SQL: 'SELECT count(cdr_extended.[UniqueID]) AS [CallCount], sum(cdr_extended.[Duration]) AS [TotalSeconds], ext_map.[FName] + ? + ext_map.[LName] AS anon_1 \nFROM cdr_extended, ext_map \nWHERE (ext_map.exten = cdr_extended.[Extension] OR ext_map.prev_exten = cdr_extended.[Extension]) AND cdr_extended.[StartTime] &gt; ? AND cdr_extended.[Extension] IN (?) GROUP BY anon_1 ORDER BY count(cdr_extended.[UniqueID]) DESC'] [parameters: (' ', datetime.datetime(2015, 1, 1, 0, 0), '8297')]"><pre class="notranslate"><code class="notranslate">#!

Traceback (most recent call last):
  File "/usr/local/lib/python2.7/site-packages/pyramid_exclog-0.7-py2.7.egg/pyramid_exclog/__init__.py", line 111, in exclog_tween
    return handler(request)
  File "/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/router.py", line 163, in handle_request
    response = view_callable(context, request)
  File "/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py", line 245, in _secured_view
    return view(context, request)
  File "/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py", line 355, in rendered_view
    result = view(context, request)
  File "/usr/local/lib/python2.7/site-packages/pyramid-1.5.4-py2.7.egg/pyramid/config/views.py", line 501, in _requestonly_view
    response = view(request)
  File "/opt/cedar/cedar/views/ViewMyDashboard.py", line 51, in MyDashboardView
    YearList = ObstinateDatabaseQueryAll(DBSession.query(func.count(ExtendedCDR.UniqueID).label("CallCount"),func.sum(ExtendedCDR.Duration).label("TotalSeconds"),ExtensionMap.AssociateName).filter(or_(ExtensionMap.exten == ExtendedCDR.Extension,ExtensionMap.prev_exten == ExtendedCDR.Extension)).filter(ExtendedCDR.StartTime&gt;year_today).filter(ExtendedCDR.Extension.in_(extensions)).group_by(ExtensionMap.AssociateName).order_by(func.count(ExtendedCDR.UniqueID).desc()))
  File "/opt/cedar/cedar/controllers/db.py", line 40, in ObstinateDatabaseQueryAll
    ret=query.all()
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2408, in all
    return list(self)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2525, in __iter__
    return self._execute_and_instances(context)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2540, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1332, in _handle_dbapi_exception
    exc_info
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (pyodbc.ProgrammingError) ('42S22', "[42S22] [FreeTDS][SQL Server]Invalid column name 'anon_1'. (207) (SQLExecDirectW)") [SQL: 'SELECT count(cdr_extended.[UniqueID]) AS [CallCount], sum(cdr_extended.[Duration]) AS [TotalSeconds], ext_map.[FName] + ? + ext_map.[LName] AS anon_1 \nFROM cdr_extended, ext_map \nWHERE (ext_map.exten = cdr_extended.[Extension] OR ext_map.prev_exten = cdr_extended.[Extension]) AND cdr_extended.[StartTime] &gt; ? AND cdr_extended.[Extension] IN (?) GROUP BY anon_1 ORDER BY count(cdr_extended.[UniqueID]) DESC'] [parameters: (' ', datetime.datetime(2015, 1, 1, 0, 0), '8297')]
</code></pre></div>