[Sqlalchemy-tickets] Issue #4242: Bad Precision Error thrown only in SQLAlchemy (zzzeek/sqlalchemy)

[Araam B. <iss...@bi...>]

New issue 4242: Bad Precision Error thrown only in SQLAlchemy
https://bitbucket.org/zzzeek/sqlalchemy/issues/4242/bad-precision-error-thrown-only-in

Araam Borhanian:

When I run this date query, SQLAlchemy has cx_Oracle throw an error, but I can't replicate this with just cx_Oracle alone. This started showing up after version 1.2.2 of SQLAlchemy. Earlier versions don't have this issue. Occurs on all versions of cx_Oracle from 5.2.1 to 6.2.1

DB Version I'm running on is Oracle Database 11g Release 11.2.0.4.0 - 64bit Production.

import cx_Oracle
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql.expression import func


# This works fine
conn = cx_Oracle.connect("conn_str")
cursor = conn.cursor()
result = cursor.execute("SELECT trunc(sysdate, :day) - :back FROM DUAL",
                        {'day': 'dd', 'back': 1}).fetchall()
cursor.close()
conn.close()

# This throws a bad precision specifier error.
some_engine = create_engine("oracle+cx_oracle://conn_str")
Session = sessionmaker(bind=some_engine)
session = Session()
session.query(func.trunc(func.sysdate(), 'dd') - 1).all()


The stacktrace after running this code:

Traceback (most recent call last):
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-01899: bad precision specifier

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "testcase.py", line 19, in <module>
    session.query(func.trunc(func.sysdate(), 'dd') - 1).all()
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2726, in all
    return list(self)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2878, in __iter__
    return self._execute_and_instances(context)
  File "...\.env\lib\site-packages\sqlalchemy\orm\query.py", line 2901, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\sql\elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1200, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "...\.env\lib\site-packages\sqlalchemy\util\compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "...\.env\lib\site-packages\sqlalchemy\engine\base.py", line 1193, in _execute_context
    context)
  File "...\.env\lib\site-packages\sqlalchemy\engine\default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-01899: bad precision specifier [SQL: 'SELECT trunc(sysdate, :trunc_1) - :trunc_2 AS anon_1 FROM DUAL'] [parameters: {'trunc_1': 'dd', 'trunc_2': 1}] (Background on this error at: http://sqlalche.me/e/4xp6)