- changed status to closed
Union query contains duplicated rows and calling .scalar() on it gets MultipleResultsFound exception
Issue #3770
closed
I am running into two issues with Union today using SQLAlchemy with MySQL.
Here is the iPython session output.
In [1]: from models.schedules import *
In [2]: from models.products import *
In [3]: from models.users import *
In [4]: query = db.session.query(Appointment)
In [5]: q_member = query.join(Schedule).join(User).filter(
User.last_name == 'Smith')
In [6]: q_practitioner = query.join(Product).join(User).filter(
User.last_name == 'Smith')
In [7]: q_member.count()
Out[7]: 135
In [8]: q_practitioner.count()
Out[8]: 277
In [9]: q_member.union(q_practitioner)
Out[9]: <sqlalchemy.orm.query.Query at 0x7f72307845f8>
In [10]: q_member.union(q_practitioner).count()
Out[10]: 388
In [11]: q_member.union(q_practitioner).scalar()
---------------------------------------------------------------------------
MultipleResultsFound Traceback (most recent call last)
<ipython-input-11-0456717d8d59> in <module>()
----> 1 q_member.union(q_practitioner).scalar()
/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/query.py in scalar(self)
2622 try:
-> 2623 ret = self.one()
2624 if not isinstance(ret, tuple):
2625 return ret
/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/query.py in one(self)
2599 else:
2600 raise orm_exc.MultipleResultsFound(
-> 2601 "Multiple rows were found for one()")
2602
2603 def scalar(self):
MultipleResultsFound: Multiple rows were found for one()
In [12]: sqlalchemy.__version__
Out[12]: '0.9.11'
1) The .union() call returns duplicated rows, as you can see above 135+277 > 388. This is violating MySQL's SELECT UNION statement syntax in that UNION DISTINCT is the default. (only UNION ALL allows duplicate rows) http://dev.mysql.com/doc/refman/5.6/en/union.html
2) Calling .scalar() on a union'd query object gets MultipleResultsFound exception
Comments (1)
-
repo owner - Log in to comment
hello -
The one() / scalar() call requires that the query return exactly one row, not hundreds, which is why that exception is raised.
I don't understand the issue with the 135 + 277 > 388. If query A returns 135 rows, query B returns 277 rows, then UNION is applied to them, duplicates will be removed. So of the 135 + 277 = 412 rows, 24 of them would be duplicates here, so the count of their union is 388. The sum of the result sets should be greater than the union.
Additoinally, SQLAlchemy only emits SQL to MySQL, it doesn't do anything with counting rows itself, so you should turn on echo=True here to inspect the SQL emitted.
This bug report also has not followed the instructions at https://bitbucket.org/zzzeek/sqlalchemy/issues/new - all bugs must include an mcve. iPython sessions that don't illustrate any of the tables / data / SQL emitted do not illustrate any bug in SQLAlchemy. Thanks!