I have the following query: min_values = ( sa.select( [ table.c.region_id, table.c.operator, table.c.day_str, sa.func.min(table.c.processed_time).label('min_processed_time'), ] ) .group_by(table.c.region_id, table.c.operator, table.c.day_str) .order_by(table.c.region_id, table.c.operator, table.c.day_str) ).alias('mv')
j = min_values.join( config_mds, sa.and_( min_values.c.region_id == config_mds.c.region_id, min_values.c.operator == config_mds.c.operator, ), ).alias('j') stmt = ( sa.select([min_values.c.region_id, min_values.c.operator, min_values.c.day_str]) .select_from(j) .where(min_values.c.min_processed_time < config_mds.c.last_reprocess_time) ) Which works well, as long as I do not add the .alias('j') part. If I add it, it generates a wildly different SQL code, which doesn't finish in reasonable time (I terminated it after a few minutes whereas the first one finishes in a few seconds. Here is the generated SQL without alias('j'): SELECT mv.region_id, mv.operator, mv.day_str FROM ( SELECT status_changes.region_id AS region_id, status_changes.operator AS OPERATOR, status_changes.day_str AS day_str, min(status_changes.processed_time) AS min_processed_time FROM status_changes GROUP BY status_changes.region_id, status_changes.operator, status_changes.day_str ORDER BY status_changes.region_id, status_changes.operator, status_changes.day_str ) AS mv JOIN config_mds ON mv.region_id = config_mds.region_id AND mv.operator = config_mds.operator WHERE config_mds.last_reprocess_time > mv.min_processed_time And here is with the added alias('j'), which doesn't terminates: SELECT mv.region_id, mv.operator, mv.day_str FROM ( SELECT status_changes.region_id AS region_id, status_changes.operator AS OPERATOR, status_changes.day_str AS day_str, min(status_changes.processed_time) AS min_processed_time FROM status_changes GROUP BY status_changes.region_id, status_changes.operator, status_changes.day_str ORDER BY status_changes.region_id, status_changes.operator, status_changes.day_str ) AS mv, config_mds, ( SELECT mv.region_id AS mv_region_id, mv.operator AS mv_operator, mv.day_str AS mv_day_str, mv.min_processed_time AS mv_min_processed_time, config_mds.region_id AS config_mds_region_id, config_mds.operator AS config_mds_operator, config_mds.disable_download AS config_mds_disable_download, config_mds.trips_url AS config_mds_trips_url, config_mds.status_changes_url AS config_mds_status_changes_url, config_mds.url_headers AS config_mds_url_headers, config_mds.login_data AS config_mds_login_data, config_mds.use_different_region AS config_mds_use_different_region, config_mds.comment AS config_mds_comment, config_mds.last_reprocess_time AS config_mds_last_reprocess_time, config_mds.token_cache AS config_mds_token_cache FROM ( SELECT status_changes.region_id AS region_id, status_changes.operator AS OPERATOR, status_changes.day_str AS day_str, min(status_changes.processed_time) AS min_processed_time FROM status_changes GROUP BY status_changes.region_id, status_changes.operator, status_changes.day_str ORDER BY status_changes.region_id, status_changes.operator, status_changes.day_str ) AS mv JOIN config_mds ON mv.region_id = config_mds.region_id AND mv.operator = config_mds.operator ) AS j WHERE config_mds.last_reprocess_time > mv.min_processed_time; This behaviour confuses me a bit. Why would adding an alias make a difference to the generated query? -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/bbf11796-b9ed-4b6f-9188-c35585dfc01d%40googlegroups.com.