[Sqlalchemy-tickets] Issue #3214: Multi-level polymorphic inheritance broken instances. (zzzeek/sqlalchemy)

[Justin S. <iss...@bi...>]

New issue 3214: Multi-level polymorphic inheritance broken instances.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3214/multi-level-polymorphic-inheritance-broken

Justin Solms:

# Problem

I get unexpected inheritance instances from the multi-level polymorphic example below. I have attached self-contained test (see attached files) for anyone to re-create the problem.

```
#!python

class Entity(Base):
    __tablename__ = 'entity'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    entity_type = Column('type',String)

    __mapper_args__ = {
        'polymorphic_identity':'entity',
        'polymorphic_on':entity_type,
        }
    
class Asset(Entity):    
    __tablename__ = 'asset'
    
    id = Column('entity_id',Integer, ForeignKey('entity.id'), primary_key=True)
    asset_type = Column('asset_type',String)
    
    __mapper_args__ = {
        'polymorphic_identity':'asset',
        'polymorphic_on':asset_type,
        }

class Listed(Asset):
    __tablename__ = 'listed'

    id = Column('asset_entity_id',Integer, ForeignKey('asset.entity_id'), primary_key=True)
    ticker = Column(String)
    asset_class = Column(String)
 
    __mapper_args__ = {
        'polymorphic_identity':'listed',
        'polymorphic_on':asset_class,
        }

class Equity(Listed):

    __mapper_args__ = {
            'polymorphic_identity':'equity',
            }

```

# Test code snippets

There are 5 instances of the same class in each test. 

The unexpected behavior:
* Note test 2 and test 3 the last output line. The class appears different from the previous 4 lines.
* The 1st test I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Asset`.

## Test 1

* I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Asset`.

```
#!python
print 'Test 1'
entities = session.query(Entity)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)
```

Output:

```
Test 1
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```

## Test 2

* I believe should deliver the type `test.Equity`. It does not. Instead it is `test.Listed`.
* Note the last output line. The class appears different from the previous 4 lines.

```
#!python
print 'Test 2'
entities = session.query(Asset)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)
```

Output:

```
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Listed'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```

## Test 3

* I believe should deliver the type `test.Equity`. It does except for the last output line which is `test.Asset`. The class appears different from the previous 4 lines.

```
#!python
print 'Test 3'
entities = session.query(Listed)
for ent in entities:
    print '<Type:%s, entity_type:%s, asset_type:%s>' % (type(ent), ent.entity_type, ent.asset_type)

```

Output

```
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Equity'>, entity_type:asset, asset_type:listed>
<Type:<class 'test.Asset'>, entity_type:asset, asset_type:listed>
```


# The self-contained test case

This is a very minimal case of the actual schema I am trying to map to Python.

Please re-construct the test case in a MySQL or other database. I have attached files that re-construct the test case for you.

The test files are:

1. `test.py` - The test code.
2. `test_db_create.sql` - Creates the simple test schema
3. `test_result.txt` - The odd output of the test.
4. `table_listing.txt` - A quick glance at the test schema tables.

Feel free to contact me. This issue is critical to my project and I cannot re-design the dependent table schema.

Kind regards,
Justin