sa.union_all() fails with sa.text() if use ORDER BY. #6434
-
Describe the bug sorry for not fully working example, but it seems this is a simple to reproduce. I have simplified the code. OK for bu in bu_ids:
view_name = f'view_{bu}'
queries.append(
sa.text(f'''
SELECT run_id
FROM {view_name}
''')
)
q_union = sa.union_all(*queries)
result = conn.execute(q_union).fetchall() SELECT run_id
FROM view_26416
UNION ALL
SELECT run_id
FROM view_192582 NOK for bu in bu_ids:
view_name = f'view_{bu}'
queries.append(
sa.text(f'''
SELECT run_id
FROM {view_name}
ORDER BY {run_id} DESC
LIMIT 1
''')
)
q_union = sa.union_all(*queries)
result = conn.execute(q_union).fetchall() SELECT run_id
FROM view_26416
ORDER BY run_id DESC
LIMIT 1
UNION ALL
SELECT run_id
FROM view_192582
ORDER BY run_id DESC
LIMIT 1 Error
FIXED SQL (
SELECT run_id
FROM view_26416
ORDER BY run_id DESC
LIMIT 1
)
UNION ALL
(
SELECT run_id
FROM view_192582
ORDER BY run_id DESC
LIMIT 1
) Versions.
|
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
Hi, SQLAlchemy does not parses the content of the text, so if the query requires parenthesis they are needed in the text, e.g. An alternative is to use the lowercase for bu in bu_ids:
view_name = f'view_{bu}'
queries.append(
sa.select([sa.column('run_id')])
.select_from(sa.table(view_name))
.order_by(sa.column(run_id))
.limit(1)
)
q_union = sa.union_all(*queries)
result = conn.execute(q_union).fetchall() |
Beta Was this translation helpful? Give feedback.
Hi,
SQLAlchemy does not parses the content of the text, so if the query requires parenthesis they are needed in the text, e.g.
sa.text(f'''(SELECT run_id FROM {view_name})''')
.An alternative is to use the lowercase
column
andtable
to generate the queries: