- edited description
JSONB indexing broken, Postgres 9.5, SQLAlchemy 1.1.4
Upgrading from SQLAlchemy 1.0.14 to 1.1.4 breaks queries that access specific fields of a JSONB column.
I've attached a minimal test script that demonstrates the issue. When run under SQLAlchemy 1.0.14, the output is:
SQLAlchemy 1.0.14
ORM: SELECT test.id AS test_id, test.attributes AS test_attributes
FROM test
WHERE CAST(test.attributes ->> %(attributes_1)s AS BOOLEAN) = true
Core: SELECT test.id, test.attributes
FROM test
WHERE CAST(test.attributes ->> %(attributes_1)s AS BOOLEAN) = true
When run under SQLAlchemy 1.1.4, the output is:
SQLAlchemy 1.1.4
ORM: SELECT test.id AS test_id, test.attributes AS test_attributes
FROM test
WHERE CAST((test.attributes -> %(attributes_1)s) AS BOOLEAN) = true
Core: SELECT test.id, test.attributes
FROM test
WHERE CAST((test.attributes -> %(attributes_1)s) AS BOOLEAN) = true
...and an exception is raised:
Traceback (most recent call last):
File "/pusheen/temp/jsontest.py", line 83, in <module>
test()
File "/pusheen/temp/jsontest.py", line 75, in test
results = list(orm_q.all())
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2645, in all
return list(self)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2797, in __iter__
return self._execute_and_instances(context)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2820, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
exc_info
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/pusheen/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 469, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type jsonb to boolean
LINE 3: WHERE CAST((test.attributes -> 'flag') AS BOOLEAN) = true
^
[SQL: 'SELECT test.id AS test_id, test.attributes AS test_attributes \nFROM test \nWHERE CAST((test.attributes -> %(attributes_1)s) AS BOOLEAN) = true'] [parameters: {'attributes_1': 'flag'}]
The ProgrammingError is because the -> operator has been used to index the JSONB field. This should be ->>
The issue is reproducible with postgres, version "PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit". Note that we're using the Posgres dialect JSONB in our code (we use this extensively).
Relevant PIP requirements are:
psycopg2==2.6.2
SQLAlchemy==1.0.14
SQLAlchemy-Utils==0.32.9
Comments (5)
-
reporter -
repo owner works if i run it like this:
orm_q = session.query(Test).filter( Test.attributes['flag'].astext.cast(Boolean) == True ) core_q = select( [ TEST_T.c.id, TEST_T.c.attributes ] ).where( TEST_T.c.attributes['flag'].astext.cast(Boolean) == True )
confirm this fixes on your end, would need to find the time to dig into what the two formats of CAST mean here, there have been changes to expression logic in 1.1.
-
repo owner - changed status to closed
yup this is a behavioral change in 1.1, note in 1.0 it is implicitly invoking "astext" (e.g. ->>) which is no longer the case, this change is described at http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#the-json-cast-operation-now-requires-astext-is-called-explicitly
-
reporter Thanks @zzzeek - I guess it's a testament to SQLAlchemy that I never expected there to be a breaking release :) . In our case, it's relatively easy to add
astext
where required, so I'll do that. -
repo owner when that second digit bumps in the release series, things will break. It should be very few things that break, and most things that break should be behaviors that weren't really defined previously being made to act a certain way, but yeah those migration notes are always out there on every x.y change.
- Log in to comment