[Sqlalchemy-tickets] Issue #4022: Select removes duplicate columns, but Insert.from_select might want them (zzzeek/sqlalchemy)

[Stijn v. D. <iss...@bi...>]

New issue 4022: Select removes duplicate columns, but Insert.from_select might want them
https://bitbucket.org/zzzeek/sqlalchemy/issues/4022/select-removes-duplicate-columns-but

Stijn van Drongelen:

I programmatically create a bunch of (complex) `INSERT ... SELECT` statements. I regularly use the pattern

    target_columns, source_elements = *query_components
    statement = target_table.insert().from_select(
        target_columns,
        select(source_elements).select_from(source_selectable)
    )

after building `query_components` and `source_selectable` from some specification. When I (granted: inadvertedly) had a duplicate column in `source_elements`, the database complained that there was a mismatch in the numer of columns in the `INSERT ... SELECT` statement.

It took me a while to figure out what the problem was. I'm not sure what the reasoning behind it is, as having duplicate columns after `SELECT` 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 `.alias()`, `.label()` can't be called without an argument (and I'd like to leverage SQLAlchemy's ability to generate fresh names).

So, in short: simple code may end up generating broken SQL because duplicate columns get dropped by `Select`. Why does it do that, and how do I generate fresh `.label()`s if I'd want to work around that for `Insert.from_select`?