Issue 1:
Describe the bug

TypeError is raised when running a query that joins two tables and has a with_entities() clause, if the with_entities contains a column that is of type sqlalchemy_utils.JSONType. That comes from the SQLAlchemy-Utils library.

I don't think this is a bug in SQLAlchemy-Utils since it worked in previous versions of SQLAlchemy? I could be wrong though, maybe it's a latent bug that some change in SQLAlchemy 1.4.19 has only now exposed.

This worked in SQLAlchemy 1.4.18, error started with SQLAlchemy 1.4.19 and persists in all versions above that.

I found that it works in 1.4.18 because self._unique_filter_state is None in call to ResultInternal._iterator_getter() in sqlalchemy/engine/result.py. In versions greater than that, self._unique_filter_state is set to a 2-tuple with value (set(), None). A different code path is walked down when it's not null, which is what raises the error:

sqlalchemy/lib/sqlalchemy/engine/result.py

Line 364
in
680baea

if self._unique_filter_state:

To Reproduce

"""
pip install SQLAlchemy-Utils==0.37.8

pip install SQLAlchemy==1.4.23

-- code below works on SQLAlchemy==1.4.18. fails on 1.4.19. so it was introduced with 1.4.19

In [2]: print(sys.version, sys.platform)
3.6.9 (default, Apr 20 2021, 10:15:16) [GCC 9.3.0] linux
"""

import sqlalchemy_utils
from sqlalchemy import Column, ForeignKey, Integer, JSON, create_engine
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

class AAA(Base):
__tablename__ = 'aaa'
id = Column(Integer, primary_key=True)
json_column = Column(sqlalchemy_utils.JSONType, nullable=False)

class BBB(Base):
__tablename__ = 'bbb'
id = Column(Integer, primary_key=True)
aaa_id = Column(Integer, ForeignKey('aaa.id'), nullable=True)
parent = relationship('AAA', backref='children')

class CCC(Base):
__tablename__ = 'ccc'
id = Column(Integer, primary_key=True)
json_column = Column(JSON, nullable=False)

class DDD(Base):
__tablename__ = 'ddd'
id = Column(Integer, primary_key=True)
ccc_id = Column(Integer, ForeignKey('ccc.id'), nullable=True)
parent = relationship('CCC', backref='children')

def main():
eng = create_engine('postgresql://postgres@localhost/mcve', echo=False)
Base.metadata.drop_all(eng)
Base.metadata.create_all(eng)
db_session = Session(eng)

db_session.add(AAA(id=13, json_column=[1, 2, 3]))
db_session.add(BBB(id=333, aaa_id=13))

db_session.add(CCC(id=13, json_column=[1, 2, 3]))
db_session.add(DDD(id=333, ccc_id=13))
db_session.commit()

print('\nthis works')
query = db_session.query(AAA).with_entities(AAA.id, AAA.json_column)
for (parent_id, json_column) in query:
print(parent_id, json_column)

print('\nthis works also')
query = db_session.query(AAA).join(BBB).with_entities(AAA.id)
for (parent_id,) in query:
print(parent_id)

print('\nthis works also. using the JSON type instead of sqlalchemy_utils.JSONType')
query = db_session.query(CCC).join(DDD).with_entities(CCC.id, CCC.json_column)
for (parent_id, json_column) in query:
print(parent_id, json_column)

print('\nbut this fails with TypeError: unhashable type: list. '
'Problem comes from having AAA.json_column in the with_entities() clause, then iterating over the query:')
query = db_session.query(AAA).join(BBB).with_entities(AAA.id, AAA.json_column)
for (parent_id, json_column) in query:
print(parent_id, json_column)

if __name__ == '__main__':
main()

Error

$ python mcve.py

this works
13 [1, 2, 3]

this works also
13

this works also. using the JSON type instead of sqlalchemy_utils.JSONType
13 [1, 2, 3]

but this fails with TypeError: unhashable type: list. Problem comes from having AAA.json_column in the with_entities() clause, then iterating over the query:
Traceback (most recent call last):
File "mcve.py", line 82, in <module>
main()
File "mcve.py", line 77, in main
for (parent_id, json_column) in query:
File "/home/jhersch/.virtualenvs/gemini3.6.9/lib/python3.6/site-packages/sqlalchemy/engine/result.py", line 371, in iterrows
if hashed in uniques:
TypeError: unhashable type: 'list'

Versions

OS: linux

Python: 3.6.9

SQLAlchemy: reproduces in 1.4.19 through 1.4.23. does NOT reproduce in 1.4.18

Database: postgresql

DBAPI: psycopg2

Additional context

If I look in postgres for the definitions of the relevant tables with JSON columns, I find their schemas are identical up to renames. So problem is certainly not in at the db level. I suppose it's in the ORM, when the json_column value is used. In one case it is converted to something that can be hashed (maybe a JSON string?) and in the other it's left as a list, which cannot be hashed.

mcve=# \d aaa
Table "public.aaa"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('aaa_id_seq'::regclass)
json_column | json | | not null |
Indexes:
"aaa_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "bbb" CONSTRAINT "bbb_aaa_id_fkey" FOREIGN KEY (aaa_id) REFERENCES aaa(id)

mcve=# \d ccc
Table "public.ccc"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------------------------------
id | integer | | not null | nextval('ccc_id_seq'::regclass)
json_column | json | | not null |
Indexes:
"ccc_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "ddd" CONSTRAINT "ddd_ccc_id_fkey" FOREIGN KEY (ccc_id) REFERENCES ccc(id)

Issue 2:
Migrated issue, originally created by Anonymous

The code looks like this (skipping a log of details):

class visit_dim_category(object):

pass

...

class schema

...

#somewhere in class schema:

self.visit_dim_category = Table( "visit_dim_category", self.metadata,

Column("id",Integer, primary_key=True)

,Column("affiliate", Integer, ForeignKey("affiliate.id"))

,Column("product", Integer, ForeignKey("product.id"))

)

Index("visit_dim_category_idx1",self.visit_dim_category.c.affiliate, self.visit_dim_category.c.product, unique=True)

...

visit_dim_category.mapper = mapper(visit_dim_category, self.visit_dim_category)

#creating test db

db = create_engine(conf.connection())

schema = schema(db)

schema.metadata.drop_all()

schema.metadata.create_all()

it works fine in 0.2.7 and 0.2.8 but after updrade to 0.3 this code gives the following errors:

[04:49 ]cff@joy:~/work/stats3/server/test# ./test_db.py

create test schema...

mysql://root:root@localhost/stat3

Traceback (most recent call last):

File "./test_db.py", line 15, in ?

schema.metadata.create_all()

File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 862, in

create_all

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

413, in create

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

437, in _run_visitor

File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 882, in

accept_schema_visitor

File "build/bdist.linux-i686/egg/sqlalchemy/ansisql.py", line 637,

in visit_metadata

File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 268, in

accept_schema_visitor

File "build/bdist.linux-i686/egg/sqlalchemy/ansisql.py", line 671,

in visit_table

File "build/bdist.linux-i686/egg/sqlalchemy/schema.py", line 819, in

accept_schema_visitor

File "build/bdist.linux-i686/egg/sqlalchemy/ansisql.py", line 749,

in visit_index

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

763, in execute

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

365, in proxy

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

329, in _execute_raw

File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line

348, in _execute

sqlalchemy.exceptions.SQLError: (OperationalError) (1061, "Duplicate

key name 'visit_dim_category_idx1'") 'CREATE UNIQUE INDEX

visit_dim_category_idx1 ON visit_dim_category (affiliate, product)' ()