- edited description
Change from 0.9.9 to 1.0.0b1 introduces syntax change that MSSQL does not appreciate
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: http://stackoverflow.com/questions/29212205/in-sqlalchemy-group-by-on-column-property-no-longer-works-in-1-0-0b1
Here's a dump of the text of that question:
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.
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.
Is there some way to revert to the old behavior without requiring a specific version?
The below code generates the following SQL in 0.9.9:
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] > '2015-01-01'
AND cdr_extended.[Extension] IN ('8297')
GROUP BY ext_map.[FName] + ' ' + ext_map.[LName]
DESC
However, in 1.0.0, it produces this code:
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] > '2015-01-01'
AND cdr_extended.[Extension] IN ('8297')
GROUP BY anon_1
DESC
Here's the model:
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>jan1)
.filter(ExtendedCDR.Extension.in_(extensions))
.group_by(ExtensionMap.AssociateName)
.order_by(func.count(ExtendedCDR.UniqueID).desc())
And finally, here's the actual stack trace when the group_by fails:
#!
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>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] > ? 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')]
Comments (8)
-
reporter -
repo owner - edited description
-
repo owner in this case we'll have to turn this on:
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index ea0ed58..d431174 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -1431,6 +1431,8 @@ class MSDialect(default.DefaultDialect): server_version_info = () + supports_simple_order_by_label = False + statement_compiler = MSSQLCompiler ddl_compiler = MSDDLCompiler type_compiler = MSTypeCompiler
-
repo owner -
repo owner - changed status to resolved
- Turned off the "simple order by" flag on the MSSQL dialect; this
is the flag that per
2992
causes an order by or group by an expression that's also in the columns clause to be copied by label, even if referenced as the expression object. The behavior for MSSQL is now the old behavior that copies the whole expression in by default, as MSSQL can be picky on these particularly in GROUP BY expressions. fixes#3338 - Add a test that includes a composed label in a GROUP BY
→ <<cset 86cebccc4c3c>>
-
repo owner - changed status to open
totally blew this. 0.9 already does simple order by label, for all dialects. This is just for 1.0, we added it to group by, and it just doesn't work very well there. so we need to just revert the group_by part of it, as
#3385illustrates even postgresql can trip up on a group by. -
repo owner Issue
#3385was marked as a duplicate of this issue. -
repo owner - changed status to resolved
- Fixed a regression that was incorrectly fixed in 1.0.0b4
(hence becoming two regressions); reports that
SELECT statements would GROUP BY a label name and fail was misconstrued
that certain backends such as SQL Server should not be emitting
ORDER BY or GROUP BY on a simple label name at all; when in fact,
we had forgotten that 0.9 was already emitting ORDER BY on a simple
label name for all backends, as described in :ref:
migration_1068
, as 1.0 had rewritten this logic as part of2992
.
In 1.0.2, the bug is fixed both that SQL Server, Firebird and others will again emit ORDER BY on a simple label name when passed a :class:
.Label
construct that is expressed in the columns clause, and no backend will emit GROUP BY on a simple label name in this case, as even Postgresql can't reliably do GROUP BY on a simple name in every case. fixes#3338, fixes#3385→ <<cset f9275198c304>>
- Log in to comment