Oracle to_date broken when using SQL generation in beta5
Here's another simple testcase that fails for Oracle beta5 but works with 0.3.7. It's about the simplest query I can come up with. Have a table with a date column in it and query against it using the "to_date" function.
def test_to_date():
start_date = '10/05/04'
where = cal_dim.c.adwkenddt == func.to_date(start_date,'MM/DD/RR')
query = select([cal_dim.c.adwkenddt](cal_dim.c.adwkenddt),
whereclause=where
)
result = query.execute()
for r in result:
print r
This fails for beta5, works for .3.7. The error is::
Traceback (most recent call last):
File "s4.py", line 88, in ?
test_to_date()
File "s4.py", line 59, in test_to_date
result = query.execute()
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/sql/expression.py",
line 973, in execute
return compiled.execute(*multiparams, **params)
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 488, in execute
return e._execute_compiled(self, multiparams, params)
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 1121, in _execute_compiled
return connection._execute_compiled(compiled, multiparams, params)
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 832, in _execute_compiled
self.__execute_raw(context)
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 850, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
parameters, context=context)
File "/home/matt/work/vpython/lib/python2.4/site-packages/SQLAlchemy-0.4.0beta5-py2.4.egg/sqlalchemy/engine/base.py",
line 867, in _cursor_execute
raise exceptions.DBAPIError.instance(statement, parameters, e)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) ORA-00932:
inconsistent datatypes: expected NUMBER got CLOB
'SELECT "AD_WEEK_CALENDAR_DIM".adwkenddt \nFROM
"AD_WEEK_CALENDAR_DIM" \nWHERE "AD_WEEK_CALENDAR_DIM".adwkenddt =
to_date(:to_date, :to_date_1)' {'to_date_1': 'MM/DD/RR', 'to_date':
'10/05/04'}
Am still confused as to the problem here.... I run the same query through text and it works::
def test_to_date_text():
s = text("""SELECT "AD_WEEK_CALENDAR_DIM".adwkenddt
FROM "AD_WEEK_CALENDAR_DIM"
WHERE "AD_WEEK_CALENDAR_DIM".adwkenddt = to_date(:to_date, :to_date_1)""")
result = connection.execute(s,
to_date_1= 'MM/DD/RR',
to_date= '10/05/04')
Comments (6)
-
Account Deleted -
repo owner the test case above is lacking the Table definition for
cal_dim
. -
repo owner also whats up with 0.3.10 ? I see the problem for 0.4 here.
-
repo owner - changed status to resolved
OK for 0.4 the issue should be fixed in e37a3a961c9f5bf822f86682c0f96418281d3e42. closing this for now.
-
Account Deleted Thanks for the fix. It appears to work with my code.
Table definition for the adwkenddt is a DATE. Bug ticket:751 covers .3.10 issues
-
repo owner - removed milestone
Removing milestone: 0.4.0 (automated comment)
- Log in to comment
Mike explained that my confusion is due to cursor.set_input_sizes being called before the SQL generated query is executed.
A workaround is to pass auto_setinputsizes=False to create_engine. The query succeeds when using the workaround.
WRT set_input_sizes being called on the cursor, in default.py DefaultExecutionContext.set_input_sizes the input sizes for this query look like this
Both bind parameters are Strings, so I'm not sure why Oracle is complaining it wants a NUMBER.