[Sqlalchemy-tickets] Issue #3770: Union query contains duplicated rows and calling .scalar() on it gets MultipleResultsFound exception (zzzeek/sqlalchemy)

[Ye W. <iss...@bi...>]

New issue 3770: Union query contains duplicated rows and calling .scalar() on it gets MultipleResultsFound exception
https://bitbucket.org/zzzeek/sqlalchemy/issues/3770/union-query-contains-duplicated-rows-and

Ye Wang:

I am running into two issues with Union today using SQLAlchemy with MySQL.

Here is the iPython session output.

```
#!python

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