SQLAlchemy 1.0.0 tries to process type value when not expected
Attached is the code to reproduce the error. Works fine with 0.9.9, fails with 1.0.0
ca@CAMBPB Downloads (chariots) % python poc_sqla_uuid.py
2015-04-20 13:02:42,846 INFO sqlalchemy.engine.base.Engine select version()
2015-04-20 13:02:42,847 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,848 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-04-20 13:02:42,848 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,850 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:42,850 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,851 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:42,851 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,852 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-04-20 13:02:42,852 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:42,854 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2015-04-20 13:02:42,854 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-04-20 13:02:42,856 INFO sqlalchemy.engine.base.Engine SELECT EXISTS (SELECT *
FROM users
WHERE users.uuid = %(uuid_1)s)
2015-04-20 13:02:42,857 INFO sqlalchemy.engine.base.Engine {'uuid_1': UUID('863599a9-05af-498e-ac8b-f593aec85f3b')}
ca@CAMBPB Downloads (chariots) % pip install sqlalchemy==1.0.0
...
ca@CAMBPB Downloads (chariots) % python poc_sqla_uuid.py
2015-04-20 13:02:59,597 INFO sqlalchemy.engine.base.Engine select version()
2015-04-20 13:02:59,597 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,598 INFO sqlalchemy.engine.base.Engine select current_schema()
2015-04-20 13:02:59,598 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,599 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:59,599 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,600 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-04-20 13:02:59,600 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,601 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2015-04-20 13:02:59,601 INFO sqlalchemy.engine.base.Engine {}
2015-04-20 13:02:59,602 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2015-04-20 13:02:59,602 INFO sqlalchemy.engine.base.Engine {'name': u'users'}
2015-04-20 13:02:59,605 INFO sqlalchemy.engine.base.Engine SELECT EXISTS (SELECT *
FROM users
WHERE users.uuid = %(uuid_1)s)
2015-04-20 13:02:59,605 INFO sqlalchemy.engine.base.Engine {'uuid_1': UUID('66d2e759-3c42-47b5-906d-90df8456cc76')}
Traceback (most recent call last):
File "poc_sqla_uuid.py", line 35, in <module>
main()
File "poc_sqla_uuid.py", line 32, in main
conn.execute(query).scalar()
File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 1053, in scalar
return row[0]
File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy/sql/type_api.py", line 979, in process
return process_value(impl_processor(value), dialect)
File "/Users/ca/.virtualenvs/chariots/lib/python2.7/site-packages/sqlalchemy_utils/types/uuid.py", line 76, in process_result_value
return uuid.UUID(value)
File "/usr/local/Cellar/python/2.7.9/Frameworks/Python.framework/Versions/2.7/lib/python2.7/uuid.py", line 134, in __init__
raise ValueError('badly formed hexadecimal UUID string')
ValueError: badly formed hexadecimal UUID string
This fails because value is False
(it's an exists query). Not sure why sqlalchemy tries to parse it as a UUID.
Using sqlachemy 0.9.9 (works) and 1.0.0 (does not work), sqlalchemy-utils (0.30.0).
Comments (7)
-
repo owner -
repo owner also this looks a whole lot like https://bitbucket.org/zzzeek/sqlalchemy/issue/3323/in-099-uuid-columns-are-broken-with. Are you absolutely sure this runs in 0.9.9 and you aren't actually using 0.9.8 ?
-
repo owner OK, new issue, will check
-
reporter The reason I did not report with sqlachemy_utils first is that (1) I did not see anything related to sqlachemy_utils doing something wrong (2) the fact that the behavior was broken in version 1.0.0 hinted at a problem coming from sqlalchemy. My apologies is those assumption were wrong, but please know that I did think about it.
I'll spend some time researching it more. Also now that I think about it, not sure why I'm not using sqlalchemy's
sqlalchemy.dialects.postgresql.UUID
. I don't really need the backend agnosticity part.Again, the reason I filed this is that this looked like a bug with sqlalchemy, and thought that'd be helpful to report it.
-
reporter Ok so I effectively don't need sqlalchemy_utils' UUID class, I'm using the one from dialects. Feel free to close and I'll reopen with sqlalchemy-utils.
-
repo owner there is a bug here and the reason the postgresql.UUID type doesn't expose it is because we use the psycopg2 type processor. This is actually an issue with the expression system that is a regression from the change for
#918. -
repo owner - changed status to resolved
- Fixed issue where a straight SELECT EXISTS query would fail to
assign the proper result type of Boolean to the result mapping, and
instead would leak column types from within the query into the
result map. This issue exists in 0.9 and earlier as well, however
has less of an impact in those versions. In 1.0, due to
#918this becomes a regression in that we now rely upon the result mapping to be very accurate, else we can assign result-type processors to the wrong column. In all versions, this issue also has the effect that a simple EXISTS will not apply the Boolean type handler, leading to simple 1/0 values for backends without native boolean instead of True/False. The fix includes that an EXISTS columns argument will be anon-labeled like other column expressions; a similar fix is implemented for pure-boolean expressions likenot_(True())
. fixes#3372
→ <<cset 3e80d628bd13>>
- Log in to comment
just curious why isnt this reported as a bug in sqlalchemy_utils? the downstream author would then present this as a sqlalchemy issue. its not cool to ask me to debug other people's projects, my own are enough.
I'll take a look and see if it is anything obvious.