[Sqlalchemy-tickets] Issue #3881: JSONB indexing broken, Postgres 9.5, SQLAlchemy 1.1.4 (zzzeek/sqlalchemy)

[ben l. <iss...@bi...>]

New issue 3881: JSONB indexing broken, Postgres 9.5, SQLAlchemy 1.1.4
https://bitbucket.org/zzzeek/sqlalchemy/issues/3881/jsonb-indexing-broken-postgres-95

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