[Sqlalchemy-tickets] Issue #3995: Postgres JSON Array access fails on postgresql+pg8000 (zzzeek/sqlalchemy)

[Patrick H. <iss...@bi...>]

New issue 3995: Postgres JSON Array access fails on postgresql+pg8000
https://bitbucket.org/zzzeek/sqlalchemy/issues/3995/postgres-json-array-access-fails-on

Patrick Hayes:

I've observed that the `[]` operator to access array elements of JSON/JSONB columns with postgresql+pg8000 returns incorrect values 

Here is a minimal example that reproduces the behaviour, it should be executable as long as you have a postgres server running on port 5432 with username `username`, password `password`, and a database `testdb`

```
from sqlalchemy import BigInteger, Column, create_engine
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Model(Base):
  __tablename__ = 'test'
  id = Column(BigInteger, primary_key=True)
  data = Column(JSONB, name='data_json', nullable=False)

def run_with_scheme(scheme):
  db_url = URL(
    scheme,
    username='username',
    password='password',
    host='localhost',
    port=5432,
    database='testdb',
  )
  engine = create_engine(db_url)
  Base.metadata.drop_all(engine)
  Base.metadata.create_all(engine)
  session_factory = sessionmaker(bind=engine)
  session = session_factory()
  session.add(Model(data=[1]))
  return session.query(Model.data[0]).all()

print run_with_scheme('postgresql')   # Returns [(1,)]
print run_with_scheme('postgresql+pg8000')  # Returns [(None,)]
```

Is this a sqlalchemy issue or a pg8000 issue? If this operator is not supported on pg8000 then perhaps it should be forbidden in the sqlalchemy layer, as opposed to silently returning the wrong results?