JSONB indexing broken, Postgres 9.5, SQLAlchemy 1.1.4

[ben last]

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

[Mike Bayer]

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.

[Mike Bayer]

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

[ben last]

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.

[Mike Bayer]

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.