Closed
Description
Describe the bug
On Oracle, when using literal tables and columns in conjunction with SQLA expressions, fully-qualified literal column names will have their parameter identifiers (seemingly) generated from the column clause key, which matches the literal column expression, something like :DUAL2.DUMMY_1. The dot is apparently invalid and needs to be escaped, so SQLA normally renders this as :"DUAL2.DUMMY_1" (with the quotes), but not for postcompiled parameters (e.g: lists).
This causes Oracle to raise an ORA-01036: illegal variable name/number error.
To Reproduce
bad_statement = select(literal_column("1")).select_from(
sql_text("""DUAL CROSS JOIN DUAL DUAL2""")
).where(
literal_column("DUAL2.DUMMY", VARCHAR(1)).in_(['X']),
)
good_statement = select(literal_column("1")).select_from(
sql_text("""DUAL CROSS JOIN DUAL DUAL2""")
).where(
literal_column("DUAL2.DUMMY", VARCHAR(1)) == 'X',
)
oracle_conn.execute(good_statement) # Works
oracle_conn.execute(bad_statement) # See errorError
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01036: illegal variable name/number
[SQL: SELECT 1
FROM DUAL CROSS JOIN DUAL DUAL2
WHERE DUAL2.DUMMY IN (:DUAL2.DUMMY_1_1)]
[parameters: {'DUAL2.DUMMY_1_1': 'X'}]
(Background on this error at: https://sqlalche.me/e/14/4xp6)
Versions
- OS: Windows
- Python: 3.10.7
- SQLAlchemy: 1.4.42
- Database: Oracle 19c
- DBAPI: cx_oracle 8.3
Additional context
>>> print(bad_statement.compile(ora_engine, compile_kwargs={"render_postcompile": True}))
SELECT 1
FROM DUAL CROSS JOIN DUAL DUAL2
WHERE DUAL2.DUMMY IN (:DUAL2.DUMMY_1_1)
>>> print(good_statement.compile(ora_engine, compile_kwargs={"render_postcompile": True}))
SELECT 1
FROM DUAL CROSS JOIN DUAL DUAL2
WHERE DUAL2.DUMMY = :"DUAL2.DUMMY_1"Metadata
Metadata
Assignees
Labels
Type
Projects
Milestone
Relationships
Development
No branches or pull requests
Participants
Activity
zzzeek commentedon Oct 24, 2022
the expanded parameters logic has very well integrated support for just escaping the characters, as opposed to quoting the bound name which is much more complicated and is specific to Oracle. it looks like Oracle dialect has an explicit skip on the usual quoting for the expanded parameters logic as it wasn't integrated there. so will just add "." to the escape characters.
sqla-tester commentedon Oct 24, 2022
Mike Bayer has proposed a fix for this issue in the main branch:
add Oracle-specific parameter escapes for expanding params https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4152
sqla-tester commentedon Oct 24, 2022
Mike Bayer has proposed a fix for this issue in the rel_1_4 branch:
add Oracle-specific parameter escapes for expanding params https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4153
zzzeek commentedon Oct 24, 2022
I might look into using escaping entirely for Oracle rather than quoting but that would be 2.0 only
add Oracle-specific parameter escapes for expanding params
zzzeek commentedon Dec 2, 2022
I missed a bit of change here
sqla-tester commentedon Dec 2, 2022
Mike Bayer has proposed a fix for this issue in the main branch:
add spaces, leading underscore to oracle checks https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4260
4 remaining items