invalid sql for mysql when using session.query instead of session.execute

[Former user]

I habe a rather complicated query:

highscore_query = select(
    [players.c.name, players.c.points,
     func.sum(donations.c.amount_wood + donations.c.amount_tradegood).label('donations')](players.c.id,),
    from_obj=players.\
    join(donations).join(islands).join(allies, allies.c.id == players.c.ally_id)).\
    group_by(players.c.name).where(allies.c.tag == 'mytag')

if i use session.query(highscore_query).all() to execute it, it is compiled to

SELECT id AS id, name AS name, points AS points, donations AS donations 
FROM (SELECT stats_player.id AS id, stats_player.name AS name, stats_player.points AS points, sum(stats_donation.amount_wood + stats_donation.amount_tradegood) AS donations 
FROM stats_player INNER JOIN stats_donation ON stats_player.id = stats_donation.player_id INNER JOIN stats_island ON stats_island.id = stats_donation.island_id INNER JOIN stats_ally ON stats_ally.id = stats_player.ally_id 
WHERE stats_ally.tag = %s GROUP BY stats_player.name)

which results in

 (OperationalError) (1248, 'Every derived table must have its own alias')

If I use session.execute() instead, the outer SELECT is missing and the query works.

SELECT stats_player.id, stats_player.name, stats_player.points, sum(stats_donation.amount_wood + stats_donation.amount_tradegood) AS donations 
FROM stats_player INNER JOIN stats_donation ON stats_player.id = stats_donation.player_id INNER JOIN stats_island ON stats_island.id = stats_donation.island_id INNER JOIN stats_ally ON stats_ally.id = stats_player.ally_id 
WHERE stats_ally.tag = %s GROUP BY stats_player.name

I am using SA 0.5.5 and MySQL 5.0.67. If I use sqlite it works without any problem in both cases.

[Mike Bayer]

call alias() on your select(). saying query(someselect) is equivalent to saying "select * from (your select)". select() doesn't aim to be entirely database agnostic from a structural point of view so it won't add the alias() automatically.

you'd probably have an easier time building up this select statement within query() to start with (i.e. don't use select(), use query(col1, col2, col3).select_from(join(...))... ), if your goal is to have tuples as results, that would avoid the unnecessary subquery. Otherwise Session.execute() is the way to go if you don't mind having RowProxy rows.