SQLAlchemy 1.0.0 tries to process type value when not expected

[Charles-Axel Dein]

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).

[Mike Bayer]

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.

[Mike Bayer]

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 ?

[Mike Bayer]

OK, new issue, will check

[Charles-Axel Dein]

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.

[Charles-Axel Dein]

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.

[Mike Bayer]

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.

[Mike Bayer]

• 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 #918 this 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 like not_(True()). fixes #3372

→ <<cset 3e80d628bd13>>