<p dir="auto"><strong>Migrated issue, originally created by Thijs Damsma (<a href="https://github.com/tdamsma">@tdamsma</a>)</strong></p>
<p dir="auto">When a distinct query is ordered, the columns on which are sorted are added to the SELECT statement, see example code:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    att1 = Column(Integer)
    att2 = Column(Integer)


Session = sessionmaker()
sess = Session()

q = sess.query(User.att2).distinct(User.att1)

print(str(q.statement.compile(dialect=postgresql.dialect())))"><pre class="notranslate"><code class="notranslate">from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer

Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    att1 = Column(Integer)
    att2 = Column(Integer)


Session = sessionmaker()
sess = Session()

q = sess.query(User.att2).distinct(User.att1)

print(str(q.statement.compile(dialect=postgresql.dialect())))
</code></pre></div>
<p dir="auto">as expected, only user.att2 will be returned:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT DISTINCT ON (&quot;user&quot;.att1) &quot;user&quot;.att2 
FROM &quot;user&quot;"><pre class="notranslate"><code class="notranslate">SELECT DISTINCT ON ("user".att1) "user".att2 
FROM "user"
</code></pre></div>
<p dir="auto">Now with an order_by statement:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="print(str(q.order_by(User.att1, User.id).statement.compile(
    dialect=postgresql.dialect())))"><pre class="notranslate"><code class="notranslate">print(str(q.order_by(User.att1, User.id).statement.compile(
    dialect=postgresql.dialect())))
</code></pre></div>
<p dir="auto">not as expected, both user.att2 and user.id will be returned</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT DISTINCT ON (&quot;user&quot;.att1) &quot;user&quot;.att2, &quot;user&quot;.att1, &quot;user&quot;.id 
FROM &quot;user&quot; ORDER BY &quot;user&quot;.att1, &quot;user&quot;.id"><pre class="notranslate"><code class="notranslate">SELECT DISTINCT ON ("user".att1) "user".att2, "user".att1, "user".id 
FROM "user" ORDER BY "user".att1, "user".id
</code></pre></div>
<p dir="auto">The intended result is:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT DISTINCT ON (&quot;user&quot;.att1) &quot;user&quot;.att2
FROM &quot;user&quot; ORDER BY &quot;user&quot;.att1, &quot;user&quot;.id"><pre class="notranslate"><code class="notranslate">SELECT DISTINCT ON ("user".att1) "user".att2
FROM "user" ORDER BY "user".att1, "user".id
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Mehdi GMIRA (<a href="https://github.com/mehdigmira">@mehdigmira</a>)</strong></p>
<p dir="auto">I encountred a bug with the postgres specific "DISTINCT ON" in SQLAlchemy<br>
The real SA query I'm using is more complicated than this, but it sums up to :</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query = db.session.query(c1, c2, c3, c4).distinct(c1, c2).order_by(c1, c2, c3)"><pre class="notranslate"><code class="notranslate">query = db.session.query(c1, c2, c3, c4).distinct(c1, c2).order_by(c1, c2, c3)
</code></pre></div>
<p dir="auto">when i compile the query by doing:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="query.statement.compile(dialect=postgres.dialect())"><pre class="notranslate"><code class="notranslate">query.statement.compile(dialect=postgres.dialect())
</code></pre></div>
<p dir="auto">The actual query is</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT DISTINCT ON (c1, c2) c1, c2, c3, c4, c1, c2, c3 FROM .... ORDER BY c1, c2, c3"><pre class="notranslate"><code class="notranslate">SELECT DISTINCT ON (c1, c2) c1, c2, c3, c4, c1, c2, c3 FROM .... ORDER BY c1, c2, c3
</code></pre></div>
<p dir="auto">As you can see, the columns c1, c2, c3 are repeated. After some investigation, it seems like this is caused by the order_by() part of the query (removing order by removes the repeated columns).<br>
The weird thing is that when actually running the query (query.all()), I only get the column that I requested, and not the repeated ones as in the compiled statement.<br>
This would have been fine with me. But the problem is it's preventing me from using a union(), because SA thinks I'm requesting more columns on one side of union().</p>
<p dir="auto">I think It's a bug, but maybe I'm just doing something wrong.</p>