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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/bbf11796-b9ed-4b6f-9188-c35585dfc01d%40googlegroups.com.
