<p dir="auto"><strong>Migrated issue, originally created by Ye Wang (<a href="https://github.com/ye">@ye</a>)</strong></p>
<p dir="auto">I am running into two issues with Union today using SQLAlchemy with MySQL.</p>
<p dir="auto">Here is the iPython session output.</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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]: &lt;sqlalchemy.orm.query.Query at 0x7f72307845f8&gt;

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)
&lt;ipython-input-11-0456717d8d59&gt; in &lt;module&gt;()
----&gt; 1 q_member.union(q_practitioner).scalar()

/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/query.py in scalar(self)
   2622         try:
-&gt; 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(
-&gt; 2601                 &quot;Multiple rows were found for one()&quot;)
   2602 
   2603     def scalar(self):

MultipleResultsFound: Multiple rows were found for one()

In [12]: sqlalchemy.__version__
Out[12]: '0.9.11'"><pre class="notranslate"><code class="notranslate">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]: &lt;sqlalchemy.orm.query.Query at 0x7f72307845f8&gt;

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)
&lt;ipython-input-11-0456717d8d59&gt; in &lt;module&gt;()
----&gt; 1 q_member.union(q_practitioner).scalar()

/usr/local/lib/python3.4/site-packages/sqlalchemy/orm/query.py in scalar(self)
   2622         try:
-&gt; 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(
-&gt; 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'
</code></pre></div>
<ol dir="auto">
<li>
<p dir="auto">The .union() call returns duplicated rows, as you can see above 135+277 &gt; 388. This is violating MySQL's SELECT UNION statement syntax in that UNION DISTINCT is the default. (only UNION ALL allows duplicate rows) <a href="http://dev.mysql.com/doc/refman/5.6/en/union.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.6/en/union.html</a></p>
</li>
<li>
<p dir="auto">Calling .scalar() on a union'd query object gets MultipleResultsFound exception</p>
</li>
</ol>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">The problem doesn't occurs using pymsqldb, but, pymssqldb has many problems with unicode :(<br>
When Lazy=True the problem doesn't occurs too !</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/495/mssql-orderby.patch">mssql-orderby.patch</a> | <a href="../wiki/imported_issue_attachments/495/test_orderby.py">test_orderby.py</a> | <a href="../wiki/imported_issue_attachments/495/monkeypatch_orderby.py">monkeypatch_orderby.py</a></p>