title: SQLAlchemy requires query to be aliased yet that alias is not used in the generated SQL
body: <p>I have a simple model class that represents a battle between two characters:</p>
  
  <pre class="lang-py prettyprint-override"><code>class WaifuPickBattle(db.Model):
      """Table which represents a where one girl is chosen as a waifu."""
  
      __tablename__ = "waifu_battles"
      id = db.Column(db.Integer, primary_key=True)
      user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
      date = db.Column(db.DateTime, nullable=False)
      winner_name = db.Column(db.String, nullable=False)
      loser_name = db.Column(db.String, nullable=False)
  </code></pre>
  
  <p>I have a method which constructs a CTE which projects the battles into a series of appearences (each battle has two appearences - the winner and the loser):</p>
  
  <pre class="lang-py prettyprint-override"><code>def get_battle_appearences_cte():
      """Create a sqlalchemy subquery of the battle appearences."""
      wins = select([
          WaifuPickBattle.date,
          WaifuPickBattle.winner_name.label("name"),
          expression.literal_column("1").label("was_winner"),
          expression.literal_column("0").label("was_loser")
      ])
      losses = select([
          WaifuPickBattle.date,
          WaifuPickBattle.loser_name.label("name"),
          expression.literal_column("0").label("was_winner"),
          expression.literal_column("1").label("was_loser")
      ])
      return wins.union_all(losses).cte("battle_appearence")
  </code></pre>
  
  <p>I then have a query which utilises this view to determine the characters which have seen the most battles:</p>
  
  <pre class="lang-py prettyprint-override"><code>def query_most_battled_waifus():
      """Find the waifus with the most battles in a given date range."""
      appearence_cte = get_battle_appearences_cte()
      query = \
          select([
              appearence_cte.c.name,
              func.sum(appearence_cte.c.was_winner).label("wins"),
              func.sum(appearence_cte.c.was_loser).label("losses"),
          ])\
          .group_by(appearence_cte.c.name)\
          .order_by(func.count().desc())\
          .limit(limit)
      return db.session.query(query).all()
  </code></pre>
  
  <p>This generates the following SQL:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH battle_appearence  AS
  (
      SELECT
          waifu_battles.date AS date,
          waifu_battles.winner_name AS name,
          1 AS was_winner,
          0 AS was_loser
      FROM waifu_battles
      UNION ALL
      SELECT
          waifu_battles.date AS date,
          waifu_battles.loser_name AS name,
          0 AS was_winner,
          1 AS was_loser
      FROM waifu_battles
  )
  SELECT
      name AS name,
      wins AS wins,
      losses AS losses
  FROM
  (
      SELECT
          battle_appearence.name AS name,
          sum(battle_appearence.was_winner) AS wins,
          sum(battle_appearence.was_winner) AS losses
      FROM battle_appearence
      GROUP BY battle_appearence.name
      ORDER BY count(*) DESC
  )
  </code></pre>
  
  <p>This works perfectly fine when executing against a SQLite database, but when executing against a Postgres SQL database the following error is given:</p>
  
  <pre><code>sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias
  
  LINE 6: FROM (SELECT battle_appearence.name AS name, count(battle_ap... ^ HINT: For example, FROM (SELECT ...) [AS] foo.
  
  [SQL: WITH battle_appearence AS (SELECT waifu_battles.date AS date, waifu_battles.winner_name AS name, 1 AS was_winner, 0 AS was_loser FROM waifu_battles UNION ALL SELECT waifu_battles.date AS date, waifu_battles.loser_name AS name, 0 AS was_winner, 1 AS was_loser FROM waifu_battles) SELECT name AS name, wins AS wins, losses AS losses FROM (SELECT battle_appearence.name AS name, count(battle_appearence.was_winner) AS wins, count(battle_appearence.was_winner) AS losses FROM battle_appearence GROUP BY battle_appearence.name ORDER BY count(*) DESC)] (Background on this error at: http://sqlalche.me/e/f405)
  </code></pre>
  
  <p>There are a few things to notice at this point:</p>
  
  <ol>
  <li>The sub-select is redundant, we should simply using the sub-select as the main select statement.</li>
  <li>You could resolve this by aliasing the sub-select and using <code>&lt;alias&gt;.&lt;column&gt;</code> in the main select statement - Postgres requiring an alias on the sub-selects is well documented elsewhere.</li>
  </ol>
  
  <p>My first question is <strong>how</strong> would I alias this sub-select seeing that SQLalchemy decides to introduce it despite not being explicitly instructed to (as far as I can tell)?</p>
  
  <p>I found a solution to the problem was to add <code>.alias("foo")</code> to the query:</p>
  
  <pre><code>query = query\
          ...\
          .alias("foo")
  </code></pre>
  
  <p>Which casuses the following SQL to be generated (one that weirdly resolved the whole redundant sub-select issue as well!):</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH battle_appearence  AS
  (
      SELECT
          waifu_battles.date AS date,
          waifu_battles.winner_name AS name,
          1 AS was_winner,
          0 AS was_loser
      FROM waifu_battles
      UNION ALL
      SELECT
          waifu_battles.date AS date,
          waifu_battles.loser_name AS name,
          0 AS was_winner,
          1 AS was_loser
      FROM waifu_battles
  )
  SELECT
      battle_appearence.name,
      sum(battle_appearence.was_winner) AS wins,
      sum(battle_appearence.was_winner) AS losses
  FROM battle_appearence
  GROUP BY battle_appearence.name
  ORDER BY count(*) DESC
  </code></pre>
  
  <p>My second question is <strong>why</strong> did adding the alias prevent the sub-select from being created <em>and</em> <strong>why</strong> is the alias not used! The <code>"foo"</code> alias was seemingly disregarded yet had a substantial effect on the generated query.</p>