<p dir="auto"><strong>Migrated issue, originally created by Stijn van Drongelen (<a href="https://github.com/rhymoid">@rhymoid</a>)</strong></p>
<p dir="auto">I programmatically create a bunch of (complex) <code class="notranslate">INSERT ... SELECT</code> statements. I regularly use the pattern</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="target_columns, source_elements = *query_components
statement = target_table.insert().from_select(
    target_columns,
    select(source_elements).select_from(source_selectable)
)"><pre class="notranslate"><code class="notranslate">target_columns, source_elements = *query_components
statement = target_table.insert().from_select(
    target_columns,
    select(source_elements).select_from(source_selectable)
)
</code></pre></div>
<p dir="auto">after building <code class="notranslate">query_components</code> and <code class="notranslate">source_selectable</code> from some specification. When I (granted: inadvertedly) had a duplicate column in <code class="notranslate">source_elements</code>, the database complained that there was a mismatch in the numer of columns in the <code class="notranslate">INSERT ... SELECT</code> statement.</p>
<p dir="auto">It took me a while to figure out what the problem was, but I've distilled a MVCE that explains my expectations and how they mismatch SQLAlchemy's actual behaviour. I'm not sure what the reasoning behind it is, as having duplicate columns after <code class="notranslate">SELECT</code> isn't semantically wrong. If I wanted to target multiple columns with the same source column, I could of course work around it by labeling every source column, but unlike <code class="notranslate">.alias()</code>, <code class="notranslate">.label()</code> can't be called without an argument (and I'd like to leverage SQLAlchemy's ability to generate fresh names).</p>
<p dir="auto">So, in short: simple code may end up generating broken SQL because duplicate columns get dropped by <code class="notranslate">Select</code>. Why does it do that, and how do I generate fresh <code class="notranslate">.label()</code>s if I'd want to work around that for <code class="notranslate">Insert.from_select</code>?</p>
<p dir="auto">The problem occurs in 1.0.15, 1.0.17, and 1.1.11.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/4022/sqlalchemy_duplicate_columns_in_select_bug.py">sqlalchemy_duplicate_columns_in_select_bug.py</a></p>
<p dir="auto">Apart from having option to select isolation level, it may come handy to create a transaction in a read only mode. This option is supported on postgres, see <a href="https://www.postgresql.org/docs/current/sql-set-transaction.html" rel="nofollow">https://www.postgresql.org/docs/current/sql-set-transaction.html</a>. I'm not sure about other database backends, but mysql seems to support them as well, see <a href="https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html" rel="nofollow">https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html</a>.</p>
<p dir="auto">I suggest to add an optional parameter transaction mode with possible values <code class="notranslate">READ WRITE</code> (default) or <code class="notranslate">READ ONLY</code> to execution options, similarly to isolation level.</p>