<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>
<p dir="auto"><strong>Describe your question</strong></p>
<p dir="auto">I have an app where all the schemas are declared.<br>
Have another readonly-app and want to shortcut schemas duplication for now by using <code class="notranslate">automap_base()</code>.<br>
No error were given from main app, but when I'm trying to do:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Base = automap_base()
Base.prepare(db.engine, reflect=True)
User = Base.classes.user
db.session.query(User).first()

ArgumentError: Error creating backref 'episode_collection' on relationship 'episode.mentor': property of that name exists on mapper 'mapped class mentor-&gt;mentor'"><pre class="notranslate"><code class="notranslate">Base = automap_base()
Base.prepare(db.engine, reflect=True)
User = Base.classes.user
db.session.query(User).first()

ArgumentError: Error creating backref 'episode_collection' on relationship 'episode.mentor': property of that name exists on mapper 'mapped class mentor-&gt;mentor'
</code></pre></div>
<p dir="auto">I have Episode class which has relations with Mentor:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="class Episode:
    ...
    author_id = db.Column(db.Integer, db.ForeignKey(&quot;mentor.id&quot;))
    author = db.relationship(&quot;Mentor&quot;, backref='episodes_author')

class Mentor:
    ...
    episodes_guest = db.relationship('Episode', secondary='episode_guest', backref='guests')

class EpisodeGuest(db.Model):
    ...
    guest = db.relationship('Mentor', backref='episodes_proxy')
    episode = db.relationship('Episode', backref='guests_proxy')"><pre class="notranslate"><code class="notranslate">class Episode:
    ...
    author_id = db.Column(db.Integer, db.ForeignKey("mentor.id"))
    author = db.relationship("Mentor", backref='episodes_author')

class Mentor:
    ...
    episodes_guest = db.relationship('Episode', secondary='episode_guest', backref='guests')

class EpisodeGuest(db.Model):
    ...
    guest = db.relationship('Mentor', backref='episodes_proxy')
    episode = db.relationship('Episode', backref='guests_proxy')
</code></pre></div>
<p dir="auto">All in all. An episode should have <code class="notranslate">author</code> and <code class="notranslate">guests</code>.<br>
In M2M table I used <code class="notranslate">episodes_proxy</code> naming to use this in <em>Flask-Admin</em> as an inline model.</p>
<p dir="auto">Can't understand where this problem comes from. Everything looks right in the main app. But failed when I trying to create using<code class="notranslate"> automap_base()</code></p>
<p dir="auto"><strong>Versions</strong></p>
<ul dir="auto">
<li>OS: arch</li>
<li>Python: 3.8</li>
<li>SQLAlchemy: 1.3.20</li>
<li>Database: postgresql</li>
</ul>
<p dir="auto"><strong>Have a nice day!</strong><br>
<strong>You too :D</strong></p>