Union query contains duplicated rows and calling .scalar() on it gets MultipleResultsFound exception

[Ye Wang]

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

[Mike Bayer]

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!